和面试官聊聊MySQL排序的坑（含order by limit 分页出现重复数据问题、结合索引 order by什么情况不会进行内存排序操作(Using filesort)）

向右看齐 2023-10-01 18:16 17阅读 0赞

#### 1. 面试官：MYSQL按多个字段排序需要注意哪些问题？ ####

这是准备考我实操经验呀！巧了前一段时间遇到过类似的坑。针对多字段进行组合排序时，首先order by 后可加多个字段时，字段之间用英文逗号隔开；其次优先级按照字段先后顺序排序；最后需要针对每个字段分别进行自定义排序；如果不指定，`order by 默认按照升序（ASC）排序`。

##### 示例: #####

表数据如下：  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_9_color_FFFFFF_t_70_g_se_x_16]  
SQL1：

select * from user ORDER BY age, userName desc limit 8;

这里我们想实现的效果是先按照age倒序排序，再按照userName倒序排序。实际效果是先按照age正序排序，再按照userName倒序排序。  
![在这里插入图片描述][9fb252a8035a4726a6ccb959ce0908e1.png]  
修改SQL如下，即可实现全部倒序的效果：

select * from user ORDER BY age desc, userName desc limit 8;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_14_color_FFFFFF_t_70_g_se_x_16]

#### 2. 面试官：有没有遇到过order by limit分页出现数据重复的问题？怎么解决的？ ####

在MySQL中我们通常使用limit关键字进行分页查询，比如limit(0,10)表示列出第一页的10条数据，limit(10,10)表示列出第二页。但是，当limit遇到order by的时候，可能会出现翻到第二页的时候，竟然又出现了第一页的记录。有时很难复现，

**SQL如下：**

select *  from user where gender = 1 ORDER BY age desc limit 5;
    select * from user where gender = 1 ORDER BY age desc limit 5,5;

**解决方案：**  
1、**索引排序字段**，即如果在字段上添加索引，就直接按照索引的有序性进行读取并分页，从而可以规避遇到的这个问题。

2、如果排序字段都没有索引，则业务上一般选择在order by的最后加上id asc，也就是在原有排序的基础上排序结果按照主键ID正序排序。因为MYSQL默认按照主键ID正序排序，所以对原结果不会有影响。**相应SQL如下：**

select *  from user where gender = 1 ORDER BY age desc, id asc limit 5;
    select * from user where gender = 1 ORDER BY age desc, id asc limit 5,5;

**3、建议使用order by limit进行分页查询时，order by 的最后加上 id asc。**

**原因分析：**

在MySQL 5.6的版本上，优化器在遇到order by limit语句的时候，做了一个优化，即使用priority queue做堆排序；以达到 在不能使用索引有序性的时候，如果要排序，并且使用了limit n，可以在排序的过程中在保留n条记录 的效果；进而减少sort buffer内存的使用。也就是说**MYSQL5.x低版本没这个问题**。

但是因为堆排序是一个不稳定的排序方法，即相同的值可能排序出来的结果和读出来的数据顺序不一致。

从SQL的执行顺序来看，在进行order by 排序时，所有的记录都是以堆排序的方式来排列的，若排序字段不具备索引有序性，则在第二页数据中，MYSQL见到哪条拿哪条。

另外在功能上，**排序是数据库提供的基础功能，而分页是衍生出来的应用需求**。MYSQL和Oracle提供的limit n和rownum < n的方法，都**没有明确定义分页的概念**，不同场景对数据分页都没有非常高的准确性要求。

#### 3. 面试官：既然你提到了MySQL5.x低版本不会有排序分页数据重复的问题，聊一下MySQL5.x低版本的排序是怎做的？ ####

**有两种方式，分别是：常规排序、优化排序**，MySQL5.6以上采用优先队列排序（堆排序算法）。

##### 常规排序 #####

