1.1 Optimizer Trace簡(jiǎn)介

1.1 引入背景

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

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

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

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

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

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

1.3 適用場(chǎng)景

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

1.4 示例代碼：開(kāi)啟 Optimizer Trace 并執(zhí)行簡(jiǎn)單查詢(xún)

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

-- 開(kāi)啟 Optimizer Trace
SET optimizer_trace="enabled=on";
 
-- 創(chuàng)建測(cè)試表
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í)行簡(jiǎn)單查詢(xún)
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 工作原理與核心價(jià)值

2.1 Optimizer Trace 的工作階段

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

join_preparation

功能：初始化 JOIN 查詢(xún)的基本信息

內(nèi)容：

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

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

join_optimization

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

內(nèi)容：

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

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

final_plan

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

內(nèi)容：

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

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

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

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

Optimizer Trace 輸出主要字段說(shuō)明：

{
  "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
        }
      ]
    }
  ]
}

字段說(shuō)明：

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

2.3 Optimizer Trace 的核心價(jià)值

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

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

定位索引選擇問(wèn)題

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

分析成本估算偏差

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

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

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

結(jié)合 EXPLAIN 使用

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

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

環(huán)境：MySQL 8.0

-- 開(kāi)啟 Trace
SET optimizer_trace='enabled=on';
 
-- 創(chuàng)建測(cè)試表
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 查詢(xún)
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)化器識(shí)別了兩個(gè)表及索引信息。
• 在 join_optimization 階段，分析優(yōu)化器嘗試的連接順序和代價(jià)比較。
• 在 final_plan 階段，確認(rèn)最終選擇了 idx_dept 索引訪問(wèn) employees 表，并輸出預(yù)估掃描行數(shù)。
• 對(duì)比 EXPLAIN 輸出，驗(yàn)證 Trace 的決策與最終執(zhí)行計(jì)劃一致性。

3 配置參數(shù)詳解

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

3.1 optimizer_trace

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

類(lèi)型：字符串（枚舉 enabled=on|off 或 JSON 配置）

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

修改方法：

-- 開(kāi)啟 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：指定追蹤對(duì)象，可選 all、optimizer

max_elements：JSON 輸出中最大元素?cái)?shù)，超過(guò)會(huì)被截?cái)?/p>

3.2 optimizer_trace_features

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

類(lèi)型：字符串（逗號(hào)分隔）

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

可選值：

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

修改方法：

SET optimizer_trace_features='all'; 

3.3 max_optimizer_trace_elements

作用：限制 Trace 輸出的最大元素?cái)?shù)量

類(lèi)型：整數(shù)

默認(rèn)值：1000

修改方法：

SET max_optimizer_trace_elements=2000; 

影響：如果 Trace 輸出超過(guò)限制，JSON 會(huì)被截?cái)啵治霾煌暾?/p>

3.4 注意事項(xiàng)

系統(tǒng)級(jí)變量 vs 會(huì)話(huà)級(jí)變量

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

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

• 開(kāi)啟 optimizer_trace 時(shí)，如果 max_optimizer_trace_elements 太小，復(fù)雜查詢(xún)的 Trace 會(huì)被截?cái)?/li>
• 輸出 JSON 大量元素可能導(dǎo)致查詢(xún)慢，因此只在調(diào)試環(huán)境開(kāi)啟

對(duì)性能的影響

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

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

環(huán)境：MySQL 8.0，測(cè)試庫(kù)

-- 創(chuàng)建測(cè)試表
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);
 
-- 開(kāi)啟 Trace 并設(shè)置輸出全部詳細(xì)信息
SET optimizer_trace='enabled=on';
SET optimizer_trace_features='all';
SET max_optimizer_trace_elements=5000;
 
-- 執(zhí)行查詢(xún)
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í)行查詢(xún)查看差異
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'：只輸出表、訪問(wèn)方法、行數(shù)和成本
• 通過(guò)對(duì)比，可以清晰看到不同參數(shù)對(duì) Trace 輸出內(nèi)容的影響

3.6 小結(jié)

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

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

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

4.1 步驟 1：開(kāi)啟 Optimizer Trace

基本開(kāi)啟方法（會(huì)話(huà)級(jí)）：

SET optimizer_trace='enabled=on';

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

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

關(guān)閉 Trace：

SET optimizer_trace='enabled=off';

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

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

示例：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 查詢(xún)
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.id = 1;

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

4.3 步驟 3：查看 Trace 輸出

查詢(xún) Trace：

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G; 

輸出分析：

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

關(guān)鍵字段：

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

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

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

與 EXPLAIN 對(duì)比

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ì)劃，包含訪問(wèn)索引、連接類(lèi)型、掃描行數(shù)
• Optimizer Trace 提供優(yōu)化器選擇該計(jì)劃的完整邏輯

分析決策過(guò)程

查看 join_optimization 階段：

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

查看 final_plan 階段：

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

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

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

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

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

4.5 小結(jié)

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

5 典型場(chǎng)景分析

場(chǎng)景1：索引選擇錯(cuò)誤

5.1.1 問(wèn)題描述

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

示例查詢(xún)（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)為全表掃描成本低于索引訪問(wèn)，可能由于：

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

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

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

場(chǎng)景2：成本估算偏差

5.2.1 問(wèn)題描述

• 優(yōu)化器選擇的執(zhí)行計(jì)劃成本估算與實(shí)際執(zhí)行成本差距大
• 可能導(dǎo)致非最優(yōu)查詢(xún)計(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 提供每個(gè)階段成本和行數(shù)估算，有助于定位問(wèn)題

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

-- 更新表統(tǒng)計(jì)信息
ANALYZE TABLE employees;
ANALYZE TABLE departments;
 
-- 調(diào)整查詢(xún)邏輯或提示優(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);

場(chǎng)景3：子查詢(xún)優(yōu)化問(wèn)題

5.3.1 問(wèn)題描述

• 子查詢(xún)導(dǎo)致性能不佳，優(yōu)化器未轉(zhuǎn)換為 JOIN
• Trace 可以幫助分析子查詢(xún)執(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}
  }
}

分析：

• 子查詢(xún)使用全表掃描，可能導(dǎo)致性能下降
• Trace 顯示優(yōu)化器未將子查詢(xún)轉(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
• 子查詢(xún)被消除，性能提升明顯

場(chǎng)景4：臨時(shí)表/文件排序問(wèn)題

5.4.1 問(wèn)題描述

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

示例：

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 表示臨時(shí)表 + 文件排序
• 對(duì)大表可能導(dǎo)致性能瓶頸

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

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

5.5 小結(jié)

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

通過(guò) Trace 可定位：

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

調(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)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！ 

最新評(píng)論