Индексы и EXPLAIN

Ускоряем запросы индексами и учимся читать план выполнения через `EXPLAIN`.

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

Зачем нужны индексы

Без индекса поиск по условию — это полный перебор таблицы (full scan): база читает каждую строку. На миллионе строк это медленно. Индекс — как алфавитный указатель в книге: вместо листания всех страниц вы открываете нужную сразу. Технически это обычно B-дерево, отсортированное по значениям столбца.

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

-- индекс по столбцу email (ускорит поиск по email)
CREATE INDEX idx_users_email ON users(email);

-- составной индекс по двум столбцам
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- посмотреть индексы таблицы
SHOW INDEX FROM users;

Первичный ключ и UNIQUE-столбцы индексируются автоматически. Под внешние ключи в InnoDB индекс тоже создаётся.

EXPLAIN — что делает запрос

EXPLAIN показывает план: как MySQL собирается выполнять запрос, использует ли индекс и сколько строк просмотрит. Ключевой признак проблемы — type = ALL (полный перебор).

EXPLAIN SELECT * FROM users WHERE email = 'anna@mail.ru';

id | type  | key             | rows | Extra
 1 | ref   | idx_users_email |    1 | Using index condition

Здесь key = idx_users_email — индекс используется, rows = 1 — база найдёт строку сразу. Без индекса было бы type = ALL и rows во всю таблицу.

Цена индексов

Индексы не бесплатны. У них две стоимости:

Место на диске — индекс хранится отдельно.
Замедление записи — при каждом INSERT/UPDATE/DELETE индексы нужно обновлять.

Поэтому индексируют не всё подряд, а столбцы, по которым реально часто ищут, фильтруют, сортируют и соединяют (в WHERE, JOIN, ORDER BY). Лишние индексы только вредят.

Демонстрация на песочнице

Создание индекса — переносимо. Запрос отработает так же, но станет быстрее на больших объёмах:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT,
  name TEXT
);

INSERT INTO users (email, name) VALUES
  ('anna@mail.ru', 'Анна'),
  ('boris@mail.ru', 'Борис'),
  ('vera@mail.ru', 'Вера');

CREATE INDEX idx_users_email ON users(email);

SELECT name FROM users WHERE email = 'boris@mail.ru';

Вывод:

name
Борис

Итог

Индекс ускоряет поиск, избавляя от полного перебора таблицы.
Индексируйте столбцы из WHERE, JOIN, ORDER BY; первичный ключ индексируется сам.
EXPLAIN показывает план: type = ALL — тревожный знак полного перебора.
Индексы замедляют запись и занимают место — не плодите лишние.