《MySQL实战45讲》——学习笔记16 “order by排序原理、为什么定义varchar(255)、order by+limit不稳定、group by隐式排序“

在开发系统的时候，你总是不可避免地会使用到order by语句；当搞清楚每个语句的排序逻辑是怎么实现的，就能便于评估每个语句的执行对系统资源的消耗，提出优化方案；

本篇介绍MySQL里面order by语句的原理，以及排序过程中2种算法流程：全字段排序和rowid排序；排序也是要使用内存空间的，当内存够用就尽量使用内存，对应的就是全字段排序，好处是避免一次回表；否则，当内存空间不够时，使用rowid排序，仅将参与排序的字段和主键id加载到内存，排完序再去查完整的数据行；此外，由于索引天然有序，适当的建立索引可以避免排序；

查询排序示例

以市民表为例，假设你要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前 1000 个人的姓名、年龄；假设这个表的部分定义是这样的：

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  ;

排序方式1：全字段排序

为避免全表扫描，在city字段加上索引，用explain命令来看看这个语句的执行情况；

Extra这个字段中的“Using filesort”表示的就是需要排序，MySQL会给每个线程分配一块内存用于排序，称为sort_buffer（server层）；为了说明这个查询语句的执行过程，先来看一下city这个索引的示意图；

通常情况下，这个语句执行流程如下所示：

（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；优先在内存中排序，当排序数据量太大内存放不下时，就必须利用磁盘临时文件辅助排序；

外部排序一般使用归并排序算法；使用外部排序时，临时文件一般有多个，即MySQL将需要排序的数据分成M份，每一份单独排序后存在这些临时文件中；然后把这M个有序文件再合并成一个有序的大文件；

排序方式2：

全字段排序算法里，只对原表的数据读了一遍，剩下的操作都是在sort_buffer和临时文件中执行的；如果查询要返回的字段很多的话，那么sort_buffer里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差；所以如果单行很大，全字段排序算法效率不够好；

如果排序的单行长度太大，MySQL还有另外一种算法——rowid排序；即放入sort_buffer的字段只有要参与排序的列（即name字段）和主键id，排完序后再回表查数据；

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

这个例子下，可以通过修改下面这个参数的值来使用算法；

SET max_length_for_sort_data = 16;

city、name、age这三个字段的定义总长度是36，把max_length_for_sort_data设置为16，则单行数据数据长度超过了；（这里的计算方式应该是将字符串当英文来计算，int类型占用4个字节，varchar类型如果存的是中文，且是UTF8格式，那么1个中文占用3个字节，如果存的是英文字母，那么1个英文字母占用1个字节，因此：16*1+16*1+4=36）

切换排序算法后，整个执行流程就如下：

（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三个字段返回给客户端；

不排序的方案——利用索引排序

MySQL的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问；

对于InnoDB表来说，rowid排序会要求回表，多造成磁盘读，因此不会被优先选择；如果MySQL实在是担心排序内存太小，会影响排序效率，才会采用rowid排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据；

其实，并不是所有的order by语句，都需要排序操作的；

从上面分析的执行过程可知，MySQL之所以需要生成sort_buffer在上面做排序操作，其原因是查询出来的数据都是无序的；而索引是B+树的数据结构天然的保证了索引字段的有序性；

如果能够保证从city这个索引上取出来的行，天然就是按照name递增排序，就可以不用再排序了；例如，可以在这个市民表上创建一个city和name的联合索引，与单独的city索引对比下索引结构：

这样整个查询过程的流程就变成了：

（1）从索引(city,name)找到第一个满足city='杭州’条件的主键id；
（2）到主键id索引取出整行，取name、city、age三个字段的值，作为结果集的一部分直接返回；
（3）从索引(city,name)取下一个记录主键id；
（4）重复步骤2、3，直到查到第1000条记录，或者是不满足city='杭州’条件时循环结束；

用 explain 的结果来印证一下：

从图中可以看到，Extra字段中没有Using filesort了，也就是不需要排序了；而且由于(city,name)这个联合索引本身有序，所以这个查询也不用把所有满足city=`杭州`的记录全都读一遍，只要找到满足条件的前1000条记录就可以了，也就是说，只需要扫描1000次。

此外，这个语句的执行流程还可以通过覆盖索引进一步优化，因为查询字段中除了联合索引已经涵盖的city和name，还有一个age，因此可以将age也加入联合索引，这样，二级索引树的叶子节点中已经有全部的查询字段信息，因此无需回表；

当然，这里并不是说要为了每个查询能用上覆盖索引，就要把语句中涉及的字段都建上联合索引，毕竟索引还是有维护代价的（插入/删除数据时需要调整索引树结构），这是一个需要权衡的决定；

order by&limit一起使用时结果排序不稳定

相关分析参考的我的文章：《编码踩坑——MySQL order by&limit顺序不一致 / 堆排序 / 排序稳定性》

现象：SQL查询语句同时包含order by和limit时，当修改limit的值，可能导致"相同排序值的元素之间的现对顺序发生改变"；

原因：MySQL对limit的优化，导致当取到指定limit的数量的元素时，就不再继续添加参与排序的记录了，因此参与排序的元素的数量变化了；而MySQL排序使用的In memory filesort是基于优先级队列，也就是堆排序，而堆排序时不稳定的，会改变排序结果中，相同排序值rank的记录的相对顺序；

