【MySQL】order by 結果不準確的問題及解決

發布時間：2020-08-19 07:32:41 來源：ITPUB博客閱讀：365 作者：風塵_NULL 欄目：MySQL數據庫

一介紹
相信作為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

注意字段a 上面是沒有索引的。
初始化數據

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);

執行兩個根據非索引字段且有重復值的 order by 排序

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)

得到id 為10, 9, 14, 8 的結果集

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)

得到id 為10 9 8 的結果集
為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)

得到id 為10 9 8 的結果集

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)

得到id 為10, 9, 14, 8 的結果集
從上面的測試來看對于一個非唯一字段無論是否含有索引，結果集都是不確定的。

三解決方法
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)

使用order by id/unique_key 排序之后，前三個結果集是一致的10,9,14 。結果集滿足我們的需求。從而解決不確定性帶來的問題。
2 是否可以去掉不必要的order by，這個是由業務邏輯決定的。

四總結
DBA在和開發溝通/培訓過程中要注意這一點基于非唯一字段的排序結果集是不確定的。如果業務邏輯對分頁或者order by結果集有比較高的嚴格要求，請記得利用唯一鍵排序。

向AI問一下細節

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

【MySQL】order by 結果不準確的問題及解決

猜你喜歡

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

【MySQL】order by 結果不準確的問題及解決

猜你喜歡

最新資訊

相關推薦

相關標簽