Что такое селективность индекса в SQL?

Что такое селективность индекса в SQL?

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

Как рассчитывается селективность?

Селективность вычисляется как отношение количества уникальных значений в индексированном столбце к общему количеству строк в таблице:

[ \text{Селективность} = \frac{\text{Количество уникальных значений}}{\text{Общее количество строк}} ]

• Высокая селективность (близко к 1 или 100%): Индекс имеет много уникальных значений. Например, столбец с первичным ключом (ID) обладает максимальной селективностью — каждая строка уникальна. Такие индексы очень эффективны, так как быстро сужают поиск до одной или нескольких записей.
• Низкая селективность (близко к 0): Индекс содержит много повторяющихся значений. Например, столбец gender (пол) с значениями «M» и «F» в большой таблице будет иметь низкую селективность. Использование такого индекса может быть неэффективно, так как он отфильтрует только часть данных, и СУБД может проигнорировать его в пользу полного сканирования таблицы.

Практическое значение селективности

В оптимизации запросов селективность напрямую влияет на решение оптимизатора SQL о выборе плана выполнения:

• Когда использовать индекс? Оптимизатор предпочтёт индекс, если селективность высока (обычно > 5–10%), так как это сокращает количество просматриваемых строк.
• Когда избегать индекса? При низкой селективности (например, < 5%) затраты на чтение индексных страниц и последующий доступ к данным могут превысить выгоду, и полное сканирование таблицы (full scan) будет быстрее.

Пример в SQL (на основе MySQL):

-- Рассчёт селективности для столбца 'email' в таблице 'users'
SELECT 
    COUNT(DISTINCT email) AS unique_values,
    COUNT(*) AS total_rows,
    (COUNT(DISTINCT email) / COUNT(*)) * 100 AS selectivity_percent
FROM users;

Результат может показать, например: 95,000 уникальных email из 100,000 строк → селективность 95% (отличный кандидат для индекса).

Влияние на составные индексы

Селективность критична для составных индексов (multi-column indexes). Порядок столбцов в таком индексе должен учитывать их селективность:

• Первый столбец должен иметь высокую селективность, чтобы максимально отфильтровать данные.
• Низкоселективные столбцы добавляются для покрывающих индексов или дополнительной фильтрации.

Пример создания индекса с учётом селективности:

-- Плохо: низкая селективность у 'status' (значения: 'active', 'inactive')
CREATE INDEX idx_status_created ON orders(status, created_at);

-- Лучше: высокая селективность у 'created_at' (уникальные даты)
CREATE INDEX idx_created_status ON orders(created_at, status);

Ключевые выводы

1. Селективность определяет эффективность индекса — индексируйте столбцы с высокой уникальностью.
2. Мониторьте селективность в больших таблицах, так как она может меняться со временем (например, из-за добавления данных).
3. Используйте селективность для проектирования индексов — избегайте индексов на столбцы с малым количеством уникальных значений (например, флаги, категории с малым набором вариантов), если они не комбинируются с другими столбцами в покрывающем индексе.
4. Анализируйте планы запросов — если оптимизатор игнорирует индекс, проверьте его селективность.

В итоге, понимание селективности позволяет снизить нагрузку на ввод-вывод (I/O) и ускорить выполнение запросов, что особенно важно в высоконагруженных приложениях на PHP, работающих с большими объёмами данных.