点击上方“Java基基”,选择“设为星标”
做积极的人,而不是积极废人!
每天 14:00 更新文章,每天掉亿点点头发...
源码精品专栏
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 users
WHERE 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=100%。MATERIALIZED:这里把子查询的4561条数据代表的结果集物化成了一个临时表,这个临时表物化会将4561条数据临时落到磁盘文件,这过程很慢!
第二条执行计划 对users表做了全表扫描,扫出49651条数据,Extra=Using join buffer,此处居然在执行join!
执行计划里的第一条 对子查询产出的一个物化临时表做了个全表查询,把里面的数据都扫描了一遍。为何对该临时表执行全表扫描?让users表的每条数据都和物化临时表里的数据进行join,所以针对users表里的每条数据,只能是去全表扫描一遍物化临时表,从物化临时表里确认哪条数据和他匹配,才能筛选出一条结果。
第二条执行计划的全表扫描结果表明一共扫到49651条,但全表扫描过程中,因为和物化临时表执行join,而物化临时表里就4561条数据,所以最终第二条执行计划的filtered=10%,即最终从users表里也筛选出4000多条数据。
基于 Spring Boot + MyBatis Plus + Vue & Element 实现的后台管理系统 + 用户小程序,支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能
项目地址:https://github.com/YunaiV/ruoyi-vue-pro
视频教程:https://doc.iocoder.cn/video/
2 到底为什么慢?
先执行了子查询查出4561条数据,物化成临时表,接着对users主表全表扫描,扫描过程把每条数据都放到物化临时表里做全表扫描,本质就是在join。
对子查询的结果做了一次物化临时表,落地磁盘,接着还全表扫描users表,每条数据居然还跑到一个无索引的物化临时表,又做了一次全表扫描找匹配数据。
对users表的全表扫描、对users表的每一条数据跑到物化临时表里做全表扫描都很耗时!所以最后结果必然很慢,几乎用不到索引,难道MySQL疯了?
看完执行计划之后,我们可以再执行:
基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 实现的后台管理系统 + 用户小程序,支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能
项目地址:https://github.com/YunaiV/yudao-cloud
视频教程:https://doc.iocoder.cn/video/
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执行计划,分析慢的原因,尽量避免全表扫描,用上索引!
欢迎加入我的知识星球,一起探讨架构,交流源码。加入方式,长按下方二维码噢:
已在知识星球更新源码解析如下:
最近更新《芋道 SpringBoot 2.X 入门》系列,已经 101 余篇,覆盖了 MyBatis、Redis、MongoDB、ES、分库分表、读写分离、SpringMVC、Webflux、权限、WebSocket、Dubbo、RabbitMQ、RocketMQ、Kafka、性能测试等等内容。
提供近 3W 行代码的 SpringBoot 示例,以及超 6W 行代码的电商微服务项目。
获取方式:点“在看”,关注公众号并回复 666 领取,更多内容陆续奉上。
文章有帮助的话,在看,转发吧。
谢谢支持哟 (*^__^*)