1.1 Optimizer Trace簡介

1.1 引入背景

在MySQL中，查詢優(yōu)化器負(fù)責(zé)解析SQL語句，并根據(jù)成本估算選擇最優(yōu)執(zhí)行計(jì)劃。然而，對于復(fù)雜查詢，有時優(yōu)化器的決策過程難以理解，尤其是出現(xiàn)：

• 索引選擇錯誤
• 連接順序不合理
• 成本估算偏差

為了解決這些問題，MySQL從5.6版本引入了 Optimizer Trace 功能，用于追蹤優(yōu)化器的內(nèi)部決策過程，以 JSON 格式輸出詳細(xì)執(zhí)行信息。

官方文檔鏈接：
MySQL 8.0 Reference Manual - Optimizer Trace

1.2 Optimizer Trace 與 EXPLAIN 的區(qū)別

可以把 EXPLAIN 理解為“最終計(jì)劃的快照”，而 Optimizer Trace 則是“優(yōu)化器的思考過程記錄”。

1.3 適用場景

• 復(fù)雜多表 JOIN 查詢調(diào)試：理解優(yōu)化器如何選擇連接順序
• 索引選擇異常：查看優(yōu)化器為何未使用期望索引
• 成本估算異常：分析行數(shù)估算、過濾率計(jì)算
• 調(diào)優(yōu)驗(yàn)證：確認(rèn)優(yōu)化器是否采納了調(diào)優(yōu)建議

1.4 示例代碼：開啟 Optimizer Trace 并執(zhí)行簡單查詢

環(huán)境：MySQL 8.0，單機(jī)測試

-- 開啟 Optimizer Trace
SET optimizer_trace="enabled=on";
 
-- 創(chuàng)建測試表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    INDEX idx_dept(department_id)
);
 
INSERT INTO employees VALUES
(1,'Alice',1),
(2,'Bob',2),
(3,'Charlie',1);
 
-- 執(zhí)行簡單查詢
SELECT * FROM employees WHERE department_id = 1;
 
-- 查看 Optimizer Trace
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
 
-- 關(guān)閉 Optimizer Trace
SET optimizer_trace="enabled=off";

預(yù)期結(jié)果：

• INFORMATION_SCHEMA.OPTIMIZER_TRACE 返回 JSON 結(jié)構(gòu)
• JSON 包含 steps、join_order、costs 等字段
• 可以看到優(yōu)化器如何決定使用 idx_dept 索引

2 工作原理與核心價值

2.1 Optimizer Trace 的工作階段

Optimizer Trace 會在查詢優(yōu)化階段對優(yōu)化器的決策流程進(jìn)行追蹤，主要包括以下幾個階段（Stage）：

join_preparation

功能：初始化 JOIN 查詢的基本信息

內(nèi)容：

• 表數(shù)量、別名信息
• 每個表的訪問方式（全表掃描、索引訪問）
• 相關(guān)統(tǒng)計(jì)信息（行數(shù)、索引基數(shù)）

作用：為后續(xù)優(yōu)化器選擇最優(yōu) JOIN 順序做準(zhǔn)備

join_optimization

功能：選擇最優(yōu)連接順序和訪問方法

內(nèi)容：

• 每個 JOIN 的可能訪問方式（access path）
• 成本估算（cost estimation）
• 排序選擇（order of tables）

作用：記錄優(yōu)化器如何計(jì)算每種執(zhí)行方案的代價并選擇最優(yōu)方案

final_plan

功能：生成最終執(zhí)行計(jì)劃

內(nèi)容：

• 確定訪問索引
• 確定連接類型（Nested Loop、Block Nested Loop、Hash Join 等）
• 預(yù)估掃描行數(shù)和代價

作用：把優(yōu)化器的“思考結(jié)果”形成可執(zhí)行計(jì)劃

注意：每個階段都會輸出 JSON 結(jié)構(gòu)，包含詳細(xì)的決策信息，包括成本（cost）、掃描行數(shù)（rows）、使用索引（access_method）等。

2.2 JSON 輸出結(jié)構(gòu)解析

Optimizer Trace 輸出主要字段說明：

