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多条数据。
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 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)
)
上述写法下，WHERE语句的OR后面的第二个条件，业务上根本不可能成立，所以不会影响SQL的业务语义，但改变SQL后，执行计划也会变，就不会再semi join优化了，而是常规地用了子查询，主查询也是基于索引。
所以最核心的，还是看懂SQL执行计划，分析慢的原因，尽量避免全表扫描，用上索引！
