ИТ.03 - 20 - Индексы и производительность в MySQL

Введение

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

Главный инструмент на старте этой темы - индексы.

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

В этой лекции разберем базовую практику:

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

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

Индекс в БД - это специальная структура данных, которая помогает быстрее находить строки по значениям столбцов.

Если очень упрощенно:

• без индекса MySQL чаще читает таблицу почти целиком;
• с индексом MySQL может перейти сразу к нужному диапазону строк.

Обычно индексы делают для полей, которые часто участвуют в:

• WHERE
• JOIN
• ORDER BY

Как работает поиск без индекса и с индексом

Без индекса

Сервер перебирает строки одну за другой. Чем больше таблица, тем дольше поиск.

С индексом

Сервер использует индекс и сразу идет в нужную часть данных, поэтому читает меньше строк.

Почему индекс работает быстро

В MySQL обычные индексы устроены как B-Tree (древовидная структура из узлов и уровней).

Простая аналогия: это как оглавление в большой книге.

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

С индексом в БД происходит то же самое: MySQL идет по диапазонам ключей сверху вниз и быстро отбрасывает лишние данные.

Структура B-Tree в упрощенном виде:

Как индекс обычно строится:

То есть индекс - это не «магия», а отдельная упорядоченная структура, где хранятся ключи и ссылки на строки.

Наглядно (оценка порядка роста числа сравнений):

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

Неуникальный индекс

Нужен, чтобы ускорить поиск. Повторяющиеся значения допускаются.

Пример: индекс по city.

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

Нужен и для скорости, и для контроля уникальности. Дубли запрещены.

Пример: индекс по email.

Наглядные иллюстрации индексов

Сравнение пути выполнения запроса:

Пример с конкретными ID и фамилиями:

Пошаговое исключение сегментов при поиске фамилии:

Короткий разбор примера:

• выполняем запрос WHERE last_name = 'Сидоров';
• в индексе находим ключ Сидоров -> id=1;
• по id=1 переходим к нужной строке таблицы;
• без индекса пришлось бы проверять много строк подряд.

Учебная база для примеров

Создадим учебную таблицу пользователей и заполним ее данными на 100 человек:

CREATE DATABASE IF NOT EXISTS index_demo;
USE index_demo;

DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  city VARCHAR(100) NOT NULL,
  age TINYINT UNSIGNED NOT NULL
);

INSERT INTO users (email, first_name, last_name, city, age)
SELECT
  CONCAT('student', LPAD(n, 3, '0'), '@example.com') AS email,
  ELT(1 + ((n - 1) % 10), 'Анна', 'Иван', 'Ольга', 'Петр', 'Мария', 'Сергей', 'Елена', 'Дмитрий', 'Нина', 'Алексей') AS first_name,
  ELT(1 + ((n - 1) % 20), 'Иванов', 'Петров', 'Сидоров', 'Кузнецов', 'Смирнов', 'Попов', 'Васильев', 'Новиков', 'Федоров', 'Морозов', 'Волков', 'Соловьев', 'Лебедев', 'Козлов', 'Николаев', 'Егоров', 'Павлов', 'Семенов', 'Голубев', 'Виноградов') AS last_name,
  ELT(1 + ((n - 1) % 10), 'Москва', 'Казань', 'Томск', 'Пермь', 'Самара', 'Уфа', 'Тула', 'Омск', 'Сочи', 'Тверь') AS city,
  18 + ((n - 1) % 8) AS age
FROM (
  SELECT ones.n + tens.n * 10 + 1 AS n
  FROM (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) AS ones
  CROSS JOIN (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) AS tens
) AS seq
WHERE n <= 100;

SELECT COUNT(*) AS total_users FROM users;

Добавление индекса

Неуникальный индекс

ALTER TABLE users
ADD INDEX idx_users_last_name (last_name);

Индекс поможет при частом поиске по last_name.

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

ALTER TABLE users
ADD UNIQUE INDEX uq_users_email (email);

Теперь значения email не могут повторяться.

Если попытаться вставить дубликат email, MySQL вернет ошибку.

Как посмотреть индексы

SHOW INDEX FROM users;

Эта команда показывает:

• имя индекса;
• по какому столбцу он построен;
• уникальный индекс или нет.

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

ALTER TABLE users
DROP INDEX idx_users_last_name;

Удалять индексы полезно, если они больше не нужны и только замедляют запись.

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

Для базовой проверки используется EXPLAIN.

EXPLAIN
SELECT *
FROM users
WHERE last_name = 'Иванов';

Для старта достаточно смотреть три поля:

• type - способ доступа к данным;
• key - какой индекс выбрал MySQL;
• rows - сколько строк MySQL ожидает прочитать.

В чем отличие от SQLite

Синтаксис создания индекса похож, но поведение СУБД не одинаковое.

Практический вывод:

• одинаковый SQL не гарантирует одинаковую скорость;
• после переноса запроса между SQLite и MySQL план нужно проверять заново.

Частые ошибки при работе с индексами

• Добавлять индексы «на все столбцы подряд».
• Не проверять результат через EXPLAIN.
• Индексировать поля, по которым почти нет фильтрации.
• Забывать, что каждый индекс замедляет INSERT и UPDATE.

Практические рекомендации

• Добавляйте индекс только под реальные частые запросы.
• Начинайте с полей из WHERE, JOIN, ORDER BY.
• Для уникальных данных (email, guid) используйте UNIQUE.
• После добавления индекса всегда делайте быструю проверку EXPLAIN.

Самопроверка

Практические задания

Задание 1. Неуникальный индекс

SELECT *
FROM users
WHERE city = 'Москва';

EXPLAIN
SELECT *
FROM users
WHERE city = 'Москва';

ALTER TABLE users
ADD INDEX idx_users_city (city);

EXPLAIN
SELECT *
FROM users
WHERE city = 'Москва';

Задание 2. Уникальный индекс

ALTER TABLE users
ADD UNIQUE INDEX uq_users_email (email);

INSERT INTO users (email, first_name, last_name, city, age)
VALUES ('student001@example.com', 'Анна', 'Иванова', 'Москва', 19);

Задание 3. Просмотр и удаление индекса

SHOW INDEX FROM users;

ALTER TABLE users
DROP INDEX idx_users_city;

SHOW INDEX FROM users;

Полезные ссылки

• MySQL 8.4 Reference Manual - Optimization and Indexes
• MySQL 8.4 Reference Manual - EXPLAIN Statement