mysql join 性能原理

mysql join 性能原理

 ​ ​ ​MySQL是只支持一种JOIN算法Nested-Loop Join（嵌套循环链接），不像其他商业数据库可以支持哈希链接和合并连接。不过MySQL的Nested-Loop Join（嵌套循环链接）也是有很多变种，能够帮助MySQL更高效的执行JOIN操作：

接着计算两张表Join的成本，这里有下列几种概念：

外表的扫描次数，记为O。通常外表的扫描次数都是1，即Join时扫描一次驱动表的数据即可

内表的扫描次数，记为I。根据不同Join算法，内表的扫描次数不同

读取表的记录数，记为R。根据不同Join算法，读取记录的数量可能不同

Join的比较次数，记为M。根据不同Join算法，比较次数不同

回表的读取记录的数，记为F。若Join的是辅助索引，可能需要回表取得最终的数据

Simple Nested-Loop Join（以下简称SNLJ）

其算法伪代码如下所示: ​

For each row r in R do ​

 ​Foreach row s in S do ​

 ​ ​If r and s satisfy the join condition ​

 ​ ​ ​Then output the tuple

下图能更好地显示整个SNLJ的过程：

SNLJ的算法相当简单、直接。即外表（驱动表）中的每一条记录与内表中的记录进行判断。但是这个算法也是相当粗暴的，粗暴的原因在于这个算法的开销其实非常大。假设外表的记录数为R，内表的记录数位S，根据上一节Inside君对于Join算法的评判标准来看，SNLJ的开销如下表所示：

Index Nested-Loop Join

 ​

SNLJ算法虽然简单明了，但是也是相当的粗暴。因此，在Join的优化时候，通常都会建议在内表建立索引，以此降低Nested-Loop Join算法的开销，MySQL数据库中使用较多的就是这种算法，以下称为INLJ。来看这种算法的伪代码：

 ​

For each row r in R do ​

 ​lookupr in S index ​

 ​ ​if found s == r

 ​ ​ ​Then output the tuple

由于内表上有索引，所以比较的时候不再需要一条条记录进行比较，而可以通过索引来减少比较，从而加速查询。整个过程如下图所示：

可以看到外表中的每条记录通过内表的索引进行访问，因为索引查询的成本是比较固定的，故优化器都倾向于使用记录数少的表作为外表（这里是否又会存在潜在的问题呢？）。故INLJ的算法成本如下表所示：

上表Smatch表示通过索引找到匹配的记录数量。同时可以发现，通过索引可以大幅降低内表的Join的比较次数，每次比较1条外表的记录，其实就是一次indexlookup（索引查找），而每次index lookup的成本就是树的高度，即IndexHeight。

但是效率是否能达到用户的预期呢？其实如果是通过表的主键索引进行Join，即使是大数据量的情况下，INLJ的效率亦是相当不错的。因为索引查找的开销非常小，并且访问模式也是顺序的（假设大多数聚集索引的访问都是比较顺序的）。

 ​

大部分人诟病MySQL的INLJ慢，主要是因为在进行Join的时候可能用到的索引并不是主键的聚集索引，而是辅助索引，这时INLJ的过程又需要多一步Fetch的过程，而且这个过程开销会相当的大：

由于访问的是辅助索引，如果查询需要访问聚集索引上的列，那么必要需要进行回表取数据，看似每条记录只是多了一次回表操作，但这才是

由于访问的是辅助索引，如果查询需要访问聚集索引上的列，那么必要需要进行回表取数据，看似每条记录只是多了一次回表操作，但这才是INLJ算法最大的弊端。首先，辅助索引的index lookup是比较随机I/O访问操作。其次，根据index lookup再进行回表又是一个随机的I/O操作。所以说，INLJ最大的弊端是其可能需要大量的离散操作，这在SSD出现之前是最大的瓶颈。而即使SSD的出现大幅提升了随机的访问性能，但是对比顺序I/O，其还是慢了很多，依然不在一个数量级上。

Block Nested-Loop Join

Block Nested-Loop Join算法较Simple Nested-Loop Join的改进就在于可以减少内表的扫描次数，甚至可以和Hash Join算法一样，仅需扫描内表一次。

接着Inside君带你来看看Block Nested-Loop Join算法的伪代码:

 ​

For each tuple r in R do

 ​store used columns as p from R in join buffer

 ​For each tuple s in S do

 ​ ​If p and s satisfy the join condition

 ​ ​ ​Then output the tuple

 ​

可以看到相比Simple Nested-Loop Join算法，Block Nested-LoopJoin算法仅多了一个所谓的Join Buffer，然为什么这样就能减少内表的扫描次数呢？下图相比更好地解释了Block Nested-Loop Join算法的运行过程：

可以看到Join Buffer用以缓存链接需要的列，然后以Join Buffer批量的形式和内表中的数据进行链接比较。就上图来看，记录r1，r2 … rT的链接仅需扫内表一次，如果join buffer可以缓存所有的外表列，那么链接仅需扫描内外表各一次，从而大幅提升Join的性能。

