Особенности хранения InnoDB, влияние PRIMARY KEY, lookup по вторичному индексу
InnoDB хранит данные принципиально иначе, чем MyISAM. Понимание кластерного индекса — ключ к проектированию эффективных схем данных.
В InnoDB данные таблицы физически хранятся в порядке PRIMARY KEY. Это не отдельная структура — это и есть таблица:
CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL,
name VARCHAR(100)
);Структура кластерного индекса (B+Tree):
Поиск по PRIMARY KEY — один lookup, сразу получаем всю строку:
SELECT * FROM users WHERE id = 42;
-- Один поиск по кластерному индексу → вся строкаInnoDB использует следующий алгоритм:
-- Нет 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 явно.
Вторичный индекс в InnoDB в листовых узлах содержит значение PK, а не указатель на строку:
CREATE INDEX idx_email ON users(email);Структура вторичного индекса:
Lookup по вторичному индексу — ДВА шага:
SELECT * FROM users WHERE email = 'test@test.com';
-- Шаг 1: поиск по idx_email → находим id = 42
-- Шаг 2: поиск по кластерному индексу (PK) WHERE id = 42 → вся строкаЭто называется bookmark lookup. Каждый поиск по вторичному индексу — это ДВА поиска в B-Tree.
Поскольку КАЖДЫЙ вторичный индекс хранит копию 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 ГБ экономии.
Хорошие кандидаты:
-- 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.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.