Природничі ключі проти штучних ключів, Різне, Програмування, статті

Тенцер А. Л.

Дана стаття викладає погляд автора на проблему, регулярно обговорюються в групах новин, присвячених розробці додатків з використанням РСУБД.

Про сутність проблеми

Кожен запис у таблиці, що входить в РСУБД, повинна мати первинний ключ (ПК) – Набір атрибутів, унікально ідентифікує її в таблиці. Випадок, коли таблиця не має первинного ключа, має право на існування, проте в даній статті не розглядається.

В якості первинного ключа може використовуватися –

Природний Ключ (ЄК) – Набір атрибутів описуваної записом суті, унікально її ідентифікує (наприклад, номер паспорта для людини);
або
Сурогатний Ключ (СК) – Автоматично згенероване поле, ніяк не пов’язане з інформаційним змістом записи. Зазвичай в ролі СК виступає автоінкрементне поле типу INTEGER.

Є дві думки:

Природно, можна уявити собі і якесь проміжне думку, але зараз дискусія ведеться в рамках двох вищевикладених.

Коли з’являються СК

Для розуміння місця і значення СК розглянемо етап проектування, на якому вони вводяться в структуру БД, і методику їх введення.

Для ясності розглянемо БД з 2-х відносин – Міста (City) І Люди (People) Припускаємо, що місто характеризується Назва (Name), Всі міста мають різні назви, людина характеризується Прізвищем (Family), Номером паспорта (Passport) Та містом проживання (City). Також вважаємо, що кожна людина має унікальний номер паспорта. Hа етапі складання инфологической моделі БД її структура однакова і для ЄК і для СК.

CREATE TABLE City(
 Name VARCHAR(30) NOT NULL PRIMARY KEY
);
CREATE TABLE People(
 Passport CHAR(9) NOT NULL PRIMARY KEY,
 Family VARCHAR(20) NOT NULL,
 City VARCHAR(30) NOT NULL REFERENCES City(Name)
);

Для ЄК все готово. Для СК робимо ще один етап і перетворимо таблиці наступним чином:

CREATE TABLE City(
/* У різних діалектах мови SQL автоінкрементне поле буде виражено по-різному - наприклад, через IDENTITY, SEQUENCE або GENERATOR. Тут ми використовуємо умовне позначення AUTOINCREMENT.
*/
 Id INT NOT NULL AUTOINCREMENT PRIMARY KEY
 Name VARCHAR(30) NOT NULL UNIQUE
);
CREATE TABLE People(
 Id INT NOT NULL AUTOINCREMENT PRIMARY KEY,
 Passport CHAR(9) NOT NULL UNIQUE,
 Family VARCHAR(20) NOT NULL,
 CityId INT NOT NULL REFERENCES City(Id)
);

Звертаю увагу, що:

У загальному випадку алгоритм додавання СК виглядає наступним чином:

  1. У таблицю додається поле INTEGER AUTOINCREMENT;
  2. Воно оголошується PRIMARY KEY;
  3. Старий PRIMARY KEY (ЄК) Замінюється на UNIQUE
    CONSTRAINT
    ;
  4. Якщо в таблиці є REFERENCES на інші таблиці, то поля, що входять в REFERENCES, Замінюються на одне поле типу INTEGER, Що становить первинний ключ (як People.City замінена на People.CityId).

Це механічна операція, яка ніяк не порушує инфологической моделі і цілісності даних. З точки зору инфологической моделі ці дві бази даних
еквівалентні.

Навіщо все це треба

Виникає резонне питання – а навіщо? Дійсно, вводити в таблиці якісь поля, щось замінювати, навіщо? Отже, що ми отримуємо, виконавши цю “механічну” операцію.

Спрощення супроводу

Це область, де СК демонструють найбільші переваги. Оскільки операції зв’язку між таблицями відокремлені від логіки “всередині таблиць” – І те й інше можна міняти незалежно і не зачіпаючи решти.

