Оптимизация многодиапазонного чтения

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

Многодиапазонное чтение можно использовать с

• range доступом
• ref и eq_ref доступом, когда они используют пакетный доступ по ключу

как показано на этой диаграмме:

Идея

Случай 1: Сортировка по идентификаторам строк для диапазонного доступа

Рассмотрим запрос по диапазону:

explain select * from tbl where tbl.key1 between 1000 and 2000;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | tbl   | range | key1          | key1 | 5       | NULL |  960 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

При выполнении этого запроса шаблон доступа к диску будет соответствовать красной линии на этой фигуре:

Выполнение будет попадать в строки таблицы в случайных местах, как отмечено синей линией/числами на рисунке.

Когда таблица достаточно велика, чтение каждой записи таблицы потребует фактического обращения к диску (и будет получено из пула буферов или кэша ОС), и выполнение запроса будет слишком медленным для практического применения. Например, дисковод с скоростью 10 000 об/мин способен выполнить 167 переходов в секунду, поэтому в худшем случае выполнение запроса будет ограничено чтением примерно 167 записей в секунду.

SSD-диски не требуют переходов на диске, поэтому они не пострадают так сильно, но производительность все равно будет низкой во многих случаях.

Оптимизация многодиапазонного чтения направлена на ускорение доступа к диску путем сортировки запросов на чтение записей, а затем выполнения одного упорядоченного сканирования диска. Если вы включите многодиапазонное чтение, EXPLAIN покажет, что используется "Rowid-ordered scan":

set optimizer_switch='mrr=on';
Query OK, 0 rows affected (0.06 sec)

explain select * from tbl where tbl.key1 between 1000 and 2000;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                     |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------+
|  1 | SIMPLE      | tbl   | range | key1          | key1 | 5       | NULL |  960 | Using index condition; Rowid-ordered scan |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------+
1 row in set (0.03 sec)

и выполнение будет проходить следующим образом:

Последовательное чтение данных с диска обычно быстрее, потому что

• Вращающиеся диски не должны перемещать головку туда и обратно
• Можно воспользоваться предварительной выборкой ввода-вывода, выполняемой на разных уровнях
• Каждая страница диска будет читаться ровно один раз, что означает, что мы не будем полагаться на кэш диска (или пул буферов), чтобы избежать повторного чтения одной и той же страницы.

Вышесказанное может существенно повлиять на производительность. Однако есть и нюанс:

• Если вы сканируете небольшие диапазоны данных в таблице, которая достаточно мала, чтобы полностью поместиться в кэш диска ОС, то вы можете заметить, что единственным эффектом MRR является то, что дополнительное буферизация/сортировка добавляют некоторую нагрузку на ЦП.

• LIMIT n и ORDER BY ... LIMIT n запросы с небольшими значениями n могут стать медленнее. Причина в том, что MRR считывает данные в порядке на диске, в то время как ORDER BY ... LIMIT n хочет получить первые n записей в порядке индекса.

Случай 2: Сортировка по идентификаторам строк для пакетного доступа по ключу

Пакетный доступ по ключу может извлечь выгоду из сортировки по идентификаторам строк так же, как и диапазонный доступ. Если у вас есть соединение, которое использует поиск по индексу:

explain select * from t1,t2 where t2.key1=t1.col1;
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref          | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL         | 1000 | Using where |
|  1 | SIMPLE      | t2    | ref  | key1          | key1 | 5       | test.t1.col1 |    1 |             |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------------+
2 rows in set (0.00 sec)

Выполнение этого запроса приведет к тому, что таблица t2 будет попадать в случайные места поисками, сделанными через t2.key1=t1.col. Если вы включите многодиапазонное чтение и пакетный доступ по ключу, вы получите доступ к таблице t2 с помощью Rowid-ordered scan:

set optimizer_switch='mrr=on';
Query OK, 0 rows affected (0.06 sec)

set join_cache_level=6;
Query OK, 0 rows affected (0.00 sec)

