1. 什么是optimizer_trace？

EXPLAIN命令可以展示SQL語句的最終執(zhí)行計劃，包括是否使用索引、表連接順序等信息，但它有一個明顯的局限性：只展示結(jié)果，不解釋原因。當(dāng)我們遇到執(zhí)行計劃不是最優(yōu)的情況時，僅憑EXPLAIN的結(jié)果很難分析優(yōu)化器為何會做出這樣的選擇。

optimizer_trace是MySQL提供的一項執(zhí)行計劃跟蹤功能，它可以跟蹤優(yōu)化器做出的各種決策（包括表訪問方式、開銷計算、各種轉(zhuǎn)換等），并將跟蹤結(jié)果以JSON格式記錄在INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。這使得我們能夠深入了解優(yōu)化器的工作機制，理解為什么選擇某個查詢計劃，查看替代計劃及其估計成本。

2. optimizer_trace的基本使用

2.1 啟用與配置

optimizer_trace默認是關(guān)閉的，因為它會產(chǎn)生一些額外開銷。不過，它是輕量級工具，開啟關(guān)閉簡便，且支持會話級別設(shè)置，對系統(tǒng)影響很小。

基本啟用方法：

-- 在會話中開啟optimizer_trace:cite[1]:cite[2]
SET SESSION optimizer_trace = "enabled=on";

-- 如果需要，還可以設(shè)置JSON格式和內(nèi)存大小:cite[4]:cite[8]
SET optimizer_trace="enabled=on",end_markers_in_json=on;
SET optimizer_trace_max_mem_size=1000000;

參數(shù)說明：

• optimizer_trace：控制是否開啟跟蹤功能

• end_markers_in_json：在JSON輸出中添加結(jié)束標(biāo)記，便于閱讀

• optimizer_trace_max_mem_size：設(shè)置跟蹤結(jié)果的最大內(nèi)存使用量，防止輸出過大被截斷

2.2 收集跟蹤信息

啟用optimizer_trace后，執(zhí)行需要分析的SQL語句，然后查詢優(yōu)化器跟蹤信息：

-- 執(zhí)行需要分析的SQL
SELECT * FROM users WHERE age > 25 AND salary < 50000;

-- 查看跟蹤結(jié)果:cite[1]:cite[2]
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G

2.3 關(guān)閉跟蹤

完成分析后，建議關(guān)閉optimizer_trace以避免不必要的性能開銷：

SET optimizer_trace = "enabled=off";

3. optimizer_trace輸出結(jié)構(gòu)詳解

optimizer_trace的輸出是一個龐大的JSON結(jié)構(gòu)，主要包含三個關(guān)鍵階段：

3.1 join_preparation（準(zhǔn)備階段）

這一階段主要進行語法解析與檢測，包括：

• 將外連接轉(zhuǎn)換成內(nèi)連接

• 合并視圖或派生表

• 處理子查詢轉(zhuǎn)換

• 消除常量和冗余表達式

示例輸出：

"join_preparation": {
  "select#": 1,
  "steps": [
    {
      "expanded_query": "/* select#1 */ select `users`.`id` AS `id`,`users`.`name` AS `name` from `users` where ((`users`.`age` > 25) and (`users`.`salary` < 50000))"
    }
  ]
}

此階段會將SQL語句中的*擴展為具體列，并添加對應(yīng)的表信息。

3.2 join_optimization（優(yōu)化階段）

這是優(yōu)化過程的核心階段，包含了查詢優(yōu)化的主要邏輯。此階段通過以下步驟生成高效的查詢執(zhí)行計劃(QEP)：

• 邏輯等價的查詢重寫(Query Rewrite)

• 基于成本的連接優(yōu)化(Cost-Based Join Optimization)

• 規(guī)則驅(qū)動的訪問路徑選擇(Rule-Based Access Path Selection)

此階段包含的關(guān)鍵子階段：

• condition_processing：條件處理，優(yōu)化WHERE和JOIN條件

• table_dependencies：分析表依賴關(guān)系

• ref_optimizer_key_uses：考慮ref類型索引使用

• rows_estimation：行數(shù)估算和成本分析

3.3 join_execution（執(zhí)行階段）

這是SQL語句的實際執(zhí)行階段，記錄執(zhí)行過程中的相關(guān)信息。

4. 關(guān)鍵分析部分：rows_estimation

在優(yōu)化階段，rows_estimation是最值得關(guān)注的部分之一，它深入分析了單表查詢的各種執(zhí)行方案的成本。

4.1 表掃描分析

