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用錯索引的原因，趕緊用起來吧！
文章持續更新，可以微信搜一搜「 一燈架構 」第一時間閱讀更多技術干貨，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/502077.html
標籤：其他
下一篇：mysql