Brief analysis of Multi range Read (MRR) & Batch key access (BKA) & block nested loop (BNL)

Another article introduced the index condition Pushdown (ICP) This spoke is MRR and related Bka

what is MRR.

Mrr:multi Range read. Bad explanation, first look at an example:

SELECT * from TB where key_column = X

in the absence of MRR, it is the result of this:

1. Select Key_column, pk_column from TB where key_column=x ORDER by Key_column---> Assuming this result set is T

2. for each row in t; Select Non_key_column from TB where pk_column = Pk_column_value. (The 2nd step in Oracle is called back to the table.) )

in the case of MRR, it does so:

1. Select Key_column, pk_column from TB where key_column = x ORDER BY Key_column---> Suppose this result set is T

2. put the result set t inside the buffer (until the buffer is full), and then the result set t according to the Pk_column sort---> assume that the sorted result set is T_sort

3. Select Non_key_column fromtb where Pk_column in (select Pk_column from T_sort)

the difference between the two is mainly two points :

1. without MRR, random IO increases because the indexed tuples obtained from the level two index are ordered, but they are unordered in the primary key index, so each time you go to the primary key index to get Non_key_column

are random IO. (If the index is overwritten, there is no need to take advantage of the MRR feature and get all the data directly from the index)

2. in the absence of MRR, the number of accesses to the primary key index also increases. There is no MRR in the case, the level two index to get the number of rows, then you have to access the number of primary key index (also can not be fully said, because MySQL implementation of the BNL), and with the MRR, the number of times is reduced to the previous number of t/buffer_size.

So MRR is mainly to solve these two problems.

So look at the explain that used MRR:

Mysql> explain select C from Test1 Wherek in (25054, 24781, 23054, 25207, 25020);

+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra

+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------+

| 1| Simple | Test1 | Range | K | K | 4 |  NULL | 329 | The Using where; Using MRR |

+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------+

No use of MRR explain:

Mysql> explain select C from Test1 Wherek in (25054, 24781, 23054, 25207, 25020);

+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra

+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

| 1| Simple | Test1 | Range | K | K | 4 |  NULL | 124 | Using where |

+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

So when it comes to MRR, there is also a concept BKA (batch key access) that has to be mentioned.

BKA is to address the above: In the absence of MRR, the number of access to the join table is mostly (in fact, in the above example, the primary key index can be seen as a join table). BKA is a batch of batches of submissions to the table to be join. In fact, in the process of BKA implementation is to pass a parameter to the MRR interface, in essence or within the MRR implementation, the following picture shows the relationship between them.

NBL and BKA are all part of a batch of submissions to the table to be join, thus reducing the number of visits, then what is the difference between them. The thoughts of NBL and BKA are similar, see details:

Http://www.mysqlab.net/docs/refman/en-5.1/page/nested-loop-joins.html.

Whereas BKA (batch key access) is primarily defined as indexed on the join table, the rows are sorted by indexed fields before they are submitted to the join table, thus reducing random io, which is the biggest difference between the two.

You may also ask why you have to NBL the BKA.

First, NBL than BKA appear, BKA until 5.6, and NBL at least 5.1 in the presence of

Second, the BKA is for the table to be join the index can be used (that is, a key), but if the user did not build index on it. Then we'll use NBL.

Summary:

MRR is mainly to reduce the number of random IO in the join and join the table's access times, no MRR before the best join algorithm in MySQL is NBL, although it can reduce the number of join tables, but the random io This is still not resolved, and now two problems are solved. and performance comparison test because now MySQL 5.6 is only developed version so not test, later can try.

reference materials:

1. http://www.mysqlab.net/docs/refman/en-5.1/page/nested-loop-joins.html

2. http://www.mysqlperformanceblog.com/2012/04/04/join-optimizations-in-mysql-5-6-and-mariadb-5-5/

3. http://www.mysqlperformanceblog.com/2012/03/21/multi-range-read-mrr-in-mysql-5-6-and-mariadb-5-5/

4. http://kb.askmonty.org/en/multi-range-read-optimization#case-3-key-sorting-for-batched-key-access