Снижение условия индекса

Снижение условия индекса — это оптимизация, применяемая для методов доступа, которые обращаются к данным таблицы через индексы: range, ref, eq_ref, ref_or_null, и Объединённый доступ по ключу.

Идея заключается в проверке части условия WHERE, которая относится к полям индекса (мы называем это *условием индекса, переданным вниз*), как только мы обратились к индексу. Если *условие индекса, переданное вниз*, не выполняется, нам не нужно читать всю запись таблицы.

Снижение условия индекса включено по умолчанию. Чтобы отключить его, установите флаг оптимизатора следующим образом:

SET optimizer_switch='index_condition_pushdown=off'

Когда используется снижение условия индекса, EXPLAIN покажет «Использование условия индекса»:

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 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+

Идея снижения условия индекса

В хранилищах данных на диске поиск по индексу выполняется в два этапа, как показано на рисунке:

Оптимизация снижения условия индекса пытается сократить количество полных чтений записей, проверяя, удовлетворяют ли записи индекса части условия WHERE, которые могут быть проверены для них:

Сколько скорости будет получено, зависит от: — Сколько записей будет отфильтровано — Насколько дорогостоящим было их чтение

Первое зависит от запроса и набора данных. Второе, как правило, больше, когда записи таблицы находятся на диске и/или большие, особенно когда у них есть BLOB-данные.

Пример ускорения

Я использовал данные эталонного теста DBT-3 со значением масштаба = 1. Поскольку эталонный тест определяет очень мало индексов, мы добавили многоколоночный индекс (снижение условия индекса обычно полезно с многоколоновыми индексами: первый(е) компонент(ы) — это то, для чего выполняется доступ к индексу, последующие — это столбцы, по которым мы считываем данные и проверяем условия).

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

Запрос должен был найти крупные (l_quantity > 40) заказы, сделанные в январе 1993 года, на доставку которых потребовалось более 25 дней:

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 без снижения условия индекса:

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

со снижением условия индекса:

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

Ускорение составило:

• Холодный буферный пул: с 5 минут до 1 минуты
• Горячий буферный пул: с 0,19 секунды до 0,07 секунды

Переменные состояния

Существует две переменные состояния сервера:

Таким образом, значение Handler_icp_attempts - Handler_icp_match показывает количество записей, которые серверу не пришлось читать из-за снижения условия индекса.

См. также

• Что такое MariaDB 5.3
• Снижение условия индекса в руководстве MySQL 5.6 (реализации снижения условия индекса MariaDB и MySQL 5.6 имеют одинаковое происхождение, поэтому очень похожи друг на друга).