Ciągi znaków jako klucze podstawowe w SQL Database


178

Nie jestem zbyt zaznajomiony z bazami danych i teoriami dotyczącymi ich działania. Czy z punktu widzenia wydajności (wstawianie / aktualizowanie / wysyłanie zapytań) wolniej jest używać ciągów znaków dla kluczy podstawowych niż liczb całkowitych?

Odpowiedzi:


191

Technicznie tak, ale jeśli ciąg ma sens jako klucz podstawowy, prawdopodobnie powinieneś go użyć. Wszystko zależy od rozmiaru tabeli, dla której ją tworzysz, i długości łańcucha, który będzie kluczem podstawowym (dłuższe ciągi == trudniej porównać). Niekoniecznie użyłbym ciągu dla tabeli, która ma miliony wierszy, ale ilość spowolnienia wydajności, którą uzyskasz, używając ciągu na mniejszych tabelach, będzie minimalna w porównaniu z bólami głowy, które możesz mieć, mając liczbę całkowitą, która nie nie mają żadnego znaczenia w odniesieniu do danych.


11
czy nie zależałoby to od bazy danych? Myślę, że prawidłowo zindeksowany ciąg nie byłby tak dużo wolniejszy, jeśli w ogóle pochodzi z liczby?
Ryan Guill

2
Zgadzam się, że należy wziąć pod uwagę wiele zmiennych. (W sqlserver) widzieliśmy rzeczywiste problemy z wydajnością przy używaniu ciągów o długościach od średnich do wysokich i wyższych, nawet po indeksowaniu. Kup, masz rację, są na przykład rzeczy do pokonania tego sprzętu.
kemiller2002

1
Słusznie. Zgodziłbym się jednak, że jeśli ciąg ma sens, to właśnie tego powinieneś użyć. Powiedziałbym również, że na pewno jest czas na pola GUID lub UUID w bazach danych, w których pole autoincrement nie działałoby.
Ryan Guill

7
Pamiętaj również, że podczas porównywania indeksów często występuje bardzo duża różnica między CHAR a VARCHAR
Tom H,

7
Liczba komentarzy do tej odpowiedzi jasno pokazuje, jak niekompletna jest ona. Wzmianka o indeksowaniu byłaby minimalną akceptowalną odpowiedzią.
Pedro Rolo

74

Innym problemem związanym z używaniem ciągów jako klucza podstawowego jest to, że ponieważ indeks jest stale ustawiany w kolejności sekwencyjnej, kiedy tworzony jest nowy klucz, który byłby w środku kolejności, indeks musi zostać ponownie uporządkowany ... jeśli używasz funkcji auto number integer, nowy klucz jest właśnie dodawany na końcu indeksu.


2
Może to jednak powodować powstawanie „gorących punktów” dla nowych płytek. O ile prawidłowo zarządzasz bazą danych, i tak powinno być na stronach dodatkowe miejsce na wstawianie, a podziały stron powinny być rzadkie.
Tom H,

20
to jest, gdy klucze podstawowe są klastrowane. możesz je również utworzyć bezklastrowo.
Nauka

Identyfikatory XID są uporządkowane, co może pomóc, jeśli użyjesz po prostu ciągów
xid

22

Wstawia do tabeli mającej indeks klastrowy, w którym wstawienie następuje w środku sekwencji NIE POWODUJE przepisania indeksu. Nie powoduje to przepisywania stron zawierających dane. Jeśli na stronie jest miejsce, do którego trafi wiersz, zostanie on umieszczony na tej stronie. Pojedyncza strona zostanie ponownie sformatowana, aby umieścić wiersz we właściwym miejscu na stronie. Gdy strona jest pełna, nastąpi podział strony, z połową wierszy na jednej stronie, a połową na drugiej. Strony są następnie ponownie łączone z połączoną listą stron, które zawierają dane tabel, które mają indeks klastrowy. Ostatecznie skończysz na zapisaniu 2 stron bazy danych.


Dobre wytłumaczenie. Ale czy to prawda dla wszystkich baz danych SQL? Słyszałem o problemach z wydajnością MySQL podczas używania losowego UUID jako klucza podstawowego.
hgoebl

13

