Loading... ## 问题描述 在查询每日任务质检情况报表时,发现当查询时间 `create_time`为7天时,出现了数据重复以及数据丢失的情况。sql如下: ```sql_more # 查询第3页,一页20。此时原本在第2页的数据在第3页重复出现,且原本应在第3页的数据丢失。 select id,... from table where create_time between a and b order by create_time desc limit 40,20 ``` 最后定位出来是因为MySQL的 `order by`排序规则导致的,当排序字段 `create_time`存在大量重复时走了文件排序中的堆排序。 <!--more--> ## Order By 排序 * **`索引排序`**:`index`排序,使用表索引,也就是说排序字段必须在索引中。 * **`文件排序`**:`filesort`排序,当无法走索引排序时就走该排序,分为**快速排序**和**堆排序**。 可以通过 `explain`中的 `extra`判断: ```sql_more # Extra:Using where; Using filesort EXPLAIN SELECT * from quality_testing_task where create_time BETWEEN 0 and 10 ORDER BY create_time desc limit 40,20; # Extra:Using where; EXPLAIN SELECT * from quality_testing_task where create_time BETWEEN 0 and 10 ORDER BY id desc limit 40,20; ``` ### 索引排序-场景 1. `ordey by id`:只要是 `order by id`一定走索引排序,多个排序字段不算。 2. `where + order by`:`where`带的条件字段和排序字段必须都在索引中。 3. `order by`:排序字段必须都在索引中。 ### 文件排序-场景 除了上述索引排序的场景,剩下的就是文件排序。主要是区分走了快排还是堆排: 1. 没有 `limit`时,走快排。 2. 有 `limit`时,根据 `check_if_pq_applicable`函数判定,数据量小内存放得下就走堆排,数据量大就走快排。 MySQL中认为快排是堆排的三倍速率,因为堆排是通过优先队列将数据全部排序完返回,适合少量数据;而我们只需要排序需要的数据就行(快排选择中心值,把需要的那部分数据排序,不用全走完) ## 解决方案 快排和堆排是没办法保证重复值的顺序的,而所有走的是B+树聚簇索引的顺序是可以保证的,可以在排序字段后面再加一个主键排序,这样就能保证了唯一性。 原先以为InnoDb对于重复值会默认加上id排序(正序or逆序),所以一开始没往这方面想,但显然是我想太多 ```sql select id,... from table where create_time between a and b order by create_time desc,id desc limit 40,20 ``` 注意不能写成 `order by create_time,id desc`否则会默认拆分成 `order by create_time asc,id desc`,在MP的 `orderByDesc()`中同样需要分两列写 --- 参考链接: 1.[MySQL order by limit排序字段有重复值导致排序随机的情况](https://www.jianshu.com/p/458dc8fbf75c) 2.[LIMIT Query Optimization](https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html) Last modification:August 22, 2022 © Allow specification reprint Like 0 喵ฅฅ