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

Как сделать индекс в БД?

1.0 Junior🔥 191 комментариев
#Базы данных (SQL)

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

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

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

Как сделать индекс в базе данных

Это техническое вопрос, который проверяет понимание оптимизации БД. Рассмотрю на примере PostgreSQL и SQLAlchemy.

Что такое индекс?

Индекс — это структура данных, которая ускоряет поиск по определённым полям. Без индекса БД выполняет полный скан таблицы (Sequential Scan), что медленно. С индексом — быстро находит нужные строки.

Без индекса: SELECT * FROM users WHERE email = 'john@example.com'
→ PostgreSQL проверяет ВСЕ 1 млн строк
→ Очень медленно

С индексом: SELECT * FROM users WHERE email = 'john@example.com'
→ PostgreSQL использует B-tree структуру
→ Находит за ~log(N) сравнений = очень быстро

1. Создание индекса в SQL

Простой индекс на одно поле

-- Создаём индекс на поле email
CREATE INDEX idx_users_email ON users(email);

-- Проверяем создание
\d users  -- В PostgreSQL

Уникальный индекс

-- Гарантирует уникальность (как PRIMARY KEY, но гибче)
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Теперь две записи с одинаковым email добавить нельзя
INSERT INTO users (email) VALUES ('john@example.com');  -- OK
INSERT INTO users (email) VALUES ('john@example.com');  -- ERROR

Составной индекс (на несколько полей)

-- Индекс на несколько колонок для queries типа:
-- SELECT * FROM orders WHERE user_id = ? AND status = ?
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';  -- status вторая колонка

Индекс на выражения

-- Индекс на функцию для регистронезависимого поиска
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Теперь быстро работает:
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

Частичный индекс (условный)

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

-- Экономит место, т.к. не индексирует удалённых пользователей
SELECT * FROM users WHERE id = 5 AND active = true;  -- Использует индекс
SELECT * FROM users WHERE id = 5 AND active = false;  -- Полный скан

Удаление индекса

DROP INDEX idx_users_email;

2. Создание индекса через SQLAlchemy (ORM)

В определении модели

from sqlalchemy import Column, Integer, String, Index, Boolean
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    email = Column(String, unique=True)  # Автоматически создаёт индекс
    first_name = Column(String)
    last_name = Column(String)
    active = Column(Boolean, default=True)
    
    # Простой индекс
    __table_args__ = (
        Index('idx_users_firstname', 'first_name'),
        Index('idx_users_active', 'active'),
    )

class Order(Base):
    __tablename__ = 'orders'
    
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    status = Column(String)
    created_at = Column(DateTime)
    
    # Составной индекс
    __table_args__ = (
        Index('idx_orders_user_status', 'user_id', 'status'),
    )

Индекс на выражение через SQLAlchemy

from sqlalchemy import func

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    email = Column(String)
    
    # Индекс на LOWER(email)
    __table_args__ = (
        Index('idx_users_email_lower', func.lower(email)),
    )

Частичный индекс через SQLAlchemy

from sqlalchemy import and_

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    email = Column(String)
    active = Column(Boolean)
    
    # Индекс только для активных пользователей
    __table_args__ = (
        Index(
            'idx_users_email_active',
            'email',
            postgresql_where=and_(active == True)
        ),
    )

3. Миграция с Alembic/Goose

Если используешь миграции (Goose)

-- migrations/0003_add_indexes.sql

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
CREATE INDEX idx_users_active ON users(id) WHERE active = true;

Откат (если нужно удалить индексы)

-- Downsql для Goose
DROP INDEX IF EXISTS idx_users_email;
DROP INDEX IF EXISTS idx_orders_user_status;
DROP INDEX IF EXISTS idx_users_active;

4. Как выбрать поля для индексов?

Поля, которые НУЖНО индексировать:

# 1. Поля в WHERE условиях (часто ищешь по ним)
SELECT * FROM users WHERE email = ?  # Индексируй email
SELECT * FROM orders WHERE status = ?  # Индексируй status

# 2. Поля в JOIN условиях (Foreign Keys)
SELECT * FROM orders
JOIN users ON orders.user_id = users.id  # Индексируй user_id

# 3. Поля в ORDER BY (сортировка)
SELECT * FROM posts ORDER BY created_at  # Индексируй created_at

# 4. Поля в GROUP BY
SELECT COUNT(*), category FROM products
GROUP BY category  # Индексируй category

Поля, которые НЕ НУЖНО индексировать:

# 1. Boolean поля с неравномерным распределением
# Индекс на is_deleted не помогает, если 99% активные

