Освоение индексирования MySQL для повышения производительности запросов

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

Практический вопрос не в том: «Должен ли этот столбец иметь индекс?». Лучший вопрос: «Какой запрос я пытаюсь удешевить, и как я докажу, что индекс помог?». Держите этот вопрос в уме, пока читаете. Хорошее индексирование начинается с реальных запросов, а не со списка столбцов, которые выглядят важными.

Что такое индекс MySQL?

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

Когда вы запрашиваете таблицу, MySQL может использовать индекс для поиска соответствующих строк гораздо быстрее, чем при проверке каждой строки. Это особенно полезно для таблиц с большим количеством строк или для запросов, включающих фильтрацию (предложения WHERE), объединение таблиц (предложения JOIN) или сортировку (предложения ORDER BY).

Как работают индексы

MySQL обычно использует B-деревья для обычных индексов InnoDB. B-дерево хранит ключи в отсортированном порядке, поэтому оно хорошо подходит для поиска по равенству, диапазонам, упорядоченного сканирования и многих объединений. Когда вы создаете индекс по одному или нескольким столбцам, MySQL строит структуру, в которой:

• Листовые узлы содержат фактические указатели на данные или, в случае кластеризованных индексов (например, первичного ключа InnoDB), сами строки данных.
• Внутренние узлы содержат ключи, которые помогают перемещаться по дереву, чтобы найти правильный листовой узел.

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

Типы индексов MySQL

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

1. PRIMARY KEY

• Ограничение PRIMARY KEY гарантирует, что каждое значение в столбце(ах) уникально и не NULL. Оно неявно индексировано.
• Таблица может иметь только один PRIMARY KEY.
• Таблицы InnoDB физически упорядочены по своему первичному ключу (кластеризованный индекс).

Пример:

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

2. UNIQUE индекс

• UNIQUE индекс гарантирует, что все значения в индексированном столбце(ах) различны. Он допускает значения NULL, но допускается несколько NULL (если столбец также не является частью PRIMARY KEY или другого ограничения UNIQUE, которое это предотвращает).
• Полезен для обеспечения целостности данных, когда столбец должен быть уникальным, но не является основным идентификатором.

Пример:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    sku VARCHAR(50) UNIQUE
);

3. INDEX (или KEY)

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

Пример:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    INDEX idx_customer_id (customer_id)
);

4. FULLTEXT индекс

• Используется для полнотекстового поиска по столбцам CHAR, VARCHAR и TEXT.
• Позволяет выполнять поиск по ключевым словам в больших текстовых полях.
• Поддерживается InnoDB в современных версиях MySQL. В более старых установках MySQL могут быть другие ограничения, поэтому проверьте свою версию перед проектированием.

Пример:

CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(255),
    body TEXT,
    FULLTEXT (title, body)
);

5. SPATIAL индекс

• Используется для индексирования пространственных типов данных, таких как точки, линии и полигоны.
• Поведение и требования различаются в зависимости от версии MySQL и движка хранения. Протестируйте точный пространственный запрос, который вы планируете выполнять; не предполагайте, что пространственный индекс поможет любому предикату в стиле ГИС.

6. HASH индекс (ограниченное использование)

• Движок хранения MEMORY MySQL поддерживает индексы HASH. Они предназначены для поиска по равенству, а не для сканирования диапазонов или сортировки.
• Не является индексом общего назначения для большинства распространенных сценариев.

Создание и управление индексами

Как создать индекс

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

1. При создании таблицы:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    hire_date DATE,
    INDEX idx_department (department_id),
    INDEX idx_hire_date (hire_date)
);

2. Изменение существующей таблицы:

-- Добавить одно-столбцовый индекс
ALTER TABLE customers
ADD INDEX idx_email (email);

-- Добавить уникальный индекс
ALTER TABLE users
ADD UNIQUE INDEX uidx_username (username);

-- Добавить много-столбцовый (составной) индекс
ALTER TABLE orders
ADD INDEX idx_customer_date (customer_id, order_date);

Как удалить индекс

Если индекс больше не нужен или отрицательно влияет на производительность (например, во время записи), вы можете его удалить.

-- Удалить стандартный индекс
ALTER TABLE customers
DROP INDEX idx_email;

-- Удалить уникальный индекс
ALTER TABLE users
DROP INDEX uidx_username;

Много-столбцовые (составные) индексы

Составные индексы создаются по двум или более столбцам. Порядок столбцов в составном индексе имеет решающее значение.

• Составной индекс по (col1, col2) может использоваться для запросов, фильтрующих только по col1, или по обоим col1 И col2.
• Обычно он не используется для запросов, фильтрующих только по col2.

Пример:

Рассмотрим индекс по (customer_id, order_date). Этот индекс наиболее эффективен для запросов, таких как:

SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2023-10-27';
SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_date;

Он может быть не очень полезен для SELECT * FROM orders WHERE order_date = '2023-10-27';.

Лучшие практики индексирования MySQL

1. Индексируйте столбцы, используемые в предложениях WHERE, JOIN и ORDER BY

Это наиболее распространенные места, где индексы обеспечивают значительный прирост производительности.

