千万级用户系统的SQL调优实战

2022-07-18 00:00:00 数据 执行 扫描 物化 表里


来源:https://blog.csdn.net/qq_33589510/article/details/123516429

1 案例引入

某系统需要对特定的大量用户推送一些消息:

  • 促销活动

  • 让你办卡

  • 有个特价商品

而首先要通过一些条件筛选出这些用户,而该过程很耗时!

日活百万,注册用户千万,而且若还未分库分表,则该DB里的用户表可能就一张,单表就上千万的用户数据。对该运营系统筛选用户的SQL:

SELECT id, name FROM users WHERE id IN (  SELECT user_id   FROM users_extent_info  # 查询近登录过的用户  WHERE latest_login_time < xx)

一般存储用户数据的表会分为两张表:

  • 存储用户的核心数据,如id、name、昵称、手机号,即users表

  • 存储用户的一些拓展信息,如家庭住址、兴趣爱好、近一次登录时间,即users_extent_info表

然后在外层查询,用 IN 子句查询 id 在子查询结果范围里的users表数据,此时该SQL突然会查出很多数据,可能几千、几万、几十万,所以执行此类SQL前,都会先执行count!

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

再在内存里再做小批量的批次读数据操作,比如判断:

  • 若结果在1k条内,就一下子读出来

  • 若超过1k条,可通过Limit语句,每次就从该结果集里查1k条,查1000条就做一次批量的消息Push,再查下一批次的1k条数据

但在千万级数据量的大表下,上面SQL竟然耗时几十s!

系统运行时,先Count该结果集有多少数据,再分批查询。然而Count在千万级大表场景下,都要花几十s。其实不同MySQL版本都可能会调整生成执行计划的方式。

通过:

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

如下执行计划是为了调优,在测试环境的单表2w条数据场景。即使5w条数据,当时这SQL都跑了十几s,注意执行计划里的数据量:

| 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 |
  • 先子查询,users_extent_info使用idx_login_time索引,做range类型的索引范围扫描,查出4561条数据,无额外筛选,所以filtered=。MATERIALIZED:这里把子查询的4561条数据代表的结果集物化成了一个临时表,这个临时表物化会将4561条数据临时落到磁盘文件,这过程很慢!

  • 第二条执行计划 对users表做了全表扫描,扫出49651条数据,Extra=Using join buffer,此处居然在执行join!

  • 执行计划里的条 对子查询产出的一个物化临时表做了个全表查询,把里面的数据都扫描了一遍。为何对该临时表执行全表扫描?让users表的每条数据都和物化临时表里的数据进行join,所以针对users表里的每条数据,只能是去全表扫描一遍物化临时表,从物化临时表里确认哪条数据和他匹配,才能筛选出一条结果。

第二条执行计划的全表扫描结果表明一共扫到49651条,但全表扫描过程中,因为和物化临时表执行join,而物化临时表里就4561条数据,所以终第二条执行计划的filtered=10%,即终从users表里也筛选出4000多条数据。

2到底为什么慢?

先执行了子查询查出4561条数据,物化成临时表,接着对users主表全表扫描,扫描过程把每条数据都放到物化临时表里做全表扫描,本质就是在join。

对子查询的结果做了一次物化临时表,落地磁盘,接着还全表扫描users表,每条数据居然还跑到一个无索引的物化临时表,又做了一次全表扫描找匹配数据。

对users表的全表扫描、对users表的每一条数据跑到物化临时表里做全表扫描都很耗时!所以后结果必然很慢,几乎用不到索引,难道MySQL疯了?

看完执行计划之后,我们可以再执行:

3 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+子查询 的操作,那这对users表不就是全表扫描了吗?

对users表里的每条数据,去对物化临时表全表扫描做semi join,无需将users表里的数据真的跟物化临时表里的数据join。只要users表里的一条数据,在物化临时表能找到匹配数据,则users表里的数据就会返回,这就是semi join,用来做筛选。

所以就是semi join和物化临时表导致的慢,那怎么优化?

4 做个实验

SET optimizer_switch='semijoin=off'

关闭半连接优化,再执行EXPLAIN发现恢复为正常状态:

  • 有个SUBQUERY子查询,基于range方式扫描索引,搜索出4561条数据

  • 接着有个PRIMARY类型主查询,直接基于id这个PRIMARY主键索引搜索

  • 然后再把这个SQL语句真实跑一下看看,性能竟然提升几十倍,仅100多ms。

所以,其实反而是MySQL自动执行的semi join半连接优化,导致极差性能,关闭之即可。

生产环境当然不能随意更改这些设置,于是想了多种办法尝试去修改SQL语句的写法,在不影响其语义情况下,尽可能改变SQL语句的结构和格式,终尝试出如下写法:

SELECT COUNT(id)FROM usersWHERE (    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后面的第二个条件,业务上根本不可能成立,所以不会影响SQL的业务语义,但改变SQL后,执行计划也会变,就不会再semi join优化了,而是常规地用了子查询,主查询也是基于索引。

所以核心的,还是看懂SQL执行计划,分析慢的原因,尽量避免全表扫描,用上索引!




相关文章