Unique Identifier als primary key? Waarom niet.

woensdag 24 maart 2021

In dit artikel

Het is een discussie die je veel voorbij hoort komen. Kan een unique identifier als primary key dienen. Er lijkt vaak wel een klassieke scheiding te zijn tussen ontwikkelaars en beheerders. Ontwikkelaars willen niets anders dan unique identifiers en beheerders zien identity velden als een betere oplossing. Het is iets dat ik vaker hoor terugkomen en wie heeft er nu gelijk? Zoals zo vaak, ‘it depends’. Er zijn sowieso twee kanten aan het verhaal. De ene kant, vaak de ontwikkelaars gaat over modelering en roundtrips. De andere kant gaat over performance en beheer.

Wat is een primary key voor ontwikkelaars

Voordat we een duidelijke mening kunnen vormen over wat goed is en wat minder goed is het belangrijk eerst helder te hebben wat dan een primary key is. Een primary key is een veld dat een uniek record beschrijft in een tabel. Dat kan ook een combinatie van velden zijn maar bij voorkeur wordt dit zoveel mogelijk beperkt. Daarnaast moet een veld dat dient als primary key altijd gevuld zijn. Daarmee is nog niet vastgesteld wat het datatype moet zijn. Het kan een numerieke waarde zijn zoals een autonummering veld. Maar ook een tekstveld met daarin een uniek kenmerk zoals een BSN of IBAN nummer. Of natuurlijk een unique identifier.

Wat is een primary key voor beheerders

Een primary key op zich zelf is voor beheerders niet anders dan voor ontwikkelaars. Maar er zit nog een aspect aan. Data moet ook opgeslagen worden. Vaak zal een primary key opgeslagen worden als een clustered index. Een clustered index kan maar één keer voorkomen in een tabel, net als een primary key. De reden hiervoor is dat een clustered index niet alleen de index key bevat maar het hele record. In het volgende eenvoudige voorbeeld zie je een tabel met een clustered index op het id veld. Maar een nonclustered index op de landcode.

Clustered Index

id	country_name	country_iso
1	Nederland	NL
2	België	BE
3	Duitsland	DE

Non Clustered Index

country_iso	id
BE	2
DE	3
NL	1

Zoals je ziet in dit voorbeeld bevat de clustered index het hele record en de nonclustered index de landcode en een referentie naar de primary key. De definitie van deze tabel zou als volgt zijn:

CREATE TABLE dbo.country (
    id int identity not null,
    country_name varchar(50) not null,
    country_iso char(2) not null,
    constraint pk_country primary key clustered(id)
);
go
CREATE INDEX ix_country_iso ON dbo.country(country_iso);
go

Het probleem met een unique identifier

Vanuit een modeleringsprincipe is een unique identifier een geschikte kandidaat als primary key. Maar vanuit het fysieke aspect ligt dat iets anders. Omdat een primary key vaak ook de clustered index is gelden ook de regels van een clustered index. Deze zijn:

Zo uniek mogelijk.
Zijn sequentieel.

Tenzij anders aangegeven wordt bij het aanmaken van een primary key ook een clustered index aangemaakt. Hoe de records op disk opgeslagen worden hangt af van hoe de tabel is aangemaakt. Met een heap of een clustered index. Bij een heap wordt ieder record opgeslagen in volgorde van insert. Bij een clustered index is dat op basis van de index key, vaak ook de primary key. Neem de volgende inserts:

INSERT INTO dbo.country VALUES (3, 'Duitsland', 'DE');
INSERT INTO dbo.country VALUES (2, 'België', 'BE');
INSERT INTO dbo.country VALUES (1, 'Nederland', 'NL');

Als je kijkt hoe de data opgeslagen wordt zie je het verschil tussen een heap en een clustered index.

heap


3	Duitsland	DE
2	België	BE
1	Nederland	NL

clustered index


1	Nederland	NL
2	België	BE
3	Duitsland	DE

Zoals aangegeven wordt bij een heap de data in volgorde van aanbod opgeslagen. Bij een clustered index is dat volgens de key. Of een heap beter is dan een clustered is een discussie voor een andere dag. Het probleem met de clustered index in dit geval is namelijk dat de inserts niet in dezelfde volgorde zijn als de index. Wat er gebeurt is dat de eerste insert netjes op zijn plek terecht komt. De volgende insert hoort daar echter voor. Dus het eerste record wordt verplaatst om ruimte te maken voor het tweede record. Dit herhaalt zich bij het derde record. Deze verplaatsingen kosten extra resources en komen de performance niet ten goede. Bij grote hoeveelheden data wordt dit probleem groter en komt ook page splitting om de hoek kijken. In dat geval is er onvoldoende ruimte op de page om het record toe te voegen en wordt de page in tweeën gedeeld. Wederom weer IO resources. En niet alleen dat, meestal gaat dit gepaard met fragmentatie en dit betekent vrijwel altijd dat de data inefficiënt opgeslagen is. Dit is nu precies het probleem met een unique identifier. Omdat deze ‘random’ is, is fragmentatie onvermijdelijk met een verminderde performance tot gevolg.

