sql klucz podstawowy i indeks

Powiedzmy, że mam wiersz ID (int) w bazie danych ustawionej jako klucz podstawowy. Jeśli często odpytuję o identyfikator, czy muszę go również indeksować? A może jest to klucz podstawowy, co oznacza, że ​​jest już indeksowany?

Powodem, o który pytam, jest to, że w MS SQL Server mogę utworzyć indeks na tym ID, który, jak powiedziałem, jest moim kluczem podstawowym.

Edycja: dodatkowe pytanie - czy dodatkowe indeksowanie klucza podstawowego zaszkodzi?

Masz rację, to mylące, że SQL Server pozwala na tworzenie zduplikowanych indeksów w tych samych polach. Ale fakt, że możesz utworzyć inny, nie oznacza, że ​​indeks PK również już nie istnieje.

Dodatkowy indeks nic nie daje, ale jedyną szkodą (bardzo małą) jest dodatkowy rozmiar pliku i obciążenie związane z tworzeniem wierszy.

Jak wszyscy już powiedzieli, klucze podstawowe są indeksowane automatycznie.

Tworzenie większej liczby indeksów w kolumnie klucza podstawowego ma sens tylko wtedy, gdy trzeba zoptymalizować zapytanie, które używa klucza podstawowego i niektórych innych określonych kolumn. Tworząc inny indeks w kolumnie klucza podstawowego i włączając w to kilka innych kolumn, można osiągnąć pożądaną optymalizację zapytania.

Na przykład masz tabelę z wieloma kolumnami, ale odpytujesz tylko o kolumny ID, Nazwa i Adres. Biorąc ID jako klucz podstawowy, możemy utworzyć następujący indeks, który jest oparty na identyfikatorze, ale zawiera kolumny Nazwa i Adres.

CREATE NONCLUSTERED INDEX MyIndex
ON MyTable(ID)
INCLUDE (Name, Address)

Więc kiedy używasz tego zapytania:

SELECT ID, Name, Address FROM MyTable WHERE ID > 1000

SQL Server poda wynik tylko przy użyciu utworzonego indeksu i nie odczyta niczego z rzeczywistej tabeli.

UWAGA: Ta odpowiedź dotyczy tworzenia oprogramowania dla dużych przedsiębiorstw .

Jest to problem RDBMS, a nie tylko SQL Server, a zachowanie może być bardzo interesujące. Po pierwsze, chociaż klucze podstawowe są indeksowane automatycznie (unikalnie), NIE jest to bezwzględne. Są chwile, kiedy konieczne jest, aby klucz podstawowy NIE był jednoznacznie indeksowany.

W większości systemów RDBMS unikatowy indeks zostanie automatycznie utworzony na kluczu podstawowym, jeśli jeszcze nie istnieje . Dlatego można utworzyć własny indeks w kolumnie klucza podstawowego przed zadeklarowaniem go jako klucza podstawowego, a następnie ten indeks będzie używany (jeśli jest to dopuszczalne) przez aparat bazy danych podczas stosowania deklaracji klucza podstawowego. Często można utworzyć klucz podstawowy i zezwolić na utworzenie jego domyślnego unikatowego indeksu, a następnie utworzyć własny indeks alternatywny w tej kolumnie, a następnie usunąć indeks domyślny.

A teraz zabawna część - kiedy NIE potrzebujesz unikalnego indeksu klucza podstawowego? Nie chcesz jednego i nie możesz go tolerować, gdy twoja tabela zbiera wystarczającą ilość danych (wierszy), aby utrzymanie indeksu było zbyt kosztowne. Różni się to w zależności od sprzętu, silnika RDBMS, charakterystyki tabeli i bazy danych oraz obciążenia systemu. Jednak zwykle zaczyna się manifestować, gdy tabela osiągnie kilka milionów wierszy.

Zasadniczą kwestią jest to, że każde wstawienie wiersza lub aktualizacja kolumny klucza podstawowego powoduje skanowanie indeksu w celu zapewnienia unikalności. To unikalne skanowanie indeksu (lub jego odpowiednik w jakimkolwiek RDBMS) staje się znacznie droższe wraz ze wzrostem tabeli, aż zdominuje wydajność tabeli.

