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

Является ли составной индекс неупорядоченным?

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

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

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

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

Является ли составной индекс неупорядоченным?

Ответ: Нет, составной индекс УПОРЯДОЧЕН, но порядок сортировки определяется ПОРЯДКОМ КОЛОНОК в определении индекса. Это критически важный момент для понимания производительности запросов в базах данных.

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

-- Составной (composite/compound) индекс
-- включает несколько колонок
CREATE INDEX idx_user_profile 
ON users(country, city, age);

-- Это НЕ то же самое, что три отдельных индекса!
CREATE INDEX idx_country ON users(country);
CREATE INDEX idx_city ON users(city);
CREATE INDEX idx_age ON users(age);

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

public class CompositeIndexOrdering {
    // Индекс (country, city, age) упорядочен так:
    // 
    // Сначала по country (первая колонка)
    // Потом по city (вторая колонка) внутри country
    // Потом по age (третья колонка) внутри city
    // 
    // На диске выглядит так:
    // 
    // country | city      | age | row_id
    // --------|-----------|-----|-------
    // USA     | New York  | 25  | 101
    // USA     | New York  | 30  | 102
    // USA     | Boston    | 22  | 103
    // USA     | Boston    | 35  | 104
    // Canada  | Toronto   | 28  | 105
    // Canada  | Montreal  | 31  | 106
    // UK      | London    | 29  | 107
    // 
    // Это УПОРЯДОЧЕНО по (country, city, age)
}

SQL примеры использования составного индекса

1. Query, который использует индекс полностью

-- Индекс: (country, city, age)

-- Эффективно: использует индекс для всех трех колонок
SELECT * FROM users 
WHERE country = 'USA' 
  AND city = 'New York' 
  AND age > 25;
-- Выполнение: найти в индексе USA → New York,
-- затем range scan по age

2. Query, который использует индекс частично

-- Индекс: (country, city, age)

-- Эффективно: использует индекс для первых двух колонок
SELECT * FROM users 
WHERE country = 'USA' 
  AND city = 'New York';
-- Индекс помогает до (city)
-- age не используется (он не важен для WHERE)

-- Хорошо: использует индекс для country
SELECT * FROM users 
WHERE country = 'USA';
-- Индекс помогает найти все USA строки

3. Query, который НЕ использует индекс оптимально

-- Индекс: (country, city, age)

-- Неэффективно: пропускает middle column (city)
SELECT * FROM users 
WHERE country = 'USA' 
  AND age > 25;
-- Индекс используется только для country
-- age условие не может использовать индекс
-- (потому что city в условии отсутствует)

-- Очень неэффективно: начинает со второй колонки
SELECT * FROM users 
WHERE city = 'New York';
-- Индекс НЕ используется вообще!
-- (leading column country отсутствует)

Java пример с ORM

public class CompositeIndexJPA {
    @Entity
    @Table(name = "users",
           indexes = {
               @Index(name = "idx_country_city_age",
                      columnList = "country, city, age")
           })
    public class User {
        @Id
        private Long id;
        
        @Column(name = "country")
        private String country;
        
        @Column(name = "city")
        private String city;
        
        @Column(name = "age")
        private Integer age;
    }
    
    // ХОРОШО: использует индекс полностью
    public List<User> findUsersInArea1(
            String country, String city, int minAge) {
        return entityManager.createQuery(
            "SELECT u FROM User u "
            + "WHERE u.country = :country "
            + "  AND u.city = :city "
            + "  AND u.age > :minAge",
            User.class)
            .setParameter("country", country)
            .setParameter("city", city)
            .setParameter("minAge", minAge)
            .getResultList();
    }
    
    // ХОРОШО: использует индекс для (country, city)
    public List<User> findUsersInCity(
            String country, String city) {
        return entityManager.createQuery(
            "SELECT u FROM User u "
            + "WHERE u.country = :country "
            + "  AND u.city = :city",
            User.class)
            .setParameter("country", country)
            .setParameter("city", city)
            .getResultList();
    }
    
    // ПЛОХО: не использует индекс
    public List<User> findUsersByAge(
            int minAge) {
        return entityManager.createQuery(
            "SELECT u FROM User u "
            + "WHERE u.age > :minAge",
            User.class)
            .setParameter("minAge", minAge)
            .getResultList();
    }
}

Правило Leading Column

public class LeadingColumnRule {
    // Индекс: (country, city, age)
    // 
    // Правило Leading Column (Leading Prefix):
    // Индекс может использоваться для запроса
    // только если WHERE условие содержит
    // leading columns без пропусков
    // 
    // ✅ Хорошо:
    // - country
    // - country, city
    // - country, city, age
    // 
    // ❌ Плохо (пропускает middle column):
    // - city
    // - age
    // - city, age
    // - country, age (пропускает city!)
    // 
    // Подумай: индекс отсортирован как
    // (country ASC) → (city ASC) → (age ASC)
    // Если в WHERE пропускаешь country,
    // ты не можешь использовать упорядочение
    // по city и age
}

Практический пример с EXPLAIN PLAN

-- Индекс: (country, city, age)

-- ✅ EXPLAIN показывает использование индекса
EXPLAIN 
SELECT * FROM users 
WHERE country = 'USA' 
  AND city = 'New York'
  AND age > 25;
