Естественный ключ против суррогатного ключа

Вопрос:

У меня есть 2 таблицы:

Product
id INT
name VARCHAR(64)
something TEXT
else INT
entirely BOOL

и

Ingredient
id INT
name VARCHAR(64)
description TEXT

Теперь у меня также есть таблица ссылок

Products_Ingredients
product_id INT
ingredient_id INT

для моего многого отношения.

Теперь оба продукта и ингредиенты будут иметь уникальные имена. Поэтому я могу использовать имена как естественные ключи... однако это будет хорошая идея?

Скажем, у меня есть продукт: Paint Thinner Supreme с ингредиентом: Butylonitrotetrocycline

Будет ли это хорошей идеей использовать эти имена в качестве составного ключа в таблице ссылок?

Насколько я понимаю идею использования естественных ключей над суррогатами, я не могу перестать думать, что использование простых целых чисел в качестве первичных ключей (и иностранных) будет намного быстрее. Будет ли разница в том, как сервер MySQL переваривает эти разные ключи?

Каково ваше мнение?

Ответ 1

Мнения не имеют значения, когда вы можете измерить.

Я реализовал это на PostgreSQL, используя как естественные ключи, так и суррогаты. Я использовал 300 000 продуктов в общей сложности, 180 ингредиентов и заполнил две таблицы "ингредиентов" с 3-17 ингредиентами на продукт, для 100 000 случайно выбранных продуктов (1053462 строки).

Выбор всех ингредиентов для одного продукта с использованием натуральных ключей, возвращаемых в 0.067 мс. Использование суррогатов, 0.199мс.

Возврат всех столбцов non-id для одного продукта с использованием натуральных ключей, возвращаемых в 0.145 ms. Использование суррогатов, 0.222 мс

Таким образом, естественные ключи в этом наборе данных были примерно в 2 - 3 раза быстрее.

Натуральные ключи не требуют каких-либо объединений для возврата этих данных. Суррогатные ключи требуют двух соединений.

Фактическая разница в производительности зависит от ширины ваших таблиц, количества строк, размера страницы и длины имен и тому подобного. Там будет точка, где суррогатные ключи начинают превосходить естественные ключи, но мало кто пытается это измерить.

Когда я занимался разработкой базы данных для рабочей базы моего работодателя, я построил стенд с таблицами, разработанными вокруг натуральных клавиш и таблицами, созданными вокруг номеров идентификаторов. Обе эти схемы содержат более 13 миллионов строк сэмплированных данных, генерируемых компьютером. В некоторых случаях запросы по схеме номера номера превосходили схему естественных ключей на 50%. (Таким образом, сложный запрос, который занимал 20 секунд с номерами идентификаторов, занял 30 секунд с естественными ключами.) Но 80% тестовых запросов имели более высокую производительность SELECT против схемы естественных ключей. И иногда это было ошеломляюще быстрее - разница в 30 к 1.

Мы ожидаем, что естественные ключи будут превосходить суррогаты в нашей базе данных на долгие годы. (Если мы не переместим некоторые таблицы на SSD, в этом случае естественные ключи, вероятно, превзойдут суррогаты навсегда.)

Ответ 2

В этом случае я бы предпочел суррогатные ключи, потому что

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