Shulou(Shulou.com)06/01 Report--

MySQL version 5.6 began to add an algorithm to improve the performance of the table join: a new feature of Batched Key Access (BKA).

The principle of BKA algorithm: the row / result set of the outer loop is stored in join buffer, and each row of data in the memory loop is compared with the records in the entire buffer

It can reduce the scanning times of the inner loop.

For multi-table join statements, when MySQL accesses the second join table using the index, a join buffer is used to collect the first Operand generation

Gets or sets the related column value of the After the key is built by BKA, it is passed to the engine layer in batch for index search. Key is submitted to the engine through the MRR interface

In this way, MRR makes the query more efficient.

If the external table scans the primary key, the record access in the table is orderly, but if the joined column is a non-primary key index, then for the records in the table

The visit may be very discrete. So for the join of non-primary key indexes, the Batched Key Access Join algorithm

It will greatly improve the execution efficiency of SQL. The BKA algorithm supports inner join, outer join and semi-join operation, including nested outer join.

The working steps of Batched Key Access Join algorithm are as follows:

1) put the relevant columns in the external table into the Join Buffer.

2) send Key (index key value) to Multi-Range Read (MRR) interface in batch.

3) Multi-Range Read (MRR) sorts the received Key according to its corresponding ROWID, and then reads the data.

4) return the result set to the client.

For multi-table join statements, when MySQL accesses the second join table using the index, a join buffer is used to collect the correlation generated by the first Operand

Column value. After the key is built by BKA, it is passed to the engine layer in batch for index search. The key is submitted to the engine through the MRR interface (mrr is intended to be sequential).

In this way, MRR makes the query more efficient.

The general process is as follows:

1 BKA uses join buffer to save eligible data generated by the first operation of join

2 then the BKA algorithm builds key to access the connected table, and uses the MRR interface to submit the keys to the database storage engine for lookup.

3 after submitting the keys, MRR uses the best way to get rows and feedback to BKA

Both BNL (Block Nested Loop) and BKA (MySQL Batched Key Access) submit some rows to the join table in batches, thus reducing access to

Times, so what's the difference between them?

The first BNL appeared earlier than BKA. BKA did not appear until 5.6, while BNL existed at least in 5.1.

The second BNL is mainly used when there is no index on the table being join.

The third BKA mainly means that there is an index on the join table that can be used, so the rows are sorted by index fields just before they are submitted to the table by join.

So the random IO is reduced, and sorting is the biggest difference between the two, but what if the table is not indexed by join?

Then use BNL.

The following settings enable BKA:

To use BKA, you must adjust the value of the system parameter optimizer_switch. It is officially recommended to turn off mrr_cost_based and set it to off.

Mysql > SET global optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'

Note:

BKA is mainly suitable for join tables where indexes are available, and BNL can only be used without indexes.

Multi-table join statement, the join table / undriven table must have an index available.

In the EXPLAIN output, when the extra value contains Using join buffer (Batched Key Access), BKA is used.

+-- +

| | Extra |

+-- +

| | NULL |

| | Using where; Using join buffer (Batched Key Access) |

+-- +

Using hint, the way to force BKA:

For example:

Mysql > explain SELECT / * + bka (a) * / a.gender, b.dept_no FROM employees a, dept_emp b WHERE a.birth_date = b.from_date

+-+-+ -+-+

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-+-+ -+-+

| | 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 331143 | 100.00 | NULL |

| | 1 | SIMPLE | a | NULL | ref | idx_birth_date | idx_birth_date | 3 | employees.b.from_date | 62 | 100.00 | Using join buffer (Batched Key Access) |

+-+-+ -+-+

2 rows in set, 1 warning (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.