Индексы

Таблицы MySQL – это обычные файлы. Выполним запрос такого вида:

SELECT * FROM users WHERE age = 29

MySQL при этом открывает файл, где хранятся данные из таблицы users. А дальше — начинает перебирать весь файл, чтобы найти нужные записи.

Итак, есть две проблемы при чтении данных:

Низкая скорость чтения файлов из-за расположения блоков в разных частях диска (фрагментация).
Большое количество операций сравнения для поиска нужных данных.

Представим, что мы отсортировали наши 10 записей по убыванию. Тогда используя алгоритм бинарного поиска, мы могли бы максимум за 4 операции отобрать нужные нам значения:

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

Индекс – это и есть отсортированный набор значений. В MySQL индексы всегда строятся для какой-то конкретной колонки. Например, мы могли бы построить индекс для колонки age из примера.

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

-- для запроса
SELECT * FROM users WHERE age = 29
-- создадим индекс
CREATE INDEX idx_age ON users(age);

После этой операции MySQL начнет использовать индекс age для выполнения подобных запросов.

Все системы управления базами данных предоставляют возможность просмотра существующих индексов. Для пользователей MySQL существует команда SHOW, которая позволяет отобразить все индексы для конкретной таблицы, как показано в примере ниже:

SHOW INDEX FROM Users;

если необходимо удалить индекс

DROP INDEX idx_email ON Users;

Уникальные индексы

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

-- для запроса
SELECT * FROM users WHERE email = 'user@gmail.com';
-- создадим индекс
CREATE UNIQUE INDEX email ON users(email)

Тогда при поиске данных, MySQL остановится после обнаружения первого соответствия. В случае обычного индекса будет обязательно проведена еще одна проверка (следующего значения в индексе).

Уникальный индекс выполняет две функции:

он обеспечивает все преимущества стандартного индекса
он предотвращает дублирование значений в индексируемом столбце

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

Составные индексы

MySQL может использовать только один индекс для запроса. Поэтому, для запросов, в которых используется несколько колонок, необходимо использовать составные индексы.

-- для запроса
SELECT * FROM users WHERE age = 29 AND gender = 'male'
-- составим индекс
CREATE INDEX age_gender ON users(age, gender);

Очередность колонок в индексе будет играть большую роль. Колонки, которые используются в условиях WHERE, следует ставить в начало индекса. Колонки из ORDER BY — в конец.

Поиск по диапазону

SELECT * FROM users WHERE age <= 29 AND gender = 'male'

Тогда MySQL не сможет использовать полный индекс, т.к. значения gender будут отличаться для разных значений колонки age. В этом случае база данных попытается использовать часть индекса (только age), чтобы выполнить этот запрос:

age_gender -- индекс из примера выше
12male
15female
29male

Сначала будут отфильтрованы все данные, которые подходят под условие age <= 29. Затем, поиск по значению “male” будет произведен без использования индекса.

Сортировка

--для запроса
SELECT * FROM users WHERE gender = 'male' ORDER BY age
--создадим индекс
CREATE INDEX gender_age ON users(gender, age);

Такой порядок колонок в индексе позволит выполнить фильтрацию по первой части индекса, а затем отсортировать результат по второй.

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

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

EXPLAIN SELECT * FROM users WHERE email = 'user@gmail.com';

Колонка key показывает используемый индекс. Колонка possible_keys показывает все индексы, которые могут быть использованы для этого запроса. Колонка rows показывает число записей, которые пришлось прочитать базе данных для выполнения этого запроса (Прочитана всего одна запись, так как был использован индекс).

Проверка длины составных индексов

Explain также поможет определить правильность использования составного индекса.

EXPLAIN SELECT * FROM users WHERE age = 29 AND gender = 'male';

Значение key_len показывает используемую длину индекса. В нашем случае 24 байта – длина всего индекса (5 байт age + 19 байт gender).

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

EXPLAIN SELECT * FROM users WHERE age <= 29 AND gender = 'male';

Это сигнал о том, что созданный индекс не подходит для этого запроса. Если же мы создадим правильный индекс:

Create index gender_age on users(gender, age);
EXPLAIN SELECT * FROM users WHERE age < 29 and gender = 'male';

В этом случае MySQL использует весь индекс gender_age, т.к. порядок колонок в нем позволяет сделать эту выборку.

Селективность индексов

Любое значение колонки age обычно содержит около 200 записей.
Любое значение колонки gender – около 6000 записей.

Если колонка age будет идти первой в индексе, тогда MySQL после первой части индекса сократит количество записей до 200. Останется сделать выборку по ним. Если же колонка gender будет идти первой, то количество записей будет сокращено до 6000 после первой части индекса. Т.е. на порядок больше, чем в случае age.

Это значит, что индекс age_gender будет работать лучше, чем gender_age.

Селективность колонки определяется количеством записей в таблице с одинаковыми значениями. Когда записей с одинаковым значением мало – селективность высокая. Такие колонки необходимо использовать первыми в составных индексах.

Первичные ключи

Первичный ключ (Primary Key) — это особый тип индекса, который является идентификатором записей в таблице

Кластерные индексы

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

Кластерные индексы сохраняют данные записей целиком, а не ссылки на них. При работе с таким индексом не требуется дополнительной операции чтения данных.

Первичные ключи таблиц являются кластерными. Поэтому выборки по ним происходят очень эффективно.

Особенности

Важно помнить, что индексы предполагают дополнительные операции записи на диск. При каждом обновлении или добавлении данных в таблицу, происходит также запись и обновление данных в индексе.

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

show table status;

Когда создавать индексы?

Индексы следует создавать по мере обнаружения медленных запросов. В этом поможет slow log в MySQL. Запросы, которые выполняются более 1 секунды, являются первыми кандидатами на оптимизацию. (настройка в конфиге, вывод в файл)
Начинайте создание индексов с самых частых запросов. Запрос, выполняющийся секунду, но 1000 раз в день наносит больше ущерба, чем 10-секундный запрос, который выполняется несколько раз в день.
Не создавайте индексы на таблицах, число записей в которых меньше нескольких тысяч. Для таких размеров выигрыш от использования индекса будет почти незаметен.
Не создавайте индексы заранее, например, в среде разработки. Индексы должны устанавливаться исключительно под форму и тип нагрузки работающей системы.
Удаляйте неиспользуемые индексы.

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

Если использование индекса требует от MySQL прохода более чем по 30% строк в данной таблице (в таких случаях просмотр таблицы, по всей видимости, окажется намного быстрее, так как потребуется выполнить меньше операций поиска). Следует учитывать, что если подобный запрос использует LIMIT по отношению только к извлекаемой части строк, то MySQL будет применять индекс в любом случае, так как небольшое количество строк можно найти намного быстрее, чтобы вернуть результат.
Если диапазон изменения индекса может содержать величины NULL при использовании выражений ORDER BY ... DESC.