【读书笔记】MySql实战45讲——orderby的工作原理

该课程来自极客时间《MySQL实战45讲》

一、举例

表的部分定义如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

假设你要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前1000个人的姓名、年龄

sql语句可以写为

select city,name,age from t where city='杭州' order by name limit 1000;

二、name字段无序

2.1、全字段排序

为避免全表扫描，在city字段上加索引

创建完索引后，可以用explain查看执行情况

在Extra字段中，“Using filesort”表示需要排序（因为name字段无序）

在MySql中会给每个线程分配一块内存用于排序，称为sort_buffer

city索引示意图如下：

orderby语句执行流程如下：

1. 初始化sort_buffer，确定放入name、city、age这三个字段；

2. 从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X；

3. 到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；

4. 从索引city取下一个记录的主键id；

5. 重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中的ID_Y；

6. 对sort_buffer中的数据按照字段name做快速排序；

7. 按照排序结果取前1000行返回给客户端。

2.1.1、排序的完成地点

内存中的为sort_buffer，但当sort_buffer容量小于要排序的数据量时，就需要使用外部排序

外部排序是利用磁盘临时文件辅助排序，且外部排序一般使用归并排序算法

将需要排序的数据分成x份，每一份单独排序后存在不同的临时文件中，最后把x份有序文件进行合并成一个有序的大文件

2.2、rowid排序

在上面这个算法过程里面，只对原表的数据读了一遍，剩下的操作都是在sort_buffer和临时文件中执行的。

但这个算法有一个问题，就是如果查询要返回的字段很多的话，那么sort_buffer里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。

如果排序的单行长度太大，可以采用rowid排序。

max_length_for_sort_data，是MySQL中专门控制用于排序的行数据的长度的一个参数。

它的意思是，如果单行的长度超过这个值，MySQL就认为单行太大，要换一个算法。

新的算法放入sort_buffer的字段，只有要排序的列（name字段）和主键id

但这时，排序的结果就因为少了city和age字段的值，不能直接返回了

执行流程如下：

1. 初始化sort_buffer，确定放入两个字段，即name和id；

2. 从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X；

3. 到主键id索引取出整行，取name、id这两个字段，存入sort_buffer中；

4. 从索引city取下一个记录的主键id；

5. 重复步骤3、4直到不满足city='杭州’条件为止，也就是图中的ID_Y；

6. 对sort_buffer中的数据按照字段name进行排序；

7. 遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name和age三个字段返回给客户端。

对比全字段排序，由于排序结果缺少city和age字段的值，不能直接返回，所以多了第七步，需要回原表取前1000行

2.3、全字段排序与rowid排序的对比

如果内存够，就要多利用内存，尽量减少磁盘访问。

如果MySQL实在是担心排序内存太小，会影响排序效率，才会采用rowid排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据

如果MySQL认为内存足够大，会优先选择全字段排序，把需要的字段都放到sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据

对于InnoDB表来说，rowid排序会要求回表多造成磁盘读，因此不会被优先选择

不是所有orderby都需要排序操作的，如果原来的name就是有序的呢？

三、name字段有序

创建city和name的联合索引

alter table t add index city_user(city, name);

这样流程就会是

1. 从索引(city,name)找到第一个满足city='杭州’条件的主键id；

2. 到主键id索引取出整行，取name、city、age三个字段的值，作为结果集的一部分直接返回；

3. 从索引(city,name)取下一个记录主键id；

4. 重复步骤2、3，直到查到第1000条记录，或者是不满足city='杭州’条件时循环结束。

用explain查看，可以看出是不需要临时表和排序的

由于返回的字段有三个，现在是city和name的联合索引，我们自然会想到

使用city、name、age的联合索引，就符合返回结果条件，这就会是联合索引，无需回原表取值

创建联合索引

alter table t add index city_user_age(city, name, age);

执行流程为

1. 从索引(city,name,age)找到第一个满足city='杭州’条件的记录，取出其中的city、name和age这三个字段的值，作为结果集的一部分直接返回；

2. 从索引(city,name,age)取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；

3. 重复执行步骤2，直到查到第1000条记录，或者是不满足city='杭州’条件时循环结束。
   
   explain结果
   
   Extra中多了“Using index”，使用了覆盖索引

四、问题

假设你的表里面已经有了city_name(city, name)这个联合索引，然后你要查杭州和苏州两个城市中所有的市民的姓名，并且按名字排序，显示前100条记录。如果SQL查询语句是这么写的 ：

mysql> select * from t where city in ('杭州',"苏州") order by name limit 100;

那么，这个语句执行的时候会有排序过程吗，为什么？

如果业务端代码由你来开发，需要实现一个在数据库端不需要排序的方案，你会怎么实现呢？

进一步地，如果有分页需求，要显示第101页，也就是说语句最后要改成 “limit 10000,100”， 你的实现方法又会是什么呢？

答案：虽然有(city,name)联合索引，对于单个city内部，name是递增的。但是由于这条SQL语句不是要单独地查一个city的值，而是同时查了"杭州"和" 苏州 "两个城市，因此所有满足条件的name就不是递增的了。也就是说，这条SQL语句需要排序。

那怎么避免排序呢？

这里，我们要用到(city,name)联合索引的特性，把这一条语句拆成两条语句，执行流程如下：

执行select * from t where city=“杭州” order by name limit 100; 这个语句是不需要排序的，客户端用一个长度为100的内存数组A保存结果。

执行select * from t where city=“苏州” order by name limit 100; 用相同的方法，假设结果被存进了内存数组B。

现在A和B是两个有序数组，然后你可以用归并排序的思想，得到name最小的前100值，就是我们需要的结果了。

如果把这条SQL语句里“limit 100”改成“limit 10000,100”的话，处理方式其实也差不多，即：要把上面的两条语句改成写：

select * from t where city="杭州" order by name limit 10100; 

和

 select * from t where city="苏州" order by name limit 10100。

这时候数据量较大，可以同时起两个连接一行行读结果，用归并排序算法拿到这两个结果集里，按顺序取第10001~10100的name值，就是需要的结果了。

当然这个方案有一个明显的损失，就是从数据库返回给客户端的数据量变大了。

所以，如果数据的单行比较大的话，可以考虑把这两条SQL语句改成下面这种写法：

select id,name from t where city="杭州" order by name limit 10100; 

和

select id,name from t where city="苏州" order by name limit 10100。

然后，再用归并排序的方法取得按name顺序第10001~10100的name、id的值，然后拿着这100个id到数据库中去查出所有记录。

上面这些方法，需要你根据性能需求和开发的复杂度做出权衡。