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

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

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

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

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

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

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

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

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

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

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

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

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

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

• MySQL 版本：8.0.30

• 測試表結(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)合索引（過濾字段id2）
);
-- 插入測試數(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');

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

2. 參數(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：通過主鍵索引掃描后，逐行過濾id2>8。

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

3. 參數(shù)關(guān)閉時（優(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：利用索引過濾id2>8（ICP 特性減少 IO），再對結(jié)果排序取前 2 條。

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

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

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

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

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

• 建議開啟的場景：

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

• 運(yùn)維建議：

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

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

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

五、總結(jié)

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

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

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

最新評論