MySQL---5.join

目录

一、join是否应该使用

1.可以使用被驱动表的索引（Index Nested-Loop Join）

2.被驱动表上无可用索引（Simple Nested-Loop Join）

3.被驱动表上无可用索引（Block Nested-Loop Join）

4.结论

二、join的优化

1.Multi-Range Read优化（MRR）

2.Batched Key Access（BKA），对NLJ算法进行优化

3.Batched Key Access（BKA），对BNL算法进行优化

三、join的疑问

1.如果使用left join的话，左边的表一定是驱动表吗？

2.如果两个表的join包含多个条件的等值匹配，是都写到on中还是只把一个条件写到on中，其他条件写到where部分？

---

 

例子：两个表都有一个主键id和一个普通索引a，字段b上无索引，t2中有1000行数据，t1中有100行数据

CREATE TABLE `t2` (  `id` int(11) NOT NULL,  `a` int(11) DEFAULT NULL,  `b` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `a` (`a`) ) ENGINE=InnoDB;     create table t1 like t2; insert into t1 (select * from t2 where id<=100)

一、join是否应该使用

1.可以使用被驱动表的索引（Index Nested-Loop Join）

select * from t1 straight_join t2 on (t1.a=t2.a)

如果直接使用join语句，MySQL优化器可能选择t1或者t2作为驱动表，

而使用straight_join可以让MySQL使用固定的连接方式执行查询，t1是驱动表，t2是被驱动表

t1作为驱动表，会全表扫描，被驱动表t2的字段a上有索引，join过程会用到这个索引

①执行流程

a）从表t1中读入一行数据R

b）从数据行R中，取出a字段去表t2里去查找

c）取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分

d）重复执行上面的三个步骤，直到表t1循环查询结束

②扫描行数

a）对于驱动表t1做了全表扫描，这个过程需要扫描100行

b）对于t1中扫描出来的每一行，根据a字段再去t2中再去查找，如果数据一一对应，每次扫描t2都只会扫描1行，也是总共扫描100行

c）所以，整个执行过程，总扫描行数是200

如果不使用join，那么需要先查询出t1所有的数据（select * from t1），再根据a字段去t2字段查询（select * from t2 where a = $a）,

需要执行1+100条SQL语句，和使用join只执行一条SQL相比，多了100次交互

 

结论：在使用被驱动表的索引前提下

①使用join语句，性能比拆成多个单表执行SQL语句的性能要好

②如果使用join语句的话，需要让小表做驱动表（驱动表走全表扫描，被驱动表走树索引）

2.被驱动表上无可用索引（Simple Nested-Loop Join）

select * from t1 straight_join t2 on (t1.a=t2.b);

由于t2的字段b上无索引，因此每次去t2匹配的时候，就要做一次全表扫描

如果t1和t2都是10万行的表，那么就会需要扫描10万*10万=10亿行，MySQL没有使用这种笨重的算法，而是使用了下面这种

3.被驱动表上无可用索引（Block Nested-Loop Join）

select * from t1 straight_join t2 on (t1.a=t2.b);

①算法的流程：

a）把表t1的数据读入到线程内存join_buffer中，由于写的是select *，因此把整个表t1放入到内存中（如果有关于t1的where条件，则先过滤再放入内存中）

b）扫描表t2，把表t2中的每一行取出来，和join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回

②扫描行数：

在这个过程中，对表t1和t2都做了一次全表扫描，因此总得扫描行数为1100。

由于join_buffer中的数据是以无序数组的方式组织的，因此对于表t2中的每一行，都要做100次判断（判断所有t1数据），

总共需要在内存中判断 100*1000=10万次。虽然上面的算法也是扫描相同行数，但是这个算法是内存操作，速度快很多，性能也更好

 

③join_buffer过小，放不下表t1全部数据：

join_buffer的大小是由join_buffer_size设定的，默认值是256k，如果放不下表t1的所有数据，那么就会分段放，执行过程变成下面这样

a）扫描表t1，顺序读取数据行放入join_buffer中，当放了88行join_buffer满了，继续下一步

b）扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据对比，满足join条件的，作为结果集的一部分返回

c）清空join_buffer

d）继续扫描表t1，顺序读取最后的12行数据放入到join_buffer，继续执行b操作

使用小表做驱动表，可以减少被驱动表的扫描次数（上面需要扫描两次t2表），join_buffer越大，一次可以放入的行越多，分成的段数越少，对被驱动表的全表扫描次数越少

 

注：Simple Nested Loop Join算法，其实也是把数据读到内存中，再按照匹配条件进行判断，但是和 Block Nested-Loop Join 性能差距有些大

①SNL在对被驱动表全表扫描的时候，如果数据没在buffer pool中，就需要从磁盘中进行读取，而且被驱动表会被多次访问， 很容易将这些数据页放到Buffer Pool的头部，影响正常业务的Buffer Pool命中率

②即使被驱动表的数据都在内存中，每次查找"下一个记录的操作"，都类似指针操作，而join buffer中的是数组，遍历成本更低

 

4.结论

①如果可以使用被驱动表上的索引，是可以使用join

②如果不能使用被驱动表上的索引，尤其是大表上的join，可能会导致扫描被驱动表很多次，会占用大量的系统资源，此时尽量不要用join

