MySQL深入——25

Join语句如何优化?

Join语句的两种算法，分别为Index Nested-Loop Join和Block Nested-Loop Join

NLJ在大表Join当中还不错，但BNL在大表join时性能就差很多，很耗CPU资源。

如何优化这两个算法

创建t1，t2算法，在t1中插1000行数据，每一行a=1001-id也就是a是逆序的，t2中插入10万条数据。

Multi-Range Read优化（MRR）优化的主要目的为尽量使用顺序读盘。

我们先来看看回表，回表的概念：InnoDB在普通索引a上查到主键id后，再根据一个个主键id到主键索引当中去查找数据的概念。

那么回表是一行行查数据还是批量的查数据呢？

因为主键是一颗b+树，在这颗树上每次只能根据一个主键id查数据，所以回表过程必然是一行一行进行的。

若是随着a的值递增的，id的值就会变为随机的，那么会出现随机访问，性能差，但是如果改变了查询的顺序，性能就会提升。所以我们可以认为，若是按照主键递增的顺序查找，对磁盘的读就会接近顺序读，所以可以提升读性能。

这就是MRR优化的主要思路，语句的执行变成下面这种样子。

1.根据索引a，定位满足的记录，将id值放入read_rnd_buffer中

2.将read_rnd_buffer中的id进行递增排序

3.排序后的id数组，依次到主键id索引当中查记录。

read_rnd_buffer的大小由read_rnd_buffer_size控制，满了之后走完了23，之后再到1继续。

要稳定的使用MRR优化，需要设置optimizer_switch=“mmr_cost_based=off”，因为优化器策略会更倾向于不使用MRR优化，我们这样设置可以强制使用。

MRR能够提升性能的核心在于查询的a是范围查询能够获得足够多的主键id，排序后再去查，才能体现出顺序的优势。

Batched Key Access

MySQL5.6之后引入BKA算法，对NLJ的优化。

NLJ算法是从驱动表t1，一行行的取出a的值，再到被驱动表当中去做join，对于t2来说，每次匹配一个值，MRR是无法使用的。

所以我们可以将t1当中的数据取出来一部分放入join_buffer，一次行传入多个数据给t2，就可以进行优化了。

启用BKA算法

set optimizer_switch='mmr=on,mmr_cost_based=off,batched_key_access=on'

BKA主要依赖于RMM，所以我们得先打开RMM算法。

BNL算法的性能问题

若是一个使用BNL算法的Join语句，多扫描一个冷表，而且这个语句执行时间超过了1秒，就没会再次扫描冷表的时候将这个冷表的数据页移到LRU头部，这种情况是冷表数据量小于整个Buffer Pool的3/8，能够完全放入old区域。

若是这个冷表很大，一个正常访问的数据页进入young区域，join语句就在读磁盘和淘汰内存页的时候，进入old区域的数据页，可能会被删除。

大表join对Io有影响，但是语句结束之后对IO的影响就会结束，但是对于Buffer Pool的影响是持续性的。

可以通过增大joinn_buffer_size的值，减少对驱动表的扫描行数。

对无BNL常见的优化方法就是给被驱动表的join字段加上索引，将BNL转为BKA算法。

一些情况下，直接进将被驱动表上加索引就可以转为BKA算法，当时有些情况下是不可以的，比如：一个十万的表在where的限制条件下筛选出的需要参加join语句的只有2000条，这个时候添加索引就有些浪费了，但是要是不添加索引，一次一次的的对比就太耗费CPU资源，有一种方法可以完美解决这种问题，就是创建一个临时表。

创建一个临时表，将这2000条符合条件的语句添加到其中，然后给这个临时表添加索引，这样触发BKA算法，拉提升性能。