{
  "trace": [
    {
      "step": 1,
      "phase": "join_preparation",
      "table": "employees",
      "access_method": "ALL",
      "rows": 3
    },
    {
      "step": 2,
      "phase": "join_optimization",
      "join_order": ["employees"],
      "cost": 0.03,
      "chosen_access": "index(idx_dept)"
    },
    {
      "step": 3,
      "phase": "final_plan",
      "plan": [
        {
          "table": "employees",
          "access_method": "index",
          "key": "idx_dept",
          "rows_examined": 2
        }
      ]
    }
  ]
}

字段說明：

通過這個 JSON 輸出，開發(fā)者可以 逐步追蹤優(yōu)化器的每個決策，并分析為什么某些索引未被使用、JOIN 順序?yàn)楹尾缓侠怼?/p>

2.3 Optimizer Trace 的核心價值

可視化優(yōu)化器決策過程

對比 EXPLAIN，Trace 不僅展示最終計(jì)劃，還展示選擇路徑和成本比較。

定位索引選擇問題

能夠分析為什么優(yōu)化器沒有選擇期望索引，便于調(diào)整統(tǒng)計(jì)信息或優(yōu)化 SQL。

分析成本估算偏差

Trace 提供每一步的成本和行數(shù)估算，方便對比實(shí)際執(zhí)行情況。

調(diào)優(yōu)驗(yàn)證

調(diào)整索引或 SQL 后，可通過 Trace 驗(yàn)證優(yōu)化器是否采納建議。

結(jié)合 EXPLAIN 使用

EXPLAIN 展示最終計(jì)劃，Trace 展示優(yōu)化器思路，二者結(jié)合可以完整理解查詢執(zhí)行邏輯。

2.4 示例代碼：分析復(fù)雜查詢的 Trace 輸出

環(huán)境：MySQL 8.0

-- 開啟 Trace
SET optimizer_trace='enabled=on';
 
-- 創(chuàng)建測試表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
 
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    INDEX idx_dept(department_id)
);
 
INSERT INTO departments VALUES (1,'HR'),(2,'IT');
INSERT INTO employees VALUES (1,'Alice',1),(2,'Bob',2),(3,'Charlie',1);
 
-- 執(zhí)行多表 JOIN 查詢
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.id = 1;
 
-- 查看 Trace
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
 
-- 關(guān)閉 Trace
SET optimizer_trace='off';

分析步驟：

• 查看 join_preparation 階段，確認(rèn)優(yōu)化器識別了兩個表及索引信息。
• 在 join_optimization 階段，分析優(yōu)化器嘗試的連接順序和代價比較。
• 在 final_plan 階段，確認(rèn)最終選擇了 idx_dept 索引訪問 employees 表，并輸出預(yù)估掃描行數(shù)。
• 對比 EXPLAIN 輸出，驗(yàn)證 Trace 的決策與最終執(zhí)行計(jì)劃一致性。

3 配置參數(shù)詳解

Optimizer Trace 依賴 MySQL 系統(tǒng)變量來控制開啟、輸出內(nèi)容和調(diào)試精度。主要參數(shù)包括：

3.1 optimizer_trace

作用：控制 Optimizer Trace 功能開關(guān)

類型：字符串（枚舉 enabled=on|off 或 JSON 配置）

默認(rèn)值：enabled=off

修改方法：

-- 開啟 Trace
SET optimizer_trace='enabled=on';
 
-- 關(guān)閉 Trace
SET optimizer_trace='enabled=off';

JSON 形式：

SET optimizer_trace='{
    "enabled": true,
    "trace_objects": "all",
    "max_elements": 1000
}';

trace_objects：指定追蹤對象，可選 all、optimizer

max_elements：JSON 輸出中最大元素數(shù)，超過會被截斷

3.2 optimizer_trace_features

作用：控制 Trace 輸出的內(nèi)容粒度

類型：字符串（逗號分隔）

默認(rèn)值："basic"

可選值：

• basic：基本信息（表、訪問方法、成本）
• io：包含 I/O 成本
• memory：包含內(nèi)存使用估算
• all：包含全部詳細(xì)信息

