mysql中order by和limit混用出现的问题

硅谷探秘者 数据库 97 0 0

  在Mysql中我们常常用order by来进行排序,使用limit来进行分页,当需要先排序后分页时我们往往使用类似的写法select * from table order by column limt M,N。但是这种写法却隐藏着较深的使用陷阱。在排序字段有数据重复的情况下,会很容易出现排序结果与预期不一致的问题。

一、案例

mysql版本:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.35-log |
+------------+
1 row in set (0.00 sec)

表结构:

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8;

数据

INSERT INTO `test`.`user` (`id`, `name`, `create_time`) VALUES (1, '1', '2017-04-01 00:01:00');
INSERT INTO `test`.`user` (`id`, `name`, `create_time`) VALUES (2, '1', '2017-04-01 00:00:00');
INSERT INTO `test`.`user` (`id`, `name`, `create_time`) VALUES (3, '1', '2017-04-01 00:00:00');
INSERT INTO `test`.`user` (`id`, `name`, `create_time`) VALUES (4, '2', '2017-04-01 00:00:00');
INSERT INTO `test`.`user` (`id`, `name`, `create_time`) VALUES (5, '3', '2017-04-01 00:00:00');
INSERT INTO `test`.`user` (`id`, `name`, `create_time`) VALUES (6, '6', '2017-04-01 00:00:00');
INSERT INTO `test`.`user` (`id`, `name`, `create_time`) VALUES (7, '7', '2017-04-01 00:00:00');
INSERT INTO `test`.`user` (`id`, `name`, `create_time`) VALUES (8, '8', '2017-04-01 00:00:00');

  数据特点,大部分数据create_time都相同。
  现在想根据创建时间升序查询user表,并且分页查询,每页2条,那很容易写出sql为:

select * from user order by create_time limit M,2;

而实际查询过程中会发现分页会出现重复数据。
第一页数据:

mysql> select * from user order by create_time limit 2;
+----+------+---------------------+
| id | name | create_time         |
+----+------+---------------------+
|  8 | 8    | 2017-04-01 00:00:00 |
|  2 | 1    | 2017-04-01 00:00:00 |
+----+------+---------------------+
2 rows in set (0.00 sec)

第四页数据:

mysql> select * from user order by create_time limit 6,2;
+----+------+---------------------+
| id | name | create_time         |
+----+------+---------------------+
|  8 | 8    | 2017-04-01 00:00:00 |
|  1 | 1    | 2017-04-01 00:01:00 |
+----+------+---------------------+
2 rows in set (0.00 sec)

发现第一页和第四页出现了重复的数据。

二、分析

  官方文档:https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

  从官方文档得知,这其实是mysql对limit做的优化。

  官方文档里提到,如果你将Limit row_count与order by混用,mysql会找到排序的row_count行后立马返回,而不是排序整个查询结果再返回。如果是通过索引排序,会非常快;如果是文件排序,所有匹配查询的行(不带Limit的)都会被选中,被选中的大多数或者全部会被排序,直到limit要求的row_count被找到了。如果limit要求的row_count行一旦被找到,Mysql就不会排序结果集中剩余的行了。

查看执行计划:

mysql> explain select * from user order by create_time limit 6,2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)

从执行计划中可以看出,该sql用的是文件排序,排序字段没有添加任何索引。
  为什么文件排序会出现这种问题呢?主要还是取决与filesort的排序算法。filesort采用的是快速排序和堆排序,而这两种排序都是不稳定的排序。所以排序值相同的时候无法保证排序后的顺序,所以也无法保证排序后结果集的顺序。

三、解决方案

  官方也给出了解决方案,可以在ORDER BY子句中包含额外的列以使顺序具有​​确定性。例如,如果id值是唯一的,您可以通过如下排序:

mysql> select * from user order by create_time,id limit 2;
+----+------+---------------------+
| id | name | create_time         |
+----+------+---------------------+
|  2 | 1    | 2017-04-01 00:00:00 |
|  3 | 1    | 2017-04-01 00:00:00 |
+----+------+---------------------+
2 rows in set (0.00 sec)

另外也可以通过给排序字段加索引来避免文件排序。

猜你喜欢
数据结构与算法 7653 1.描述::上面有一个迷宫,灰色部分代表不能通过,白色部分代表可以通过,在从a点发,能否找到一条路径到达b点,如果能,输此路径。下面采试探法求解,采栈结构保存每一步内容(包括坐标
数据结构与算法 11487 描述给定一个int类型一维数组a[],一个int类型数值b。编写一个程序,判断数组有没有两个数(a[i],a[j])等于b,如果存在,返回两个数在a数组下表
算法基础 232 今天在项目遇到"|"分割字符串,如果直接使下面方式,不会按照我们预想分割:String[]ids="12|13|14".split("|");分割来是[1,2,|,1,3,|1,4
数据库基础 2279 mysql按照文拼音排序对于存储字段采是GBK字符集情况:orderbynameasc/desc对于存储字段采是utf-8字符集情况:orderbyconvert(nameusinggbk)asc/desc
数据库基础 1411 mysql查询判断字段是不是null在mysql,查询某字段为空时,切记不可=null,而是isnull,不为空则是isnotnull例:select*fromtablewherecolumnisnull;select*fromtablewherecolumnisnotnull;
数据库 96 有如下数据:mysqlselect*fromarea;+----+----------------+----------------+-----------+-----------+|id|proviance_code|proviance_name|city_code|city_name|+----+----------------+----------------+-----------+---
数据结构与算法 11141 数据结构-图着色描述:图m-着色判定——给定无向连通图Gm种不同颜色。这些颜色为图G各顶点着色,每个顶点着一种颜色,是否有一种着色法使G任意相邻2个顶点着不同颜色?个人感
其他 8508 在一本书上看到过子类可以重载父类方法,关于这一点有点疑惑,个人重载是发生在同一个类。网上关于这个也存在争议。先暂时作为一个记录在此下面这张图片引自《疯狂java讲义第三版》关于重写,遵循以
归档
2018年11月  12 2018年12月  33 2019年01月  28 2019年02月  28 2019年03月  32 2019年04月  27 2019年05月  33 2019年06月  6 2019年07月  12 2019年08月  12 2019年09月  21 2019年10月  8 2019年11月  15 2019年12月  25 2020年01月  9 2020年02月  5 2020年03月  16 2020年04月  4 2020年06月  1 2020年07月  7 2020年08月  13 2020年09月  9 2020年10月  5 2020年12月  3 2021年01月  1 2021年02月  5 2021年03月  7 2021年04月  4 2021年05月  4 2021年06月  1 2021年07月  7 2021年08月  2 2021年09月  8 2021年10月  9 2021年11月  16
标签
算法基础 linux 前端 c++ 数据结构 框架 数据库 计算机基础 储备知识 java基础 ASM 其他 深入理解java虚拟机 nginx git 消息中间件 搜索 maven redis docker dubbo vue 导入导出 软件使用 idea插件 协议
目录
祝愿神州十三飞行乘组平安归来