Если вы занимаетесь программированием на профессиональном уровне, вероятно, вам приходится взаимодействовать с базами данных 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».
[customscript]techrocks_custom_after_post_html[/customscript]
[customscript]techrocks_custom_script[/customscript]
