读mysql45讲-order by排序

假设有一张表，DDL如下：

CREATE TABLE `t_user_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `city_name` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5001 DEFAULT CHARSET=utf8

插入数据的存储过程：

CREATE DEFINER=`zhuruilin_dev`@`%` PROCEDURE `dev`.`test1`()
begin declare i int;
set
i = 1;

while(i <= 5000)do
insert into t_user_info(name,city_name,age) 
values(concat('机器人' , i , '号'),concat('模拟城市',i%2,'号'), i);
set
i = i + 1;
end while;
end

有一个高频请求查询是根据一个城市的所有人的名字，年龄，以及城市名，并且根据姓名排序，取前1000条。

explain select city_name,age,name
from t_user_info tui 
where tui.city_name  = '模拟城市0号'
order by name
limit 1000

没有给city_name添加索引前：

给city_name加了索引之后：

Extra这个字段中的“Using filesort”表示的就是需要排序，MySQL会给每个线程分配一块内存用于
排序，称为sort_buffer

执行sql的大概流程就是：

• 初始化sort_buffer，讲name，age，city_name就是查询字段放进去
• 找到第一个满足 city_name = ’模拟城市0号‘的索引项，
• 根据找到的第一个满足条件的索引项的id，进行回表操作，取 name，age，city_name字段的值放入sort_buffer中；
• 继续寻找满足条件的索引项，并且重复上面的操作，直到不满足查询条件
• 讲sort_buffer中的数据根据name字段进行排序
• 取前1000条记录返回

按照name字段排序这个步骤可能是在内存中完成，也可能需要使用外部排序，也就是借用磁盘空间进行排序，这个取决于排序所需要的内存和参数sort_buffer_size。参数sort_buffer_size就是MYSQL预先设置好排序可以使用的内存大小。如果排序的数据量小于sort_buffer_size的大小，那就在内存中完成；否则就利用磁盘临时文件辅助排序。

当需要使用外部排序的时候（外部排序通常使用归并排序算法），MYSQL将整个数据分成好几份，每一份排完序之后就单独放在一个临时文件中，然后统一把所有的单独的排好序的临时文件再合并成一个有序的文件。

使用rowid排序

上面的算法中，只在根据id查询name，age，city_name的时候进行了回表的操作；剩下的都是在sort_buffer和临时文件中进行的，如果需要回表查询的字段很多的话，那sort_buffer中就会被占用很大空间，但实际的行数却很少，就需要分成很多个临时文件，排序的性能就有下降。

max_length_for_sort_data，是MySQL中专门控制用于排序的行数据的长度的一个参数。它的意 思是，如果单行的长度超过这个值，MySQL就认为单行太大，要换一个算法。

大概的流程如下：

• 初始化sort_buffer，将需要排序的字段和主键id放入
• 从索引树上找到第一个满足city_name='模拟城市0号’的索引项
• 根据索引项中的id回表查询到name，id；放入到sort_buffer中；
• 继续寻找满足条件的数据，重复上面的操作
• 直到找到不满足条件的数据，
• 对sort_buffer中数据根据name字段进行排序
• 取前1000个数据的id，然后回表去查询name，city_name，age，返回

rowid排序过程和上面全字段排序过程的区别就是多了一个回表查询name,city_name,age的操作，在rowid的第三个步骤中，只是将name，id放入了sort_buffer中，比全字段排序过程中的第三个步骤放入的字段少了一些；这样的话sort_buffer就可以放入更多的行数，避免了使用很多临时文件的情况。

其实，并不是所有的order by语句，都需要排序操作的。从上面分析的执行过程，我们可以看 到，MySQL之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。如果根据city_name这个有索引的字段找到name都是排序好的，那是不是就不需要再排序了？

创建一个city_name和name的联合索引，再次查询sql：

Extra字段中没有Using filesort了，也就是不需要排序了。