New Features of MySQL5.6-Batched Key Access

New Features of MySQL5.6-Batched Key Access

1. Introduction

MySQL 5.6 provides many performance optimization features, one of which is about the Batched Key Access (BKA) algorithm to improve the performance of table join. This article will combine the previous MRR, this algorithm is described in detail by BNL optimization features. This article is my longest delay. I have never been clear about the association between MRR and BKA, the difference between BKA and BNL. It took me one day to collect data this week, there is a document-based translation, which may be inaccurate. please correct me.

Principle 2

For multi-table join statements, when MySQL uses indexes to access the second join table, a join buffer is used to collect the column values generated by the first operation object. After BKA builds keys, it sends them to the engine layer for index search in batches. Key is submitted to the engine through the MRR interface. In this way, MRR makes the query more efficient.

The general process is as follows:

1 BKA uses the join buffer to save the qualified data generated by the first join operation.

2. Then, the BKA algorithm constructs a key to access the connected tables, and uses the MRR interface in batch to submit the keys to the database storage engine for search.

3. After submitting the keys, MRR uses the best method to obtain the row and feed it back to BKA.

BKA uses the join buffer size to determine the buffer size. The larger the buffer, the more sequential the access to the joined/internal tables.

The MRR interface has two application scenarios:

Scenario 1: It is applied to traditional disk-based storage engines (innodb, myisam). For these engines, in the join buffer, keys are submitted to MRR at one time, and MRR finds rowid through key, get data through rowid

Scenario 2: It is applied to the remote storage engine (NDB). Some keys from the join buffer are sent from the SQL node to the data node, and then the SQL NODE receives a combination of rows matching by correlation. Then use these rows to match the new row. Then

Key until the sending is complete.

Relationship between BNL, BKA, and MRR

BNL and BKA both submit a part of the result set in batch to the next joined table (marked as T), thus reducing the number of times the table T is accessed. What are the differences between them? The idea of NBL and BKA is similar. For details, see nest-loop-join official manual.

The first NBL occurs earlier than BKA, and BKA does not appear until 5.6, and NBL exists in at least 5.1.

The second NBL is mainly used when the joined table has no index, join buffering can be used when the Join is of type ALL or index (in other words, when no possible keys can be used, and a full

Scan is done, of either the data or index rows, respectively)

Third, BKA mainly refers to the use of indexes on the joined table, so the rows are sorted by index fields before the row is submitted to the joined table, thus reducing random I/O, sorting is the biggest difference between the two, but what if the joined table does not use indexes? Use NBL.

The above principle environment mentioned that the BKA implementation process is to pass keys to the MRR interface, which is basically implemented in MRR. The following figure shows the relationship between them:

4. How to Use

To use BKA, you must adjust the value of the system parameter optimizer_switch. Set batched_key_access to on. Because BKA uses MRR, you must enable MRR, however, the cost-based optimization MRR algorithm is not particularly accurate. It is recommended to disable mrr_cost_based in official documents and set it to off.

Set optimizer_switch = 'mrr = on, mrr_cost_based = off, batched_key_access = On'

In addition, for multi-table join statements, the indexes of the joined or non-driving tables must be available.

This article permanently updates the link address: