join语句的过程

如果有2个大小不同的表做join,应该用哪个表来做驱动表？

我们先设计2个相同的表结构，一个大表插入一万条数据，一个小表插入一千条数据。

CREATE TABLE `small_table` (
    `A` INT(11) NOT NULL,
    `B` INT(11) NULL DEFAULT NULL,
    `C` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`A`),
    INDEX `B` (`B`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

字段A是主键，字段B有索引，字段C无索引。

select * from small_table s straight_join big_table b  on (s.B =b.B);

在straight_join之前的是驱动表，之后的是被驱动表。

一：第一种情况：连接字段有索引

explain select * from small_table s straight_join big_table b  on (s.B =b.B);

这个执行过程是这样的：

1 从驱动表small_table中读入一行数据R；

2 从数据行R中，取出字段B到被驱动表big_table里去查找；

3 取出表big_table中满足条件的行，跟R组成一行，作为结果集的一部分；

4 重复执行步骤1到3，直到驱动表small_table的末尾循环结束。

结论：因为驱动表要走全表扫描，而被驱动表可以走索引，所以自然小表作为驱动表速度更快。

二：第二种情况：连接字段没有索引

explain select * from small_table s straight_join big_table b  on (s.C =b.C);

因为C字段是没有索引的，所以出现了Block Nested Loop的字样，他的执行流程是这样的:

1 把表small_table的数据读入内存join_buffer中，由于我们语句中的是select *,所以把整个small_table放入内存中；

2 扫描表big_table，把表big_table中的每一行取出来，跟join_buffer中的数据做判断，满足join条件的，作为结果集的一部分返回。

此时，两个表都做了一次全表扫描，所以总的扫描行数是1000+10000；内存中的判断次数是1000*10000，所以此时无论选择哪个作为驱动表，执行耗时是一样的。

另外一种情况：如果join_buffer_size不够大，放不下small_table的数据的话：

join_buffer的大小是由参数join_buffer_size设定的，默认为256k（256*1024=262144）

如果一次性放不下small_table的数据的话，那么只能分段放入，这就是Block的由来。

（join很慢的话，可以增加join_buffer_size的数值）

如果我们要分2次才能把small_table的数据放入内存中的话，那么第一次放入500行，第二次再放入500行，扫描的行数就是

第一次：500+500*10000

第二次：500+500*10000

两次总共就是1000+1000*10000；就是驱动表行数+驱动表行数*被驱动表行数，所以驱动表行数更少的话，总扫描的行数会更少。

所以结论是：小表当驱动表更好。

(因为在join执行之前，会先执行where条件语句，所以这里所谓的小表指的是在where条件语句过滤之后更小的表。特别地，

如果连接条件是在索引字段上进行的，那么更小的表指的是行数更少的表；如果连接条件是在无索引字段上进行的，那么

更小的表指的是数据量更小的表，可以尽量放入更多的数据在join_buffer_size中，使得block更少。)