1> 从 保存满足where条件的全部记录的临时表中获取全部记录；  
2> 将要排序的字段值和row ID 组成键值对（例如：(id, age)），存放到`sort buffer`中；  
3> 如果sort buffer可以存放所有满足条件的(id,age)对，则直接采用快速排序算法进行排序；否则`sort buffer满后，进行排序并持久化到临时文件中`；  
4> 若排序中产生了临时文件，则利用`归并排序算法，保证临时文件中记录是有序的`；  
5> 循环执行上述过程，直到所有满足条件的记录全部参与排序；  
6> 扫描排好序的(id,age)键值对，并利用id回表去捞取SELECT需要返回的列（比如(age,name)）；  
7> 将获取的结果集返回给用户。

##### 优化排序 #####

常规排序除了排序本身，还需要额外两次IO：回表、排序存到临时文件。优化排序相对于常规排序，减少了回表的IO损耗。主要区别在于放入sort buffer的键值对不再由要排序的字段和row ID组成(比如：`(id,age)`)；而是查询的字段和要排序的字段（比如：`(age,name)`）。只是这种方式是有代价的，同样大小的sort buffer，能存放的`(age,name)`数目要小于`(id,age)`，如果sort buffer不够大，可能导致原本不需要写临时文件的排序需要写临时文件，造成额外的IO。

**不过这种方式是有开启条件的：** 参数max\_length\_for\_sort\_data，只有当排序元组小于max\_length\_for\_sort\_data时，才能利用优化排序方式，否则只能用常规排序方式。

**面试官OS：** 可以呀，又答上来了。既然上面你提到了 **根据索引排序字段** 可以解决order by limit分页出现数据重复的问题。

#### 4.面试官：结合索引 order by什么情况不会进行内存排序操作？ ####

我丢，问这么细；是不是要压我工资啊，嘿嘿，我会。

我之前做过一个试验，从order by的字段在不在where条件中、在不在select中？select内容是否索引覆盖、回表？验证了 **结合索引 order by不会进行内存排序操作的情况**。

**表结构和索引如下：**

*  ![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_10_color_FFFFFF_t_70_g_se_x_16]
 *  user表只有一个非主键索引的索引 – **组合索引：idx\_name\_age(`age`, `gender`)**

##### **具体分了四种场景，如下：** #####

###### 1> where和order by的字段都在索引中，但order by的字段不在where条件中，会在内存中进行排序操作。 ######

explain select id, age, gender from user where age > 1 order by gender desc;

从执行计划来看，Extra信息中有`Using filesort`，也就是进行了内存排序操作；另外type = index表示查询只走了`组合索引idx_name_age`，即**索引覆盖**。  
![在这里插入图片描述][fbd362a64ce24384ae02f9e9cef42d57.png]

###### 2> where和order by的字段都在索引中，并且order by的字段也在where条件中，不会在内存中进行排序操作。 ######

explain select id, age, gender from user where age > 1 and gender = 1 order by gender desc;

从执行计划来看，Extra信息中没有`Using filesort`，也就是没进行内存排序操作；另外type = index表示查询只走了`组合索引idx_name_age`，即**索引覆盖**。

![在这里插入图片描述][f6f8bfc8be8848f1bfdd072c920ffe99.png]

###### 3> 在2> where和order by的字段都在索引中，并且order by的字段也在where条件中 的基础上，order by的字段不出现在select条件中，不会在内存中进行排序操作。 ######

explain select id, age from user where age > 1 and gender = 1 order by gender desc;

**结果和2> 的结果一样**；从执行计划来看：Extra信息中没有`Using filesort`，也就是没进行内存排序操作；另外type = index表示查询只走了`组合索引idx_name_age`，即**索引覆盖**。  
![在这里插入图片描述][7a7987830778494394ad1bde2f3c94ee.png]

###### 4> 在2> where和order by的字段都在索引中，并且order by的字段也在where条件中 的基础上，select内容没有索引覆盖，要回表的情况下，不会在内存中进行排序操作 ######

