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

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

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

Основная формула

Селективность = Количество уникальных значений / Общее количество строк * 100%

Пример:

• Таблица users имеет 1000 строк
• Столбец city содержит 50 уникальных городов
• Селективность = 50 / 1000 * 100% = 5%

Классификация селективности

Высокая селективность (эффективные индексы):

-- Столбец id (уникальный ключ): 10000 строк, 10000 уникальных значений
-- Селективность: 100%
SELECT * FROM users WHERE id = 123;

-- Столбец email (уникальный): 10000 строк, 9999 уникальных значений
-- Селективность: 99.99%
SELECT * FROM users WHERE email = 'ivan@example.com';

-- Столбец username: 10000 строк, 8500 уникальных значений
-- Селективность: 85%
SELECT * FROM users WHERE username = 'ivan';

Низкая селективность (неэффективные индексы):

-- Столбец is_active (boolean): 10000 строк, 2 уникальных значения
-- Селективность: 0.02% (очень низко!)
SELECT * FROM users WHERE is_active = true;  -- Вернёт 5000 строк

-- Столбец country_code: 1000000 строк, 249 уникальных значений
-- Селективность: 0.02%
SELECT * FROM users WHERE country_code = 'US';  -- Индекс может быть неэффективен

-- Столбец status: 100000 строк, 3 уникальных значения (ACTIVE, INACTIVE, PENDING)
-- Селективность: 0.003%
SELECT * FROM orders WHERE status = 'PENDING';

Как оптимизатор использует селективность

Правило 1: Высокая селективность — используй индекс

-- Селективность 100% — индекс точно будет использован
SELECT * FROM users WHERE user_id = 42;
-- План выполнения:
-- Index Scan on users_user_id_idx -> очень быстро!

Правило 2: Низкая селективность — может быть полное сканирование таблицы

-- Селективность 1% — индекс может не использоваться
SELECT * FROM users WHERE is_verified = true;
-- План выполнения:
-- Seq Scan on users -> не использует индекс (может быть быстрее!)

Правило 3: Средняя селективность — зависит от много факторов

-- Селективность 10-50% — оптимизатор решит в зависимости от:
-- - размера таблицы
-- - стоимости I/O
-- - конфигурации
SELECT * FROM users WHERE country = 'USA';  -- Может использовать индекс

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

1. Информация об индексе через ANALYZE:

-- Сначала проанализировать таблицу
ANALYZE users;

-- Просмотреть статистику столбцов
SELECT 
    attname,
    n_distinct,
    null_frac,
    avg_width
FROM pg_stats
WHERE tablename = 'users';

-- Результат:
-- attname        | n_distinct | null_frac | avg_width
-- ----------------+-----------|-----------|----------
-- id             | 10000     | 0.0       | 8
-- email          | 9999      | 0.0001    | 25
-- country        | 245       | 0.001     | 2
-- is_active      | 2         | 0.0       | 1

2. Вычислить селективность вручную:

-- Столбец с высокой селективностью
SELECT 
    tablename,
    attname,
    n_distinct,
    ROUND(100.0 * n_distinct / (SELECT COUNT(*) FROM users), 2) as selectivity_percent
FROM pg_stats
WHERE tablename = 'users' AND attname = 'email';

-- Результат: selectivity_percent = 99.99

3. Посмотреть план выполнения запроса:

-- Команда EXPLAIN показывает, как оптимизатор использует индекс
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM users WHERE email = 'ivan@example.com';

-- Вывод:
--  Index Scan using users_email_idx on users  (cost=0.29..8.30 rows=1)
--    Index Cond: (email = 'ivan@example.com')
--    Heap Blocks: exact=1

Примеры из реальной жизни

1. Хороший индекс — на столбец с высокой селективностью:

CREATE TABLE accounts (
    id BIGSERIAL PRIMARY KEY,
    account_number VARCHAR(50) UNIQUE,  -- Селективность: 100%
    country_code CHAR(2),
    is_active BOOLEAN                    -- Селективность: ~1%
);

-- Хороший индекс
CREATE INDEX ON accounts(account_number);

-- Плохой индекс (селективность низкая)
CREATE INDEX ON accounts(is_active);  -- Редко полезен

2. Составной индекс с селективностью:

-- Таблица: 1000000 заказов
-- user_id: 10000 уникальных значений (селективность: 1%)
-- status: 3 значения (селективность: 0.03%)
-- created_at: 100000 уникальных значений (селективность: 10%)

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    status VARCHAR(20),
    created_at TIMESTAMP
);

-- Правильный порядок в составном индексе
-- (высокая селективность сначала)
CREATE INDEX orders_user_status_date 
ON orders(user_id, status, created_at);

-- Запрос получит пользователя за O(log n) вместо полного сканирования
SELECT * FROM orders 
WHERE user_id = 123 AND status = 'COMPLETED';

3. Когда индекс неполезен:

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    gender CHAR(1),  -- М/Ж — селективность: ~0.5%
    is_verified BOOLEAN,
    country VARCHAR(2)
);

-- Плохой индекс (селективность очень низкая)
CREATE INDEX ON users(gender);  
-- Запрос с этим индексом скорее всего не будет использовать индекс
SELECT * FROM users WHERE gender = 'М';
-- Вернёт ~50% таблицы, сканирование быстрее!

Вычисление селективности для запроса

Как оптимизатор оценивает селективность:

-- Запрос с фильтром
SELECT * FROM users 
WHERE country = 'USA' AND is_verified = true;

-- Оптимизатор оценит:
-- 1. Селективность country = 'USA': 0.5% (вернёт 5000 из 1000000 строк)
-- 2. Селективность is_verified = true: 50% (вернёт 500000 из 1000000)
-- 3. Объединённая селективность: 0.5% * 50% = 0.25% (1250 строк)

-- Если есть индекс на (country, is_verified)
-- Оптимизатор использует его, т.к. результирующая селективность приемлемая

Улучшение селективности

Стратегия 1: Добавить фильтры с высокой селективностью:

-- Плохой запрос (низкая селективность)
SELECT * FROM orders WHERE status = 'PENDING';

-- Хороший запрос (добавили фильтр с высокой селективностью)
SELECT * FROM orders 
WHERE status = 'PENDING' AND user_id = 123;

Стратегия 2: Использовать правильный порядок в составном индексе:

-- Неправильно (низкая селективность сначала)
CREATE INDEX ON orders(status, user_id);  -- status = 0.03%

-- Правильно (высокая селективность сначала)
CREATE INDEX ON orders(user_id, status);  -- user_id = 1%

Заключение

Селективность индекса — критический показатель эффективности индекса в SQL. Высокая селективность означает, что индекс будет использоваться оптимизатором и выполнение запроса будет быстрым. Низкая селективность может привести к тому, что оптимизатор предпочтёт полное сканирование таблицы. Для оптимизации индексов:

1. Изучи селективность столбцов через pg_stats
2. Создавай индексы на столбцы с высокой селективностью
3. Для составных индексов порядок столбцов важен (высокая селективность первой)
4. Используй EXPLAIN для проверки использования индекса
5. Регулярно анализируй статистику через ANALYZE