Какие знаешь виды индексов в базах данных?

Виды индексов в базах данных

Индексы — это фундаментальный инструмент оптимизации БД. Расскажу про основные виды и когда их использовать.

B-Tree Index (самый распространённый)

B-Tree — сбалансированное дерево, используется по умолчанию в большинстве БД.

from sqlalchemy import 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(255), index=True)  # B-Tree индекс по умолчанию
    username = Column(String(100))
    created_at = Column(DateTime, index=True)
    
    # Составной индекс (composite index)
    __table_args__ = (
        Index('idx_username_email', 'username', 'email'),
    )

# SQL эквивалент:
# CREATE INDEX idx_users_email ON users(email);
# CREATE INDEX idx_username_email ON users(username, email);

Структура B-Tree:

          [50]
         /    \
      [25]    [75]
     /   \   /   \
   [10] [30] [60] [90]

Хорошо для:

• Поиск по конкретному значению (WHERE email = ?)
• Диапазонные запросы (WHERE age > 18 AND age < 65)
• Сортировка (ORDER BY created_at)
• LIKE с префиксом (WHERE name LIKE 'John%')

Медленно для:

• Поиск в середине: WHERE email LIKE '%gmail%'
• Полнотекстовый поиск

Hash Index

Hash — быстро находит точное совпадение, но не работает для диапазонов.

# MySQL пример
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    INDEX hash_email (email) USING HASH  -- Hash индекс
);

# PostgreSQL: hash индекс реже используется, но есть
CREATE INDEX idx_email_hash ON users USING hash (email);

Хорошо для:

• Точный поиск: WHERE email = 'user@example.com'
• Очень быстро (O(1) в среднем)

Плохо для:

• Диапазонные запросы: WHERE email LIKE '%gmail%' ✗
• Сортировка: ORDER BY email ✗

Bitmap Index

Bitmap — для столбцов с небольшим числом уникальных значений (категории, флаги).

# Хороший кандидат для bitmap индекса:
class Product(Base):
    __tablename__ = "products"
    
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    status = Column(Enum("active", "inactive", "archived"))  # Мало уникальных
    is_featured = Column(Boolean, index=True)  # Только 2 значения!
    category = Column(String(50), index=True)  # ~10-50 категорий

# SQL (для Oracle/Vertica):
# CREATE BITMAP INDEX idx_product_status ON products(status);

Bitmap представляет данные как биты:

status = 'active'   → 1 1 0 1 1 0 1 1
status = 'inactive' → 0 0 1 0 0 1 0 0
status = 'archived' → 0 0 0 0 0 0 0 0

Найти active: очень быстро (побитовые операции)

Хорошо для:

• Столбцы с мало уникальных значений
• OLAP запросы (analytics)
• Комбинированные фильтры

Плохо для:

• Часто обновляемые данные
• Много уникальных значений

Full-Text Index

Full-Text — для поиска текста (как Ctrl+F).

from sqlalchemy import Text
from sqlalchemy.dialects.mysql import FULLTEXT

class Article(Base):
    __tablename__ = "articles"
    
    id = Column(Integer, primary_key=True)
    title = Column(String(255))
    content = Column(Text)
    
    # MySQL
    __table_args__ = (
        Index('idx_article_ft', 'title', 'content', mysql_using='FULLTEXT'),
    )

# PostgreSQL использует встроенный полнотекстовый поиск
# CREATE INDEX idx_article_search ON articles USING gin(to_tsvector('english', content));

# Запросы:
# MySQL: SELECT * FROM articles WHERE MATCH(title, content) AGAINST('python web' IN BOOLEAN MODE);
# PostgreSQL: SELECT * FROM articles WHERE to_tsvector('english', content) @@ plainto_tsquery('english', 'python');

Хорошо для:

• Полнотекстовый поиск (как Google)
• Морфологический анализ (слова, корни)
• Фразовый поиск

Плохо для:

• Точные совпадения (B-Tree лучше)

Spatial Index (GiST, BRIN)

Spatial — для географических координат, многомерных данных.

from geoalchemy2 import Geometry
from sqlalchemy import Index

class Location(Base):
    __tablename__ = "locations"
    
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    coordinates = Column(Geometry('POINT', srid=4326))
    
    # GiST индекс для spatial запросов
    __table_args__ = (
        Index('idx_location_gist', 'coordinates', postgresql_using='gist'),
    )

# Запросы:
# SELECT * FROM locations WHERE ST_DWithin(coordinates, 'POINT(37.77 -122.41)', 1000);
# Найти все точки в радиусе 1000м от координат

Хорошо для:

• Географический поиск (карты)
• Поиск по близости
• Многомерные данные

Partial (Filtered) Index

Partial — индекс только для части строк (фильтрованный).

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True)
    email = Column(String(255))
    is_deleted = Column(Boolean, default=False)

# Создаём индекс только для активных пользователей
# PostgreSQL:
# CREATE INDEX idx_active_users ON users(email) WHERE is_deleted = FALSE;