修改方法：

SET optimizer_trace_features='all'; 

3.3 max_optimizer_trace_elements

作用：限制 Trace 輸出的最大元素數(shù)量

類型：整數(shù)

默認(rèn)值：1000

修改方法：

SET max_optimizer_trace_elements=2000; 

影響：如果 Trace 輸出超過限制，JSON 會被截斷，分析不完整。

3.4 注意事項(xiàng)

系統(tǒng)級變量 vs 會話級變量

• SET GLOBAL optimizer_trace='...'：修改全局，重啟后生效
• SET SESSION optimizer_trace='...'：只對當(dāng)前會話有效

與其他參數(shù)的沖突

• 開啟 optimizer_trace 時，如果 max_optimizer_trace_elements 太小，復(fù)雜查詢的 Trace 會被截斷
• 輸出 JSON 大量元素可能導(dǎo)致查詢慢，因此只在調(diào)試環(huán)境開啟

對性能的影響

• 開啟 Trace 會增加優(yōu)化器計(jì)算開銷
• 生產(chǎn)環(huán)境應(yīng)謹(jǐn)慎開啟，僅用于分析問題

3.5 示例：調(diào)整參數(shù)并觀察 Trace 輸出差異

環(huán)境：MySQL 8.0，測試庫

-- 創(chuàng)建測試表
CREATE TABLE test_employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    INDEX idx_dept(dept_id)
);
 
INSERT INTO test_employees VALUES (1,'Alice',1),(2,'Bob',2),(3,'Charlie',1);
 
-- 開啟 Trace 并設(shè)置輸出全部詳細(xì)信息
SET optimizer_trace='enabled=on';
SET optimizer_trace_features='all';
SET max_optimizer_trace_elements=5000;
 
-- 執(zhí)行查詢
SELECT * FROM test_employees WHERE dept_id=1;
 
-- 查看 Trace
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
 
-- 調(diào)整參數(shù)只輸出 basic
SET optimizer_trace_features='basic';
 
-- 再次執(zhí)行查詢查看差異
SELECT * FROM test_employees WHERE dept_id=1;
 
-- 關(guān)閉 Trace
SET optimizer_trace='enabled=off';

預(yù)期效果：

• optimizer_trace_features='all'：輸出包含 I/O、內(nèi)存估算等完整信息
• optimizer_trace_features='basic'：只輸出表、訪問方法、行數(shù)和成本
• 通過對比，可以清晰看到不同參數(shù)對 Trace 輸出內(nèi)容的影響

3.6 小結(jié)

• optimizer_trace：開啟/關(guān)閉 Trace
• optimizer_trace_features：控制輸出粒度
• max_optimizer_trace_elements：控制最大輸出元素數(shù)
• 調(diào)整參數(shù)可以幫助在不同調(diào)試場景下獲取所需信息，同時避免不必要的性能開銷

4 使用方法與調(diào)試流程

Optimizer Trace 的使用主要包括四個步驟：開啟 Trace → 執(zhí)行查詢 → 查看 Trace → 分析與驗(yàn)證。下面逐步講解。

4.1 步驟 1：開啟 Optimizer Trace

基本開啟方法（會話級）：

SET optimizer_trace='enabled=on';

指定輸出詳細(xì)程度（可選）：

SET optimizer_trace_features='all';  -- 輸出所有信息，包括 I/O 和內(nèi)存估算
SET max_optimizer_trace_elements=5000;  -- 避免復(fù)雜查詢輸出被截斷

關(guān)閉 Trace：

SET optimizer_trace='enabled=off';

注意：生產(chǎn)環(huán)境中建議僅在調(diào)試場景開啟，以避免性能開銷。

4.2 步驟 2：執(zhí)行需要分析的查詢

示例：MySQL 8.0

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    INDEX idx_dept(department_id)
);
 
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
 
INSERT INTO departments VALUES (1,'HR'),(2,'IT');
INSERT INTO employees VALUES (1,'Alice',1),(2,'Bob',2),(3,'Charlie',1);
 
-- 執(zhí)行復(fù)雜 JOIN 查詢
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.id = 1;

