PrepBro
← Назад к вопросам

Чем полезны для производительности составные индексы?

2.0 Middle🔥 171 комментариев
#Базы данных (SQL)

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI22 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Составные индексы и производительность

Составные индексы (Composite Indexes или Multi-Column Indexes) — это индексы, построенные на нескольких колонках одновременно. Они критически важны для оптимизации сложных запросов.

1. Основная концепция

Составной индекс создаёт структуру для быстрого поиска по нескольким полям одновременно:

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

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

-- Индекс на 3+ колонках
CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at);

Второй индекс полезен для запросов вроде:

-- Быстро найдёт пользователя по email И status благодаря составному индексу
SELECT * FROM users WHERE email = 'john@example.com' AND status = 'active';

-- Также сработает для части условий (prefix search)
SELECT * FROM users WHERE email = 'john@example.com';

-- Но для этого индекс уже НЕ поможет (reverse order)
SELECT * FROM users WHERE status = 'active';

2. B-Tree структура составного индекса

Построение индекса:

InDEX (email, status)

Создаёт сортировку:
- (alice@example.com, active)
- (alice@example.com, inactive)
- (bob@example.com, active)
- (bob@example.com, inactive)
- (charlie@example.com, active)

Примечание: первая колонка (email) сортируется основная,
вторая (status) сортирует внутри каждого значения email

3. Примеры использования и оптимизация

Сценарий 1: Фильтрация по нескольким полям

-- Таблица логов
CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    user_id INT,
    event_type VARCHAR(50),
    severity VARCHAR(20),
    created_at TIMESTAMPTZ,
    message TEXT
);

-- Плохо: три отдельных индекса
CREATE INDEX idx_user_id ON logs(user_id);
CREATE INDEX idx_event_type ON logs(event_type);
CREATE INDEX idx_severity ON logs(severity);

-- Хорошо: один составной индекс
CREATE INDEX idx_logs_composite ON logs(user_id, event_type, severity);

-- Запрос использует весь индекс
SELECT COUNT(*) FROM logs 
WHERE user_id = 123 
  AND event_type = 'error' 
  AND severity = 'critical';

Сценарий 2: Сортировка и фильтрация

-- Таблица заказов
CREATE TABLE orders (
    id UUID PRIMARY KEY,
    user_id UUID,
    status VARCHAR(20),
    total_amount DECIMAL(10, 2),
    created_at TIMESTAMPTZ
);

-- Составной индекс для этого запроса
CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at DESC);

-- Запрос использует индекс для поиска И сортировки
SELECT * FROM orders
WHERE user_id = 'user-123' AND status = 'completed'
ORDER BY created_at DESC
LIMIT 10;

Без индекса:

  • Сканирование всей таблицы O(n)
  • Сортировка результатов в памяти (дорого)

С индексом:

  • Прямой доступ к отсортированным данным O(log n)
  • Возврат уже отсортированных результатов

4. Правило: Порядок колонок в индексе

Порядок критически важен! Это правило называется Equality-Range-Sort (ERS):

-- Хороший порядок колонок
CREATE INDEX idx_good ON orders(user_id, status, created_at);
--                              Equality Range  Sort
--                                 ↓       ↓      ↓

-- Запросы, которые будут быстрыми:

-- 1. WHERE user_id = ? AND status = ? ORDER BY created_at
--    ✓ Использует весь индекс

-- 2. WHERE user_id = ? AND status = ? 
--    ✓ Использует первые две колонки

-- 3. WHERE user_id = ?
--    ✓ Использует первую колонку (prefix search)

-- 4. WHERE user_id = ? AND created_at > ?
--    ✗ Индекс не поможет с created_at (status пропущен)

Плохой порядок:

CREATE INDEX idx_bad ON orders(created_at, status, user_id);

-- Запрос WHERE user_id = ? не может использовать начало индекса
-- Пришлось бы сканировать весь индекс (неэффективно)

5. Пример в PostgreSQL

-- Реальный пример: интернет-магазин
CREATE TABLE products (
    id UUID PRIMARY KEY,
    category_id UUID NOT NULL,
    brand_id UUID NOT NULL,
    price DECIMAL(10, 2),
    in_stock BOOLEAN,
    created_at TIMESTAMPTZ,
    rating FLOAT
);

CREATE TABLE reviews (
    id UUID PRIMARY KEY,
    product_id UUID REFERENCES products(id),
    user_id UUID,
    rating INT,
    created_at TIMESTAMPTZ
);

