На что обращаешь внимание при индексации колонок?

Индексация колонок базы данных: Практические рекомендации

Отличный вопрос о оптимизации производительности БД! Индексирование — это критический аспект архитектуры приложения. Позвольте разобраться, на что обращать внимание при создании индексов.

Основные принципы индексирования

Индекс — это структура данных (обычно B-tree), которая ускоряет поиск строк по значениям колонок. Но неправильное использование может замедлить БД!

1. Выбор колонок для индексирования

Индексируй колонки, которые часто используются в:

-- Часто используется в WHERE
CREATE INDEX idx_user_email ON users(email);

-- Часто используется в JOIN
CREATE INDEX idx_order_user_id ON orders(user_id);

-- Часто используется в ORDER BY
CREATE INDEX idx_user_created_at ON users(created_at DESC);

НЕ индексируй:

• Колонки с низкой селективностью (cardinality) — много одинаковых значений
• Очень малые таблицы (< 1000 строк)
• Колонки, редко используемые в запросах

-- ❌ Плохо - низкая селективность (только M/F/O)
CREATE INDEX idx_gender ON users(gender);

-- ✅ Хорошо - высокая селективность
CREATE INDEX idx_user_id ON users(id);
CREATE INDEX idx_email ON users(email);  -- Уникальные значения

2. Анализ статистики индекса

Селективность (Cardinality) — отношение уникальных значений к всем строкам:

-- PostgreSQL - проверка статистики индекса
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,              -- Сколько раз индекс использован
    idx_tup_read,          -- Туплы прочитаны через индекс
    idx_tup_fetch          -- Туплы извлечены
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- MySQL - проверка индексов
SHOW INDEX FROM users;

-- SQL Server
EXEC sp_helpindex 'users';

3. Типы индексов

B-tree индекс — по умолчанию, для большинства случаев:

-- Простой индекс
CREATE INDEX idx_user_email ON users(email);

-- Уникальный индекс
CREATE UNIQUE INDEX idx_user_email_unique ON users(email);

-- Составной индекс (несколько колонок)
CREATE INDEX idx_user_dept_role ON users(department_id, role);

Hash индекс — для точного совпадения, быстрее для = операторов:

-- PostgreSQL
CREATE INDEX idx_email_hash ON users USING hash(email);

-- Хорош для точного поиска, плохо для диапазонов
-- SELECT * FROM users WHERE email = 'test@example.com';  -- БЫСТРО
-- SELECT * FROM users WHERE email LIKE 'test%';         -- МЕДЛЕННО

GiST, GIN индексы — для полнотекстового поиска:

-- PostgreSQL - для JSONB
CREATE INDEX idx_data_gin ON documents USING gin(data);

-- PostgreSQL - для текстового поиска
CREATE INDEX idx_search_tsvector ON documents 
    USING gin(to_tsvector('english', content));

4. Составные индексы (Composite Indexes)

Порядок колонок критичен — первая колонка используется для быстрого поиска:

-- Таблица
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    status VARCHAR(20),
    created_at TIMESTAMP
);

-- Составной индекс
CREATE INDEX idx_orders_user_status 
    ON orders(user_id, status, created_at);

-- ✅ БЫСТРО - использует индекс полностью
SELECT * FROM orders 
WHERE user_id = 5 AND status = 'completed';

-- ✅ БЫСТРО - использует префикс индекса
SELECT * FROM orders WHERE user_id = 5;

-- ⚠️ МЕДЛЕННО - не использует индекс полностью
SELECT * FROM orders WHERE status = 'completed';

-- ⚠️ МЕДЛЕННО - status не в индексе первым
SELECT * FROM orders 
WHERE status = 'completed' AND user_id = 5;

Правило ESR (Equality, Sort, Range):

-- Лучший порядок: Equality → Sort → Range
CREATE INDEX idx_orders_esr ON orders(user_id, created_at, price);

-- Query 1: Equality на user_id, Sort по created_at
SELECT * FROM orders 
WHERE user_id = 5 
ORDER BY created_at DESC;
-- ✅ ОЧЕНЬ БЫСТРО