• Предложения WHERE: Условия фильтрации являются основным вариантом использования.
• Условия JOIN: Индексирование столбцов, используемых в предложениях ON операторов JOIN, значительно ускоряет объединение таблиц.
• Предложения ORDER BY и GROUP BY: Индексы могут помочь MySQL избежать операций сортировки.

2. Разумно используйте составные индексы

• Порядок имеет значение: Помещайте столбцы, соответствующие форме запроса, первыми. Фильтры равенства обычно идут перед фильтрами диапазона. Столбцы, используемые для сортировки, могут помочь после столбцов фильтрации.
• Не помещайте слепо самый селективный столбец первым, если ваши реальные запросы не фильтруют по нему. Индекс по (status, created_at) может быть отличным для WHERE status = 'paid' ORDER BY created_at DESC LIMIT 50, даже если status имеет низкую кардинальность, потому что он соответствует шаблону доступа.

3. Используйте EXPLAIN до и после

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

EXPLAIN
SELECT order_id, total, created_at
FROM orders
WHERE customer_id = 123
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 20;

Посмотрите на type, key, rows и Extra. Если key равен NULL, MySQL не выбрал индекс. Если rows все еще близко к размеру таблицы, индекс может быть недостаточно селективным для этого запроса. Если в Extra указано Using filesort, это не обязательно плохо, но это говорит о том, что MySQL не смог вернуть строки в запрошенном порядке из выбранного индекса.

Для MySQL 8.0.18 и более поздних версий EXPLAIN ANALYZE может быть еще более полезным, поскольку он выполняет запрос и сообщает фактическое время и количество строк:

EXPLAIN ANALYZE
SELECT order_id, total
FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 20;

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

4. Стройте индексы вокруг рабочих процессов, а не таблиц

Представьте экран администратора, который показывает последние неудачные платежи:

SELECT id, customer_id, failure_code, created_at
FROM payments
WHERE status = 'failed'
ORDER BY created_at DESC
LIMIT 100;

Индекс только по status может все еще заставлять MySQL сортировать большое количество неудачных строк. Индекс по (status, created_at) обычно лучше подходит, потому что MySQL может найти неудачные строки и прочитать их в порядке времени. Если запрос возвращает только столбцы из индекса, вы можете рассмотреть покрывающий индекс:

CREATE INDEX idx_payments_status_created_cover
ON payments (status, created_at, id, customer_id, failure_code);

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

5. Следите за индексами, которые выглядят правильно, но ничего не делают

Вот распространенные ловушки:

• Функция скрывает индексированное значение: WHERE DATE(created_at) = '2025-01-01'.
• Ведущий подстановочный знак предотвращает нормальное использование B-дерева: WHERE email LIKE '%@example.com'.
• Несоответствие типов вынуждает преобразование: сравнение целочисленного столбца со строкой в кавычках может работать, но может сбивать с толку планы в реальных схемах.
• Индекс начинается с неправильного столбца для запроса: (created_at, customer_id) — это не то же самое, что (customer_id, created_at).

Перепишите предикат, когда это возможно:

WHERE created_at >= '2025-01-01'
  AND created_at <  '2025-01-02'

Эта форма позволяет MySQL использовать сканирование диапазона по created_at.

6. Осторожно удаляйте избыточные и неиспользуемые индексы

Чрезмерное индексирование — это тихая проблема производительности. Каждый дополнительный вторичный индекс должен поддерживаться во время INSERT, UPDATE и DELETE. В таблице с интенсивной записью пять неиспользуемых индексов могут иметь большее значение, чем один медленный SELECT.

В MySQL 5.7 и 8.0 схема sys может помочь вам найти кандидатов:

SELECT *
FROM sys.schema_unused_indexes
WHERE object_schema = 'app';

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

7. Безопасно добавляйте индексы в большие таблицы

На небольших таблицах ALTER TABLE ... ADD INDEX обычно проходит без происшествий. В большой производственной таблице это может быть настоящей операцией. В зависимости от версии MySQL, движка хранения, определения таблицы и точного DDL, добавление индекса может использовать онлайн DDL или может создавать нагрузку через блокировки метаданных, временное пространство, генерацию redo и задержку репликации.

Прежде чем добавить большой индекс, проверьте:

• Насколько велика таблица и существующие индексы.
• Успевают ли реплики.
• Поддерживает ли ваша версия MySQL ожидаемый вами онлайн-алгоритм.
• Может ли приложение выдержать блокировку метаданных, если длинная транзакция блокирует DDL.

Для чувствительных систем используйте инструмент миграции, такой как pt-online-schema-change или gh-ost, или запланируйте DDL на окно с низким трафиком.

Практическая процедура проверки индексов

Когда я проверяю медленный запрос MySQL, я использую следующий порядок:

1. Захватите точный SQL с реальными значениями привязки.
2. Запустите EXPLAIN и, где безопасно, EXPLAIN ANALYZE.
3. Проверьте, соответствуют ли существующие индексы шаблону WHERE, JOIN и ORDER BY.
4. Добавьте наименьший полезный составной индекс в среде разработки.
5. Сравните количество просмотренных строк, время выполнения запроса и влияние на запись.
6. Разверните осторожно и следите за журналами медленных запросов и задержкой репликации.

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