快捷導(dǎo)航

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

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

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

前言

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

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

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

1. 查看optimizer trace配置

show variables like '%optimizer_trace%';

輸出參數(shù)詳解：

optimizer_trace 主配置，enabled的on表示開(kāi)啟，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. 開(kāi)啟optimizer trace

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

SET optimizer_trace="enabled=on";

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

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

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='用戶(hù)表';

創(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)無(wú)法排查分析，MySQL優(yōu)化器為什么使用了(name)上的索引，而沒(méi)有使用(gender,name)上的聯(lián)合索引。

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

3. 使用optimizer trace

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

SELECT * FROM information_schema.OPTIMIZER_TRACE;

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

QUERY 表示我們執(zhí)行的查詢(xún)語(yǔ)句

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

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

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

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