-- Query 2: Equality на user_id, Range на price
SELECT * FROM orders 
WHERE user_id = 5 AND price > 100;
-- ✅ БЫСТРО

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

Индексируй только нужные строки:

-- PostgreSQL - индекс только для активных пользователей
CREATE INDEX idx_active_users 
    ON users(email) 
    WHERE is_active = true;

-- Экономит место и ускоряет вставки/обновления
SELECT * FROM users WHERE email = 'test@example.com' AND is_active = true;
-- ✅ Использует индекс

SELECT * FROM users WHERE email = 'test@example.com' AND is_active = false;
-- ❌ НЕ использует индекс (не соответствует WHERE условию)

6. Индексы на выражения

-- PostgreSQL - индекс на функцию
CREATE INDEX idx_users_email_lower 
    ON users(LOWER(email));

SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- ✅ Использует индекс

-- MySQL
CREATE INDEX idx_users_email_lower 
    ON users(LOWER(email));

7. Размер индекса

Большие индексы замедляют INSERT/UPDATE/DELETE операции:

-- PostgreSQL - проверка размера индекса
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_indexes
JOIN pg_stat_user_indexes ON indexrelname = indexname
ORDER BY pg_relation_size(indexrelid) DESC;

-- MySQL - размер индекса
SELECT 
    INDEX_NAME,
    SEQ_IN_INDEX,
    COLUMN_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_NAME = 'users'
ORDER BY SEQ_IN_INDEX;

8. Избегайте Over-indexing

-- ❌ ПЛОХО - слишком много индексов (замедляет INSERT/UPDATE)
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    name VARCHAR(100),
    phone VARCHAR(20),
    address VARCHAR(255),
    created_at TIMESTAMP
);
CREATE INDEX idx1 ON users(email);
CREATE INDEX idx2 ON users(name);
CREATE INDEX idx3 ON users(phone);
CREATE INDEX idx4 ON users(address);
CREATE INDEX idx5 ON users(created_at);
-- Каждый UPDATE требует обновления всех индексов!

-- ✅ ХОРОШО - только часто используемые индексы
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_created_at ON users(created_at);

9. Мониторинг использования индексов

-- PostgreSQL - не используемые индексы
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- MySQL
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA != 'mysql'
AND COUNT_STAR = 0
AND OBJECT_NAME != 'PRIMARY'
ORDER BY COUNT_READ DESC;

10. Переиндексация

-- PostgreSQL - пересоздание индекса (оптимизация)
REINDEX INDEX idx_user_email;
REINDEX TABLE users;

-- MySQL - оптимизация таблицы
OPTIMIZE TABLE users;

-- SQL Server
ALTER INDEX ALL ON users REORGANIZE;
ALTER INDEX ALL ON users REBUILD;

11. Когда избегать индексирования

-- ❌ Не индексируй булевы поля (низкая селективность)
CREATE INDEX idx_is_active ON users(is_active);

-- ❌ Не индексируй очень большие текстовые поля
CREATE INDEX idx_description ON products(description);  -- VARCHAR(5000)

-- ❌ Не индексируй редко используемые колонки
CREATE INDEX idx_backup_field ON users(backup_field);

-- ✅ Индексируй только критичные колонки
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);

Checklist при индексировании

• Селективность — индекс на колонке с уникальными/редкими значениями
• Использование — колонка используется в WHERE, JOIN, ORDER BY
• Размер — индекс не слишком большой
• Производительность — индекс ускоряет чтение, но замедляет запись
• Мониторинг — проверяй статистику idx_scan
• Порядок — для составных индексов важен порядок колонок
• Частичные индексы — используй WHERE для экономии места
• Не over-index — не создавай индексы "на будущее"

Выводы

1. Индексируй по селективности — высокая уникальность = хороший индекс
2. Порядок в составных индексах — Equality → Sort → Range (ESR)
3. Мониторь использование — удаляй неиспользуемые индексы
4. Баланс — индексы ускоряют чтение, замедляют запись
5. Профилируй реальные запросы — индексируй то, что медленно