MySQL基础优化(6)：高效join语句

有时来自一张表的数据不能满足查询需求，很多时候都涉及到多张表的连接查询。

首先创建测试表，

use test; 
drop table if exists t1; 
CREATE TABLE t1 ( 
id int(11) NOT NULL auto_increment,
a int(11) DEFAULT NULL,
b int(11) DEFAULT NULL,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
COMMENT '记录更新时间',
PRIMARY KEY (id),
KEY idx_a (a)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

drop procedure if exists insert_t1; 

delimiter ;;
create procedure insert_t1() /* 创建存储过程insert_t1 */
begin
declare i int; 
set i=1; 
while(i<=10000)do 
insert into t1(a,b) values(i, i); 
set i=i+1;
end while;
end;;
delimiter ;
call insert_t1(); /* 运行存储过程insert_t1 */
drop table if exists t2; 
create table t2 like t1; /* 创建表t2，表结构与t1一致 */
insert into t2 select * from t1 limit 100; /* 将表t1的前100行数据导入到t2 */

关联查询算法

MySQL嵌套循环算法有3种，

• Nested-Loop join（NLJ）算法
• Block Nested-Loop join（BNL）算法
• Batched Key Access算法，实际上是对NLJ算法的优化

1） NLJ算法

一次一行底从第一张表(驱动表)中读取数据行，在该行中获取关联字段，根据关联字段在另一张表(被驱动表)中取出满足条件的行，之后取出两张表的结果合集。

当关联字段在被驱动表中不存在索引时，每次驱动表取出数据在被驱动表中寻找对应数据的过程都是对被驱动表进行一次全表扫描。

所以MySQL只在关联字段在被驱动表中存在索引时，才会使用NLJ算法，所以一般默认情况下使用的是Index Nested-Loop join。

例如下面的SQL语句，

select * from t1 inner join t2 on t1.a = t2.a;

使用explain对该语句进行分析，结果如下，

• 驱动表示t2，被驱动表是t1。使用explain分析关联查询时。第一行的就是驱动表，选择t2作为驱动表的原因是在默认情况下，优化器选择小表作为驱动表。所以使用inner join时，语句中先出现的表不一定是驱动表
• 上面语句使用了NLJ算法。一般的join语句中，如果执行计划Extra项中没有出现 Using join buffer；则表示使用的算法是NLJ

上面语句执行过程如下，

1. 从表t2中读取一行数据
2. 从读取到的一行数据中取出关联字段a，在表t1中查找
3. 取出表t1中满足条件的行，跟t2中获得的结果进行合并，作为结果返回给客户端
4. 重复上面3个步骤

2）BNL算法

BNL算法的思想是：把驱动表的数据读取到join buffer中，之后扫描被驱动表，把被驱动表每一行数据取出来与join buffer中的数据进行比对，如果满足join的条件，就返回结果给客户端。

当关联字段在被驱动表中不存在索引时，优化器会选择BNL算法作为关联算法。

select * from t1 inner join t2 on t1.b = t2.b;

该语句的分析结果如下，

Extra项中已经说明 Using join buffer(Block Nested Loop)，说明该语句使用的是BNL算法。

上面SQL语句的执行流程如下，

1. 将t2的所有数据读取到 join buffer中
2. 将表t1中每一行数据取出，与join buffer中的数据进行比对
3. 返回满足条件的数据

3）BKA算法

BKA算法是对NLJ和BNL算法的结合，

• NLJ算法核心思想：被驱动表的关联字段存在索引
• BNL算法核心思想：将驱动表的数据批量提交到 join buffer中

BKA算法的原理

• 将驱动表中关联字段放入到 join buffer中
• 批量将关联字段的值发送到 Muti-Range Read(MRR)接口
• MRR收到值后对其主键ID进行排序，之后再进行数据读取操作
• 返回结果给客户端

MRR设计思路：查询辅助索引时，对查询结果先按照主键进行排序，按照主键排序后的顺序进行顺序查找，从而减少访问磁盘的次数

使用MRR时，Extra项会显示 Using MRR，optimizer_switch中 mrr_cost_based参数的值会影响MRR，

• 若mrr_cost_based=on表示优化器尝试在使用MRR和不使用MRR之间基于成本进行选择
• 若mrr_cost_based=off说明一直使用MRR

# 开启BKA
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

此时再看使用字段a进行关联查询时的执行计划，

explain select * from t1 inner join t2 on t1.a = t2.a;

返回结果如下，

Extra字段中 Using join buffer表示使用了BKA算法。

优化管理查询

1）关联字段添加索引

让BNL变为NLJ或BKA能够有效提升 join 的效率，因此建议在被驱动表的关联字段上添加索引。

2）小表做驱动表

NLJ算法会将驱动表中全部数据读取。若驱动表中总行数为$n$，则会将这$n$行数据都读取，同时遍历这些数据中关联字段的值。

依据驱动表中关联字段的值，当被驱动表中关联字段存在索引时，可依据该值得到相应行的数据。整个过程扫描的总行数为$2n$。

故当确定被关联字段在小表中存在索引时，建议使用小表作为驱动表，因为扫描的总行数与驱动表记录条目数是直接挂钩的。

可以使用straight_join语句显示指定前面的表为驱动表，

select * from t2 straight_join t1 on t1.a = t2.a;

此时表t2将被作为驱动表。

3）临时表

多数情况下可以通过在被驱动表的关联字段上添加索引 的方式让 join 使用NLJ或BKA算法。但是有时只因为一次临时查询对表中的字段添加索引可能会浪费资源。

此时可以使用临时表的方式，虽然临时表的建立会消耗空间，但是与BNL相比还是快很多。但是当数据量很大时，创建临时表的过程也是十分缓慢的。

创建如下临时表，

CREATE TEMPORARY TABLE t1_tmp (
  id int(11) NOT NULL AUTO_INCREMENT,
  a int(11) DEFAULT NULL,
  b int(11) DEFAULT NULL,
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
  PRIMARY KEY (id),
  KEY idx_a (a),
  KEY idx_b (b)
) ENGINE=InnoDB ;

insert into t1_tmp select * from t1;

原表t1中，字段b是不存在索引的。而创建的临时表t1_tmp中，字段b存在索引。

explain select * from t1 join t2 on t1.b= t2.b;

explain select * from t1_tmp join t2 on t1_tmp.b= t2.b;

分析结果如下，

第一条语句的Extra项中出现了Block Nested Loop的信息，说明使用了BNL算法。

语句2中并没有出现Block Nested Loop的信息，说明使用的是NLJ算法。