Как создать индекс?

Создание индексов в БД

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

Основы индексов

Без индекса БД проверяет КАЖДУЮ строку (full table scan):

100,000 строк × 0.1ms = 10,000ms (10 сек)

С индексом БД находит за логарифмическое время (B-tree):

log2(100,000) = 17 сравнений × 0.1ms = 1.7ms

SQL: создание индексов

-- Простой индекс на одном поле
CREATE INDEX idx_user_email ON users(email);

-- Уникальный индекс (гарантирует уникальность)
CREATE UNIQUE INDEX idx_user_email_unique ON users(email);

-- Составной индекс (на нескольких полях)
CREATE INDEX idx_order_user_date ON orders(user_id, created_at);

-- Частичный индекс (только для активных)
CREATE INDEX idx_active_users ON users(id) WHERE is_active = true;

-- Индекс с сортировкой DESC
CREATE INDEX idx_posts_date_desc ON posts(created_at DESC);

-- Удаление индекса
DROP INDEX idx_user_email;

-- Переименование
ALTER INDEX idx_old_name RENAME TO idx_new_name;

Django ORM: создание индексов

from django.db import models

class User(models.Model):
    email = models.EmailField(
        db_index=True  # Простой индекс
    )
    username = models.CharField(max_length=100)
    is_active = models.BooleanField(default=True)
    created_at = models.DateTimeField(auto_now_add=True)
    
    class Meta:
        # Составной индекс
        indexes = [
            models.Index(fields=["username", "created_at"]),
        ]
        # Уникальный индекс
        constraints = [
            models.UniqueConstraint(
                fields=["email"],
                name="user_email_unique"
            ),
        ]

Миграция для индекса

python manage.py makemigrations
python manage.py migrate

Джанго автоматически создаст SQL:

CREATE INDEX user_email ON users(email);
CREATE UNIQUE INDEX user_email_unique ON users(email);
CREATE INDEX idx_username_created ON users(username, created_at);

SQLAlchemy: индексы

from sqlalchemy import Column, String, Index, Boolean, DateTime, create_engine
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True)
    email = Column(String(255), index=True)  # Простой индекс
    username = Column(String(100))
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    # Составной индекс
    __table_args__ = (
        Index("idx_username_created", "username", "created_at"),
        Index("idx_active_users", "id", postgresql_where="is_active = true"),
    )

Типы индексов в PostgreSQL

-- B-tree (по умолчанию, для <, >, <=, >=, =, IN)
CREATE INDEX idx_standard ON users(id);

-- Hash (только для =, очень быстро)
CREATE INDEX idx_hash ON users USING HASH(email);

-- GiST (для геоданных, полнотекстового поиска)
CREATE INDEX idx_location ON places USING GIST(coordinates);

-- GIN (для массивов и JSON, полнотекстового поиска)
CREATE INDEX idx_tags ON posts USING GIN(tags);

-- BRIN (для очень больших таблиц, экономит место)
CREATE INDEX idx_huge_table ON huge_data USING BRIN(created_at);

Стратегия выбора индексов

# ПРОБЛЕМА: этот запрос медленный
users = User.objects.filter(email="john@example.com").first()  # 10 сек

# РЕШЕНИЕ 1: индекс на email
class Meta:
    indexes = [models.Index(fields=["email"])]
# Результат: 0.01 сек (1000x ускорение)

# ПРОБЛЕМА: этот запрос медленный
orders = Order.objects.filter(
    user_id=123,
    created_at__gte=datetime(2023, 1, 1)
).order_by("-created_at")  # 5 сек

# РЕШЕНИЕ: составной индекс в правильном порядке
class Meta:
    indexes = [
        # Порядок важен! Сначала колонны в WHERE, потом в ORDER BY
        models.Index(fields=["user_id", "-created_at"])
    ]
# Результат: 0.02 сек

EXPLAIN для анализа

-- PostgreSQL: показать план выполнения
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = "john@example.com";

-- Результат БЕЗ индекса:
Seq Scan on users  (cost=0.00..35.50 rows=1)
  Filter: (email = "john@example.com")

-- Результат С индексом:
Index Scan using idx_user_email on users  (cost=0.29..8.31 rows=1)
  Index Cond: (email = "john@example.com")

-- MySQL: EXPLAIN
EXPLAIN
SELECT * FROM users WHERE email = "john@example.com"\G

-- Смотри на rows: без индекса 100000, с индексом 1

Составные индексы (Column Order Matters!)

# ПЛОХО: неправильный порядок
class Meta:
    indexes = [
        models.Index(fields=["created_at", "user_id"])  # Неоптимально
    ]

# Запрос:
Order.objects.filter(user_id=1, created_at__gte="2023-01-01")
# БД проверит все строки с любой датой, потом отфильтрует по user_id

# ХОРОШО: правильный порядок (WHERE условия сначала)
class Meta:
    indexes = [
        models.Index(fields=["user_id", "created_at"])  # Оптимально
    ]

# Теперь БД сначала найдёт пользователя, потом дату

Проблемы с индексами

# ПРОБЛЕМА 1: Слишком много индексов
# Замедляет вставку и обновление, занимает место

# РЕШЕНИЕ: удалить неиспользуемые индексы
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0  # Никогда не использовался
ORDER BY idx_blks_read DESC, idx_blks_hit DESC;

# ПРОБЛЕМА 2: Индекс не используется
def bad_query():
    # Функция скрывает значение от оптимизатора
    users = User.objects.filter(
        email__upper="JOHN@EXAMPLE.COM"  # Индекс не сработает
    )
    return users

# РЕШЕНИЕ: использовать данные как есть или collate
users = User.objects.filter(
    email__iexact="john@example.com"  # Работает с индексом
)

# ПРОБЛЕМА 3: N+1 проблема
for post in Post.objects.all():  # 1 запрос
    print(post.author.name)  # 1000 запросов (N+1)

# РЕШЕНИЕ: select_related или prefetch_related
posts = Post.objects.select_related("author").all()  # 1 запрос

Мониторинг индексов

-- PostgreSQL: размер индексов
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- Неиспользуемые индексы
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Частичные индексы (для оптимизации)

-- Индекс только для активных пользователей
CREATE INDEX idx_active_users ON users(id)
WHERE is_active = true;

-- Экономит место и ускоряет вставку деактивированных пользователей
SELECT * FROM users WHERE id = 1 AND is_active = true;  -- Использует индекс
SELECT * FROM users WHERE id = 1 AND is_active = false;  -- Full scan (их мало)

Best Practices

1. Создавай индексы для часто используемых WHERE условий
2. Составной индекс: WHERE колонны first, ORDER BY second
3. Не переусложняй — слишком много индексов замедляют обновление
4. Используй EXPLAIN для проверки
5. Удаляй неиспользуемые индексы — займут много места
6. Помни про объём вставок vs поиска — для write-heavy таблиц меньше индексов
7. Для больших таблиц используй BRIN — экономит место
8. Мониторь индексы регулярно — они могут фрагментироваться
9. Не индексируй всё подряд — выбирай с умом
10. Первичный ключ уже индексирован — не нужно дополнительно

Индексы — это одна из самых важных оптимизаций БД, которые дают максимальный результат!