Хорошо ли закидывать булевые поля в индекс

Булевы (Boolean) поля в индексах: Анализ целесообразности

Вопрос о включении булевых полей в индексы — классический для Data Engineer и DBA. Ответ зависит от множества факторов. Рассмотрим детально.

Краткий ответ

Часто НЕ стоит включать булевы поля в обычные индексы, потому что:

1. Они имеют низкую селективность (всего 2 значения: true/false)
2. Могут привести к раздуванию индекса без выигрыша в производительности
3. Оптимизатор может проигнорировать индекс и выбрать full scan

Но в некоторых случаях это имеет смысл.

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

Селективность — процент уникальных значений в колонке.

Низкая селективность   → Индекс неэффективен
Высокая селективность → Индекс очень полезен

Примеры:

-- Булево поле: 2 значения (true/false)
SELECT COUNT(DISTINCT active) FROM users;
-- Result: 2 (селективность 2 / 1,000,000 = 0.0002%)

-- Email: уникально
SELECT COUNT(DISTINCT email) FROM users;
-- Result: 1,000,000 (селективность 100%)

-- Страна: примерно 200 значений
SELECT COUNT(DISTINCT country) FROM users;
-- Result: 195 (селективность 195 / 1,000,000 = 0.0195%)

Когда булевы индексы НЕЭФФЕКТИВНЫ

Пример 1: Поиск активных пользователей (плохо)

-- Индекс на active (boolean)
CREATE INDEX idx_users_active ON users(active);

-- Запрос
SELECT * FROM users WHERE active = true;

-- Проблемы:
-- - active = true будет match ~500K из 1M строк (50%)
-- - Индекс читает 500K записей, потом full table scan
-- - Дешевле просто full table scan (1 операция вместо 2)

Объяснение:

Индекс на active с value = true:
- Шаг 1: поиск в индексе "active = true" → 500K ссылок на строки
- Шаг 2: полный запрос данных (SELECT *) по ссылкам → 500K row lookups
- Итого: 500K операций

Full table scan:
- Одна операция: read all 1M rows
- Фильтр: true/false за один pass
- Часто быстрее!

Пример 2: Смешанный WHERE с булевым индексом

-- Индексы: idx_active (boolean), idx_country (string)
CREATE INDEX idx_active ON users(active);
CREATE INDEX idx_country ON users(country);

SELECT * FROM users 
WHERE active = true AND country = 'USA';

-- Проблемы:
-- - active имеет низкую селективность
-- - country более селективен
-- - Оптимизатор может использовать idx_country (лучше)
-- - idx_active не будет использован

Когда булевы индексы ЭФФЕКТИВНЫ

Пример 1: Очень редкие значения

-- Таблица с 1M записей, но deleted = true только у 10 записей
CREATE INDEX idx_soft_delete ON events(deleted);

SELECT * FROM events 
WHERE deleted = false;

-- Здесь индекс полезен:
-- - deleted = false: 999,990 строк (99.999%)
-- - deleted = true: 10 строк (0.001%)
-- - Индекс на true очень селективен!

Пример 2: Composite индекс (с другим полем)

-- Не просто boolean, а комбинация с более селективным полем
CREATE INDEX idx_user_active_join_date ON users(active, created_at);

SELECT * FROM users 
WHERE active = true AND created_at > '2024-01-01';

-- Здесь работает хорошо:
-- - active = true фильтрует до ~500K
-- - created_at = '> 2024-01-01' фильтрует дальше до ~50K
-- - Комбинация высокоселективна!

Порядок полей в индексе критичен:

-- ✅ Хорошо: сначала более селективный индекс
CREATE INDEX idx_date_then_active ON users(created_at, active);

SELECT * FROM users 
WHERE active = true AND created_at > '2024-01-01';
-- PostgreSQL использует индекс: created_at filter → active filter

-- ❌ Плохо: булево поле первым
CREATE INDEX idx_active_then_date ON users(active, created_at);

SELECT * FROM users 
WHERE active = true AND created_at > '2024-01-01';
-- PostgreSQL может не использовать индекс (неправильный порядок)

Пример 3: Фильтр с IN

-- Редкий случай полезности булева индекса
CREATE INDEX idx_status_flag ON orders(is_premium);

SELECT * FROM orders 
WHERE is_premium = true;  -- только 1% orders

-- Здесь индекс окупается:
-- - Без индекса: full scan 10M rows
-- - С индексом: read 100K rows из индекса
-- - Выигрыш: 100x

