Комментарии (1)
🐱
claude-haiku-4.5PrepBro AI22 мар. 2026 г.(ред.)
Ответ сгенерирован нейросетью и может содержать ошибки
# Минусы индексов в базах данных
Индексы значительно ускоряют чтение, но имеют существенные недостатки. Разберемся, почему индексы — это компромисс между скоростью и ресурсами.
1. Увеличение размера базы данных
Индекс требует дополнительное место на диске для хранения.
-- Таблица users: 10 000 000 строк
SELECT pg_size_pretty(pg_total_relation_size('users'));
-- 2 GB — сама таблица
-- Каждый индекс занимает свое место
CREATE INDEX idx_email ON users(email); -- +400 MB
CREATE INDEX idx_name ON users(name); -- +400 MB
CREATE INDEX idx_created_at ON users(created_at); -- +400 MB
-- Итого: 2 GB + 1.2 GB = 3.2 GB (вместо 2 GB!)
-- Составной индекс еще больше
CREATE INDEX idx_user_email_name ON users(email, name); -- +500 MB
SELECT sum(pg_relation_size(indexrelname::regclass)) as size
FROM pg_indexes
WHERE tablename = 'users';
-- Все индексы: 1.5 GB!
2. Замедление операций записи (INSERT, UPDATE, DELETE)
При вставке данных база должна обновлять ВСЕ индексы одновременно.
# Без индексов: INSERT быстрый
from time import time
start = time()
for i in range(1_000_000):
user = User.objects.create(email=f"user{i}@example.com") # ~0.3 сек
print(f"Without indexes: {time() - start:.2f}s") # 0.3 сек
# С 5 индексами: INSERT медленный
from django.db import connection
cursor = connection.cursor()
# Добавим индексы
cursor.execute("CREATE INDEX idx_email ON users(email)")
cursor.execute("CREATE INDEX idx_name ON users(name)")
cursor.execute("CREATE INDEX idx_created_at ON users(created_at)")
cursor.execute("CREATE INDEX idx_status ON users(status)")
cursor.execute("CREATE INDEX idx_phone ON users(phone)")
start = time()
for i in range(1_000_000):
user = User.objects.create(email=f"user{i}@example.com") # ~2 сек
print(f"With 5 indexes: {time() - start:.2f}s") # 2+ сек (7x медленнее!)
Математика:
- INSERT в таблицу: O(log n) — одна операция
- INSERT + обновить 5 индексов: O(log n) × 5 = 5x медленнее
3. Более высокая нагрузка на CPU
-- UPDATE каждой строки требует пересчета индексов
UPDATE users SET email = "new_email@example.com" WHERE id = 1;
-- БД должна:
-- 1. Найти строку в таблице
-- 2. Удалить старое значение из idx_email
-- 3. Добавить новое значение в idx_email
-- 4. Обновить другие индексы (если есть)
-- 5. Обновить B-Tree структуры
-- Bulk update 100 000 строк с 5 индексами
-- CPU использование: 85% × 5 индексов = высокая нагрузка
UPDATE users SET status = "active" WHERE created_at > "2024-01-01";
-- Может занять часы вместо минут
4. Проблемы с синхронизацией
# Проблема — индекс отстает от таблицы
from django.db import connection
# Вставляем 1 млн строк
User.objects.bulk_create([User(...) for _ in range(1_000_000)])
# Индекс может быть не обновлен сразу
user = User.objects.filter(email="test@example.com").first() # Может вернуть NULL!
# Решение — явно обновить индекс
from django.db import connection
cursor = connection.cursor()
cursor.execute("REINDEX TABLE users;")
user = User.objects.filter(email="test@example.com").first() # Теперь ОК
5. Unused индексы (мертвые индексы)
-- Индекс создан, но никто его не использует
CREATE INDEX idx_rarely_used ON users(phone);
-- Никто не ищет по phone
-- Каждый INSERT/UPDATE/DELETE все равно обновляет индекс
-- Но выигрыша нет!
-- Найти неиспользуемые индексы
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0 -- Индекс никогда не сканировался
ORDER BY pg_relation_size(indexrelid) DESC;
-- Удалить ненужные индексы
DROP INDEX idx_rarely_used; # Экономим 400 MB
6. Query Planner может выбрать неоптимальный индекс
-- У таблицы есть индексы по email и status
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_status ON users(status);
-- Запрос с обоими условиями
SELECT * FROM users WHERE email = "test@example.com" AND status = "active";
-- Query planner может выбрать:
-- Вариант 1: Использовать idx_email (50 000 строк с этим email)
-- Затем фильтровать по status (50 строк)
-- Вариант 2: Использовать idx_status (30 000 активных)
-- Затем фильтровать по email (1 строка)
-- Если planner выбрал вариант 1 (неправильно)
-- Решение — составной индекс
CREATE INDEX idx_email_status ON users(email, status);
-- Теперь planner будет умнее
7. Проблемы с большими индексами
-- Индекс больше, чем кэш процессора
CREATE INDEX idx_huge ON logs(user_id, timestamp, data);
-- Индекс: 50 GB (не помещается в RAM)
-- Каждый поиск становится медленным
-- Cache miss → обращение к диску → 100ms
SELECT * FROM logs WHERE user_id = 1;
-- Вместо 1ms (в памяти), теперь 100ms (с диска)
-- Решение — индекс только на необходимые поля
CREATE INDEX idx_user ON logs(user_id); -- Меньше, быстрее
8. Фрагментация индекса
-- После множества INSERT/DELETE/UPDATE индекс становится фрагментированным
SELECT * FROM users WHERE email = "test@example.com"; -- 5ms (норма)
-- Спустя время индекс распределен по разным блокам диска
SELECT * FROM users WHERE email = "test@example.com"; -- 50ms (медленнее!)
-- Решение — REINDEX
REINDEX INDEX idx_email; -- Пересчитать индекс
-- Теперь снова быстро
SELECT * FROM users WHERE email = "test@example.com"; -- 5ms
9. Проблемы с полнотекстовым поиском
-- Полнотекстовый индекс может быть огромным
CREATE INDEX idx_fulltext ON articles USING gin(to_tsvector('english', content));
-- Индекс: 5 GB для 100 000 статей
-- И обновляется после каждого INSERT
INSERT INTO articles (title, content) VALUES (...);
-- Еще нужно пересчитать полнотекстовый индекс
10. Сложность выбора индексов
# Где создать индексы?
class User(models.Model):
email = models.EmailField() # Индекс?
username = models.CharField() # Индекс?
phone = models.CharField() # Индекс?
address = models.CharField() # Индекс?
bio = models.TextField() # Индекс?
created_at = models.DateTimeField() # Индекс?
updated_at = models.DateTimeField() # Индекс?
status = models.CharField() # Индекс?
# Если создать индексы на ВСЕ:
# - Таблица 100 MB → становится 500 MB
# - INSERT 100x медленнее
# - Большинство индексов не используются
# Правильное решение:
# - Профилировать реальные запросы
# - Индексировать только поля, по которым часто ищут
# - Использовать EXPLAIN для анализа
Когда НЕ использовать индексы
-- 1. Для маленьких таблиц (< 1000 строк)
CREATE TABLE status (id INT, name VARCHAR(10));
-- Индекс замедлит, не поможет
-- 2. Для столбцов с низкой selectivity
CREATE TABLE users (gender CHAR(1)); -- Только 'M' и 'F'
CREATE INDEX idx_gender ON users(gender);
-- Пол принимает только 2 значения
-- Индекс не поможет, замедлит
-- 3. Для часто обновляемых столбцов
CREATE INDEX idx_view_count ON posts(view_count);
-- view_count обновляется после каждого просмотра
-- Индекс постоянно перестраивается
-- 4. Для JOIN на вычисляемые поля
CREATE INDEX idx_upper_name ON users(UPPER(name));
-- Индекс на функцию медленнее
Мониторинг индексов
import psycopg2
conn = psycopg2.connect("postgresql://localhost/mydb")
cursor = conn.cursor()
# Найти медленные запросы из-за плохих индексов
cursor.execute("""
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
WHERE mean_time > 100 -- Медленнее 100ms
ORDER BY mean_time DESC
LIMIT 10;
""")
for query, calls, total, mean in cursor.fetchall():
print(f"Slow: {query[:50]} - {mean:.2f}ms avg")
# Индексы, которые редко используются
cursor.execute("""
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan < 100 -- Менее 100 сканирований
ORDER BY pg_relation_size(indexrelid) DESC;
""")
for name, scans, size in cursor.fetchall():
print(f"Unused: {name} ({size}) - {scans} scans")
Решение: индексы по требованию
# Создавай индексы ТОЛЬКО для:
# 1. Primary Key (автоматически)
# 2. Foreign Keys (для JOIN)
# 3. WHERE условия в реальных запросах
# 4. ORDER BY/GROUP BY с большими наборами
# Не создавай индексы на:
# 1. Редко используемые столбцы
# 2. Столбцы с низкой selectivity (Boolean, Gender)
# 3. BLOB поля (huge)
# 4. Часто обновляемые поля
Заключение
Индексы — это компромисс:
Плюсы:
- Ускорение чтения (SELECT) в 100+ раз
Минусы:
- Замедление записи (INSERT/UPDATE/DELETE) в 5-10 раз
- Увеличение размера БД на 30-50%
- Использование CPU на синхронизацию
- Возможность выбрать неправильный индекс
- Фрагментация и мертвые индексы
Правило: Создавай индексы только когда профилировщик показывает медленные запросы. Профилактика — враг оптимизации.