⭐⭐⭐ Spring Boot 项目实战	⭐⭐⭐ Spring Cloud 项目实战
《Dubbo 实现原理与源码解析 —— 精品合集》	《Netty 实现原理与源码解析 —— 精品合集》
《Spring 实现原理与源码解析 —— 精品合集》	《MyBatis 实现原理与源码解析 —— 精品合集》
《Spring MVC 实现原理与源码解析 —— 精品合集》	《数据库实体设计合集》
《Spring Boot 实现原理与源码解析 —— 精品合集》	《Java 面试题 + Java 学习指南》

摘要: 原创出处 blog.csdn.net/qq_33589510/article/details/123516429 「JavaEdge.」欢迎转载，保留摘要，谢谢！

🙂🙂🙂关注**微信公众号：【芋道源码】**有福利：

RocketMQ / MyCAT / Sharding-JDBC 所有源码分析文章列表

RocketMQ / MyCAT / Sharding-JDBC 中文注释源码 GitHub 地址

您对于源码的疑问每条留言都将得到认真回复。甚至不知道如何读源码也可以请教噢。

新的源码解析文章实时收到通知。每周更新一篇左右。

认真的源码交流微信群。

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 | ﬁ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 |

先子查询，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多条数据。

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=oﬀ'

关闭半连接优化，再执行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执行计划，分析慢的原因，尽量避免全表扫描，用上索引！

芋道源码 —— 纯源码解析博客

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

1 案例引入

2 到底为什么慢？

3 show warnings

4 做个实验