По каким полям создавал бы индекс, при медленном запросе и выборкой по нескольким полям

# По каким полям создавал бы индекс при медленном запросе и выборке по нескольким полям

Это классический вопрос о оптимизации баз данных и индексировании. Правильный выбор полей для индекса критически важен для производительности запросов.

Анализ проблемы

Когда запрос медленный и выбирает по нескольким полям, нужно понять:

1. Какие поля используются в WHERE?
2. Какие поля в ORDER BY?
3. Какие поля в JOIN?
4. Какие данные выбираются (SELECT)?

Правило ESR для индексов

ESR = Equality, Sort, Range — порядок полей в составном индексе:

INDEX (field_equality, field_order, field_range)
         ↑               ↑              ↑
      WHERE =         ORDER BY      WHERE <>
                      LIMIT

Пример: Медленный запрос

SELECT user_id, name, created_at
FROM users
WHERE status = ACTIVE
  AND department_id = 5
  AND created_at >= 2024-01-01
ORDER BY created_at DESC
LIMIT 10;

Анализ:

• WHERE status = ACTIVE → Equality (точное совпадение)
• WHERE department_id = 5 → Equality (точное совпадение)
• WHERE created_at >= 2024-01-01 → Range (диапазон)
• ORDER BY created_at DESC → Sort (сортировка)

Оптимальный индекс (ESR правило):

CREATE INDEX idx_users_esr 
ON users(status, department_id, created_at);
       ↑    ↑              ↑              ↑
   Equality #1        Equality #2      Range

Пошаговый подход

Шаг 1: Определить поля равенства (WHERE col = value)

-- Медленный запрос
SELECT * FROM orders
WHERE customer_id = 123
  AND status = PENDING
  AND region_id = 5;

-- Все три поля используют =
-- Индекс: (customer_id, status, region_id)
CREATE INDEX idx_orders_1
ON orders(customer_id, status, region_id);

Шаг 2: Добавить поле сортировки

-- Медленный запрос со сортировкой
SELECT * FROM orders
WHERE customer_id = 123
  AND status = PENDING
ORDER BY created_at DESC
LIMIT 10;

-- ESR порядок: (customer_id, status, created_at)
CREATE INDEX idx_orders_2
ON orders(customer_id, status, created_at DESC);

Шаг 3: Диапазон (WHERE col > value или col < value)

-- Медленный запрос с диапазоном
SELECT * FROM transactions
WHERE user_id = 456
  AND type = PAYMENT
  AND amount > 1000
ORDER BY date DESC;

-- ESR: равенство, сортировка, диапазон
-- (user_id, type, date, amount)
CREATE INDEX idx_transactions_1
ON transactions(user_id, type, date DESC, amount);

Практические примеры

Пример 1: E-commerce запрос

-- МЕДЛЕННЫЙ ЗАПРОС
SELECT product_id, name, price
FROM products
WHERE category_id = 10
  AND brand = Nike
  AND price BETWEEN 50 AND 200
ORDER BY created_at DESC
LIMIT 20;

Execution: 8.5 seconds, 500K rows examined

Анализ:

• WHERE category_id = 10 → Equality
• WHERE brand = Nike → Equality
• WHERE price BETWEEN 50 AND 200 → Range
• ORDER BY created_at DESC → Sort

Решение - создать индекс:

-- Вариант 1: ESR порядок
CREATE INDEX idx_products_esr
ON products(category_id, brand, created_at DESC, price);

-- Вариант 2: категория и бренд наиболее селективны
CREATE INDEX idx_products_v2
ON products(category_id, brand, price, created_at DESC);

After index:
Execution: 0.15 seconds, 200 rows examined ← 50x быстрее!

Пример 2: Запрос с JOIN

SELECT o.order_id, o.total, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = COMPLETED
  AND o.created_at >= 2024-01-01
ORDER BY o.total DESC
LIMIT 100;

-- МЕДЛЕННЫЙ
Execution: 5.2 seconds

Решение:

-- Индекс на таблице orders
CREATE INDEX idx_orders_esr
ON orders(status, created_at DESC, total DESC);

-- Убедись, что есть индекс на foreign key
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

-- Индекс на primary key customers (usually exists)
CREATE INDEX idx_customers_id
ON customers(id);

After indexes:
Execution: 0.08 seconds

Пример 3: Сложный запрос с несколькими условиями

-- МЕДЛЕННЫЙ ЗАПРОС
SELECT u.user_id, u.username, COUNT(*) as posts
FROM users u
LEFT JOIN posts p ON u.user_id = p.user_id
WHERE u.country = USA
  AND u.age >= 18
  AND u.status = ACTIVE
  AND p.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY u.user_id
ORDER BY posts DESC
LIMIT 50;

Execution: 12.3 seconds

Решение:

-- Индекс на users (Equality на status, country; Range на age)
CREATE INDEX idx_users_active
ON users(status, country, age);

-- Индекс на posts (foreign key и date)
CREATE INDEX idx_posts_user_date
ON posts(user_id, created_at DESC);

After indexes:
Execution: 0.25 seconds

Как определить, какие индексы создавать

Способ 1: EXPLAIN запроса

EXPLAIN SELECT * FROM users
WHERE status = ACTIVE
  AND created_at >= 2024-01-01
ORDER BY created_at DESC;

-- Результат:
id | select_type | table | key | rows | Extra
1  | SIMPLE      | users | NULL| 150K | Using where; Using filesort
                        ↑
                    key = NULL означает полный скан таблицы!
                    Using filesort = медленная сортировка

Создаем индекс:

CREATE INDEX idx_users_status_date
ON users(status, created_at DESC);

-- После индекса
EXPLAIN SELECT * FROM users
WHERE status = ACTIVE
  AND created_at >= 2024-01-01