explain select * from t1,t2 where t2.key1=t1.col1;
+----+-------------+-------+------+---------------+------+---------+--------------+------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref          | rows | Extra                                                  |
+----+-------------+-------+------+---------------+------+---------+--------------+------+--------------------------------------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL         | 1000 | Using where                                            |
|  1 | SIMPLE      | t2    | ref  | key1          | key1 | 5       | test.t1.col1 |    1 | Using join buffer (flat, BKA join); Rowid-ordered scan |
+----+-------------+-------+------+---------------+------+---------+--------------+------+--------------------------------------------------------+
2 rows in set (0.00 sec)

Преимущества будут аналогичны тем, которые перечислены для range доступа.

Дополнительный источник ускорения — это свойство: если в t1 есть несколько записей с одинаковым значением t1.col1, то обычное соединение вложенных циклов будет выполнять несколько поисков по индексу для одного и того же значения t2.key1=t1.col1. Поиски могут или не могут попадать в кэш в зависимости от размера соединения. С пакетным доступом по ключу и многодиапазонным чтением дубликаты поисков по индексу не будут выполняться.

Случай 3: Сортировка ключей для пакетного доступа по ключу

Давайте еще раз рассмотрим пример соединения вложенных циклов с ref доступом ко второй таблице:

explain select * from t1,t2 where t2.key1=t1.col1;
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref          | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL         | 1000 | Using where |
|  1 | SIMPLE      | t2    | ref  | key1          | key1 | 5       | test.t1.col1 |    1 |             |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------------+

Выполнение этого плана запроса приведет к случайным попаданиям в индекс t2.key1, как показано на этой картинке:

В частности, на шаге #5 мы прочитаем ту же страницу индекса, которую прочитали на шаге #2, а страница, которую мы прочитали на шаге #4, будет перечитана на шаге #6. Если все страницы, к которым вы обращаетесь, находятся в кэше (в пуле буферов, если вы используете InnoDB, и в кэше ключей, если вы используете MyISAM), это не проблема. Однако, если ваш коэффициент попаданий низкий и вы будете обращаться к диску, имеет смысл отсортировать ключи поиска, как показано на этой фигуре:

Это примерно то, что делает оптимизация Key-ordered scan. В EXPLAIN это выглядит следующим образом:

set optimizer_switch='mrr=on,mrr_sort_keys=on';
Query OK, 0 rows affected (0.00 sec)

set join_cache_level=6;
Query OK, 0 rows affected (0.02 sec)
explain select * from t1,t2 where t2.key1=t1.col1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: a
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ref
possible_keys: key1
          key: key1
      key_len: 5
          ref: test.t1.col1
         rows: 1
        Extra: Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
2 rows in set (0.00 sec)

((TODO: Примечание о том, почему сканирование с проходом по кластеризованному первичному индексу InnoDB (который фактически является всей таблицей InnoDB) будет использовать алгоритм Key-ordered scan, а не алгоритм Rowid-ordered scan, хотя концептуально они в этом случае идентичны))

Управление буферным пространством

Как было показано выше, многодиапазонное чтение требует буферов сортировки для работы. Размер буферов ограничен системными переменными. Если MRR должен обработать больше данных, чем может поместиться в его буфер, он разделит сканирование на несколько проходов. Чем больше проходов, тем меньше ускорение, поэтому необходимо найти баланс между слишком большими буферами (которые потребляют много памяти) и слишком маленькими буферами (которые ограничивают возможные ускорения).

Диапазонный доступ

Когда MRR используется для range доступа, размер его буфера контролируется системной переменной mrr_buffer_size. Его значение определяет, сколько места может использоваться для каждой таблицы. Например, если есть запрос, представляющий собой соединение из 10 таблиц, и для каждой таблицы используется MRR, может быть использовано 10*@@mrr_buffer_size байт.

Пакетный доступ по ключу

Когда многодиапазонное чтение используется пакетным доступом по ключу, тогда код BKA управляет буферным пространством, автоматически предоставляя часть своего буферного пространства MRR. Вы можете контролировать количество используемого пространства BKA, установив

• join_buffer_size для ограничения количества памяти, используемой BKA для каждой таблицы, и
• join_buffer_space_limit для ограничения общего количества памяти, используемой BKA в соединении.

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

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