• 查詢執(zhí)行過程中，優(yōu)化器會根據(jù)統(tǒng)計(jì)信息計(jì)算多個執(zhí)行方案，并記錄在 Trace 中
• Trace 會捕捉每個階段（join_preparation、join_optimization、final_plan）的決策

4.3 步驟 3：查看 Trace 輸出

查詢 Trace：

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G; 

輸出分析：

• trace 字段為 JSON，包含所有優(yōu)化器決策步驟

關(guān)鍵字段：

• phase：優(yōu)化階段
• table：操作的表
• access_method：表訪問方式
• rows：預(yù)估掃描行數(shù)
• cost：優(yōu)化器成本估算
• chosen_access：最終選擇訪問方式
• plan：最終執(zhí)行計(jì)劃詳情

結(jié)合 JSON 格式化工具，可清晰看到優(yōu)化器每一步?jīng)Q策過程。

4.4 步驟 4：分析與驗(yàn)證

與 EXPLAIN 對比

EXPLAIN SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.id = 1\G;

• EXPLAIN 提供最終執(zhí)行計(jì)劃，包含訪問索引、連接類型、掃描行數(shù)
• Optimizer Trace 提供優(yōu)化器選擇該計(jì)劃的完整邏輯

分析決策過程

查看 join_optimization 階段：

• 哪些訪問方案被嘗試
• 各方案成本估算
• 為什么最終選擇當(dāng)前方案

查看 final_plan 階段：

• 驗(yàn)證訪問索引是否符合預(yù)期
• 驗(yàn)證掃描行數(shù)與成本是否合理

調(diào)試索引選擇錯誤示例

假設(shè)優(yōu)化器未使用期望索引：

-- 查詢未使用索引
SELECT * FROM employees WHERE department_id=1;
 
-- Trace 顯示 access_method=ALL（全表掃描）
-- 分析可能原因：
-- 1. 索引統(tǒng)計(jì)信息不準(zhǔn)確
-- 2. 表行數(shù)過少，優(yōu)化器認(rèn)為全表掃描成本低
-- 調(diào)整統(tǒng)計(jì)信息
ANALYZE TABLE employees;
 
-- 再次執(zhí)行查詢，觀察 Trace 輸出是否選擇 idx_dept 索引

通過對比 Trace 輸出，可以清楚看到優(yōu)化器為何選擇或忽略某個索引，從而指導(dǎo)索引優(yōu)化或 SQL 調(diào)整。

4.5 小結(jié)

• 開啟 Trace → 執(zhí)行查詢 → 查看輸出 → 分析決策 是標(biāo)準(zhǔn)流程
• Trace 能夠詳細(xì)記錄優(yōu)化器每個階段的決策，包括訪問方式、成本估算、連接順序
• 與 EXPLAIN 配合使用，可以同時掌握最終計(jì)劃與優(yōu)化器思路
• 適用于調(diào)試復(fù)雜查詢、索引選擇錯誤、成本估算偏差等問題

5 典型場景分析

場景1：索引選擇錯誤

5.1.1 問題描述

• 查詢條件明確，但優(yōu)化器未使用預(yù)期索引
• 導(dǎo)致全表掃描或性能下降

示例查詢（MySQL 8.0）：

SELECT * FROM employees WHERE department_id = 1; 

5.1.2 Trace 輸出分析

{
  "trace": [
    {
      "phase": "join_preparation",
      "table": "employees",
      "access_method": "ALL",
      "rows": 3
    },
    {
      "phase": "final_plan",
      "plan": [
        {
          "table": "employees",
          "access_method": "ALL",
          "rows_examined": 3
        }
      ]
    }
  ]
}

分析：

access_method=ALL 表示全表掃描

優(yōu)化器認(rèn)為全表掃描成本低于索引訪問，可能由于：

• 表數(shù)據(jù)量小
• 索引統(tǒng)計(jì)信息過時

5.1.3 調(diào)優(yōu)建議

-- 更新表統(tǒng)計(jì)信息
ANALYZE TABLE employees;
 
-- 再次執(zhí)行查詢，觀察 Trace 輸出
SELECT * FROM employees WHERE department_id=1;
 
-- 預(yù)期 Trace 輸出：
-- access_method=index
-- 使用 idx_dept 索引

場景2：成本估算偏差

5.2.1 問題描述

• 優(yōu)化器選擇的執(zhí)行計(jì)劃成本估算與實(shí)際執(zhí)行成本差距大
• 可能導(dǎo)致非最優(yōu)查詢計(jì)劃

示例：

SELECT * FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.id IN (1,2);

5.2.2 Trace 輸出分析

{
  "phase": "join_optimization",
  "join_order": ["departments","employees"],
  "cost": 0.05,
  "chosen_access": "index(idx_dept)"
}

分析：

• 優(yōu)化器選擇先掃描 departments 再掃描 employees
• 如果統(tǒng)計(jì)信息不準(zhǔn)確，實(shí)際掃描行數(shù)可能比估算大
• Trace 提供每個階段成本和行數(shù)估算，有助于定位問題

5.2.3 調(diào)優(yōu)建議

-- 更新表統(tǒng)計(jì)信息
ANALYZE TABLE employees;
ANALYZE TABLE departments;
 
-- 調(diào)整查詢邏輯或提示優(yōu)化器
SELECT /*+ JOIN_ORDER(d,e) */ e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.id IN (1,2);

場景3：子查詢優(yōu)化問題

5.3.1 問題描述

• 子查詢導(dǎo)致性能不佳，優(yōu)化器未轉(zhuǎn)換為 JOIN
• Trace 可以幫助分析子查詢執(zhí)行計(jì)劃

示例：

SELECT name
FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE name='HR'
);

5.3.2 Trace 輸出分析

{
  "phase": "join_optimization",
  "subquery": {
    "type": "IN",
    "rows": 1,
    "access_method": "ALL"
  },
  "final_plan": {
    "employees": {"access_method": "index", "rows_examined": 2}
  }
}

分析：

• 子查詢使用全表掃描，可能導(dǎo)致性能下降
• Trace 顯示優(yōu)化器未將子查詢轉(zhuǎn)換為半連接（semi-join）

5.3.3 調(diào)優(yōu)建議

-- 轉(zhuǎn)換為 JOIN
SELECT e.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name='HR';

Trace 輸出應(yīng)顯示：

• access_method=index
• 子查詢被消除，性能提升明顯

場景4：臨時表/文件排序問題

5.4.1 問題描述

• 查詢涉及 ORDER BY、GROUP BY 或 DISTINCT
• 優(yōu)化器使用臨時表或文件排序，影響性能

示例：

SELECT department_id, COUNT(*) 
FROM employees
GROUP BY department_id
ORDER BY COUNT(*) DESC;

5.4.2 Trace 輸出分析

{
  "phase": "final_plan",
  "plan": [
    {
      "table": "employees",
      "access_method": "index",
      "using_temporary": true,
      "using_filesort": true
    }
  ]
}

分析：

• using_temporary=true，using_filesort=true 表示臨時表 + 文件排序
• 對大表可能導(dǎo)致性能瓶頸

5.4.3 調(diào)優(yōu)建議

-- 使用覆蓋索引或調(diào)整查詢
CREATE INDEX idx_dept_count ON employees(department_id);
 
-- 執(zhí)行查詢并觀察 Trace 輸出
-- 預(yù)期不再使用文件排序，性能提升

5.5 小結(jié)

Optimizer Trace 能清晰展示優(yōu)化器每一步?jīng)Q策

通過 Trace 可定位：

• 索引未使用
• 成本估算不準(zhǔn)
• 子查詢優(yōu)化未生效
• 臨時表或文件排序影響性能

調(diào)優(yōu)流程：

• 查看 Trace
• 分析決策邏輯
• 調(diào)整索引或 SQL
• 驗(yàn)證 Trace 輸出變化

到此這篇關(guān)于MySQL優(yōu)化器追蹤(Optimizer Trace)的使用小結(jié)的文章就介紹到這了,更多相關(guān)MySQL優(yōu)化器追蹤內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！ 

最新評論