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

Вопрос:

У меня есть 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

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

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