MYSQL 之 Join

文章目录

• • • 不要使用 hint，如：sql\_no\_cache, force index, ignore key, straight join等
    • JOIN 语句混用逗号和 ANSI 模式
    • 同一张表被连接两次
    • OUTER JOIN 失效
• 被驱动表用得上索引的情况
• • Index Nested-Loop Join
• 被驱动表用不上索引的情况
• • Simple Nested-Loop Join
  • Block Nested-Loop Join
  • • 内存中能够放下
    • 内存中放不下
• 结论
• • 能不能使用join语句？
  • 如果要使用join，应该选择大表做驱动表还是选择小表做驱动表？
• Join语句的优化
• • Multi-Range Read 优化
  • Batched Key Access
  • BNL算法的性能问题与优化
  • • 性能问题
    • 如何优化，其实就是让驱动表使用到索引

首先先列出一些我们公司内部的 Join 使用规范，建议结合其他大厂出的编程规约一起食用即可。

不要使用 hint，如：sql_no_cache, force index, ignore key, straight join等

• Content:hint 是用来强制 SQL 按照某个执行计划来执行，但随着数据量变化我们无法保证自己当初的预判是正确的。
• Case:

SELECT * FROM t1 USE INDEX (i1) ORDER BY a;

JOIN 语句混用逗号和 ANSI 模式

• Content:表连接的时候混用逗号和 ANSI JOIN 不便于人类理解，并且MySQL不同版本的表连接行为和优先级均有所不同，当 MySQL 版本变化后可能会引入错误。
• Case:

select c1,c2,c3 from t1,t2 join t3 on t1.c1=t2.c1,t1.c3=t3,c1 where id>1000;

同一张表被连接两次

• Content:相同的表在 FROM 子句中至少出现两次，可以简化为对该表的单次访问。
• Case:

select tb1.col from (tb1, tb2) join tb2 on tb1.id=tb.id where tb1.id=1;

OUTER JOIN 失效

• Content:由于 WHERE 条件错误使得 OUTER JOIN 的外部表无数据返回，这会将查询隐式转换为 INNER JOIN 。如：select c from L left join R using© where L.a=5 and R.b=10。这种 SQL 逻辑上可能存在错误或程序员对 OUTER JOIN 如何工作存在误解，因为 LEFT/RIGHT JOIN 是 LEFT/RIGHT OUTER JOIN 的缩写。
• Case:

select c1,c2,c3 from t1 left outer join t2 using(c1) where t1.c2=2 and t2.c3=4;

创建两张相同的表：这两个表都有一个主键索引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;

被驱动表用得上索引的情况

Index Nested-Loop Join

我们来看一下这个语句：
select * from t1 straight_join t2 on (t1.a=t2.a);
如果直接使用 join 语句，MySQL优化器可能会选择表t1或t2作为驱动表，这样会影响我们分析SQL语句的执行过程。所以，为了便于分析执行过程中的性能问题，改用straight_join让MySQL使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去join。在这个语句里，t1 是驱动表，t2是被驱动表。

此语句的执行流程是：

1. 从表t1中读入一行数据 R；
2. 从数据行R中，取出a字段到表t2里去查找；
3. 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
4. 重复执行步骤1到3，直到表t1的末尾循环结束。

在第三步的时候，因为被驱动表t2的字段a上有索引，join 过程会用上这个索引

这个过程是先遍历表 t1，然后根据从表t1中取出的每行数据中的a值，去表t2中查找满足条件的记录。在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“Index Nested-Loop Join”，简称 NLJ

那么在刚才这个语句中，需要扫描多少行数据呐？

1. 驱动表 t1 自然是 100行
2. 被驱动表 t2 ，根据a字段去表 t2 查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描100行；
3. 所以总的执行扫描行数是：200 行

假设不使用Join操作，那我们自己会如何实现呐？

1. 执行 select * from t1，查出表 t1 的所有数据，这里有100行；
2. 循环遍历这100行数据：
   • 从每一行R取出字段a的值 $R.a；
   • 执行select * from t2 where a=$R.a （这里其实直接就可以 a in(a1,a2,a3,a4)）；
   • 把返回的结果和R构成结果集的一行。

可以看到，在这个查询过程，也是扫描了200行，但是总共执行了101条语句，比直接join多了100次交互。除此之外，客户端还要自己拼接SQL语句和结果。

显然，这么做还不如直接join好。所以公司级给的建议就是：

将嵌套查询重写为 JOIN 通常会具有更高效的执行和更有效的优化

• Content:一般来说，非嵌套子查询总是用于关联子查询，最多是来自FROM子句中的一个表，这些子查询用于 ANY, ALL 和 EXISTS 的谓词。如果可以根据查询语义决定子查询最多返回一个行，那么一个不相关的子查询或来自FROM子句中的多个表的子查询就被压平了。
• Case:

SELECT s,p,d FROM tbl WHERE p.p_id = 
(SELECT s.p_id FROM tbl WHERE s.c_id = 100996 AND s.q = 1 );

我们再来看看第二个问题：怎么选择驱动表？ 直接结论就是：始终使用小表作为驱动表

被驱动表用不上索引的情况

Simple Nested-Loop Join

如果被驱动表用不上索引的话，那么上述的查询扫描的行数就是：100*1000 行数据了。当然，MySQL也没有使用这个Simple Nested-Loop Join算法，而是使用了另一个叫作“Block Nested-Loop Join”的算法，简称 BNL 。

Block Nested-Loop Join

内存中能够放下

• 把表t1的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存；
• 扫描表t2，把表t2中的每一行取出来（其实就是把t2也读到了内存中），跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。
  在这个过程中，对表t1和t2都做了一次全表扫描，因此总的扫描行数是1100。由于join_buffer是以无序数组的方式组织的，因此对表t2中的每一行，都要做100次判断，总共需要在内存中做的判断次数是：100*1000=10万次。