Ciągi są wolniejsze w łączeniach iw prawdziwym życiu bardzo rzadko są naprawdę wyjątkowe (nawet jeśli powinny). Jedyną zaletą jest to, że mogą zmniejszyć liczbę złączeń, jeśli dołączasz do tabeli podstawowej tylko po to, aby uzyskać nazwę. Jednak ciągi znaków również często ulegają zmianie, co stwarza problem konieczności naprawiania wszystkich powiązanych rekordów, gdy zmienia się nazwa firmy lub osoba wychodzi za mąż. Może to być ogromny spadek wydajności, a jeśli wszystkie tabele, które powinny być w jakiś sposób powiązane, nie są powiązane (zdarza się to częściej niż myślisz), możesz również mieć niedopasowanie danych. Liczba całkowita, która nigdy się nie zmieni przez cały okres istnienia rekordu, jest o wiele bezpieczniejszym wyborem z punktu widzenia integralności danych, jak również z punktu widzenia wydajności. Klucze naturalne zwykle nie są tak dobre do przechowywania danych.

Chcę również zwrócić uwagę, że najlepszym z obu światów jest często użycie klucza autoinkrementacji (lub w niektórych wyspecjalizowanych przypadkach GUID) jako PK, a następnie umieszczenie unikalnego indeksu na kluczu naturalnym. Otrzymujesz szybsze łączenia, nie otrzymujesz zduplikowanych rekordów i nie musisz aktualizować miliona rekordów podrzędnych, ponieważ zmieniła się nazwa firmy.


26
Ciągi, które są dobrymi kandydatami na PK, nie mają duplikatów - w przeciwnym razie nie byłyby dobrym kandydatem na PK. Pomyśl o kodach ICD-9, kodach krajów, numerach VIN. Używanie nazwy jako przykładu problemu z kluczami naturalnymi jest błędne, ponieważ przede wszystkim nie powinny one być kandydatami.
Tom H

6
@Tom H: Kody ISO County NIE zmieniają się. [ en.wikipedia.org/wiki/ISO_3166-1#Editions_and_changes ] W odpowiedzi na pokrewne pytanie powiedział: [ stackoverflow.com/questions/925266/… ] „W przypadku kluczy PODSTAWOWYCH upewnij się, że ich wyjątkowość jest pod Twoją kontrolą”
Steve Schnepp

4
@SteveSchnepp: tak, a ISO jest zaufanym organem do zarządzania tą zmianą. Z drugiej strony, gdy musisz połączyć swój monotoniczny ciąg rosnących wartości całkowitych z cudzymi, jesteś sam;)
kiedy

1
Zgadzam się, że nazwiska nie powinny być uważane za klucz, widziałem je już wiele razy, kiedy były.
HLGEM

1
@onedaypodczas scalania 2 monotonicznych sekwencji rosnącej liczby całkowitej można dość łatwo wykonać poprzez prefiksowanie lub sufiksowanie :)
Steve Schnepp

6

Nie ma znaczenia, jakiego używasz jako klucza podstawowego, o ile jest on WYJĄTKOWY. Jeśli zależy Ci na szybkości lub dobrym projekcie bazy danych, użyj int, chyba że planujesz replikować dane, a następnie użyj identyfikatora GUID.

Jeśli jest to baza danych dostępu lub jakaś malutka aplikacja, to kogo to naprawdę obchodzi. Myślę, że powodem, dla którego większość z nas, programistów, uderza stary int lub guid z przodu, jest to, że projekty mają sposób na rozwój i chcesz zostawić sobie możliwość rozwoju.


5

Za dużo zmiennych. Zależy to od wielkości tabeli, indeksów, charakteru domeny kluczy łańcuchowych ...

Generalnie liczby całkowite będą szybsze. Ale czy różnica będzie na tyle duża, żeby się tym przejmować? Trudno powiedzieć.

Jaka jest Twoja motywacja do wyboru stringów? Często też o wiele łatwiejsze są klawisze numeryczne z automatycznym zwiększaniem wartości . Czy to semantyka? Wygoda? Problemy z replikacją / rozłączeniem? Twoja odpowiedź może ograniczyć twoje opcje. To również przywodzi na myśl trzecią opcję „hybrydową”, o której zapominasz: Guids.


to nie ma sensu cloutierm, co masz na myśli?
HLGEM

@HLGEM: Jeśli rozumiem, że pisze, ma na myśli synchronizację rekordów utworzonych na laptopie z główną bazą danych.
Joel Coehoorn

Mam na myśli dwie oddzielne bazy danych z tymi samymi jednostkami, tylko jedna jest aktualizowana rzadziej w celu trwałego przechowywania. Jeśli zapytam o jednostkę „Kalifornia” w bazie danych A, chcę, aby była zasadniczo taka sama jak „Kalifornia” w bazie danych B.
mainstringargs

