Для чего использовать индексы?

Для чего использовать индексы в БД

Индексы — это основной инструмент оптимизации производительности базы данных. Объясню на практических примерах из моего опыта.

Определение

Индекс — это структура данных (обычно B-tree), которая позволяет быстро найти строки в таблице без полного сканирования всех записей.

Без индекса: O(n) — нужно проверить все строки С индексом: O(log n) — бинарный поиск

Основная причина: скорость поиска

Представь таблицу с 1 миллионом пользователей:

-- ❌ БЕЗ индекса: сканирует 1,000,000 строк
SELECT * FROM users WHERE email = 'user@example.com';  -- 500ms

-- ✓ С индексом: сканирует ~20 строк (log2(1M))
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'user@example.com';  -- 1ms

Разница в 500 раз без каких-либо изменений в коде!

Типы индексов и их применение

1. Primary Key индекс (идентификатор)

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,  -- Автоматический индекс
    email VARCHAR(255) UNIQUE,
    name VARCHAR(255)
);

Используется для:

• Быстрый поиск по ID
• Гарантия уникальности
• JOIN операции

# Django: быстро находит пользователя
user = User.objects.get(id=123)  # Индекс используется

2. UNIQUE индекс (уникальность)

CREATE UNIQUE INDEX idx_users_email ON users(email);
-- Гарантирует что email уникален
-- И позволяет быстро найти пользователя по email

3. B-tree индекс (поиск и сортировка)

CREATE INDEX idx_posts_created ON posts(created_at);

-- ✓ Использует индекс (быстро)
SELECT * FROM posts WHERE created_at > '2024-01-01' ORDER BY created_at;

-- ✓ Использует индекс (быстро)
SELECT * FROM posts WHERE title LIKE 'Python%';

-- ❌ НЕ использует индекс
SELECT * FROM posts WHERE title LIKE '%Python';

4. Composite индекс (несколько колонок)

Использую когда часто ищу по двум колонкам:

CREATE INDEX idx_orders_user_status 
    ON orders(user_id, status);

-- ✓ Использует индекс
SELECT * FROM orders WHERE user_id = 5 AND status = 'completed';

-- ⚠ Использует индекс, но менее эффективно
SELECT * FROM orders WHERE status = 'completed';

Важно: порядок колонок в индексе имеет значение!

5. PARTIAL индекс (условный)

-- Индексирую только активные заказы (экономя место)
CREATE INDEX idx_orders_active 
    ON orders(created_at) 
    WHERE status = 'pending';

-- Использует индекс
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;

Когда ОБЯЗАТЕЛЬНО нужны индексы

1. WHERE условия

# Django ORM
User.objects.filter(email='user@example.com')  # Нужен индекс на email
User.objects.filter(is_active=True, created_at__gte=date)  # Индекс на (is_active, created_at)

2. JOIN операции

-- Foreign keys должны быть индексированы
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),  -- Автоматический индекс
    created_at TIMESTAMP
);

-- ✓ Быстро благодаря индексам
SELECT u.*, o.* 
FROM users u 
JOIN orders o ON u.id = o.user_id
WHERE u.email = 'user@example.com';

3. ORDER BY и GROUP BY

CREATE INDEX idx_posts_status_date 
    ON posts(status, created_at DESC);

-- ✓ Использует индекс для сортировки
SELECT * FROM posts WHERE status = 'published' ORDER BY created_at DESC;

Практический пример из моего проекта

Проект с 5 млн заказов. Главная страница отображает последние заказы пользователя:

# ❌ ДО оптимизации
DECLARE @user_id INT = 123;

SELECT * FROM orders WHERE user_id = @user_id 
ORDER BY created_at DESC LIMIT 20;
-- Время выполнения: 2500ms (сканирует все заказы)

# ✓ ПОСЛЕ индекса
CREATE INDEX idx_orders_user_created 
    ON orders(user_id, created_at DESC);
-- Время выполнения: 5ms (сканирует 20 строк)

Результат: ускорение в 500 раз без изменения кода!

Стоимость индексов (важно знать!)

Индексы не бесплатны:

# Таблица 1M строк, индекс займёт ~50MB
# Стоит ли? Если выполняется 1000 SELECT в день — ДА!

Как выбрать что индексировать

ИНДЕКСИРУЙ:

• ✓ Колонки в WHERE (часто)
• ✓ Foreign keys для JOIN
• ✓ Колонки в ORDER BY (если часто)
• ✓ Колонки в GROUP BY (если много групп)
• ✓ Колонки в DISTINCT (если много значений)

НЕ ИНДЕКСИРУЙ:

• ❌ Булевы колонки (мало уникальных значений)
• ❌ Колонки с низкой селективностью
• ❌ Редко используемые колонки
• ❌ TEXT колонки большого объема

SQL для анализа индексов

-- Какие индексы есть в таблице
SHOW INDEXES FROM users;

-- Статистика индексов (PostgreSQL)
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Неиспользуемые индексы (PostgreSQL)
SELECT indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

Django ORM + индексы

class User(models.Model):
    email = models.EmailField(db_index=True)  # Индекс
    created_at = models.DateTimeField(auto_now_add=True, db_index=True)
    is_active = models.BooleanField(default=True)
    
    class Meta:
        indexes = [
            models.Index(fields=['email']),  # B-tree
            models.Index(fields=['is_active', 'created_at']),  # Composite
            models.Index(
                fields=['created_at', '-id'],
                name='idx_user_recent'
            ),
        ]

# Миграция содержит CREATE INDEX

Главное правило

Индексы — это инвестиция:

• Дороговизна: память + время на INSERT/UPDATE
• Результат: почти мгновенный SELECT

Для выбора индексов нужно:

1. Профилировать запросы (EXPLAIN ANALYZE)
2. Видеть какие колонки используются в WHERE
3. Измерять прирост производительности
4. Не переиндексировать всё подряд