join的使用

Posted on | Edited on | In ,

MySql的join

我们在考虑sql语句用join的时候往往基于以下两点来看：

  1. 业务是否能用join
  2. 哪个表是驱动表

Index Nested-Loop Join（NLJ）

见下面的语句

1
2
# a有索引
select * from t1 straight_join t2 on (t1.a=t2.a);

这条语句的执行顺序如下：

  1. 表T读出一行数据R
  2. 用数据R到t2中找
  3. 取出t2中的一行作为结果集
  4. 重复1-3步骤,t1循环结束

这里先对是t1的全表扫描,t2查找相关数据，时间复杂度（N+N*2log2N 注意N是驱动表，2log2N是采用2级索引），如果是采用这种nlj算法，显然join会比用程序一行行循环查找要好。因为减少了和数据库的交互次数

同时因为时间复杂度是N+N*2log2N ，所以N越小越好，也就是驱动表是小表。

Block Nested-Loop Join（BNL）

如果join的字段如果没有所以呢?如:

1
select * from t1 straight_join t2 on (t1.a=t2.b);

这时候mysql为了防止多次扫描驱动表，会用到join_buffer，通过参数join_buffer_size，默认是256K

执行的过程如下：

  1. 扫描表t1，顺序读取t1，数据放到join_buffer中，如果超过join_buffer就放一部分
  2. 扫描表t2，把t2中的每一行取出来，跟join_buffer做对比，满足条件的当结果集返回
  3. 清空join_buffer
  4. 继续扫描表t1,执行步骤2-3直到结束。

当t1表数据不超过join_buffer_size时候，谁当驱动表都无所谓；否则的话小表当驱动表，减少写join_buffer次数。

BNL的坏处：

  1. 多次扫描被驱动表，增大磁盘的IO负担
  2. 内存中计算的时间复杂度可能是M*N,增大CPU负担
  3. 影响buffer_pool：对于大的冷数据表的join查询，可能会将这些数据移到buffer_pool的young区，影响业务的buff_pool命中率，而1、2在语句结束后会停止影响，可是3的影响是持续的需要后面业务主键淘汰掉这些数据页的buffer

优化建议

对于NLJ

可以开启mrr（multi-range-read）和bka(batch-key-access)

  • 原理是 原来是需要从驱动表找到一条数据，然后一条去被驱动表查询。且是随机读，速度很慢；
  • 开启mrr和bak
    • mrr原理是在查询的索引上做一次排序，然后把随机度改为顺序读。
    • bak是基于mrr，将join时候从驱动表中尽量多取出一些行（只保留必要字段），放到join_buffer中如果超过join_buffer_size会分多段，然后去被驱动表中查询。批量的顺序读
1
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

对于BNL

通过建立索引的方式转成NLJ的方式
对于不适合建立索引的，通过业务拆分优化