mysql因为字符集导致left join出现Using join buffer (Block Nested Loop)

1.执行计划

EXPLAIN
SELECT *
FROM 
t2
LEFT JOIN 
t1
ON
t1.bill_num = t2.bill_num

 

 

 这里出现了Using where; Using join buffer (Block Nested Loop) 50w数据查询了一两分钟没有查询出来，到底是为什么？

什么是：BNL

BNL(Block Nested-Loop Join)算法

NLJ的原理是内外两层循环，对外循环中的每条记录，都要再内循环中做一次检索。foreacht(){foreacht(){}}

什么是：BKA

BKA(Batched Key Access)算法

通过缓存外层循环读的行，来降低内层的读取次数。例如，10行数据读入buffer中，然后buffer被传递到内层循环，内层表读出的每一行都会跟这个缓存10行依次做对比，这样就降低了内层表数据的读取次数

2.增加两个表的bill_num索引

 

 再次查询还是不行。索引失效

3.索引为什么失效

1. 隐式转换导致索引失效
2. 随着表的增长，where条件出来的数据太多，大于15%，使得索引失效（会导致CBO计算走索引花费大于走全表）
3. 字符集不一致导致索引失效一个utf8一个utf8mb4

4.排查问题发现

两个表都执行

ALTER TABLE t1 CONVERT TO CHARSET utf8mb4;
ALTER TABLE t2 CONVERT TO CHARSET utf8mb4;

5.再次查看执行计划

EXPLAIN
SELECT *
FROM 
t2
LEFT JOIN 
t1
ON
t1.bill_num = t2.bill_num

查询一下