1
Podobnie jest z synchronizacją rekordów utworzonych na laptopie, ponieważ jest to ten sam problem: rekordy utworzone w jednym miejscu nie powinny kolidować z rekordami utworzonymi w innym. Jednym z możliwych rozwiązań są klucze Guid.
Joel Coehoorn

5

Nie martw się o wydajność, dopóki nie uzyskasz prostego i solidnego projektu, który będzie zgodny z tematem, który opisują dane, i będzie dobrze pasował do zamierzonego wykorzystania danych. Następnie, jeśli pojawią się problemy z wydajnością, możesz je rozwiązać, dostosowując system.

W takim przypadku prawie zawsze lepiej jest użyć ciągu jako naturalnego klucza podstawowego, pod warunkiem, że możesz mu zaufać. Nie martw się, jeśli jest to ciąg, o ile jest on rozsądnie krótki, powiedzmy maksymalnie 25 znaków. Nie zapłacisz dużej ceny pod względem wydajności.

Czy osoby wprowadzające dane lub automatyczne źródła danych zawsze dostarczają wartości dla domniemanego klucza naturalnego, czy też są czasami pomijane? Czy zdarza się, że dane wejściowe są błędne? Jeśli tak, w jaki sposób są wykrywane i naprawiane błędy?

Czy programiści i użytkownicy interaktywni, którzy określają zapytania, są w stanie używać klucza naturalnego, aby uzyskać to, czego chcą?

Jeśli nie możesz zaufać naturalnemu kluczowi, wymyśl surogat. Jeśli wymyślisz surogat, równie dobrze możesz wymyślić liczbę całkowitą. Wtedy musisz się martwić, gdzie ukryć surogat przed społecznością użytkowników. Niektórzy programiści, którzy nie ukryli klucza zastępczego, zaczęli tego żałować.


3

Indeksy implikują wiele porównań.

Zazwyczaj łańcuchy są dłuższe niż liczby całkowite, a do porównania można zastosować reguły sortowania, więc porównywanie łańcuchów jest zwykle bardziej wymagającym obliczeniowo niż porównywanie liczb całkowitych.

Czasami jednak szybsze jest użycie łańcucha jako klucza podstawowego niż wykonanie dodatkowego sprzężenia z string to numerical idtabelą.


2

Tak, ale jeśli nie spodziewasz się milionów wierszy, nieużywanie klucza opartego na ciągach znaków, ponieważ jest wolniejszy, jest zwykle „przedwczesną optymalizacją”. W końcu łańcuchy są przechowywane jako duże liczby, podczas gdy klawisze numeryczne są zwykle przechowywane jako mniejsze liczby.

Jedną rzeczą, na którą należy zwrócić uwagę, jest sytuacja, w której masz indeksy klastrowe na dowolnym kluczu i wykonujesz dużą liczbę wstawień, które nie są sekwencyjne w indeksie. Każda napisana linia spowoduje ponowne zapisanie indeksu. jeśli robisz wstawianie wsadowe, może to naprawdę spowolnić proces.


2

Dwa powody, dla których warto używać liczb całkowitych w kolumnach PK:

  1. Możemy ustawić tożsamość dla pola liczb całkowitych, które zwiększało się automatycznie.

  2. Kiedy tworzymy PK, baza danych tworzy indeks (Cluster lub Non Cluster), który sortuje dane, zanim zostaną zapisane w tabeli. Używając tożsamości na PK, optymalizator nie musi sprawdzać kolejności sortowania przed zapisaniem rekordu. Poprawia to wydajność przy dużych stołach.


1

Jaki jest powód posiadania ciągu jako klucza podstawowego?

Po prostu ustawiłbym klucz podstawowy na automatycznie zwiększające się pole liczby całkowitej i umieściłbym indeks w polu ciągu.

W ten sposób, jeśli wyszukujesz w tabeli, powinny one być stosunkowo szybkie, a wszystkie twoje łączenia i zwykłe wyszukiwania pozostaną niezmienione.

Możesz także kontrolować ilość indeksowanego pola tekstowego. Innymi słowy, możesz powiedzieć „indeksuj tylko pierwsze 5 znaków”, jeśli uważasz, że to wystarczy. Lub jeśli Twoje dane mogą być stosunkowo podobne, możesz zindeksować całe pole.