Частичные индексы (Partial Indexes)

Умное решение: создавай индекс только на интересующих строках!

-- ❌ Обычный индекс (неэффективен)
CREATE INDEX idx_active_users ON users(id, name) 
WHERE active = true;
-- Индекс содержит только активных пользователей (500K строк)

-- ✅ Частичный индекс (эффективен)
CREATE INDEX idx_deleted_events ON events(id) 
WHERE deleted = true;
-- Индекс только для удалённых событий (10 строк)

-- Использование:
SELECT * FROM events WHERE deleted = true;
-- PostgreSQL использует этот узкий индекс!

-- Другие queries не используют этот индекс:
SELECT * FROM events WHERE deleted = false;
-- Полный таблсcan более эффективен

Индексы на НУЛЛ значениях

-- Булево поле может быть NULL (три состояния: true, false, NULL)
CREATE TABLE documents (
    id INT,
    title TEXT,
    is_published BOOLEAN  -- может быть NULL
);

-- ❌ Обычный индекс
CREATE INDEX idx_published ON documents(is_published);

-- ✅ Лучше: partial индекс
CREATE INDEX idx_published_true ON documents(id) 
WHERE is_published = true;

-- NULL обычно исключаются из индексов!
SELECT * FROM documents WHERE is_published IS NULL;
-- Это full table scan несмотря на индекс!

PostgreSQL vs MySQL vs Больших БД

PostgreSQL

-- Поддерживает partial индексы (супер!)
CREATE INDEX idx_active_recent ON users(id) 
WHERE active = true AND created_at > now() - interval '30 days';

-- Очень эффективно для булевых полей!

MySQL

-- Нет partial индексов
-- Все булевы значения в обычном индексе
CREATE INDEX idx_users_active ON users(active);

-- Менее гибко

Snowflake / BigQuery

-- Clustering вместо индексов
ALTER TABLE users CLUSTER BY (active, country);

-- Более эффективно для колончатых БД

Практический пример: User Events Table

CREATE TABLE user_events (
    event_id BIGINT PRIMARY KEY,
    user_id INT,
    event_type VARCHAR,
    is_tracked BOOLEAN,   -- 99% = true, 1% = false
    is_sampled BOOLEAN,   -- 10% = true, 90% = false
    created_at TIMESTAMP,
    data JSONB
);

-- ❌ Плохые индексы
CREATE INDEX idx_is_tracked ON user_events(is_tracked);  -- LOW selectivity
CREATE INDEX idx_is_sampled ON user_events(is_sampled);  -- LOW selectivity

-- ✅ Хорошие индексы
-- 1. Partial индекс для редких значений
CREATE INDEX idx_not_tracked ON user_events(event_id) 
WHERE is_tracked = false;  -- только 1% событий

-- 2. Composite индекс для частых запросов
CREATE INDEX idx_user_timestamp ON user_events(user_id, created_at);

-- 3. Partial composite индекс
CREATE INDEX idx_sampled_recent ON user_events(user_id, event_type) 
WHERE is_sampled = true AND created_at > now() - interval '7 days';

-- ✅ Хороший запрос (использует индекс)
SELECT COUNT(*) FROM user_events 
WHERE is_tracked = false;  -- Использует idx_not_tracked

-- ❌ Плохой запрос (full scan)
SELECT * FROM user_events 
WHERE is_tracked = true;  -- Очень много строк, полный scan дешевле

Рекомендации (TL;DR)

Проверка эффективности индекса

-- PostgreSQL: посмотреть план запроса
EXPLAIN ANALYZE
SELECT * FROM users WHERE active = true;

-- Ищи: "Index Scan" (хорошо) vs "Seq Scan" (bad)

-- MySQL: посмотреть статистику
ANALYZE TABLE users;
SELECT * FROM information_schema.statistics 
WHERE table_name = 'users' AND column_name = 'active';

На собеседовании

Расскажи:

1. Селективность: булевы поля имеют низкую селективность (2 значения)
2. Когда плохо: WHERE active = true (50% rows, full scan дешевле)
3. Когда хорошо: Rare values (WHERE deleted = true, 0.1% rows)
4. Partial индексы: Лучший подход для булевых полей
5. Composite индексы: Комбинируй булево с более селективным полем
6. EXPLAIN ANALYZE: Всегда проверяй план запроса

Главное: не слепо добавляй индексы. Анализируй селективность и профилируй запросы!