建议：排序值带上主键id，即order by rank 改为 order by rank, id 即可；

group by默认隐式排序

在Mysql8.0之前，group by会默认根据作用字段（group by的后接字段）对结果进行排序；在能利用索引的情况下，group by不需要额外进行排序操作；但当无法利用索引排序时，Mysql优化器就不得不选择通过使用临时表然后再排序的方式来实现group by了；

且当结果集的大小超出系统设置临时表大小时，Mysql会将临时表数据copy到磁盘上面再进行操作，语句的执行效率会变得极低；这也是Mysql8.0后选择将此操作（隐式排序）弃用的原因；

思考题

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

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

问题：

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

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

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

答案：

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

（2）从(city,name)联合索引的数据结构可知，单独的city条件下，name有序；因此可以把这一条语句拆成两条语句，分别查出city=“杭州”和city=“苏州”的前100条记录，然后将这2组有序的集合做归并排序，对排序的结果取前100条即可；

（3）思路与问题（2）类似，分别查出city=“杭州”和city=“苏州”的前10100条记录，然后将这2组有序的集合做归并排序，对排序的结果取第10001~10100 条记录即可；但是这种方案下，如果单条记录太长，会导致一次查出的20200条记录太大，因此可以仅将参与排序的字段name和主键id查出来，最终拿100个id回表查完整的数据；

补充知识：为什么经常看到数据库的字符串字段类型定义为varchar(255)？

问题1：varchar(255)能存多少英文字符/中文？

答案：

在字符集为UTF-8的情况下：

（1）MySQL|ver<4.1：VARCHAR以字节为单位存储，所以假设全部为常用汉字（UTF-8

3字节编码长度），则VARCHAR(255)共可存放约85个汉字；

（2）MySQL|ver>=4.1：VARCHAR以字符为单位存储，假设输入仍然为常用汉字，则VARCHAR(255)可以存放255个汉字；

问题2：varchar的最大值是多少呢？

答案：根据官方文档，varchar理论上最多可以是65535字节（这也意味着一条记录只有这一个字段，因为MySQL一行最多只能包含65535字节）；

问题3：varchar和char的区别？

答案：

1. char比较占内存，性能块；varchar不占内存，性能相对慢（只是相对，我们是感觉不到的）；
2. varchar(100)，用多少占多少；当只存储10个字符时，在内存中消耗的就是10个字符；
3. char(100)，固定占位100；当只存储了10个字符时，会在内存中自动补齐，占足100个位置；

因此一般业务字段会使用varchar多一点，建表时指定varchar长度时也可以适当大一点预留长度；

问题4：为什么经常看到数据库的字符串字段类型定义为varchar(255)？

答案：使用varchar(255)主要还是因为节省空间；首先，原则是根据实际存储数据的长度来确定varchar的长度；如果实际数据数据长度大于255，如1000字符长度，则可按照实际情况并预留部分长度来确定varchar的长度，如varchar(1500)；如果实际数据数据长度小于255，则推荐将varchar的长度设置为255，原因如下：

mysql的vachar字段的类型虽然最大长度是65535，其中2到3个字符有特殊用途：
（1）当长度不超过255时，需要有1个字节存储长度（因为一个字节8位，能纪录的长度值最大为2的8次方，即255）；而当长度超过255时，需要有2个字节存储长度（最大记录2的16次方，即65535，而数据库字符串理论最大长度正是65535）；
（2）当不允许非空字段时，需要有1个字节存储不可为空的标识；当允许非空字段时，则不需要占用空间存储标识；

因此，varchar的长度设置为255，可节省1个字节的存储；

相比之下，也经常看到主键的类型设置为 INT(11) 或 BIGINT(20)，有什么特殊作用吗？

答案：实际没啥作用；《MYSQL中的int(11)到底代表什么意思？》这篇文章讲的很清楚，INT(11)中，11代表的并不是长度，而是字符的显示宽度，在字段类型为int时，无论你显示宽度设置为多少，int类型能存储的最大值和最小值永远都是固定的；

那么这个数值什么时候有用呢？当int字段类型设置为无符号且填充零（UNSIGNED ZEROFILL）时，当数值位数未达到设置的显示宽度时，会在数值前面补充零直到满足设定的显示宽度；为什么会有无符号的限制呢，是因为ZEROFILL属性会隐式地将数值转为无符号型，因此不能存储负的数值；

问题5：为什么MYSQL规范中要求对表的varchar字段建索引，长度小于191？

MySQL官方手册索引的章节提到——前缀索引长度限制是和引擎相关的，如果用的是InnoDB，前缀上限是767字节，当启用innodb_large_prefix时，上限可以达到3072字节；

数据库常用的字符集是utf8mb4；utf8mb4是MySQL 5.5.3之后增加的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode，utf8mb4是utf8的超集；之所以需要utf8mb4，是因为之前的utf8编码最大字符长度为3字节，如果遇到4字节的宽字符就会插入异常了，如Emoji表情（Emoji是一种特殊的Unicode 编码，常见于手机上）和很多不常用的汉字；

utf8mb4编码，默认字符长度则应该是767除以4向下取整，就是191；

下篇文章：《MySQL实战45讲》——学习笔记17 “随机排序、内存临时表“

本章参考：16 | “order by”是怎么工作的？-极客时间