MySQL查询性能优化七种武器之链路追踪

冷不防 2024-04-06 09:53 70阅读 0赞

MySQL优化器可以生成Explain执行计划，我们可以通过执行计划查看是否使用了索引，使用了哪种索引？

但是到底为什么会使用这个索引，我们却无从得知。

好在MySQL提供了一个好用的工具 — optimizer trace（优化器追踪），可以帮助我们查看优化器生成执行计划的整个过程，以及做出的各种决策，包括访问表的方法、各种开销计算、各种转换等。

1. 查看optimizer trace配置

show variables like '%optimizer\_trace%';

![45fc5296a8bf4e208b41d2df9c8b859a.png][]

输出参数详解：

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";

![c3599818728342929534bd53de9d02c5.png][]

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='一灯';

![3e732cc8d8964f54b58938056b4fab24.png][]

跟期望的一致，优先使用了(gender,name)的联合索引，因为where条件中刚好有gender和name两个字段。

我们把这条SQL传参换一下试试：

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

![5c1588734c754c29a5aeae1a8dc0b64e.png][]

这次竟然用了(name)上面的索引，同一条SQL因为传参不同，而使用了不同的索引。

到这里，使用现有工具，我们已经无法排查分析，MySQL优化器为什么使用了(name)上的索引，而没有使用(gender,name)上的联合索引。

只能请今天的主角 —optimizer trace（优化器追踪）出场了。

3. 使用optimizer trace

使用optimizer trace查看优化器的选择过程：

SELECT \* FROM information\_schema.OPTIMIZER\_TRACE;

![a77c2f6fc6b4421ba131fcee0f6a2d7e.png][]

输出结果共有4列：

QUERY 表示我们执行的查询语句  
TRACE 优化器生成执行计划的过程（重点关注）  
MISSING\_BYTES\_BEYOND\_MAX\_MEM\_SIZE 优化过程其余的信息会被显示在这一列  
INSUFFICIENT\_PRIVILEGES 表示是否有权限查看优化过程，0是，1否  
接下来我们看一下TRACE列的内容，里面的数据很多，我们重点分析一下range\_scan\_alternatives结果列，这个结果列展示了索引选择的过程。

![0a800830b0184762ad0ef84b176d1d4f.png][]  
输出结果字段含义：

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用错索引的原因，赶紧用起来吧！

[45fc5296a8bf4e208b41d2df9c8b859a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/e0c9373a09044a02b658a682e6cf9b77.png
[c3599818728342929534bd53de9d02c5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/32efcd18d92e4a20a2b90cb455817396.png
[3e732cc8d8964f54b58938056b4fab24.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/5d4ab9b96c184fc59bd651bf4ab59280.png
[5c1588734c754c29a5aeae1a8dc0b64e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/7eeddbb8e9254b3b88fd21912835f243.png
[a77c2f6fc6b4421ba131fcee0f6a2d7e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/3aef039e7ecf438ebbb8819be55c5c07.png
[0a800830b0184762ad0ef84b176d1d4f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/8f4d29913b694950b448f9e7dee110a4.png