16 | order by原理

一、抛砖引玉
现有表结构如下：

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;

SQL如下：select city,name,age from t where city=‘杭州’ order by name limit 1000 ;
🚩：假设表中有4000条数据满足杭州这个条件

问题：这句话的执行流程是什么？下图是相关索引

二、全字段排序

👋：1.了解，Extra 这个字段中的“Using filesort”表示的就是需要排序。

🏁：2.MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。
知道了sort_buffer 那么我们大概就知道了排序的基本套路了：

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 行返回给客户端。

⭐️：“按 name 排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。如果数据量太大就需要用磁盘临时文件辅助排序。

🏁：3.如何确定是否使用了临时文件？
可以通过查看 OPTIMIZER_TRACE 的结果中的number_of_tmp_files字段值来确认，其中当大于0时需要使用外部排序，外部排序一般使用归并排序算法。

下图仅供了解

PS：可以这么简单理解，MySQL 将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件。select @b-@a 表示整个执行过程只扫描了 4000 行。

只列出方式方法，不重要，不需要记
/* 打开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; 👋：表示整个执行过程只扫描了 4000 行。

二、rowid排序
全字段排序只对原表的数据读了一遍，剩下的操作都是在 sort_buffer 和临时文件中执行的。但这个算法有一个问题，就是如果查询要返回的字段很多的话，那么sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。

🏁：4.如何让MySQL换一种算法呢？
// 它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。
SET max_length_for_sort_data = 16;

🏁：5.rowid算法跟全字段排序的区别是什么？优缺点是什么？
区别：
第一步中，初始化sort_buffer时放入的字段只有要排序的列和主键ID
最后一步中，根据主键ID回表查询得到其他字段值后返回客户端
优缺点：
全字段排序更加快速，但是内存占用高
rowid方式占用内存小，但是需要回表效率低

三、不排序

到这里是不是觉得这两种方案都不太行呢，是不是所有的 order by 都需要排序操作呢？如果不排序就能得到正确的结果，那对系统的消耗会小很多，语句的执行时间也会变得更短。

⭐️⭐️⭐️首先要明确，为什么MySQL需要排序呢？
MySQL 之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。
所以，给排序列和条件列加上联合索引 就能 避免 MySQL 在内存中排序了！！！

好处是只用扫描limit中的行数就可以返回无需扫描整个符合条件的数据

四、扩展总结
🏁👋：6.Extra内总结
【Using filesort】 本次查询语句中有order by，且排序依照的字段不在本次使用的索引中，不能自然有序。需要进行额外的排序工作。
【Using index】 使用了覆盖索引——即本次查询所需的所有信息字段都可以从利用的索引上取得。无需回表，额外去主索引上去数据。
【Using index condition】 使用了索引下推技术ICP。（虽然本次查询所需的数据，不能从利用的索引上完全取得，还是需要回表去主索引获取。但在回表前，充分利用索引中的字段，根据where条件进行过滤。提前排除了不符合查询条件的列。这样就减少了回表的次数，提高了效率。）
【Using where】 表示本次查询要进行筛选过滤。

五、课后题
假设你的表里面已经有了 city_name(city, name) 这个联合索引，然后你要查杭州和苏州两个城市中所有的市民的姓名，并且按名字排序，显示前 100 条记录。如果 SQL 查询语句是这么写的 ：
mysql> select * from t where city in (“杭州”,“苏州”) order by name limit 100;
那么，这个语句执行的时候会有排序过程吗，为什么？
如果业务端代码由你来开发，需要实现一个在数据库端不需要排序的方案，你会怎么实现呢？
进一步地，如果有分页需求，要显示第 101 页，也就是说语句最后要改成 “limit 10000,100”， 你的实现方法又会是什么呢？

• 单个 city 内部，name 是递增的。但是由于这条 SQL 语句不是要单独地查一个 city 的值，因此所有满足条件的 name 就不是递增的了。也就是说，这条 SQL 语句需要排序。
• 可以执行两条语句select * from t where city = ‘杭州’ limit 100; select * from t where city = ‘苏州’ limit 100; 然后把200条记录在java中排序。
• 深翻页问题可以用每次的最小id取值。

六、课后评论
🏁：7.如下语句会走索引么？select * from t order by create_time desc

• 无条件查询如果只有order by create_time,即便create_time上有索引,也不会使用到。因为优化器认为走二级索引再去回表成本比全表扫描排序更高。所以选择走全表扫描,然后根据老师讲的两种方式选择一种来排序。
• 无条件查询但是是order by create_time limit m。如果m值较小，是可以走索引的。因为优化器认为根据索引有序性去回表查数据,然后得到m条数据,就可以终止循环,那么成本比全表扫描小,则选择走二级索引。即便没有二级索引,mysql针对order by limit也做了优化,采用堆排序。

🏁：8.sort_buffer是在引擎层还是在server层？
server层

🏁：9.varchar(255)为什么大家都这么设定字符串？另外varchar最多保存的多大的字节？
1个字节8位，最大值是 255，用来记录 varchar 的长度。如果大于255，1个字节无法存储，因此需要2个字节（16位）
VARCHAR 最多可以是 65535 字节，事实上如果你用的 utf8mb3 编码的话按理最大可以设置到 varchar(21845)