一、查詢優(yōu)化器（Optimizer）簡(jiǎn)介

查詢優(yōu)化器是數(shù)據(jù)庫(kù)內(nèi)核中最復(fù)雜、最核心的模塊之一。它的作用是根據(jù)解析器和預(yù)處理器生成的語(yǔ)法樹(shù)，結(jié)合表的統(tǒng)計(jì)信息、索引、SQL語(yǔ)句結(jié)構(gòu)等，生成一套最優(yōu)的執(zhí)行計(jì)劃，以盡可能高效地完成查詢。

查詢優(yōu)化器的主要目標(biāo)是：用最少的資源、最快的速度獲取正確的結(jié)果。

二、查詢優(yōu)化器的主要任務(wù)

1. 邏輯優(yōu)化

作用

• 對(duì)SQL語(yǔ)句的結(jié)構(gòu)做變換和簡(jiǎn)化，減少不必要的計(jì)算。

過(guò)程

• 謂詞簡(jiǎn)化：如將 WHERE age > 25 AND age > 20 簡(jiǎn)化為 WHERE age > 25。
• 表達(dá)式重寫(xiě)：如將 WHERE NOT (a = b) 改寫(xiě)為 WHERE a <> b。
• 子查詢改寫(xiě)：如將某些相關(guān)子查詢改寫(xiě)為JOIN，提高效率。
• 等價(jià)變換：如分布律、交換律等，將SQL語(yǔ)句轉(zhuǎn)換為等價(jià)但更高效的形式。

2. 物理優(yōu)化

作用

• 決定具體的物理操作方式和順序，選擇最優(yōu)的執(zhí)行路徑。

過(guò)程

• 訪問(wèn)路徑選擇：決定是否走索引，選擇哪個(gè)索引。
• 連接順序優(yōu)化：多表JOIN時(shí)，決定各表的訪問(wèn)順序（如驅(qū)動(dòng)表、被驅(qū)動(dòng)表）。
• 連接算法選擇：如嵌套循環(huán)連接（Nested Loop Join）、Block Nested Loop Join等。
• 索引覆蓋：如果查詢字段都在索引中，則只掃描索引而不訪問(wèn)數(shù)據(jù)頁(yè)。
• 謂詞下推：將過(guò)濾條件盡量提前到數(shù)據(jù)讀取階段，減少數(shù)據(jù)量。
• LIMIT優(yōu)化：如只掃描滿足LIMIT條件的最少數(shù)據(jù)。

3. 執(zhí)行計(jì)劃生成

作用

• 根據(jù)邏輯和物理優(yōu)化結(jié)果，生成一份詳細(xì)的執(zhí)行計(jì)劃（Execution Plan）。

過(guò)程

• 執(zhí)行計(jì)劃描述了每一步的操作方式、訪問(wèn)對(duì)象、使用的索引、連接方式、過(guò)濾條件等。
• 可以用 EXPLAIN 命令查看執(zhí)行計(jì)劃。

三、查詢優(yōu)化器的關(guān)鍵技術(shù)

1. 統(tǒng)計(jì)信息

• 優(yōu)化器依賴表的統(tǒng)計(jì)信息（如行數(shù)、索引分布、字段基數(shù)等）來(lái)估算不同執(zhí)行路徑的成本。
• 統(tǒng)計(jì)信息由存儲(chǔ)引擎維護(hù)，可通過(guò) ANALYZE TABLE 命令刷新。

2. 成本模型（Cost Model）

• 優(yōu)化器會(huì)為每種可能的執(zhí)行計(jì)劃估算“成本”，包括CPU、IO、內(nèi)存消耗等。
• 選擇成本最低的執(zhí)行計(jì)劃。

3. 索引選擇

• 優(yōu)化器會(huì)分析WHERE條件、ORDER BY、GROUP BY等子句，判斷哪些索引可以使用。
• 優(yōu)先選擇“選擇性高”的索引（即過(guò)濾能力強(qiáng)）。

4. 連接順序優(yōu)化

• 多表JOIN時(shí)，優(yōu)化器會(huì)嘗試多種連接順序，選擇成本最低的方案。
• 例如：A JOIN B JOIN C，可能嘗試(A→B→C)、(B→C→A)等不同順序。

5. 子查詢與視圖優(yōu)化

• 優(yōu)化器會(huì)嘗試將相關(guān)子查詢轉(zhuǎn)換為JOIN，或?qū)⒁晥D進(jìn)行“內(nèi)聯(lián)”展開(kāi)。

