PrepBro
← Назад к вопросам

Почему важен порядок столбцов при создании составного индекса?

2.3 Middle🔥 71 комментариев
#Базы данных и SQL

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI23 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Ответ: Почему важен порядок столбцов при создании составного индекса

Краткий ответ

Порядок столбцов в составном (композитном) индексе критически важен, потому что база данных использует принцип "слева направо" (leftmost prefix rule). Это определяет, когда индекс будет использован, какие запросы будут быстрыми, а какие замедлены.

Что такое составной индекс?

Составной индекс — это индекс по нескольким столбцам одновременно:

-- Создаём составной индекс
CREATE INDEX idx_name_age_city ON users(name, age, city);

-- Это эквивалентно индексу по трём столбцам в ТАКОМ порядке
-- (name) → (name, age) → (name, age, city)

Правило "слева направо" (Leftmost Prefix Rule)

Датабаза может использовать индекс только если первый (левый) столбец указан в условии WHERE:

INDEX: idx_name_age_city ON (name, age, city)

-- ✅ ИСПОЛЬЗУЕТ ИНДЕКС (первый столбец name присутствует)
SELECT * FROM users WHERE name = 'John';

-- ✅ ИСПОЛЬЗУЕТ ИНДЕКС (name и age в правильном порядке)
SELECT * FROM users WHERE name = 'John' AND age = 30;

-- ✅ ИСПОЛЬЗУЕТ ИНДЕКС (все три столбца)
SELECT * FROM users WHERE name = 'John' AND age = 30 AND city = 'Moscow';

-- ❌ НЕ ИСПОЛЬЗУЕТ ИНДЕКС (name отсутствует, начинаем с age)
SELECT * FROM users WHERE age = 30;

-- ❌ НЕ ИСПОЛЬЗУЕТ ИНДЕКС (name и city, но age в середине)
SELECT * FROM users WHERE name = 'John' AND city = 'Moscow';

-- ⚠️ ЧАСТИЧНО ИСПОЛЬЗУЕТ ИНДЕКС (only на name)
SELECT * FROM users WHERE name = 'John' AND city = 'Moscow';

Визуальное объяснение индекса

ИНДЕКС: idx_name_age_city(name, age, city)

Структура B-tree:
                    [A-M]
                   /      \
              [A-G]         [M-Z]
              /    \        /    \
          [A-C] [D-G]  [M-R] [S-Z]

Когда ищем WHERE name = 'John':
1. name = 'John' → идём в ветку [J]
2. Находим всех Johns, но ещё отсортированы по age
3. Если добавляем AND age = 30 → используем age как вторичный порядок
4. Если добавляем AND city = 'Moscow' → используем city как третичный порядок

Когда ищем WHERE age = 30 (без name):
❌ Не можем использовать индекс!
   Потому что индекс СНАЧАЛА отсортирован по name,
   а потом по age. Без name фильтра индекс бесполезен.

Пример: Почему порядок имеет значение

-- Таблица users с данными
users:
id | name    | age | city
1  | Alice   | 25  | Moscow
2  | Bob     | 30  | SPB
3  | Charlie | 25  | Ekb
4  | David   | 30  | Moscow
5  | Eve     | 35  | SPB

-- Вариант 1: Индекс по (name, age, city)
CREATE INDEX idx_v1 ON users(name, age, city);

-- Быстрые запросы (используют индекс)
SELECT * FROM users WHERE name = 'Alice';  -- ✅ O(log n)
SELECT * FROM users WHERE name = 'Alice' AND age = 25;  -- ✅ O(log n)
SELECT * FROM users WHERE name = 'Alice' AND age = 25 AND city = 'Moscow';  -- ✅ O(log n)

-- Медленные запросы (НЕ используют индекс)
SELECT * FROM users WHERE age = 25;  -- ❌ O(n) - Full Table Scan
SELECT * FROM users WHERE city = 'Moscow';  -- ❌ O(n) - Full Table Scan

