Раздел 34 · MySQL с нуля

Индексы и EXPLAIN

junior~25 мин

без входаВойди, чтобы отмечать прогресс, вести заметки и решать задачи в редакторе. войти

В прошлом уроке мы научились безопасно менять билеты и залы. В нашем кинотеатре шесть зрителей и одиннадцать билетов, и любой запрос к ним мгновенный. Через год зрителей станет полмиллиона, и запрос «все зрители из Казани» начнёт думать секундами. Сегодня разбираем, что такое индекс и как спросить MySQL, каким путём он собирается выполнить запрос.

Предметный указатель

Тебе нужно найти в справочнике по SQL всё про JOIN. Можно листать 900 страниц подряд. А можно открыть предметный указатель в конце книги: слова там стоят по алфавиту, рядом номера страниц. Нашёл слово, открыл страницы, готово.

Индекс в базе работает так же. Это отдельная структура, где значения колонки разложены по порядку, и у каждого есть ссылка на строку. InnoDB хранит индексы в виде B-дерева: от корня к нужному листу три-четыре шага даже для миллиона строк. Как дерево устроено внутри, разобрано в уроке теории. Здесь смотрим на него снаружи.

EXPLAIN: как ты это сделаешь?

Перед запросом можно написать EXPLAIN. Тогда сервер не выполнит запрос, а расскажет свой план:

EXPLAIN SELECT * FROM viewers WHERE city = 'Казань';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | viewers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Колонок много, но для начала хватит семи:

  • type способ чтения. ALL значит полный просмотр таблицы, худший вариант.
  • possible_keys индексы, которые подошли бы. Здесь NULL: подходящих нет.
  • key индекс, который сервер в итоге выбрал.
  • key_len сколько байтов ключа индекса используется.
  • rows сколько строк сервер ожидает перебрать.
  • filtered какой процент из них, по оценке, пройдёт условие.
  • Extra дополнительные пометки. Using where значит, что условие проверяется на каждой прочитанной строке.

А 1 warning в конце не ошибка: MySQL кладёт в предупреждение переписанный им текст запроса.

Создаём индекс

CREATE INDEX idx_city ON viewers (city);
EXPLAIN SELECT * FROM viewers WHERE city = 'Казань';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | viewers | NULL       | ref  | idx_city      | idx_city | 163     | const |    2 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Теперь type = ref: сервер идёт по индексу к строкам с нужным городом и читает две строки вместо шести. key_len = 163 это 40 символов VARCHAR(40) по 4 байта в utf8mb4, плюс 2 байта на длину и 1 байт на признак NULL.

Самый быстрый способ чтения const: условие на первичный ключ или UNIQUE, строка максимум одна.

EXPLAIN SELECT * FROM films WHERE id = 3;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | films | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Часть индексов у тебя уже есть, хотя ты их не создавал. Первичный ключ и UNIQUE всегда индекс. А InnoDB сам строит индекс под каждый внешний ключ, мы видели это в уроке про внешние ключи. Поэтому поиск сеансов фильма сразу идёт через ref:

EXPLAIN SELECT * FROM screenings WHERE film_id = 1;
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | screenings | NULL       | ref  | film_id       | film_id | 4       | const |    2 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Диапазоны и свежая статистика

Индекс помогает не только для равенства. Раз значения в нём лежат по порядку, сервер может пройти отрезок «от 400 и дальше». Создадим индекс по цене и обновим статистику командой ANALYZE TABLE: по ней оптимизатор оценивает число строк, и после изменений таблицы её стоит пересчитать перед EXPLAIN.

CREATE INDEX idx_price ON screenings (price);
ANALYZE TABLE screenings, viewers;
EXPLAIN SELECT * FROM screenings WHERE price > 400;
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | screenings | NULL       | range | idx_price     | idx_price | 4       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

range значит чтение отрезка индекса. Два сеанса дороже 400 рублей, их сервер и прочитает.

Покрывающий индекс

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

EXPLAIN SELECT id, price FROM screenings WHERE price > 400;
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | screenings | NULL       | range | idx_price     | idx_price | 4       | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

Using index в Extra значит, что ответ собран из одного индекса. Такой индекс называют покрывающим. Это одна из причин не писать SELECT * по привычке.

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

Индекс хранит значения колонки, а не результаты функций от неё. Оберни колонку в функцию, и указатель бесполезен:

EXPLAIN SELECT * FROM viewers WHERE LOWER(city) = 'казань';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | viewers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Снова ALL, хотя idx_city на месте. LOWER здесь ещё и лишний: правило сравнения utf8mb4_0900_ai_ci не различает регистр, city = 'казань' и так найдёт Казань. С датами то же самое: вместо YEAR(registered_at) = 2026 пиши диапазон registered_at >= '2026-01-01' AND registered_at < '2027-01-01'.

