MySQL JOIN原理 以及JOIN优化策略_mysql inner join优化

前言

在日常开发中，只要写了sql，难免会使用的join关键字连接两个或多个表，在这里还是先解释一下inner join、left join、right join之间的区别以及驱动表的概念。

INNER JOIN：查询两个表之间的交集

取值时遵循笛卡尔乘积，即利用双层循环遍历两个表的数据，举例有table1表和table2表，若table1的结果集比较少，那么就拿它当作外层循环，称为驱动表，外层循环每取一条数据，就拿该数据去内层循环table2表中匹配结果集，此时table2称为被驱动表

LEFT JOIN：取左表(驱动表)的全部数据，右表(被驱动表)如果有对应数据就显示，没有就为NULL

RIGHT JOIN：取右表(驱动表)的全部数据，左表(被驱动表)如果有对应数据就显示，没有就显示为NULL

JOIN连接查询原理

MySQL使用了一种算法去优化它：Nested-Loop Join（嵌套循环连接），但是这个算法有三个变种，分别是：

简单嵌套循环：连接比如有A表，B表，两个表JOIN的话 会拿着A表的连表条件 一条一条在B表循环，匹配A表和B表相同的id 放入结果集，这种效率是非常低的。

索引嵌套循环连接：连接表条件基础上加上索引，那么就直接拿这个字段的值去另一个表取值，这个算法就是根据索引进行的优化。

块索引嵌套连接：mysql使用了一个叫join buffer的缓冲区去减少循环次数，这个缓冲区默认是256KB，可以通过命令show variables like 'join_%'查看 其具体的做法是，将第一表中符合条件的列一次性查询到缓冲区中，然后遍历一次第二个表，并逐一和缓冲区的所有值比较，将比较结果加入结果集中

注意：只有当JOIN类型为ALL，index，rang或者是index_merge的时候才会使用join buffer，可以通过explain查看SQL的查询类型。

JOIN语句的优化

1. 用小结果集驱动大结果集，尽量减少join语句中的Nested Loop的循环总次数；

2. 优先优化Nested Loop的内层循环，因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能；

3. 对被驱动表的join字段上建立索引；

4. 当被驱动表的join字段上无法建立索引的时候，设置足够的Join Buffer Size。