-- Вариант 2: Индекс по (age, city, name) - ДРУГОЙ ПОРЯДОК
CREATE INDEX idx_v2 ON users(age, city, name);

-- Быстрые запросы
SELECT * FROM users WHERE age = 25;  -- ✅ O(log n)
SELECT * FROM users WHERE age = 25 AND city = 'Moscow';  -- ✅ O(log n)
SELECT * FROM users WHERE age = 25 AND city = 'Moscow' AND name = 'Alice';  -- ✅ O(log n)

-- Медленные запросы
SELECT * FROM users WHERE name = 'Alice';  -- ❌ O(n) - Full Table Scan
SELECT * FROM users WHERE city = 'Moscow';  -- ❌ O(n) - Full Table Scan

B-Tree структура индекса

ИНДЕКС (name, age, city):

Сортировка СНАЧАЛА по name, ПОТОМ по age, ПОТОМ по city:

Alice(25, Moscow)
Alice(25, SPB)
Alice(30, Ekb)
Bob(25, Moscow)
Bob(30, SPB)
Charlie(25, Ekb)
...

Для WHERE age = 30 (без name):
Мы не можем перейти по индексу, потому что
age НЕ является первым столбцом!
Приходится сканировать ВСЕ записи.

Для WHERE name = 'Alice':
Мы сразу переходим в индексе к Alice,
без сканирования остальных имён.

Практический пример на Java/Spring

@Entity
public class User {
    @Id
    private Long id;
    
    private String name;
    private int age;
    private String city;
}

// ❌ НЕПРАВИЛЬНЫЙ ПОРЯДОК
@Table(name = "users", 
       indexes = @Index(name = "idx_wrong", 
                       columnList = "age, name, city"))
public class UserWrong { ... }

// ✅ ПРАВИЛЬНЫЙ ПОРЯДОК (в зависимости от запросов)
@Table(name = "users", 
       indexes = @Index(name = "idx_correct", 
                       columnList = "name, age, city"))
public class UserCorrect { ... }

// Если часто ищем по age:
@Table(name = "users", 
       indexes = @Index(name = "idx_age_first", 
                       columnList = "age, name, city"))
public class UserAgeFirst { ... }

Как выбрать правильный порядок?

Правило 1: ESR (Equality, Sort, Range)

-- Порядок индекса должен быть:
-- 1. Equality (равенство) условия в WHERE
-- 2. Sort (сортировка) в ORDER BY
-- 3. Range (диапазон) условия в WHERE

-- Пример запроса:
SELECT * FROM users 
WHERE city = 'Moscow'         -- Equality
AND age > 25                  -- Range
ORDER BY created_date;        -- Sort

-- Правильный порядок индекса:
INDEX idx_esr ON users(
    city,               -- Equality
    created_date,       -- Sort
    age                 -- Range
)

Пример: E-S-R в действии

Запрос:
SELECT * FROM orders 
WHERE customer_id = 123         -- Equality
AND order_date >= '2024-01-01'  -- Range
ORDER BY created_at DESC;       -- Sort

-- ✅ ОПТИМАЛЬНЫЙ индекс
INDEX idx_optimal ON orders(
    customer_id,     -- Equality (сначала отфильтруем)
    created_at,      -- Sort (потом отсортируем)
    order_date       -- Range (последний)
)

-- ❌ НЕОПТИМАЛЬНЫЙ индекс
INDEX idx_bad ON orders(
    order_date,      -- Начинаем с range?
    customer_id,     -- Equality в середине?
    created_at       -- Sort в конце?
)

Пример: Частые запросы

-- Таблица orders
TABLE orders (user_id, product_id, status, amount)

-- Частый запрос 1: фильтр по user_id и status
SELECT * FROM orders WHERE user_id = 1 AND status = 'completed';