Block Nested-Loop Join极大的避免了内表的扫描次数，如果Join Buffer可以缓存外表的数据，那么内表的扫描仅需一次，这和Hash Join非常类似。但是Block Nested-Loop Join依然没有解决的是Join比较的次数，其仍然通过Join判断式进行比较。综上所述，到目前为止各Join算法的成本比较如下所示：

MRR

在说明Batched Key Access Join前，首先介绍下MySQL 5.6的新特性mrr——multi range read。这个特性根据rowid顺序地，批量地读取记录，从而提升数据库的整体性能。看下面的SQL语句的执行计划：

 ​

mysql> explain select * from orders  ​

-> where o_orderdate >= '1993-08-01' ​

-> and o_orderdate < date_add(  ​'1993-08-01' ,interval '3' month)\G

*************************** 1. row  ​***************************

 ​ ​ ​ ​ ​ ​id: 1

 ​ ​select_type: SIMPLE

 ​ ​ ​ ​ ​table: orders

 ​ ​partitions: NULL

 ​ ​ ​ ​ ​type: range

possible_keys: i_o_orderdate

 ​ ​ ​ ​ ​key:  ​i_o_orderdate

 ​ ​ ​ ​key_len: 4

 ​ ​ ​ ​ ​ ​ref: NULL

 ​ ​ ​ ​ ​rows: 143210

 ​ ​ ​filtered: 100.00

 ​ ​ ​ ​ ​Extra: Using index condition

1 row in set, 1 warning (0.00 sec)

 ​

上述的SQL语句需要根据辅助索引i_o_orderdate进行查询，但是由于要求得到的是表中所有的列，因此需要回表进行读取。而这里就可能伴随着大量的随机I/O。这个过程如下图所示：

而mrr的优化在于，并不是每次通过辅助索引读取到数据就回表去取记录，而是将其rowid给缓存起来，然后对rowid进行排序后，再去访问记录，这样就能将随机I/O转化为顺序I/O，从而大幅地提升性能。这个过程如下所示：

从上图可以发现mrr通过一个额外的内存来对rowid进行排序，然后再顺序地，批量地访问表。这个进行rowid排序的内存大小由参数read_rnd_buffer_size控制，默认256K。

 ​

要开启mrr还有一个比较重的参数是在变量optimizer_switch中的mrr和mrr_cost_based选项。mrr选项默认为on，mrr_cost_based选项默认为off。mrr_cost_based选项表示通过基于成本的算法来确定是否需要开启mrr特性。然而，在MySQL当前版本中，基于成本的算法过于保守，导致大部分情况下优化器都不会选择mrr特性。为了确保优化器使用mrr特性，请执行下面的SQL语句：

 ​

mysql>set optimizer_switch='mrr=on,mrr_cost_based=off';  ​

 ​

同样执行前面的SQL语句，可以发现这时优化的执行计划为：

 ​

mysql> explain select * from orders where ​

-> o_orderdate >= '1993-08-01' ​

-> and o_orderdate < date_add('1993-08-01' ,interval '3' month)\G

*************************** 1. row***************************

 ​ ​ ​ ​ ​id: 1

 ​select_type: SIMPLE

 ​ ​ ​ ​table: orders

 ​partitions: NULL

 ​ ​ ​ ​type: range

possible_keys: i_o_orderdate

 ​ ​ ​ ​ ​key: i_o_orderdate

 ​ ​ ​key_len: 4

 ​ ​ ​ ​ ​ref: NULL

 ​ ​ ​ ​rows: 143210

 ​ ​filtered: 100.00

 ​ ​ ​ ​Extra: Using index condition; Using MRR

1row in set, 1 warning (0.00 sec)

 ​

最后来对比一下关闭和开启mrr特性后上述SQL的执行时间：

在讲述完mrr特性后，再来看BKA Join就非常清晰明了了。通过mrr特性优化Join的回表操作，从而提升Join的性能。这时BKA Join的整个过程如下所示：

然而，这么好的特性，却是在MySQL中默认关闭的！！！这可能是导致用户认为MySQL Join性能比较差的一个原因。若要使用BKA Join，务必执行下列的SQL语句：

 ​

mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

Query OK, 0 rows affected (0.00 sec)

 ​

若开启了BKA Join，则通过EXPLAIN命令，可以发现优化器的执行结果选项会有Using join buffer (Batched Key Access)的提示，如：

 ​

mysql> explain SELECT

-> COUNT(*)

-> FROM

-> part,

-> lineitem

-> WHERE

-> l_partkey， = p_partkey

-> AND p_retailprice > 2050 AND p_size < 100

-> AND l_discount > 0.04\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: part

partitions: NULL

type: ALL

possible_keys: PRIMARY

key: NULL

key_len: NULL

ref: NULL

rows: 196810

filtered: 11.11

Extra: Using where

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: lineitem

partitions: NULL

type: ref

possible_keys: i_l_suppkey_partkey,i_l_partkey

key: i_l_suppkey_partkey

key_len: 5

ref: dbt3_s1.part.p_partkey

rows: 28

filtered: 33.33

Extra: Using where; Using join buffer (Batched Key Access)

2 rows in set, 1 warning (0.00 sec)

 ​

最后来看下执行速度，可以发现BKA的提升非常明显：

未完待续