Можно ли создать индекс на 2 поля?

Создание индекса на два поля в базе данных

Да, создание составного индекса (composite index) на несколько полей — это очень распространённая практика в базах данных. Это значительно улучшает производительность запросов с фильтрацией по нескольким столбцам.

SQL примеры

PostgreSQL:

-- Создание составного индекса
CREATE INDEX idx_user_email_status ON users(email, status);

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

-- Частичный индекс
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Индекс на выражение
CREATE INDEX idx_user_email_lower ON users(LOWER(email));

MySQL:

-- Составной индекс
CREATE INDEX idx_user_email_status ON users(email, status);
ALTER TABLE users ADD INDEX idx_email_status(email, status);

-- Проверка индексов
SHOW INDEXES FROM users;

Использование в Python (SQLAlchemy)

from sqlalchemy import Column, String, Integer, Index
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    email = Column(String, nullable=False)
    status = Column(String, default='active')
    created_at = Column(DateTime, nullable=False)
    
    # Способ 1: Index в __table_args__
    __table_args__ = (
        Index('idx_email_status', 'email', 'status'),
        Index('idx_created_status', 'created_at', 'status'),
    )

# Способ 2: Использование Index напрямую
user_email_status_idx = Index(
    'idx_user_email_status',
    User.email,
    User.status
)
user_email_status_idx.create(engine)

Порядок полей в индексе

Порядок полей в индексе критичен для производительности:

-- Плохо: неправильный порядок
CREATE INDEX idx_bad ON orders(user_id, status, created_at);

-- Запрос использует индекс частично:
SELECT * FROM orders WHERE status = 'pending';  -- Не использует индекс эффективно

-- Хорошо: часто используемые фильтры первыми
CREATE INDEX idx_good ON orders(status, user_id, created_at);

-- Этот запрос использует индекс полностью:
SELECT * FROM orders WHERE status = 'pending' AND user_id = 123;

Правила построения составных индексов

1. Правило ESR (Equality, Sort, Range):

-- Порядок: сначала equality, потом sort, потом range
CREATE INDEX idx_optimal ON posts(
    author_id,      -- equality
    created_at DESC, -- sort
    likes           -- range
);

-- Эффективный запрос:
SELECT * FROM posts 
WHERE author_id = 1 
ORDER BY created_at DESC 
AND likes > 100;

2. Выборочность первого поля:

-- Плохо: первое поле мало различимо
CREATE INDEX idx_bad ON users(gender, email);  -- gender имеет только M/F

-- Хорошо: первое поле с высокой выборочностью
CREATE INDEX idx_good ON users(email, gender);

Проверка использования индекса

PostgreSQL:

EXPLAIN ANALYZE
SELECT * FROM users 
WHERE email = 'test@example.com' 
AND status = 'active';

-- Результат покажет, используется ли индекс idx_email_status

MySQL:

EXPLAIN 
SELECT * FROM users 
WHERE email = 'test@example.com' 
AND status = 'active';

-- Смотрим на поле 'key' и 'rows'

Python — проверка с SQLAlchemy

from sqlalchemy import text, inspect

# Получить информацию об индексах
inspector = inspect(engine)
indexes = inspector.get_indexes('users')

for idx in indexes:
    print(f"Индекс: {idx['name']}")
    print(f"Поля: {idx['column_names']}")
    print(f"Уникальный: {idx['unique']}")

# Выполнить EXPLAIN
with engine.connect() as conn:
    result = conn.execute(text("""
        EXPLAIN (ANALYZE, BUFFERS)
        SELECT * FROM users 
        WHERE email = :email AND status = :status
    """), {"email": "test@test.com", "status": "active"})
    
    for row in result:
        print(row)

Миграции с использованием индексов

# migrations/0001_add_user_indexes.sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMP WITH TIME ZONE NOT NULL
);

-- Составной индекс
CREATE INDEX idx_users_email_status ON users(email, status);

-- Индекс для сортировки
CREATE INDEX idx_users_created_status ON users(created_at DESC, status);

Производительность

• Индекс ускоряет SELECT, WHERE, JOIN, ORDER BY запросы
• Индекс замедляет INSERT, UPDATE, DELETE (нужно обновлять индекс)
• Составной индекс требует больше памяти чем одиночный
• Каждый дополнительный индекс — это компромисс между скоростью чтения и записи

Когда нужны составные индексы

• Частые фильтры по двум и более полям
• Сортировка по полям из индекса
• Покрывающие индексы (covering index) для быстрого access к данным

Заключение

Составные индексы на 2+ поля — это мощный инструмент для оптимизации БД. Главное — правильно выбрать порядок полей на основе паттернов запросов в приложении.