Урок 10.4 · Время чтения: ~10 мин

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

Введение в SQL-индексы

В прошлом уроке мы научились читать план выполнения через EXPLAIN и находить узкие места в запросах. Теперь логичный следующий шаг - понять основной механизм ускорения выборок: индексы.

Индексы напрямую связаны с тем, как СУБД ищет строки. Без них сервер часто вынужден просматривать таблицу целиком, а с индексом может перейти к нужным данным значительно быстрее.

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

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

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

Чаще всего в реляционных СУБД используются B-tree индексы, которые хорошо работают для:

• точного поиска (=);
• диапазонов (>, <, BETWEEN);
• сортировки (ORDER BY) по индексированному столбцу.

Как индекс влияет на производительность

Ускоряет чтение (SELECT)

Когда условие в WHERE использует индексируемый столбец, СУБД может найти нужные строки без полного сканирования таблицы.

Может замедлять запись (INSERT, UPDATE, DELETE)

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

Занимает дополнительное место

Индексы хранятся отдельно и требуют дискового пространства. Поэтому добавлять индексы на все столбцы подряд - плохая стратегия.

Базовый синтаксис

Создание простого индекса:

CREATE INDEX idx_customer_last_name
ON customer (last_name);

Удаление индекса (синтаксис зависит от СУБД):

DROP INDEX idx_customer_last_name ON customer;

Примечание: в PostgreSQL используется форма DROP INDEX index_name; без указания таблицы.

Пример 1: Ускорение фильтрации по одному столбцу

Допустим, мы часто ищем клиентов по фамилии:

SELECT
   customer_id,
   first_name,
   last_name
FROM customer
WHERE last_name = 'SMITH';

Без индекса по last_name СУБД может выполнять полный просмотр таблицы customer. После создания индекса поиск обычно переходит к более эффективному типу доступа.

Проверка через EXPLAIN:

EXPLAIN
SELECT
   customer_id,
   first_name,
   last_name
FROM customer
WHERE last_name = 'SMITH';

Результат: в плане выполнения вы должны увидеть, что используется индекс (поля key/possible_keys в MySQL или Index Scan в PostgreSQL).

Пример 2: Составной индекс

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

CREATE INDEX idx_payment_customer_date
ON payment (customer_id, payment_date);

Запрос, который хорошо подходит под такой индекс:

SELECT
   payment_id,
   customer_id,
   amount,
   payment_date
FROM payment
WHERE customer_id = 15
  AND payment_date >= '2005-07-01'
ORDER BY payment_date;

Примечание: порядок столбцов в составном индексе важен. В большинстве случаев сначала ставят более часто используемое поле фильтрации.

Когда индекс может не использоваться

Даже если индекс создан, запрос не всегда его применит. Частые причины:

• функция над индексируемым полем в WHERE (YEAR(payment_date));
• поиск с ведущим % (LIKE '%abc');
• слишком низкая селективность столбца;
• неудачный порядок столбцов в составном индексе.

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

SELECT
   payment_id,
   payment_date
FROM payment
WHERE YEAR(payment_date) = 2005;

Более индекс-дружелюбный вариант:

SELECT
   payment_id,
   payment_date
FROM payment
WHERE payment_date >= '2005-01-01'
  AND payment_date < '2006-01-01';

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

• Добавляйте индексы под реальные частые запросы, а не "на всякий случай".
• Начинайте с полей из WHERE, JOIN, ORDER BY.
• После добавления индекса обязательно сравнивайте план через EXPLAIN.
• Следите за балансом: слишком много индексов ухудшают операции записи.

Ключевые выводы этого урока:

• Индекс - это структура, ускоряющая поиск строк в таблице.
• Индексы обычно заметно ускоряют SELECT, но могут замедлять INSERT, UPDATE и DELETE.
• Простые и составные индексы решают разные задачи фильтрации.
• Порядок столбцов в составном индексе критически важен.
• EXPLAIN помогает проверить, использует ли СУБД созданный индекс.

Часто задаваемые вопросы

Создаёт ли первичный ключ индекс автоматически?

Да. В MySQL, PostgreSQL и SQL Server ограничения PRIMARY KEY и UNIQUE автоматически создают индекс. С внешними ключами по-разному: MySQL (InnoDB) создаёт индекс по столбцу внешнего ключа, если его нет, а PostgreSQL — нет, его нужно добавить самому.

Сколько индексов должно быть у таблицы?

Фиксированного числа нет. Каждый индекс ускоряет подходящие под него запросы, но замедляет INSERT, UPDATE и DELETE и занимает место на диске. Индексируйте столбцы из частых WHERE, JOIN и ORDER BY и удаляйте неиспользуемые и дублирующие индексы: индекс по (customer_id) лишний, если есть индекс по (customer_id, payment_date).

Как посмотреть индексы таблицы?

В MySQL выполните SHOW INDEX FROM customer;. В PostgreSQL используйте \d customer в psql или запрос к системному представлению: SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'customer';.

Вопросы для собеседования

Что такое индекс в SQL и зачем он нужен?

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

Почему индекс может ускорить SELECT, но замедлить INSERT?

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

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

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