Второй случай LIKE. Вспомни указатель: слова на «С» найти легко, они стоят подряд. А слова, в середине которых есть «ер», разбросаны по всему указателю. Создадим индекс по названию фильма:

CREATE INDEX idx_title ON films (title);
EXPLAIN SELECT * FROM films WHERE title LIKE 'С%';
EXPLAIN SELECT * FROM films WHERE title LIKE '%ер%';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | films | NULL       | range | idx_title     | idx_title | 402     | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | films | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |    14.29 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Начало строки известно, значит range по двум фильмам на «С». Шаблон с % в начале, значит полный просмотр.

Составной индекс и левый префикс

Телефонный справочник отсортирован по фамилии, а внутри фамилии по имени. Найти «Иванов Пётр» легко. Найти всех Иванов быстро. А найти всех Петров без фамилии справочник не поможет: Петры разбросаны по всем страницам.

Составной индекс работает так же. Расписание часто ищут по залу и времени, построим индекс по обеим колонкам:

CREATE INDEX idx_hall_start ON screenings (hall_id, starts_at);
EXPLAIN SELECT * FROM screenings WHERE hall_id = 2 AND starts_at >= '2026-10-25';
EXPLAIN SELECT * FROM screenings WHERE starts_at >= '2026-10-25';
EXPLAIN SELECT * FROM screenings WHERE hall_id = 2;
+----+-------------+------------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+------------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | screenings | NULL       | range | idx_hall_start | idx_hall_start | 9       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+------------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | screenings | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |    33.33 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | screenings | NULL       | ref  | idx_hall_start | idx_hall_start | 4       | const |    3 |   100.00 | NULL  |
+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Зал и время: key_len = 9, сервер использует обе колонки (4 байта INT и 5 байтов DATETIME). Только время: ALL, индекс не подошёл. Только зал: ref с key_len = 4, работает первая колонка. Это правило левого префикса. Отсюда совет: первой в составной индекс ставь колонку, по которой ищут чаще всего.

Сортировка

Индекс лежит по порядку, поэтому помогает и ORDER BY. Сравни сортировку по длительности, где индекса нет, и по первичному ключу:

EXPLAIN SELECT title FROM films ORDER BY duration_min;
EXPLAIN SELECT * FROM films ORDER BY id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | films | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | films | NULL       | index | NULL          | PRIMARY | 4       | NULL |    7 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Using filesort значит отдельную сортировку после чтения. Во втором плане type = index: сервер прошёл первичный ключ от начала до конца, и строки уже вышли по порядку.

План соединения

В JOIN у каждой таблицы своя строка плана:

EXPLAIN SELECT f.title, s.starts_at FROM films f JOIN screenings s ON s.film_id = f.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref            | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
|  1 | SIMPLE      | s     | NULL       | ALL    | film_id       | NULL    | NULL    | NULL           |    7 |   100.00 | NULL  |
|  1 | SIMPLE      | f     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | kino.s.film_id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

Читай сверху вниз: MySQL перебирает все сеансы, и для каждого находит его фильм по первичному ключу. eq_ref значит «ровно одна строка на каждую строку предыдущей таблицы», это лучший способ присоединить таблицу. Как соединения работают, мы разбирали в уроке про JOIN.

Цена индекса

Индекс не бесплатный. Каждый INSERT, UPDATE и DELETE обновляет не только таблицу, но и все её индексы, и каждый индекс занимает место на диске. Поэтому индексы строят под настоящие запросы, а не на каждую колонку. Посмотреть, какие индексы есть у таблицы, помогает SHOW INDEX:

SHOW INDEX FROM viewers;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| viewers |          0 | PRIMARY  |            1 | id          | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| viewers |          0 | email    |            1 | email       | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| viewers |          1 | idx_city |            1 | city        | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

Non_unique = 0 у уникальных индексов, Cardinality показывает, сколько разных значений насчитала статистика: у idx_city четыре города, считая NULL. Ненужный индекс удаляют командой DROP INDEX idx_price ON screenings.

Как то же самое выглядит в PostgreSQL и чем его план отличается, смотри в уроке про индексы в PostgreSQL.

Что запомнить

  • Индекс работает как предметный указатель: сервер находит строки, не читая всю таблицу.
  • В EXPLAIN смотри на type, key и rows: ALL значит полный перебор, ref и const значат поиск по индексу.
  • Функция над колонкой в условии, например YEAR(registered_at), выключает индекс.
  • Составной индекс работает слева направо: без первой колонки он почти бесполезен.
  • Каждый индекс замедляет запись и занимает место, поэтому их создают под реальные запросы.

Попробуй сам

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

Поменяй местами колонки в составном индексе и посмотри, какой из двух последних запросов теперь использует индекс. Напиши запрос, который берёт из screenings только hall_id и starts_at, и найди в плане Using index. Проверь EXPLAIN для соединения билетов со зрителями.

Проверь себя

Задание

Дальше

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

домашка

Домашка