MySQL性能优化之join buffer

join buffer

众所周知,join buffer是多表join时MySQL为线程分配的空间，默认大小是256K，不同的索引结构和不同的join语句以及不同的数据量，都会影响MySQL的join算法。

create table test_join_a(
    id bigint(20) primary key auto_increment,
    name varchar(255),
    address varchar(255)
);


create table test_join_b(
    id bigint(20) primary key auto_increment,
    relation_id bigint(20),
    name varchar(255),
    address varchar(255)
);

创建如上两个表，表b逻辑关联表a的主键，分别查看表a做驱动表join表b，和表b做驱动表join表a的执行计划，可以发现,表b做驱动表时执行计划明显走了索引，而表a做驱动表时都走了全表扫描，这是因为表b的relation_id字段没有建立索引, 因此在使用a表做驱动表，查询表b的relation_id与a的id相等这一条件时没有可用索引。

create index idx_relation_id on test_join_b(relation_id);

接下来在表b的relation_id建立索引，重新执行表a驱动表b，可以看到这时候也可以走上索引，这种join算法叫index nested-loop join,在没有where条件过滤时，驱动表做全表扫描，被驱动表走索引扫描，假设驱动表示M行，被驱动表示M行，那么时间复杂度就是NlognM，n为多路搜索树的阶数。接下来比较一下大表join小表和小表join大表的差别，假设小表a1000行，大表b10000行，为方便计算，取搜索树阶数为10，小表join大表复杂度为1000 * lg10000 = 4000,而大表join小表复杂度为10000 * lg1000 = 30000,这个复杂度的差别还是非常明显的，因此表关联需要用小表join大表。

但是某些情况下，join关联字段没有索引，这时候就无法走树搜索，就回到了上面的全表扫描的情况。这时候，MySQL先取出驱动表数据放在join buffer，然后一行一行取被驱动表和驱动表做比较，匹配到满足条件的行则作为结果集的一部分，一直到完成全表扫描，这种join算法叫simple nested-loop join,整个复杂度是N * M, 因此如果是1000 行的驱动表和 10000行的被驱动表做关联，时间复杂度就是10000000。这个时间复杂度已经非常恐怖了，但这还不是最糟糕的，因为这10000000次都是内存判断。上面说了join buffer默认只有256K，如果驱动表非常大，join buffer放不下，那么一次就只能取一部分，然后取被驱动表数据进行比较，完成之后取下一部分，重复加在表b数据进行判断，直到完成整个过程，这个过程中判断次数依然是n * m,但是表b的数据会被多次加载到磁盘，消耗额外的磁盘io，因此这种join的效率更低,这种join方式叫block nested-loop join。了解了join的过程，那么想要优化就很显然了，一是尽可能在被驱动表join字段添加列索引，二是恰当地调大join buffer size的值，使驱动表可以加载更多数据。

总结

1.尽量使用index nested-loop join,即在被驱动列上添加索引。

2.适当设置join buffer size的值，使驱动表加载次数尽量的少。