La cache delle Subquery

L'obiettivo della cache delle subquery è ottimizzare l'esecuzione delle subquery, immagazzinando i risultati insieme ai parametri in una cache, ed evitare che vengano rieseguite nel caso in cui il risultato sia presente nella cache.

Amministrazione

A partire da MariaDB 5.3.2-beta la cache è attiva per default. Nelle versioni precedenti di MariaDB è disattivata per default. E' possibile attivarla e disattivarla tramite la variabile optimizer_switch subquery_cache in questo modo:

SET optimizer_switch='subquery_cache=on';

L'efficienza della cache delle subquery è visibile in due variabili statistiche:

• subquery_cache_hit - Contatore globale degli utilizzi della cache.
• subquery_cache_miss - Contatore globale dei casi in cui la cache non viene usata.

Le variabili di sessione tmp_table_size e max_heap_table_size influenzano le dimensioni delle tabelle temporanee in memoria utilizzate per la cache. Queste dimensioni non possono crescere oltre il massimo di queste variabili (si veda la sezione Implementazione per ulteriori dettagli).

Visibilità

L'uso della cache delle subquery può essere analizzato negli warning di EXTENDED EXPLAIN in questo modo: "<expr_cache><//list of parameters//>(//cached expression//)". Per esempio:

MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT b FROM t2);
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

MariaDB [test]> SHOW WARNINGS;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                    |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` where (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`b`)))) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Nell'esempio sopra, la presenza di "<expr_cache><`test`.`t1`.`a`>(...)" indica in quale modo si sta usando la cache delle subquery.

Implementazione

La cache crea una tabella temporanea dove vengono registrati i risultati e tutti i parametri. Ha un indice unico sui parametri. Per prima cosa, la cache viene creata in una tabella MEMORY (se non è possibile farlo, la cache viene disabilitata per l'espressione corrente). Quando la tabella cresce oltre il valore minimo di tmp_table_size e max_heap_table_size, viene controllata la frequenza degli accessi:

• se la frequenza è molto piccola (<0.2) la cache viene disabilitata;
• se la frequenza è moderata (<0.7) la tabella viene svuotata e mantenuta in memoria;
• se la frequenza è alta, la tabella viene scritta su disco.

frequenza = accessi / (accessi + mancati_utilizzi)

Impatto sulle performance

Vi sono alcuni esempi che mostrano come le performance influenzano la cache delle subquery (questi test sono stati eseguiti su un 2.53 GHz Intel Core 2 Duo MacBook Pro with dbt-3 con un dataset).

Esempio 1

Il dataset del benchmark DBT-3, una query per trovare i clienti con un bilancio vicino al massimo per la loro nazione:

select count(*) from customer 
where 
   c_acctbal > 0.8 * (select max(c_acctbal) 
                      from customer C 
                      where C.c_nationkey=customer.c_nationkey
                      group by c_nationkey);

Esempio 2

Benchmark DBT-3, query #17

select sum(l_extendedprice) / 7.0 as avg_yearly 
from lineitem, part 
where 
  p_partkey = l_partkey and 
  p_brand = 'Brand#42' and p_container = 'JUMBO BAG' and 
  l_quantity < (select 0.2 * avg(l_quantity) from lineitem 
                where l_partkey = p_partkey);

Esempio 3

Benchmark DBT-3, query #2

select
        s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
from
        part, supplier, partsupp, nation, region
where
        p_partkey = ps_partkey and s_suppkey = ps_suppkey and p_size = 33
        and p_type like '%STEEL' and s_nationkey = n_nationkey
        and n_regionkey = r_regionkey and r_name = 'MIDDLE EAST'
        and ps_supplycost = (
                select
                        min(ps_supplycost)
                from
                        partsupp, supplier, nation, region
                where
                        p_partkey = ps_partkey and s_suppkey = ps_suppkey
                        and s_nationkey = n_nationkey and n_regionkey = r_regionkey
                        and r_name = 'MIDDLE EAST'
        )
order by
        s_acctbal desc, n_name, s_name, p_partkey;

Esempio 4

Benchmark DBT-3, query #20

select
        s_name, s_address
from
        supplier, nation
where
        s_suppkey in (
                select
                        distinct (ps_suppkey)
                from
                        partsupp, part
                where
                        ps_partkey=p_partkey
                        and p_name like 'indian%'
                        and ps_availqty > (
                                select
                                        0.5 * sum(l_quantity)
                                from
                                        lineitem
                                where
                                        l_partkey = ps_partkey
                                        and l_suppkey = ps_suppkey
                                        and l_shipdate >= '1995-01-01'
                                        and l_shipdate < date_ADD('1995-01-01',interval 1 year)
                                )
        )
        and s_nationkey = n_nationkey and n_name = 'JAPAN'
order by
        s_name;

Vedi anche

• http://mysqlmaniac.com/2012/what-about-the-subqueries/ Un post che descrive l'impatto dell'ottimizzazione della cache delle subquery sulle query utilizzate dall'estensione di MediaWiki DynamicPageList
• http://varokism.blogspot.ru/2013/06/mariadb-subquery-cache-in-real-use-case.html Un altro caso d'uso dal mondo reale
• What is MariaDB 5.3