（1）前序：
根据SQL语句的执行计划找出速度慢的原因所在，想办法优化他的速度。
（2）分析执行计划：
回看下执行计划的内容：EXPLAIN SELECT COUNT(id) FROM users WHERE id IN (SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxx)
+----+-------------+-------+------------+-------+---------------+----------+---------+------+| id | select_type | table | type | key | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+----------+---------+------+| 1 | SIMPLE | <subquery2> | 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表的每条数据跑到物化临时表里做全表扫描，很耗时，然后跟物化临时表做join，join的时候还要全表扫描物化临时表。
执行完上述SQL的EXPLAIN命令，看到执行计划后，可以执行一下show warnings命令。这个show warnings 命令此时会显示出如下内容：
/ select#1 / select count(d2.users.user_id`) ASCOUNT(users.user_id)`
from ‘d2’.’users’ semi join xxxx 下面省略一段内容，因为可读性不高，大家关注的应该是这里的semi join这个关键字。
在MySQL生成执行计划的时候，自动把一个普通的IN语句，”优化”成了基于semi join来进行IN+子查询的操作，这个semi join什么意思？简单来说，对users表全表扫描了。对物化临时表全表扫描做semi join，不需要把users表里的数据 真的跟物化临时表里的数据 join上。只要users表里的一条数据，在物化临时表可以找到匹配的数据，那么users表里的数据就会返回，这就叫做 semi join，它是用来筛选的。
所以慢也就慢在这里，既然知道了是semi join和物化临时表导致的问题，应该如何优化呢？
可以先做一个小实验，SET optimizer_switch=’semijoin=off’，也就是关闭掉半连接优化，此时执行EXPLAIN命令看一下此时的执行计划，发现此时恢复为一个正常的状态。就是有一个基于SUBQUERY的子查询，基于range方式去扫描索引搜索出4561条数据，接着有一个PRIMARY类型的主查询，直接是基于id这个PRIMARY主键聚簇索引去执行的搜索，然后再把这个SQL语句真实执行下，发现性能提升。
因此到此为止，这个SQL的性能问题，真相大白，其实是自动执行的semi join半连接优化，导致了问题，一旦禁止掉semi join自动优化，用正常的方式让他基于索引去执行，性能恢复正常。
当然，生产环境不能随意更改这些配置，所以想了多种办法尝试修改SQL语句的写法，在不影响他语义的情况下，尽可能的去改变SQL语句的结构和格式，最终尝试出一种写法，如下所示：
SELECT COUNT(id) FROM users WHERE (
id IN (SELECT user_id FROM user_extent_info WHERE latest_login_time < xxxx )
OR
id IN (SELECT user_id FROM users_extent_info WHERE latest_login_time< -1 ) )
在上述写法下，WHERE语句的OR后面的第二个条件，根本是不可能成立的，因为没有数据的latest_login_time 是 小于-1的，所以那是不会影响SQL语义的，但是我们改变了SQL的写法之后，执行计划随之改变，他并没有再进行semi join 优化，而是正常的子查询，主查询也是基于索引去执行的，这样在线上的SQL语句，性能极大提升。这是将mysql的优化semi join操作优化为 子查询方式。
（3）总结：
从这个SQL调优案例里得到最核心的，还是看懂SQL的执行计划，然后去分析到底为什么会那么慢，接着就是想办法避免全表扫描之类的，一定让他用索引，用索引才是最重要的。
问题1：为何加了 or 就不走 join了？
解答： id in 两个结果集了，优化器不会去优化join操作
问题2：物化表性能怎么样？另外会自动对物化表创建主键索引嘛？如果创建是以什么为主键索引？
解答： 物化表效率很低，可能会有索引
问题3：
子查询的结果产生的物化表可能基于memory存储引擎存储，也可能是普通的B+聚簇索引的方式保存在磁盘，文中又说没有索引，mysql对这种没有索引的为什么不优化成让物化表的数据去users表里查询匹配？而是优化成了semi join 让users 表里去物化表全表扫描匹配？
解答：物化表semi join 筛选出来的行数少很多，小表驱动大表。
知识点：SQL优化使用了semi join的，但是会走硬盘化，没有主键id，但是增加了 or 后面的条件，改变了原有的执行计划，不走semi join。
知识点：
禁用semi join优化，执行计划是以物化表作为驱动表(小表作为驱动表),users表为被驱动表，join过程中extra中使用的是join buffer，即先将物化表放入到join buffer 中，然后被驱动表全表扫描逐行和join buffer中物化表进行比较，满足则返回。 行数据少的表作为驱动表，
知识点：
mysql执行计划有时也不是最优的，因为他优化的参考数据如行数都是大概，并不是一定精确，所以有时候按mysql本身的执行计划并不一定是最优解，实际上还要按实际环境对应调整。
若有收获，就点个赞吧
0 人点赞
