MySQL：千万级用户场景下的运营系统SQL调优

现场

这是一个互联网公司的系统，这个互联网公司的用户量比较大，有百万日活用户的一个量级。

但这个互联网公司，有一个系统是专门通过各种条件筛选出大量的用户，接着对那些用户去推送一些消息的，有的时候可能是一些促销活动的消息，有的时候可能是办会员卡的消息，有的时候可能是告知特教商品的消息。

总而言之，其实通过一些条件可以筛选出大量的用户，接着针对这些用户做一些推送，是互联网公司的运营系统中比较常见的一种功能。这个过程，比较耗时的是筛选用户的这个过程。

因为这种胡文龙公司，往往用户是日活百万级的，注册用户是千万级的，而且如果还没有进行分库分表的话，那么这个数据库里的用户表可能就一张，单表里上上千万的用户数据，大概是这么一个情况。

现在我们来对运营系统筛选用户的SQL做一个简化：

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

上面的SQL语句是啥意思？

• 它的意思就是说一般存储用户数据的表会分为两张表，一个表用来存储用户的核心数据，比如id、name、昵称、手机号之类的信息，也就是上面SQL语句里的users表
• 另外一个表可能会存储用户的一些拓展信息，比如说家庭住址、兴趣爱好、最近一次登录时间之类的，就是上面的users_extent_info表
• 所以上面的SQL语句的意思就很明显了，有一个子查询，里面针对用户的拓展信息表，也就是users_extent_info查询了一下最近一次登录时间小于某个时间点的用户，这里其实可以是查询最近才登陆过的用户，也可以查询的是很长时间没登录过的用户 ，然后给他们发送一些push，无论哪种场景，这个SQL都是适用的
• 然后在外层的查询里，直接就是用了id IN子句情趣查询在子查询结果范围里的users表的所有数据，此时这个SQL往往一下子会查出来很多数据，可能几千、几万、几十万，都有可能，所以其实一般运行这类SQL之前，都会先跑一个count聚合函数，看看有多少条。如下：

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

• 然后内存里做一个小批量多批次读取数据的操作，比如判断如果在1000条以内，那么就一下子读取出来，如果超过1000条，可以通过LIMIT语句，每次就从这个结果集里查1000条数据，查1000条就做一次批量PUSH，再查下一波1000条。

那这个场景有什么问题呢？问题就是这个COUNT聚合函数的SQL在千万级数据量的大表场景下，直接跑出来耗时十几秒的速度。所以这个SQL不优化是不行了。

怎么优化呢？

第一步是获取执行计划：

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

在测试环境的单表2万条数据场景下跑出来的如下：

+----+-------------+-------+------------+-------+---------------+----------+---------+------+
| id | select_type | table | type | key | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+
| 1 | SIMPLE | | 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 ，表明了这里吧子查询的4531条数据代表的结构集进行了物化，物化成了一个临时表，这个临时表物化，一定是会把4561条数据临时落在磁盘文件里去的，这个过程其实就挺慢的
• 然后第二条执行计划表明，接着针对users表做了一个全表扫描，在全表扫描的时候扫出来了49651 条数据。根据extra字段的Using join buffer，这个信息明确表示，此时在执行join操作。
• 执行计划的第一条，这里它是针对子查询产出的一个物化临时表，也就是做了一个全表查询，把里面的数据都扫描一遍，那么为什么要对这个临时表进行全表扫描的
• 原因在于要让users表的每一条数据，都要去跟物化临时表里的数据进行join，所以针对users表里面的每一条数据，只能是全表扫描一遍物化临时表，找找临时表里哪条数据时跟他匹配的，才能筛选出来一条结果。
• 第二条执行计划的全表扫描的结果表明是一共扫到了49651条数据，但是全表扫描的过程中，因为去跟物化临时表执行了一个join操作，而物化临时表里就4561条数据，所以最终第二条执行计划的filtered显示的是10%，也就是说，最终从users表里筛选出了也是4000多条数据。

总的来说，这个SQL执行的过程就是先执行了子查询查出来的4561条数据，物化成了一个临时表，接着它对users主表做了一个全表扫描，扫描的过程中把每一条数据都放到物化临时表里去做全表扫描，本质再做join的事情。

那么这里为什么会跑这么慢呢？其实很明显，首先它对子查询的结构做了一次物化临时表，落地磁盘了，接着它还全表扫描了users表的所有数据，每一条数据居然跑到一个没有索引的物化临时表里再做一次全表扫描找匹配数据。

在这个过程中，对users表的全表扫描很耗时。对users表的每一条数据跑到无论临时表里做全表扫描也是很耗时的。所以这个过程非常慢，几乎就没怎么用到索引。

那么为什么会出现上述一个全表扫描users表，然后跟物化临时表做join，join的时候还要全表扫描物化临时表的过程？

这时候我们可以执行一下show warnings命令，显示出来的内容如下：/* select#1 */ select count( d2. users . user_id) AS COUNT(users.user_id)from d2 . users users semi join xxxxxx，下面省略一大段内容，因为可读性实在不高，大家关注的应该是这里的semi join这个关键字.

这里就显而易见了！MySQL在这里，生成执行计划的时候，自动就把一个普通的IN子句，“优化”成了基于semi join来进行IN+子查询的操作，这个semi join是什么意思呢？

简单来说，对users表不是全表扫描了么？对users表里每一条数据，去对物化临时表全表扫描做semijoin，不需要把users表里的数据真的跟物化临时表里的数据join上。只要users表里的一条数据，在物化临时表里可以找到匹配的数据，那么users表里的数据就会返回，这就叫做semi join，他是用来筛选的。

所以慢，也就慢在这里了，那既然知道了是semi join和物化临时表导致的问题，应该如何优化呢？

先别急，做个小实验，执行SET optimizer_switch=‘semijoin=off’，也就是关闭掉半连接优化，此时执行EXPLAIN命令看一下此时的执行计划，发现此时会恢复为一个正常的状态。

就是有一个SUBQUERY的子查询，基于range方式去扫描索引搜索出4561条数据，接着有一个PRIMARY类型的主查询，直接是基于id这个PRIMARY主键聚簇索引去执行的搜索，然后再把这个SQL语句真实跑一下看看，发现性能一下子提升了几十倍，变成了100多毫秒！

当然，在生产环境是不能随意更改这些设置的，所以后来我们想了一个办法，多种办法尝试去修改SQL语句的写法，在不影响他语义的情况下，尽可能的去改变SQL语句的结构和格式，最终被我们尝试出了一个写法，如下所示：

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

在上述写法下，WHERE语句的OR后面的第二个条件，根本是不可能成立的，因为没有数据的latest_login_time是小于-1的，所以那是不会影响SQL语义的，但是我们发现改变了SQL的写法之后，执行计划也随之改变。

他并没有再进行semi join优化了，而是正常的用了子查询，主查询也是基于索引去执行的，这样我们在线上上线了这个SQL语句，性能从几十秒一下子就变成几百毫秒了。

这个案例的主要问题是MySQL内部自动使用了半连接优化，结果半连接的时候导致了大量无索引的全表扫描，引发了性能急剧下降

总而言之，这里最核心的是，看懂SQL的执行计划，然后去分析到底他为什么会那么慢，接着你就是要想办法避免他全表扫描之类的操作，一定要让他去用索引，用索引是王道，是最重要的！