Кластерные и вторичные индексы InnoDB

InnoDB хранит данные принципиально иначе, чем MyISAM. Понимание кластерного индекса — ключ к проектированию эффективных схем данных.

#Кластерный индекс: данные = индекс

В InnoDB данные таблицы физически хранятся в порядке PRIMARY KEY. Это не отдельная структура — это и есть таблица:

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(100)
);

Структура кластерного индекса (B+Tree):

Внутренние узлы: ключи (id) для навигации
Листовые узлы: ПОЛНЫЕ строки (id, email, name)

Поиск по PRIMARY KEY — один lookup, сразу получаем всю строку:

SELECT * FROM users WHERE id = 42;
-- Один поиск по кластерному индексу → вся строка

#Если нет PRIMARY KEY

InnoDB использует следующий алгоритм:

PRIMARY KEY — если определён, используется как кластерный индекс
Первый UNIQUE NOT NULL индекс — если PK нет
Скрытый 6-байтный ROWID — если нет ни того, ни другого

-- Нет PRIMARY KEY, но есть UNIQUE NOT NULL
CREATE TABLE sessions (
    token VARCHAR(64) UNIQUE NOT NULL,
    user_id INT,
    expires_at DATETIME
);
-- token становится кластерным индексом

-- Нет ни PK, ни UNIQUE NOT NULL
CREATE TABLE logs (
    message TEXT,
    level VARCHAR(10),
    created_at DATETIME
);
-- InnoDB создаёт скрытый 6-байтный ROWID

Скрытый ROWID не виден в SQL — его нельзя использовать в запросах. Таблица без явного PK — это проблема для репликации, ORM и производительности.

Правило: всегда определяйте PRIMARY KEY явно.

#Вторичные индексы: двойной lookup

Вторичный индекс в InnoDB в листовых узлах содержит значение PK, а не указатель на строку:

CREATE INDEX idx_email ON users(email);

Структура вторичного индекса:

Внутренние узлы: ключи (email) для навигации
Листовые узлы: email + значение PK (id)

Lookup по вторичному индексу — ДВА шага:

SELECT * FROM users WHERE email = 'test@test.com';
-- Шаг 1: поиск по idx_email → находим id = 42
-- Шаг 2: поиск по кластерному индексу (PK) WHERE id = 42 → вся строка

Это называется bookmark lookup. Каждый поиск по вторичному индексу — это ДВА поиска в B-Tree.

#Влияние размера PRIMARY KEY

Поскольку КАЖДЫЙ вторичный индекс хранит копию PK, размер PK критически важен:

-- Плохо: большой PK
CREATE TABLE products (
    sku VARCHAR(64) PRIMARY KEY,  -- 64 байта × каждый вторичный индекс
    name VARCHAR(255),
    category_id INT,
    INDEX idx_category (category_id)  
    -- idx_category хранит: category_id + sku(64 байта)
);

-- Хорошо: компактный PK
CREATE TABLE products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- 8 байт
    sku VARCHAR(64) UNIQUE,
    name VARCHAR(255),
    category_id INT,
    INDEX idx_category (category_id)
    -- idx_category хранит: category_id + id(8 байт)
);

Разница: каждый entry во вторичном индексе меньше на 56 байт. Для таблицы с 10 млн строк и 5 вторичными индексами: 10M × 5 × 56 = ~2.8 ГБ экономии.

#Выбор PRIMARY KEY

Хорошие кандидаты:

-- AUTO_INCREMENT INT/BIGINT — оптимальный выбор
id BIGINT AUTO_INCREMENT PRIMARY KEY

-- UUID v7 (сортируемый) — хороший компромисс для распределённых систем
id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID7()))

Плохие кандидаты:

-- UUID v4 (случайный) — вставки по всему дереву, частые split
id CHAR(36) PRIMARY KEY DEFAULT (UUID())

-- Составной из нескольких столбцов — большой, все вторичные индексы раздуваются
PRIMARY KEY (tenant_id, entity_type, entity_id)

-- Строковый — медленное сравнение, большой размер
PRIMARY KEY (email)

#Порядок строк и диапазонные запросы

Поскольку данные отсортированы по PK, диапазонные запросы по PK очень эффективны:

-- Последовательное чтение — очень быстро
SELECT * FROM orders WHERE id BETWEEN 1000000 AND 1000100;

Но для любых ДРУГИХ столбцов данные НЕ упорядочены:

-- Данные не отсортированы по created_at — может быть filesort
SELECT * FROM orders WHERE created_at > '2024-01-01' ORDER BY created_at;

Если частые запросы идут по диапазону другого столбца — рассмотрите его как кандидат для PK (если он уникален и монотонно растёт).

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

PRIMARY KEY: BIGINT AUTO_INCREMENT по умолчанию. UUID v7 только если нужна глобальная уникальность (распределённые системы).

Минимизируйте размер PK. INT вместо BIGINT, если < 4 млрд строк. Избегайте строковых PK.

Помните про двойной lookup. Запрос по вторичному индексу = 2 поиска. Покрывающий индекс устраняет второй lookup.

Всегда определяйте PK. Таблица без PK — скрытый ROWID, проблемы с репликацией и ORM.