MySQL 中 order by 與 limit 同時使用導致亂序
最近在專案中遇到SQL輸出的分頁結果異常，出現大量重複數據的問題。當時花了不少時間除錯，也誤打誤撞地解決了問題。但事後仍對問題發生的原因感到困惑，在查閱些許資料和文件後對此有初步的了解，最終決定撰寫這篇文章做踩雷紀錄。
原始的需求是對資料排序order by後再做分頁limit，其MySQL語句是
SELECT * FROM `table_name` ORDER BY `column` LIMIT m,n而這看似平淡無奇的SQL操作，卻深藏陷阱。當order by排序的欄位中有重複值，將會導致分頁結果與預期的情況不同。
為了簡單說明情境，在此引用MySQL官方文件中的範例。有張名為ratings的資料表，其內容如下圖。
最終需求是對category排序後以每頁5筆紀錄做分頁。先來看看只做排序、不做分頁情況下的輸出，如下圖。
輸出結果很正常，順序沒有問題。接著加入分頁，預期的輸出結果會是前5筆資料並且在id上看起來是1, 5, 3, 4, 6的順序，而實際輸出如下圖。
可以看到加入分頁後輸出的順序與資料全撈時輸出的順序不同（在這範例中id = 3與id = 4的資料順序被對調了）。而這樣的輸出結果是對於這條SQL語句來說是卻是合理的，因為輸出結果確實按category排序了。
也就是說，在一般直覺下對於這樣的SQL語句會認為「資料經過篩選後，會先排序好再取出指定數量的紀錄，且最終排序將會與分頁前相同」，但實際情況卻完全不一樣，輸出的結果是亂序的，甚至在之後的頁次中出現重複數據。而導致這情況的原因是MySQL從5.6版本開始會對LIMIT語句做優化，全文可以到MySQL官方文件查看，這邊節錄與此情境有直接關係的內容。
If multiple rows have identical values in the
ORDER BYcolumns, 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.
簡單翻譯一下，
假如
order by的欄位中有多條紀錄存有相同的值，MySQL 將能以任何順序輸出這些紀錄，並可能根據執行計畫以不同的順序輸出。換言之，這些紀錄將被視為無序的且輸出順序是隨機的。
從文章的內容可以得知，造成亂序的原因是category欄位裡有多筆記錄存有相同的值（id = 1, 5 一組、id = 3, 4, 6一組、id = 2, 7一組），因而導致這幾筆紀錄之間是無序的，最終輸出時將以隨機順序輸出這些紀錄。
對於這種情境，MySQL官方文件建議在order by中額外加上unique key來排序，例如大多資料表會有的id 。以ratings資料表來舉例，在最後一個排序需求category的後方加上id，如下圖。
能看到在order by加入id後，分頁部分的資料確實有按照期望的排序順序輸出結果，完美的達成需求。
希望這篇文章能讓還沒遇到過類似需求的人，在將來要處理這類需求時能省下大把時間，珍愛生命從帶有limit的order by中加入id開始。