四、優(yōu)化器的執(zhí)行流程

1. 接收語(yǔ)法樹(shù)
2. 收集統(tǒng)計(jì)信息
3. 枚舉所有可能的執(zhí)行計(jì)劃
4. 計(jì)算每種計(jì)劃的成本
5. 選擇成本最低的計(jì)劃
6. 輸出最終執(zhí)行計(jì)劃

五、EXPLAIN命令與執(zhí)行計(jì)劃

通過(guò) EXPLAIN 可以查看優(yōu)化器生成的執(zhí)行計(jì)劃。例如：

EXPLAIN SELECT name FROM users WHERE age > 25 ORDER BY name LIMIT 10;

輸出（示例）：

含義：

• type：訪問(wèn)類型（如 ALL、index、range、ref、const、eq_ref）。
• key：使用的索引。
• rows：優(yōu)化器估算需掃描的行數(shù)。
• Extra：額外信息（如是否排序、是否用索引覆蓋）。

六、常見(jiàn)優(yōu)化器相關(guān)問(wèn)題

• 索引未被使用：可能SQL寫(xiě)法不合理，或統(tǒng)計(jì)信息不準(zhǔn)確。
• JOIN順序不優(yōu)：可以用 STRAIGHT_JOIN 強(qiáng)制順序。
• 子查詢未被優(yōu)化：可嘗試改寫(xiě)為JOIN。
• 執(zhí)行計(jì)劃不理想：可通過(guò)分析EXPLAIN結(jié)果，調(diào)整SQL或表結(jié)構(gòu)。

七、優(yōu)化器的局限與補(bǔ)充

• MySQL優(yōu)化器并非“全知全能”，有時(shí)因統(tǒng)計(jì)信息不準(zhǔn)或算法限制，選擇的計(jì)劃并非最優(yōu)。
• 可以通過(guò)SQL重寫(xiě)、添加/調(diào)整索引、更新統(tǒng)計(jì)信息來(lái)“引導(dǎo)”優(yōu)化器。
• MySQL支持部分“優(yōu)化器提示”（Optimizer Hint），如 USE INDEX、FORCE INDEX、STRAIGHT_JOIN 等。

八、流程圖

解析樹(shù)
   ↓
收集統(tǒng)計(jì)信息
   ↓
枚舉執(zhí)行計(jì)劃
   ↓
計(jì)算成本
   ↓
選擇最優(yōu)計(jì)劃
   ↓
輸出執(zhí)行計(jì)劃

九. 優(yōu)化器的內(nèi)部算法與決策流程

1 枚舉所有可能的執(zhí)行計(jì)劃

優(yōu)化器會(huì)根據(jù) SQL 的結(jié)構(gòu)，嘗試多種執(zhí)行路徑。例如三表 JOIN，理論上有 6 種連接順序，優(yōu)化器會(huì)枚舉這些順序，結(jié)合不同的索引選擇和連接算法，形成多種候選執(zhí)行計(jì)劃。

2 計(jì)算成本

每個(gè)執(zhí)行計(jì)劃都會(huì)被打分。成本模型會(huì)估算：

• IO成本：需要讀取多少數(shù)據(jù)頁(yè)
• CPU成本：需要進(jìn)行多少計(jì)算、比較、排序
• 內(nèi)存成本：排序、臨時(shí)表等需要多少內(nèi)存
• 網(wǎng)絡(luò)成本：分布式場(chǎng)景下考慮網(wǎng)絡(luò)傳輸

優(yōu)化器會(huì)選出成本最低的執(zhí)行計(jì)劃。

3 選擇連接算法

常見(jiàn)的連接算法有：

• Nested Loop Join（嵌套循環(huán)連接）：最常用，適合小表驅(qū)動(dòng)大表或有索引的連接
• Block Nested Loop Join：批量處理一組數(shù)據(jù)，減少IO
• Hash Join：MySQL暫不支持，但部分商業(yè)數(shù)據(jù)庫(kù)支持
• Sort-Merge Join：適合大表，先排序后合并

4 謂詞下推與索引覆蓋

謂詞下推（Predicate Pushdown）是指將 WHERE 條件盡量提前到最底層的數(shù)據(jù)訪問(wèn)階段，減少無(wú)效數(shù)據(jù)傳遞。
索引覆蓋（Covering Index）指查詢所需字段全部在索引中，無(wú)需訪問(wèn)數(shù)據(jù)頁(yè)，極大提升性能。

