Нужно ли писать индекс на таблицу которую часто изменяют?

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

Да, индексы необходимо писать даже на таблицы, которые часто изменяются. Но это требует понимания компромиссов между скоростью чтения и скоростью записи.

Основной принцип

Индексы ускоряют чтение (SELECT), но замедляют запись (INSERT, UPDATE, DELETE), так как база данных должна обновить сами индексы.

Когда писать индексы на часто изменяемые таблицы

Нужны индексы, если:

• Таблица часто читается (SELECT запросы)
• Объём таблицы велик (тысячи и миллионы строк)
• Запросы на обновление затрагивают мало строк
• Скорость чтения критична для бизнеса

Не нужны индексы, если:

• Таблица почти не читается
• Таблица мала (десятки или сотни строк)
• Все строки обновляются целиком в каждой операции
• Приложение CPU-bound, а не I/O-bound

Пример компромисса

Представим таблицу user_activity с частыми обновлениями:

CREATE TABLE user_activity (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    last_action_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    action_count INT DEFAULT 0,
    status VARCHAR(50)
);

-- Индекс на часто используемый фильтр
CREATE INDEX idx_user_activity_user_id ON user_activity(user_id);

-- Индекс на часто используемый диапазон
CREATE INDEX idx_user_activity_last_action 
    ON user_activity(last_action_at DESC);

Плюсы:

• SELECT по user_id работает быстро (О(log N) вместо О(N))
• Поиск свежих действий быстро

Минусы:

• UPDATE statement меньше быстродействия (нужно обновить индексы)
• INSERT медленнее на ~10-30%
• Требует больше памяти (хранение индексов)

Стратегии для часто изменяемых таблиц

1. Индексы на столбцы, используемые в WHERE

-- Если часто фильтруешь по status
SELECT * FROM user_activity WHERE status = "active" AND user_id = 123;

-- Составной индекс эффективнее
CREATE INDEX idx_activity_status_user 
    ON user_activity(status, user_id);

2. Избегай индексов на столбцы, которые часто обновляются

-- Плохо: часто обновляемый столбец в индексе
CREATE INDEX idx_bad ON logs(updated_at);
UPDATE logs SET updated_at = NOW() WHERE id = 1;  -- Медленно

-- Хорошо: индекс только на immutable столбцы
CREATE INDEX idx_good ON logs(created_at);

3. Используй PARTIAL индексы (если поддерживается БД)

-- Индекс только на активные записи
CREATE INDEX idx_active_users 
    ON user_activity(user_id) 
    WHERE status = "active";

-- Это быстрее и компактнее, чем полный индекс

4. Batch операции эффективнее отдельных UPDATE

# Плохо: 1000 отдельных UPDATE запросов
for user_id in user_ids:
    db.execute(
        "UPDATE user_activity SET action_count = action_count + 1 WHERE user_id = %s",
        (user_id,)
    )

# Хорошо: один UPDATE с IN
db.execute(
    f"UPDATE user_activity SET action_count = action_count + 1 WHERE user_id IN ({,.join(map(str, user_ids))})"
)

# Или с JOIN если нужна логика
db.execute("""
    UPDATE user_activity ua
    SET action_count = action_count + 1
    FROM (VALUES %s) AS updates(user_id)
    WHERE ua.user_id = updates.user_id
""", (tuple(user_ids),))

Анализ компромисса на конкретном примере

Сценарий: Таблица events с 100М записей

• SELECT запросов: 10,000/сек
• INSERT запросов: 100/сек

CREATE TABLE events (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    event_type VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data JSONB
);

-- Index 1: На user_id
CREATE INDEX idx_events_user ON events(user_id);
-- Плюс: SELECT по user_id ~100x быстрее
-- Минус: INSERT медленнее на ~5%
-- Вывод: НУЖЕН (10k SELECT за 100 INSERT)

-- Index 2: На event_type
CREATE INDEX idx_events_type ON events(event_type);
-- Плюс: SELECT по типу быстрее
-- Минус: INSERT медленнее на ~5%
-- Вывод: НУЖЕН (много SELECT по типам)

-- Index 3: На data JSONB
CREATE INDEX idx_events_data ON events USING GIN (data);
-- Плюс: JSON поиск работает
-- Минус: INSERT медленнее на ~20%, больше памяти
-- Вывод: Нужен ТОЛЬКО если часто ищут в data

Инструменты для анализа

PostgreSQL: EXPLAIN для анализа планов

-- Без индекса
EXPLAIN ANALYZE SELECT * FROM user_activity WHERE user_id = 123;
-- Seq Scan on user_activity (cost=0.00..35000.00 rows=100000)

-- С индексом
EXPLAIN ANALYZE SELECT * FROM user_activity WHERE user_id = 123;
-- Index Scan using idx_user_activity_user_id (cost=0.29..100.29 rows=100000)

Python: Мониторинг времени выполнения

import time
from sqlalchemy import text

def benchmark_query(engine, query, name):
    start = time.time()
    with engine.connect() as conn:
        conn.execute(text(query))
    elapsed = time.time() - start
    print(f"{name}: {elapsed*1000:.2f}ms")

# Сравнение
benchmark_query(engine, 
    "SELECT * FROM users WHERE status = active", 
    "Without index")
benchmark_query(engine, 
    "SELECT * FROM users WHERE status = active", 
    "With index on status")

Итоговые рекомендации

1. Пиши индексы по умолчанию на столбцы, используемые в WHERE и JOIN
2. Не пиши индексы на:
   • Неселективные столбцы (булевы, enum с 2-3 значениями)
   • Редко используемые столбцы
   • Часто обновляемые столбцы
3. Используй EXPLAIN для анализа реальной производительности
4. Батчируй INSERT/UPDATE операции
5. PARTIAL индексы экономят память и ускоряют операции
6. Мониторь размер индексов — они занимают память
7. Удаляй неиспользуемые индексы (они только замедляют запись)