Ключ или отмычка - Почему выбирают суррогатные ключи
Связи между таблицами на основе суррогатных ключей
Эффективность связей между отношениями

Когда говорят об эффективности связей основанной на суррогатных ключах, то подразумевают два основных момента. Первый связан с тем, что суррогатные ключи компактны. Второй момент состоит в том, что суррогатный ключ, как правило, содержит числовую информацию, которую процессоры обрабатывают наиболее эффективно.

В отличие от суррогатных ключей, интеллектуальные ключи имеют тот размер, который они должны иметь. Размер интеллектуального ключа определяется предметной областью, но не возможностями того или иного процессора. Если в какой-то таблице используется атрибут кода страны, то в случае интеллектуального ключа он будет 2-х или 3-х символьным, в то время как суррогатный ключ будет определяться СУБД и возможностями конкретного процессора, и он сегодня может быть 32 бита или 64 бита. В этом частном случае интеллектуальный ключ может оказаться меньше, чем суррогатный ключ. В другом случае интеллектуальный ключ может состоять из нескольких строковых атрибутов, каждый из которых будет, к примеру, более десятка символов. Соответственно размер интеллектуального ключа окажется намного больше, чем размер суррогатного ключа. 

Тем не менее, использование интеллектуальных ключей часто ограничивают, мотивируя такое решение тем, что их размер, как правило, больше, чем размер суррогатного ключа. Для значительного количества задач такое оправдание выглядит не очень убедительно. Действительно, можно рассмотреть такие наиболее распространённые задачи, как автоматизация бухгалтерии, отдела кадров, складской учёт, учёт оборудования и т.п., и убедиться в том, что здесь интеллектуальные ключи основных сущностей имеют формат целого числа. Так, номер счетов в бухгалтерии типичное целое число; табельный номер служащего, также представим в виде целого числа, как, впрочем, и номер договора со служащим; номенклатурные номера товаров и инвентарные номера оборудования тоже представимы целыми числами. Таким образом, заявления о том, что интеллектуальные ключи обязательно имеют больший размер, а, следовательно, работа с ними происходит медленнее, не всегда соответствуют действительности. Тем более странными кажутся попытки наряду с простым и компактным интеллектуальным ключом поддерживать дополнительный суррогатный ключ, который не имеет какой-либо информационной ценности.

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

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

Возвращаясь к эффективности связей между отношениями, надо отметить, что не существует прямой зависимости между эффективностью и размером ключевых атрибутов. Можно вернуться к схемам баз данных представленных на рис. 3 и рис. 4. Для того чтобы получить исчерпывающую информацию о том, какими изделиями был укомплектован заказ с номером 777 в схеме, построенной на интеллектуальных ключах, достаточно выполнить простой запрос, не требующий соединения таблиц:

SELECT MODEL, SERIAL_NO FROM COMPLETE_SET WHERE ORD_NO = 777;

Для достижения той же информативности в схеме, построенной на суррогатных ключах, придётся написать гораздо более сложный запрос, требующий соединения всех таблиц схемы:

SELECT M.NAME, PR.SERIAL_NO FROM ORDERS O
    INNER JOIN POSITIONS P ON P.ORDER_ID = O.ID
    INNER JOIN COMPLETE_SET CS ON CS.POSITION_ID = P.ID
    INNER JOIN PRODUCTS PR ON PR.ID = CS.PRODUCTS_ID
    INNER JOIN MODELS M ON M.ID = PR.MODEL_ID
WHERE O.ORD_NO = 777;

Можно не сомневаться в том, что первый запрос будет выполнен гораздо быстрее, чем второй. Аналогичная ситуация будет наблюдаться и в случае запроса противоположного приведённому выше. Пусть требуется узнать номер заказа, по которому проходило конкретное изделие:

SELECT ORD_NO FROM COMPLETE_SET
WHERE MODEL = "Celeron 500" AND SERIAL_NO = "XXXYYYZZZ";

для схемы на интеллектуальных ключах. Для схемы на суррогатных ключах, опять нужно выполнить соединение всех таблиц:

SELECT O.ORD_NO FROM ORDERS O
    INNER JOIN POSITIONS P ON P.ORDER_ID = O.ID
    INNER JOIN COMPLETE_SET CS ON CS.POSITION_ID = P.ID
    INNER JOIN PRODUCTS PR ON PR.ID = CS.PRODUCTS_ID
    INNER JOIN MODELS M ON M.ID = PR.MODEL_ID
WHERE M.NAME = "Celeron 500" AND PR.SERIAL_NO = "XXXYYYZZZ";

Потребность в очень большом числе слияний при использовании суррогатных ключей объяснима, она связана с отсутствием какой-либо полезной информации во внешних ключах.

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

При исполнении запроса к схеме базы данных, построенной на суррогатных ключах, действия будут иные. Сначала при помощи уникального индекса будет найдена требуемая модель в таблице моделей. Далее произойдёт загрузка в память нужного кортежа этой таблицы. Из этого кортежа будет извлечён идентификатор модели. Теперь необходимо по найденному идентификатору и заданному серийному номеру выполнить поиск в уникальном индексе, который был создан на атрибутах «Model_ID» и «Serial_No». Кортеж, соответствующий критерию поиска подгружается в память и из него извлекается значение атрибута «Product_ID». По этому атрибуту в индексе, образованном внешним ключом таблицы комплектующих, надо найти все соответствующие кортежи и загрузить их в память. Из этих кортежей извлекаются значения атрибутов позиций заказов. По выбранным атрибутам с помощью уникального индекса, образованного первичным ключом таблицы позиций, выбираются кортежи, из которых получают идентификаторы заказов. И вот, наконец, можно перейти к таблице заказов и узнать номера заказов, по которым проходило заданное изделие.

Обычно, для ускорения операций объединения используются кластерные индексы, то есть информация в таблице упорядочивается по значению первичного ключа. Имеет смысл отметить, что запросы, поступающие от пользователей, никогда не задают поиск информации по атрибуту, образующему, суррогатный ключ, по той простой причине, что значения этого атрибута неизвестны пользователю. Таким образом, эффективность поиска по сравнению с использованием естественных ключей снижается, так как СУБД сначала должна найти по вторичному индексу значение кластерного индекса, а затем уже по этому значению сам требуемый кортеж. Если использовать в качестве кластерного индекса вторичный индекс, то снижается эффективность объединений таблиц.

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