前言

MySQL優(yōu)化器可以生成Explain執(zhí)行計(jì)劃，我們可以通過執(zhí)行計(jì)劃查看是否使用了索引，使用了哪種索引？

但是到底為什么會(huì)使用這個(gè)索引，我們卻無從得知。

好在MySQL提供了一個(gè)好用的分析工具 — optimizer trace（優(yōu)化器追蹤），可以幫助我們查看優(yōu)化器生成執(zhí)行計(jì)劃的整個(gè)過程，以及做出的各種決策，包括訪問表的方法、各種開銷計(jì)算、各種轉(zhuǎn)換等。

1. 查看optimizer trace配置

show variables like '%optimizer_trace%';

輸出參數(shù)詳解：

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

optimizer_trace_features 表示優(yōu)化器的可選特性，包括貪心搜索、范圍優(yōu)化等

optimizer_trace_limit 表示優(yōu)化器追蹤最大顯示數(shù)目，默認(rèn)是1條

optimizer_trace_max_mem_size 表示優(yōu)化器追蹤占用的最大容量

optimizer_trace_offset 表示顯示的第一個(gè)優(yōu)化器追蹤的偏移量

2. 開啟optimizer trace

optimizer trace默認(rèn)是關(guān)閉，我們可以使用命令手動(dòng)開啟：

SET optimizer_trace="enabled=on";

3. 線上問題復(fù)現(xiàn)

先造點(diǎn)數(shù)據(jù)備用，創(chuàng)建一張用戶表：

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='用戶表';

創(chuàng)建了兩個(gè)索引，分別是(name)和(gender,name)。

執(zhí)行一條SQL，看到底用到了哪個(gè)索引：

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

跟期望的一致，優(yōu)先使用了(gender,name)的聯(lián)合索引，因?yàn)閣here條件中剛好有gender和name兩個(gè)字段。

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

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

這次竟然用了(name)上面的索引，同一條SQL因?yàn)閭鲄⒉煌?，而使用了不同的索引?/p>

到這里，使用現(xiàn)有工具，我們已經(jīng)無法排查分析，MySQL優(yōu)化器為什么使用了(name)上的索引，而沒有使用(gender,name)上的聯(lián)合索引。

只能請(qǐng)今天的主角 —optimizer trace（優(yōu)化器追蹤）出場(chǎng)了。

3. 使用optimizer trace

使用optimizer trace查看優(yōu)化器的選擇過程：

SELECT * FROM information_schema.OPTIMIZER_TRACE;

輸出結(jié)果共有4列：

QUERY 表示我們執(zhí)行的查詢語句

TRACE 優(yōu)化器生成執(zhí)行計(jì)劃的過程（重點(diǎn)關(guān)注）

MISSING_BYTES_BEYOND_MAX_MEM_SIZE 優(yōu)化過程其余的信息會(huì)被顯示在這一列

INSUFFICIENT_PRIVILEGES 表示是否有權(quán)限查看優(yōu)化過程，0是，1否

接下來我們看一下TRACE列的內(nèi)容，里面的數(shù)據(jù)很多，我們重點(diǎn)分析一下range_scan_alternatives結(jié)果列，這個(gè)結(jié)果列展示了索引選擇的過程。

輸出結(jié)果字段含義：

• index 索引名稱
• ranges 查詢范圍
• index_dives_for_eq_ranges 是否用到索引潛水的優(yōu)化邏輯
• rowid_ordered 是否按主鍵排序
• using_mrr 是否使用mrr
• index_only 是否使用了覆蓋索引
• in_memory 使用內(nèi)存大小
• rows 預(yù)估掃描行數(shù)
• cost 預(yù)估成本大小，值越小越好
• chosen 是否被選擇
• cause 沒有被選擇的原因，cost表示成本過高

從輸出結(jié)果中，可以看到優(yōu)化器最終選擇了使用(name)索引，而(gender,name)索引因?yàn)槌杀具^高沒有被使用。

再也不用擔(dān)心找不到MySQL用錯(cuò)索引的原因，趕緊用起來吧！

到此這篇關(guān)于MySQL查詢性能優(yōu)化武器之鏈路追蹤的文章就介紹到這了,更多相關(guān)MySQL鏈路追蹤內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論