溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
一天，小明很著急地在通訊工具上說：這邊線上出現了個奇怪的問題，麻煩 DBA 大大鑒定下，執行語句 select xx from table_name wheere xxx order by 字段A limit offset;，
表數據總共 48 條，分頁數量正常，但出現了結果混雜的情況，第一頁的數據出現在了第二頁上；如果 order by 字段B 就不會出現這個現象，怎么會這樣呢！
其實，這個問題很簡單，如果你有仔細閱讀官檔的話。~^_^~
我們先來看看官檔是怎么說的：
If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.
One factor that affects the execution plan is LIMIT, so an ORDER BY query with and without LIMIT may return rows in different orders.
本次實驗使用社區版 MySQL 5.6.26（因為小明出現問題的環境就是這個版本O(∩_∩)O~），下面先創建實驗環境和初始化測試數據：
root@localhost [(none)]>select @@version; +------------+ | @@version | +------------+ | 5.6.26-log | +------------+1 row in set (0.00 sec) root@localhost [(none)]>show variables like "sql_mode"; +---------------+------------------------+ | Variable_name | Value | +---------------+------------------------+ | sql_mode | NO_ENGINE_SUBSTITUTION | +---------------+------------------------+1 row in set (0.00 sec) root@localhost [(none)]>create database glon_ho; Query OK, 1 row affected (0.04 sec) root@localhost [(none)]>use glon_ho Database changed root@localhost [glon_ho]>create table glon( -> id int not null auto_increment primary key, -> name varchar(20) not null, -> create_time datetime not null, -> age tinyint unsigned default 18 -> ); Query OK, 0 rows affected (0.01 sec) root@localhost [glon_ho]>INSERT INTO `glon` VALUES (1, 'Eason Chan', '2017-05-02 08:10:10', 19),(2, 'Glon Ho', '2017-05-03 12:10:10', 18),(3, '趙敏', '2017-05-03 14:10:10', 17),(4, 'Jacky Cheung', '2017-05-02 14:00:00', 22),(5, '周芷若', '2017-05-02 14:00:00', 16),(6, 'Andy Lau', '2017-05-02 14:00:00', 50),(7, '至尊寶', '2017-05-02 14:00:00', 20),(8, '劉三姐', '2017-05-02 14:00:00', 19); Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0root@localhost [glon_ho]>select * from glon; +----+--------------+---------------------+------+ | id | name | create_time | age | +----+--------------+---------------------+------+ | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 3 | 趙敏 | 2017-05-03 14:10:10 | 17 | | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | | 5 | 周芷若 | 2017-05-02 14:00:00 | 16 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | | 7 | 至尊寶 | 2017-05-02 14:00:00 | 20 | | 8 | 劉三姐 | 2017-05-02 14:00:00 | 19 | +----+--------------+---------------------+------+8 rows in set (0.00 sec)
這里創建了一個 glon 表，字段有自增 id, 姓名 name, 年齡 age, 及用戶注冊時間 create_time。
接著來復現問題：
根據用戶注冊時間 create_time 來排序：
root@localhost [glon_ho]>select * from glon ORDER BY create_time limit 0, 4; +----+--------------+---------------------+------+ | id | name | create_time | age | +----+--------------+---------------------+------+ | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | | 8 | 劉三姐 | 2017-05-02 14:00:00 | 19 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | +----+--------------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon ORDER BY create_time limit 4, 4; +----+-----------+---------------------+------+ | id | name | create_time | age | +----+-----------+---------------------+------+ | 7 | 至尊寶 | 2017-05-02 14:00:00 | 20 | | 8 | 劉三姐 | 2017-05-02 14:00:00 | 19 | | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 3 | 趙敏 | 2017-05-03 14:10:10 | 17 | +----+-----------+---------------------+------+4 rows in set (0.00 sec)
可以看到兩次查詢結果中都出現了 id 為 8 的劉三姐，從上面初始化數據來看，總共有 8 條數據，現在不但分頁出現重復數據，還丟了一條！
問題確實重現了，不過先不急，我們再來試多幾組其他的排序方式。
create_time 和 age 組合排序
root@localhost [glon_ho]>select * from glon ORDER BY create_time,age limit 0, 4; +----+------------+---------------------+------+ | id | name | create_time | age | +----+------------+---------------------+------+ | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | | 5 | 周芷若 | 2017-05-02 14:00:00 | 16 | | 8 | 劉三姐 | 2017-05-02 14:00:00 | 19 | | 7 | 至尊寶 | 2017-05-02 14:00:00 | 20 | +----+------------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon ORDER BY create_time,age limit 4, 4; +----+--------------+---------------------+------+ | id | name | create_time | age | +----+--------------+---------------------+------+ | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 3 | 趙敏 | 2017-05-03 14:10:10 | 17 | +----+--------------+---------------------+------+4 rows in set (0.00 sec)
create_time 和 id 組合排序
root@localhost [glon_ho]>select * from glon ORDER BY create_time,id limit 0, 4; +----+--------------+---------------------+------+ | id | name | create_time | age | +----+--------------+---------------------+------+ | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | | 5 | 周芷若 | 2017-05-02 14:00:00 | 16 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | +----+--------------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon ORDER BY create_time,id limit 4, 4; +----+-----------+---------------------+------+ | id | name | create_time | age | +----+-----------+---------------------+------+ | 7 | 至尊寶 | 2017-05-02 14:00:00 | 20 | | 8 | 劉三姐 | 2017-05-02 14:00:00 | 19 | | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 3 | 趙敏 | 2017-05-03 14:10:10 | 17 | +----+-----------+---------------------+------+4 rows in set (0.00 sec)
主鍵 id 排序
root@localhost [glon_ho]>select * from glon ORDER BY id limit 0, 4; +----+--------------+---------------------+------+ | id | name | create_time | age | +----+--------------+---------------------+------+ | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 3 | 趙敏 | 2017-05-03 14:10:10 | 17 | | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | +----+--------------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon ORDER BY id limit 4, 4; +----+-----------+---------------------+------+ | id | name | create_time | age | +----+-----------+---------------------+------+ | 5 | 周芷若 | 2017-05-02 14:00:00 | 16 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | | 7 | 至尊寶 | 2017-05-02 14:00:00 | 20 | | 8 | 劉三姐 | 2017-05-02 14:00:00 | 19 | +----+-----------+---------------------+------+4 rows in set (0.00 sec)
看到，后面的幾組排序方式都沒有再出現問題了，結合官檔，我們知道 order by 排序的時候，如果排序字段中有多行相同的列值，則排序結果是不確定的。所以后面的幾組組合形式的排序或者是主鍵 id 的排序，因為唯一性高，所以排序是確定的，不會出現結果混亂的問題。
那是不是可以就此結束了呢，no way， 我們再來看下面的實驗，繼續鞏固一下：
根據年齡 age 來排序：
root@localhost [glon_ho]>select * from glon ORDER BY age limit 0, 4; +----+------------+---------------------+------+ | id | name | create_time | age | +----+------------+---------------------+------+ | 5 | 周芷若 | 2017-05-02 14:00:00 | 16 | | 3 | 趙敏 | 2017-05-03 14:10:10 | 17 | | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | +----+------------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon ORDER BY age limit 4, 4; +----+--------------+---------------------+------+ | id | name | create_time | age | +----+--------------+---------------------+------+ | 8 | 劉三姐 | 2017-05-02 14:00:00 | 19 | | 7 | 至尊寶 | 2017-05-02 14:00:00 | 20 | | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | +----+--------------+---------------------+------+4 rows in set (0.00 sec)
咦，這個排序也只是根據一個字段 age 來排序，怎么就沒有出問題呢？不急，還有招：
root@localhost [glon_ho]>insert into glon values (9,'喬峰','2017-05-03 13:10:10',22),(10,'段譽','2017-05-03 15:10:10',19),(11,'郭靖','2017-05-03 17:10:10',20),(12,'黃蓉','2017-05-03 08:10:10',19); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 root@localhost [glon_ho]>select * from glon; +----+--------------+---------------------+------+ | id | name | create_time | age | +----+--------------+---------------------+------+ | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 3 | 趙敏 | 2017-05-03 14:10:10 | 17 | | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | | 5 | 周芷若 | 2017-05-02 14:00:00 | 16 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | | 7 | 至尊寶 | 2017-05-02 14:00:00 | 20 | | 8 | 劉三姐 | 2017-05-02 14:00:00 | 19 | | 9 | 喬峰 | 2017-05-03 13:10:10 | 22 | | 10 | 段譽 | 2017-05-03 15:10:10 | 19 | | 11 | 郭靖 | 2017-05-03 17:10:10 | 20 | | 12 | 黃蓉 | 2017-05-03 08:10:10 | 19 | +----+--------------+---------------------+------+12 rows in set (0.00 sec)
我又給 glon 表新增了幾條數據，然后再來看看：
root@localhost [glon_ho]>select * from glon ORDER BY create_time limit 0, 4; +----+--------------+---------------------+------+ | id | name | create_time | age | +----+--------------+---------------------+------+ | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | | 5 | 周芷若 | 2017-05-02 14:00:00 | 16 | +----+--------------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon ORDER BY create_time limit 4, 4; +----+-----------+---------------------+------+ | id | name | create_time | age | +----+-----------+---------------------+------+ | 7 | 至尊寶 | 2017-05-02 14:00:00 | 20 | | 8 | 劉三姐 | 2017-05-02 14:00:00 | 19 | | 12 | 黃蓉 | 2017-05-03 08:10:10 | 19 | | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | +----+-----------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon ORDER BY create_time limit 8, 4; +----+--------+---------------------+------+ | id | name | create_time | age | +----+--------+---------------------+------+ | 9 | 喬峰 | 2017-05-03 13:10:10 | 22 | | 3 | 趙敏 | 2017-05-03 14:10:10 | 17 | | 10 | 段譽 | 2017-05-03 15:10:10 | 19 | | 11 | 郭靖 | 2017-05-03 17:10:10 | 20 | +----+--------+---------------------+------+4 rows in set (0.00 sec)
根據 create_time 排序，沒有問題了，再來：
root@localhost [glon_ho]>select * from glon ORDER BY age limit 0, 4; +----+------------+---------------------+------+ | id | name | create_time | age | +----+------------+---------------------+------+ | 5 | 周芷若 | 2017-05-02 14:00:00 | 16 | | 3 | 趙敏 | 2017-05-03 14:10:10 | 17 | | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | +----+------------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon ORDER BY age limit 4, 4; +----+-----------+---------------------+------+ | id | name | create_time | age | +----+-----------+---------------------+------+ | 12 | 黃蓉 | 2017-05-03 08:10:10 | 19 | | 10 | 段譽 | 2017-05-03 15:10:10 | 19 | | 8 | 劉三姐 | 2017-05-02 14:00:00 | 19 | | 7 | 至尊寶 | 2017-05-02 14:00:00 | 20 | +----+-----------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon ORDER BY age limit 8, 4; +----+--------------+---------------------+------+ | id | name | create_time | age | +----+--------------+---------------------+------+ | 7 | 至尊寶 | 2017-05-02 14:00:00 | 20 | | 9 | 喬峰 | 2017-05-03 13:10:10 | 22 | | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | +----+--------------+---------------------+------+4 rows in set (0.00 sec)
可以看到根據年齡 age 排序，問題出現了。
然后在看看組合的排序：
root@localhost [glon_ho]>select * from glon ORDER BY create_time,id limit 0, 4; +----+--------------+---------------------+------+ | id | name | create_time | age | +----+--------------+---------------------+------+ | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | | 5 | 周芷若 | 2017-05-02 14:00:00 | 16 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | +----+--------------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon ORDER BY create_time,id limit 4, 4; +----+-----------+---------------------+------+ | id | name | create_time | age | +----+-----------+---------------------+------+ | 7 | 至尊寶 | 2017-05-02 14:00:00 | 20 | | 8 | 劉三姐 | 2017-05-02 14:00:00 | 19 | | 12 | 黃蓉 | 2017-05-03 08:10:10 | 19 | | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | +----+-----------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon ORDER BY create_time,id limit 8, 4; +----+--------+---------------------+------+ | id | name | create_time | age | +----+--------+---------------------+------+ | 9 | 喬峰 | 2017-05-03 13:10:10 | 22 | | 3 | 趙敏 | 2017-05-03 14:10:10 | 17 | | 10 | 段譽 | 2017-05-03 15:10:10 | 19 | | 11 | 郭靖 | 2017-05-03 17:10:10 | 20 | +----+--------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon ORDER BY create_time,age limit 0, 4; +----+------------+---------------------+------+ | id | name | create_time | age | +----+------------+---------------------+------+ | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | | 5 | 周芷若 | 2017-05-02 14:00:00 | 16 | | 8 | 劉三姐 | 2017-05-02 14:00:00 | 19 | | 7 | 至尊寶 | 2017-05-02 14:00:00 | 20 | +----+------------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon ORDER BY create_time,age limit 4, 4; +----+--------------+---------------------+------+ | id | name | create_time | age | +----+--------------+---------------------+------+ | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | | 12 | 黃蓉 | 2017-05-03 08:10:10 | 19 | | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | +----+--------------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon ORDER BY create_time,age limit 8, 4; +----+--------+---------------------+------+ | id | name | create_time | age | +----+--------+---------------------+------+ | 9 | 喬峰 | 2017-05-03 13:10:10 | 22 | | 3 | 趙敏 | 2017-05-03 14:10:10 | 17 | | 10 | 段譽 | 2017-05-03 15:10:10 | 19 | | 11 | 郭靖 | 2017-05-03 17:10:10 | 20 | +----+--------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon ORDER BY age,id limit 0, 4; +----+------------+---------------------+------+ | id | name | create_time | age | +----+------------+---------------------+------+ | 5 | 周芷若 | 2017-05-02 14:00:00 | 16 | | 3 | 趙敏 | 2017-05-03 14:10:10 | 17 | | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | +----+------------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon ORDER BY age,id limit 4, 4; +----+-----------+---------------------+------+ | id | name | create_time | age | +----+-----------+---------------------+------+ | 8 | 劉三姐 | 2017-05-02 14:00:00 | 19 | | 10 | 段譽 | 2017-05-03 15:10:10 | 19 | | 12 | 黃蓉 | 2017-05-03 08:10:10 | 19 | | 7 | 至尊寶 | 2017-05-02 14:00:00 | 20 | +----+-----------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon ORDER BY age,id limit 8, 4; +----+--------------+---------------------+------+ | id | name | create_time | age | +----+--------------+---------------------+------+ | 11 | 郭靖 | 2017-05-03 17:10:10 | 20 | | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | | 9 | 喬峰 | 2017-05-03 13:10:10 | 22 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | +----+--------------+---------------------+------+4 rows in set (0.00 sec)
既然排序不定，那么給排序字段加上索引會不會有用呢？
root@localhost [glon_ho]>alter table glon add index ix_age(age); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0root@localhost [glon_ho]>show create table glon\G *************************** 1. row *************************** Table: glon Create Table: CREATE TABLE `glon` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `create_time` datetime NOT NULL, `age` tinyint(3) unsigned DEFAULT '18', PRIMARY KEY (`id`), KEY `ix_age` (`age`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf81 row in set (0.00 sec) root@localhost [glon_ho]>select * from glon order by age limit 0,4; +----+------------+---------------------+------+ | id | name | create_time | age | +----+------------+---------------------+------+ | 5 | 周芷若 | 2017-05-02 14:00:00 | 16 | | 3 | 趙敏 | 2017-05-03 14:10:10 | 17 | | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | +----+------------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon order by age limit 4,4; +----+-----------+---------------------+------+ | id | name | create_time | age | +----+-----------+---------------------+------+ | 12 | 黃蓉 | 2017-05-03 08:10:10 | 19 | | 10 | 段譽 | 2017-05-03 15:10:10 | 19 | | 8 | 劉三姐 | 2017-05-02 14:00:00 | 19 | | 7 | 至尊寶 | 2017-05-02 14:00:00 | 20 | +----+-----------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from glon order by age limit 8,4; +----+--------------+---------------------+------+ | id | name | create_time | age | +----+--------------+---------------------+------+ | 7 | 至尊寶 | 2017-05-02 14:00:00 | 20 | | 9 | 喬峰 | 2017-05-03 13:10:10 | 22 | | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | +----+--------------+---------------------+------+4 rows in set (0.00 sec)
也可以不在 order by 后面加多一列增加唯一性，可以改寫成下面的形式：
root@localhost [glon_ho]>select * from (select distinct g.* from glon g order by age) t limit 0,4; +----+------------+---------------------+------+ | id | name | create_time | age | +----+------------+---------------------+------+ | 5 | 周芷若 | 2017-05-02 14:00:00 | 16 | | 3 | 趙敏 | 2017-05-03 14:10:10 | 17 | | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | +----+------------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from (select distinct g.* from glon g order by age) t limit 4,4; +----+-----------+---------------------+------+ | id | name | create_time | age | +----+-----------+---------------------+------+ | 10 | 段譽 | 2017-05-03 15:10:10 | 19 | | 8 | 劉三姐 | 2017-05-02 14:00:00 | 19 | | 12 | 黃蓉 | 2017-05-03 08:10:10 | 19 | | 11 | 郭靖 | 2017-05-03 17:10:10 | 20 | +----+-----------+---------------------+------+4 rows in set (0.00 sec) root@localhost [glon_ho]>select * from (select distinct g.* from glon g order by age) t limit 8,4; +----+--------------+---------------------+------+ | id | name | create_time | age | +----+--------------+---------------------+------+ | 7 | 至尊寶 | 2017-05-02 14:00:00 | 20 | | 9 | 喬峰 | 2017-05-03 13:10:10 | 22 | | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | +----+--------------+---------------------+------+4 rows in set (0.00 sec)
總之，如果發生了，最簡單的方法就是在排序列（如 create time）上加索引，然后在 order by 上明示 primary key，這個問題就非常圓滿的解決了。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
