[Эта статья была написана Stephane Combaudon]
В предыдущем посте мы обсуждали, как разрабатывать индексы для многих типов запросов, используя одну таблицу. Вот реальный пример проблем, с которыми вы столкнетесь при попытке оптимизировать запросы: два похожих запроса, но один выполняет полное сканирование таблицы, а другой использует индекс, который мы специально создали для этих запросов. Ошибка или ожидаемое поведение? Читать дальше!
Наши два похожих запроса
# Q1
mysql> explain select col1, col2 from t where ts >= '2015-04-30 00:00:00';
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | t | ALL | ts | NULL | NULL | NULL | 4111896 | Using where |
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+
# Q2
mysql> explain select count(*) from t where ts >='2015-04-30 00:00:00';
+----+-------------+---------------+-------+---------------+--------------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+--------------+---------+------+---------+--------------------------+
| 1 | SIMPLE | t | range | ts | ts | 5 | NULL | 1809458 | Using where; Using index |
+----+-------------+---------------+-------+---------------+--------------+---------+------+---------+--------------------------+Q1 выполняет сканирование полной таблицы, в то время как Q2 использует индекс ts, который, кстати, покрывает — см. Using index В Extra поле. Почему такие разные планы выполнения?
Давайте попробуем понять, что происходит с Q1.
Это запрос с одним неравенством в поле ts, и у нас есть индекс ts. Оптимизатор пытается увидеть, пригоден ли этот индекс ( possible_keys поле), это все очень логично. Теперь, если мы посмотрим на rows поле для Q1 и Q2, мы увидим, что индекс позволит нам читать только 45% записей (1,8 млн. Из 4,1 млн.). Конечно, это не отлично, но в любом случае это должно быть намного лучше, чем полное сканирование таблицы, верно?
Если вы так думаете, внимательно прочитайте, что будет дальше. Потому что это предположение просто не правильно!
Оценка стоимости плана выполнения (упрощенно)
Прежде всего, оптимизатор не знает, находятся ли данные или индексы в памяти или их нужно прочитать с диска, он просто предположит, что все находится на диске. Однако он знает, что последовательное чтение намного быстрее, чем случайное чтение.
Итак, давайте выполним Q1 с включенным индексом ts. Шаг 1 — выполнить сканирование диапазона по этому индексу, чтобы определить записи 1.8M, которые соответствуют условию: это последовательное чтение, так что это довольно быстро. Однако в настоящее время шаг 2, чтобы получить col1 и col2 поля для каждой записи , которые соответствуют условию. Индекс предоставляет значение первичного ключа для каждой соответствующей записи, поэтому нам нужно будет выполнить поиск первичного ключа для каждой соответствующей записи.
Вот в чем проблема: поиск первичного ключа 1.8M эквивалентен случайному чтению 1.8M, поэтому это займет много времени. Намного больше времени, чем последовательное чтение полной таблицы (что означает полное сканирование первичного ключа, потому что мы используем InnoDB здесь).
Сравните это с тем, как можно выполнить Q2 с включенным индексом ts. Шаг 1 такой же: определите 1,8M совпадающих записей. Но разница в том, что нет второго шага! Вот почему мы называем этот индекс «индексом покрытия»: нам не нужно выполнять точечные запросы по первичному ключу, чтобы получить дополнительные поля. Поэтому на этот раз использование индекса on ts гораздо эффективнее, чем чтение полной таблицы (что опять-таки означает, что мы будем выполнять сканирование первичного ключа в полной таблице).
Теперь есть еще одна вещь, которую нужно понять: сканирование полной таблицы — это последовательная операция, когда вы думаете об этом с логической точки зрения, однако страницы InnoDB, безусловно, не хранятся последовательно на диске. Таким образом, на уровне диска полная таблица больше похожа на многократное случайное чтение, чем на одно большое последовательное чтение.
Однако это все еще намного быстрее, чем очень большое число или точечный запрос, и легко понять, почему: когда вы читаете страницу размером 16 КБ для полного сканирования таблицы, будут использоваться все записи. Когда вы читаете страницу размером 16 КБ для случайного чтения, вы можете использовать только одну запись. Таким образом, в худшем случае чтение 1,8M записей потребует 1,8M случайного чтения, в то время как чтение полной таблицы с записями 4M потребует только 100K случайного чтения — полное сканирование таблицы все еще на порядок быстрее.
Оптимизация нашего запроса
Теперь, когда мы поняли, почему оптимизатор выбрал полное сканирование таблицы для Q1, есть ли способ ускорить его работу с помощью индекса? Если мы сможем создать индекс покрытия, нам больше не понадобятся дорогостоящие поиски первичного ключа. Тогда оптимизатор, скорее всего, выберет этот индекс для полного сканирования таблицы. Создать такой индекс покрытия легко:
ALTER TABLE t ADD INDEX idx_ts_col1_col2 (ts, col1, col2);Некоторые из вас могут возразить, что из-за неравенства tsдругие столбцы использовать нельзя. Это было бы верно, если бы у нас были условия в col1 или col2 в WHERE предложении, но это не применяется здесь, так как мы только добавляем эти дополнительные столбцы, чтобы получить индекс покрытия.
Вывод
Понимание того, как индексы можно использовать для фильтрации, сортировки или покрытия, имеет первостепенное значение для возможности оптимизации запросов, даже простых. Понимание (даже приблизительно) того, как выполняется запрос в соответствии с заданным планом выполнения, также очень полезно. В противном случае вы будете иногда озадачены решениями, принятыми оптимизатором.
Также обратите внимание, что начиная с MySQL 5.7, модель стоимости может быть настроена . Это может помочь оптимизатору принимать более правильные решения: например, случайное чтение намного быстрее при быстром хранении, чем на обычных дисках.