【MySQL 数据宝典】【索引原理】- 004 优化示例-join & in &exist

一、join 优化原理

1.1 基本连接方式介绍

JOIN 是 MySQL 用来进行联表操作的，用来匹配两个表的数据，筛选并合并出符合我们要求的结果集。

1.2 驱动表的定义

1.2.1 什么是驱动表

• 多表关联查询时,第一个被处理的表就是驱动表,使用驱动表去关联其他表.
• 驱动表的确定非常的关键,会直接影响多表关联的顺序,也决定后续关联查询的性能

1.2.2 选择驱动表的基本原则

在对最终的结果集没有影响的前提下,优先选择结果集最小的那张表作为驱动表

1.3 常见的 Join 算法

1.3.1 Simple Nested-Loop Join（ 简单的嵌套循环连接 )

简单来说嵌套循环连接算法就是一个双层for 循环 ，通过循环外层表的行数据，逐个与内层表的所有行数据进行比较来获取结果
这种算法是最简单的方案，性能也一般。对内循环没优化。

例子

-- 连接用户表与订单表 连接条件是 u.id = o.user_id
select * from user t1 left join order t2 on t1.id = t2.user_id;
-- user表为驱动表,order表为被驱动表

转化成代码的思路是:

for(user表行 uRow : user表){
    for(Order表的行 oRow : order表){
        if(uRow.id = oRow.user_id){
            return uRow;
        }
    }
}

匹配流程如下所示：

1.3.2 Index Nested-Loop Join（ 索引嵌套循环连接 ）

• Index Nested-Loop Join 其优化的思路:  主要是为了减少内层表数据的匹配次数 , 最大的区别在于，用来进行 join 的字段已经在被驱动表中建立了索引。
• 从原来的  匹配次数 = 外层表行数 * 内层表行数 , 变成了  匹配次数 = 外层表的行数 * 内层表索引的高度  ，极大的提升了 join的性能。
• 当  order  表的   user_id  为索引的时候执行过程会如下图：

注意：使用Index Nested-Loop Join 算法的前提是匹配的字段必须建立了索引。

1.3.3 Block Nested-Loop Join( 块嵌套循环连接 )

• 如果 join 的字段有索引，MySQL 会使用 INL 算法。如果没有的话，MySQL 会如何处理？
• 因为不存在索引了，所以被驱动表需要进行扫描。这里 MySQL 并不会简单粗暴的应用 SNL 算法，而是加入了 buffer 缓冲区，降低了内循环的个数，也就是被驱动表的扫描次数。

• 在外层循环扫描 user表中的所有记录。扫描的时候，会把需要进行 join 用到的列都缓存到 buff