ORDER BY created_at DESC;

-- Результат:
id | select_type | table | key                    | rows | Extra
1  | SIMPLE      | users | idx_users_status_date | 200  | Using index condition
                          ↑                        ↑
                      Индекс используется    Намного меньше рядов

Способ 2: ANALYZE информация

-- MySQL
ANALYZE TABLE products;

-- PostgreSQL
ANALYZE products;

-- Затем посмотри план запроса
EXPLAIN ANALYZE SELECT * FROM products WHERE price > 100;

Способ 3: Логирование медленных запросов

-- MySQL
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;  -- Запросы медленнее 2 сек

-- Смотри логи
TAIL /var/log/mysql/slow.log

-- Анализируй
mysqldumpslow /var/log/mysql/slow.log | head -20

Правило выбора полей для индекса

1. Начни с WHERE clause

SELECT * FROM products
WHERE category_id = 5     ← Добавь в индекс
  AND price > 100        ← Добавь в индекс
  AND color = red;     ← Добавь в индекс

-- Индекс начинается с этих полей
CREATE INDEX idx_products
ON products(category_id, color, price);

2. Добавь ORDER BY и GROUP BY

SELECT category_id, COUNT(*) 
FROM products
WHERE status = ACTIVE
GROUP BY category_id
ORDER BY COUNT(*) DESC;

-- Индекс: WHERE, потом GROUP BY
CREATE INDEX idx_products_group
ON products(status, category_id);

3. Рассмотри SELECT (covering index)

-- БЕЗ covering index
SELECT product_id, name, price
FROM products
WHERE category = Electronics;

-- Индекс только где clause
CREATE INDEX idx_products_cat
ON products(category);
-- Нужно прочитать основную таблицу

-- С covering index
CREATE INDEX idx_products_covering
ON products(category, product_id, name, price);
-- Полностью использует индекс (index only scan)

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

Правило 1: Selectively > Range > Order

-- Запрос
SELECT * FROM users
WHERE country = USA      -- Селективно (много)
  AND age >= 18           -- Диапазон
ORDER BY created_at;

-- НЕПРАВИЛЬНО
CREATE INDEX idx_wrong
ON users(age, country, created_at);  ← age первым = неправильно

-- ПРАВИЛЬНО
CREATE INDEX idx_correct
ON users(country, created_at, age);  ← country первым

Правило 2: DESC для ORDER BY DESC

SELECT * FROM orders
WHERE status = PENDING
ORDER BY created_at DESC;

-- НЕПРАВИЛЬНО (MySQL сортирует назад)
CREATE INDEX idx_wrong
ON orders(status, created_at);

-- ПРАВИЛЬНО (индекс DESC совпадает)
CREATE INDEX idx_correct
ON orders(status, created_at DESC);

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

-- MySQL
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE status = ACTIVE\G

-- Ищи в output:
"using_index": true
"key_length": 5  ← размер использованной части индекса
"rows": 100      ← сколько строк просмотрено (меньше = лучше)

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE status = ACTIVE;

Plan -> Index Scan using idx_users_status
Rows Removed by Filter: 0  ← 0 = хорошо

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

-- ❌ НЕ создавай индекс
CREATE INDEX idx_temp ON temp_table(column);
-- временные таблицы не нуждаются в индексах

-- ❌ НЕ создавай индекс
CREATE INDEX idx_bool ON table(is_active);
-- булевы поля имеют низкую селективность (2 значения)

-- ❌ НЕ создавай индекс
CREATE INDEX idx_uuid ON table(uuid);
-- если это primary key (уже проиндексирован)

-- ✅ Создавай индекс
CREATE INDEX idx_email ON users(email);
-- email имеет высокую селективность

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

-- Найди неиспользуемые индексы (MySQL)
SELECT * FROM sys.schema_unused_indexes;

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

-- Размер индексов
SELECT * FROM sys.schema_index_statistics
ORDER BY stat DESC;

Best Practices

1. Анализируй EXPLAIN перед созданием индекса
2. Следуй ESR правилу: Equality, Sort, Range
3. Начни с самых селективных полей
4. Избегай слишком много индексов (замедляет INSERT/UPDATE/DELETE)
5. Мониторь использование индексов регулярно
6. Удаляй неиспользуемые индексы
7. Профилируй на реальных данных

Пример полного анализа

-- ИСХОДНЫЙ МЕДЛЕННЫЙ ЗАПРОС
SELECT * FROM orders
WHERE customer_id = 123
  AND status = SHIPPED
  AND created_at >= 2024-01-01
ORDER BY total DESC;

-- Время выполнения: 6.8 секунд

-- АНАЛИЗ EXPLAIN
EXPLAIN SELECT ...;
-- Результат: Using where, Using filesort ← плохо

-- СОЗДАЕМ ИНДЕКС (ESR правило)
CREATE INDEX idx_orders_opt
ON orders(customer_id, status, total DESC, created_at);

-- ПРОВЕРЯЕМ
EXPLAIN SELECT ...;
-- Результат: Using index condition ← хорошо

-- Новое время: 0.08 секунд ← 85x быстрее!

Заключение

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

1. Используй EXPLAIN для анализа текущего плана
2. Следуй ESR правилу:
   • E = Equality (WHERE col = value)
   • S = Sort (ORDER BY)
   • R = Range (WHERE col > value)
3. Начни с наиболее селективных полей
4. Добавь поля из WHERE, ORDER BY, GROUP BY
5. Рассмотри covering index для полного использования индекса
6. Мониторь и удаляй неиспользуемые индексы
7. Профилируй на реальных данных и нагрузке

Правильный индекс может ускорить запрос на 10-100 раз!