in MDEV-16232, InnoDB is implementing an optimization to cut down on latch acquisitions/releases and other kinds of overhead when the query is about to perform multiple operations on the [index] records on the same page.
In the Storage engine API, this translates into the following calls:
InnoDB will hold internal page latches (and something else?) between calls (1) and (2),
so the SQL layer must guarantee the following properties of the code that may execute in BATCH_OF_OPERATIONS:
The mini-transaction calls can be put around all loops where we call 'read next' repeatedly for just one table.
The places in the select/filesort code where this happens:
- When using only one table InnoDB table (it's ok mixing InnoDB tables and other tables).
**There can be any number of const tables as these are only called once before accessing any other tables.
- The last table in the join tabs.
- * This is calling read-next repeatedly until there are no more matching rows.
- The last table just before a table with a join cache
- This is calling read-next repeatedly until there are no more matching rows. All read rows are put into the join cache (until join cache is full, in which case we have to end the mini transaction).
- When scanning a table in filesort
- In multi_range_read around each range
- In index_merge when finding rowid's for one table
Batching cannot be used if one of the following holds:
- We use index_merge .
- We use Multi-Range-Read
- The WHERE condition accesses other tables (that is, uses subqueries)
- For UPDATE/DELETEs: the modified table has triggers (which could potentially access other tables)
- For UPDATE/DELETEs: there are foreign keys that depend on this table.
Single-Table UPDATE and DELETE that satisfy the Applicability criteria should execute the whole query in one batch.
The same goes for single-table SELECT: It can start a batch when select starts and end it when it ends.
This means we will add just two
onto the main execution path.
The prototype patch is in this branch: 10.9-MDEV-16232, this commit:
It implements the check for UPDATE and DELETE. It doesn't have the applicability check (it has some hack). Or mysql-test coverage. Because of that it can cause crashes:
The prototype patch has some hack to handle single-table selects.
A multi-table select may use batches for the last table in the join. Access to the last table must satisfy the Applicability criteria.
Can we avoid calling "if (table does batching)" for every row examined for any table?
It's tempting to use special functions in READ_RECORD. This will allow to avoid any overhead. (This is for starting the batch, what about ending it? Especially if the SELECT in question is a subquery with LIMIT?)
Note: the prototype patch doesn't handle this at all.
Need some way to see if the optimization is applied.
This is probably a too fine detail to show in EXPLAIN output.
Let's have a counter instead. It will count the number batches (that is, number of start_operations_batch() calls).
We should also write about the decision whether batching is to be used into the optimizer trace.
It's a good idea to be able to switch the optimization on and off.
The switch should be at the SQL layer.
- Do we need to call end_operations_batch() in case of an error condition? (A: Yes)