MySQL——强制索引优化背后的学习

Tarzan写bug

已于 2022-11-20 16:26:01 修改

阅读量265

点赞数

文章标签： mysql 数据库

于 2022-11-20 16:14:21 首次发布

本文链接：https://blog.csdn.net/weixin_39544175/article/details/127950226

版权

MySQL——强制索引优化背后的学习

1. 背景

某天，遇到一个神奇的SQL语句。神奇点如下：

同一条SQL语句，当参数不同时，查询速度相差很大；
在对该条SQL语句进行优化时，使用强制走某个索引后，查询速度从5s到0.05ms；

针对以上问题，记录下整个优化过程及背后的原理。

2. 慢SQL语句

以下为脱敏后的SQL语句：

SELECT
	a2.fieldmenu_id 
FROM
	a2
	INNER JOIN m2 ON a2.fieldmenu_id = m2.id 
	AND m2.client_id = '123' 
	AND m2.state = 1 
	AND m2.unable = 1 
	AND m2.type = 0
	INNER JOIN b2 ON a2.id = b2.fieldresources_id 
	AND b2.state = 1 
	AND b2.unable = 1
	INNER JOIN c2 ON b2.fieldrole_id = c2.fieldrole_id 
	AND c2.state = 1 
	AND c2.unable = 1 
	AND c2.user_id = 123456
	INNER JOIN d2 ON c2.fieldrole_id = d2.id 
	AND d2.state = 1 
	AND d2.unable = 1 
WHERE
	a2.state = 1 
	AND a2.unable = 1 
ORDER BY
	a2.fieldmenu_id;

对应表的索引：

a2: idx(fieldmenu_id)

b2: idx(fieldresources_id), idx(fieldrole_id)

c2: idx(fieldrole_id), idx(user_id), idx(fieldrole_id, state)

m2: idx(client_id)

3. 优化过程

1. explain

首先分析执行计划，发现都是用到了索引，但c2表用的是idx(user_id)索引，当时由于想到的是优化原则上说要用on条件的索引，所以使用了强制走idx(fieldrole_id, state)索引来尝试优化

SELECT
	a2.fieldmenu_id 
FROM
	a2
	INNER JOIN m2 ON a2.fieldmenu_id = m2.id 
	AND m2.client_id = '123' 
	AND m2.state = 1 
	AND m2.unable = 1 
	AND m2.type = 0
	INNER JOIN b2 ON a2.id = b2.fieldresources_id 
	AND b2.state = 1 
	AND b2.unable = 1
	INNER JOIN c2 force index(idx_fieldrole_id_state) ON b2.fieldrole_id = c2.fieldrole_id 
	AND c2.state = 1 
	AND c2.unable = 1 
	AND c2.user_id = 123456
	INNER JOIN d2 ON c2.fieldrole_id = d2.id 
	AND d2.state = 1 
	AND d2.unable = 1 
WHERE
	a2.state = 1 
	AND a2.unable = 1 
ORDER BY
	a2.fieldmenu_id;

发现查询速度从5s变为0.05ms，马上自称SQL优化大神，哈哈哈哈~

2. 意外发现

优化完之后，对于为什么查询速度会提升一直很疑惑，所以导了一份表结构和数据到本地数据库。结果既然在没有使用强制索引的情况下只需要0.05ms，马上意识到数据库版本问题。发现MySQL5.6只需要0.05ms，而MySQL5.7要5s，并且对比两者的执行计划发现用的索引和表执行的顺序有所不同。

3. 查看表执行顺序

当执行explain输出执行计划时，id列表示的就是表的执行顺序。id相同时，从上到下执行；id不同时，id大的先执行。

4. 为什么版本不同，执行顺序和使用的索引不同

因为查询优化器的存在，不同版本查询优化器逻辑有所不同。

5. 在MySQL5.7版本中，强制使用索引为什么更快

对比两条语句的执行计划，发现除了强制使用索引生效外，表的执行顺序也不同了。

6. 怎么查看查询优化器执行的整个过程？从中判断基于什么原因选择不同的表顺序和索引

这里就要学习optimizer_trace优化跟踪了，首先打开优化跟踪功能；

-- 查看优化跟踪是否开启（5.7版本默认关闭）
show variables like '%optimizer_trace%';
-- 开启优化跟踪
set optimizer_trace = "enabled=on"
-- 优化跟踪输入内存设置，不然只会输出一半
set optimizer_trace_max_mem_size = 1048576

需要分析的SQL和下面的优化跟踪输出语句同步执行（注意要一起执行，不然不会输出）：

select * from information_schema.OPTIMIZER_TRACE;

把结果中的TRACE列复制出来分析，JSON中的rows_estimation为每张表预计扫描行数以及使用的索引，considered_execution_plans为可能的表执行方案，其中主要关注rows和cost，代表这个节点扫描的行数和代价，将considered_execution_plans画成一棵树，并在每个节点标注cost，选择其中最小cost的树路径，对应执行计划中的表执行顺序，可以发现查询优化器是基于代价最低为原则选择表的执行顺序和索引的。

7. 为什么代价高的反而查询快

对比慢SQL1和使用强制索引的SQL2的代价，发现SQL1的代价低于SQL2的代价几倍，那为什么代价低的查询反而慢了呢？

代价主要由查询行数决定，而查询行数只是采样统计的预估值。这也就是为什么同一条语句，参数不同查询速度会不一样的原因，因为当参数不同导致数据样本数量不同时，会使预估值不一样。当预估值不一样时，MySQL可能没有选中最优的执行路径。

4. 总结

上述是对整个优化过程背后的学习，关注点在MySQL的查询优化器是基于代价最低原则去选择表的执行顺序和索引的，好SQL、坏SQL都会经常查询优化器的优化，那SQL语句优化是不是就没用了呢？这里我提出两点基于SQL语句层面的优化目标，第一是干预查询优化器，让查询优化器可以选择最优的查询路径；第二是让查询优化器执行优化的时间变短，这样整条语句执行时间就会变短。这两点都要熟悉查询优化器的原理才能反过来进行SQL语句优化，将在后面的文章中对查询优化器进行详细讲解。

世界那么大，感谢遇见，未来可期…

欢迎同频共振的那一部分人

作者公众号：Tarzan写bug

淘宝店：提供一元解决Java问题和其他方面的解决方案，欢迎私信