MDEV-28817 Derived table elimination issue for multiple occurences
If the same field occurs more than once in the SELECT list
(using aliases) we need to store all those occurences
to be able to eliminate the derived table. For example,
SELECT t1.* from t1 LEFT JOIN
(SELECT a AS a1, MAX(c), b AS b1, b AS b2, a AS a2
FROM t2 GROUP BY a, b) D
ON D.a1=t1.a and D.b2=t1.b
Derived table D is joined using aliases a1 and b2 so the eliminating
algorithm must know that fields (a1,b2) are the same as (a,b)
which are on the GROUP BY list
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.
MDEV-28708 Increased congestion on buf_pool.flush_list_mutex
In commit f80deb9590775af44da200920ee9ac662e93205d (MDEV-27868)
a fix for a correctness regression caused a performance regression
by increasing the amount of work that is executed while holding
buf_pool.flush_list_mutex.
buf_page_t::set_temp_modified(): Relax an assertion, to allow an
already dirty block to be marked as dirty.
buf_page_t::flush_list_requests: Note that the variable is not
always protected by buf_pool.flush_list_mutex. Already dirty blocks
that are being written to will increment the counter without
holding buf_pool.flush_list_mutex.
mtr_t::process_freed_pages(): Handle pages that were freed during
the execution of the mini-transaction.
ReleaseUnlogged, mtr_t::release_unlogged(): Release modified pages when
no log was written. This is for pages of the temporary tablespace, or for
IMPORT TABLESPACE.
ReleaseModified: Renamed from ReleaseBlocks.
Assume that buf_pool.flush_list_mutex was acquired by the caller.
ReleaseSimple: A combination of ReleaseLatches and ReleaseModified,
for the case that for any modified pages, some earlier modifications
are already waiting to be written.
mtr_t::commit(): Invoke one of release_unlogged(), ReleaseModified,
ReleaseSimple, ReleaseAll. Acquire and release buf_pool.flush_list_mutex
at most once.
memo_slot_release(): Simplify the code.
mtr_t::sx_latch_at_savepoint(), mtr_t::x_latch_at_savepoint():
Reduce the size of the critical section.
fil_space_t::update_last_freed_lsn(), fil_space_t::clear_freed_ranges(),
fil_space_t::add_free_range(): Assume that freed_range_mutex is held
by the caller.
buf_pool_t::prepare_insert_into_flush_list(): Determine the insert
position for buf_pool_t::insert_into_flush_list(). Remove any clean
blocks from buf_pool.flush_list that were encountered while searching.
buf_pool_t::insert_into_flush_list(): Insert the block at the
predetermined position.
