关于MySQL limit+order by分页数据重复的问题

2020-06-12 11:06:39   MySQL

最近发现一个神奇的现象,就是limit跟order by同时使用的时候,居然不同分页出现了数据重复,原因竟然是MySQL的优化

问题以及解决方法

问题SQL:

select name, gender from student where cls = 1 order by score desc limit 0, 5;

解决方案,加上自增id排序。

select name, gender from student where cls = 1 order by score desc, id asc limit 0, 5;

原因分析

MySQL是在5.6后引入堆排序来优化limit子句,重要的是它的排序采用了堆排序,学过《数据结构与算法》的同学们应该知道,堆排序是个不稳定的排序算法,什么是不稳定呢?就是说,遇到相等的值,是不会保证前后顺序的,举个例子,假设有a, b, c, d 四个同学取得了同样的分,在输入算法的时候是abcd的顺序,输出可能就变了,就不是abcd的顺序了。

再来看MySQL的默认执行顺序:

(1)     SELECT 
(2)     DISTINCT <select_list>
(3)     FROM <left_table>
(4)     <join_type> JOIN <right_table>
(5)     ON <join_condition>
(6)     WHERE <where_condition>
(7)     GROUP BY <group_by_list>
(8)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

当前面的一系列筛选条件执行完之后,将符合条件的记录select出来,再执行 order by 操作,select出来的数据是按顺序来的,但是经过了堆排序,顺序就有可能发生变化。而如果你将Limit row_countorder by混用,mysql会找到排序的row_count行后立马返回,而不是排序整个查询结果再返回。

比如,查找第一页2个学生是ab,第二页2个学生是bc,或许真正的排序(或者说全部数据完全排序完成)是adbc,而出现这个问题的原因就是b和d的成绩是相同的。在第一页查询中,找到两条分数最大的两个记录ab就够了,就不在继续排序了,直接返回,而在第二页查询中需要将abcd排序后返回后两条记录,这个过程中堆排序就可能会改变相同分数的学生位置。

解决方案

下面是官方文档的一句话:

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. For details, see The In-Memory filesort Algorithm.

就是说在ORDER BY + LIMIT的查询语句中,如果ORDER BY不能使用索引的话,优化器可能会使用in-memory sort操作。也就出现了上述结果,而给出的解决方案也很简单,就是将主键(或者具有唯一性的字段)排序引入需要排序的业务字段后,就如同开头讲的:

select name, gender from student where cls = 1 order by score desc, id asc limit 0, 5;

order by score desc,后面加id asc

viencoding.com版权所有,允许转载,但转载请注明出处和原文链接: https://viencoding.com/article/275
欢迎小伙伴们在下方评论区留言 ~ O(∩_∩)O
文章对我有帮助, 点此请博主吃包辣条 ~ O(∩_∩)O

猜你喜欢


评论

There are no comments yet.
未登录

登录后即可发表评论

登录或注册

标签

AdSense Anaconda Android API apt Auth AWS B-tree Bandwagon Blog bower brew bytes Caffe Catalina cloudcone Composer conda CoreML CPU crontab CSS csv Cuda cv2 datetime Digitalocean DNS Docker Docker-Compose export Flask FTP GET Git GitHub Gmail GoDaddy Google GTM hash Homebrew Homestead HTML http HTTPS IDEA image imagemagick imagick imgick import InnoDB ios iPhone ISO8601 iTerm2 Java JPG Keras Laravel Laravel-Admin lazyload Linux list lnmp load logs Lravel Mac Markdown matplotlib md5 mix MobileNet Mojave mongo MongoDB MySQL Namesilo Nginx npm numpy Nvidia Nvidia-Docker onevps OpenCV Openpose Outline parse PayPal PHP php-fpm PhpStorm PHP扩展 PIL Pillow pip PNG POST Protobuf PyCharm pyenv pymongo Python Python,人工智能,机器学习,VOC,xml Queue Redis requests RGB save selenium SEO Shadowsock Shadowsocks ShadowsocksR simplemde Spring Boot SQLServer ssd SSH SSL证书 SSR str Sublime sudo swap Swift Tensorflow TensorflowLite Terminal Terminator Ubuntu urllib UTC v2ray Valet Validation Validator VienBlog virtualenvs VPN VPS Vultr Web Windows WordPress Xcode yaml YUV zip zmq zsh 下载图片 云主机 云服务器 人工智能 优化 优惠码 伪原创 作弊与反作弊 免费ss账号 免费提现 切片 前端 加密 协议 博客 友链 双击事件 后台运行 后端 命令 国内镜像源 图片操作 图片转换 域名 大小写转换 姿态检测 安卓模拟器 安装 定时任务 定时执行 导出导入 小程序码 延迟加载 微信小程序 慢查询 懒加载 提现 搜索引擎 搬瓦工 搭梯子 教程 数据库 数据重复 文件上传 无法登录 日志 日期 时区 时间 时间戳 服务器 机器学习 权限 梯子 模拟浏览器 港版支付宝 爬虫 生活服务 科学上网 系统升级 索引 组件开发 编辑器 自动付款 英文伪原创 计划任务 计算机视觉 语法 远程连接 配置文件 重定向 错误异常 错误提示 队列 阿里云 香港 香港手机号
亲情非友情链接