Про суррогатные ключи

Если выполнить SQL-запрос:

  1. SELECT id FROM <таблица>

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

Многие мои знакомые программисты добавляют во все создаваемые ими таблицы столбец с автоматически генерируемыми монотонно возрастающими целыми числами. Но они не просто добавляют этот столбец, они делают его первичным ключом. Когда я пытаюсь выяснить, зачем это нужно, я очень редко слышу в ответ какие-то разумные аргументы. Большинство ответов сводится к «все так делают» или «просто привык». Как вы знаете, привычки бывают полезные (например, чистить зубы по утрам и вечерам) и вредные (например, курить вместо того, чтобы работать). В этой записи я постараюсь показать, что привычка добавлять автоинкрементный столбец и делать его первичным ключом несомненно вредная.

Для начала, давайте подумаем, зачем вообще нужны первичные ключи. Распространённая, хоть и неправильная, точка зрения состоит в том, что первичный ключ позволяет однозначно идентифицировать строку в таблице. Люди, которые так отвечают, путают причину и следствие: на самом деле первичные ключи нужны для того, чтобы гарантировать отсутствие одинаковых строк. Когда это требование уже выполнено, можно утверждать, что первичный ключ однозначно определяет конкретную строку в таблице. Если быть чуть более строгим, то однозначно указывать на строку может как первичный ключ, так и подмножество столбцов первичного ключа, и даже столбцы, которые в первичный ключ не входят (такое часто наблюдается, в ситуациях, когда программисты бездумно создают суррогатные ключи) — всё зависит от того, как спроектирована таблица.

Но давайте вернёмся к основной цели нашего разговора. Мы уже выяснили, что первичный ключ нужен, чтобы гарантировать отсутсвие повторяющихся строк. Это такой способ «усилить семантику». Сознательно добавляя столбец с гарантированно уникальным значением, вы ничего нового в плане семантики не добавляете, а только даёте себе ложную уверенность в том, что таблица хорошо спроектирована. На деле вы можете создать сколько угодно строк, которые будут совпадать во всех столбцах, кроме столбца с суррогатным ключом. Описывать проблемы, к которым это может привести, я не буду, уверен, вы и сами понимаете, почему такие таблицы — очень плохая идея.

На этом моменте многие могут возразить, что «естественный» первичный ключ может содержать несколько столбцов и таскать его из родительской таблицы в дочернюю — плохая идея. Я тут одновременно и не согласен (т.к. на идеологическом уровне в этом нет ничего плохого) и согласен (т.к. понимаю, что это влечёт накладные расходы). Я пока не седовласый теоретик, поэтому в таких ситуациях советую использовать суррогатный ключ, но обязательно накладывать на потенциальный ключ ограничение уникальности. Т.о. мы одновременно защищаемся от появления в таблице семантически одинаковых строк, и получаем возможность передавать в процедуры, запросы и просто дочерние таблицы одиночное целое значение.

Безусловно, есть и другие ситуации, когда суррогатные ключи нужны. Например, в организациях есть табельные номера сотрудников — идеальный пример для использования автоинкрементных уникальных значений. Да что там говорить, каждый из вас сможет придумать множество ситуаций, где суррогатные ключи нужны. Но, прошу, не надо впихивать их в каждую таблицу!

P.S. Если вы всё-таки не можете отказаться от суррогатных ключей, то хотя бы начните делать их GUIDами.

Про суррогатные ключи: 2 комментария

  1. yandex.ru Миша 1

    Хз, нужно разбираться в мотивациях идти против устоявшихся техник.

    Запахивание во все таблицы зачастую продиктовано надстройками над ORM, а где-нибудь и самим ORM для унификации генерации, например.
    Чтобы в эту часть не лазили прикладные программисты и не пытались столь осмысленно к этому подходить без должной квалификации.

  2. vk.com Алексей Свитин

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

    Вопрос холиварный и есть поклонники у каждого подхода.
    Вначале отмечу самую важную поднятую проблему — очень часто забывают что на всех неэкзотических таблицах должен быть натуральный ключ и суррогатник не является заменой натурального ключа. Это просто бич проектирования.
    Спасибо, что поднимаете эту проблему. Надеюсь, таких ошибок будет меньше.
    (и даже тут есть спорные «экзотические» случаи, например можно удалить натуральный ключ с таблицы логов, стейджинг таблиц, если это улучшит производительность и мы можем жертвовать качеством данных для конкретного случая).

    Тем не менее, я не соглашусь, что в суррогатниках нет толка.
    Это великолепный инструмент для:
    1) повышения производительности
    1.1 Чем короче ключ,( сравните SMALLINT и GUID) тем меньше места потребует таблица, тем меньше индекс первичного ключа, тем быстрее работаю FK констрейнты, тем меньше места требуется во всех связанных через FK таблицах(это очень важно и это часто упускают), тем быстрее работают индексы поверх FK констрейнтов других таблиц( и про необходимость индексов поверх FK часто забывают, хотя тут тоже есть экзотика) и главное быстрее работают джойны.
    Снова, замечу, что все относительно. Для маленьких проектов с крошечными таблицами проще использовать GUID. Там вряд ли перечисленные экономии можно будет ощутить.
    А вот когда потребуется оптимизировать терабайтные БД, то тут не грех вспомнить про суррогатники.
    1.2 Для последовательного увеличения значения ключа распределения данных( в случае MS SQL кластерного индекса). Такие увеличения сокращают шанс фрагментации( в MS SQL).
    2) Спрятать сложный натуральный ключ за коротким значением.
    Это упрощает как проектирование, так понимание модели, и так же влияет на производительность.
    3) Открывает дорогу к интересным методологиям проектирования, например Data Vault или Conformed dimensions.

    Надеюсь, мой комментарий оправдает суррогатники в ваших глазах. :)

    Спасибо за статьи и удачи!

Добавить комментарий