# Преимущества:
# - Меньше размер индекса (не индексируем удалённые)
# - Быстрее обновление
# - Скорость поиска не меняется

Хорошо для:

• Логически удалённые данные
• Состояния (active, draft, archived)
• Очистка старых данных

Covering Index (Include Columns)

Covering — индекс содержит все нужные данные, не требует доступа к таблице.

class Order(Base):
    __tablename__ = "orders"
    
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer)
    total_amount = Column(Decimal)
    created_at = Column(DateTime)

# Covering индекс — содержит user_id, total_amount, created_at
# PostgreSQL:
# CREATE INDEX idx_user_orders ON orders(user_id) INCLUDE (total_amount, created_at);

# Запрос без доступа к таблице:
# SELECT total_amount, created_at FROM orders WHERE user_id = 123;
# ← использует только индекс, таблица не читается!

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

• Index-only scan (очень быстро)
• Не требует обращения к основной таблице

Composite (Multi-column) Index

Composite — индекс по нескольким столбцам.

class Product(Base):
    __tablename__ = "products"
    
    id = Column(Integer, primary_key=True)
    category_id = Column(Integer)
    status = Column(String(20))
    price = Column(Decimal)
    created_at = Column(DateTime)
    
    __table_args__ = (
        Index('idx_category_status_price', 'category_id', 'status', 'price'),
    )

# SQL:
# CREATE INDEX idx_products ON products(category_id, status, price);

# Работает для запросов, использующих LEFT PART правила:
queries_ok = [
    "WHERE category_id = 5",
    "WHERE category_id = 5 AND status = 'active'",
    "WHERE category_id = 5 AND status = 'active' AND price > 100",
]

queries_bad = [
    "WHERE status = 'active'",  # Не начинается с category_id!
    "WHERE price > 100",        # Пропускает первые колонки!
    "WHERE category_id = 5 AND price > 100",  # Пропускает status!
]

Inverted Index

Inverted — для полнотекстового поиска и документов.

Уобычное индексирование:
  document_1 → ['python', 'coding', 'tutorial']
  document_2 → ['java', 'coding']

Inverted Index:
  'python' → [document_1]
  'coding' → [document_1, document_2]
  'tutorial' → [document_1]
  'java' → [document_2]

Поиск 'coding' — мгновенно找 документы [1, 2]

Сравнение индексов

┌──────────────┬──────────┬─────────────┬──────────┬─────────────┐
│ Тип          │ Точный   │ Диапазон    │ Сортир.  │ Полнотекст  │
├──────────────┼──────────┼─────────────┼──────────┼─────────────┤
│ B-Tree       │ ⭐⭐⭐⭐⭐ │ ⭐⭐⭐⭐⭐   │ ⭐⭐⭐⭐⭐ │ ✗           │
│ Hash         │ ⭐⭐⭐⭐⭐ │ ✗           │ ✗        │ ✗           │
│ Bitmap       │ ⭐⭐⭐   │ ⭐⭐⭐       │ ✗        │ ✗           │
│ Full-Text    │ ⭐⭐⭐   │ ✗           │ ✗        │ ⭐⭐⭐⭐⭐   │
│ Spatial      │ ⭐⭐⭐   │ ⭐⭐⭐⭐⭐   │ ✗        │ ✗           │
└──────────────┴──────────┴─────────────┴──────────┴─────────────┘

Когда НЕ нужны индексы

# ❌ Не индексируй:
class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True)
    biography = Column(Text)  # Большой текст, редко ищут
    password_hash = Column(String(255))  # Зачем искать по паролю?
    json_data = Column(JSON)  # Медленно индексировать
    
    # ✅ А это индексируй:
    email = Column(String(255), index=True)  # Часто ищут
    username = Column(String(100), index=True)  # Часто ищут
    is_active = Column(Boolean, index=True)  # Много фильтров

EXPLAIN ANALYZE

from sqlalchemy import text

# Анализируем план выполнения
query = "SELECT * FROM users WHERE email = 'test@example.com'"
explain = session.execute(text(f"EXPLAIN ANALYZE {query}")).fetchall()
for row in explain:
    print(row)

# Результат:
# Seq Scan on users (cost=0.00..35.00 rows=1)  ← БЕЗ индекса
# Filter: (email = 'test@example.com')

# После создания индекса:
# Index Scan using idx_email on users (cost=0.29..8.30 rows=1)  ← С индексом!

Рекомендации

1. Начни с B-Tree для большинства столбцов
2. Составные индексы для частых фильтров (но не более 3-4 колонок)
3. Full-Text если есть поиск по тексту
4. Partial индексы для логически удалённых
5. Covering индексы для часто читаемых данных
6. Профилируй с EXPLAIN ANALYZE перед оптимизацией
7. Помни про trade-off: индексы ускоряют чтение, но замедляют запись (INSERT, UPDATE, DELETE)