Статьи

Полное сканирование таблицы в сравнении с полным сканированием индекса

Октябрь 31, 2018

Ранее на этой неделе Седрик писал о том, как легко можно запутаться между индексом покрытия и полным сканированием индекса в EXPLAINвыходных данных. В то время как индекс покрытия (видимый EXPLAINкак Extra: Using index) является очень интересной оптимизацией производительности, полное сканирование индекса ( type: index) согласно документации является вторым худшим из возможных планов выполнения после полного сканирования таблицы.
Если очевидно, что полное сканирование таблицы не является хорошим для производительности, сколько мы можем ожидать, если сможем переключиться на полное сканирование индекса? Другими словами, всегда ли полное сканирование таблицы является наихудшим возможным исполнением, и его следует избегать любой ценой?

Давайте возьмем базу данных сотрудников и немного изменим таблицы сотрудников:

mysql> ALTER TABLE employees ADD INDEX idx_first (first_name),ENGINE=InnoDB;

А затем давайте рассмотрим этот запрос:

SELECT * FROM employees ORDER BY first_name;

Этот запрос, конечно, можно выполнить, запустив полное сканирование таблицы, но мы также можем воспользоваться индексом idx_first, который будет переводиться в полное сканирование индекса.
Посмотрим, как оптимизатор выполнит это:

mysql> EXPLAIN SELECT * FROM employees ORDER BY first_name\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 300363
        Extra: Using filesort

Удивительные? Оптимизатор предпочел полное сканирование таблицы, и даже не рассматривал сканирование индекса idx_first как уместный выбор ( possible_keys: NULL).

Что мы получим, если заставим оптимизатор использовать индекс?

mysql> EXPLAIN SELECT * FROM employees FORCE INDEX(idx_first) ORDER BY first_name\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
         type: index
possible_keys: NULL
          key: idx_first
      key_len: 16
          ref: NULL
         rows: 300363
        Extra:

Честно говоря, это выглядит лучше: количество строк одинаковое, но полное сканирование индекса вместо полного сканирования таблицы и отсутствие сортировки файлов. Но предсказать, как запрос будет выполняться, просто взглянув на план выполнения, недостаточно, мы должны выполнить оба запроса и сравнить время выполнения.

Первый случай: таблица сотрудников не помещается в памяти.
При полном сканировании таблицы запрос выполняется примерно за 4 секунды .
При полном сканировании индекса он выполняется примерно через 30 секунд .

Так что оптимизатор был прав в конце концов. Но почему? Просто потому, что все шаблоны доступа не равны. Когда мы выполняем полное сканирование таблицы, мы выполняем последовательное чтение, которое выполняется довольно быстро даже на медленных дисках. Но когда мы используем индекс, мы сначала должны выполнить полное сканирование индекса (быстрое последовательное чтение, без проблем), а затем много случайных чтений, чтобы выбрать строки по значению индекса. И случайное чтение на несколько порядков медленнее, чем последовательное чтение.
Оптимизатор обладает этими знаниями, поэтому он может выбрать правильный план выполнения.

Второй случай: таблица сотрудников помещается в память.
При полном сканировании таблицы запрос выполняется примерно за 3,3 с .
При полном сканировании индекса запрос выполняется примерно за 2,6 с .

Здесь мы видим ограничение оптимизатора: он не знает, на каком типе медиаданных хранится. Если он хранится на вращающихся дисках, предположение о том, что случайное чтение намного медленнее, чем последовательное чтение, является правильным, но это уже не так, если данные хранятся в памяти. Вот почему, когда планы выполнения выглядят одинаково, вы всегда должны выполнять запрос, чтобы действительно увидеть, какой план выполнения следует выбрать. Здесь, если мы знаем, что таблица всегда будет в памяти, мы должны добавить FORCE INDEXподсказку, чтобы обеспечить оптимальное время отклика.

Теперь давайте изменим запрос, выбрав только поле first_name вместо выбора всех полей:

mysql> explain SELECT first_name FROM employees ORDER BY first_name\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
         type: index
possible_keys: NULL
          key: idx_first
      key_len: 16
          ref: NULL
         rows: 300584
        Extra: Using index

Оптимизатор выбирает полное сканирование индекса. Это хороший выбор, потому что индекс теперь покрывает запрос, что означает, что чтение индекса достаточно для получения результатов.

Выводы:

  • Для непокрывающего индекса разница между полным сканированием таблицы и планом выполнения, основанным на полном сканировании индекса, в основном является разницей между последовательным чтением и случайным чтением: оно может быть близким, если у вас быстрое хранилище, или может быть очень разным, если у вас медленное хранение.
  • Полное сканирование индекса может стать интересным, когда индекс покрывает.
  • Не забудьте измерить время отклика, когда вы пробуете разные планы выполнения. Слишком легко сфокусироваться на том, чтобы хорошо выглядеть, но конечному пользователю важно только время отклика!

40
Share: