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

朴灿烈づ我的快乐病毒、 2023-09-23 14:46 72阅读 0赞

在开发系统的时候，你总是不可避免地会使用到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命令来看看这个语句的执行情况；

![8db88df7f6b44d009ae1cc5ba54982e4.png][]

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

![cb0f5a380ee2400db92d7da463b1cc3d.png][]

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

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

这个排序过程，称为全字段排序，执行流程的示意图如下所示：

![c58fe401a01740368e158120c00f8619.png][]

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

![fca624621eb641c59718d46491426209.png][]

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

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

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

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

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

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

![08b3b58e4e4943a48d82f783f42a947b.png][]

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

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

![94506d71bfe64ade85d4f3ebc0aa0c65.png][]

用 explain 的结果来印证一下：

![43410a67ee182f696c33d6f711ae7aee.png][]

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

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

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

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

相关分析参考的我的文章：[《编码踩坑——MySQL 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)到底代表什么意思？》][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 “随机排序、内存临时表“][MySQL_45_17]

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

[8db88df7f6b44d009ae1cc5ba54982e4.png]: https://img-blog.csdnimg.cn/8db88df7f6b44d009ae1cc5ba54982e4.png
[cb0f5a380ee2400db92d7da463b1cc3d.png]: https://img-blog.csdnimg.cn/cb0f5a380ee2400db92d7da463b1cc3d.png
[c58fe401a01740368e158120c00f8619.png]: https://img-blog.csdnimg.cn/c58fe401a01740368e158120c00f8619.png
[fca624621eb641c59718d46491426209.png]: https://img-blog.csdnimg.cn/fca624621eb641c59718d46491426209.png
[08b3b58e4e4943a48d82f783f42a947b.png]: https://img-blog.csdnimg.cn/08b3b58e4e4943a48d82f783f42a947b.png
[94506d71bfe64ade85d4f3ebc0aa0c65.png]: https://img-blog.csdnimg.cn/94506d71bfe64ade85d4f3ebc0aa0c65.png
[43410a67ee182f696c33d6f711ae7aee.png]: https://img-blog.csdnimg.cn/img_convert/43410a67ee182f696c33d6f711ae7aee.png
[MySQL order by_limit_ _ _ _]: https://blog.csdn.net/minghao0508/article/details/129463749
[MYSQL_int_11]: https://blog.51cto.com/yszr/2819384
[MySQL_45_17]: https://blog.csdn.net/minghao0508/article/details/127841700
[16 _ _order by_-]: https://time.geekbang.org/column/article/73479