# 2. Поля с мало уникальными значениями (low cardinality)
# Индекс на gender (M/F) бесполезен

# 3. Очень большие текстовые поля
# Индекс на описание занимает много места

# 4. Часто обновляемые поля
# Индекс нужно перестраивать при каждом обновлении

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

EXPLAIN ANALYZE — твой лучший друг

-- Без индекса
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';

-- Вывод (плохо):
-- Seq Scan on users  (cost=0.00..1234.00 rows=1)
-- Filter: (email = 'john@example.com')
-- Planning Time: 0.1 ms
-- Execution Time: 45.2 ms  <- МЕДЛЕННО!

-- С индексом
CREATE INDEX idx_users_email ON users(email);
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';

-- Вывод (хорошо):
-- Index Scan using idx_users_email on users
-- Index Cond: (email = 'john@example.com')
-- Planning Time: 0.1 ms
-- Execution Time: 0.5 ms  <- БЫСТРО!

В Python коде

from sqlalchemy import event, create_engine
import logging

logger = logging.getLogger('sqlalchemy.engine')
logger.setLevel(logging.INFO)

# Включи SQL логирование для анализа
engine = create_engine(
    'postgresql://user:pass@localhost/db',
    echo=True  # Выводит все SQL запросы
)

# Теперь видишь, какие запросы выполняются

6. Практический пример: оптимизация медленного приложения

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

Base = declarative_base()

class BlogPost(Base):
    __tablename__ = 'blog_posts'
    
    id = Column(Integer, primary_key=True)
    author_id = Column(Integer, ForeignKey('users.id'))
    title = Column(String(255))
    slug = Column(String(255), unique=True)  # Автоматический индекс
    content = Column(String)
    published = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    
    # Индексы для типичных queries
    __table_args__ = (
        # Часто ищем по author_id
        Index('idx_posts_author', 'author_id'),
        
        # Часто ищем опубликованные посты
        Index('idx_posts_published', 'published'),
        
        # Часто сортируем по created_at
        Index('idx_posts_created', 'created_at'),
        
        # Часто: все посты автора, отсортированные по дате
        Index('idx_posts_author_created', 'author_id', 'created_at'),
        
        # Частичный индекс: только опубликованные
        Index(
            'idx_posts_published_created',
            'created_at',
            postgresql_where='published = true'
        ),
    )

Типичные queries:

# Query 1: Все посты автора
author_posts = session.query(BlogPost).filter_by(author_id=5).all()
# Использует: idx_posts_author

# Query 2: Опубликованные посты, отсортированные по дате
recent_posts = session.query(BlogPost)\
    .filter_by(published=True)\
    .order_by(BlogPost.created_at.desc())\
    .limit(10)
    .all()
# Использует: idx_posts_published_created

# Query 3: Пост по slug (быстро благодаря unique)
post = session.query(BlogPost).filter_by(slug='my-post').first()
# Использует: автоматический индекс на slug

7. Лучшие практики

Не переусложняй:

# Плохо: индекс на каждое поле
class User(Base):
    __table_args__ = (
        Index('idx_id', 'id'),  # Ненужно, уже PRIMARY KEY
        Index('idx_name', 'name'),  # Редко ищем по имени
        Index('idx_phone', 'phone'),  # Медленно обновляется
    )

# Хорошо: индекс только на нужные поля
class User(Base):
    __table_args__ = (
        Index('idx_email', 'email'),  # Часто ищем по email
        Index('idx_active', 'active'),  # Часто фильтруем
    )

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

-- Какие индексы вообще используются?
SELECT schemaname, tablename, indexname 
FROM pg_indexes 
WHERE schemaname = 'public';

-- Есть ли неиспользуемые индексы?
SELECT i.relname AS index_name,
       (SELECT COUNT(*) FROM pg_stat_user_indexes 
        WHERE indexrelname = i.relname AND idx_scan = 0) AS never_used
FROM pg_class i
WHERE i.relkind = 'i';

Резюме

Когда создавать индекс:

  • На поля в WHERE, JOIN, ORDER BY, GROUP BY
  • На Foreign Keys
  • На поля, используемые для фильтрации

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

  • Простой индекс (одно поле)
  • Составной индекс (несколько полей)
  • Уникальный индекс (UNIQUE)
  • Частичный индекс (WHERE условие)
  • Индекс на выражение (LOWER(email))

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

  • EXPLAIN ANALYZE
  • pg_stat_user_indexes
  • SQLAlchemy echo=True

Золотое правило: Создавай индекс только после анализа EXPLAIN, убедись, что это действительно нужно.

Как сделать индекс в БД? | PrepBro