mysql的sort by学习
前沿
学习了mysql的next-key-lock后，现在正式进入sort by的学习阶段。有时在项目里会用到sort by语句。我也听说sort by有可能会对mysql造成压力，所以要学习一下sort by的过程，从而才能深刻的了解自己的sort by语句对mysql的压力，避免线上性能低下或者事故。
场景
有这么一个场景，要对杭州市民按照姓名排序，取前1000个人。
给city加索引，表设计如下：
1 CREATE TABLE `t` ( `id` int(11) NOT NULL, 2 `city` varchar(16) NOT NULL, 3 `name` varchar(16) NOT NULL, 4 `age` int(11) NOT NULL, 5 `addr` varchar(128) DEFAULT NULL, 6 PRIMARY KEY (`id`), 7 KEY `city` (`city`)) ENGINE=InnoDB;
然后执行：
1 select city,name,age from t where city='杭州' order by name limit 1000 ;
全字段排序
那接下来引擎层是如何执行的呢？大致过程如下：
1、初始化该sql线程的sort buffer，其大小是sort buffer size决定的。
2、city索引上查找第一个city='杭州'的记录，取出id
3、到主键索引取出select所需的三个字段：city、name和age，插入到sort buffer中；
4、然后继续在city索引上查找下一个记录，重复2、3步，直到city不等于'杭州'；
5、在sort buffer中按照name进行排序
6、取出前1000的记录，返回给用户
这边有一个问题，就是如果数据量太大，那么sort buffer不一定能放下。那此时就要借助磁盘辅助排序。mysql在这边做了优化，是将数据放到若干个小的临时文件中，先各自排序，然后合并成一个大的临时文件，应该是类似于归并排序吧。
上述排序的好处是读完原表中数据后只需借助sort buffer和临时文件排序就行了。
row id排序
还有一个问题，就是要返回的当行数据过大时，会导致sort buffer中放不了多少行数据，就不得不借助临时文件排序，影响性能。row id排序可以缓解这种情况。通过如下配置，告知mysql单行超过多大需要使用row id排序。
假设city, name, age三个字段有32字节，而我们设置超过16个字节就采用row id排序。
1 SET max_length_for_sort_data = 16;
row id排序过程大致如下：
1、初始化sort buffer，确定放入两个字段：name和id；
2、从索引 city 找到第一个满足 city='杭州’条件的主键 id
3、用主键id在主键索引中查找name字段
4、将id和name放入sort buffer中
5、索引中继续查找下一个满足条件的主键id，重复3、4步骤，直到遇到第一个不满足条件的记录。
6、在sort buffer中按name排序，取前1000个
7、回表，获取这1000个id对应的city和age，返回给用户。
这样做的好处是，同样大小的sort buffer可以放更多的记录，尽可能的在内存中完成操作，代价是会多一次回表取其余字段。
借助联合索引
如果选出来的结果是天然有序的，那么我们就不用再做额外排序了。因为索引是天然有序的，所以可以创建city和name的联合索引，这样选出来的结果自然是按照name排序的了
alter table t add index city_user(city, name); select name, age, city from t where city='杭州' limit 1000;
具体的执行过程就不分析了。如果想避免一次回表去读取age字段，也可以讲索引改成如下，这就是覆盖索引。
1 alter table t add index city_user(city, name, age);
