Czy klucz obcy poprawia wydajność zapytań?

Załóżmy, że mam 2 tabele, produkty i kategorie produktów. Obie tabele mają relację na CategoryId. I to jest pytanie.

SELECT p.ProductId, p.Name, c.CategoryId, c.Name AS Category
FROM Products p
INNER JOIN ProductCategories c ON p.CategoryId = c.CategoryId
WHERE c.CategoryId = 1;

Kiedy tworzę plan wykonania, tabela ProductCategories przeprowadza przeszukiwanie indeksu klastra, co jest zgodne z oczekiwaniami. Ale w przypadku produktów tabelowych wykonuje skanowanie indeksu klastra, co budzi wątpliwości. Dlaczego FK nie pomaga poprawić wydajności zapytań?

Więc muszę utworzyć indeks na Products.CategoryId. Kiedy ponownie tworzę plan wykonania, obie tabele wykonują przeszukiwanie indeksu. Szacowany koszt poddrzewa jest znacznie obniżony.

Moje pytania to:

1. Oprócz FK pomaga w ograniczaniu relacji, czy ma jakąś inną użyteczność? Czy poprawia wydajność zapytań?

2. Czy powinienem utworzyć indeks dla wszystkich kolumn FK (takich jak Products.CategoryId) we wszystkich tabelach?

Klucze obce są referencyjnym narzędziem integralności, a nie narzędziem wydajności. Przynajmniej w SQL Server, utworzenie FK nie tworzy skojarzonego indeksu i powinieneś utworzyć indeksy dla wszystkich pól FK, aby skrócić czas wyszukiwania.

Klucze obce mogą poprawić (i zaszkodzić) wydajność

1. Jak stwierdzono tutaj: Klucze obce zwiększają wydajność

2. Należy zawsze tworzyć indeksy w kolumnach FK, aby zmniejszyć liczbę wyszukiwań. SQL Server nie robi tego automatycznie.

Edytować

Ponieważ łącze wydaje się być teraz martwe (chwała dla Chrisa za zauważenie) , poniżej przedstawiono istotę tego, dlaczego klucze obce mogą poprawić (i zaszkodzić) wydajność.

Czy klucz obcy może poprawić wydajność

Ograniczenie klucza obcego poprawia wydajność w momencie odczytu danych, ale jednocześnie spowalnia wydajność w momencie wstawiania / modyfikowania / usuwania danych.

W przypadku czytania zapytania optymalizator może użyć ograniczeń klucza obcego do tworzenia bardziej wydajnych planów zapytań, ponieważ ograniczenia klucza obcego są wstępnie zadeklarowanymi regułami. Zwykle wiąże się to z pominięciem części planu zapytania, ponieważ na przykład optymalizator może zobaczyć, że ze względu na ograniczenie klucza obcego nie jest konieczne wykonywanie tej określonej części planu.

Klucz obcy to koncepcja DBMS zapewniająca integralność bazy danych.

Wszelkie implikacje / ulepszenia dotyczące wydajności będą specyficzne dla używanej technologii baz danych i będą drugorzędne w stosunku do celu klucza obcego.

W SQL Server dobrą praktyką jest upewnienie się, że wszystkie klucze obce mają co najmniej indeks nieklastrowy.

Mam nadzieję, że to wszystko wyjaśni, ale możesz poprosić o więcej szczegółów.

Najlepszym sposobem na wydajność jest używanie indeksów na często używanych polach. Jeśli używasz programu SQL Server, możesz użyć profilera do profilowania określonej bazy danych i pobrać plik, który wyprowadza, i użyć kreatora strojenia, aby otrzymać zalecenia dotyczące miejsca umieszczenia indeksów. Lubię też używać profilera do opróżniania długo działających procedur składowanych, mam listę dziesięciu najgorszych przestępców, którą publikuję co tydzień, aby ludzie byli uczciwi: D.

Możesz go użyć, aby zwiększyć wydajność zapytania. Pozwala to na restrukturyzację zapytań w SQL Server, aby używać sprzężenia zewnętrznego zamiast wewnętrznego, co eliminuje konieczność sprawdzania przez serwery sql, czy w kolumnie jest wartość null. Nie musisz umieszczać tego kwalifikatora, ponieważ relacja z kluczem obcym już to wymusza.

Więc to:

    select p.ProductId, p.Name, c.CategoryId, c.Name AS Category 
from Products p inner join ProductCategories c on p.CategoryId = c.CategoryIdwhere c.CategoryId = 1;

Staje się tym:

SELECT p.ProductId, p.Name, c.CategoryId, c.Name AS Category 
FROM ProductCategories c 
LEFT OUTER JOIN Products P ON
c.CategoryId = p.CategoryId 
WHERE c.CategoryId = 1;

Niekoniecznie zapewnia to dużą wydajność w przypadku małych zapytań, ale gdy tabele stają się duże, może być bardziej wydajne.

W przypadku MySQL 5.7 z pewnością może zadziwiająco przyspieszyć zapytania obejmujące wielokrotne łączenia!

Użyłem „wyjaśnienia”, aby zrozumieć moje zapytanie i stwierdziłem, że dołączam do 4-5 tabel - w których nie używano żadnych kluczy. Nie zrobiłem nic, tylko dodałem klucz obcy do tych tabel, a wynikiem było 90% skrócenie czasu ładowania. Zapytania, które trwały dłużej niż 5 sekund, zajmują teraz 500 ms lub mniej.

To OGROMNA poprawa!

ORAZ, jak wspominali inni, otrzymujesz dodatkową premię w postaci zapewnienia integralności relacji.

Poza tym zapewnienie integralności referencyjnej ma również swoje zalety w zakresie wydajności. Efekt drugiego rzędu zapewnia, że ​​tabele z kluczem obcym są „aktualne” z tabelą obcą. Załóżmy, że masz tabelę użytkowników i tabelę komentarzy i robisz statystyki dotyczące tabeli komentarzy. Prawdopodobnie jeśli trwale usuniesz użytkownika, nie chcesz już też jego komentarzy.

Dodanie klucza obcego do tabeli nie poprawi wydajności, po prostu mówiąc, że jeśli wstawiasz rekord do bazy danych tabeli ProductCategories, spróbujesz znaleźć kolumnę klucza obcego o wartości istniejącej w wartości klucza podstawowego tabeli produktów, to wyszukiwanie, Operacja jest narzucona na bazę danych za każdym razem, gdy dodajesz nowy wpis w tabeli ProductCategories. Zatem dodanie klucza obcego nie poprawi wydajności bazy danych, ale zadba o integralność bazy danych. Tak, poprawi to wydajność twojej bazy danych, jeśli sprawdzasz integralność za pomocą klucza obcego zamiast uruchamiania wielu zapytań w celu sprawdzenia, czy rekord istnieje w bazie danych w twoim programie.

Nie wiem zbyt wiele o serwerze SQL, ale w przypadku Oracle posiadanie kolumny klucza obcego zmniejsza wydajność ładowania danych. Dzieje się tak, ponieważ baza danych musi sprawdzać integralność danych dla każdej wstawki. I tak, jak już zostało wspomniane, posiadanie indeksu w kolumnie klucza obcego jest dobrą praktyką.

Począwszy od SQL Server 2008, klucze obce mogą wpływać na wydajność, wpływając na sposób, w jaki silnik bazy danych wybiera optymalizację zapytania. Zobacz temat Star Join Heuristics w następującym artykule: https://technet.microsoft.com/en-us/library/2008.04.dwperformance.aspx