判断方式
- 没有where条件时
左连接查询时前面的表是驱动表,后面的表是被驱动表
右连接时相反
内连接时哪张表数据少哪张表是驱动表 - 有where条件时
带where条件的是驱动表,否则是被驱动表
连接查询的算法
- 简单嵌套循环连接算法
一旦确定了驱动表和被驱动表,那么查询的过程就是双重循环
for (row1 : 驱动表) {
for (row2 : 被驱动表){
if (conidtion == true){
send client
}
}
}
- 索引嵌套循环连接法
join查询的优化思路就是小表驱动大表,在大表(被驱动表)上建立索引,如果驱动表创建了索引,mysql是不会使用的。
for (row1 : 驱动表) {
索引在被驱动表中命中,不用再遍历被驱动表了
}
- 基于块的连接嵌套循环算法
这个思想其实就是将一行变成了一批
块嵌套循环算法对外部循环中读取的行进行缓存,以减少必须读取内部循环中表的次数。
比如说将10行读入缓冲区并将缓冲区传递到下一个内部缓冲,那么就可以将内部循环中读取的每一行和缓冲区中所有的10行进行对比,这样内部表必须读取的次数就少了一个数量级。
MySql连接缓冲区大小控制由join_buffer_size参数控制
连接缓冲区的一些特征:- 只有无法使用索引时才会使用连接缓冲区
- 连接中只有感兴趣的列才存储在连接缓冲区中,而不是整个行
- 为每个可以缓冲的连接分配一个缓冲区,因此可以使用多个连接缓冲区来处理给定查询
- 在执行连接之前分配连接缓冲区,在查询完成后释放。所以在查询的的时候最好不要把 * 作为查询条件,而是需要什么字段就查询什么字段,这样缓冲区可以缓冲更多的行。
三种算法优先级
- 第一种算法忽略,MySql不会使用这种。
- 当被驱动表创建了索引,那么MySql肯定使用第二种算法。
- 当没有创建索引或者对驱动表创建了索引,肯定使用第三种方法。
驱动表和被驱动表的理解
不论对于 nest loop 还是hash join的方式,决定驱动表和被驱动表,需要综合表的数据量大小和表数据的适合访问方式(随机读取或顺序读取)来确定。
对于循环嵌套 nest loop的表联结方式
- 驱动表,即需要从驱动表中拿出来每条记录,去与被驱动表的所有记录进行匹配探测。
- 理解驱动表和被驱动表的差异,最本质的问题,需要理解顺序读取和随机读取的差异,内存是适合随机读取的,但是硬盘就不是,对于硬盘来说顺序读取的效率比较好。
- 驱动表,作为外层循环,若能只进行一次IO把所有数据拿出来最好,这就比较适合顺序读取,一次性批量的把数据读取出来,这里没考虑缓存等细节。
- 被驱动表,即里层循环,由于需要不断的拿外层循环传进来的每条记录去匹配,所以如果是适合随机读取的,那么效率就会比较高。如果表上有索引,实际上就意味着这个表是适合随机读取的。
- 如果表的数据量较大,且没有索引,那么就不适合多次的随机读取,比较适合一次性的批量读取,就应该作为驱动表。
对于哈希关联 hash join的表联结方式
通常应该选用小表作为被探测表优先放入内存中,然后读取被探测表并计算关联列的hash值与前者匹配,因此,被探测表适合顺序读取或关联列有索引实际已排序,是比较好的。