共计 1024 个字符,预计需要花费 3 分钟才能阅读完成。
导读:在日常的 MySQL 的 SQL 语句优化工作中, 总会遇到了各种各样的问题。今天就是遇到了一个比较诡异的问题,在这里记录下来方便自己的记忆。
MySQL 版本信息: MySQL 5.6.38
SQL 语句(其中的关键字信息已经做脱敏处理):
SELECT
id
, name, headurl, intro, gender, location, job, birthday, source,created_at FROM user WHERE name LIKE
'%name%'
ORDER BY created_at DESC LIMIT
0
,
100
;
2. 表 user 的表结构:
3. SQL 的执行计划和 profile 信息以及执行耗时:
4. 优化思路:在执行计划中可以看得到 SQL 语句由于是模糊查询所以并没有使用索引, 并且在执行 SQL 之后可以明显的看出在创建排序索引上面耗费了 99% 以上的时间, 我们在看整个的 SQL 语句,只有在字段 created_at 上面有做排序操作, 所以按照优化思路那么我们就需要在 created_at 这个字段上面创建索引。创建索引之后的表结构:
红框就是添加的索引信息。
5. 修改之后的 SQL 的执行计划和 profile 以及耗时信息:
在上面的执行计划进行比对我们可以很明显的看出来, 返回的数据由 450w 减少到了 100 行, 数据量大大的减少了;但是在执行 SQL 之后发现耗时居然更长了使用了 6s 多, 并且分析 profile 的时候发现在 sending data 耗时花费了 6.6s 的样子, 在这里解释一下 sending data 耗时指的的是从引擎层发送数据到 server 层或者是 client 层。
发现这种情况我感到很吃惊,我并不知道发生了什么事情导致这种结果。在多方查询无果之后我之后请教我的一个师兄,经过我详细的描述和实验, 他告诉我: 主要是由于在 where 条件过滤和排序的时候走索引没有查询到任何的结果导致 mysql 获取查询所有的索引然后在去回表进行全局扫描; 在没有添加的索引的情况下,SQL 直接就回回表不会进行全部的索引扫描。
为了验证这个结果, 我更改了 where 条件, 在没有添加 created_at 这个字段索引的情况下进行对比情况:
没有添加索引的耗时:
100
rows
in
set
(
2.53
sec)
添加索引的耗时:
100
rows
in
set
(
0.16
sec)
可以很明显的看到添加索引之后 速度提高了一大堆, 并且这个是有查询结果的。