-- Составной индекс для поиска товаров по категории, наличию и цене
CREATE INDEX idx_products_category_stock_price 
ON products(category_id, in_stock, price);

-- Составной индекс для отзывов
CREATE INDEX idx_reviews_product_user_date 
ON reviews(product_id, user_id, created_at DESC);

-- Быстрый запрос благодаря индексу
SELECT p.* FROM products p
WHERE p.category_id = 'cat-123' 
  AND p.in_stock = true
  AND p.price BETWEEN 100 AND 500
ORDER BY p.created_at DESC
LIMIT 20;

6. Пример на SQLAlchemy + Python

from sqlalchemy import Column, Integer, String, Float, Boolean, DateTime, ForeignKey, create_engine, Index
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
from datetime import datetime
from sqlalchemy.dialects.postgresql import UUID
import uuid

Base = declarative_base()

class Product(Base):
    __tablename__ = 'products'
    
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    category_id = Column(UUID(as_uuid=True), nullable=False)
    brand_id = Column(UUID(as_uuid=True), nullable=False)
    price = Column(Float)
    in_stock = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    # Составной индекс
    __table_args__ = (
        Index('idx_products_category_stock_price', 'category_id', 'in_stock', 'price'),
        Index('idx_products_brand_stock', 'brand_id', 'in_stock'),
    )

class Review(Base):
    __tablename__ = 'reviews'
    
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    product_id = Column(UUID(as_uuid=True), ForeignKey('products.id'))
    user_id = Column(UUID(as_uuid=True))
    rating = Column(Integer)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    __table_args__ = (
        Index('idx_reviews_product_rating', 'product_id', 'rating'),
    )

# Использование в запросах
engine = create_engine('postgresql://user:password@localhost/db')

with Session(engine) as session:
    # Запрос использует составной индекс
    products = session.query(Product).filter(
        Product.category_id == 'cat-123',
        Product.in_stock == True,
        Product.price >= 100,
        Product.price <= 500
    ).order_by(Product.created_at.desc()).limit(20).all()
    
    # Проверка плана выполнения
    from sqlalchemy import text
    
    result = session.execute(text("""
        EXPLAIN ANALYZE
        SELECT * FROM products 
        WHERE category_id = 'cat-123' 
          AND in_stock = true 
          AND price BETWEEN 100 AND 500
        ORDER BY created_at DESC
        LIMIT 20
    """))
    
    for row in result:
        print(row)

7. Анализ производительности

-- Проверка плана выполнения без индекса
EXPLAIN ANALYZE
SELECT * FROM orders 
WHERE user_id = 'user-123' AND status = 'completed';

-- Результат БЕЗ индекса:
-- Seq Scan on orders  (cost=0.00..35000.00 rows=100000)
--   Filter: (user_id = 'user-123' AND status = 'completed')

-- Создаём индекс
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Результат С индексом:
-- Bitmap Index Scan using idx_orders_user_status  (cost=0.00..100.00 rows=50)
--   Index Cond: (user_id = 'user-123' AND status = 'completed')

-- Улучшение: ~350x быстрее!

8. Минусы составных индексов

  • Занимают место на диске — индекс = дополнительные данные
  • Замедляют INSERT/UPDATE/DELETE — нужно обновлять индекс
  • Сложность выбора — нужно выбрать правильный порядок колонок
  • Переиспользование — избыток индексов = медленнее всё

9. Когда создавать составные индексы

-- Измеряй:
-- 1. Частота запроса
-- 2. Размер таблицы (маленькие таблицы < 10k строк не нуждаются в индексах)
-- 3. Селективность (уникальность значений)

-- Индексируй только горячие запросы
-- Убирай неиспользуемые индексы

-- Найти неиспользуемые индексы в PostgreSQL
SELECT schemaname, tablename, indexname
FROM pg_indexes
WHERE idx_scan = 0
ORDER BY idx_size DESC;

Резюме

Составные индексы улучшают производительность:

  1. Многоколончатые WHERE условия — ускорение в 100-1000x
  2. Сортировка — использование отсортированного индекса
  3. Covering индексы — все данные в индексе (zero disk reads)
  4. Prefix search — использование левых колонок индекса

Ключевое правило: Equality-Range-Sort (ERS) — порядок колонок в индексе критичен!