文章说明
这篇文章主要是记录自己最近在真实工作中遇到的慢查询的案例, 然后进行调优分析的过程, 欢迎大家一起讨论调优经验.(以下出现的表名, 列名都是化名, 实际数据也进行过一点微调.)
PS: 最近做了一个面试题精选精答的开源项目, 如果想要了解更多 MySQL 相关的技术总结, 可以看一看, 如果对大家有帮助, 希望大家帮忙给一个 star, 谢谢大家了!
《面试指北》项目地址: https://github.com/NotFound9/interviewGuide
之前建了一个技术交流群, 大家感兴趣也可以进一下, 希望可以和大家一起学习进步!
一. 复杂的深分页问题优化
背景
有一个 article 表, 用于存储文章的基本信息的, 有文章 id, 作者 id 等一些属性, 有一个 content 表, 主要用于存储文章的内容, 主键是 article_id, 需求需要将一些满足条件的作者发布的文章导入到另外一个库, 所以我同事就在项目中先查询出了符合条件的作者 id, 然后开启了多个线程, 每个线程每次取一个作者 id, 执行查询和导入工作.
查询出作者 id 是 1111, 名下的所有文章信息, 文章内容相关的信息的 SQL 如下:
- SELECT
- a.*, c.*
- FROM
- article a
- LEFT JOIN content c ON a.id = c.article_id
- WHERE
- a.author_id = 1111
- AND a.create_time <'2020-04-29 00:00:00'
- LIMIT 210000,100
因为查询的这个数据库是机械硬盘的, 在 offset 查询到 20 万时, 查询时间已经特别长了, 运维同事那边直接收到报警, 说这个库已经 IO 阻塞了, 已经多次进行主从切换了, 我们就去 navicat 里面试着执行了一下这个语句, 也是一直在等待, 然后对数据库执行 show proceesslist 命令查看了一下, 发现每个查询都是处于 Writing to.NET 的状态, 没办法只能先把导入的项目暂时下线, 然后执行 kill 命令将当前的查询都杀死进程(因为只是客户端 Stop 的话, MySQL 服务端会继续查询).
然后我们开始分析这条命令执行慢的原因:
是否是联合索引的问题
当前是索引情况如下:
article 表的主键是 id,author_id 是一个普通索引
content 表的主键是 article_id
所以认为当前是执行流程是先去 article 表的普通索引 author_id 里面找到 1111 的所有文章 id, 然后根据这些文章 id 去 article 表的聚集索引中找到所有的文章, 然后拿每个文章 id 去 content 表中找文章内容等信息, 然后判断 create_time 是否满足要求, 进行过滤, 最终找到 offset 为 20000 后的 100 条数据.
所以我们就将 article 的 author_id 索引改成了联合索引 (author_id,create_time), 这样联合索引(author_id,create_time) 中的 B + 树就是先安装 author_id 排序, 再按照 create_time 排序, 这样一开始在联合 (author_id,create_time) 查询出来的文章 id 就是满足 create_time < '2020-04-29 00:00:00'条件的, 后面就不用进行过滤了, 就不会就是符合就不用对 create_time 过滤.
流程确实是这个流程, 但是去查询时, 如果 limit 还是 210000, 100 时, 还是查不出数据, 几分钟都没有数据, 一直到 navica 提示超时, 使用 Explain 看的话, 确实命中索引了, 如果将 offset 调小, 调成 6000, 100, 勉强可以查出数据, 但是需要 46s, 所以瓶颈不在这里.
真实原因如下:
先看关于深分页的两个查询, id 是主键, val 是普通索引
直接查询法
select * from test where val=4 limit 300000,5;
先查主键再 join
- select * from test a
- inner join
- (select id from test where val=4 limit 300000,5) as b
- on a.id=b.id;
这两个查询的结果都是查询出 offset 是 30000 后的 5 条数据, 区别在于第一个查询需要先去普通索引 val 中查询出 300005 个 id, 然后去聚集索引下读取 300005 个数据页, 然后抛弃前面的 300000 个结果, 只返回最后 5 个结果, 过程中会产生了大量的随机 I/O. 第二个查询一开始在普通索引 val 下就只会读取后 5 个 id, 然后去聚集索引下读取 5 个数据页.
同理我们业务中那条查询其实是更加复杂的情况, 因为我们业务的那条 SQL 不仅会读取 article 表中的 210100 条结果, 而且会每条结果去 content 表中查询文章相关内容, 而这张表有几个 TEXT 类型的字段, 我们使用 show table status 命令查看表相关的信息发现
Name | Engine | Row_format | Rows | Avg_Row_length |
---|---|---|---|---|
article | InnoDB | Compact | 2682682 | 266 |
content | InnoDB | Compact | 2824768 | 16847 |
发现两个表的数据量都是 200 多万的量级, article 表的行平均长度是 266,content 表的平均长度是 16847, 简单来说是当 InnoDB 使用 Compact 或者 Redundant 格式存储极长的 VARCHAR 或者 BLOB 这类大对象时, 我们并不会直接将所有的内容都存放在数据页节点中, 而是将行数据中的前 768 个字节存储在数据页中, 后面会通过偏移量指向溢出页.
(详细了解可以看看这篇文章深度好文带你读懂 MySQL 和 InnoDB)
这样再从 content 表里面查询连续的 100 行数据时, 读取每行数据时, 还需要去读溢出页的数据, 这样就需要大量随机 IO, 因为机械硬盘的硬件特性, 随机 IO 会比顺序 IO 慢很多. 所以我们后来又进行了测试,
只是从 article 表里面查询 limit 200000,100 的数据, 发现即便存在深分页的问题, 查询时间只是 0.5s, 因为 article 表的平均列长度是 266, 所有数据都存在数据页节点中, 不存在页溢出, 所以都是顺序 IO, 所以比较快.
- // 查询时间 0.51s
- SELECT a.* FROM article a
- WHERE a.author_id = 1111
- AND a.create_time < '2020-04-29 00:00:00'
- LIMIT 200100, 100
相反的, 我们直接先找出 100 个 article_id 去 content 表里面查询数据, 发现比较慢, 第一次查询时需要 3s 左右(也就是这些 id 的文章内容相关的信息都没有过, 没有缓存的情况), 第二次查询时因为这些溢出页数据已经加载到 buffer pool, 所以大概 0.04s.
- SELECT SQL_NO_CACHE c.*
- FROM article_content c
- WHERE c.article_id in(100 个 article_id)
解决方案
所以针对这个问题的解决方案主要有两种:
先查出主键 id 再 inner join
非连续查询的情况下, 也就是我们在查第 100 页的数据时, 不一定查了第 99 页, 也就是允许跳页查询的情况, 那么就是使用先查主键再 join 这种方法对我们的业务 SQL 进行改写成下面这样, 下查询出 210000, 100 时主键 id, 作为临时表 temp_table, 将 article 表与 temp_table 表进行 inner join, 查询出中文章相关的信息, 并且去 left Join content 表查询文章内容相关的信息. 第一次查询大概 1.11s, 后面每次查询大概 0.15s
- SELECT
- a.*, c.*
- FROM article a
- INNER JOIN(
- SELECT id FROM article a
- WHERE a.author_id = 1111
- AND a.create_time < '2020-04-29 00:00:00'
- LIMIT 210000 ,
- 100
- ) as temp_table ON a.id = temp_table.id
- LEFT JOIN content c ON a.id = c.article_id
优化结果
优化前, offset 达到 20 万的量级时, 查询时间过长, 一直到超时.
优化后, offset 达到 20 万的量级时, 查询时间为 1.11s.
利用范围查询条件来限制取出的数据
这种方法的大致思路如下, 假设要查询 test_table 中 offset 为 10000 的后 100 条数据, 假设我们事先已知第 10000 条数据的 id, 值为 min_id_value
select * from test_table where id> min_id_value order by id limit 0, 100, 就是即利用条件 id> min_id_value 在扫描索引是跳过 10000 条记录, 然后取 100 条数据即可, 这种处理方式的 offset 值便成为 0 了, 但此种方式有限制, 必须知道 offset 对应 id, 然后作为 min_id_value, 增加 id> min_id_value 的条件来进行过滤, 如果是用于分页查找的话, 也就是必须知道上一页的最大的 id, 所以只能一页一页得查, 不能跳页, 但是因为我们的业务需求就是每次 100 条数据, 进行分批导数据, 所以我们这种场景是可以使用. 针对这种方法, 我们的业务 SQL 改写如下:
- // 先查出最大和最小的 id
- SELECT min(a.id) as min_id , max(a.id) as max_id
- FROM article a
- WHERE a.author_id = 1111
- AND a.create_time <'2020-04-29 00:00:00'
- // 然后每次循环查找
- while(min_id<max_id) {
- SELECT a.*, c.* FROM article a LEFT JOIN content c ON a.id = c.article_id WHERE a.author_id = 1111 AND a.id> min_id LIMIT 100
- // 这 100 条数据导入完毕后, 将 100 条数据数据中最大的 id 赋值给 min_id, 以便导入下 100 条数据
- }
优化结果
优化前, offset 达到 20 万的量级时, 查询时间过长, 一直到超时.
优化后, offset 达到 20 万的量级时, 由于知道第 20 万条数据的 id, 查询时间为 0.34s.
二. 联合索引问题优化
联合索引其实有两个作用:
1. 充分利用 where 条件, 缩小范围
例如我们需要查询以下语句:
SELECT * FROM test WHERE a = 1 AND b = 2
如果对字段 a 建立单列索引, 对 b 建立单列索引, 那么在查询时, 只能选择走索引 a, 查询所有 a=1 的主键 id, 然后进行回表, 在回表的过程中, 在聚集索引中读取每一行数据, 然后过滤出 b = 2 结果集, 或者走索引 b, 也是这样的过程.
如果对 a,b 建立了联合索引(a,b), 那么在查询时, 直接在联合索引中先查到 a=1 的节点, 然后根据 b=2 继续往下查, 查出符合条件的结果集, 进行回表.
2. 避免回表(此时也叫覆盖索引)
这种情况就是假如我们只查询某几个常用字段, 例如查询 a 和 b 如下:
SELECT a,b FROM test WHERE a = 1 AND b = 2
对字段 a 建立单列索引, 对 b 建立单列索引就需要像上面所说的, 查到符合条件的主键 id 集合后需要去聚集索引下回表查询, 但是如果我们要查询的字段本身在联合索引中就都包含了, 那么就不用回表了.
3. 减少需要回表的数据的行数
这种情况就是假如我们需要查询 a>1 并且 b=2 的数据
SELECT * FROM test WHERE a> 1 AND b = 2
如果建立的是单列索引 a, 那么在查询时会在单列索引 a 中把 a>1 的主键 id 全部查找出来然后进行回表.
如果建立的是联合索引(a,b), 基于最左前缀匹配原则, 因为 a 的查询条件是一个范围查找(= 或者 in 之外的查询条件都是范围查找), 这样虽然在联合索引中查询时只能命中索引 a 的部分, b 的部分命中不了, 只能根据 a>1 进行查询, 但是由于联合索引中每个叶子节点包含 b 的信息, 在查询出所有 a>1 的主键 id 时, 也会对 b=2 进行筛选, 这样需要回表的主键 id 就只有 a>1 并且 b=2 这部分了, 所以回表的数据量会变小.
我们业务中碰到的就是第 3 种情况, 我们的业务 SQL 本来更加复杂, 还会 join 其他表, 但是由于优化的瓶颈在于建立联合索引, 所以进行了一些简化, 下面是简化后的 SQL:
- SELECT
- a.id as article_id ,
- a.title as title ,
- a.author_id as author_id
- from
- article a
- where
- a.create_time between '2020-03-29 03:00:00.003'
- and '2020-04-29 03:00:00.003'
- and a.status = 1
我们的需求其实就是从 article 表中查询出最近一个月, status 为 1 的文章, 我们本来就是针对 create_time 建了单列索引, 结果在慢查询日志中发现了这条语句, 查询时间需要 0.91s 左右, 所以开始尝试着进行优化.
为了便于测试, 我们在表中分别对 create_time 建立了单列索引 create_time, 对 (create_time,status) 建立联合索引 idx_createTime_status.
强制使用 idx_createTime 进行查询
- SELECT
- a.id as article_id ,
- a.title as title ,
- a.author_id as author_id
- from
- article a FORCE INDEX(idx_createTime)
- where
- a.create_time between '2020-03-22 03:00:00.003'
- and '2020-04-22 03:00:00.003'
- and a.status = 1
强制使用 idx_createTime_status 进行查询(即使不强制也是会选择这个索引)
- SELECT
- a.id as article_id ,
- a.title as title ,
- a.author_id as author_id
- from
- article a FORCE INDEX(idx_createTime_status)
- where
- a.create_time between '2020-03-22 03:00:00.003'
- and '2020-04-22 03:00:00.003'
- and a.status = 1
优化结果:
优化前使用 idx_createTime 单列索引, 查询时间为 0.91s
优化前使用 idx_createTime_status 联合索引, 查询时间为 0.21s
EXPLAIN 的结果如下:
id | type | key | key_len | rows | filtered | Extra |
---|---|---|---|---|---|---|
1 | range | idx_createTime | 4 | 311608 | 25.00 | Using index condition; Using where |
2 | range | idx_createTime_status | 6 | 310812 | 100.00 | Using index condition |
原理分析
先介绍一下 EXPLAIN 中 Extra 列的各种取值的含义
Using filesort
当 Query 中包含 ORDER BY 操作, 而且无法利用索引完成排序操作的时候, MySQL Query Optimizer 不得不选择相应的排序算法来实现. 数据较少时从内存排序, 否则从磁盘排序. Explain 不会显示的告诉客户端用哪种排序.
Using index
仅使用索引树中的信息从表中检索列信息, 而不需要进行附加搜索来读取实际行(使用二级覆盖索引即可获取数据). 当查询仅使用作为单个索引的一部分的列时, 可以使用此策略.
Using temporary
要解决查询, MySQL 需要创建一个临时表来保存结果. 如果查询包含不同列的 GROUP BY 和 ORDER BY 子句, 则通常会发生这种情况. 官方解释:" 为了解决查询, MySQL 需要创建一个临时表来容纳结果. 典型情况如查询包含可以按不同情况列出列的 GROUP BY 和 ORDER BY 子句时. 很明显就是通过 where 条件一次性检索出来的结果集太大了, 内存放不下了, 只能通过加临时表来辅助处理.
Using where
表示当 where 过滤条件中的字段无索引时, MySQL Sever 层接收到存储引擎 (例如 innodb) 的结果集后, 根据 where 条件中的条件进行过滤.
Using index condition
Using index condition 会先条件过滤索引, 过滤完索引后找到所有符合索引条件的数据行, 随后用 WHERE 子句中的其他条件去过滤这些数据行;
我们的实际案例中, 其实就是走单个索引 idx_createTime 时, 只能从索引中查出 满足 a.create_time between '2020-03-22 03:00:00.003' and '2020-04-22 03:00:00.003'条件的主键 id, 然后进行回表, 因为 idx_createTime 索引中没有 status 的信息, 只能回表后查出所有的主键 id 对应的行. 然后 innodb 将结果集返回给 MySQL Sever,MySQL Sever 根据 status 字段进行过滤, 筛选出 status 为 1 的字段, 所以第一个查询的 Explain 结果中的 Extra 才会显示 Using where.
filtered 字段表示存储引擎返回的数据在 server 层过滤后, 剩下多少满足查询的记录数量的比例, 这个是预估值, 因为 status 取值是 null,1,2,3,4, 所以这里给的 25%.
所以第二个查询与第一个查询的区别主要在于一开始去 idx_createTime_status 查到的结果集就是满足 status 是 1 的 id, 所以去聚集索引下进行回表查询时, 扫描的行数会少很多(大概是 2.7 万行与 15 万行的区别), 之后 innodb 返回给 MySQL Server 的数据就是满足条件 status 是 1 的结果集(2.7 万行), 不用再进行筛选了, 所以第二个查询才会快这么多, 时间是优化前的 23%.(两种查询方式的 EXPLAIN 预估扫描行数都是 30 万行左右是因为 idx_createTime_status 只命中了 createTime, 因为 createTime 不是查单个值, 查的是范围)
- // 查询结果行数是 15 万行左右
- SELECT count(*) from article a
- where a.post_time
- between '2020-03-22 03:00:00.003' and '2020-04-22 03:00:00.003'
- // 查询结果行数是 2 万 6 行左右
- SELECT count(*) from article a
- where a.post_time
- between '2020-03-22 03:00:00.003' and '2020-04-22 03:00:00.003'
- and a.audit_status = 1
发散思考: 如果将联合索引 (createTime,status) 改成 (status,createTime) 会怎么样?
- where
- a.create_time between '2020-03-22 03:00:00.003'
- and '2020-04-22 03:00:00.003'
- and a.status = 1
根据最左匹配的原则, 因为我们的 where 查询条件是这样, 如果是 (createTime,status) 那么索引就只能用到 createTime, 如果是 (status,createTime), 因为 status 是查询单个值, 所以 status,createTime 都可以命中, 在(status,createTime) 索引中扫描行数会减少, 但是由于 (createTime,status) 这个索引本身值包含 createTime,status,id 三个字段的信息, 数据量比较小, 而一个数据页是 16k, 可以存储 1000 个以上的索引数据节点, 而且是查询到 createTime 后, 进行的顺序 IO, 所以读取比较快, 总得的查询时间两者基本是一致. 下面是测试结果:
首先创建了 (status,createTime) 名叫 idx_status_createTime,
- SELECT
- a.id as article_id ,
- a.title as title ,
- a.author_id as author_id
- from
- article a FORCE INDEX(idx_status_createTime)
- where
- a.create_time between '2020-03-22 03:00:00.003'
- and '2020-04-22 03:00:00.003'
- and a.status = 1
查询时间是 0.21, 跟第二种方式 (createTime,status) 索引的查询时间基本一致.
Explain 结果对比:
id | type | key | key_len | rows | filtered | Extra |
---|---|---|---|---|---|---|
2 | range | idx_createTime_status | 6 | 310812 | 100.00 | Using index condition |
3 | range | idx_status_createTime | 6 | 52542 | 100.00 | Using index condition |
扫描行数确实会少一些, 因为在 idx_status_createTime 的索引中, 一开始根据 status = 1 排除掉了 status 取值为其他值的情况.
来源: https://www.cnblogs.com/notfound9/p/12928763.html