前面我们说过，如果使用Simple Nested-Loop Join算法进行查询，扫描行数也是10万行。因此，从时间复杂度上来说，这两个算法是一样的。但是，Block Nested-Loop Join算法的这10万次判断是内存操作，速度上会快很多，性能也更好。

可以看到在这里面，驱动表选择大表或者是小表是没有任何区别的。

内存中放不下

join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。如果放不下表t1的所有数据话，策略很简单，就是分段放。

1. 扫描表t1，顺序读取数据行放入join_buffer中，放完第88行join_buffer满了，继续第2步；
2. 扫描表t2，把t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回；
3. 清空 join_buffer；
4. 继续扫描表t1，顺序读取最后的12行数据放入join_buffer中，继续执行第2步。

这时候由于表t1被分成了两次放入join_buffer中，导致表t2会被扫描两次。虽然分成两次放入join_buffer，但是判断等值条件的次数还是不变的，依然是(88+12)*1000=10万次。

我们再来看下，在这种情况下驱动表的选择问题。

假设，驱动表的数据行数是N，需要分K段才能完成算法流程，被驱动表的数据行数是M。

注意，这里的K不是常数，N越大K就会越大，因此把K表示为λ*N，显然λ的取值范围是(0,1)。

所以，在这个算法的执行过程中：

• 扫描行数是 N+λNM；
• 内存判断 N*M 次。

显然，内存判断次数是不受选择哪个表作为驱动表影响的。而考虑到扫描行数，在M和N大小确定的情况下，N小一些，整个算式的结果会更小。

所以结论是，应该让小表当驱动表。

所以结论就是：

结论

能不能使用join语句？

• 如果可以使用Index Nested-Loop Join算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
• 如果使用Block Nested-Loop Join算法，扫描行数就会过多。尤其是在大表上的join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种join尽量不要用。
• 所以你在判断要不要使用join语句时，就是看explain结果里面，Extra字段里面有没有出现“Block Nested Loop”字样。

如果要使用join，应该选择大表做驱动表还是选择小表做驱动表？

• 如果是Index Nested-Loop Join算法，应该选择小表做驱动表；
• 如果是Block Nested-Loop Join算法：
• 在join_buffer_size足够大的时候，是一样的；
• 在join_buffer_size不够大的时候（这种情况更常见），应该选择小表做驱动表。
  所以，
  这个问题的结论就是，总是应该使用小表做驱动表。

Join语句的优化

Multi-Range Read 优化

回表是指，InnoDB在普通索引a上查到主键id的值后，再根据一个个主键id的值到主键索引上去查整行数据的过程。那么回表（查主键）过程是一行行地查数据，还是批量地查数据？

主键索引是一棵B+树，在这棵树上，每次只能根据一个主键id查到一行数据。因此，回表肯定是一行行搜索主键索引的。

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

如果此时主键不是有序的话，就会导致随机读，读性能就会很恶化。

因此可以做的一项优化就是排序。这就是MRR优化。

1. 根据索引a，定位到满足条件的记录，将id值放入read_rnd_buffer中;
2. 将read_rnd_buffer中的id进行递增排序；
3. 排序后的id数组，依次到主键id索引中查记录，并作为结果返回。

Batched Key Access

但是对于 Join 语句来说，其每次都是直接去被驱动表匹配一个值，所以它也用不上MRR优化，那怎么才能一次性地多传些值给表t2呢？方法就是，从表t1里一次性地多拿些行出来，一起传给表t2。

既然如此，我们就把表t1的数据取出来一部分，先放到一个临时内存。这个临时内存不是别人，就是 join_buffer。

通过上一篇文章，我们知道join_buffer 在BNL算法里的作用，是暂存驱动表的数据。但是在NLJ算法里并没有用。那么，我们刚好就可以复用join_buffer到BKA算法中。

如图所示，是上面的NLJ算法优化后的BKA算法的流程。

BNL算法的性能问题与优化

性能问题

使用Block Nested-Loop Join(BNL)算法时，可能会对被驱动表做多次扫描。如果这个被驱动表是一个大的冷数据表，除了会导致IO压力大以外，还会对系统有什么影响呢？

由于InnoDB对Bufffer Pool的LRU算法做了优化，即：第一次从磁盘读入内存的数据页，会先放在old区域。如果1秒之后这个数据页不再被访问了，就不会被移动到LRU链表头部，这样对Buffer Pool的命中率影响就不大。

但是，如果一个使用BNL算法的join语句，多次扫描一个冷表，而且这个语句执行时间超过1秒，就会在再次扫描冷表的时候，把冷表的数据页移到LRU链表头部。

这种情况对应的，是冷表的数据量小于整个Buffer Pool的3/8，能够完全放入old区域的情况。

如果这个冷表很大，就会出现另外一种情况：业务正常访问的数据页，没有机会进入young区域。

由于优化机制的存在，一个正常访问的数据页，要进入young区域，需要隔1秒后再次被访问到。但是，由于我们的join语句在循环读磁盘和淘汰内存页，进入old区域的数据页，很可能在1秒之内就被淘汰了。这样，就会导致这个MySQL实例的Buffer Pool在这段时间内，young区域的数据页没有被合理地淘汰。

也就是说，这两种情况都会影响Buffer Pool的正常运作。

大表join操作虽然对IO有影响，但是在语句执行结束后，对IO的影响也就结束了。但是，对Buffer Pool的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。

如何优化，其实就是让驱动表使用到索引

• 在原表上加索引
• 用有索引的临时表

我们的思路都是让join语句能够用上被驱动表上的索引，来触发BKA算法，提升查询性能。