目录
全字段排序
排序执行流程？
如何确定排序语句是否使用了临时文件？
rowid 排序
排序执行流程？
全字段排序 vs rowid排序
优化1：需排序字段本身有序
优化2：覆盖索引
平时在开发过程中，列表排序使用的挺多的。
例如：要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前 100 个人的姓名、年龄。
表结构如下：
sql实现：
select city, name, age from myuser where city='杭州' order by name limit 100;
数据情况：
explain执行情况
Using filesort：需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。
全字段排序
排序执行流程？
1、初始化sort_buffer，确定放入select字段（name、city、age）
2、从city的普通索引树找到一个满足city = '杭州' 的主键ID
3、根据ID 回表主键索引树，拿到name、city、age三个字段的值，存入sort_buffer中
4、重复2、3步骤，直到city值不满足查询条件为止
5、对sort_buffer中的数据按照字段name排序
6、按照排序结果取前1000行返回客户端
其中，步骤5排序，根据sort_buffer_size与当前排序所需要的内存的关系，来决定是在内存中完成，还是需要外部文件排序。
排序需要的内存 < sort_buffer_size，在内存中完成；反之，就需要利用磁盘临时文件辅助排序
如何确定排序语句是否使用了临时文件？
执行下方sql
/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on';
/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000;
/* 查看 OPTIMIZER_TRACE 输出，主要关注filesort_summary的内容*/
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 计算Innodb_rows_read差值 */
select @[email protected];
mysql> SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
*************************** 1. row ***************************
QUERY: select city, name,age from myuser where city='杭州' order by name limit 100
TRACE: {
"steps": [
...// 省略
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.01 sec)
摘出filesort_summary
select @[email protected] 的返回结果是 221，表示整个执行过程只扫描了 221 - 1 行。 (备注：因为查询 OPTIMIZER_TRACE 这个表时，需要用到临时表，而 internal_tmp_disk_storage_engine 的默认值是 InnoDB。如果使用的是 InnoDB 引擎的话，把数据从临时表取出来的时候，会让 Innodb_rows_read 的值加 1)
rowid 排序
如果排序后需要返回的字段太多，那么sort_buffer里能同时放下的行数很少，就要分成很多个临时文件，排序的性能会很差。
max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度（也就是city、name、age总长36）的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。
排序执行流程？
1、初始化sort_buffer，确定放入需要排序字段与ID（name、id）id
2、从city的普通索引树找到一个满足city = '杭州' 的主键ID
3、根据ID 回表主键索引树，拿到name、id两个字段的值，存入sort_buffer中
4、重复2、3步骤，直到city值不满足查询条件为止
5、对sort_buffer中的数据按照字段name排序
6、遍历排序结果，取前100行，并根据ID 回表主键索引树，取出city、name、age三个字段返回客户端（不用存储到sort_buffer了）
全字段排序 vs rowid排序
如果排序内存太小，mysql认为会影响排序效率，就会使用rowid排序算法，优点：一次可以排序更多行；缺点：最后结果集需要再次回表
如果内存足够大，mysql 会优先选择全字段排序，优点：将需要的字段都放入sort_buffer中，不需要二次回表；缺点：对于rowid来说，需要的临时文件较多
优化1：需排序字段本身有序
如果本身排序字段就有索引？执行sql给name添加索引
alter table myuser add index city_user(city, name);
再看下explain结果
extra中没有using filesort，也就是说不需要排序了
并且由于name本身有序，只需要根据索引树找到前100条数据就完成了，所以只需要扫描100次
优化2：覆盖索引
使用覆盖索引不需要回表，提高查询效率。
执行sql，添加索引
alter table myuser add index city_user_age(city, name, age);
再看下explain结果
可见，使用到了覆盖索引
备注：因为测试数据量少，对于优化1与优化2的执行时间没有明显的差距。