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

Индексы в базах данных

Индекс — это специальная структура данных, которая ускоряет поиск информации в таблице БД, подобно индексу в конце книги. Понимание индексов критично для аналитика, который пишет быстрые запросы к большим таблицам.

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

Без индекса база данных должна просканировать каждую строку таблицы (Full Table Scan), чтобы найти нужные данные. С индексом база может быстро прыгнуть к нужным строкам.

Аналогия: Ищешь имя в телефонной книге:

• Без индекса: листаешь каждую страницу с начала
• С индексом: открываешь букву и сразу видишь имена на эту букву

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

1. B-Tree индекс (самый распространенный)

Оптимален для поиска диапазонов, сортировки и равенства.

-- Создание простого индекса
CREATE INDEX idx_users_email ON users(email);

-- Индекс на нескольких столбцах (composite index)
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- Использование индекса
SELECT * FROM users WHERE email = 'user@example.com'; -- Быстро!
SELECT * FROM orders WHERE user_id = 123 AND order_date > '2024-01-01'; -- Быстро!

2. Hash индекс

Оптимален только для точного поиска (=), не для диапазонов.

CREATE INDEX idx_user_id_hash ON users USING HASH(user_id);

3. GiST индекс (для географических данных)

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

CREATE INDEX idx_geo ON users USING gist(location);

4. Уникальный индекс (Unique Index)

Одновременно обеспечивает быстрый поиск и уникальность.

CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

5. Частичный индекс (Partial Index)

Индексирует только часть таблицы, экономит место.

-- Индексируем только активных пользователей
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

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

Плюсы:

• Быстрые поиски: вместо O(n) получаешь O(log n)
• Быстрые фильтры: WHERE условия выполняются быстрее
• Быстрые сортировки: если индекс на столбце сортировки
• Быстрые JOIN: если индекс на ключе связи

Минусы:

• Замедленные вставки: нужно обновить индекс
• Замедленные обновления: нужно обновить индекс
• Место на диске: индексы занимают дополнительное место
• Потребление памяти: индексы хранятся в памяти

Примеры для Product Analytics

Плохой запрос (без индекса):

-- Полный скан таблицы из 10 млн строк
SELECT user_id, revenue
FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

-- Без индекса это может занять 30+ секунд

Быстрый запрос (с индексом):

-- Создаем индекс
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Теперь же запрос выполняется за миллисекунды
SELECT user_id, revenue
FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

Правильный составной индекс:

-- Если часто фильтруешь по user_id И дате
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Этот запрос будет очень быстрым
SELECT *
FROM orders
WHERE user_id = 123 AND created_at >= '2024-01-01';

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

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

1. Колонка используется в WHERE: WHERE user_id = 123
2. Колонка используется в JOIN: ON orders.user_id = users.id
3. Колонка используется в ORDER BY: ORDER BY created_at
4. Большая таблица: 100k+ строк
5. Часто queried: регулярно используется в аналитических запросах

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

1. Высокий cardinality: много UPDATE/INSERT операций
2. Мало строк: < 10k строк
3. Низкий selectivity: колонка имеет мало уникальных значений

Проверка эффективности индексов

PostgreSQL:

-- Посмотри план выполнения
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 123 AND created_at > '2024-01-01';

-- "Seq Scan" — плохо (полный скан)
-- "Index Scan" — хорошо (использует индекс)
-- "Bitmap Index Scan" — тоже хорошо

Анализ использования индексов:

-- Индексы которые никогда не используются
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan as scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY tablename, indexname;

SQL Query Optimization с индексами

Пример 1: Анализ конверсии по странам

-- Без индексов: может быть медленно
SELECT 
  users.country,
  COUNT(DISTINCT users.user_id) as users,
  COUNT(DISTINCT CASE WHEN orders.order_id IS NOT NULL THEN users.user_id END) as converters,
  ROUND(
    COUNT(DISTINCT CASE WHEN orders.order_id IS NOT NULL THEN users.user_id END) * 100.0 /
    COUNT(DISTINCT users.user_id), 2
  ) as conversion_rate
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id
WHERE users.created_at >= '2024-01-01'
GROUP BY users.country
ORDER BY conversion_rate DESC;

-- Добавляем индексы
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_orders_user_id ON orders(user_id);

Пример 2: Когортный анализ

-- Индексы для быстрого когортного анализа
CREATE INDEX idx_events_user_date ON events(user_id, event_date);
CREATE INDEX idx_users_signup_date ON users(DATE_TRUNC('day', signup_date));

WITH cohorts AS (
  SELECT 
    DATE_TRUNC('week', signup_date) as signup_week,
    COUNT(DISTINCT user_id) as cohort_size
  FROM users
  GROUP BY DATE_TRUNC('week', signup_date)
)
SELECT 
  signup_week,
  cohort_size
FROM cohorts
ORDER BY signup_week DESC;

Best Practices

1. Используй EXPLAIN: перед оптимизацией понимай, как БД выполняет запрос
2. Не переусложняй: не создавай индекс на каждый столбец
3. Составные индексы: для запросов с несколькими WHERE условиями
4. Порядок важен: в составном индексе сначала идут столбцы из WHERE, потом ORDER BY
5. Удаляй неиспользуемые: периодически удаляй индексы, которые не ускоряют
6. Мониторь производительность: отслеживай медленные запросы

Индексы — это основной инструмент для оптимизации аналитических запросов к большим таблицам.