Kontekst

Projektuję bazę danych (na PostgreSQL 9.6), która będzie przechowywać dane z aplikacji rozproszonej. Ze względu na rozproszony charakter aplikacji, nie mogę używać liczb całkowitych z automatycznym przyrostem ( SERIAL) jako mojego klucza głównego ze względu na potencjalne warunki wyścigu.

Naturalnym rozwiązaniem jest użycie UUID lub globalnie unikalnego identyfikatora. Postgres ma wbudowany UUIDtyp , który idealnie pasuje.

Problem, który mam z UUID, jest związany z debugowaniem: jest to ciąg nieprzyjazny dla człowieka. Identyfikator ff53e96d-5fd7-4450-bc99-111b91875ec5nic mi nie mówi, a ACC-f8kJd9xKCdchociaż nie gwarantuje, że jest unikalny, mówi mi, że mam do czynienia z ACCprzedmiotem.

Z punktu widzenia programowania często debuguje się zapytania aplikacji dotyczące kilku różnych obiektów. Załóżmy, że programista nieprawidłowo szuka obiektu ACC(konta) w ORDtabeli (kolejności). Za pomocą identyfikatora czytelnego dla człowieka programista natychmiast identyfikuje problem, a korzystając z identyfikatorów UUID spędziłby trochę czasu, zastanawiając się, co jest nie tak.

Nie potrzebuję „gwarantowanej” unikalności UUID; I nie potrzeba trochę miejsca do generowania kluczy bez konfliktów, lecz UUID jest przesadą. Ponadto, w najgorszym przypadku, nie byłoby końca świata, gdyby doszło do kolizji (baza danych ją odrzuca, a aplikacja może się zregenerować). Tak więc, biorąc pod uwagę kompromisy, mniejszy, ale przyjazny dla człowieka identyfikator byłby idealnym rozwiązaniem dla mojego przypadku użycia.

Identyfikacja obiektów aplikacji

Identyfikator, który wymyśliłem, ma następujący format:, {domain}-{string}gdzie {domain}jest zastępowany przez domenę obiektową (konto, zamówienie, produkt) i {string}jest losowo generowanym ciągiem. W niektórych przypadkach sensowne może być wstawienie {sub-domain}przed losowym ciągiem. Zignorujmy długość {domain}i {string}w celu zagwarantowania wyjątkowości.

Format może mieć stały rozmiar, jeśli pomaga w wydajności indeksowania / zapytań.

Problem

Wiedząc to:

• Chcę mieć klucze podstawowe w formacie podobnym do ACC-f8kJd9xKCd.
• Te klucze podstawowe będą częścią kilku tabel.
• Wszystkie te klucze będą używane w kilku sprzężeniach / relacjach w bazie danych 6NF.
• Większość tabel będzie miała rozmiar od średniego do dużego (średnio ~ 1 mln wierszy; największe z ~ 100 mln wierszy).

Jeśli chodzi o wydajność, jaki jest najlepszy sposób na przechowywanie tego klucza?

Poniżej cztery możliwe rozwiązania, ale ponieważ mam niewielkie doświadczenie z bazami danych, nie jestem pewien, która (jeśli w ogóle) jest najlepsza.

Rozważane rozwiązania

1. Zapisz jako string ( VARCHAR)

(Postgres nie robi różnicy między CHAR(n)i VARCHAR(n), więc ignoruję CHAR).

Po kilku badaniach odkryłem, że porównanie ciągów VARCHAR, szczególnie w operacjach łączenia, jest wolniejsze niż użycie INTEGER. To ma sens, ale czy jest to coś, o co powinienem się martwić w tej skali?

2. Zapisz jako binarne ( bytea)

W przeciwieństwie do Postgres, MySQL nie ma rodzimego UUIDtypu. Istnieje kilka postów wyjaśniających, jak przechowywać UUID za pomocą 16-bajtowego BINARYpola zamiast 36-bajtowego VARCHAR. Te posty podsunęły mi pomysł przechowywania klucza jako pliku binarnego ( byteana Postgres).

Oszczędza to rozmiar, ale bardziej martwię się wydajnością. Nie miałem szczęścia znaleźć wyjaśnienia, na którym porównanie jest szybsze: binarne lub łańcuchowe. Uważam, że porównania binarne są szybsze. Jeśli tak, byteato prawdopodobnie lepiej niż VARCHAR, mimo że programista musi teraz za każdym razem kodować / dekodować dane.

Mogę się mylić, ale myślę, że oba byteai VARCHARbędą porównywać (równość) bajt po bajcie (lub znak po znaku). Czy istnieje sposób, aby „pominąć” to porównanie krok po kroku i po prostu porównać „całość”? (Nie wydaje mi się, ale sprawdzanie nie kosztuje).

Myślę, że przechowywanie byteajest najlepszym rozwiązaniem, ale zastanawiam się, czy są jakieś inne alternatywy, które ignoruję. Również ta sama obawa wyrażona przeze mnie w odniesieniu do rozwiązania 1 jest prawdziwa: czy narzuty związane z porównaniami są wystarczające, że powinienem się martwić?

"Kreatywne rozwiązania

Wymyśliłem dwa bardzo „kreatywne” rozwiązania, które mogłyby zadziałać, po prostu nie jestem pewien, w jakim stopniu (tj. Gdybym miał problem z skalowaniem ich do więcej niż kilku tysięcy wierszy w tabeli).

3. Przechowuj jako, UUIDale z dołączoną „etykietą”

Głównym powodem nieużywania UUID jest to, że programiści mogą lepiej debugować aplikację. Ale co, jeśli możemy użyć obu: baza danych przechowuje wszystkie klucze UUIDtylko jako s, ale otacza obiekt przed / po zapytaniach.

