在 MySQL 查詢優(yōu)化中，LIMIT子句的使用非常普遍，尤其在分頁(yè)場(chǎng)景中。但當(dāng)LIMIT與ORDER BY、GROUP BY結(jié)合時(shí)，優(yōu)化器對(duì)索引的選擇往往直接影響查詢性能。MySQL 8.0.21 版本引入的prefer_ordering_index參數(shù)，為解決這類場(chǎng)景的性能問(wèn)題提供了新的控制手段。本文將深入解析該參數(shù)的作用機(jī)制、實(shí)踐效果及適用場(chǎng)景。

一、背景：LIMIT 與排序的索引選擇困境

在包含LIMIT N、ORDER BY和GROUP BY的查詢中，優(yōu)化器的核心目標(biāo)是減少排序操作—— 這通常意味著優(yōu)先選擇與ORDER BY字段相關(guān)的索引（“排序索引”），利用索引的有序性避免額外排序。

但實(shí)際場(chǎng)景中，這種 “最優(yōu)解” 可能適得其反：若排序索引與WHERE條件中的過(guò)濾字段無(wú)關(guān)，優(yōu)化器可能會(huì)放棄過(guò)濾性更好的索引，轉(zhuǎn)而掃描排序索引并回表過(guò)濾，最終導(dǎo)致全表掃描式的低效查詢。

例如，一張表同時(shí)存在主鍵索引（id1）和過(guò)濾字段索引（id2），當(dāng)查詢?yōu)?code>SELECT c2 FROM t WHERE id2>8 ORDER BY id1 LIMIT 2時(shí)：

• 優(yōu)化器可能優(yōu)先選擇主鍵索引（因ORDER BY id1），遍歷索引后逐行判斷id2>8，導(dǎo)致大量無(wú)效掃描；
• 更優(yōu)的選擇是使用id2索引過(guò)濾出符合條件的記錄，再對(duì)結(jié)果排序后取前 2 條，但優(yōu)化器可能因 “避免排序” 而忽略此方案。

在 MySQL 8.0.21 之前，這種索引選擇行為無(wú)法通過(guò)參數(shù)干預(yù)，只能通過(guò)改寫 SQL（如延遲關(guān)聯(lián)）優(yōu)化，靈活性較差。

二、新特性：prefer_ordering_index 參數(shù)的作用

MySQL 8.0.21 新增的prefer_ordering_index參數(shù)，通過(guò)optimizer_switch系統(tǒng)變量控制，用于調(diào)整優(yōu)化器對(duì) “排序索引” 的偏好：

• 開啟（默認(rèn)）：prefer_ordering_index=on，優(yōu)化器優(yōu)先選擇排序相關(guān)索引，以減少排序操作；
• 關(guān)閉：prefer_ordering_index=off，優(yōu)化器弱化對(duì)排序索引的偏好，更傾向于選擇過(guò)濾性好的索引，即使需要額外排序。

參數(shù)設(shè)置方式：

-- 開啟（默認(rèn)）
SET optimizer_switch = "prefer_ordering_index=on";
-- 關(guān)閉
SET optimizer_switch = "prefer_ordering_index=off";

三、實(shí)踐驗(yàn)證：參數(shù)對(duì)執(zhí)行計(jì)劃的影響

1. 測(cè)試環(huán)境與數(shù)據(jù)準(zhǔn)備

• MySQL 版本：8.0.30

• 測(cè)試表結(jié)構(gòu)：

CREATE TABLE t (
  id1 BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,  -- 主鍵索引
  id2 BIGINT NOT NULL,
  c1 VARCHAR(50) NOT NULL,
  c2 VARCHAR(50) NOT NULL,
  INDEX i (id2, c1)  -- 聯(lián)合索引（過(guò)濾字段id2）
);
-- 插入測(cè)試數(shù)據(jù)
INSERT INTO t(id2, c1, c2) VALUES
(1,'a','xfvs'), (2,'bbbb','xfvs'), (3,'cdddd','xfvs'),
(4,'dfdf','xfvs'), (12,'bbbb','xfvs'), (23,'cdddd','xfvs'),
(14,'dfdf','xfvs'), (11,'bbbb','xfvs'), (13,'cdddd','xfvs'),
(44,'dfdf','xfvs'), (31,'bbbb','xfvs'), (33,'cdddd','xfvs'),
(34,'dfdf','xfvs');

• 測(cè)試查詢：SELECT c2 FROM t WHERE id2>8 ORDER BY id1 ASC LIMIT 2

