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

英雄寂寞 發表於 2023-5-22 00:00:00

<img title="千万级用户系统的SQL调优实战" alt="千万级用户系统的SQL调优实战" src="https://zhuji.jb51.net/uploads/img/202305/e81dd97b527f56ed94a77c8473eef35c.jpg">

某系统专门通过各种条件筛选大量用户，接着对那些用户去推送一些消息：
<ul data-id="ucd67dc5-INWU1FoD">
<li data-id="l20de63f-U5sQiljX">
一些促销活动消息</li>
<li data-id="l20de63f-vqMlIXrS">
让你办会员卡的消息</li>
<li data-id="l20de63f-2HCVUhu6">
告诉你有一个特价商品的消息</li>
</ul>

通过一些条件筛选出大量用户，针对这些用户做推送，该过程较耗时-筛选用户过程。

用户日活百万级，注册用户千万级，而且若还没有进行分库分表，则该DB里的用户表可能就一张，单表上千万的用户数据。

对运营系统筛选用户的SQL：
<pre>
SELECT id, name FROM users WHERE id IN ( SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx ) </pre>

一般存储用户数据的表会分为两张表：
<ul data-id="ucd67dc5-gbGAZGS2">
<li data-id="l20de63f-rHjglBBV">
存储用户的核心数据，如id、name、昵称、手机号之类的信息，也就是上面SQL语句里的users表</li>
<li data-id="l20de63f-rOIRFksG">
存储用户的一些拓展信息，比如说家庭住址、兴趣爱好、最近一次登录时间之类的，即users_extent_info表</li>
</ul>

有个子查询，里面针对用户的拓展信息表，即users_extent_info查下最近一次登录时间<某个时间点的用户，可以查询最近才登录过的用户，也可查询很长时间未登录的用户，然后给他们发push，无论哪种场景，该SQL都适用。

然后在外层查询，用id IN子句查询 id 在子查询结果范围里的users表的所有数据，此时该SQL突然会查出很多数据，可能几千、几万、几十万，所以执行此类SQL前，都会先执行count：
<pre>
SELECT COUNT(id) FROM users WHERE id IN ( SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx )</pre>

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

就是在千万级数据量大表场景下，上面SQL直接轻松跑出来耗时几十s，不优化不行!

今天咱们继续来看这个千万级用户场景下的运营系统SQL调优案例，上次已经给大家说了一下业务背景以及SQL，这个SQL就是如下的一个：
<pre>
SELECT COUNT(id) FROM users WHERE id IN (SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx)</pre>

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

通过：
<pre>
EXPLAIN SELECT COUNT(id) FROM users WHERE id IN ( SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx )</pre>

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

执行计划里的第三行

先子查询，针对users_extent_info，使用idx_login_time索引，做了range类型的索引范围扫描，查出4561条数据，没有做额外筛选，所以ﬁltered=100%。

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

第二条执行计划

针对users表做了一个全表扫描，在全表扫描的时候扫出来49651条数据，Extra=Using join buﬀer，此处居然在执行join。

执行计划里的第一条

针对子查询产出的一个物化临时表，即做了个全表查询，把里面的数据都扫描了一遍。

为何对这临时表进行全表扫描?让users表的每条数据都和物化临时表里的数据进行join，所以针对users表里的每条数据，只能是去全表扫描一遍物化临时表，从物化临时表里确认哪条数据和他匹配，才能筛选出一条结果。

第二条执行计划的全表扫描结果表明一共扫到49651条，但全表扫描过程中，因为和物化临时表执行join，而物化临时表里就4561条数据，所以最终第二条执行计划的ﬁltered=10%，即最终从users表里也筛选出4000多条数据。
<h4 data-id="h6e90be6-TAooXYkm" id="h6e90be6-TAooXYkm">
到底为什么慢</h4>
<pre>
| id | select_type | table | type | key | rows | ﬁltered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+--- | 1 | SIMPLE | | ALL | NULL | NULL | 100.00 | NULL | | 1 | SIMPLE | users | ALL | NULL | 49651 | 10.00 | Using where; Using join buﬀer(Block Nested Loop) | | 2 | MATERIALIZED | users_extent_info | range | idx_login_time | 4561 | 100.00 | NULL |</pre>

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

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

对users表的全表扫描耗时吗?

对users表的每一条数据跑到物化临时表里做全表扫描耗时吗?

所以必然非常慢，几乎用不到索引。为什么MySQL会这样呢?

执行完上述SQL的EXPLAIN命令，看到执行计划之后，再执行：
<h4 data-id="h6e90be6-gOTAedaB" id="h6e90be6-gOTAedaB">
show warnings</h4>

显示出：
<pre>
/* select#1 */ select count( d2. users . user_id `) AS COUNT(users.user_id)` from d2 . users users semi join xxxxxx</pre>

注意 semi join ，MySQL在这里，生成执行计划的时候，自动就把一个普通IN子句，“优化”成基于semi join来进行IN+子查询的操作。那对users表不是全表扫描了吗?对users表里每条数据，去对物化临时表全表扫描做semi join，无需将users表里的数据真的跟物化临时表里的数据join。只要users表里的一条数据，在物化临时表能找到匹配数据，则users表里的数据就会返回，这就是semi join，用来做筛选。

所以就是semi join和物化临时表导致的慢题，那怎么优化?
<h4 data-id="h6e90be6-Wf9Vf9Cd" id="h6e90be6-Wf9Vf9Cd">
做个实验</h4>

执行
<pre>
SET optimizer_switch='semijoin=oﬀ'</pre>

关闭半连接优化，再执行EXPLAIN发现恢复为正常状态：
<ul data-id="ucd67dc5-jlFFJAUI">
<li data-id="l20de63f-NUV4OH7N">
有个SUBQUERY子查询，基于range方式去扫描索引，搜索出4561条数据</li>
<li data-id="l20de63f-QRFRKMUQ">
接着有个PRIMARY类型主查询，直接基于id这个PRIMARY主键聚簇索引去执行的搜索</li>
<li data-id="l20de63f-ei0mPO86">
然后再把这个SQL语句真实跑一下看看，性能竟然提升了几十倍，仅100多ms。</li>
</ul>

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

生产环境当然不能随意更改这些设置，于是想了多种办法尝试去修改SQL语句的写法，在不影响其语义情况下，尽可能改变SQL语句的结构和格式，最终尝试出如下写法：
<pre>
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) )</pre>

上述写法下，WHERE语句的OR后面的第二个条件，根本不可能成立，因为没有数据的latest_login_time<-1，所以那不会影响SQL业务语义，但改变SQL后，执行计划也会变，就没有再semi join优化了，而是常规地用了子查询，主查询也是基于索引，同样达到几百ms 性能优化。

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

原文地址:https://www.toutiao.com/a7068935781741855262/

頁: [1]

琼殿技术论坛's Archiver

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