Index Condition Pushdown

Index Condition Pushdown è un'ottimizzazione che riguarda i metodi di accesso che fanno uso degli indici: range, ref, eq_ref, ref_or_null e Batched Key Access. L'idea è controllare la parte della condizione WHERE che si riferisce ai campi indice (chiamati Pushed Index Condition) appena effettuato l'accesso all'indice. Se la Pushed Index Condition non viene soddisfatta, non occorre leggere l'intero record.

A partire da MariaDB 5.3.3, la Index Condition Pushdown è on per default. Per disabilitarla, impostare il suo flag in optimizer_switch, in questo modo:

SET optimizer_switch='index_condition_pushdown=off'

Quando si usa la Index Condition Pushdown, EXPLAIN mostra "Using index condition":

MariaDB [test]> explain select * from tbl where key_col1 between 10 and 11 and key_col2 like '%foo%';
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | tbl   | range | key_col1      | key_col1 | 5       | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
1 row in set (0.01 sec)

L'idea dietro a Index Condition Pushdown

Gli Storage Engine che usano il disco eseguono le ricerche su indice in due passaggi, come illustrato nella figura:

L'ottimizzazione Index Condition Pushdown cerca di ridurre il numero di record letti interamente verificando se le voci nell'indice soddisfano la parte della condizione WHERE che è possibile utilizzare:

Quanto questa ottimizzazione velocizza le query dipende da: - Quanti record possono essere filtrati - Quanto è costoso leggerli

Il primo punto dipende dalla query e dai dati. Il secondo è generalmente più elevato quando i record sono su disco e/o di grandi dimensioni, specie per i blob.

Esempio di velocizzazione

Nell'esempio si useranno i dati di benchmark DBT-3, con un fattore di scala di 1. Poiché il benchmark comprende pochissimi indici, si aggiungerà un indice multi-colonna (l'Index Condition Pushdown è molto utile con gli indici multi-colonna: l'accesso all'indice viene effettuato per il primo componente, i successivi sono colonne che verranno lette e sulle quali verranno verificate le condizioni).

alter table lineitem add index s_r (l_shipdate, l_receiptdate);

La query serve a trovare grandi (l_quantity > 40) ordini effettuati nel gennaio 1993, la cui consegna ha richiesto più di 25 giorni:

select count(*) from lineitem
where
  l_shipdate between '1993-01-01' and '1993-02-01' and
  datediff(l_receiptdate,l_shipdate) > 25 and
  l_quantity > 40;

EXPLAIN senza Index Condition Pushdown:

-+----------+-------+----------------------+-----+---------+------+--------+-------------+
 | table    | type | possible_keys         | key | key_len | ref | rows    | Extra       |
-+----------+-------+----------------------+-----+---------+------+--------+-------------+
 | lineitem | range | s_r                  | s_r | 4       | NULL | 152064 | Using where |
-+----------+-------+----------------------+-----+---------+------+--------+-------------+

Con Index Condition Pushdown:

-+-----------+-------+---------------+-----+---------+------+--------+------------------------------------+
 | table     | type | possible_keys | key | key_len | ref | rows     | Extra                              |
-+-----------+-------+---------------+-----+---------+------+--------+------------------------------------+
 | lineitem | range | s_r            | s_r | 4       | NULL | 152064 | Using index condition; Using where |
-+-----------+-------+---------------+-----+---------+------+--------+------------------------------------+

La velocizzazione è la seguente:

• Con il buffer pool freddo: da 5 min a un minimo di 1 min
• Con il buffer pool caldo: da 0.19 sec a un minimo di 0.07 sec

Variabili di stato

Vi sono due variabili server di stato

In pratica, il valore Handler_icp_attempts - Handler_icp_match indica quanti record non è stato necessario leggere grazie alla Index Condition Pushdown.

Vedi anche

• Cosa c'è in MariaDB 5.3
• Index Condition Pushdown nel manuale di MySQL 5.6 (l'implementazione della Index Condition Pushdown di MariaDB e quella di MySQL 5.6 hanno la stessa origine e sono molto simili tra loro).