Кэш подзапросов

Цель кэша подзапросов — оптимизировать вычисление коррелированных подзапросов, сохраняя результаты вместе с параметрами корреляции в кэше и избегая повторного выполнения подзапроса в случаях, когда результат уже находится в кэше.

Администрирование

Кэш включен по умолчанию. Его можно отключить, используя настройку optimizer_switch subquery_cache следующим образом:

SET optimizer_switch='subquery_cache=off';

Эффективность кэша подзапросов видна в 2 статистических переменных:

• Subquery_cache_hit — Глобальный счётчик всех попаданий в кэш подзапросов.
• Subquery_cache_miss — Глобальный счётчик всех промахов кэша подзапросов.

Переменные сессии tmp_table_size и max_heap_table_size влияют на размер временных таблиц в памяти, используемых для кэширования. Он не может увеличиваться больше, чем минимум значений вышеперечисленных переменных (подробнее см. раздел Реализация).

Видимость

Ваше использование кэша видно в EXTENDED EXPLAIN выводе (предупреждения) как "<expr_cache><//list of parameters//>(//cached expression//)". Например:

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)

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)

В приведённом выше примере наличие "<expr_cache><`test`.`t1`.`a`>(...)" показывает, что вы используете кэш подзапросов.

Реализация

Каждый кэш подзапросов создаёт временную таблицу, где хранятся результаты и все параметры. Она имеет уникальный индекс по всем параметрам. Сначала кэш создаётся в таблице MEMORY (если это невозможно, кэш становится отключённым для данного выражения). Когда таблица увеличивается до минимума из значений tmp_table_size и max_heap_table_size, проверяется коэффициент попаданий:

• если коэффициент попаданий очень мал (<0,2), кэш отключается.
• если коэффициент попаданий средний (<0,7), таблица очищается (все записи удаляются), чтобы сохранить таблицу в памяти
• если коэффициент попаданий высок, таблица преобразуется в таблицу на диске (для 5.3.0 она может быть преобразована только в таблицу на диске).

hit rate = hit / (hit + miss)

Влияние на производительность

Ниже приведены примеры, демонстрирующие влияние кэша подзапросов на производительность (эти тесты были проведены на MacBook Pro с процессором Intel Core 2 Duo 2,53 ГГц и набором данных dbt-3 scale 1).

Пример 1

Набор данных из эталона DBT-3, запрос для поиска клиентов с балансом, близким к максимальному в своей стране:

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);

Пример 2

Эталон DBT-3, запрос №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);

Пример 3

Эталон DBT-3, запрос №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;

Пример 4

Эталон DBT-3, запрос №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;

См. также

• Кэш запросов
• http://mysqlmaniac.com/2012/what-about-the-subqueries/ блог-пост, описывающий влияние оптимизации кэша подзапросов на запросы, используемые расширением DynamicPageList MediaWiki
• http://varokism.blogspot.ru/2013/06/mariadb-subquery-cache-in-real-use-case.html Ещё один пример использования из реального мира
• Что такое MariaDB 5.3