-- Частый запрос 2: фильтр по product_id
SELECT * FROM orders WHERE product_id = 5;

-- Частый запрос 3: сортировка по amount для пользователя
SELECT * FROM orders WHERE user_id = 1 ORDER BY amount DESC;

-- ✅ ЛУЧШИЙ индекс (покрывает запросы 1 и 3)
INDEX idx_user_status_amount ON orders(user_id, status, amount);

-- ❌ ХУДШИЙ индекс (покрывает только запрос 2)
INDEX idx_product ON orders(product_id);

Таблица производительности

ИНДЕКС: idx_name_age_city ON (name, age, city)

Запрос                                    | Использует ли индекс | Эффективность
─────────────────────────────────────────────────────────────────────────────
WHERE name = 'John'                       | ✅ Да (префикс)      | O(log n)
WHERE name = 'John' AND age = 30          | ✅ Да (префикс)      | O(log n)
WHERE name = 'John' AND city = 'Moscow'   | ⚠️  Частично        | O(log n) + фильтр
WHERE age = 30                            | ❌ Нет              | O(n)
WHERE city = 'Moscow'                     | ❌ Нет              | O(n)
WHERE name LIKE 'J%'                      | ✅ Да               | O(log n)
WHERE name LIKE '%ohn'                    | ❌ Нет              | O(n)

Реальный пример: Создание индексов

-- Таблица пользователей
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255),
    age INT,
    country VARCHAR(100),
    created_at TIMESTAMP,
    status VARCHAR(50)
);

-- Анализируем частые запросы приложения:

-- Запрос 1: Поиск по email
SELECT * FROM users WHERE email = ?;
-- INDEX: (email)

-- Запрос 2: Поиск по username и country
SELECT * FROM users WHERE username = ? AND country = ?;
-- INDEX: (username, country)

-- Запрос 3: Фильтр по статусу и сортировка по дате
SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC;
-- INDEX: (status, created_at)

-- Запрос 4: Полнотекстовый поиск по username
SELECT * FROM users WHERE username LIKE ?;
-- INDEX: (username) — для префиксного поиска

-- Итоговые индексы для приложения:
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_username_country ON users(username, country);
CREATE INDEX idx_status_created ON users(status, created_at DESC);

Проблема: слишком много индексов

-- Каждый индекс занимает место и замедляет INSERT/UPDATE/DELETE
-- Неправильно создавать индекс на каждый столбец

-- ❌ ПЛОХО
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_city ON users(city);
-- 3 индекса, каждый занимает память

-- ✅ ЛУЧШЕ
CREATE INDEX idx_name_age_city ON users(name, age, city);
-- 1 составной индекс, покрывает все три столбца

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

-- PostgreSQL: EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT * FROM users WHERE name = 'John';

-- Output покажет "Index Scan" если используется индекс
-- или "Seq Scan" если полное сканирование

-- MySQL: EXPLAIN
EXPLAIN
SELECT * FROM users WHERE name = 'John';

-- Ищите колонку "type" со значением "ref" или "range"
-- "ALL" означает полное сканирование

Ключевые выводы

  1. Порядок столбцов определяет эффективность — первый столбец критичен
  2. Leftmost prefix rule — индекс работает слева направо
  3. ESR правило — Equality → Sort → Range
  4. Анализируйте запросы — создавайте индексы под реальные запросы
  5. Не перегружайте индексами — каждый индекс имеет стоимость
  6. Тестируйте с EXPLAIN — проверяйте что индекс используется

Итог

Порядок столбцов в составном индексе критически важен потому что:

  • Определяет, какие запросы будут быстрыми (O(log n))
  • Определяет, какие запросы будут медленными (O(n))
  • Влияет на расход памяти
  • Влияет на производительность INSERT/UPDATE/DELETE

Примите решение о порядке на основе анализа реальных запросов вашего приложения.

Почему важен порядок столбцов при создании составного индекса? | PrepBro