MySQL查詢性能優(yōu)化武器之鏈路追蹤

更新時間：2022年08月08日 08:38:31 作者：一燈架構(gòu)???????

這篇文章主要介紹了MySQL查詢性能優(yōu)化武器之鏈路追蹤，optimizer?trace優(yōu)化器追蹤，可以幫助我們查看優(yōu)化器生成執(zhí)行計劃的整個過程，以及做出的各種決策，包括訪問表的方法、各種開銷計算、各種轉(zhuǎn)換等

前言

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

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

好在MySQL提供了一個好用的分析工具 — optimizer trace（優(yōu)化器追蹤），可以幫助我們查看優(yōu)化器生成執(zhí)行計劃的整個過程，以及做出的各種決策，包括訪問表的方法、各種開銷計算、各種轉(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ù)目，默認是1條

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

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

2. 開啟optimizer trace

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

SET optimizer_trace="enabled=on";

3. 線上問題復現(xià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)建了兩個索引，分別是(name)和(gender,name)。

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

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

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

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

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

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

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

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

3. 使用optimizer trace

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

SELECT * FROM information_schema.OPTIMIZER_TRACE;

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

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

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

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

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

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