Wielokrotnie zajmowałem się tym problemem w przypadku tabel o wielkości nawet dwóch miliardów wierszy, 8 TB pamięci masowej i czterdziestu milionów wstawianych wierszy dziennie. Otrzymałem zadanie przeprojektowania systemu, który obejmował porzucenie unikalnego indeksu klucza podstawowego praktycznie w pierwszym kroku. Rzeczywiście, obniżenie tego wskaźnika było konieczne w produkcji po prostu po to, aby odzyskać siły po przerwie, zanim jeszcze zbliżyliśmy się do przeprojektowania. To przeprojektowanie obejmowało znalezienie innych sposobów zapewnienia niepowtarzalności klucza podstawowego i zapewnienia szybkiego dostępu do danych.

Klucze podstawowe są zawsze domyślnie indeksowane.

Klucz podstawowy można zdefiniować w programie SQL Server 2012 przy użyciu programu SQL Server Management Studio lub Transact-SQL. Utworzenie klucza podstawowego automatycznie tworzy odpowiadający mu unikatowy indeks klastrowy lub nieklastrowy.

http://technet.microsoft.com/en-us/library/ms189039.aspx

Tutaj fragment z MSDN :

Po określeniu ograniczenia klucza podstawowego dla tabeli aparat bazy danych wymusza unikalność danych, tworząc unikatowy indeks dla kolumn klucza podstawowego. Indeks ten umożliwia również szybki dostęp do danych, gdy w zapytaniach używany jest klucz podstawowy. Dlatego wybrane klucze podstawowe muszą być zgodne z regułami tworzenia unikatowych indeksów.

PK stanie się indeksem klastrowym, chyba że określisz nieklastrowy

Zadeklarowanie ograniczenia PRIMARY KEYlub UNIQUEpowoduje, że SQL Server automatycznie tworzy indeks.

Unikalny indeks można utworzyć bez dopasowania ograniczenia, ale ograniczenie (klucz podstawowy lub unikalny) nie może istnieć bez unikalnego indeksu.

Stąd utworzenie ograniczenia:

• spowodować utworzenie indeksu o tej samej nazwie
• nie zezwalaj na usunięcie utworzonego indeksu, ponieważ ograniczenie nie może istnieć bez niego

a jednocześnie usunięcie ograniczenia spowoduje usunięcie powiązanego indeksu.

Czy jest więc rzeczywista różnica między a PRIMARY KEYlub UNIQUE INDEX:

• NULLwartości nie są dozwolone w PRIMARY KEY, ale dozwolone w UNIQUEindeksie; i podobnie jak w przypadku operatorów zbiorów (UNION, EXCEPT, INTERSECT), NULL = NULLco oznacza, że ​​możesz mieć tylko jedną wartość, ponieważ dwa NULLs są znalezione jako duplikaty siebie;
• tylko jeden PRIMARY KEYmoże istnieć na tabelę, podczas gdy można utworzyć 999 unikalnych indeksów
• po PRIMARY KEYutworzeniu ograniczenia jest ono tworzone jako klastrowe, chyba że istnieje już indeks klastrowy w tabeli lub NONCLUSTEREDjest używany w jego definicji; kiedy UNIQUEtworzony jest indeks, jest on tworzony tak, NONCLUSTEREDjakby nie był specyficzny CLUSTEREDi taki już nie istnieje;

Uczynienie go kluczem podstawowym powinno również automatycznie utworzyć dla niego indeks.

W SQL Server klucz podstawowy jest generalnie indeksowany automatycznie. To prawda, ale nie gwarantuje to szybszego zapytania. Klucz podstawowy zapewnia doskonałą wydajność, gdy jest tylko 1 pole jako klucz podstawowy. Ale jeśli istnieje wiele pól jako klucz podstawowy, indeks jest oparty na tych polach.

Na przykład: Pola A, B, C są kluczem podstawowym, więc kiedy wykonujesz zapytanie w oparciu o te 3 pola w swojej KLAUZULIE GDZIE, wydajność jest dobra, ALE jeśli chcesz zapytać o pole Tylko C w KLAUZULIE GDZIE, nie uzyska dobrej wydajności. Dlatego, aby uzyskać odpowiednią wydajność, musisz ręcznie zindeksować pole C.

W większości przypadków problem nie występuje, dopóki nie przekroczysz 1 miliona rekordów.

Mam ogromną bazę danych bez (osobnego) indeksu.

Za każdym razem, gdy korzystam z klucza podstawowego, wyniki są, dla wszystkich intensywnych celów, natychmiastowe.

klucze podstawowe są indeksowane automatycznie

możesz tworzyć dodatkowe indeksy za pomocą pk w zależności od zastosowania

• index kod_zip, identyfikator może być przydatny, jeśli często wybierasz według kodu_zip i identyfikatora