MySQL查詢性能優化七種武器之鏈路追蹤

2022 年 8 月 17 日
筆記
java高頻面試題, MySQL

MySQL優化器可以生成Explain執行計劃，我們可以通過執行計劃查看是否使用了索引，使用了哪種索引？

但是到底為什麼會使用這個索引，我們卻無從得知。

好在MySQL提供了一個好用的工具 — optimizer trace（優化器追蹤），可以幫助我們查看優化器生成執行計劃的整個過程，以及做出的各種決策，包括訪問表的方法、各種開銷計算、各種轉換等。

1. 查看optimizer trace配置

show variables like '%optimizer_trace%';

輸出參數詳解：

optimizer_trace 主配置，enabled的on表示開啟，off表示關閉，one_line表示是否展示成一行

optimizer_trace_features 表示優化器的可選特性，包括貪心搜索、範圍優化等

optimizer_trace_limit 表示優化器追蹤最大顯示數目，默認是1條

optimizer_trace_max_mem_size 表示優化器追蹤佔用的最大容量

optimizer_trace_offset 表示顯示的第一個優化器追蹤的偏移量

2. 開啟optimizer trace

optimizer trace默認是關閉，我們可以使用命令手動開啟：

SET optimizer_trace="enabled=on";

3. 線上問題復現

先造點數據備用，創建一張用戶表：

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(100) NOT NULL COMMENT '姓名',
  `gender` tinyint NOT NULL COMMENT '性別',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_gender_name` (`gender`,`name`)
) ENGINE=InnoDB COMMENT='用戶表';

創建了兩個索引，分別是(name)和(gender,name)。

執行一條SQL，看到底用到了哪個索引：

select * from user where gender=0 and name='一燈';

跟期望的一致，優先使用了(gender,name)的聯合索引，因為where條件中剛好有gender和name兩個字段。

我們把這條SQL傳參換一下試試：

select * from user where gender=0 and name='張三';

這次竟然用了(name)上面的索引，同一條SQL因為傳參不同，而使用了不同的索引。

到這裡，使用現有工具，我們已經無法排查分析，MySQL優化器為什麼使用了(name)上的索引，而沒有使用(gender,name)上的聯合索引。

只能請今天的主角 —optimizer trace（優化器追蹤）出場了。

3. 使用optimizer trace

使用optimizer trace查看優化器的選擇過程：

SELECT * FROM information_schema.OPTIMIZER_TRACE;

輸出結果共有4列：

QUERY 表示我們執行的查詢語句

TRACE 優化器生成執行計劃的過程（重點關注）

MISSING_BYTES_BEYOND_MAX_MEM_SIZE 優化過程其餘的信息會被顯示在這一列

INSUFFICIENT_PRIVILEGES 表示是否有權限查看優化過程，0是，1否

接下來我們看一下TRACE列的內容，裏面的數據很多，我們重點分析一下range_scan_alternatives結果列，這個結果列展示了索引選擇的過程。

輸出結果字段含義：

index 索引名稱

ranges 查詢範圍

index_dives_for_eq_ranges 是否用到索引潛水的優化邏輯

rowid_ordered 是否按主鍵排序

using_mrr 是否使用mrr

index_only 是否使用了覆蓋索引

in_memory 使用內存大小

rows 預估掃描行數

cost 預估成本大小，值越小越好

chosen 是否被選擇

cause 沒有被選擇的原因，cost表示成本過高

從輸出結果中，可以看到優化器最終選擇了使用(name)索引，而(gender,name)索引因為成本過高沒有被使用。

再也不用擔心找不到MySQL用錯索引的原因，趕緊用起來吧！

文章持續更新，可以微信搜一搜「一燈架構」第一時間閱讀更多技術乾貨。

Tags: java高頻面試題 MySQL