Ненольвые значения Handler_mrr_key_refills и/или Handler_mrr_rowid_refills означают, что сканирование с многодиапазонным чтением не имело достаточной памяти и должно было выполнить несколько проходов сортировки и сканирования ключей/идентификаторов строк. Наибольшее ускорение достигается, когда многодиапазонное чтение выполняет все за один проход, если вы видите много перезаполнений, это может быть полезно для увеличения размеров соответствующих буферов mrr_buffer_size join_buffer_size и join_buffer_space_limit

Влияние на другие переменные состояния

Когда сканирование с многодиапазонным чтением выполняет поиск по индексу (или какую-либо другую «базовую» операцию), счетчик «базовой» операции, например, Handler_read_key, также будет увеличиваться. Таким образом, вы все равно можете увидеть общее количество обращений к индексу, включая те, которые выполняются MRR. Счетчики статистики по пользователю/таблице/индексу также включают количество строк, прочитанных сканированиями с многодиапазонным чтением.

Почему использование многодиапазонного чтения может привести к более высоким значениям в переменных состояния

Многодиапазонное чтение используется для сканирований, которые выполняют полное чтение записей (т.е., они не являются сканированиями «только индекса»). Обычное сканирование, не только индекса, будет читать

1. Запись индекса, чтобы получить идентификатор строки записи таблицы
2. Запись таблицы Оба действия будут выполнены с помощью одного вызова движка хранилища, поэтому результатом вызова будет то, что соответствующий счетчик Handler_read_XXX будет увеличен НА ЕДИНИЦУ, а Innodb_rows_read будет увеличен НА ЕДИНИЦУ.

Многодиапазонное чтение будет выполнять отдельные вызовы для шагов #1 и #2, вызывая ДВА увеличения счетчиков Handler_read_XXX и ДВА увеличения счетчика Innodb_rows_read. Для непосвященного это может показаться, что многодиапазонное чтение ухудшает ситуацию. На самом деле это не так — запрос все равно будет читать те же записи индекса/таблицы, и на самом деле многодиапазонное чтение может обеспечить ускорение, так как оно считывает данные в порядке на диске.

Справочный лист многодиапазонного чтения

• Многодиапазонное чтение используется методом
  • range доступа для сканирования диапазонов.
  • Пакетный доступ по ключу для соединений
• Многодиапазонное чтение может привести к замедлению для небольших запросов над небольшими таблицами, поэтому по умолчанию оно отключено.
• Существует две стратегии:
  • Сканирование в порядке идентификаторов строк
  • Сканирование в порядке ключей
• : и вы можете определить, используется ли какой-либо из них, проверив столбец Extra в выводе EXPLAIN.
• Существует три флага optimizer_switch, которые вы можете включить:
  • mrr=on — включение MRR и сканирования в порядке идентификаторов строк
  • mrr_sort_keys=on — включение сканирования в порядке ключей (вы также должны установить mrr=on для того, чтобы это имело какой-либо эффект)
  • mrr_cost_based=on — включение выбора на основе затрат, использовать ли MRR. В настоящее время не рекомендуется, так как модель затрат пока недостаточно отлажена.

Отличия от MySQL

• MySQL поддерживает только стратегию Rowid ordered scan, которая отображается в EXPLAIN как Using MRR.
• EXPLAIN в MySQL отображает Using MRR, в то время как в MariaDB он может отображать
  • Rowid-ordered scan
  • Key-ordered scan
  • Key-ordered Rowid-ordered scan
• MariaDB использует mrr_buffer_size в качестве предела размера буфера MRR для доступа range, в то время как MySQL использует read_rnd_buffer_size.
• MariaDB имеет три счетчика MRR: Handler_mrr_init, Handler_mrr_extra_rowid_sorts, Handler_mrr_extra_key_sorts, в то время как MySQL имеет только Handler_mrr_init, и он будет считать только сканирования MRR, которые были использованы BKA. Сканирования MRR, используемые для доступа по диапазону, не учитываются.

См. также

• Что такое MariaDB 5.3
• Оптимизация многодиапазонного чтения на странице руководства MySQL