Какие индексы создавал и для чего?

Создание индексов в базах данных: Типы и применение

Индексы — фундамент оптимизации запросов. За 10+ лет я создавал разные типы индексов для разных сценариев. Расскажу о реальных примерах.

1. Primary Key индекс (Clustered)

CREATE TABLE users (
    id BIGINT PRIMARY KEY,  -- Автоматически создает B-tree индекс
    email VARCHAR(255),
    created_at TIMESTAMP
);

-- Используется для: быстрого поиска по ID
SELECT * FROM users WHERE id = 12345;  -- O(log n)

Зачем: Primary key query должен быть мгновенным. Это основа любого OLTP.

2. Foreign Key индекс

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    created_at TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE INDEX idx_orders_user_id ON orders(user_id);

Зачем:

• JOIN операции (user_id → users.id)
• CASCADE DELETE проверки
• Без индекса DELETE user получит full table scan по orders

# Плохо: N+1 query
for user in users:
    orders = db.query("SELECT * FROM orders WHERE user_id = %s", user.id)  # Медленно

# С индексом на user_id: быстро

3. Индекс на WHERE условии (поиск)

CREATE INDEX idx_users_email ON users(email);

-- Используется для:
SELECT * FROM users WHERE email = 'john@example.com';  -- O(log n)

Реальный кейс: Аутентификация по email — один из самых частых запросов.

4. Индекс на диапазон (Range)

CREATE INDEX idx_users_created_at ON users(created_at);

-- Используется для:
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

Реальный кейс: Ежедневные отчеты, аналитика по датам.

5. Составной индекс (Composite/Compound index)

-- Кейс: часто ищем заказы пользователя за период
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';

-- Может использоваться для:
SELECT * FROM orders WHERE user_id = 123;

-- НЕ используется для:
SELECT * FROM orders WHERE created_at >= '2024-01-01';  -- created_at не первая

Order имеет значение! B-tree сначала ищет по первой колонке.

6. Индекс для DISTINCT/GROUP BY

-- Часто используется GROUP BY: какие пользователи совершили заказы?
CREATE INDEX idx_orders_user_id ON orders(user_id);

SELECT DISTINCT user_id FROM orders;  -- Использует индекс
SELECT user_id FROM orders GROUP BY user_id;  -- Использует индекс

Реальный кейс: Аналитика активных пользователей.

7. Covering Index (включающий индекс)

-- Кейс: SELECT всех полей по email без доступа к основной таблице
CREATE INDEX idx_users_email_full 
ON users(email) INCLUDE (first_name, last_name, status);

SELECT email, first_name, last_name FROM users WHERE email = 'john@example.com';
-- PostgreSQL: не нужно обращаться к основной таблице (Index-only scan)

Выигрыш: Нет доступа к основной таблице, все в индексе (меньше I/O).

8. Partial Index (условный индекс)

-- Кейс: только активные пользователи часто ищут
CREATE INDEX idx_active_users_email 
ON users(email) WHERE status = 'active';

SELECT * FROM users WHERE email = 'john@example.com' AND status = 'active';  -- Использует
SELECT * FROM users WHERE email = 'john@example.com' AND status = 'inactive';  -- Не использует

Выигрыш: Индекс меньше, обновляется реже (только для активных).

9. FULLTEXT индекс (поиск по текст)

CREATE FULLTEXT INDEX idx_articles_content 
ON articles(title, content);

-- Быстрый текстовый поиск
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('machine learning' IN BOOLEAN MODE);

Альтернатива: Elasticsearch для more flexible full-text search.

10. JSON индекс (при хранении JSON)

-- PostgreSQL
CREATE INDEX idx_user_meta_age 
ON users USING GIN ((metadata -> 'age'));

SELECT * FROM users WHERE (metadata -> 'age')::int > 30;  -- Использует индекс

11. Индекс на функцию (Expression index)

-- Кейс: часто ищем по UPPER(email) (case-insensitive)
CREATE INDEX idx_users_email_upper ON users(UPPER(email));

SELECT * FROM users WHERE UPPER(email) = UPPER('John@Example.com');  -- Использует

12. Пример: Реальная таблица orders

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,           -- PRIMARY KEY
    user_id BIGINT NOT NULL,         -- FK
    product_id BIGINT NOT NULL,      -- FK
    status VARCHAR(50),              -- часто фильтруется
    amount DECIMAL(12,2),            -- иногда GROUP BY
    created_at TIMESTAMP,            -- диапазоны (от-до)
    updated_at TIMESTAMP
);

-- Индексы для моих query patterns:

-- 1. Заказы пользователя за период (очень частый запрос)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- 2. Поиск по статусу активных заказов
CREATE INDEX idx_orders_status_user 
ON orders(status, user_id) WHERE status IN ('pending', 'processing');

-- 3. Covering index для dashboard
CREATE INDEX idx_orders_user_full 
ON orders(user_id, created_at) INCLUDE (status, amount);

-- 4. Поиск по product
CREATE INDEX idx_orders_product ON orders(product_id);

-- Типичные query patterns:

-- A) Заказы пользователя за период → idx_orders_user_date
SELECT * FROM orders 
WHERE user_id = 123 AND created_at >= '2024-01-01';

-- B) Активные заказы → idx_orders_status_user
SELECT COUNT(*) FROM orders WHERE status = 'pending';

-- C) Dashboard: все заказы пользователя (с суммой) → idx_orders_user_full
SELECT user_id, created_at, status, amount FROM orders WHERE user_id = 123;

-- D) По продукту → idx_orders_product
SELECT * FROM orders WHERE product_id = 456;

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

-- 1. На колонках с низкой selectivity (мало уникальных значений)
-- Плохо: индекс на is_deleted (только true/false)
CREATE INDEX idx_orders_is_deleted ON orders(is_deleted);  -- Не нужен

-- 2. На редко используемых колонках
-- Если никто не ищет по status = 'archived', индекс — пустая трата

-- 3. На маленьких таблицах
SELECT * FROM status_enum;  -- Всего 10 строк, индекс не поможет

Мониторинг индексов

-- PostgreSQL: неиспользуемые индексы
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;  -- Никогда не использовались

-- MySQL: размер индексов
SELECT TABLE_NAME, INDEX_NAME, STAT_VALUE
FROM mysql.innodb_index_stats
WHERE STAT_NAME = 'size';

-- Удалить неиспользуемые
DROP INDEX idx_never_used ON orders;

Правила для 99% случаев

1. PRIMARY KEY — всегда
2. FOREIGN KEY — почти всегда
3. WHERE колонки — если используются в фильтрах
4. JOIN ON колонки — обязательно
5. ORDER BY колонки — если большой ORDER
6. GROUP BY колонки — если много GROUP
7. Composite indices — если часто вместе используются
8. Covering indices — для heavy OLTP
9. Partial indices — для сэкономления места

Заключение

Создание правильных индексов требует понимания query patterns приложения. Правило: сначала optimize for reads (индексы), потом (если нужно) optimize для writes (меньше индексов). Хороший индекс может ускорить запрос в 1000 раз, плохой — замедлить write в 10 раз.