Na przykład programista pyta ACC-{UUID}, baza danych ignoruje ACC-część, pobiera wyniki i zwraca je wszystkie jako {domain}-{UUID}.

Być może byłoby to możliwe dzięki hakowaniu przy użyciu procedur lub funkcji przechowywanych, ale przychodzą mi na myśl niektóre pytania:

• Czy jest to (usunięcie / dodanie domeny przy każdym zapytaniu) znaczne obciążenie?
• Czy to w ogóle możliwe?

Nigdy wcześniej nie korzystałem z procedur przechowywanych ani funkcji, więc nie jestem pewien, czy jest to w ogóle możliwe. Czy ktoś może rzucić trochę światła? Jeśli mogę dodać przezroczystą warstwę między programatorem a przechowywanymi danymi, wydaje się to idealnym rozwiązaniem.

4. (Mój ulubiony) Zapisz jako IPv6 cidr

Tak, dobrze to przeczytałeś. Okazuje się, że format adresu IPv6 doskonale rozwiązuje mój problem .

• Mogę dodawać domeny i subdomeny w pierwszych kilku oktetach, a pozostałe mogę używać jako ciągi losowe.
• Do kolizji kursy są OK. (Nie użyłbym jednak 2 ^ 128, ale nadal jest OK.)
• Porównania równości są (mam nadzieję) zoptymalizowane, więc mogę uzyskać lepszą wydajność niż zwykłe używanie bytea.
• Mogę faktycznie wykonać kilka ciekawych porównań, na przykład containsw zależności od tego, jak reprezentowane są domeny i ich hierarchia.

Załóżmy na przykład, że używam kodu 0000do reprezentowania „produktów” w domenie. Klucz 0000:0db8:85a3:0000:0000:8a2e:0370:7334reprezentowałby produkt 0db8:85a3:0000:0000:8a2e:0370:7334.

Główne pytanie tutaj: w porównaniu z tym bytea, czy jest jakaś główna zaleta lub wada korzystania z cidrtypu danych?

Za pomocą ltree

Jeśli IPV6 działa, świetnie. Nie obsługuje „ACC”. ltreerobi.

Ścieżka etykiety jest sekwencją zerową lub większą liczbą etykiet oddzielonych kropkami, na przykład L1.L2.L3, reprezentującą ścieżkę od katalogu głównego drzewa hierarchicznego do określonego węzła. Długość ścieżki etykiety musi być mniejsza niż 65 kB, ale lepiej jest utrzymać ją poniżej 2 kB. W praktyce nie jest to poważne ograniczenie; na przykład najdłuższa ścieżka etykiety w katalogu DMOZ ( http://www.dmoz.org ) ma około 240 bajtów.

Użyłbyś tego w ten sposób,

CREATE EXTENSION ltree;
SELECT replace('ACC-f8kJd9xKCd', '-', '.')::ltree;

Tworzymy przykładowe dane.

SELECT x, (
  CASE WHEN x%7=0 THEN 'ACC'
    WHEN x%3=0 THEN 'XYZ'
    ELSE 'COM'
  END ||'.'|| md5(x::text)
  )::ltree
FROM generate_series(1,10000) AS t(x);

CREATE INDEX ON foo USING GIST (ltree);
ANALYZE foo;


  x  |                ltree                 
-----+--------------------------------------
   1 | COM.c4ca4238a0b923820dcc509a6f75849b
   2 | COM.c81e728d9d4c2f636f067f89cc14862c
   3 | XYZ.eccbc87e4b5ce2fe28308fd9f2a7baf3
   4 | COM.a87ff679a2f3e71d9181a67b7542122c
   5 | COM.e4da3b7fbbce2345d7772b0674a318d5
   6 | XYZ.1679091c5a880faf6fb5e6087eb1b2dc
   7 | ACC.8f14e45fceea167a5a36dedd4bea2543
   8 | COM.c9f0f895fb98ab9159f51fd0297e236d

I altówka ..

                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=103.23..234.91 rows=1414 width=57) (actual time=0.422..0.908 rows=1428 loops=1)
   Recheck Cond: ('ACC'::ltree @> ltree)
   Heap Blocks: exact=114
   ->  Bitmap Index Scan on foo_ltree_idx  (cost=0.00..102.88 rows=1414 width=0) (actual time=0.389..0.389 rows=1428 loops=1)
         Index Cond: ('ACC'::ltree @> ltree)
 Planning time: 0.133 ms
 Execution time: 1.033 ms
(7 rows)

Zobacz dokumentację, aby uzyskać więcej informacji i operatorów

Jeśli tworzysz identyfikatory produktu, zrobiłbym to. Jeśli potrzebujesz czegoś do ich utworzenia, użyłbym UUID.

Tylko w odniesieniu do porównania wydajności z bytea. porównanie sieci odbywa się w 3 krokach: najpierw na wspólnych bitach części sieci, następnie na długości części sieci, a następnie na całym zdemaskowanym adresie. widzieć: network_cmp_internal

więc powinno być trochę wolniej niż bajt, który przechodzi prosto do memcmp. Przeprowadziłem prosty test na stole z 10 milionami wierszy, szukając jednego:

• używając numerycznego id (liczba całkowita) zajęło mi 1000ms.
• przy użyciu cidr zajęło 1300ms.
• użycie bytea zajęło 1250ms.

Nie mogę powiedzieć, że istnieje duża różnica między bajtem a cidr (chociaż różnica pozostała stała) Tylko dodatkowy if stwierdzenie - zgadnij, że nie jest tak źle dla krotek 10-milionowych.

Mam nadzieję, że to pomoże - chciałbym usłyszeć, co ostatecznie wybrałeś.