explain select * from user where age > 1 and gender = 1 order by gender desc;

![在这里插入图片描述][178169f849f4460f8f0958d1676487ff.png]  
从执行计划来看：Extra信息中没有`Using filesort`，也就是没进行内存排序操作；另外type = ALL，possible\_keys = idx\_name\_age，表示查询走了`组合索引idx_name_age`，但由于select中的内容没有索引覆盖，所以**回表做了全表扫描**。

##### 结论 #####

**无论select中的内容是什么、进不进行回表，只有当order by 的字段全部出现在 where条件中，并且where条件中的字段全部在一个索引中，才会利用索引的有序性进行排序，而不会在MYSQL中进行内存排序操作。**

另外：这个结论不仅对order by有效，对其他需要排序的操作也一样有效；比如group by 、union 、distinct等。

#### 面试官再追问：where条件的字段不在索引中，只有order by的字段在索引中，会用到索引吗？会利用索引排序吗？ ####

从上一个问题我们其实已经分析清楚了。答：**where条件的字段不在索引中，只有order by的字段在索引中，无论select的内容，都不会走索引，也不会利用索引的有序性，并且会在内存中进行排序操作**。

##### 验证如下： #####

**表结构和索引如下：**

*  ![在这里插入图片描述][ae8879bb8db2421c8d0c48074a0d9c1e.png]
 *  user2表只有一个非主键索引的索引 – **普通索引：idx\_type(`type`)**

**SQL如下：**

explain select id, type from user2 where age > 1 and gender >=0 order by type desc;
    explain select * from user2 where age > 1 and gender >=0 order by type desc;

**执行计划如下：**

两个SQL的执行计划内容是一样的：

*  **type = ALL, possible\_keys = null** ；表示查询没有利用到索引。
 *  **Extra** 中有 `Using filesort`；表示没有利用索引的有序性，直接在内容中进行了排序操作。  
    ![在这里插入图片描述][bd552a00c9164a26bd11faf2e21f4410.png]  
    ![在这里插入图片描述][6871638057394c659cb960e73e79d068.png]

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_9_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/52fec265a9d84ad1b5b41f8866fc0184.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA56eD5aS054ix5YGl6Lqr,size_9,color_FFFFFF,t_70,g_se,x_16
[9fb252a8035a4726a6ccb959ce0908e1.png]: https://img-blog.csdnimg.cn/9fb252a8035a4726a6ccb959ce0908e1.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_14_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/2e83f61cb43941379dd1c6ae23259702.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA56eD5aS054ix5YGl6Lqr,size_14,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA56eD5aS054ix5YGl6Lqr_size_10_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/ede5962e9cbc4d28a4d5265b43cf4752.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA56eD5aS054ix5YGl6Lqr,size_10,color_FFFFFF,t_70,g_se,x_16
[fbd362a64ce24384ae02f9e9cef42d57.png]: https://img-blog.csdnimg.cn/fbd362a64ce24384ae02f9e9cef42d57.png
[f6f8bfc8be8848f1bfdd072c920ffe99.png]: https://img-blog.csdnimg.cn/f6f8bfc8be8848f1bfdd072c920ffe99.png
[7a7987830778494394ad1bde2f3c94ee.png]: https://img-blog.csdnimg.cn/7a7987830778494394ad1bde2f3c94ee.png
[178169f849f4460f8f0958d1676487ff.png]: https://img-blog.csdnimg.cn/178169f849f4460f8f0958d1676487ff.png
[ae8879bb8db2421c8d0c48074a0d9c1e.png]: https://img-blog.csdnimg.cn/ae8879bb8db2421c8d0c48074a0d9c1e.png
[bd552a00c9164a26bd11faf2e21f4410.png]: https://img-blog.csdnimg.cn/bd552a00c9164a26bd11faf2e21f4410.png
[6871638057394c659cb960e73e79d068.png]: https://img-blog.csdnimg.cn/6871638057394c659cb960e73e79d068.png