Индексы и оптимизация MySQL-запросов

0
555
views
Фото автора Federico Orlandi з Pexels

Если вы занимаетесь программированием на профессиональном уровне, вероятно, вам приходится взаимодействовать с базами данных SQL. Они играют важную роль в разработке, ведь позволяют легко сохранять и получать данные. К тому же, на рынке представлено достаточно бесплатных СУБД с открытым кодом.

Но, несмотря на интенсивность использования баз данных, у многих людей возникают трудности с оптимизацией запросов. Давайте это исправим.

В этой статье речь пойдет о MySQL, но большая часть сказанного будет касаться и любой другой СУБД. Так что вы в любом случае узнаете что-то полезное и сможете применить это на практике.

Мы будем использовать базу данных employees от самой MySQL. В этом репозитории есть инструкции по настройке и запуску базы данных, так что вы сможете вводить команды по мере чтения статьи.

EXPLAIN, пожалуйста

Если вы хотите заняться оптимизацией своих запросов к реляционной БД, первое, с чем вам стоит познакомиться, это команда EXPLAIN. (Англ. explain переводится как «объясни», — прим. перев.).

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

Допустим, у нас есть таблица:

CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,    
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
);

И запрос, который должен найти нам всех сотрудников с именем Joe:

SELECT * FROM employees WHERE first_name = "Joe";

Вы запускаете запрос, прописав впереди EXPLAIN:

EXPLAIN SELECT * FROM employees WHERE first_name = "Joe"\G 

\G здесь касается конкретно MySQL. Это указание выводить данные построчно. Вывод будет таким:

mysql> EXPLAIN SELECT * FROM employees WHERE first_name = "Joe"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 299645
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

То есть, когда мы спрашиваем СУБД, что она думает насчет этого запроса, ее ответ довольно неприятен. Она говорит, что для поиска сотрудников с именем Joe ей придется пересмотреть 299645 строк (в этой таблице их всего 300024, т. е. проверяется практически каждая строка).

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

От редакции Techrocks. Предлагаем также почитать «Запросы SQL: руководство для начинающих». В частности, в этой статье есть раздел о WHERE.

Итак, мы знаем, что нужно найти пользователей с заданным именем, а ответ EXPLAIN показывает, что для данного столбца нет индексов. Давайте добавим:

CREATE INDEX employees_first_name_idx ON employees (first_name);

Запустим EXPLAIN еще раз:

mysql> EXPLAIN SELECT * FROM employees WHERE first_name = "Joe"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: employees_first_name_idx
          key: employees_first_name_idx
      key_len: 58
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

Проверяется всего одна строка!

Как так вышло?

Когда вы просите СУБД создать индекс по столбцу, она создает оптимизированную структуру, которая позволяет быстро находить все строки с нужным значением.

Индексы — волшебное решение. Нужно создать их для всех столбцов, и все будет просто прекрасно. Или нет?

«Нужно иметь индексы для всех столбцов, которые вы используете для запросов к таблице»

Это весьма распространенное заблуждение о работе индексов в реляционных базах данных. Типа, если у вас будет индекс для каждого столбца, любой запрос автоматически будет оптимизирован, ведь база данных сможет использовать все эти индексы для поиска строк. На самом деле все не так.

В некоторых случаях MySQL может использовать больше одного индекса при запросе данных. Если мы создадим отдельные индексы для столбцов first_name и last_name и попробуем найти конкретного пользователя по обоим значениям, мы получим это:

mysql> EXPLAIN SELECT * FROM employees WHERE last_name = 'Halloran' AND first_name = 'Aleksander'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: index_merge
possible_keys: employees_first_name_idx,employees_last_name_idx
          key: employees_last_name_idx,employees_first_name_idx
      key_len: 66,58
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using intersect(employees_last_name_idx,employees_first_name_idx); Using where

В общем, тут по-прежнему все неплохо, проверяется только одна строка. Но в поле Extra появилась любопытная информация —

intersect(employees_last_name_idx,employees_first_name_idx).

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

Если мы создадим один индекс для обоих столбцов (last_name и first_name), результат будет таким:

mysql> EXPLAIN SELECT * FROM employees WHERE last_name = 'Halloran' AND first_name = 'Aleksander'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: employees_first_name_idx,employees_last_name_idx,employees_last_first_name_idx
          key: employees_last_first_name_idx
      key_len: 124
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

Сервер, чтобы найти соответствующую строку, погружается в один индекс, а не в два. Наличие нескольких столбцов в индексе также помогает с покрывающими индексами, к которым мы еще вернемся.

Подбор столбцов для индексации

Выбор индексов зависит от того, как вы составляете запросы к таблице и какие поля входят в эти запросы.

Когда мы ищем в таблице сотрудников, мы хотим находить их по имени и фамилии, поэтому создаем индекс для обоих столбцов. Следует учитывать, что при создании индекса для нескольких столбцов их порядок имеет значение.

Допустим, у нас есть сотрудник Georgi Facello. В нашем индексе он будет значиться как Facello-Georgi (наш индекс — last_name, first_name).

