Optimizer Enhancements in MySQL 5.6: Multi-Range Read (MRR), Block Nested-Loop (BNL), Batched Key Access (BKA)
(Doc ID 1464378.1)
Last updated on FEBRUARY 27, 2020
Applies to:MySQL Server - Version 5.6 and later
Information in this document applies to any platform.
What exactly is optimized
- Multi-Range Read (MRR) - MySQL uses temporary buffer for converting set of random row lookups into lookups ordered by row ID (usually primary key).
- Block Nested-Loop (BNL) - During (some) join operations (instead of lookups in joined table), MySQL sequentially reads next row from joined table and matches it against portion of joining table located in join_buffer. This approach uses sequential scan instead of random lookups, number of scans depend on size of join_buffer.
- Batched Key Access (BKA) - Basically it is the same as MRR, just used during (some) joins to serialize random key lookups into batched partitions (ordered by row ID).
When new approach is faster and troubleshooting
To disable new feature use following commands:
Currently MRR cost is set too high, to be able utilize new algorithm in all suitable cases, it is required to tell optimizer to ignore MRR cost penalty:
MRR and BKA bring most performance boost for systems with expensive random accesses comparing to sequential reads.
So e.g. MyISAM (except SSD storage) and NDB should benefit the most from utilizing MRR. In contrast, InnoDB doesn't benefit much from accessing rows sequentially when buffer_pool is hot, so keeping mrr_cost_based=on may be good solution.
Performance boost should be always expected if MySQL is able to utilize BNL.
Increasing following parameters may speed up performance of new algorithms:
- MRR - read_rnd_buffer_size
- BNL - join_buffer_size
- BKA - join_buffer_size
Typically increasing related parameter should bring performance benefit until buffers are big enough (so MySQL is able to produce resultset just with few iterations).
Side Note: Order of rows
MySQL doesn't guarantee order of rows if no sorting is used in query, thus knowing details of algorithm implementation we can somehow guess order in output (this is still not reliable approach and used only for better understanding how algorithms work)
- No-MRR: output typically will be sorted by secondary index
- MRR: output typically will be sorted by primary key
- No-BNL: output typically will be sorted by index
- BNL: order of rows typically will match 'No-BNL' approach. With outer join, rows without matched joins will go at the end. If join_buffer_size is not big enough to store joined table, rows in output will go in blocks: start of each block contains rows with matched joins, end of each block contains rows without matched joins. (This approach can be used to understand how many rows join buffer may hold).
- No-BKA: rows in output typically sorted by secondary index
- BKA: rows in output typically sorted by primary index
BNL in MySQL statistics
Example below shows advantages of using BNL in mysql statistics.
- Connection 13 had BNL disabled. MySQL did read index on 'City' and found 110 rows matching condition (city.population > 1000000) (See Handler_read_key=111 and results of query from Connection 14 below).
- Then for each City retrieved MySQL did join against 237 Countries matched condition (country.code > 'M'). (see results of query from Connection 14 below to get number of matched countries).
- In total MySQL did 110*237 row reads which matches variable Handler_read_next=26180 .
- In contrast, query with NBL (Connection 12) did access each table only once (see Handler_read_next=347, which is equal number of matched rows in each table 110+237).
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document
|What exactly is optimized|
|When new approach is faster and troubleshooting|
|Side Note: Order of rows|
|BNL in MySQL statistics|