【MySQL】order by 結果不準確的問題及解決
一 介紹
相信作為DBA 在和開發打交道的過程中,經常會遇到分頁查詢 order by 排序這樣的需求。本文源於生產過程中的案例,5.6版本的資料庫使用limit和order by 一個非唯一欄位時,結果集並不總是確定的.已經確定為bug,詳見:
提醒讀者朋友注意。
二 分析
環境準備
注意欄位a 上面是沒有索引的。
初始化資料
執行兩個 根據非索引欄位且有重複值的 order by 排序
得到id 為10, 9, 14, 8 的結果集
得到id 為10 9 8 的結果集
為a欄位加上索引
得到id 為10 9 8 的結果集
得到id 為10, 9, 14, 8 的結果集
從上面的測試來看對於一個非唯一欄位 無論是否含有索引,結果集都是不確定的。
三 解決方法
1 業務屬性確保 a 欄位不能唯一,則需要針對排序結果再加上 一個唯一欄位的排序 比如id
使用order by id/unique_key 排序之後,前三個結果集是一致的10,9,14 。 結果集滿足我們的需求。從而解決不確定性帶來的問題。
2 是否可以去掉不必要的order by,這個是由業務邏輯決定的。
四 總結
DBA在和開發溝通/培訓過程中要注意這一點 基於非唯一欄位的排序 結果集是不確定的。如果業務邏輯對分頁或者order by結果集有比較高的嚴格要求 ,請記得利用唯一鍵排序。
相信作為DBA 在和開發打交道的過程中,經常會遇到分頁查詢 order by 排序這樣的需求。本文源於生產過程中的案例,5.6版本的資料庫使用limit和order by 一個非唯一欄位時,結果集並不總是確定的.已經確定為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結果集有比較高的嚴格要求 ,請記得利用唯一鍵排序。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30221425/viewspace-2133112/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL order by 排序結果不正確MySql排序
- MySQL JDBC row_count()數字不準確的問題MySqlJDBC
- 自定義key解決zabbix埠監聽取值不準確的問題
- Elasticsearch聚合學習之五:排序結果不準的問題分析Elasticsearch排序
- Es資料彙總不準確的問題
- ETL過程中資料精度不準確問題
- 【MYSQL 基準測試結果】MySql
- 有趣的ES:解決es返回結果數量限制問題
- 解決 Python 指令碼無法生成結果的問題Python指令碼
- Mybatis動態傳入order by 引數的時候不生效的問題解決方案MyBatis
- crontab不執行mysql的指令碼問題的解決!MySql指令碼
- iOS --NSDecimalNumber 處理計算精度不準確問題iOSDecimal
- mysql order by 和 group by 順序問題MySql
- C# Thread.Sleep 不精準的問題以及解決方案C#thread
- Oracle學習遇到的問題收集及解決 - 不斷更新Oracle
- Oracle 錯誤總結及問題解決 ORAOracle
- 安裝mysql資料庫及問題解決方法MySql資料庫
- docker下mysql連線數修改後不生效問題的解決DockerMySql
- MySQL 在併發場景下的問題及解決思路MySql
- MySQL在併發場景下的問題及解決思路MySql
- 安裝DBD:MySql模組遇到的問題及解決辦法MySql
- MySQL的EXPLAIN命令結果詳解MySqlAI
- MySQL 與 Elasticsearch 資料不對稱問題解決辦法MySqlElasticsearch
- Mybatis plus 一對多關聯查詢分頁不準確的問題MyBatis
- 幽默:程式設計中困難的不是解決問題,而是確定要解決的問題 - Paul程式設計
- 關於mysql查詢字符集不匹配問題的解決方法MySql
- 回收站不工作的問題解決
- MySQL 中文 like 問題解決MySql
- mysql大小寫問題解決MySql
- 常見問題及解決
- Mysql安裝過程中遇到的問題及解決辦法MySql
- 路徑偏移定位不準確?華為定位SuperGNSS技術幫您解決這一難題!
- ORDER對查詢結果進行排序排序
- 奇怪的登入問題及解決
- Oracle Database 12c查詢最佳化器的缺陷-外連線導致結果不準確OracleDatabase
- input 與 button 的問題 (空隙/不等高/對不齊)及 解決辦法
- Windows 安裝 MySQL 5.7.20 教程(及常見問題解決)WindowsMySql
- MySQL並行複製延時時間不準確MySql並行