De oplossing

Er zijn een paar oplossingen. Deze zijn sterk afhankelijk van het gebruik. Het probleem ontstaat vooral bij inserts. Stel je voor dat je een unique identifier gebruikt als sleutel voor een bestelling en je duizenden bestellingen per seconde moet verwerken. Dan wordt het best wel een uitdaging om dit goed te managen. Maar wanneer je een unique identifier gebruikt om een gebruiker binnen je organisatie te identificeren en er hooguit een handjevol per maand bij en af gaan is het probleem veel minder zichtbaar.

Het is vooral een probleem als het een probleem is. Anders kun je het rustig laten zoals het is. Maar wanneer je nu wel een unique identifier wilt gebruiken in combinatie met een goede performance is er een eenvoudige oplossing. Zoals eerder gezegd is over het algemeen de primary key ook de clustered index. Maar dat is niet verplicht. Het is slechts de standaard tenzij anders aangegeven. Hieronder zie je een voorbeeld maar dan met een aanpassing voor de primary key.

CREATE TABLE dbo.country (
    id int identity not null,
    uid unique identifier not null,
    country_name varchar(50) not null,
    country_iso char(2) not null,
    constraint pk_country primary key nonclustered(uid)
);
go
CREATE CLUSTERED INDEX ix_country_id ON dbo.country(id);
go

Het is dus vrij eenvoudig. Bij het aanmaken van de primary key specificeer je clustered of nonclustered en dat bepaalt hoe SQL Server de data fysiek opslaat. Let wel, de nonclustered index met de unique identifier heeft nog steeds last van fragmentatie. Maar omdat de index alleen de index key bevat en niet het hele record zoals bij een clustered index is de impact op de performance veel kleiner.

Kan het niet zonder unique identifier?

Een unique identifier heeft een aantal voordelen. Hij is uniek. In theorie over de hele wereld. Dat maakt een unique identifier ideaal als sleutel bij gedistribueerde systemen. Een ander voordeel is dat een unique identifier aangemaakt kan worden op de client. In tegenstelling tot bijvoorbeeld een autonummering. Bij een autonummering veld is altijd een roundtrip nodig omdat deze wordt aangemaakt op de server. Daarnaast zou in theorie zelfs contentie kunnen ontstaan wanneer met een hoog volume records worden toegevoegd.

Een alternatief is het voorbeeld van Twitter. In dit geval wordt eerst een tijdscomponent samengesteld. Bijvoorbeeld het aantal milliseconden sinds een specifieke datum; 1 januari 2021. Dit wordt aangevuld met een host id. In onderstaand voorbeeld gebruik ik daarvoor 8 bits. Dat geeft mogelijk 256 hosts. Ik gebruik meestal niet meer dan 1 host dus dat is voldoende. Omdat theoretisch meerdere id’s per milliseconde gegenereerd kunnen is er ook een extra counter. In mijn geval zijn dat 12 bits. Dat geeft 4096 mogelijkheden. Hieronder staat het voorbeeld:

public class IdService
{
    private readonly object _lock = new object();
    private bool _done = true;
    private int _counter = 0;
    private readonly DateTime STARTDATE = 
        new DateTime(2021, 1, 1, 0,0,0,DateTimeKind.Utc);
    private long _id;
    private readonly int host;

    public IdService(int host)
    {
        this.host = host;
    }
    public long Next()
    {
        if (_done)
        {
            lock (_lock)
            {
                if (_done)
                {
                    _done = false;
                    _counter++;
                    if (_counter == 4096)
                        _counter = 0;
                    _id = (long)DateTime.UtcNow
                        .Subtract(STARTDATE).TotalMilliseconds;
                    _id <<= 8;
                    _id += host;
                    _id <<= 12;
                    _id += _counter;
                    _done = true;
                }
            }
        }
        return _id;
    }
}

Zoals gezegd. Een unique identifier is niet per definitie fout. Door goed te kijken naar de requirements en verwachte workload is het niet de grote performance killer waar de meeste database beheerders bang voor zijn.