【走进Mysql之消失的数据order by + limit 数据重复 + 消失!

前言

先说下背景，最近在做业务需求，而这个需求会插入很多数据，并且这个数据拥有过期时间，需要在前端根据过期时间降序分页展示的~

再来描述下问题: 我在测试过程中发现我明明新插入的一条数据，在列表页根本没展示出来！并且在下一页中我看到了上一页已经出现过的数据

带着问题，我们先根据下面的SQL来简单复现下问题~

问题案例

我们先来创建表和数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `add_time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (1, '熊大', '2023-11-14 22:03:44');
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (2, '皮皮虾', '2023-11-14 22:03:44');
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (3, '熊二', '2023-11-14 22:03:44');
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (4, '翠花', '2023-11-13 02:03:52');
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (5, '光头强', '2023-11-13 02:03:52');
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (6, '吉吉国王', '2023-11-13 02:03:52');
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (7, '喜羊羊', '2023-11-12 16:11:00');
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (8, '沸羊羊', '2023-11-14 22:03:44');
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (9, '美羊羊', '2023-11-14 22:03:44');
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (10, '懒洋洋', '2023-11-11 05:03:52');
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (11, '炭治郎', '2023-11-13 02:03:52');
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (12, '五条悟', '2023-11-13 02:03:52');
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (13, '小鸟游六花', '2023-11-13 02:03:52');
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (14, '勇太', '2023-11-13 02:03:52');
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (15, 'The shy', '2023-11-14 22:03:44');
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (16, '虎杖悠仁', '2023-11-14 22:03:44');
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (17, '猪猪', '2023-11-14 22:03:44');
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (18, '小明', '2023-11-14 22:03:44');
INSERT INTO `student` (`id`, `name`, `add_time`) VALUES (19, '小刚', '2023-11-14 22:03:44');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

执行SQl后数据如上，我们假设现在需要根据过期时间降序并且分页，每页4条进行展示，那么第1、2页SQL如下:

-- 第一页
SELECT * from student ORDER BY add_time desc LIMIT 0, 4;

-- 第二页
SELECT * from student ORDER BY add_time desc LIMIT 4, 4;

我们分别执行这两条语句，结果如下：

我们发现熊大这条数据，在第一页和第二页中都出现了！

又例如下面这条SQl，我们每页6条，查询第一、二页，一共根据add_time降序查询前12条数据，但根据我下面的对比图发现，虎杖悠仁这条数据在第一二页中都没出现？？？

原因

上面自己定义了一下数据，简单复现了我遇到的问题~

那么接下来该查找原因了，sql语法啥的感觉也没啥问题，为啥会出现重复数据、丢数据的问题呢，奇奇怪怪的~

网上搜了一大堆，大致了解了是什么情况，那么我也来简单总结下吧

首先，参考Mysql官方文档的下面这两句话

• If an index is not used for ORDER BY but a LIMIT clause is also present, the optimizer may be able to avoid using a merge file and sort the rows in memory using an in-memory filesort operation.

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

如官方文档所说，结合我给出的测试数据，当多行具有相同值时，mysql可以以任意顺序返回，这样一来数据就可能重复出现甚至丢失，也符合我们遇到的问题。

并且文档还说如果order by的字段没有索引，并且存在limit语句时，优化器会使用filesort来进行排序

我们通过explain查看执行计划，确实如官方所说，使用到了filesort，代表需要排序

MySQL会有内存排序和外部排序两种：

• 如果待排序的数据量小于sort buffer size，排序就在内存中完成(快速排序)；
• 如果待排序的数据量大于sort buffer size，就使用临时文件进行外部排序(归并排序);

sort buffer size默认256k

无论是快排还是归并，都是根据全部结果进行排序后，再根据limit取数，这是不会影响数据结果的（重复数据、丢失数据）

但是，MySQL 5.6 版本针对 ORDER BY LIMIT做了个小优化（排序字段无索引，且列值不唯一时）：优化器在遇到 ORDER BY LIMIT语句的时候，使用了priority queue（优先队列）。

关键就在于这个优先队列，并不是一下子对全部结果排序，然后取出需要的

结合order by add_time desc limit 0, 5来说，是会维护一个大小为5的大顶堆，当出现大于等于堆顶的数据时，该数据会加入到堆中，同时堆需要进行排序调整，淘汰堆中最小的那个数据

大顶堆：每个结点的值都大于或等于其左右孩子结点的值
小顶堆：每个结点的值都小于或等于其左右孩子结点的值

当add_time都一样时，淘汰的数据就不可控了，因为在第一二页我们可能就会看到重复的数据，甚至丢失数据

解决

除了使用add_time外，我们在增加使用主键id来进行降序，这样一来就能保证唯一性和有序性，从而避免出现重复数据和丢失数据的情况。

总结

通过这次遇到的问题，也了解到了Mysql一些新知识点，还是很不错滴~

正所谓干的越多，错的越多，hh，但最后学到的也更多！！！

我是 Code皮皮虾 ，会在以后的日子里跟大家一起学习，一起进步！
觉得文章不错的话，可以在 掘金 关注我，这样就不会错过很多技术干货啦~

参考

• 深入浅出 MySQL 优先队列(你一定会踩到的order by limit 问题)
• [MySQL]order by limit排序字段重复值导致数据重复、丢失 - 唐宋
• MySQL :: MySQL 5.7 Reference Manual :: 8.2.1.17 LIMIT Query Optimization