​官网​

Using join buffer (Block Nested Loop)

调优前

EXPLAIN SELECT qj.*,s.NAME,s.facultyName,s.className,s.sfzh,tcf.loginName AS teacherphone,bu.NAME AS teachernameFROM qingjia AS qjLEFT JOIN base_student AS s ON qj.stuId = s.stuIdLEFT JOIN teacherclassfaculty AS tcf ON s.className = tcf.className AND tcf.deptType = 1LEFT JOIN base_userinfo AS bu ON tcf.loginName = bu.phone ORDER BY qj.createtime DESC

Using join buffer (Block Nested Loop)是因为右表没有在join列上建索引导致嵌套循环。

添加被驱动表索引调优

ALTER TABLE base_userinfo ADD INDEX phone (phone);alter table teacherclassfaculty add index className(className);

Nested Loop Join原理

1.第一步筛选出驱动表符合条件的记录

-- 驱动表select * from 驱动表 where sql中where字段 = ''

2.通过连接条件on后的条件对被驱动表的数据筛选

-- 被驱动表select * from 被驱动表 where 被驱动表on关联字段 = '逐行驱动表记录字段数值' and 其他on条件;-- 被驱动表sselect * from base_student where stuId = 'qj_id';-- 被驱动表tcfselect * from teacherclassfaculty where className = 's_classname' and depttype = 1;

Nested Loop Join三种算法

NLJ是通过两层循环，用第一张表做Outter Loop，第二张表做Inner Loop，Outter Loop的每一条记录跟Inner Loop的记录作比较，符合条件的就输出。而NLJ又有3种细分的算法

1、​​Simple Nested Loop Join（SNLJ）​​

for each row in t1 matching range {  for each row in t2 matching reference key {    for each row in t3 {      if row satisfies join conditions, send to client    }  }}

SNLJ就是两层循环全量扫描连接的两张表，得到符合条件的两条记录则输出，这也就是让两张表做笛卡尔积，比较次数是R * S，是比较暴力的算法，会比较耗时，所以mysql查询优化器。

Index Nested Loop Join（INLJ）

INLJ是在SNLJ的基础上做了优化，通过连接条件确定可用的索引，在Inner Loop中扫描索引而不去扫描数据本身，从而提高Inner Loop的效率。

2、​​Block Nested Loop Join（BNLJ）​​

for each row in t1 matching range {  for each row in t2 matching reference key {    store used columns from t1, t2 in join buffer  -- 将之前的关联表存入buffer    if buffer is full {      for each row in t3 {        for each t1, t2 combination in join buffer {          if row satisfies join conditions, send to client        }      }      empty join buffer    }  }}-- 当有之前表join的buffer数据,则直接用t3作为outer去loopif buffer is not empty {  for each row in t3 {    for each t1, t2 combination in join buffer {      if row satisfies join conditions, send to client    }  }}