index_merge sort_intersection
Prima di MariaDB 5.3, il metodo di accesso index_merge supportava le operazioni union, sort-union e intersection. A partire da MariaDB 5.3, supporta anche l'operazione sort-intersection. Questo consente l'uso di index_merge in un maggior numero di casi.
Questa funzionalità è disabilitata per default. Per abilitarla occorre impostare lo switch dell'ottimizzatore index_merge_sort_intersection, in questo modo:
SET optimizer_switch='index_merge_sort_intersection=on'
Limitazioni di index_merge/intersection
Prima di MariaDB 5.3, il metodo di accesso index_merge aveva una strategia di intersezione chiamata intersection. Essa poteva essere utilizzata quando si univano scansioni su indici che erano ordinate per rowid. In pratica ciò significa che una intersection poteva essere realizzata solo per le condizioni di eguaglianza (=).
Per esempio, ecco una query che usa intersection:
MySQL [ontime]> EXPLAIN SELECT AVG(arrdelay) FROM ontime WHERE depdel15=1 AND OriginState ='CA'; +--+-----------+------+-----------+--------------------+--------------------+-------+----+-----+-------------------------------------------------+ |id|select_type|table |type |possible_keys |key |key_len|ref |rows |Extra | +--+-----------+------+-----------+--------------------+--------------------+-------+----+-----+-------------------------------------------------+ | 1|SIMPLE |ontime|index_merge|OriginState,DepDel15|OriginState,DepDel15|3,5 |NULL|76952|Using intersect(OriginState,DepDel15);Using where| +--+-----------+------+-----------+--------------------+--------------------+-------+----+-----+-------------------------------------------------+
Ma se si sostituisce OriginState ='CA' con OriginState IN ('CA', 'GB') (che restituisce lo stesso numero di record), ecco che intersection non è più utilizzabile:
MySQL [ontime]> explain select avg(arrdelay) from ontime where depdel15=1 and OriginState IN ('CA', 'GB'); +--+-----------+------+----+--------------------+--------+-------+-----+-----+-----------+ |id|select_type|table |type|possible_keys |key |key_len|ref |rows |Extra | +--+-----------+------+----+--------------------+--------+-------+-----+-----+-----------+ | 1|SIMPLE |ontime|ref |OriginState,DepDel15|DepDel15|5 |const|36926|Using where| +--+-----------+------+----+--------------------+--------+-------+-----+-----+-----------+
La seconda query è più lenta di 5.x volte (da 2.2 a 10.8 secondi) negli esperimenti che sono stati fatti.
How index_merge/sort_intersection improves the situation
In MariaDB 5.3, when index_merge_sort_intersection is enabled,
index_merge intersection plans can be constructed from non-equality
conditions:
MySQL [ontime]> explain select avg(arrdelay) from ontime where depdel15=1 and OriginState IN ('CA', 'GB'); +--+-----------+------+-----------+--------------------+--------------------+-------+----+-----+--------------------------------------------------------+ |id|select_type|table |type |possible_keys |key |key_len|ref |rows |Extra | +--+-----------+------+-----------+--------------------+--------------------+-------+----+-----+--------------------------------------------------------+ | 1|SIMPLE |ontime|index_merge|OriginState,DepDel15|DepDel15,OriginState|5,3 |NULL|60754|Using sort_intersect(DepDel15,OriginState); Using where | +--+-----------+------+-----------+--------------------+--------------------+-------+----+-----+--------------------------------------------------------+
In our tests, this query ran in 3.2 seconds, which is not as good as the case
with two equalities, but still much better than 10.8 seconds we were getting
without sort_intersect.
The sort_intersect strategy has higher overhead than intersect but is
able to handle a broader set of WHERE conditions.
When to use
index_merge/sort_intersection works best on tables with lots of records and
where intersections are sufficiently large (but still small enough to make a
full table scan overkill).
The benefit is expected to be bigger for io-bound loads.
