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.
In che modo index_merge/sort_intersection migliora la situazione
In MariaDB 5.3, se index_merge_sort_intersection è abilitato, il piano di intersezione index_merge può essere utilizzato anche con condizioni diverse dall'uguaglianza:
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 | +--+-----------+------+-----------+--------------------+--------------------+-------+----+-----+--------------------------------------------------------+
Nei test eseguiti, questa query ha impiegato 3.2 secondi, il che non è vantaggioso come il caso in cui ci sono due eguaglianze, ma è comunque meglio degli 10.8 secondi ottenuti senza sort_intersect.
La strategia sort_intersect ha un ritardo più elevato rispetto a intersect, ma può gestire un maggior numero di condizioni WHERE.
Quando usarlo
index_merge/sort_intersection funziona meglio sulle tabelle con molti record e dove le intersezioni sono sufficientemente grandi (ma abbastanza piccole da rendere una scansione completa una strage).
Ci si aspetta un beneficio maggiore quando il carico di lavoro è legato all'I/O.
