Использование индексов в MySQL

Чем больше мы пользуемся ORM, тем меньше задумываемся об оптимизации БД, до тех пор пока не прижмёт. В простых кейсах для ускорения запроса проблем не возникает, но если случай чуть сложнее чем "добавить индекс", то разработчики часто не знают за что хвататься. Здесь хочу оставить пару заметок, которые могут натолкнуть на различные решения в подобной ситуации.

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

На что обратить внимание при оптимизации?

• Не исключен случай когда одна колонка используется в нескольких индексах. В таком случае MySQL выбирает индекс который вернет наименьшее кол-во строк (наиболее избирательный).
• При использовании составного (композитного) индекса помните, что он может использоваться и в более простых выборках, но только по столбцам перечисленным слева направо. Например индекс (col1, col2, col3) будет работать для выборок (col1), (col1, col2), и (col1, col2, col3), но не будет для (col2, col3) или (col3).

Для получения строк из других таблиц при JOIN

• Для сравнения строковых столбцов оба столбца должны использовать одну и ту же кодировку. Например, сравнение столбца utf8 со столбцом latin1 исключает использование индекса.
• MySQL может использовать индексы более эффективно, если они одного и того же типа и размера. В этом контексте VARCHAR и CHAR считаются одинаковыми, если они объявлены с одинаковым размером. Например, VARCHAR (10) и CHAR (10) имеют одинаковый размер, а VARCHAR (10) и CHAR (15) - нет. Сравнение столбцов разного типа (например, VARCHAR с DATETIME или INT) может препятствовать использованию индексов, если при этом необходимо преобразование. Например в одной таблице у вас INT 1, а в другой VARCHAR ' 1' или '00001'.

Не слишком очевидное

• Индексы менее важны для маленьких таблиц или для больших, из которых нам нужно извлечь все данные или большую их часть. В таком случае последовательное чтение выполняется быстрее, чем при работе с индексом. Всё потому, что последовательное чтение минимизируют поиск на диске, даже если нам нужны не абсолютно все строки.

• Оптимизатору можно задать подсказку по выбору или игнорированию индекса.

SELECT * FROM table1 USE INDEX (col1_index,col2_index)
WHERE col1=1 AND col2=2 AND col3=3;

SELECT * FROM table1 IGNORE INDEX (col3_index)
WHERE col1=1 AND col2=2 AND col3=3;

Повторяющиеся индексы и неиспользуемые индексы

Повторяющиеся индексы могут не замедлить запросы SELECT, но вполне могут замедлить запросы на INSERT (а в некоторых случаях и UPDATE). В целом рекомендуется избегать дублирования ключей. Например если в одной таблице 2 индекса:

KEY firstname (firstname),
KEY firstname_lastname_id (firstname,lastname,id)

то firstname является дубликатом firstname_lastname_id, так как firstname является первым столбцом индекса firstname_lastname_id.

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

Начиная с версии 8+, MySQL поддерживает индексы по убыванию (нисходящие, DESC), что означает, что он может хранить индексы в порядке убывания. Это может пригодиться, когда у вас есть выборки где надо получать последние добавленные данные.

CREATE TABLE t (
	c1 INT, c2 INT,
	INDEX idx1 (c1 ASC, c2 ASC),
	INDEX idx2 (c1 ASC, c2 DESC),
	INDEX idx3 (c1 DESC, c2 ASC),
	INDEX idx4 (c1 DESC, c2 DESC)
);

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