Почему не всегда применяются индексы?

Почему не всегда применяются индексы в SQL-запросах?

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

1. Неоптимальная структура запроса

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

-- Пример: использование функций или выражений над индексированным полем
SELECT * FROM users WHERE YEAR(created_at) = 2023;
-- Индекс по created_at не будет использоваться, так как применяется функция YEAR()

-- Решение: переписать запрос
SELECT * FROM users 
WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

2. Низкая селективность данных

Если индекс имеет низкую селективность (т.е. выбирает слишком большую часть таблицы), оптимизатор может предчитать полное сканирование таблицы (Full Table Scan). Это происходит, когда:

• Индекс охватывает столбец с малым количеством уникальных значений (например, gender, status).
• Запрос возвращает более 20-30% строк таблицы.

-- Пример: индекс по полу (значения 'M'/'F') в таблице с 1 млн записей
SELECT * FROM employees WHERE gender = 'M';
-- Если 500k записей имеют gender='M', сканирование индекса + обращение к таблице может быть медленнее полного сканирования

3. Отсутствие подходящего индекса

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

-- Пример: составной индекс (category_id, price)
CREATE INDEX idx_category_price ON products(category_id, price);

-- Запрос использует только price без category_id - индекс не подходит
SELECT * FROM products WHERE price > 1000;
-- Индекс не будет использоваться, так как он начинается с category_id

4. Особенности работы с NULL и LIKE

Некоторые операции плохо сочетаются с индексами:

• LIKE с начальным wildcard: WHERE name LIKE '%john%'
• Сравнения с NULL: WHERE column IS NULL (зависит от типа индекса)
• Неравенства: WHERE status != 'active'

-- Индекс по name не будет эффективен
SELECT * FROM users WHERE name LIKE '%son%';

-- Частичное решение: полнотекстовый поиск или reverse index

5. Статистика и стоимость выполнения

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

-- Устаревшая статистика может заставить оптимизатор:
-- 1. Использовать индекс, когда выгоднее сканирование таблицы
-- 2. Игнорировать индекс, когда он был бы полезен

-- Решение: регулярное обновление статистики
ANALYZE TABLE table_name;

6. Ограничения типов индексов

Разные СУБД имеют различные ограничения:

• MySQL/InnoDB: максимальная длина индекса — 3072 байта
• PostgreSQL: индексы не используются для ILIKE без дополнительных расширений
• Составные индексы: работают только с префиксами ключа

7. Влияние на операции записи

Даже если индекс теоретически полезен для чтения, его создание может быть нецелесообразно из-за накладных расходов:

• Замедление INSERT/UPDATE/DELETE: каждый индекс требует обновления при изменении данных
• Дополнительное использование дискового пространства
• Блокировки при обслуживании

8. Маленькие таблицы

Для таблиц, которые помещаются в память (несколько страниц), полное сканирование часто быстрее, чем доступ через индекс с дополнительными обращениями к данным.

9. Принудительное игнорирование индексов

Иногда разработчики явно указывают СУБД не использовать индексы:

-- MySQL
SELECT * FROM users IGNORE INDEX(idx_email) WHERE email = 'test@example.com';

-- PostgreSQL
SET enable_indexscan = off;

10. Особые случаи оптимизатора

Некоторые СУБД имеют специфические оптимизации:

• MySQL: может отказаться от индекса при использовании OR с разными полями
• PostgreSQL: может предпочитать bitmap index scan вместо обычного для нескольких условий

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

1. Анализируйте планы выполнения с помощью EXPLAIN или EXPLAIN ANALYZE
2. Следите за селективностью индексов
3. Регулярно обновляйте статистику
4. Проектируйте индексы под конкретные запросы, а не таблицы
5. Используйте покрывающие индексы (covering indexes), когда это возможно
6. Тестируйте производительность на реалистичных объемах данных

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