一、简单例子
二、工作原理
三、优化思路
- 联合索引
- 覆盖索引
四、注意问题
- 没有where条件，order by字段需要加索引吗
- 分页limit过大时，会导致大量排序怎么办?

一、简单例子

假设用一张员工表，表结构如下

CREATE TABLE `staff` (
`id` BIGINT ( 11 ) AUTO_INCREMENT COMMENT '主键id',
`id_card` VARCHAR ( 20 ) NOT NULL COMMENT '身份证号码',
`name` VARCHAR ( 64 ) NOT NULL COMMENT '姓名',
`age` INT ( 4 ) NOT NULL COMMENT '年龄',
`city` VARCHAR ( 64 ) NOT NULL COMMENT '城市',
PRIMARY KEY ( `id`),
INDEX idx_city ( `city` )
) ENGINE = INNODB COMMENT '员工表';

表数据结构

我们现在有这么一个需求：查询前10个，来自深圳员工的姓名、年龄、城市，并且按照年龄小到大排序。对应的 SQL 语句就可以这么写：

select name,age,city from staff where city = '深圳' order by age limit 10;

这条语句的逻辑很清楚，但是它的底层执行流程是怎样的呢？

二、工作原理

explain 执行计划

我们先用Explain关键字查看一下执行计划

执行计划的key这个字段，表示使用到索引idx_city
Extra这个字段的 Using index condition 表示索引条件
Extra 这个字段的 Using filesort 表示用到排序
全字段排序
MySQL 会给每个查询线程分配一块小内存，用于排序的，称为 sort_buffer。什么时候把字段放进去排序呢，其实通过idx_city 索引找到对应的数据，才把数据放进去。

idx_city 索引数，叶子节点存储的是主键id。还有一颗id主键簇族索引数。

先通过idx_city索引树，找到对应的主键id,然后再通过拿到的主键id,索引id主键索引数，找到对应的行数据。
加上order by 之后，整体的执行流程就是

先根据辅助索引，通过city=’深圳’，找到对应主键id
通过主键id回表查询，查询到数据放进sort_buffer
将在sort_buffer中，将所有数据根据age进行排序;
按照排序结果取前10行返回客户端

将查询所需的的字段全部读取到sort_buffer中，就是全字段排序，把查询的所有字段放在sort_buffer,而sort_buffer是一个内存，如果数据太大，需要用到磁盘来排序。
实际sort_buffer的大小是由一个参数控制的：sort_buffer_size

磁盘临时文件排序

确定使用磁盘文件进行排序，可以使用下面几个命令

## 打开optimizer_trace，开启统计
set optimizer_trace = "enabled=on";
## 执行SQL语句
select name,age,city from staff where city = '深圳' order by age limit 10;
## 查询输出的统计信息
select * from information_schema.optimizer_trace

可以从 number_of_tmp_files 中看出，是否使用临时文件

number_of_tmep_files 表示使用排序的磁盘临时文件。如果number_of_temp_files > 0 , 表示使用了磁盘文件来排序。
使用了磁盘文件，整个排序过程

从主键id索引数，拿到需要的数据，并放到sort_buffer内存块中。当sort_buffer快要满，就对sort_buffer中的数据排序，排完后，把数据临时放到一个小文件中。
继续回到主键 id 索引树取数据，继续放到sort_buffer内存中，排序后，也把这些数据写入到磁盘临时小文件中。
继续循环，直到取出所有满足条件的数据。最后把磁盘的临时排好序的小文件，合并成一个有序的大文件

TPS: 借助磁盘临时小文件排序，实际上使用的是归并排序算法。

rowid排序

rowid 排序就是，只把查询SQL需要用于字段和主键id,放到sort_buffer中。那么如何确定走的是全字段排序还是rowid排序呢？
实际上有个参数控制的。这个参数就是max_length_for_sort_data，它表示MySQL用于排序行数据的长度的一个参数，如果单行的长度超过这个值，MySQL 就认为单行太大，就换rowid 排序。我们可以通过命令看下这个参数取值。

show variables like 'max_length_for_sort_data';

使用rowid排序流程

MySQL 为对应的线程初始化sort_buffer，放入需要排序的age字段，以及主键id；
从索引树idx_city，找到第一个满足 city=’深圳’条件的主键 id，也就是图中的id=9；
到主键 id 索引树拿到id=9的这一行数据，取age和主键id的值，存到sort_buffer；
从索引树idx_city 拿到下一个记录的主键 id，即图中的id=13；
重复步骤 3、4 直到city的值不等于深圳为止；
前面5步已经查找到了所有city为深圳的数据，在 sort_buffer中，将所有数据根据age进行排序；
遍历排序结果，取前10行，并按照 id 的值回到原表中，取出city、name 和 age 三个字段返回给客户端。

对比一下全字段排序的流程，rowid 排序多了一次回表。

全字段排序和rowid排序

全字段排序：sort_buffer内存不够的话，就需要用到磁盘临时文件，造成磁盘访问。
rowid排序：sort_buffer可以放更多数据，但是需要再回到原表去取数据，比全字段排序多一次回表。

三、优化思路
因为数据是无序的，所以就需要排序。如果数据本身是有序的，那就不用排了。而索引数据本身是有序的，我们通过建立联合索引，优化order by 语句。

我们还可以通过调整max_length_for_sort_data等参数优化；

联合索引

再回顾下示例SQL的查询计划

explain select name,age,city from staff where city = '深圳' order by age limit 10;

我们给查询条件city和排序字段age，加个联合索引idx_city_age。再去查看执行计划

alter table staff add  index idx_city_age(city,age);
explain select name,age,city from staff where city = '深圳' order by age limit 10;

可以发现，加上idx_city_age联合索引，就不需要Using filesort排序了。为什么呢？因为索引本身是有序的，我们可以看下idx_city_age联合索引示意图，如下：

整个SQL执行流程变成酱紫：

从索引idx_city_age找到满足city=’深圳’ 的主键 id
到主键 id索引取出整行，拿到 name、city、age 三个字段的值，作为结果集的一部分直接返回
从索引idx_city_age取下一个记录主键id
重复步骤 2、3，直到查到第10条记录，或者是不满足city=’深圳’ 条件时循环结束。

流程示意图如下：

从示意图看来，还是有一次回表操作。针对本次示例，有没有更高效的方案呢？有的，可以使用覆盖索引

覆盖索引

四、注意问题

没有where条件，order by字段需要加索引吗

日常开发过程中，我们可能会遇到没有where条件的order by , 那么这个时候order by 后面的字段是否需要加索引呢，如果有一个SQL,create_time是否需要加索引

select * from A order by create_time;

无条件查询的话，即使create_time上有索引,也不会使用到。因为MySQL优化器认为走普通二级索引，再去回表成本比全表扫描排序更高。所以选择走全表扫描,然后根据全字段排序或者rowid排序来进行。

select * from A order by create_time limit m;

无条件查询,如果m值较小,是可以走索引的.因为MySQL优化器认为，根据索引有序性去回表查数据,然后得到m条数据,就可以终止循环,那么成本比全表扫描小,则选择走二级索引。

分页limit过大时，会导致大量排序怎么办?

select * from A order by a limit 100000,10

可以记录上一页最后的id，下一页查询时，查询条件带上id，如：where id > 上一页最后id limit 10。
也可以在业务允许的情况下，限制页数。

Order by详解