共计 3063 个字符,预计需要花费 8 分钟才能阅读完成。
导读 | 用户日活百万级,注册用户千万级,而且若还没有进行分库分表,则该 DB 里的用户表可能就一张,单表上千万的用户数据。 |
某系统专门通过各种条件筛选大量用户,接着对那些用户去推送一些消息:
一些促销活动消息
让你办会员卡的消息
告诉你有一个特价商品的消息
通过一些条件筛选出大量用户,针对这些用户做推送,该过程较耗时 - 筛选用户过程。
用户日活百万级,注册用户千万级,而且若还没有进行分库分表,则该 DB 里的用户表可能就一张,单表上千万的用户数据。
对运营系统筛选用户的 SQL:
SELECT id, name
FROM users
WHERE id IN (
SELECT user_id
FROM users_extent_info
WHERE latest_login_time
一般存储用户数据的表会分为两张表:
存储用户的核心数据,如 id、name、昵称、手机号之类的信息,也就是上面 SQL 语句里的 users 表
存储用户的一些拓展信息,比如说家庭住址、兴趣爱好、最近一次登录时间之类的,即 users_extent_info 表
有个子查询,里面针对用户的拓展信息表,即 users_extent_info 查下最近一次登录时间
SELECT COUNT(id)
FROM users
WHERE id IN (
SELECT user_id
FROM users_extent_info
WHERE latest_login_time
然后内存里做个小批量,多批次读取数据的操作,比如判断如果在 1000 条以内,那么就一下子读取出来,若超过 1000 条,可通过 LIMIT 语句,每次就从该结果集里查 1000 条数据,查 1000 条就做次批量 PUSH,再查下一波 1000 条。
就是在千万级数据量大表场景下,上面 SQL 直接轻松跑出来耗时几十 s,不优化不行!
今天咱们继续来看这个千万级用户场景下的运营系统 SQL 调优案例,上次已经给大家说了一下业务背景 以及 SQL,这个 SQL 就是如下的一个:
SELECT COUNT(id) FROM users WHERE id IN (SELECT user_id FROM
users_extent_info WHERE latest_login_time
系统运行时,先 COUNT 查该结果集有多少数据,再分批查询。然而 COUNT 在千万级大表场景下,都要花几十 s。实际上每个不同的 MySQL 版本都可能会调整生成执行计划的方式。
通过:
EXPLAIN
SELECT COUNT(id)
FROM users
WHERE id IN (
SELECT user_id
FROM users_extent_info
WHERE latest_login_time
如下执行计划是为了调优,在测试环境的单表 2 万条数据场景,即使是 5 万条数据,当时这个 SQL 都跑了十多 s,注意执行计划里的数据量
执行计划里的第三行
先子查询,针对 users_extent_info,使用 idx_login_time 索引,做了 range 类型的索引范围扫描,查出 4561 条数据,没有做额外筛选,所以filtered=100%。
MATERIALIZED:这里把子查询的 4561 条数据代表的结果集进行了物化,物化成了一个临时表,这个临时表物化,一定是会把 4561 条数据临时落到磁盘文件里去的,这过程很慢。
第二条执行计划
针对 users 表做了一个全表扫描,在全表扫描的时候扫出来 49651 条数据,Extra=Using join buffer,此处居然在执行 join。
执行计划里的第一条
针对子查询产出的一个物化临时表,即做了个全表查询,把里面的数据都扫描了一遍。
为何对这临时表进行全表扫描? 让 users 表的每条数据都和物化临时表里的数据进行 join,所以针对 users 表里的每条数据,只能是去全表扫描一遍物化临时表,从物化临时表里确认哪条数据和他匹配,才能筛选出一条结果。
第二条执行计划的全表扫描结果表明一共扫到 49651 条,但全表扫描过程中,因为和物化临时表执行 join,而物化临时表里就 4561 条数据,所以最终第二条执行计划的filtered=10%,即最终从 users 表里也筛选出 4000 多条数据。
到底为什么慢
| 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 |
先执行了子查询查出 4561 条数据,物化成临时表,接着对 users 主表全表扫描,扫描过程把每条数据都放到物化临时表里做全表扫描,本质在做 join。
对子查询的结果做了一次物化临时表,落地磁盘,接着还全表扫描 users 表,每条数据居然跑到一个没有索引的物化临时表里,又做了一次全表扫描找匹配的数据。
对 users 表的全表扫描耗时吗?
对 users 表的每一条数据跑到物化临时表里做全表扫描耗时吗?
所以必然非常慢,几乎用不到索引。为什么 MySQL 会这样呢?
执行完上述 SQL 的 EXPLAIN 命令,看到执行计划之后,再执行:
显示出:
/* 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 和物化临时表导致的慢题,那怎么优化?
执行
SET optimizer_switch='semijoin=off'
关闭半连接优化,再执行 EXPLAIN 发现恢复为正常状态:
有个 SUBQUERY 子查询,基于 range 方式去扫描索引,搜索出 4561 条数据
接着有个 PRIMARY 类型主查询,直接基于 id 这个 PRIMARY 主键聚簇索引去执行的搜索
然后再把这个 SQL 语句真实跑一下看看,性能竟然提升了几十倍,仅 100 多 ms。
所以,其实反而是 MySQL 自动执行的 semi join 半连接优化,导致了极差性能,关闭即可。
生产环境当然不能随意更改这些设置,于是想了多种办法尝试去修改 SQL 语句的写法,在不影响其语义情况下,尽可能改变 SQL 语句的结构和格式,最终尝试出如下写法:
SELECT COUNT(id)
FROM users
WHERE (
id IN (
SELECT user_id
FROM users_extent_info
WHERE latest_login_time
上述写法下,WHERE 语句的 OR 后面的第二个条件,根本不可能成立,因为没有数据的 latest_login_time
![post-qrcode](https://www.xgss.net/wp-content/uploads/2023/07/二维码-微信公众号256x256.jpg)