-- Output:
-- Index: idx_country_city_age
-- Type: range
-- Key length: полная длина индекса
-- Rows: относительно мало

-- ❌ EXPLAIN показывает full table scan
EXPLAIN 
SELECT * FROM users 
WHERE age > 25;
-- Output:
-- Type: ALL (full table scan)
-- Rows: 1000000 (все строки!)

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

1. Equality columns first (Колонки с = первыми)

public class IndexColumnOrdering {
    // Плохо: вложил age раньше city
    // CREATE INDEX idx_bad ON users(age, city, country);
    // 
    // Хорошо: equality conditions first
    // CREATE INDEX idx_good ON users(country, city, age);
    // 
    // Логика: equality columns позволяют
    // быстро фильтровать, range columns — скан
    
    // Пример:
    // WHERE country = 'USA' (equality, ограничивает диапазон)
    //   AND city = 'Boston' (equality, еще ограничивает)
    //   AND age > 30 (range, скан внутри диапазона)
}

2. Порядок based на query patterns

-- Частые запросы:
-- Q1: WHERE country = ? AND city = ?
-- Q2: WHERE country = ?
-- Q3: WHERE country = ? AND city = ? AND age = ?

-- Лучший индекс:
CREATE INDEX idx_queries ON users(
    country,   -- используется во всех
    city,      -- используется в Q1 и Q3
    age        -- используется в Q3
);

-- Этот индекс поддерживает:
-- - Q1: точно (country, city)
-- - Q2: точно (country)
-- - Q3: точно (country, city, age)

3. Фильтрующие vs сортирующие колонки

-- Если нужна сортировка в результатах
CREATE INDEX idx_country_age_city ON users(
    country,   -- для фильтра WHERE
    age,       -- для фильтра WHERE + сортировка
    city       -- для ORDER BY
);

SELECT * FROM users
WHERE country = 'USA'
  AND age > 25
ORDER BY city;  -- может использовать индекс для сортировки

Различие между составным индексом и отдельными индексами

public class CompositeVsSeparate {
    // СОСТАВНОЙ ИНДЕКС
    // CREATE INDEX idx ON users(country, city);
    // 
    // Плюсы:
    // - Эффективен для queries (country, city)
    // - Может быть Index-Only scan
    // - Лучше для Range queries
    // - Меньше памяти, чем 2 индекса
    // 
    // Минусы:
    // - Не эффективен для query только по city
    // - Порядок колонок критичен
    
    // ДВА ОТДЕЛЬНЫХ ИНДЕКСА
    // CREATE INDEX idx1 ON users(country);
    // CREATE INDEX idx2 ON users(city);
    // 
    // Плюсы:
    // - Эффективен для любого условия
    // - Гибче при изменении queries
    // 
    // Минусы:
    // - Больше памяти
    // - Медленнее при обновлениях
    // - MySQL может использовать только один индекс
    //   для фильтрации (Index Merge очень медленно)
}

Специальные случаи

1. DESC сортировка в индексе

-- Можешь контролировать направление сортировки
CREATE INDEX idx_mixed ON users(
    country ASC,   -- возрастающий
    age DESC       -- убывающий
);

-- Это помогает для
SELECT * FROM users
WHERE country = 'USA'
ORDER BY age DESC;  -- может использовать индекс без sorting!

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

CREATE INDEX idx_active_users ON users(
    country, city
)
WHERE active = true;  -- индекс только для активных

-- Это экономит памяти и быстрее

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

public class BTreeStructure {
    // Составной индекс (country, city, age)
    // хранится как B-Tree, где:
    // 
    // 1. Root: все значения country
    // 2. Level 2: для каждого country — все city
    // 3. Leafs: для каждого (country, city) — все age + row_id
    // 
    // Это создает упорядоченную иерархическую структуру
    // которая позволяет быстро найти нужные строки
}

Практические рекомендации

public class BestPractices {
    // 1. Анализируй свои queries
    // Посмотри, какие WHERE условия чаще всего
    
    // 2. Leading column должна быть selective
    // (иметь много уникальных значений)
    
    // 3. Второе место: колонка из WHERE условий
    
    // 4. Последнее место: колонка для сортировки
    // SELECT * FROM users
    // WHERE country = ? AND city = ?
    // ORDER BY age;
    // → CREATE INDEX ON (country, city, age)
    
    // 5. Тестируй с EXPLAIN / ANALYZE
    // Убедись, что индекс используется
    
    // 6. Избегай слишком много индексов
    // каждый индекс замедляет INSERT/UPDATE/DELETE
    
    // 7. Пересматривай индексы регулярно
    // query patterns меняются со временем
}

Вывод

Составной индекс УПОРЯДОЧЕН — первая колонка упорядочена по всему диапазону, вторая колонка упорядочена внутри каждого значения первой колонки, и так далее. Порядок колонок в определении индекса критичен для эффективности запросов:

  1. Используй Leading Column Rule
  2. Помещай equality conditions перед range conditions
  3. Тестируй с EXPLAIN PLAN
  4. Выбирай порядок на основе actual query patterns

Это один из самых важных аспектов оптимизации баз данных.

Является ли составной индекс неупорядоченным? | PrepBro