Что такое индексы, зачем они нужны и как работают. Типы операций и базовые принципы.
Индексы — это фундамент производительности баз данных. Понимание того, когда и зачем они нужны, отличает профессионала от новичка.
Индекс — это дополнительная структура данных, которая ускоряет поиск строк в таблице. Представьте книгу без оглавления: чтобы найти нужную главу, придётся листать все страницы подряд. Индекс — это и есть «оглавление» вашей базы данных.
Индексы не бесплатны:
| Преимущество | Недостаток |
|---|---|
| Ускорение SELECT с WHERE, ORDER BY, JOIN | Замедление INSERT, UPDATE, DELETE |
| Быстрый поиск по ключу | Дополнительное место на диске |
| Поддержка уникальности | Накладные расходы на обслуживание |
Правило: Создавайте индексы осознанно. Каждый индекс — это компромисс между скоростью чтения и записи.
Рассмотрим таблицу пользователей:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255),
name VARCHAR(100),
created_at TIMESTAMP
);
INSERT INTO users (email, name, created_at)
SELECT
'user' || i || '@example.com',
'Name ' || i,
NOW() - (i || ' days')::INTERVAL
FROM generate_series(1, 1000000) AS i;EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user500000@example.com';Результат:
Seq Scan on users (cost=0.00..23334.00 rows=1 width=...)
Filter: (email = 'user500000@example.com'::text)
Rows Removed by Filter: 999999
Execution Time: 45.2 ms
Seq Scan (Sequential Scan) означает, что PostgreSQL читает все 1 000 000 строк, чтобы найти одну нужную. Это называется полное сканирование таблицы.
CREATE INDEX idx_users_email ON users(email);EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user500000@example.com';Результат:
Index Scan using idx_users_email on users (cost=0.43..8.45 rows=1 width=...)
Index Cond: (email = 'user500000@example.com'::text)
Execution Time: 0.08 ms
Ускорение: в 560 раз! (45.2 мс → 0.08 мс)
SELECT * FROM users WHERE email = 'test@example.com';SELECT * FROM users WHERE created_at > '2026-01-01';
SELECT * FROM users WHERE id BETWEEN 1000 AND 2000;SELECT * FROM users ORDER BY created_at DESC LIMIT 10;Индекс по created_at позволяет избежать сортировки — данные уже упорядочены.
SELECT u.*, o.*
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.email = 'test@example.com';Индексы по users.email и orders.user_id критически важны для производительности JOIN.
SELECT DATE(created_at), COUNT(*)
FROM users
GROUP BY DATE(created_at);PostgreSQL использует три основных типа сканирования:
Полное сканирование таблицы. Чтение всех строк последовательно.
Когда используется:
Seq Scan on users
Filter: (status = 'active')
Rows Removed by Filter: 750000
Чтение индекса + чтение строк из таблицы по указателям (TID).
Когда используется:
Index Scan using idx_users_email on users
Index Cond: (email = 'test@example.com')
Построение битовой карты страниц, затем чтение страниц.
Когда используется:
Bitmap Heap Scan on users
Recheck Cond: (status = 'active')
-> Bitmap Index Scan on idx_users_status
Index Cond: (status = 'active')
-- ❌ Индекс не сработает
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- ✅ Создайте индекс по выражению
CREATE INDEX idx_lower_email ON users(LOWER(email));-- ❌ Индекс может не сработать
SELECT * FROM users WHERE phone = 89001234567; -- phone — VARCHAR
-- ✅ Явное приведение
SELECT * FROM users WHERE phone = '89001234567';-- ❌ Индекс не сработает
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- ✅ Сработает с префиксом
SELECT * FROM users WHERE email LIKE 'test@%';
-- ✅ Используйте GIN для поиска по подстроке
CREATE INDEX idx_email_gin ON users USING GIN (email gin_trgm_ops);-- Индекс не поможет, если 50% строк подходят
SELECT * FROM users WHERE gender = 'M'; -- 50% пользователейОптимизатор выберет Seq Scan, потому что стоимость случайных чтений по индексу выше стоимости последовательного чтения всей таблицы.
CREATE INDEX idx_name ON users(last_name, first_name);
-- ✅ Сработает
SELECT * FROM users WHERE last_name = 'Ivanov';
-- ❌ Не сработает (или сработает плохо)
SELECT * FROM users WHERE first_name = 'Ivan';Селективность — это доля строк, удовлетворяющих условию запроса.
Селективность = (подходящие строки) / (всего строк)
| Селективность | Пример | Использовать индекс? |
|---|---|---|
| 0.0001% | WHERE id = 1 | ✅ Да, обязательно |
| 1% | WHERE email = '...' | ✅ Да |
| 10% | WHERE status = 'pending' | ⚠️ Зависит от ситуации |
| 50% | WHERE gender = 'M' | ❌ Нет, лучше Seq Scan |
Эмпирическое правило: Индекс эффективен, когда выбирается менее 5-10% строк таблицы.
-- Все индексы таблицы
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';
-- Размер индексов
SELECT
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'users';
-- Статистика использования индексов
SELECT
indexrelname,
idx_scan AS index_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE relname = 'users';EXPLAIN ANALYZE для проверки планов выполненияorders с полями: id, user_id, status, total, created_atEXPLAIN ANALYZEuser_idstatuscreated_atВы изучили основы. Далее:
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.