MySql查询优化--Join查询

目的

记录mysql数据库在日常开发过程中，多表关联查询相关的原理性知识及注意事项。

原理篇

Join算法

假设场景：
A表，a1有索引、a2无索引，行数：10。
B表，b1有索引、b2无索引，行数：1000。

• NLJ（Nested-Loop Join）
  满足条件： 被驱动表关联字段有索引；
  执行语句： select * from A left join B on A.a1 = B.b1;
  执行步骤：
  1、A（驱动表）扫描出10条（驱动数据）；
  2、每条驱动数据通过索引查表B；
  3、返回结果集。
  磁盘扫描次数： A * 10次 + B * 10次 = 20次

• BNL（Block Nested-Loop Join）
  满足条件： 被驱动表关联字段没有索引；
  执行语句： select * from A left join B on A.a2 = B.b2;
  执行步骤：
  1、A（驱动表）扫描出10条（驱动数据）；
  2、驱动数据放入Join Buffer内存中（如果超过join_buffer_size ，则进行多次存放，效率更差）；
  3、B表（被驱动表）扫描出1000条（被驱动数据）；
  4、每条被驱动数据去Join Buffer内存中匹配驱动数据；
  5、返回结果集。
  磁盘扫描次数： A * 10次 + B * 1000次 = 1010次
  内存中比对次数： 1000 * 10 = 10000次

谁是驱动表

• left join，左边是驱动表；
• right join，右表是驱动表；
• join，mysql自动根据行数判断驱动表；
• 可以强行指定驱动表。

使用篇

Join关联查询建议

• 小表驱动大表；（注：表的大小并非取决于表的总行数，取决于按where条件过滤后的总行数）
• 大表关联字段添加索引，尽量走NLJ；

判断sql最终使用了哪种Join算法

根据执行计划中Extra字段，如果是Using join buffer (Block Nested Loop)则为BNL，否则就是NLJ。