十. 常見(jiàn)優(yōu)化場(chǎng)景與實(shí)例

1 多表 JOIN 順序優(yōu)化

SELECT * FROM a JOIN b ON a.id = b.a_id JOIN c ON b.id = c.b_id WHERE a.status = 1;

• 優(yōu)化器會(huì)根據(jù) a 表的 status 過(guò)濾條件，優(yōu)先驅(qū)動(dòng) a 表（小表/過(guò)濾性強(qiáng)的表）。
• 可能的執(zhí)行計(jì)劃:
  • a → b → c
  • b → a → c
  • c → b → a
• 優(yōu)化器會(huì)估算每種方案的成本，選最優(yōu)。

2 子查詢改寫(xiě)為 JOIN

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

優(yōu)化器會(huì)嘗試將 IN 子查詢改寫(xiě)為半連接或 JOIN，提高效率。

3 索引選擇

SELECT * FROM users WHERE age > 30 AND city = 'Beijing';

• 若 age 和 city 都有索引，優(yōu)化器會(huì)根據(jù)選擇性（過(guò)濾效果）決定用哪個(gè)索引。
• 也可能選擇聯(lián)合索引。

十一. 優(yōu)化器提示（Optimizer Hint）

有時(shí)優(yōu)化器選擇的執(zhí)行計(jì)劃并非我們期望的，可以用優(yōu)化器提示強(qiáng)制指定計(jì)劃：

• USE INDEX：建議優(yōu)化器使用某個(gè)索引
• FORCE INDEX：強(qiáng)制優(yōu)化器使用某個(gè)索引
• IGNORE INDEX：忽略某個(gè)索引
• STRAIGHT_JOIN：強(qiáng)制 JOIN 順序

示例：

SELECT * FROM users USE INDEX (idx_age) WHERE age > 30;
SELECT * FROM a STRAIGHT_JOIN b ON a.id = b.a_id;

十二. EXPLAIN 結(jié)果詳細(xì)解讀

EXPLAIN 是診斷 SQL 性能的核心工具。常見(jiàn)字段說(shuō)明如下：

示例解讀：

• type=ALL：全表掃描，性能最差
• type=range：索引范圍掃描
• type=ref/eq_ref：索引等值查詢，性能較好
• Extra=Using index：索引覆蓋，無(wú)需回表
• Extra=Using filesort：需要額外排序，可能影響性能

十三. 與其他數(shù)據(jù)庫(kù)優(yōu)化器的對(duì)比

• MySQL：主要采用基于成本的優(yōu)化器，支持多種連接算法和索引優(yōu)化。
• Oracle/PostgreSQL：優(yōu)化器更復(fù)雜，支持 Hash Join、Merge Join、并行執(zhí)行等高級(jí)特性。
• SQL Server：優(yōu)化器也非常強(qiáng)大，支持更多執(zhí)行計(jì)劃緩存和并行優(yōu)化。

MySQL 優(yōu)化器更適合中小型業(yè)務(wù)，大型復(fù)雜查詢可能需要手動(dòng)干預(yù)或SQL改寫(xiě)。

十四. 執(zhí)行計(jì)劃的生成與結(jié)構(gòu)

優(yōu)化器最終會(huì)輸出一份執(zhí)行計(jì)劃，描述 SQL 查詢的每一步操作。執(zhí)行計(jì)劃包括：

• 訪問(wèn)哪些表，順序如何；
• 使用哪些索引；
• 采用什么連接算法；
• 何時(shí)做排序、分組、聚合；
• 是否需要臨時(shí)表、文件排序等。

執(zhí)行計(jì)劃的結(jié)構(gòu)通常是一個(gè)樹(shù)狀結(jié)構(gòu)，每個(gè)節(jié)點(diǎn)代表一個(gè)操作（如表掃描、索引查找、JOIN、排序等），節(jié)點(diǎn)之間有父子關(guān)系，表示數(shù)據(jù)流動(dòng)的順序。

示例

假設(shè)有如下 SQL：

SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 30 AND o.status = 'paid'
ORDER BY o.amount DESC
LIMIT 5;

優(yōu)化器會(huì)考慮：

