Когда составной индекс не используется?

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

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

• Отсутствие префикса: Самый частый случай. Если запрос фильтрует по столбцам, не начиная с первого столбца индекса, сканирование индекса становится неэффективным. Например, для индекса (department, salary) запрос WHERE salary > 5000 не сможет его использовать для поиска, так как первым ключом является department.
• Использование функций или выражений: Если в условии WHERE столбец индекса используется внутри функции (например, UPPER(name)) или арифметического выражения (например, salary + 1000 > 5000), индекс, как правило, не применяется.
• Низкая селективность: Если первый столбец индекса имеет очень мало уникальных значений (например, столбец gender с значениями 'M'/'F'), оптимизатор может решить, что сканирование индекса и последующее обращение к таблице (index scan) будет дороже, чем полное сканирование таблицы (full table scan).
• Несоответствие типа данных: Неявное преобразование типов, например сравнение строкового столбца с числом, может привести к тому, что индекс не будет использован.
• Альтернативные индексы: Оптимизатор может выбрать другой, более подходящий индекс для данного конкретного запроса.

Примеры кода

Рассмотрим таблицу employees и составной индекс idx_dept_sal на столбцы (department_id, salary).

-- Индекс будет использован: фильтр по префиксу (department_id)
SELECT * FROM employees WHERE department_id = 5 AND salary > 3000;

-- Индекс, скорее всего, НЕ будет использован для поиска: нет префикса department_id
SELECT * FROM employees WHERE salary > 3000;
-- Может быть использовано только полное сканирование таблицы или сканирование индекса по salary, если он существует отдельно.

-- Индекс не будет использован из-за функции
SELECT * FROM employees WHERE UPPER(department_name) = 'SALES';
-- Даже если department_name входит в индекс, применение функции UPPER делает индекс неприменимым для поиска.

Практическое применение и вывод

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

Итог: Составной индекс не применяется, когда запрос не фильтрует по префиксу его столбцов, модифицирует их в условии или когда оптимизатор считает сканирование таблицы более эффективным. Это знание помогает избегать лишних индексов и оптимизировать существующие запросы.