MDEV-26278 Add functionality to eliminate derived tables from the query
Elimination of unnecessary tables from SQL queries is already present
in MariaDB. But it only works for regular tables and not for derived ones.
Imagine we have a view:
CREATE VIEW v1 AS SELECT a, b, max(c) AS maxc FROM t1 GROUP BY a, b
Due to "GROUP BY a, b" the values of combinations {a, b} are unique,
and this fact can be treated as like derived table "v1" has a unique key
on fields {a, b}.
Suppose we have a SQL query:
SELECT t2.* FROM t2 LEFT JOIN v1 ON t2.a=v1.a and t2.b=v1.b
1. Since {v1.a, v1.b} is unique and both these fields are bound to t2,
"v1" is functionally dependent on t2.
This means every record of "t2" will be either joined with
a single record of "v1" or NULL-complemented.
2. No fields of "v1" are present on the SELECT list
These two facts allow the server to completely exclude (eliminate)
the derived table "v1" from the query.
The performance_schema counter wait/io/file/innodb/innodb_log_file
is always reported as 0.
The way how redo log writes are being waited for was refactored in
commit 30ea63b7d2077883713e63cbf4e661ba0345bf68 by the introduction
of flush_lock and write_lock. Even before that change, all the
wait/io/file/innodb/ counters were always 0 in my tests.
Moreover, if the PMEM interface that was introduced in
commit 3daef523af25e4f1e4e75d2c26a9b25475f0c679
is being used, writes to the InnoDB log file will completely avoid
any system calls and performance_schema instrumentation.
In commit 685d958e38b825ad9829be311f26729cccf37c46 also the reads
of the redo log (during recovery) would bypass any system calls.
MDEV-27735 Deprecate the parameter innodb_change_buffering
As a follow-up to MDEV-27734 Set innodb_change_buffering=none by default
we mark the option innodb_change_buffering deprecated, to inform users
of its future removal.