"range_analysis": {www.ausxx.com

  "table_scan": {
    "rows": 10000,
    "cost": 2045.25
  },
  "potential_range_indexes": [m.ausxx.com

    {
      "index": "PRIMARY",
      "usable": false,
      "cause": "not_applicable"
    },
    {
      "index": "idx_age",
      "usable": true,
      "key_parts": ["age", "id"]
    }
  ],
  "best_covering_index_scan": {wap.ausxx.com

    "index": "idx_age",
    "cost": 1256.45,
    "chosen": falsetsl.ausxx.com

  }
}

4.2 索引選擇分析

優(yōu)化器會對比不同索引的成本，選擇最優(yōu)方案：

"analyzing_range_alternatives": {
  "range_scan_alternatives": [
    {
      "index": "idx_age",
      "ranges": ["25 < age"],
      "index_dives_for_eq_ranges": true,
      "rowid_ordered": false,
      "using_mrr": false,
      "index_only": false,
      "rows": 3500,
      "cost": 4201.5,
      "chosen": false,gov.ausxx.com

      "cause": "cost"govzb.ausxx.com

    }
  ]
}

5. 實際應(yīng)用案例

5.1 為什么查詢未使用索引？

一個常見的疑問是：為什么有索引但查詢沒有使用？ 通過optimizer_trace，我們可以看到優(yōu)化器基于成本評估做出的決策。

示例分析：假設(shè)有一個表，其中val列有索引，但查詢時未使用該索引。通過optimizer_trace的range_analysis部分，可以看到MySQL對比了全表掃描和使用val索引兩個方案的成本。

在這種情況下，即使使用索引可以減少掃描行數(shù)，優(yōu)化器可能仍然選擇全表掃描，原因通常是回表代價過高。當(dāng)查詢需要返回的列不在索引中時，使用索引查找需要額外的回表操作，如果回表數(shù)據(jù)量較大（通常超過表中約1/5的記錄），成本可能會超過全表掃描。

5.2 多表連接順序選擇

對于多表連接查詢，optimizer_trace的considered_execution_plans部分會展示各種連接順序和算法的成本比較，幫助理解優(yōu)化器為何選擇特定的連接順序。

6. 進階使用技巧

6.1 處理大型跟蹤結(jié)果

當(dāng)跟蹤結(jié)果很大時，可以將其導(dǎo)出到文件進行分析：

-- 將跟蹤結(jié)果導(dǎo)出到文件:cite[4]
SELECT TRACE INTO DUMPFILE "/tmp/test.trace" FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

6.2 權(quán)限考慮

optimizer_trace表有一個INSUFFICIENT_PRIVILEGES字段，表示是否有權(quán)限查看完整的優(yōu)化過程，通常為0，特殊情況下為1。

6.3 測試環(huán)境中的快捷使用

在測試環(huán)境中，可以使用特定的快捷方式啟用optimizer_trace，這相當(dāng)于手動存儲當(dāng)前值、開啟跟蹤、運行查詢、查看結(jié)果和恢復(fù)原值的過程。

7. 注意事項與最佳實踐

• 性能影響：雖然optimizer_trace是輕量級工具，但在生產(chǎn)環(huán)境中仍應(yīng)謹慎使用，分析完成后及時關(guān)閉

• 結(jié)果完整性：設(shè)置足夠的optimizer_trace_max_mem_size，避免結(jié)果因大小限制被截斷

• 統(tǒng)計信息準(zhǔn)確性：優(yōu)化器的決策依賴于統(tǒng)計信息的準(zhǔn)確性，定期更新統(tǒng)計信息可以獲得更可靠的跟蹤分析

• 結(jié)合其他工具：optimizer_trace應(yīng)與EXPLAIN、性能模式(Performance Schema)等工具結(jié)合使用，形成完整的性能分析體系

8. 總結(jié)

optimizer_trace是MySQL性能分析的強大工具，它揭開了查詢優(yōu)化器的神秘面紗，讓我們能夠：

• ? 深入理解優(yōu)化器的工作機制和決策過程

• ? 診斷執(zhí)行計劃選擇不合理的原因

• ? 驗證索引設(shè)計和查詢重寫的效果

• ? 學(xué)習(xí)優(yōu)化器如何權(quán)衡不同執(zhí)行計劃的成本

通過掌握optimizer_trace的使用方法和分析技巧，數(shù)據(jù)庫開發(fā)和管理人員可以更加精準(zhǔn)地定位和解決SQL性能問題，提升數(shù)據(jù)庫整體性能。

無論是調(diào)優(yōu)復(fù)雜查詢，還是理解MySQL優(yōu)化器的行為，optimizer_trace都是一個不可或缺的工具。下次當(dāng)你對MySQL的執(zhí)行計劃有疑問時，不妨打開optimizer_trace，深入探索優(yōu)化器的思考過程。

到此這篇關(guān)于MySQL性能分析利器之optimizer_trace使用的文章就介紹到這了,更多相關(guān)MySQL性能分析optimizer_trace內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論