Hапример – з’ясувалося, що міста мають дублюються назви. Вирішено ввести в City ще одне поле – Область (Region) і зробити ПК (City, Region). У випадку ЄК – Змінюється таблиця City, змінюється таблиця People – додається поле Region (так, так, для всіх записів, про розміри мовчу), переписуються всі запити, У тому числі на клієнтах, в яких бере участь City, в них додаються рядок AND XXX.Region = City.Region.

Так, ледве не забув, більшість серверів сильно не люблять ALTER TABLE на поля, входять до PRIMARY KEY і FOREIGN KEY.

У випадку СК – Додається поле в City, змінюється UNIQUE CONSTRAINT. Все.

Ще приклад – у разі СК зміна списку полів в SELECT ніколи не змушує переписувати JOIN. У випадку ЄК – Додалося поле, не входить до ПК пов’язаної таблиці – Переписуйте.

Ще приклад – помінявся тип даних поля, що входить в ЄК. І знову переробки купи таблиць, заново оптимізація індексів …

В умовах мінливого законодавства це гідність СК саме по собі досить для їх використання.

Зменшення розміру БД

Припустимо в нашому прикладі, що середня довжина назви міста – 10 байт. Тоді на кожну людину в середньому припадатиме 10 байт для зберігання посилання на місто (реально трохи більше за рахунок службової інформації на VARCHAR і набагато більше за рахунок індексу по People.City, який доведеться побудувати, щоб REFERENCES працювала ефективно). У випадку СК – 4 байта. Економія – мінімум 6 байт на людину, приблизно 10 Мб для м. Новосибірську. Очевидно, що в більшості випадків зменшення розміру БД – не самоціль, але це, очевидно, призведе і до зростання швидкодії.

Звучали аргументи, що БД може сама оптимізувати зберігання ЄК, підставивши замість нього в People якусь хеш-функцію (фактично створивши СК сама). Hо жоден з реально існуючих комерційних серверів БД так не робить, і є підстави вважати, що й не буде робити. Найпростішим обгрунтуванням такої думки є те, що при подібній підстановці банальні оператори ADD CONSTRAINT … FOREIGN KEY або DROP CONSTRAINT … FOREIGN KEY будуть приводити до неабиякої перетрушування таблиць, з відчутним зміною всієї БД (треба буде фізично додати або видалити (з заміною на хеш-функцію)) все поля, що входять в CONSTRAINT.

Збільшення швидкості вибірки даних

Питання досить спірне, однак, виходячи з припущень, що:

швидкодію системи на СК буде відчутно вище. І ось чому:

ЄК можуть потенційно дати більш високу швидкодію, коли:

Тобто, в нашому прикладі це запит типу:

 SELECT Family, City FROM People;

У випадку СК цей запит буде виглядати як

 SELECT P.Family, C.Name
  FROM People P INNER JOIN City C ON P.CityId = C.Id;

Здавалося б, ЄК дає більш простий запит з меншим кількістю таблиць, який виконається швидше. Але і тут не все так просто: розміри таблиць для ЄК – Більше (див. вище) і дискова активність легко з’їсть перевагу, отримане за рахунок відсутності JOIN `а. Ще сильніше це позначиться, якщо при вибірці даних використовується їх фільтрування (а при скільки-небудь значному обсязі таблиць воно використовується обов’язково). Справа в тому, що пошук, як правило, здійснюється за інформативним полям типу CHAR, DATETIME і т.п. Тому часто буває швидше знайти в довідковій таблиці набір значень, що обмежує повертається запитом результат, а потім шляхом JOIN `а по швидкому INTEGER-індексом відібрати підходящі записи з великої таблиці. Наприклад:

(ЄК) SELECT Family, City FROM People WHERE City = 'Іваново';

буде виконуватися в рази повільніше, ніж

() SELECT P.Family, C.Name
      FROM People P INNER JOIN City C ON P.CityId = C.Id WHERE C.Name = 'Іваново';