• 先掃描 users 表，利用 age 索引過(guò)濾；
• 以 users 為驅(qū)動(dòng)表，關(guān)聯(lián) orders 表（orders 的 user_id 有索引）；
• 對(duì)結(jié)果按 amount 排序，取前 5 條。

最終執(zhí)行計(jì)劃大致為：

TableScan(users) → Filter(age > 30) → NestedLoopJoin(orders, user_id) → Filter(status='paid') → Sort(amount DESC) → Limit(5)

十五. EXPLAIN 實(shí)踐與分析

基本用法

EXPLAIN SELECT ...;

典型輸出解讀

• type=range：users 表用 age 索引做范圍掃描，性能較好；
• type=ref：orders 表用 user_id 索引做等值連接；
• Using filesort：需要額外排序，可能影響性能；
• rows：優(yōu)化器估算的需掃描行數(shù)，實(shí)際越少越好。

進(jìn)階技巧

• 使用 EXPLAIN ANALYZE（MySQL 8.0.18+）可獲得真實(shí)執(zhí)行時(shí)間與行數(shù)，幫助識(shí)別瓶頸。
• 結(jié)合 SHOW WARNINGS 查看優(yōu)化器為何沒(méi)用索引。

十六. 優(yōu)化器的局限與手動(dòng)優(yōu)化

局限性

• 統(tǒng)計(jì)信息不準(zhǔn)確：如果表/索引統(tǒng)計(jì)信息過(guò)時(shí)，優(yōu)化器可能選錯(cuò)執(zhí)行路徑。
• 索引選擇不理想：有時(shí)優(yōu)化器未選用最優(yōu)索引，需手動(dòng)干預(yù)。
• 復(fù)雜 SQL 難以優(yōu)化：如大量嵌套子查詢、復(fù)雜表達(dá)式，優(yōu)化器可能“放棄”優(yōu)化。
• 文件排序/臨時(shí)表：ORDER BY、GROUP BY 涉及大數(shù)據(jù)量時(shí)，容易使用文件排序或臨時(shí)表，影響性能。

手動(dòng)優(yōu)化手段

• 更新統(tǒng)計(jì)信息：ANALYZE TABLE，讓優(yōu)化器有最新數(shù)據(jù)。
• 優(yōu)化器提示：用 USE INDEX、FORCE INDEX、STRAIGHT_JOIN 強(qiáng)制優(yōu)化器按指定方式執(zhí)行。
• SQL改寫(xiě)：將子查詢改為 JOIN，減少不必要的計(jì)算。
• 索引設(shè)計(jì)：為常用過(guò)濾條件、排序字段建立復(fù)合索引。
• 分解復(fù)雜查詢：將復(fù)雜 SQL 拆分為多步處理，減少優(yōu)化器壓力。

示例

SELECT /*+ USE_INDEX(users, age_idx) */ name FROM users WHERE age > 30;

十七. 優(yōu)化器與存儲(chǔ)引擎協(xié)作

• 優(yōu)化器負(fù)責(zé)策略，存儲(chǔ)引擎負(fù)責(zé)執(zhí)行。優(yōu)化器只決定“怎么做”，具體的數(shù)據(jù)訪問(wèn)、索引遍歷、鎖管理等由存儲(chǔ)引擎（如 InnoDB）完成。
• 優(yōu)化器會(huì)向存儲(chǔ)引擎請(qǐng)求統(tǒng)計(jì)信息，決定索引選擇、訪問(wèn)順序。
• 存儲(chǔ)引擎可以影響優(yōu)化器的能力（如 InnoDB 支持行鎖、MVCC，有些優(yōu)化策略才可用）。

十八. 性能調(diào)優(yōu)建議

• 定期用 EXPLAIN 檢查慢查詢；
• 保持統(tǒng)計(jì)信息新鮮；
• 合理設(shè)計(jì)索引，避免冗余、重復(fù)；
• 避免復(fù)雜嵌套查詢，能用 JOIN 就不用子查詢；
• 用 LIMIT 限制返回?cái)?shù)據(jù)量，減少內(nèi)存壓力；
• 對(duì)大表的 ORDER BY、GROUP BY 盡量用索引覆蓋。

十九. 參考工具

• EXPLAIN / EXPLAIN ANALYZE：分析執(zhí)行計(jì)劃和實(shí)際消耗
• SHOW INDEX FROM 表名：查看索引情況
• SHOW PROFILE：分析 SQL 各階段消耗
• 慢查詢?nèi)罩?/strong>：定位性能瓶頸