③如果使用join，应该选择小表（在两个表按照各自条件过滤后，计算参与join的各个字段的总数据量，数据量小的那个表，就是小表）

④导致的问题：

如果被驱动表是大表，并且是一个冷数据表，除了查询过程中可能会导致IO压力大，还会导致系统响应变慢

原因：如果驱动表分段，那么被驱动表就会被多次读取，而被驱动表又是大表，循环读取的间隔大概率会超过1秒，而当数据页在LRU_OLD的存在时间超过1秒，

就会被移动young区，会把大部分热点数据都淘汰掉，从而导致buffer pool命中率低，需要重新读取磁盘，系统响应变慢

二、join的优化

1.Multi-Range Read优化（MRR）

目的：尽量使用顺序读盘

例子：select * from t1 where a >=1 and a<= 100;（a=1001-id，a倒序）

①优化点

如果随着a的值递增顺序查询的话，id的值就变成随机的，那么就会出现随机访问，性能比较差。

虽然按行查这个机制不能改，但是调整查询的顺序，还是可以加速的

因为大多数的数据是按照主键递增顺序插入得到的，所以可以认为，如果按照递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能

②MRR优化后的语句执行流程

a）根据索引a，定位到满足条件的记录，将id放入到 read_rnd_buffer 中

b）将read_rnd_buffer中的id进行递增排序

c）排序后的数组，依次到主键id索引中查找记录，并作为结果返回

这里，read_rnd_buffer的大小是由read_rnd_buffer_size参数控制的，如果放满了，就会先执行b、c，然后清空read_rnd_buffer，再继续找索引a的下个记录并继续循环。

MRR能够提高性能的核心在于，这条查询语句在索引a上做的是一个范围查询，可以得到足够多的主键id。通过排序后，再去主键索引查数据，才能体现出顺序性的优势。

2.Batched Key Access（BKA），对NLJ算法进行优化

NLJ算法的执行逻辑是：从驱动表t1，一行行取出a的值，再到被驱动表t2去做join，对于表t2来说，每次都是匹配一个值。这时，MRR的优势就用不到了

BKA算法优化：将驱动表t1的数据取出一部分，放到join_buffer中（放不下则分段），这样就可以用上MRR的排序性

启用BKA算法：前两个参数启动MRR，BKA算法的优化依赖于MRR

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

3.Batched Key Access（BKA），对BNL算法进行优化

①在被驱动表表上建索引，这时就可以直接转成BKA算法

②但是，有时候会遇到不适合在被驱动表上建索引的情况。比如下面这个语句：

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

如果t2的数据量比较大，且语句也是一个低频的SQL语句，那么创建索引就比较浪费

此时，可以考虑使用临时表：

a）把表t2中满足条件的数据放在临时表tmp中

b）为了让join使用BKA算法，给临时表tmp的字段b上加上索引

c）让表t1和临时表tmp做join操作

create temporary table tmp(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);

三、join的疑问

1.如果使用left join的话，左边的表一定是驱动表吗？

2.如果两个表的join包含多个条件的等值匹配，是都写到on中还是只把一个条件写到on中，其他条件写到where部分？

例子：

create table a(f1 int, f2 int, index(f1))engine=innodb;
create table b(f1 int, f2 int)engine=innodb;
insert into a values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
insert into b values(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

表a和表b都有两个字段f1、f2，不同的是表a的字段f1上有索引。然后，往两个表各插入6条记录，其中表a和表b同时存在的数据有4行

下面有两条执行语句，对应问题2
select * from a left join b on(a.f1=b.f1) and (a.f2=b.f2); /*Q1*/
select * from a left join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q2*/

①分析Q1语句：和上图Q1语句有偏差，不影响分析

a）把表a的内容读入join_buffer，因为是select * 所以字段f1和f2都被放到join buffer中

b）顺序扫描表b，对于每一行数据，判断是否满足on后面的条件，即 left join on后面的条件，只过滤表b的数据

满足条件的记录，作为结果集的一行返回，如果有where，再判断where是否满足，再返回

c）表b扫描完成后，对于每一匹配的表a的行，剩余字段补上NULL，再放入结果集中

②分析Q2语句：

在MySQL中，NULL跟任何值执行等值判断或者不等值判断的结果都是NULL，select NULL = NULL 的结果也是返回NULL

因此Q2中where a.f2=b.f2 表示，查询结果里面不包含b.f2是NULL的行

即：找到这两个表中，f1、f2对应相同的行，对于表a中存在，而表b中匹配不到的行，就放弃

因此，优化器就把这条语句的left join改写成join，然后因为表a的f1上有索引，就把表b作为驱动表，这样就可以用上NLJ算法。在执行explain之后，在执行show warnings

这个例子说明，即使我们在SQL语句中写成了left join，执行过程还是有可能不是从左到右连接的，也就是说，使用left join，左边的表不一定是驱动表

所以说，如果需要left join 语义，就不能把被驱动表的字段放在where条件里面做等值判断或者不等值判断，必须都写在on中

 

如果是join语句：

select * from a join b on(a.f1=b.f1) and (a.f2=b.f2); /*Q3*/
select * from a join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q4*/

可以看到，这两条语句都被改写成：select * from a join b where (a.f1=b.f1) and (a.f2=b.f2);

也就是说，在这种情况下，join将判断条件全部放到on或者where就没有区别了