当前位置：首页 > Mysql > 正文内容

110 案例实战：千万级用户场景下的运营系统SQL调优（2）

CarlZhou3年前 (2021-05-12)Mysql9865

今天咱们继续来看这个千万级用户场景下的运营系统SQL调优案例，上次已经给大家说了一下业务背景以及SQL，这个SQL就是如下的一个：

SELECT COUNT(id) FROM users WHERE id IN (SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx)

之前说了，系统运行的时候，肯定会先跑一下COUNT聚合函数来查查这个结果集有多少数据，然后再分批查询。结果就是这个COUNT聚合函数的SQL，在千万级大表的场景下，都要花几十秒才能跑出来，简直是大跌眼镜，这种性能，系统基本就没法跑了！

所以我们今天一起来分析一下这个SQL的执行计划，不过这里要给大家提醒一点的是，因为不同的MySQL版本的执行计划可能都不一样，平时我们开发可能感觉不出来，但是实际上每个不同的MySQL版本都可能会调整生成执行计划的方式，所以同样的SQL在不同的MySQL版本下跑 ，可能执行计划都不太一样。

我们这里给出的执行计划是当时在我们的MySQL中得到的，可能大家自己拿同样的SQL去自己的MySQL里没法还原出来这里的执行计划，但是没关系，大家重点学的是执行计划分析的思路，以及如何从执行计划里看出性能问题所在，最后就是如何进行调优，重点是这个过程，没法还原出来执行计划，也是没关系的。

通过：

EXPLAIN SELECT COUNT(id) FROM users WHERE id IN (SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx)

可以得到下面的执行计划，我们为了方便大家看，把执行计划简化了几个字段，保留了最关键的几个字段。

另外，给大家提示一点，下面的执行计划是当时我们为了调优，在测试环境的单表2万条数据场景下跑出来的执行计划，即使是5万条数据，当时这个SQL都跑了十多秒，所以足够复现当时的生产问题了，所以大家注意下执行计划里的数据量问题。

+----+-------------+-------+------------+-------+---------------+----------+---------+------+

| id | select_type | table | type  | key | rows | filtered | Extra |

+----+-------------+-------+------------+-------+---------------+----------+---------+------+

| 1 | SIMPLE | <subquery2> | ALL | NULL | NULL | 100.00 | NULL  |

| 1 | SIMPLE | users | ALL | NULL | 49651 | 10.00 | Using where; Using join buffer(Block Nested Loop)  |

| 2 | MATERIALIZED | users_extent_info | range | idx_login_time | 4561 | 100.00 | NULL  |

+----+-------------+-------+------------+-------+---------------+----------+---------+------+

从上面的执行计划，我们可以清晰的看到这条SQL语句的一个执行过程

首先，针对子查询，是执行计划里的第三行实现的，他清晰的表明，针对users_extent_info，使用了idx_login_time这个索引，做了range类型的索引范围扫描，查出来了4561条数据，没有做其他的额外筛选，所以filtered是100%。

接着他这里的MATERIALIZED，表明了这里把子查询的4561条数据代表的结果集进行了物化，物化成了一个临时表，这个临时表物化，一定是会把4561条数据临时落到磁盘文件里去的，这个过程其实就挺慢的。

然后第二条执行计划表明，接着就是针对users表做了一个全表扫描，在全表扫描的时候扫出来了49651条数据，同时大家注意看Extra字段，显示了一个Using join buffer的信息，这个明确表示，此处居然在执行join操作？？？

接着看执行计划里的第一条，这里他是针对子查询产出的一个物化临时表，也就是<subquery2>，做了一个全表查询，把里面的数据都扫描了一遍，那么为什么要对这个临时表进行全表扫描呢？

原因就是在让users表的每一条数据，都要去跟物化临时表里的数据进行join，所以针对users表里的每一条数据，只能是去全表扫描一遍物化临时表，找找物化临时表里哪条数据是跟他匹配的，才能筛选出来一条结果。

第二条执行计划的全表扫描的结果表明是一共扫到了49651条数据，但是全表扫描的过程中，因为去跟物化临时表执行了一个join操作，而物化临时表里就4561条数据，所以最终第二条执行计划的filtered显示的是10%，也就是说，最终从users表里筛选出了也是4000多条数据。

这就是这条SQL语句的执行计划，不同MySQL版本可能是不一样的，甚至差别很大，所以大家没必要强求必须是要在自己本地可以还原出这个执行计划，但是大家重点是看明白我们这里对这个SQL语句的执行计划过程的一个分析。

扫描二维码推送至手机访问。

版权声明：本文由周飞的博客发布，如需转载请注明出处。

本文链接：http://szv5.cn/?id=127

返回列表

上一篇：109案例实战：千万级用户场景下的运营系统SQL调优（1）

下一篇：111 案例实战：千万级用户场景下的运营系统SQL调优（3）

相关文章

23 生产经验：在生产环境中，如何基于机器配置来合理设置Buffer Pool？

1、生产环境中应该给buffer pool设置多少内存？今天这篇文章我们接着上一次讲解的Buffer Pool的一些内存划分的原理，来给大家最后总结一下，在生产环境中到底应该如何设置Buffer Pool的大小呢。首先考虑第一个问题...

25 对于VARCHAR这种变长字段，在磁盘上到底是如何存储的？

1、一行数据在磁盘上存储的时候，包含哪些东西？上一讲我们已经告诉了大家，一行数据在磁盘上存储的时候，其实不仅仅是包含我们想象的那一点数据，他还包含了很多其他的信息，之前告诉大家，一行数据的存储格式大致如下所示。变长字段的长度列表，n...

30 用于存放磁盘上的多行数据的数据页到底长个什么样子？

30 用于存放磁盘上的多行数据的数据页到底长个什么样子？

之前我们老是给大家提到一个概念，就是数据页，大家都知道平时我们执行crud的时候，都会从磁盘上加载数据页到Buffer Pool的缓存页里去，然后更新了缓存页后，又会刷新回磁盘上的数据页里去。所以其实MySQL中进行数据操作的最小单位应...

32 一文总结初步了解到的MySQL存储模型以及数据读写机制

32 一文总结初步了解到的MySQL存储模型以及数据读写机制

今天我们来用一篇文章初步总结一下我们近期学习到的MySQL存储模型以及对应的读写机制，其实大家通过近期的学习也仅仅是初步了解了MySQL底层数据的存储模型而已，因为后续我们还要讲解MySQL的增删改查执行背后的深入底层的各种存储数据读写细节...

37 案例实战：RAID锂电池充放电导致的MySQL数据库性能抖动的优化

37 案例实战：RAID锂电池充放电导致的MySQL数据库性能抖动的优化

前面经过了几天的生产经验的一些铺垫，包括MySQL磁盘读写的机制，Linux存储系统的原理，RAID磁盘阵列的介绍，RAID锂电池定时充放电的原理，今天终于可以切入真正的生产案例的讲解了！其实只要大家理解了之前的内容，今天真正讲解案例的...

40 重新回顾redo日志对于事务提交后，数据绝对不会丢失的意义

40 重新回顾redo日志对于事务提交后，数据绝对不会丢失的意义

之前我们在给大家介绍了大量的MySQL底层原理知识之后，理论结合实践，给大家讲解了两个真实的生产环境的数据库优化案例，一个是数据库所在服务器的RAID存储系统的锂电池充放电导致的性能抖动问题，一个是数据库底层的linux操作系统的文件句柄限...

发表评论