Mysql 系列 | join 优化

上一篇中说了 join 语句的执行过程，了解了深层逻辑，则优化方案呼之欲出。

Multi-Range Read（MRR） 优化

select * from t1 where a>=1 and a<=100;

• 回表时，根据 ID 去主键索引 B+ 树上查到一行数据。

• 当回表时 ID 是随机的，就会出现随机访问，性能较差。如果能按照主键递增顺序查找，对磁盘读接近顺序读，能提升读性能。此时语句的执行流程如下：

  • 根据索引 a 定位到满足条件的数据，将 ID 放在 read_rnd_buffer

  • 将 buffer 中的 ID 进行递增排序

  • 排序后的 ID 依次去主键索引查找数据，并作为结果返回

• read_rnd_buffer 大小由 read_rnd_buffer_size 设定。如果查询过程中，buffer 满了，则先回表查询，再清空 buffer 继续查询 ID 再排序，再回表。

• 官方优化器策略，判断资源消耗时会放弃使用 MRR，这时需要设置 set optimizer_switch="mrr_cost_based=off" 就可以稳定使用 MRR。

• 用到 MRR 优化时，explain 中 Extra 中会显示 Using MRR 此时查到的结果集按照 ID 递增排序。

• 当按照条件查到的 ID 足够多，更能体现顺序查的优势。

Batched Key Access（BKA）算法

• BKA 算法是 NLJ 算法的优化

• NLJ 的逻辑，从驱动表 t1 一行行取出 a，再去被驱动表 t2 进行 join。每次在 t2 表只查一条数据。

• 如果能一次性多传值给 t2 表，从 t1 取出需要查询的数据先放在 join_buffer 中，不再一条条去 t2 进行 join。

• BKA 算法依赖于 MRR，则应该设置 set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'

BNL 转 BKA

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000

• BNL 算法对系统的影响较大

  • 会扫描大量的被驱动表数据，占用大量 IO 资源

  • 对比大量数据会占用较多 CUP 资源

  • 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率

• 通常情况下，在被驱动表字段创建索引，就可以直接转为 BKA 算法

• 如果被驱动表不方便加索引，则可以考虑使用临时表

  • 把 t2 表中满足条件的数据放在临时表 tmp_t

  • 给临时表的字段 b 加上索引

  • 让表 t1 和 tmp_t 进行 join

  create temporary table temp_t(id int primary key, a int, b int,  index(b))engine=innodb;
  insert into temp_t select * from t2 where b>=1 and b<=2000;
  select * from t1 join temp_t on (t1.b=temp_t.b);
  

• BKA 算法，Mysql 内置支持，建议默认使用。

hash join

• 如果 join buffer 中存的是 hash 表，则直接去 t2 中进行 hash 查找，不再需要进行大量判断，执行速度会快很多。

• Mysql 不支持 hash join，可以考虑在业务层实现

  • select * from t1; 取得 t1 的全表数据，在业务层存入一个 hash 结构（如 PHP 中的 dict）。

  • select * from t2 where b>=1 and b<=2000; 获取表 t2 中满足条件的 2000 行数据

  • 把 2000 行数据，一行行取到业务端，到 hash 结构的数据表中寻找匹配的数据。满足匹配条件的这行数据，就作为结果集的一行。

---

join 总是会消耗大量资源，执行前先 explain，进行合理优化提高性能！