Поэтому такой индекс будет полезен только для запросов, в которых вы ищете фамилию и имя или только фамилию. Для поиска только по имени этот индекс не пригодится, поскольку читается слева направо. Тут нужен индекс, начинающийся с first_name.

Наш индекс (last_name, first_name) также не подойдет для случаев, когда нужно найти человека с определенной фамилией ИЛИ именем. Для такого поиска нужно, чтобы и last_name, и first_name стояли в индексе крайними слева. Поэтому в таких случаях индексы для двух столбцов должны быть отдельными. MySQL при обработке запроса запустит объединение этих индексов.

А если вы запрашиваете все или практически все столбцы в индексе, в каком порядке они должны идти?

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

mysql> SELECT COUNT(DISTINCT emp_no)/COUNT(*) FROM employees;
+---------------------------------+
| COUNT(DISTINCT emp_no)/COUNT(*) |
+---------------------------------+
|             1.0000              |
+---------------------------------+

Первичный ключ — прекрасный пример. Каждая строка имеет уникальное значение, поэтому в качестве среднего значения мы получаем единицу. Значения для столбцов, которые мы будем использовать в индексе, должны быть как можно ближе к единице. Давайте проверим столбец first_name:

mysql> SELECT COUNT(DISTINCT first_name)/COUNT(*) FROM employees;
+-------------------------------------+
| COUNT(DISTINCT first_name)/COUNT(*) |
+-------------------------------------+
|               0.0042 |
+-------------------------------------+

А теперь last_name:

mysql> SELECT COUNT(DISTINCT last_name)/COUNT(*) FROM employees;
+------------------------------------+
| COUNT(DISTINCT last_name)/COUNT(*) |
+------------------------------------+
|               0.0055               |
+------------------------------------+

Итак, last_name даст нам лучшую фильтрацию, чем first_name, а значит, этот столбец должен идти левее в индексе.

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

Проверим last_name:

mysql> SELECT last_name, COUNT(*) AS total FROM employees GROUP BY last_name ORDER BY total DESC LIMIT 10;
+-----------+-------+
| last_name | total |
+-----------+-------+
| Baba      |  226  |
| Gelosh    |  223  |
| Coorg     |  223  |
| Sudbeck   |  222  |
| Farris    |  222  |
| Adachi    |  221  |
| Osgood    |  220  |
| Mandell   |  218  |
| Neiman    |  218  |
| Masada    |  218  |
+-----------+-------+

Итак, здесь выбросов нет. Значения довольно близки между собой. Давайте проверим first_name:

mysql> SELECT first_name, COUNT(*) AS total FROM employees GROUP BY first_name ORDER BY total DESC LIMIT 10;
+-------------+-------+
| first_name | total |
+-------------+-------+
| Shahab      |  295 |
| Tetsushi    |  291 |
| Elgin       |  279 |
| Anyuan      |  278 |
| Huican      |  276 |
| Make        |  275 |
| Sreekrishna |  272 |
| Panayotis   |  272 |
| Hatem       |  271 |
| Giri        |  270 |
+-------------+-------+

Тоже неплохо. Значения не слишком разбросаны. Порядок столбцов, который мы определили для индекса, вполне хорош.

Индексы и сортировка

При создании индексов также следует учесть порядок сортировки результатов.

СУБД использует индекс, чтобы быстро находить строки, но также может и сортировать результаты, если вы сортируете по тем же столбцам, что содержатся в индексе, и в том порядке, в котором они идут в индексе.

В нашем случае с индексом last_name, first_name это означает ORDER BY last_name или ORDER BY last_name, first_name.

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

Покрывающие индексы

Еще одна весомая причина иметь индексы, содержащие несколько столбцов, это функция покрывающих индексов, которую предоставляет MySQL. Когда ваш запрос загружает только первичный ключ и столбцы в индексе, СУБД не надо даже искать в таблице и читать результаты. Она читает все из индекса. Вот как это выглядит:

mysql> explain select emp_no, last_name, first_name from employees where last_name = "Baba"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: employees_last_name_idx,employees_last_first_name_idx
          key: employees_last_first_name_idx
      key_len: 66
          ref: const
         rows: 226
     filtered: 100.00
        Extra: Using index

Смотреть надо на Extra: Using index. Это означает, что все данные читаются только из индекса. Поскольку индекс уже содержит всю необходимую информацию (все индексы включают первичный ключ таблицы), СУБД загружает все из него и возвращает нам, даже не заходя в таблицу. Это наилучший сценарий для запросов, особенно если ваш индекс помещается в память.

Создание нескольких индексов

Создание индексов не обходится даром. Они позволяют нам быстрее находить данные, но вместе с тем замедляют внесение изменений в таблицу, поскольку запись в проиндексированные столбцы приводит к обновлению индексов. Поэтому нужно искать баланс между желанием сделать быстрыми как можно больше запросов и потребностью не замедлять команды INSERT/UPDATE/DELETE.

Итоги

Итак, при оптимизации запросов помните о следующем:

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

Перевод статьи «Optimizing your MySQL queries».

ОСТАВЬТЕ ОТВЕТ

Please enter your comment!
Please enter your name here