Почему оптимизатор запросов SQL может не использовать существующий индекс?

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

1. Низкая селективность индекса: Если индекс содержит много повторяющихся значений (например, столбец gender с значениями 'M'/'F'), сканирование всей таблицы (Full Table Scan) может быть дешевле, чем обращение к индексу с последующими множественными чтениями данных из таблицы (random I/O).

-- Индекс по `status` (всего 3 возможных значения) вряд ли будет использован.
SELECT * FROM orders WHERE status = 'PENDING';

2. Неоптимальный запрос (подавление индекса):

• Использование функций или выражений: WHERE UPPER(name) = 'ALICE' не использует индекс по name. Нужен функциональный индекс: CREATE INDEX idx_upper_name ON users(UPPER(name)).
• Нестрогое неравенство LIKE с % в начале: WHERE email LIKE '%@domain.com' не может использовать индекс по email.
• Операторы OR с незаиндексированными столбцами.
• Сравнение разных типов данных: WHERE int_column = '123' (сравнение integer с string).

3. Статистика устарела: Оптимизатор принимает решения на основе статистики (распределение данных, количество строк). Если статистика не актуальна, оценка стоимости будет неверной.

-- После массового удаления/вставки данных
ANALYZE TABLE table_name; -- Обновление статистики для MySQL/PostgreSQL

4. Маленький размер таблицы: Для очень маленьких таблиц полное сканирование часто быстрее, чем двойной доступ (индекс + таблица).

*5. Использование `SELECT `: Если запрос выбирает много столбцов, не входящих в индекс, стоимость обращения к таблице за каждым найденным ключом может быть высокой. В таких случаях покрывающий индекс (covering index)**, включающий все необходимые столбцы, решает проблему.

-- Плохо для индекса по `id`: нужно идти в таблицу за `name` и `email`.
SELECT id, name, email FROM users WHERE id BETWEEN 100 AND 200;

-- Хорошо: покрывающий индекс `(id, name, email)` позволит выполнить запрос, обращаясь только к индексу.

6. Явное указание использовать/не использовать индекс: В некоторых СУБД можно давать подсказки оптимизатору (hints), например, USE INDEX или FORCE INDEX в MySQL.