切記！MySQL中Order By與Limit不要一起用！

作者：廢物大師兄 2021-01-13 09:07:32

數(shù)據(jù)庫(kù) MySQL

切記！MySQL 中 ORDER BY 與 LIMIT 不要一起用，有大坑......

[[375496]]

圖片來(lái)自 Pexels

現(xiàn)象與問(wèn)題

ORDER BY 排序后，用 LIMIT 取前幾條，發(fā)現(xiàn)返回的結(jié)果集的順序與預(yù)期的不一樣。

下面是我遇到的問(wèn)題：

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

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

于是，改成 order by status，id：

問(wèn)題雖然是解決了，但還是看看官方文檔上怎么說(shuō)的吧!

LIMIT 查詢(xún)優(yōu)化

摘自“LIMIT 查詢(xún)優(yōu)化”

如果你只需要結(jié)果集中的指定數(shù)量的行，那么請(qǐng)?jiān)诓樵?xún)中使用 LIMIT 子句，而不是抓取整個(gè)結(jié)果集并丟棄剩下那些你不要的數(shù)據(jù)。

MySQL 有時(shí)會(huì)優(yōu)化一個(gè)包含 LIMIT 子句并且沒(méi)有 HAVING 子句的查詢(xún)：

①M(fèi)ySQL 通常更愿意執(zhí)行全表掃描，但是如果你用 LIMIT 只查詢(xún)幾行記錄的話(huà)，MySQL 在某些情況下可能會(huì)使用索引。

②如果你將 LIMIT row_count 子句與 ORDER BY 子句組合在一起使用的話(huà)，MySQL 會(huì)在找到排序結(jié)果的第一個(gè) row_count 行后立即停止排序，而不是對(duì)整個(gè)結(jié)果進(jìn)行排序。如果使用索引來(lái)完成排序，這將非常快。

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

一旦找到第一個(gè) row_count 之后，MySQL 不會(huì)對(duì)結(jié)果集的任何剩余部分進(jìn)行排序。

這種行為的一種表現(xiàn)形式是，一個(gè) ORDER BY 查詢(xún)帶或者不帶 LIMIT 可能返回行的順序是不一樣的。

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

④LIMIT 0 可以快速返回一個(gè)空的結(jié)果集，這是用來(lái)檢測(cè)一個(gè)查詢(xún)是否有效的一種很有用的方法。

⑤如果服務(wù)器使用臨時(shí)表來(lái)解析查詢(xún)，它將使用 LIMIT row_count 子句來(lái)計(jì)算需要多少空間。

⑥如果 ORDER BY 不走索引，而且后面還帶了 LIMIT 的話(huà)，那么優(yōu)化器可能可以避免用一個(gè)合并文件，并使用內(nèi)存中的 filesort 操作對(duì)內(nèi)存中的行進(jìn)行排序。

⑦如果 ORDER BY 列有多行具有相同的值，服務(wù)器可以自由地以任何順序返回這些行，并且根據(jù)總體執(zhí)行計(jì)劃可能以不同的方式返回。換句話(huà)說(shuō)，這些行的排序順序?qū)τ跓o(wú)序列是不確定的。

影響執(zhí)行計(jì)劃的一個(gè)因素是 LIMIT，因此對(duì)于一個(gè) ORDER BY 查詢(xún)而言，帶與不帶 LIMIT 返回的行的順序可能是不一樣的。

看下面的例子：