Что такое индексы в базах данных и когда их использовать?

Индексы в базах данных — ускорение поиска

Индекс в БД — это структура данных, которая ускоряет поиск, фильтрацию и сортировку по определённому столбцу. Без индекса БД просматривает все строки (full table scan), что медленно на больших таблицах.

Как работает индекс

Индекс можно представить как книжный указатель: вместо того чтобы читать всю книгу, вы смотрите в указатель и сразу находите нужные страницы.

# Без индекса — полное сканирование таблицы
SELECT * FROM users WHERE email = 'alice@example.com';
-- Проверяет каждую строку в таблице

# С индексом — быстрый поиск
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'alice@example.com';
-- Использует B-tree структуру для быстрого поиска

B-tree индекс (стандартный)

Мост часто используемый тип индекса — B-tree (сбалансированное дерево). Он хранит значения в отсортированном виде:

Корень
  ├─ [A-M]
  │  ├─ alice (0x1000)
  │  ├─ bob (0x2000)
  │  └─ maria (0x3000)
  └─ [N-Z]
     ├─ nina (0x4000)
     └─ zero (0x5000)

Поиск происходит логарифмически — O(log n) вместо O(n).

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

Создавай индексы для:

1. Первичные ключи (PRIMARY KEY) — всегда индексируются автоматически
2. Внешние ключи (FOREIGN KEY) — для быстрого JOIN
3. Столбцы в WHERE clause — особенно в часто используемых запросах
4. Столбцы в ORDER BY и GROUP BY — для ускорения сортировки
5. Столбцы с UNIQUE констрейнтом — для проверки уникальности

НЕ создавай индексы для:

1. Редко запрашиваемых столбцов
2. Столбцов с низкой кардинальностью (мало уникальных значений, например пол: М/Ж)
3. Больших TEXT или BLOB полей (неэффективно)
4. Слишком много индексов на одной таблице (замедляет INSERT/UPDATE/DELETE)

Примеры использования в Python

Пример 1: Создание простого индекса (PostgreSQL)

import sqlalchemy as sa
from sqlalchemy import create_engine, Column, Integer, String, 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, unique=True)  # Уникальный индекс автоматически
    username = Column(String, nullable=False)
    created_at = Column(sa.DateTime, nullable=False)
    
    # Явное создание индекса
    __table_args__ = (
        Index('idx_username', 'username'),
        Index('idx_created_at', 'created_at'),
    )

Пример 2: Составной индекс (несколько столбцов)

class Order(Base):
    __tablename__ = 'orders'
    
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, nullable=False)
    status = Column(String, nullable=False)
    created_at = Column(sa.DateTime, nullable=False)
    
    # Составной индекс для часто используемого запроса
    __table_args__ = (
        Index('idx_user_status', 'user_id', 'status'),
    )

# Быстрый запрос благодаря составному индексу
# SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

Пример 3: SQL запросы для управления индексами

from sqlalchemy import text

engine = create_engine('postgresql://user:pass@localhost/db')

with engine.connect() as conn:
    # Создание индекса
    conn.execute(text('''
        CREATE INDEX idx_users_email ON users(email);
    '''))
    conn.commit()
    
    # Удаление индекса
    conn.execute(text('''
        DROP INDEX idx_users_email;
    '''))
    conn.commit()
    
    # Информация об индексах
    result = conn.execute(text('''
        SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';
    '''))
    for row in result:
        print(f'{row.indexname}: {row.indexdef}')

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

B-tree индекс (самый частый)

CREATE INDEX idx_email ON users(email);  -- B-tree по умолчанию
SELECT * FROM users WHERE email = 'test@example.com';
SELECT * FROM users WHERE email LIKE 'test%';

Hash индекс (для точного совпадения)

CREATE INDEX idx_hash ON users USING HASH(status);
SELECT * FROM users WHERE status = 'active';  -- Быстро

GiST индекс (для полнотекстового поиска)

CREATE INDEX idx_search ON articles USING GiST(content);  -- PostgreSQL
SELECT * FROM articles WHERE content @@ to_tsquery('python & database');

JSONB индекс (для JSON полей)

CREATE INDEX idx_data ON users USING GIN(data);  -- PostgreSQL
SELECT * FROM users WHERE data->'role' = '"admin"';

Стоимость индексов

Преимущества:

• Ускорение SELECT запросов (часто 100x быстрее)
• Ускорение WHERE, ORDER BY, GROUP BY
• Ускорение JOIN операций

Недостатки:

• Замедление INSERT (нужно обновить индекс)
• Замедление UPDATE (нужно обновить индекс)
• Замедление DELETE (нужно обновить индекс)
• Использование дополнительной памяти на диске
• Сложность в поддержке

# Медленно с индексом на email
for i in range(1000000):
    user = User(email=f'user{i}@example.com', username=f'user{i}')
    db.session.add(user)
db.session.commit()  # Обновляет индекс 1 млн раз!

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

PostgreSQL: EXPLAIN ANALYZE

from sqlalchemy import text

with engine.connect() as conn:
    result = conn.execute(text('''
        EXPLAIN ANALYZE
        SELECT * FROM users WHERE email = 'alice@example.com';
    '''))
    
    for row in result:
        print(row[0])
    
    # Вывод покажет:
    # - с индексом: Seq Scan (Full Table Scan) или Index Scan
    # - Cost примерно 0.29..8.30 rows=1 (быстро)

MySQL: EXPLAIN

EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
-- type=ref означает использование индекса
-- type=ALL означает full table scan

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

1. Индексируй столбцы в WHERE clause часто используемых запросов
2. Проверяй производительность с EXPLAIN перед добавлением индекса
3. Используй составные индексы для часто используемых комбинаций
4. Мониторь размер индексов — они занимают место
5. Удаляй неиспользуемые индексы — они только замедляют пишущие операции
6. Переиндексируй после больших данных для оптимизации

# PostgreSQL: переиндексирование
with engine.connect() as conn:
    conn.execute(text('REINDEX TABLE users;'))
    conn.commit()

Ключевые моменты

• Индекс ускоряет SELECT, замедляет INSERT/UPDATE/DELETE
• B-tree — основной тип индекса для большинства случаев
• Индексируй первичные ключи, внешние ключи, WHERE clause
• Не индексируй редко используемые или низкокардинальные столбцы
• EXPLAIN ANALYZE помогает проверить использование индекса
• Баланс между чтением и записью — ключ к правильному индексированию