2. 參數(shù)開啟時(shí)（默認(rèn)行為）

-- 確認(rèn)參數(shù)狀態(tài)
SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';  -- 返回1（開啟）
-- 查看執(zhí)行計(jì)劃
EXPLAIN SELECT c2 FROM t WHERE id2>8 ORDER BY id1 ASC LIMIT 2\G

執(zhí)行計(jì)劃關(guān)鍵信息：

• type: index：使用索引掃描（主鍵索引PRIMARY）；
• key: PRIMARY：選擇主鍵索引；
• Extra: Using where：通過(guò)主鍵索引掃描后，逐行過(guò)濾id2>8。

問(wèn)題：主鍵索引與id2無(wú)關(guān)，需掃描大量無(wú)關(guān)記錄后過(guò)濾，在大表中會(huì)導(dǎo)致嚴(yán)重性能問(wèn)題。

3. 參數(shù)關(guān)閉時(shí)（優(yōu)化后）

-- 關(guān)閉參數(shù)
SET optimizer_switch = "prefer_ordering_index=off";
-- 查看執(zhí)行計(jì)劃
EXPLAIN SELECT c2 FROM t WHERE id2>8 ORDER BY id1 ASC LIMIT 2\G

執(zhí)行計(jì)劃關(guān)鍵信息：

• type: range：使用范圍掃描（索引i）；
• key: i：選擇id2的聯(lián)合索引；
• Extra: Using index condition; Using filesort：利用索引過(guò)濾id2>8（ICP 特性減少 IO），再對(duì)結(jié)果排序取前 2 條。

優(yōu)勢(shì)：通過(guò)過(guò)濾性更好的id2索引減少掃描范圍，即使增加排序步驟，整體效率仍高于全表掃描。

四、適用場(chǎng)景與最佳實(shí)踐

prefer_ordering_index參數(shù)并非 “銀彈”，需根據(jù)具體場(chǎng)景選擇是否關(guān)閉：

• 建議關(guān)閉的場(chǎng)景：

  • WHERE條件有高效過(guò)濾索引（如id2），但ORDER BY字段為其他索引（如主鍵）；
  • 表數(shù)據(jù)量大，排序索引與過(guò)濾字段無(wú)關(guān)，優(yōu)先過(guò)濾可大幅減少數(shù)據(jù)量；
  • 執(zhí)行計(jì)劃顯示type: index且rows值過(guò)大（全表掃描風(fēng)險(xiǎn)）。

• 建議開啟的場(chǎng)景：

  • ORDER BY字段的索引同時(shí)包含過(guò)濾條件（如聯(lián)合索引(id1, id2)），可同時(shí)滿足過(guò)濾和排序；
  • 數(shù)據(jù)量小，排序索引掃描的成本低于 “過(guò)濾 + 排序”。

• 運(yùn)維建議：

  通過(guò)EXPLAIN對(duì)比參數(shù)開關(guān)時(shí)的執(zhí)行計(jì)劃，判斷是否存在 “無(wú)效排序索引偏好”；

  僅在確認(rèn)性能問(wèn)題時(shí)臨時(shí)關(guān)閉參數(shù)（會(huì)話級(jí)別），避免全局設(shè)置影響其他查詢；

  結(jié)合慢查詢?nèi)罩?，定位?code>LIMIT+ORDER BY導(dǎo)致的低效查詢，針對(duì)性優(yōu)化。

五、總結(jié)

MySQL 8.0 引入的prefer_ordering_index參數(shù)，為LIMIT與排序結(jié)合的查詢提供了更精細(xì)的優(yōu)化控制。它的核心價(jià)值在于：允許開發(fā)者干預(yù)優(yōu)化器對(duì) “排序索引” 的偏好，在 “避免排序” 和 “減少掃描范圍” 之間找到平衡。

隨著 MySQL 優(yōu)化器的不斷進(jìn)化，這類參數(shù)的出現(xiàn)體現(xiàn)了從 “自動(dòng)最優(yōu)” 到 “可控優(yōu)化” 的趨勢(shì)。掌握這類特性，能幫助開發(fā)者在復(fù)雜業(yè)務(wù)場(chǎng)景中更精準(zhǔn)地提升查詢性能，避免因優(yōu)化器的 “想當(dāng)然” 導(dǎo)致的性能陷阱。

到此這篇關(guān)于MySQL 8.0 中 LIMIT 優(yōu)化新特性 的文章就介紹到這了,更多相關(guān)mysql limit優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論