Як обрати первинний ключ для таблиці InnoDB?

Question

Accepted Answer

В InnoDB первинний ключ - не просто унікальний ідентифікатор, а порядок фізичного зберігання рядків (кластерний індекс) і частина кожного вторинного індексу. Тому вибір ключа впливає на продуктивність усієї таблиці.
Вимоги до хорошого первинного ключа:

короткий - він копіюється в кожен вторинний індекс;
незмінний - зміна ключа означає переміщення рядка в дереві й оновлення всіх індексів;
монотонно зростаючий - нові рядки дописуються в кінець дерева, сторінки заповнюються щільно;
завжди заданий (NOT NULL).

Варіант за замовчуванням - автоінкремент:
$table->id();   // BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY

8 байтів, зростає, нічого не коштує. BIGINT замість INT - щоб не впертися в ~4,29 млрд (пропуски в нумерації з'їдають запас швидше, ніж здається).
UUID - коли справді потрібен (генерація id на клієнті, злиття даних з різних баз, неперебірні публічні ідентифікатори):

UUIDv4 (випадковий) - найгірший варіант для InnoDB. Кожна вставка йде у випадкову сторінку: розщеплення сторінок, фрагментація, сторінки заповнені наполовину, буферний пул використовується неефективно;
UUIDv7 / ULID (впорядковані за часом) - вставляються майже послідовно, проблеми v4 зникають. Laravel HasUuids генерує саме впорядковані UUID;
зберігати компактно - BINARY(16), а не CHAR(36).

Компроміс: внутрішній BIGINT-ключ для зв'язків плюс окрема унікальна колонка uuid для зовнішнього світу (URL, API).
Природні ключі (email, номер телефону, код ІПН) - погана ідея: вони змінюються, бувають довгими, а зміна каскадом зачіпає всі зовнішні ключі. Унікальний індекс на них - так; первинний ключ - ні.
Складений первинний ключ доречний у таблицях зв'язку «багато-до-багатьох»:
Schema::create('role_user', function (Blueprint $table) {
    $table->foreignId('user_id')->constrained();
    $table->foreignId('role_id')->constrained();
    $table->primary(['user_id', 'role_id']);
});

Рядки одного користувача лежать поруч, а пошук «ролі користувача» читає сусідні записи.
Таблиця без первинного ключа отримає прихований внутрішній ключ, недоступний у запитах. До того ж Group Replication (InnoDB Cluster) та інструменти онлайн-змін схеми вимагають явного первинного ключа, а керовані хмарні MySQL часто вмикають sql_require_primary_key.
Докладніше в документації: Оптимізація первинного ключа

Як обрати первинний ключ для таблиці InnoDB?

Схожі питання