【MySQL】order by 结果不准确的问题及解决
发表于:2024-09-21 作者:千家信息网编辑
千家信息网最后更新 2024年09月21日,一 介绍相信作为DBA 在和开发打交道的过程中,经常会遇到分页查询 order by 排序这样的需求。本文源于生产过程中的案例,5.6版本的数据库使用limit和order by 一个非唯一字段时,结
千家信息网最后更新 2024年09月21日【MySQL】order by 结果不准确的问题及解决一 介绍
相信作为DBA 在和开发打交道的过程中,经常会遇到分页查询 order by 排序这样的需求。本文源于生产过程中的案例,5.6版本的数据库使用limit和order by 一个非唯一字段时,结果集并不总是确定的.已经确定为bug,详见:MySQL 官方的bug
提醒读者朋友注意。
二 分析
环境准备
初始化数据
为a字段加上索引
从上面的测试来看对于一个非唯一字段 无论是否含有索引,结果集都是不确定的。
三 解决方法
1 业务属性确保 a 字段不能唯一,则需要针对排序结果再加上 一个唯一字段的排序 比如id
2 是否可以去掉不必要的order by,这个是由业务逻辑决定的。
四 总结
DBA在和开发沟通/培训过程中要注意这一点 基于非唯一字段的排序 结果集是不确定的。如果业务逻辑对分页或者order by结果集有比较高的严格要求 ,请记得利用唯一键排序。
相信作为DBA 在和开发打交道的过程中,经常会遇到分页查询 order by 排序这样的需求。本文源于生产过程中的案例,5.6版本的数据库使用limit和order by 一个非唯一字段时,结果集并不总是确定的.已经确定为bug,详见:MySQL 官方的bug
提醒读者朋友注意。
二 分析
环境准备
- CREATE TABLE `tb1` (
- `id` bigint(20) NOT NULL AUTO_INCREMENT,
- `a` decimal(19,2) NOT NULL,
- `acid` bigint(20) NOT NULL,
- `prid` bigint(20) NOT NULL,
- PRIMARY KEY (`id`),
- KEY `idx_prid` (`prid`),
- KEY `idx_acid` (`acid`)
- ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8
初始化数据
- INSERT INTO `tb1` (`id`, `a`, `acid`, `prid`)
- VALUES (1,2.00,3,2),(2,3.00,3,2),(3,4.00,2,3),(4,5.00,2,3),(5,6.00,2,3),(6,8.00,2,3),(7,10.00,2,3),(8,12.00,2,3),(9,16.00,2,3),(10,20.00,2,3),(11,6.00,2,4),(12,8.00,2,4),(13,10.00,2,4),(14,12.00,2,4),(15,5.00,2,2),(16,6.00,2,2);
- mysql> select * from tb1 order by a desc limit 4;
- +----+-------+------+------+
- | id | a | acid | prid |
- +----+-------+------+------+
- | 10 | 20.00 | 2 | 3 |
- | 9 | 16.00 | 2 | 3 |
- | 14 | 12.00 | 2 | 4 |
- | 8 | 12.00 | 2 | 3 |
- +----+-------+------+------+
- 4 rows in set (0.00 sec)
- mysql> select * from tb1 order by a desc limit 3;
- +----+-------+------+------+
- | id | a | acid | prid |
- +----+-------+------+------+
- | 10 | 20.00 | 2 | 3 |
- | 9 | 16.00 | 2 | 3 |
- | 8 | 12.00 | 2 | 3 |
- +----+-------+------+------+
- 3 rows in set (0.00 sec)
为a字段加上索引
- mysql> alter table tb1 add key ind_tb1a(a);
- Query OK, 0 rows affected (0.00 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- mysql> select * from tb1 order by a desc limit 3;
- +----+-------+------+------+
- | id | a | acid | prid |
- +----+-------+------+------+
- | 10 | 20.00 | 2 | 3 |
- | 9 | 16.00 | 2 | 3 |
- | 8 | 12.00 | 2 | 3 |
- +----+-------+------+------+
- 3 rows in set (0.00 sec)
- mysql> select * from tb1 order by a desc limit 4;
- +----+-------+------+------+
- | id | a | acid | prid |
- +----+-------+------+------+
- | 10 | 20.00 | 2 | 3 |
- | 9 | 16.00 | 2 | 3 |
- | 14 | 12.00 | 2 | 4 |
- | 8 | 12.00 | 2 | 3 |
- +----+-------+------+------+
- 4 rows in set (0.00 sec)
从上面的测试来看对于一个非唯一字段 无论是否含有索引,结果集都是不确定的。
三 解决方法
1 业务属性确保 a 字段不能唯一,则需要针对排序结果再加上 一个唯一字段的排序 比如id
- mysql> select * from tb1 order by a desc ,id desc limit 4;
- +----+-------+------+------+
- | id | a | acid | prid |
- +----+-------+------+------+
- | 10 | 20.00 | 2 | 3 |
- | 9 | 16.00 | 2 | 3 |
- | 14 | 12.00 | 2 | 4 |
- | 8 | 12.00 | 2 | 3 |
- +----+-------+------+------+
- 4 rows in set (0.00 sec)
- mysql> select * from tb1 order by a desc ,id desc limit 3;
- +----+-------+------+------+
- | id | a | acid | prid |
- +----+-------+------+------+
- | 10 | 20.00 | 2 | 3 |
- | 9 | 16.00 | 2 | 3 |
- | 14 | 12.00 | 2 | 4 |
- +----+-------+------+------+
- 3 rows in set (0.00 sec)
2 是否可以去掉不必要的order by,这个是由业务逻辑决定的。
四 总结
DBA在和开发沟通/培训过程中要注意这一点 基于非唯一字段的排序 结果集是不确定的。如果业务逻辑对分页或者order by结果集有比较高的严格要求 ,请记得利用唯一键排序。
结果
字段
排序
索引
业务
过程
非唯
数据
逻辑
需求
开发
问题
一致
三个
不确定性
两个
官方
属性
常会
数据库
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
松江区网络技术应用
甜糖云服务器部署
2018年网络安全周主题是
网络技术人员网络犯罪辩护
微信聊天网络安全图片
软件开发有哪些大学
达芬奇怎么设置数据库保存位置
志高虚拟服务器
什么是采用综合的网络技术
武汉商城app开发软件开发
菏泽网络技术排山东省第几
wow为什么会突然退出服务器
怎么把apache安装到服务器
软件开发报价资料
2019q绑数据库
信息技术和网络技术差别
关于部队网络安全的演讲稿
滁州市网络安全协会
辽宁机械软件开发质量保障
英雄联盟战绩显示到服务器
腾讯云服务器ip地址是哪个
上海技术管理软件开发
网友热议网络安全法
网络安全的重要性科普视频
日本制药通用名数据库
山东淘盟网络技术
梅林搭建web服务器
go语言链接数据库增删改查
计算机网络技术职业特点有哪些
安卓软件开发转行苹果