目录
- 前言
- 全字段排序
- rowid
- 总结
前言
本章详细讲下排序，排序在我们业务开发非常常见，有对时间进行排序，又对城市进行排序的。
不合适的排序，将对系统是灾难性的，这个不是危言耸听。
可能有些人会想，对于排序mysql 是怎么实现的，它的底层原理是怎么样的，如果我加上分页，排序是不是就会快一些。
关于这些问题，本章详细讲解。
有人经常问我，mysql 优化的规则，总是不假思索的说ESR，E 是 equal ，S是sort 。
可见排序有多么重要，为了讲解方便，我先画个思维导图。
上图标的1，2 是mysql 配置文件可以配置的。
可以通过 show variables like 'max_length_for_sort_data'; 可以具体的配置。
从图上我们可以看到mysql 排序分为全字段排序，和 rowid 。
这是两大类，里面又分为内存排序，文件排序，我将从这2大类4小类讲解。
全字段排序
由上图可以看出 Extra = Using filesort 就表示了排序，但此时还不能判断是文件排序还是内存排序
可以根据下面介绍的方法，来确定一个排序语句是否使用了临时文件
/* 打开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 输出 */
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 @b-@a;
Number_of_tmp_files>0 就表示文件排序，没有就表示是内存排序。
sort_buffer_size 越小，那么 Number_of_tmp_files 就会越大，文件排序用的是归并排序，也就是把数据分给多个文件，每个文件排序后，最终合并一个文件。
上面sort_mode 可以看到，这是一个全字段排序，什么是全字段排序，就拿上面这个sql 语句来说，city ,name,age 都在文件里，对name 进行排序
这个排序的内部是这么实现的：
- 初始化 sort_buffer，确定放入 name、city、age 这三个字段；
- 从索引 city 找到第一个满足 city='杭州’ 条件的主键 id
- 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
- 从索引 city 取下一个满足 city='杭州’ 的主键 id；
- 重复步骤 3、4 直到 city 的值不满足查询条件为止
- 对 sort_buffer 中的数据按照字段 name 做快速排序；
- 按照排序结果取前 1000 行返回给客户端。
由此我们发现，排序会对表的所有的记录进行排序，然后在取出1000条
rowid
如果 排序数据的长度超过了 max_length_for_sort_data 就是 rowid排序。
排序数据的长度就是指拿上面这个例子说 name、city、age 这三个字段大于 max_length_for_sort_data 就是rowid 排序。
为什么会这样的呢，mysql 会尽量用内存排序，字段越长，占用空间越大，未了提高排序效率，就会用rowid 排序。
rowid排序的步骤是这样的：
- 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
- 从索引 city 找到第一个满足 city='杭州’条件的主键 id
- 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
- 从索引 city 取下一个记录的主键 id；
- 重复步骤 3、4 直到不满足 city='杭州’条件为止，
- 对 sort_buffer 中的数据按照字段 name 进行排序；
- 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。
我们可以看到 rowid 会多访问一次表，在mysql 看来，排序的复杂度高于回表的复杂度，这也是一种取舍。
综上可以看出不管是内存排序还是文件排序，都是很繁琐的，那么有没有对于这个问题有没有优化点了，在前面我们已经讲过了，索引一定是有序的，如果我们对city，name 建一个联合索引，就不用mysql 重新排序，因为索引本身就是有序的。
就是如下所示：
alter table t add index city_user(city, name);
但是上面虽然不用mysql 用文件排序，但是还是要回表的，那还有没有进一步的优化呢，我们可以考虑用覆盖索引
如下所示：
alter table t add index city_user_age(city, name, age);
这样就不用回表了，用explain 来看 Extra using index
大家要综合考虑吧，索引越多，索引越大，会影响插入的速度的。
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持悠久资源网。
