Индексы в MySQL

Индексы в MySQL — это отличный инструмент для оптимизации SQL-запросов. Чтобы понять, как они работают, давайте рассмотрим, что происходит при работе с данными без индексов.

Содержание

1. Чтение данных с диска
2. Поиск данных в MySQL
3. Сортировка данных
4. Выбор индексов в MySQL
   • Сортировка
   • Внутреннее хранение индексов
   • Уникальные индексы
5. Составные индексы
   • Структура составного индекса
   • Поиск по диапазону
   • Сортировка
6. Использование EXPLAIN для анализа индексов
   • Проверка длины составных индексов
7. Выборочность индексов
8. Первичные ключи
   • Сгруппированные индексы
   • Переплата
   • Когда создавать индексы?
   • Наиболее важные аспекты

Чтение данных с диска

На жестком диске нет понятия "файл" в привычном понимании. Вместо этого существуют блоки. Один файл обычно занимает несколько блоков. Каждый блок знает, какой блок идёт следующим. Файл делится на части, и каждая часть хранится в ��вободном блоке.

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

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

Поиск данных в MySQL

Таблицы MySQL — это обычные файлы. Например, мы можем выполнить запрос:

SELECT * FROM users WHERE age = 29;

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

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

Таким образом, возникают две проблемы при чтении данных:

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

Сортировка данных

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

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

Выбор индексов в MySQL

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

Рассмотрим запрос:

SELECT * FROM users WHERE age = 29;

Нам нужно создать индекс на столбце age:

CREATE INDEX age ON users(age);

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

SELECT * FROM users WHERE age < 29;

Сортировка

Для запросов такого типа:

SELECT * FROM users ORDER BY register_date;

применяется то же правило — создаем индекс на столбце, по которому происходит сортировка:

CREATE INDEX register_date ON users(register_date);

Внутреннее хранение индексов

Представим, что наша таблица выглядит следующим образом:

После создания индекса на столбце age, MySQL сохранит все его значения в отсортированном виде:

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

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

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

SELECT * FROM users WHERE email = '[email protected]';

Уникальный индекс следует создать для столбца email:

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 <= 29. Затем поиск по "male" будет выполнен без использования индекса.

Сортировка

Составные индексы также могут быть использованы, если выполняется сортировка:

SELECT * FROM users WHERE gender = 'male' ORDER BY age;

В этом случае нужно создать индекс в другом порядке, так как сортировка (ORDER) происходит после фильтрации (WHERE):

CREATE INDEX gender_age ON users(gender, age);

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

Будет возможно добавить больше столбцов в индекс при необходимости:

SELECT * FROM users WHERE gender = 'male' AND country = 'UA' ORDER BY age, register_time;

В таком случае следует создать такой индекс:

CREATE INDEX gender_country_age_register ON users(gender, country, age, register_time);

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

Команда EXPLAIN покажет данные о применении индексов для конкретного запроса. Например:

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

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

Как видно, в примере индекс не используется. После создания индекса:

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

Тут уже была прочитана только одна запись, так как был использован индекс.

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

EXPLAIN также поможет определить, используется ли составной индекс правильно. Давайте проверим запрос из примера (с индексом по столбцам age и gender):

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, потому что порядок столбцов в нем позволяет выполнить этот выбор.

Выборочность индексов

Вернемся к запросу:

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

Для такого запроса необходимо создать составной индекс. Но как выбрать правильный порядок столбцов в индексе? Существует два варианта:

• age, gender
• gender, age

Оба будут работать, но с разной эффективностью.

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

SELECT age, COUNT(*) FROM users GROUP BY age;

Это покажет, сколько записей имеет каждое значение в столбце age. Аналогично, можно выполнить:

SELECT gender, COUNT(*) FROM users GROUP BY gender;

Этот анализ подскажет, какой из индексов будет более эффективным.

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

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

CREATE TABLE users (
 id int(10) unsigned NOT NULL AUTO_INCREMENT,
 email varchar(128) NOT NULL,
 name varchar(128) NOT NULL,
 PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

При использовании таблиц InnoDB всегда определяйте первичные ключи. Если первичного ключа нет, MySQL создаст виртуальный скрытый ключ.

Сгруппированные индексы

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

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

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

Переплата

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

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

SHOW TABLE STATUS;

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

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

Наиболее важные аспекты

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

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