切記!MySQL中order by與limit不要一起用!
現(xiàn)象與問題
ORDER BY 排序后,用 LIMIT 取前幾條,發(fā)現(xiàn)返回的結(jié)果集的順序與預(yù)期的不一樣。
可以看到,帶 LIMIT 與不帶 LIMIT 的結(jié)果與我預(yù)期的不一樣,而且“很不可思議”,真是百思不得其解。
后來百度了一下,如果 order by 的列有相同的值時(shí),MySQL 會(huì)隨機(jī)選取這些行,為了保證每次都返回的順序一致可以額外增加一個(gè)排序字段(比如:id),用兩個(gè)字段來盡可能減少重復(fù)的概率。
于是,改成 order by status,id:
問題雖然是解決了,但還是看看官方文檔上怎么說的吧!
LIMIT 查詢優(yōu)化
摘自“LIMIT 查詢優(yōu)化”
如果你只需要結(jié)果集中的指定數(shù)量的行,那么請(qǐng)?jiān)诓樵冎惺褂?LIMIT 子句,而不是抓取整個(gè)結(jié)果集并丟棄剩下那些你不要的數(shù)據(jù)。
MySQL 有時(shí)會(huì)優(yōu)化一個(gè)包含 LIMIT 子句并且沒有 HAVING 子句的查詢:
①MySQL 通常更愿意執(zhí)行全表掃描,但是如果你用 LIMIT 只查詢幾行記錄的話,MySQL 在某些情況下可能會(huì)使用索引。
②如果你將 LIMIT row_count 子句與 ORDER BY 子句組合在一起使用的話,MySQL 會(huì)在找到排序結(jié)果的第一個(gè) row_count 行后立即停止排序,而不是對(duì)整個(gè)結(jié)果進(jìn)行排序。如果使用索引來完成排序,這將非??臁?br/>
如果必須執(zhí)行文件排序,則在找到第一個(gè) row_count 行之前,選擇所有與查詢匹配但不包括 LIMIT 子句的行,并對(duì)其中大部分或所有行進(jìn)行排序。
一旦找到第一個(gè) row_count 之后,MySQL 不會(huì)對(duì)結(jié)果集的任何剩余部分進(jìn)行排序。
這種行為的一種表現(xiàn)形式是,一個(gè) ORDER BY 查詢帶或者不帶 LIMIT 可能返回行的順序是不一樣的。
③如果 LIMIT row_count 與 DISTINCT 一起使用,一旦找到 row_count 惟一的行,MySQL 就會(huì)停止。
④LIMIT 0 可以快速返回一個(gè)空的結(jié)果集,這是用來檢測(cè)一個(gè)查詢是否有效的一種很有用的方法。
⑤如果服務(wù)器使用臨時(shí)表來解析查詢,它將使用 LIMIT row_count 子句來計(jì)算需要多少空間。
⑥如果 ORDER BY 不走索引,而且后面還帶了 LIMIT 的話,那么優(yōu)化器可能可以避免用一個(gè)合并文件,并使用內(nèi)存中的 filesort 操作對(duì)內(nèi)存中的行進(jìn)行排序。
⑦如果 ORDER BY 列有多行具有相同的值,服務(wù)器可以自由地以任何順序返回這些行,并且根據(jù)總體執(zhí)行計(jì)劃可能以不同的方式返回。換句話說,這些行的排序順序?qū)τ跓o序列是不確定的。
影響執(zhí)行計(jì)劃的一個(gè)因素是 LIMIT,因此對(duì)于一個(gè) ORDER BY 查詢而言,帶與不帶 LIMIT 返回的行的順序可能是不一樣的。
看下面的例子:
如果你需要確保無論帶不帶 LIMIT 都要以相同的順序返回,那么你可以在 ORDER BY 中包含附加列,以使順序具有確定性。例如:
小結(jié)
如果你只需要結(jié)果集中的某幾行,那么建議使用 limit。這樣這樣的話可以避免抓取全部結(jié)果集,然后再丟棄那些你不要的行。
對(duì)于 order by 查詢,帶或者不帶 limit 可能返回行的順序是不一樣的。
如果 limit row_count 與 order by 一起使用,那么在找到第一個(gè) row_count 就停止排序,直接返回。
如果 order by 列有相同的值,那么 MySQL 可以自由地以任何順序返回這些行。換言之,只要 order by 列的值不重復(fù),就可以保證返回的順序。
可以在 order by 子句中包含附加列,以使順序具有確定性。