[[375496]]  

圖片來自 Pexels

現象與問題

ORDER BY 排序后，用 LIMIT 取前幾條，發現返回的結果集的順序與預期的不一樣。

下面是我遇到的問題：

可以看到，帶 LIMIT 與不帶 LIMIT 的結果與我預期的不一樣，而且“很不可思議”，真是百思不得其解。

后來百度了一下，如果 order by 的列有相同的值時，MySQL 會隨機選取這些行，為了保證每次都返回的順序一致可以額外增加一個排序字段(比如：id)，用兩個字段來盡可能減少重復的概率。

于是，改成 order by status，id：

問題雖然是解決了，但還是看看官方文檔上怎么說的吧!

LIMIT 查詢優化

摘自“LIMIT 查詢優化”

如果你只需要結果集中的指定數量的行，那么請在查詢中使用 LIMIT 子句，而不是抓取整個結果集并丟棄剩下那些你不要的數據。

MySQL 有時會優化一個包含 LIMIT 子句并且沒有 HAVING 子句的查詢：

①MySQL 通常更愿意執行全表掃描，但是如果你用 LIMIT 只查詢幾行記錄的話，MySQL 在某些情況下可能會使用索引。

②如果你將 LIMIT row_count 子句與 ORDER BY 子句組合在一起使用的話，MySQL 會在找到排序結果的第一個 row_count 行后立即停止排序，而不是對整個結果進行排序。如果使用索引來完成排序，這將非?？?。

如果必須執行文件排序，則在找到第一個 row_count 行之前，選擇所有與查詢匹配但不包括 LIMIT 子句的行，并對其中大部分或所有行進行排序。

一旦找到第一個 row_count 之后，MySQL 不會對結果集的任何剩余部分進行排序。

這種行為的一種表現形式是，一個 ORDER BY 查詢帶或者不帶 LIMIT 可能返回行的順序是不一樣的。

③如果 LIMIT row_count 與 DISTINCT 一起使用，一旦找到 row_count 惟一的行，MySQL 就會停止。

④LIMIT 0 可以快速返回一個空的結果集，這是用來檢測一個查詢是否有效的一種很有用的方法。

⑤如果服務器使用臨時表來解析查詢，它將使用 LIMIT row_count 子句來計算需要多少空間。

⑥如果 ORDER BY 不走索引，而且后面還帶了 LIMIT 的話，那么優化器可能可以避免用一個合并文件，并使用內存中的 filesort 操作對內存中的行進行排序。

⑦如果 ORDER BY 列有多行具有相同的值，服務器可以自由地以任何順序返回這些行，并且根據總體執行計劃可能以不同的方式返回。換句話說，這些行的排序順序對于無序列是不確定的。

影響執行計劃的一個因素是 LIMIT，因此對于一個 ORDER BY 查詢而言，帶與不帶 LIMIT 返回的行的順序可能是不一樣的。

看下面的例子：

包含 LIMIT 可能會影響每一個 category 行的順序。例如：

如果你需要確保無論帶不帶 LIMIT 都要以相同的順序返回，那么你可以在 ORDER BY 中包含附加列，以使順序具有確定性。例如：

小結

如果你只需要結果集中的某幾行，那么建議使用 limit。這樣這樣的話可以避免抓取全部結果集，然后再丟棄那些你不要的行。

對于 order by 查詢，帶或者不帶 limit 可能返回行的順序是不一樣的。

如果 limit row_count 與 order by 一起使用，那么在找到第一個 row_count 就停止排序，直接返回。

如果 order by 列有相同的值，那么 MySQL 可以自由地以任何順序返回這些行。換言之，只要 order by 列的值不重復，就可以保證返回的順序。

可以在 order by 子句中包含附加列，以使順序具有確定性。

參考文檔：

• https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html
• https://dev.mysql.com/doc/refman/5.7/en/
• https://dev.mysql.com/doc/

作者：廢物大師兄

編輯：陶家龍

出處：cnblogs.com/cjsblog/p/10874938.html