3
Myślę, że wkładanie inteligencji do klucza to proszenie się o kłopoty. Czy pozostaną wyjątkowe? Czy wszystkie numery rachunków zaczynali od skrótu stanu na początku tylko do ruchu klienta. Zaktualizuj pole - nie ma problemu - wszystkie te tabele połączone numerem konta - co za bałagan.
JeffO,

1
Przykładem użycia łańcucha jako PK może być tabela ustawień. np. settingNamePK, isUserEditable, isCustomerEditable etc Następnie, jeśli chcesz zmodyfikować zachowanie ustawień "UPDATE setting SET ... WHERE settingNamePK = 'dailyWorkObligation'" jest o wiele przyjemniejsze niż używanie identyfikatorów i przechowywanie gdzieś mapowania identyfikatorów. Oczywiście możesz mieć liczbę całkowitą PK i nazwę ustawienia jako kolejny unikalny klucz.
MeatPopsicle,

Skoro klucz podstawowy jest liczbą całkowitą automatycznie zwiększaną, czy wstawianie również nie powinno mieć wpływu na szybkość?
Dennis

Dla ciekawskich deweloperów Railsów, oto jak określić długość indeksu . Zauważ, że SQLite nie obsługuje długości indeksu.
Dennis

1

Z punktu widzenia wydajności - ciąg znaków tak (PK) spowolni wydajność w porównaniu z wydajnością uzyskaną przy użyciu liczby całkowitej (PK), gdzie PK ---> klucz podstawowy.

Z punktu widzenia wymagań - chociaż nie jest to część Twojego pytania, to jednak chciałbym wspomnieć. Kiedy obsługujemy ogromne dane w różnych tabelach, zazwyczaj szukamy prawdopodobnego zestawu kluczy, które można ustawić dla określonej tabeli. Dzieje się tak przede wszystkim dlatego, że istnieje wiele tabel i przeważnie każda lub jedna tabela byłaby powiązana z drugą poprzez jakąś relację (pojęcie klucza obcego). Dlatego naprawdę nie zawsze możemy wybrać liczbę całkowitą jako klucz podstawowy, raczej wybieramy kombinację 3, 4 lub 5 atrybutów jako klucz podstawowy dla tych tabel. I te klucze mogą być używane jako klucze obce, gdy odnosimy się do rekordów z inną tabelą. To sprawia, że ​​w razie potrzeby przydatne jest powiązanie rekordów z różnymi tabelami.

Dlatego dla optymalnego wykorzystania - zawsze tworzymy kombinację 1 lub 2 liczb całkowitych z 1 lub 2 atrybutami ciągów, ale ponownie tylko wtedy, gdy jest to wymagane.


0

Może wystąpić bardzo duże nieporozumienie związane z ciągiem znaków w bazie danych. Prawie każdy pomyślał, że reprezentacja liczb w bazie danych jest bardziej zwarta niż w przypadku łańcuchów. Myślą, że w db-s liczby są reprezentowane jak w pamięci. ALE to nieprawda. W większości przypadków reprezentacja liczb jest bardziej zbliżona do reprezentacji typu string, jak do innych.

Szybkość używania liczby lub łańcucha jest bardziej zależna od indeksowania niż od samego typu.


0

Domyślnie ASPNetUserIds to ciągi 128 znaków, a wydajność jest dobra.

Jeśli klucz MUSI być unikalny w tabeli, powinien to być Klucz. Dlatego;

podstawowy łańcuch-klucz = poprawne relacje z bazą danych, 1 klucz ciągu (podstawowy) i 1 indeks ciągu (podstawowy).

Inną opcją jest typowy klucz int, ale jeśli łańcuch MUSI być unikalny, prawdopodobnie nadal będziesz musiał dodać indeks z powodu ciągłych zapytań w celu sprawdzenia poprawności lub sprawdzenia, czy jest unikalny.

Tak więc użycie klucza tożsamości int = niepoprawne relacje DB, 1 klucz int (podstawowy), 1 indeks int (podstawowy), prawdopodobnie unikalny indeks ciągu i ręczne sprawdzenie tego samego ciągu nie istnieje (może coś takiego jak sprawdzenie sql ).

Aby uzyskać lepszą wydajność, używając int zamiast ciągu dla klucza podstawowego, gdy łańcuch MUSI być unikalny, musiałaby to być bardzo dziwna sytuacja. Zawsze wolałem używać kluczy typu string. I zgodnie z dobrą zasadą, nie denormalizuj bazy danych, dopóki nie jest to POTRZEBNE .

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.