La scelta migliore tra le ottimizzazioni range e index_merge
Il metodo index_merge è utilizzato dall'ottimizzatore per estrarre righe da una singola tabella tramite diverse scansioni degli indici. I risultati di queste ricerche vengono poi fusi.
Eseguendo EXPLAIN, se l'ottimizzatore sceglie il piano di esecuzione index_merge, esso viene indicato nella colonna "type". Per esempio:
MariaDB [ontime]> SELECT COUNT(*) FROM ontime; +--------+ |count(*)| +--------+ | 1578171| +--------+ MySQL [ontime]> EXPLAIN SELECT * FROM ontime WHERE (Origin='SEA' OR Dest='SEA'); +--+-----------+------+-----------+-------------+-----------+-------+----+-----+--------------------------------------+ |id|select_type|table |type |possible_keys|key |key_len|ref |rows |Extra | +--+-----------+------+-----------+-------------+-----------+-------+----+-----+--------------------------------------+ | 1|SIMPLE |ontime|index_merge|Origin,Dest |Origin,Dest|6,6 |NULL|92800|Using union (Origin,Dest); Using where| +--+-----------+------+-----------+-------------+-----------+-------+----+-----+--------------------------------------+
La colonna "rows" costituisce un modo per comparare l'efficienza di index_merge con quella degli altri piani.
A volte è necessario scartare index_merge in favore di un altro piano, per evitare un'esplosione combinatoria di possibili strategie range e/o index_merge. Ma la vecchia logica che MySQL usava per decidere quando escludere index_merge poteva portare a non considerare nemmeno questo piano quando invece era adatto. In particolare, dei predicati AND aggiuntivi nella clausola WHERE potevano portare all'esclusione di index_merge in favore di un piano meno efficiente. Il rallentamento poteva andare da 10x a oltre 100x. Ecco due esempi (basati sulla query sopra riportata) utilizzando MySQL:
MySQL [ontime]> EXPLAIN SELECT * FROM ontime WHERE (Origin='SEA' OR Dest='SEA') AND securitydelay=0; +--+-----------+------+----+-------------------------+-------------+-------+-----+------+-----------+ |id|select_type|table |type|possible_keys |key |key_len|ref |rows |Extra | +--+-----------+------+----+-------------------------+-------------+-------+-----+------+-----------+ | 1|SIMPLE |ontime|ref |Origin,Dest,SecurityDelay|SecurityDelay|5 |const|791546|Using where| +--+-----------+------+----+-------------------------+-------------+-------+-----+------+-----------+ MySQL [ontime]> EXPLAIN SELECT * FROM ontime WHERE (Origin='SEA' OR Dest='SEA') AND depdelay < 12*60; +--+-----------+------+----+--------------------+----+-------+----+-------+-----------+ |id|select_type|table |type|possible_keys |key |key_len|ref |rows |Extra | +--+-----------+------+----+--------------------+----+-------+----+-------+-----------+ | 1|SIMPLE |ontime|ALL |Origin,DepDelay,Dest|NULL|NULL |NULL|1583093|Using where| +--+-----------+------+----+--------------------+----+-------+----+-------+-----------
In questo output, la colonna "rows" indica che il primo caso è quasi 10x meno efficiente e il secondo è più di 15x meno efficiente di index_merge.
A partire da MariaDB 5.3, l'ottimizzatore ritarda l'esclusione dei possibili piani index_merge fino al momento in cui è realmente necessario. Si veda MWL#24
per ulteriori informazioni.
Evitando di scartare i possibili piani index_merge fino a quando è realmente necessario, le due query rimangono efficienti quanto quella originale:
MariaDB [ontime]> EXPLAIN SELECT * FROM ontime WHERE (Origin='SEA' or Dest='SEA'); +--+-----------+------+-----------+-------------+-----------+-------+----+-----+-------------------------------------+ |id|select_type|table |type |possible_keys|key |key_len|ref |rows |Extra | +--+-----------+------+-----------+-------------+-----------+-------+----+-----+-------------------------------------+ | 1|SIMPLE |ontime|index_merge|Origin,Dest |Origin,Dest|6,6 |NULL|92800|Using union(Origin,Dest); Using where| +--+-----------+------+-----------+-------------+-----------+-------+----+-----+-------------------------------------+ MariaDB [ontime]> EXPLAIN SELECT * FROM ontime WHERE (Origin='SEA' or Dest='SEA') AND securitydelay=0; +--+-----------+------+-----------+-------------------------+-----------+-------+----+-----+-------------------------------------+ |id|select_type|table |type |possible_keys |key |key_len|ref |rows |Extra | +--+-----------+------+-----------+-------------------------+-----------+-------+----+-----+-------------------------------------+ | 1|SIMPLE |ontime|index_merge|Origin,Dest,SecurityDelay|Origin,Dest|6,6 |NULL|92800|Using union(Origin,Dest); Using where| +--+-----------+------+-----------+-------------------------+-----------+-------+----+-----+-------------------------------------+ MariaDB [ontime]> EXPLAIN SELECT * FROM ontime WHERE (Origin='SEA' or Dest='SEA') AND depdelay < 12*60; +--+-----------+------+-----------+--------------------+-----------+-------+----+-----+-------------------------------------+ |id|select_type|table |type |possible_keys |key |key_len|ref |rows |Extra | +--+-----------+------+-----------+--------------------+-----------+-------+----+-----+-------------------------------------+ | 1|SIMPLE |ontime|index_merge|Origin,DepDelay,Dest|Origin,Dest|6,6 |NULL|92800|Using union(Origin,Dest); Using where| +--+-----------+------+-----------+--------------------+-----------+-------+----+-----+-------------------------------------+
Questo comportamento è sempre abilitato e non c'è bisogno di attivarlo. Questa ottimizzazione non ha problemi o effetti negativi noti.
