分析下Mysql子查询的各种情况及执行计划

---

前言

之前已经学习了单表查询语句和多表关联于的执行原理，也熟悉了在生成具体执行计划的时候，是如何根据成本计算去选择最优执行计划的，今天再来分析下Mysql子查询的各种情况及执行计划

sql场景举例

（一）select * from t1 where x1 = （select x1 from t2 where id =xxx）

这个sql语句执行的时候，其实会被拆分为两部分，第一个步骤就是先执行子查询，也即是 select x1 from t2 where id =xxx，，然后直接根据主键定位出一条数据的x1字段的值，接着再执行 select * from t1 where x1 = 子查询的结果集，而这个过程其实和之前单表查询的方式是一样的。

select * from t1 where x1 = （select x1 from t2 where t1.x2 =t2.x2）

这种情况下可以看到子查询里的where条件依赖于t1表的字段值，所以这种查询就会效率很低下，它需要遍历t1表的每一条数据，对每一条数据取出x2字段的值，放到子查询里去执行，找出t2表的某条数据的x1字段的值，再放到外层去判断，是否符合跟t1表的x1字段匹配

select * from t1 where x1 in （select x2 from t2 where x3 =xxx）

针对于这种带in的子查询可能会一开始想当然的以为先执行子查询，然后在针对t1表进行全表扫描。判断每一条数据是否在这个子查询的结果集里，但是这种方式的效率实在是太低了，

所以mysql本身会对这样的sql做优化，即先执行子查询，把查询到的数据都放到一个临时表里，也可以叫做物化表，意思就是把这个中间结果集做物化。这个物化表可能会基于memory存储引擎通过内存存放，如果结果集太大，那么可能采用普通的b+树聚簇索引的方式放到磁盘里面，但是无论如何，这个物化表都会建立索引，即这波中间数据写入物化表是有索引的。

那么紧接着是不是全局扫描t1表然后对每条数据都去物化表里根据索引快速查找是否在物化表里呢？

其实这里还可以优化就是说，假设t1表有50万条数据，而物化表只有500条数据，那么此时完全可以改成全表扫描物化表，然后对每一条数据的值都去t1表根据x1这个字段的索引进行查找，查找物化表的这个值是否在t1表的x1索引树里，如果在的话那么就符合条件，所以基于in语句的子查询方式，会按照上述进行优化