Что такое индексы в базе данных? Какие типы индексов вы знаете?
Комментарии (1)
Ответ сгенерирован нейросетью и может содержать ошибки
Индексы в базах данных: полное руководство System Analyst
Индексы — это одна из самых важных концепций для System Analyst, которая напрямую влияет на производительность системы. Правильное использование индексов может ускорить запросы в 100+ раз, а неправильное может наоборот замедлить систему. Давайте разберемся глубоко.
Что такое индекс?
Определение
Индекс — это структура данных, которая хранит отсортированную копию выбранных колонок таблицы и указатели на полные строки таблицы. Это позволяет БД быстро найти данные без полного просмотра таблицы.
Аналогия
Таблица без индекса = книга без оглавления
╰→ Нужно прочитать все страницы, чтобы найти информацию
Таблица с индексом = книга с оглавлением и предметным указателем
╰→ Можно быстро найти нужную информацию по указателю
Как работает индекс внутри
Таблица users:
id name email
───────────────────────────
1 Alice alice@example.com
2 Bob bob@example.com
3 Charlie charlie@example.com
...
1000000 Zoe zoe@example.com
Без индекса: поиск Zoe → проверяем все 1M строк
Индекс на (name):
name → id
─────────────────
Alice → 1
Bob → 2
Charlie → 3
...
Zoe → 1000000
С индексом: поиск Zoe → заходим в индекс (логарифм от 1M ≈ 20 операций)
Типы индексов
1. B-Tree Index (самый распространённый)
Описание: Сбалансированное дерево, которое поддерживает быстрый поиск, вставку и удаление.
Структура:
[M-Z]
/ \
[A-L] [N-Z]
/ | | \ / | | \
A B C L N O P Z
Когда использовать:
- Точные поиски (WHERE user_id = 123)
- Диапазонные поиски (WHERE age > 18 AND age < 65)
- Сортировка (ORDER BY created_at)
- Практически всегда (по умолчанию)
Пример:
CREATE INDEX idx_user_email ON users(email);
-- Быстро
SELECT * FROM users WHERE email = 'alice@example.com';
Производительность:
- 1K rows: 10ms (полный скан) vs 1ms (индекс)
- 1M rows: 5000ms (полный скан) vs 2ms (индекс)
2. Hash Index
Описание: Использует хеш-функцию для быстрого поиска. Быстрее B-Tree для точных совпадений, но не поддерживает диапазоны.
Когда использовать:
- Только точные совпадения (WHERE id = 123)
- Очень часто читаемые ключи
- В памяти (Redis, Memcached)
Пример:
-- MySQL
CREATE INDEX idx_user_id USING HASH ON users(id);
-- БЫСТРО: точное совпадение
SELECT * FROM users WHERE id = 123;
-- МЕДЛЕННО: диапазон
SELECT * FROM users WHERE id > 100 AND id < 200; -- Hash index не помогает
Недостаток: PostgreSQL и большинство БД не поддерживают Hash индексы.
3. Composite Index (Составной индекс)
Описание: Индекс на нескольких колонках одновременно.
Когда использовать:
- Фильтры на нескольких колонках
- LEFT-prefix правило должно быть соблюдено
Пример:
-- Индекс на (user_id, status, created_at)
CREATE INDEX idx_orders_list ON orders(user_id, status, created_at DESC);
-- БЫСТРО: используется составной индекс
SELECT * FROM orders
WHERE user_id = 123 AND status = 'completed'
ORDER BY created_at DESC;
-- БЫСТРО: первый prefix работает
SELECT * FROM orders WHERE user_id = 123;
-- МЕДЛЕННО: skipped первый столбец
SELECT * FROM orders WHERE status = 'completed'; -- Не использует индекс
-- МЕДЛЕННО: только второй столбец
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '1 day';
4. Unique Index
Описание: Гарантирует уникальность значений в колонке(ах).
Пример:
-- Каждый email может быть только один раз
CREATE UNIQUE INDEX idx_user_email ON users(email);
-- Попытка вставить duplicate email → ERROR
INSERT INTO users (name, email) VALUES ('John', 'alice@example.com'); -- ERROR
5. Full-Text Index
Описание: Оптимизирован для поиска текста. Игнорирует маленькие слова (stopwords), поддерживает близость слов.
Когда использовать:
- Поиск по статьям, документам
- Полнотекстовый поиск
Пример:
-- MySQL
CREATE FULLTEXT INDEX idx_article_text ON articles(title, content);
-- Поиск
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('database' IN BOOLEAN MODE);
-- Альтернатива: использовать Elasticsearch для больших объёмов
6. Partial Index (Conditional Index)
Описание: Индекс только на подмножество строк.
Когда использовать:
- Если часто фильтруешь по условию
- Экономит место, если большой % rows не нужны
Пример:
-- Индекс только на активных пользователей
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';
-- БЫСТРО: условие совпадает
SELECT * FROM users WHERE status = 'active' AND email = 'alice@example.com';
-- МЕДЛЕННО: условие не совпадает
SELECT * FROM users WHERE status = 'deleted' AND email = 'alice@example.com';
7. GiST (Generalized Search Tree)
Описание: Универсальная структура для сложных типов данных.
Когда использовать:
- Географические данные (PostGIS)
- Диапазоны (range queries)
- Полнотекстовый поиск (PostgreSQL)
Пример:
-- PostgreSQL
CREATE INDEX idx_location ON users USING GIST(location);
-- Найти пользователей в радиусе 10км
SELECT * FROM users
WHERE location <-> point(55.7558, 37.6173) < 10;
8. BRIN (Block Range INdex)
Описание: Индекс для очень больших таблиц с естественным порядком (временные ряды).
Когда использовать:
- Таблицы > 100GB
- Данные отсортированы (логи, метрики)
- Экономит место: 1000x меньше чем B-Tree
Пример:
-- PostgreSQL
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);
-- Логи за 2024-03-25
SELECT * FROM logs WHERE created_at::date = '2024-03-25';
Практические рекомендации для System Analyst
Когда создавать индекс
Создавай индекс если:
1. Колонка часто в WHERE clause
2. Колонка в JOIN ON
3. Колонка в ORDER BY
4. Нужна UNIQUE гарантия
НЕ создавай индекс если:
1. Таблица маленькая (< 1000 rows)
2. Колонка редко используется
3. Много значений NULL
4. Колонка часто обновляется
Стратегия индексирования
Шаг 1: Анализируем query patterns
POST /api/v1/orders?user_id=123&status=completed&sort=-created_at
POST /api/v1/orders?status=pending (для администратора)
GET /api/v1/users?email=alice@example.com
Шаг 2: Выбираем индексы
Query 1: WHERE user_id = ? AND status = ?
ORDER BY created_at DESC
→ INDEX (user_id, status, created_at DESC)
Query 2: WHERE status = ?
→ INDEX (status, created_at DESC)
Query 3: WHERE email = ?
→ UNIQUE INDEX (email)
Шаг 3: Проверяем EXPLAIN
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123 AND status = 'completed'
ORDER BY created_at DESC;
-- Результат: Index Scan using idx_orders_list (GOOD!)
-- vs Seq Scan (BAD!)
Проблемы с индексами
Проблема 1: Неиспользуемые индексы
-- Найти неиспользуемые индексы
SELECT * FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY idx_blks_read DESC;
-- Удалить
DROP INDEX idx_unused;
Проблема 2: Слишком много индексов
Каждый индекс:
- Занимает место на диске (~10-30% от размера таблицы)
- Замедляет INSERT/UPDATE/DELETE (нужно обновить индекс)
- Требует обслуживание (VACUUM, ANALYZE)
Рекомендация: 3-5 индексов на таблицу
Максимум: не более 10
Проблема 3: Неправильный порядок в составном индексе
-- ПЛОХО
CREATE INDEX idx_orders ON orders(status, user_id, created_at DESC);
-- Запрос
SELECT * FROM orders WHERE user_id = 123;
-- Не использует индекс (status в начале, но не в WHERE)
-- ХОРОШО
CREATE INDEX idx_orders ON orders(user_id, status, created_at DESC);
-- Используется
Матрица индексов
Тип индекса │ Скорость │ Вставка │ Место │ Диапазон │ Юзкейс
────────────────┼───────────────┼──────────┼────────┼──────────┼──────────────
B-Tree │ Отличная │ Хорошо │ Норм │ Да │ 90% случаев
Hash │ Быстрее │ Хорошо │ Норм │ Нет │ Key-value
Unique │ Отличная │ Хорошо │ Норм │ Нет │ PK, constraints
Full-Text │ Хорошо │ Медленнее │ Много │ Частично │ Поиск текста
Partial │ Отличная │ Хорошо │ Меньше │ Да │ Часто фильтры
BRIN │ Хорошо │ Хорошо │ Мало │ Да │ Большие таблицы
GiST │ Хорошо │ Хорошо │ Много │ Да │ Специальные
Best Practices
1. Правило LEFT-PREFIX
INDEX (a, b, c) может использоваться для:
✅ WHERE a = ?
✅ WHERE a = ? AND b = ?
✅ WHERE a = ? AND b = ? AND c = ?
❌ WHERE b = ? (skipped first)
❌ WHERE c = ? (skipped first two)
2. Порядок в составном индексе
Ордер факторов имеет значение:
CREATE INDEX idx_list ON orders(user_id, status, created_at DESC);
Лучший порядок:
1. Equality columns (user_id = ?)
2. Range columns (created_at > ?)
3. Sorting columns (ORDER BY ...)
3. Мониторинг индексов
-- PostgreSQL: когда последний раз использовался индекс?
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
4. Стоимость индексирования
Сценарий: Таблица 10M rows, 100GB
1 индекс B-Tree: ~10-15GB (новой памяти + I/O)
Многих индексов: замедление INSERT/UPDATE на 20-50%
BRIN индекс: ~5MB (!)
цена: немного медленнее для некоторых запросов
Real-world пример
Эквайринговая система:
-- Таблица транзакций: 100M rows
CREATE TABLE transactions (
id UUID PRIMARY KEY,
merchant_id UUID,
user_id UUID,
amount DECIMAL(10, 2),
status VARCHAR(20),
created_at TIMESTAMPTZ,
-- Индекс для балансирования мерчанта
INDEX idx_merchant_balance (merchant_id, status, created_at DESC),
-- Индекс для поиска по пользователю
INDEX idx_user_created (user_id, created_at DESC),
-- BRIN для временных рядов
INDEX idx_created_brin USING BRIN(created_at),
-- Unique для отслеживания
UNIQUE INDEX idx_external_id (merchant_id, external_transaction_id)
);
Результат:
- Получить баланс мерчанта: 10ms (было 5s)
- История транзакций пользователя: 15ms (было 8s)
- Отчеты по дате: 100ms (было 2min)
Заключение
Индексы — ключевой инструмент для System Analyst:
- B-Tree индексы — используй по умолчанию
- Составные индексы — план query patterns перед созданием
- Partial индексы — экономь место когда часто фильтруешь
- Мониторинг — проверяй unused индексы регулярно
- EXPLAIN ANALYZE — всегда проверяй перед production
Правильное индексирование может улучшить производительность на 100x+, но неправильное может замедлить систему. Это искусство, которое требует понимания как data, так и query patterns.