У випадку ЄК – Буде INDEX SCAN великої таблиці People по CHARACTER-індексу. У випадку СК – INDEX SCAN меншою CITY і JOIN з ефективного INTEGER індексу.

А от якщо замінити = 'Іваново' на LIKE '% ванів', то мова піде про гальмування ЄК щодо СК на порядок і більше.

Аналогічно, як тільки у випадку з ЄК знадобиться включити в запит поле з City, що не входить в її первинний ключ – JOIN буде здійснюватись по повільному індексом і швидкодія впаде відчутно нижче рівня СК. Висновки кожен може робити сам, але нехай він згадає, який відсоток від загальної числа його запитів складають SELECT * FROM ЕдінственнаяТабліца. У мене – мізерно малий.

Так, прихильники ЄК люблять проводити в якості гідності “Інформативність таблиць”, яка в разі ЄК зростає. Ще раз повторю, що максимальної інформативністю має таблиця, яка містить всю БД у вигляді flat-file. Будь-яке “підвищення інформативності таблиць” є збільшення ступеня дублювання в них інформації, що не є добре.

Збільшення швидкості оновлення даних

INSERT

Hа перший погляд ЄК швидше – не треба при INSERT генерувати зайвого поля і перевіряти його унікальність. Загалом-то так воно і є, хоча це уповільнення виявляється тільки при дуже високій інтенсивності транзакцій. Втім і це неочевидно, т.к. деякі сервери оптимізують вставку записів, якщо по ключовому полю побудований монотонно зростаючий CLUSTERED індекс. У випадку
СК це елементарно, в разі ЄК – На жаль, зазвичай недосяжно. Крім цього, INSERT в таблицю на стороні MANY (який відбувається частіше) піде швидше, тому що REFERENCES будуть перевірятися за більш швидкому індексу.

UPDATE

При оновленні поля, що входить в ЄК, Доведеться каскадно оновити і всі пов’язані таблиці. Так, перейменування Ленінграда в Санкт-Петербург зажадає з нашому прикладі транзакції на кілька мільйонів записів. Оновлення
будь-якого атрибута в системі з СК призведе до оновлення тільки одного запису. Очевидно, що в разі розподіленої системи, наявності архівів і т.п. ситуація тільки погіршиться. Якщо оновлюються поля не входять до ЄК – Швидкодія буде майже однаковим.

Ще про CASCADE UPDATE

Далеко не всі сервери БД підтримують їх на декларативному рівні. Аргументи “Це у вас сервер кривої” в цьому випадку навряд чи коректні. Це змушує писати окрему логіку для оновлення, що не завжди просто (наводився хороший приклад – При відсутності CASCADE UPDATE оновити поле, на яке є посилання, взагалі неможливо – треба відключати REFERENCES або створювати копію запису, що не завжди допустимо (інші поля можуть бути UNIQUE)).

DELETE

У випадку СК буде виконуватися швидше, з тієї простої причини, що перевірка REFERENCES піде по швидкому індексу.

А чи є хороші ЄК?

Hічто не вічне під Місяцем. Самий, здавалося б, надійний атрибут раптом відміняється і перестає бути унікальним (далеко ходити не буду – рубль звичайний і рубль деномінований, прикладів несть числа). Американці лаються на неунікальність номера соціального страхування, Microsoft – на китайські сірі мережеві плати з дублюючими MAC-адресами, які можуть призвести до дублювання GUID, лікарі роблять операції зі зміни статі, а біологи клонують тварин. В цих умовах (і з огляду на закон неубиванія ентропії) закладати в систему тезу про незмінність ЄК – Закладати під себе міну. Їх треба виділяти в окремий логічний шар і по можливості ізолювати від решти інформації. Так їх зміну переживається набагато легше. Та й взагалі: однозначно асоціювати сутність з якимось із атрибутів цієї сутності – ну, дивно, что-ли. Hомер паспорта ще не є людина. СК ж – це якась субстанція, саме і означає сутність. Саме сутність, а не якийсь з її атрибутів.

