Какой порядок полей сделаешь для составного ключа в SQL?

Порядок полей в составном ключе SQL: стратегия проектирования

При проектировании составного ключа (compound key) в SQL я руководствуюсь несколькими фундаментальными принципами, которые влияют на производительность, читаемость кода и поддерживаемость системы. Вот мой подход к определению порядка полей.

Ключевые критерии выбора порядка

1. Селективность (Cardinality)

Наиболее селективные поля должны идти первыми. Поле с высокой кардинальностью (большое количество уникальных значений) эффективнее сокращает пространство поиска.

-- ПЛОХО: сначала поле с низкой селективностью
CREATE TABLE orders (
    status VARCHAR(20),      -- низкая селективность (5-10 значений)
    user_id INT,             -- высокая селективность
    created_at DATETIME,
    PRIMARY KEY (status, user_id, created_at)
);

-- ХОРОШО: сначала поле с высокой селективностью
CREATE TABLE orders (
    user_id INT,
    created_at DATETIME,
    status VARCHAR(20),
    PRIMARY KEY (user_id, created_at, status)
);

2. Частота использования в WHERE

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

-- Если часто ищем по department_id и реже по employee_id
CREATE TABLE assignments (
    department_id INT,    -- частый фильтр в WHERE
    employee_id INT,      -- менее частый фильтр
    project_id INT,
    PRIMARY KEY (department_id, employee_id, project_id)
);

3. Порядок сортировки по умолчанию

Составной ключ определяет физический порядок хранения данных (в кластеризованных индексах). Если данные чаще запрашиваются в определенном порядке, это должно отражаться в ключе.

-- Если типичный запрос: WHERE user_id = X ORDER BY created_at DESC
CREATE TABLE user_actions (
    user_id INT,
    created_at DATETIME,  -- вторым для правильной сортировки
    action_type VARCHAR(50),
    PRIMARY KEY (user_id, created_at)
);

4. Принцип кратчайшего префикса

Порядок должен минимизировать длину ключа для наиболее частых запросов. Если запросы используют только первые N полей ключа, они всё равно смогут эффективно использовать индекс.

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

1. Определите самый частый сценарий запросов

   • Какие поля чаще всего в WHERE?
   • Какая типичная сортировка?

2. Проанализируйте селективность каждого поля

   • Рассчитайте отношение уникальных значений к общему количеству записей
   • Проверьте планы выполнения запросов

3. Учитывайте влияние на JOIN операции

   • Для внешних ключей порядок должен соответствовать родительской таблице

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

-- Ситуация: система бронирования отелей
CREATE TABLE bookings (
    -- hotel_id имеет среднюю селективность (сотни отелей)
    hotel_id INT,
    
    -- room_type низкая селективность (5-10 типов номеров)
    room_type VARCHAR(30),
    
    -- check_in_date высокая селективность
    check_in_date DATE,
    
    -- booking_id уникален, но уже есть отдельный PK
    booking_id INT UNIQUE,
    
    -- НЕПРАВИЛЬНО: room_type, hotel_id, check_in_date
    -- ПРАВИЛЬНО: check_in_date, hotel_id, room_type
    -- Почему: чаще ищут брони на конкретную дату,
    -- затем фильтруют по отелю, и только потом по типу номера
    
    PRIMARY KEY (check_in_date, hotel_id, room_type)
);

-- Создаем дополнительный индекс для альтернативных сценариев
CREATE INDEX idx_hotel_checkin ON bookings(hotel_id, check_in_date);

Исключения и особые случая

• Кластеризованные индексы в SQL Server: порядок определяет физическое расположение данных
• InnoDB в MySQL: первичный ключ определяет физический порядок
• Генерация серийных номеров: автоинкрементные поля обычно последние, если не нужны для частых диапазонных запросов

Оптимизация для конкретных СУБД

-- PostgreSQL: учитываем влияние на многоколоночные индексы
CREATE TABLE events (
    tenant_id INT,       -- для мультитенантных приложений
    event_date DATE,     -- частый диапазонный запрос
    event_type INT,      -- низкая селективность
    PRIMARY KEY (tenant_id, event_date, event_type)
);

-- SQL Server: кластеризованный индекс определяет порядок данных
CREATE TABLE documents (
    department_id INT,
    year INT,
    doc_number INT,
    PRIMARY KEY CLUSTERED (department_id, year, doc_number)
);

Золотое правило: всегда тестируйте производительность с реалистичными объемами данных и типичными запросами. Порядок, оптимальный для 10,000 записей, может быть неэффективным для 10,000,000. Используйте EXPLAIN ANALYZE в PostgreSQL или Execution Plan в SQL Server для проверки гипотез.

Правильный порядок полей в составном ключе сокращает время выполнения запросов на 50-90% в сложных системах, уменьшает размер индексов и улучшает масштабируемость приложения.