Mysql单表访问方法，索引合并，多表连接原理，基于规则的优化，子查询优化( 三 ) _生活百科

on
对于外连接，如果无法在被驱动表中找到符合on的记录，驱动表的记录还是会加入结果集中，对应的被驱动表的各个字段使用null填充。
如select * from user_detail d left join user u onu.id = d.id where d.detail like '%Dz%'查询结果如下
Mysql单表访问方法，索引合并，多表连接原理，基于规则的优化，子查询优化

文章插图
最后只有满足d.detail like '%Dz%'的记录才会加入到结果集。
对于下面的两个内连接查询语句

select * from user_detail d inner joinuser u on u.id = d.id and d.detail like '%Dz%'select * from user_detail d inner joinuser u on u.id = d.id where d.detail like '%Dz%'

查询结果如下：

文章插图

?说明在内连接中 on 等同于where
6.连接查询的原理上面我们复习了内连接外连接的基础知识，下面我们开始学习下mysql是如何进行连接查询的
6.1嵌套循环连接

选取驱动表，使用驱动表相关的过滤条件，选择代价最低的单表访问方法对驱动表进行单表查询
对上述查询中获取的每一条记录，都分别到被驱动表中查找匹配的记录。

文章插图
这样就将夺多表的连接查询，转化为多次单表查询，其中驱动表只需要访问依次，但是被驱动表需要访问多次，访问的次数取决于驱动表执行单表查询后的结果集具备多少条记录。
这或许就是为什么外连接建议使用小表join大表
6.2 使用索引加载连接速度在获取到驱动表的单表查询后的一条记录后，需要在被驱动中找到符合条件的记录
select * from a left join b on a.id=b.id where a.name like '%陈%' and b.age>10
首先是执行select * from a wherea.name like '%陈%' ,在a表中得到a.id=1和a.id=2的记录，然后去b中执行select * from b where b.id=1 and b.age>10 和select * from b where b.id=2 and b.age>10
如果id是b表的索引，这时候就可以使用到索引，或者age是b表的索引也同样可以使用到索引,由于查询的是*如果索引B+树叶子节点的内容无法覆盖，那么将进行回表
6.3 基于块的嵌套连接查询上面我们说到嵌套循环连接，每次在驱动表中获取到一条记录都需要去被驱动表中进行查询，如果驱动表查询到了很多条数据，被驱动表数据量很大，且无法使用到被驱动表的索引，那么需要对被驱动表进行多次全表扫描，导致IO代价非常大，所以需要减少被驱动表的访问次数
解决的办法便是，将被驱动表的记录加载到内存，一次性和驱动表中的多条记录进行匹配。mysql有一个join buffer,在执行连接查询的时候申请一块固定大小的内存，先把若干条驱动表结果集，转载join buffer中，然后开始扫描被驱动表，然后被驱动表的记录一次性与join buffer进行匹配，由于匹配是在内存中进行的，这样可以显著减少被驱动表的io代价。
Join buffer的大小可以通过join_buffer_size,如果实在无法在被驱动表上使用到较好的索引，mysql服务所在的机器内存比较大，可以调大对连接查询进行优化。
Join Buffer并不会存储驱动表的所有字段，只会存储涉及到查询条件的字段和查询列表中的字段，所以尽量不要使用select * 可以让join buffer存放更多的数据
五丶基于规则的优化&子查询优化1条件化简