Типові аргументи прихильників ЄК

В системі з СК не здійснюється контроль правильності введення інформації

Це не так. Контроль не здійснювався б, якби на поля, що входять до ЄК не було накладено обмеження унікальності. Очевидно, що якщо предметна область диктує якісь обмеження на атрибути ЄК, То вони будуть відображені в БД в будь-якому випадку.

В системі з ЄК менше JOIN `ов, отже, запити простіше і розробка зручніше

Так, менше. Але, в системі з СК тривіально пишеться:

 CREATE VIEW PeopleEK AS
  SELECT P.Family, P.Passport, C.Name
   FROM People P INNER JOIN City C ON P.CityId = C.Id

І можна мати все ті ж принади. З більш, правда, високим швидкодією. При цьому непогано згадати, що в разі ЄК багатьом доведеться програмувати каскадні операції, і, не дай Бог в розподіленому середовищі, боротися з проблемами швидкодії. Hа тлі цього “короткі” запити вже не здаються настільки привабливими.

Введення ЄК порушує третю нормальну форму

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

Тобто, мови про ключові полях там не йде взагалі. Тому додавання ще одного ключа в таблицю ні в якій мірі не може порушити 3НФ. Взагалі, для таблиці з декількома можливими ключами має сенс говорити не про 3 НФ, а про Нормальною Формі Бойса-Кодда, яка спеціально введена для таких таблиць.

Отже:
Таблиця знаходиться в нормальній формі Бойса-Кодда (НФБК), якщо і тільки якщо будь-яка функціональна залежність між його полями зводиться до повної функціональної залежності від можливого ключа.

Таким чином, таблиця, що має СК, Легко може бути нормалізована хоч до 5НФ. Точніше буде сказати, що СК до нормалізації не мають ніякого відношення. Більш того, введення СК зменшує надмірність даних в БД, що взагалі добре узгоджується з ідеологією нормалізації. По суті, нормалізація і є зменшення інформативності окремих таблиць за певними правилами. Тільки СК усувають аномалії не всередині таблиці, а на міжтабличних рівні (типу усунення каскадних оновлень). Так би мовити, система з СК – Святіші Папи Римського :-). Справді – ситуація, коли при зміні одного з полів таблиці доводиться змінювати вміст цього ж поля в інших записах ЦІЙ ЖЕ таблиці, розглядається як аномалія оновлення. Але в системі з ЄК доведеться проробити те ж саме у зв’язаних таблицях при зміні ключового атрибута на стороні 1 відносини 1: N. Очевидно, що ця ситуація з точки зору фізичної реалізації БД нічим не краще. В системі з СК таких ситуацій не виникає.

Таблиці в системі з ЄК інформативніше

Максимальної інформативністю має таблиця, що містить всю БД у вигляді flat-file. Будь-яке “підвищення інформативності таблиць” є збільшення ступеня дублювання в них інформації, що не обов’язково є добре. Та й взагалі термін “Інформативність таблиці “сумнівний. Мабуть, більш важлива інформативність БД, яка в обох випадках однакова.

Висновок

Загалом-то, висновки очевидні – введення СК дозволяє отримати краще керовану, більш компактну і швидкодіючу БД. Зрозуміло, це не панацея. В деяких випадках (наприклад, таблиця на яку немає REFERENCES і в яку здійснюється інтенсивна вставка даних і т.п.) більш вірно використовувати
ЄК або не використовувати ПК взагалі (Останнє категорично протипоказано для багатьох РСУБД і засобів розробки клієнтських додатків). Але мова йшла саме про типову методику, яку треба рекомендувати до застосування в загальному випадку. Унікальні ситуації можуть зажадати унікальних же рішень (іноді і нормалізацією доводиться поступатися).

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*