Objaśnienie indeksów SQL, Pt. 1

Prawidłowo użyty indeks bazy danych SQL może być tak skuteczny, że może wydawać się magią. Jednak następująca seria ćwiczeń pokaże, że pod spodem logika większości indeksów SQL — i posługiwania się nimi poprawnie — jest dość prosta.

W tej serii, SQL Indexes Explained , omówimy motywacje korzystania z indeksów w celu uzyskania dostępu do danych oraz projektowania indeksów w sposób, w jaki robią to wszystkie nowoczesne systemy zarządzania bazą danych (RDBMS). Następnie przyjrzymy się algorytmom używanym do zwracania danych dla określonych wzorców zapytań.

Nie musisz dużo wiedzieć o indeksach, aby móc śledzić SQL Indexes Explained . Istnieją tylko dwa warunki wstępne:

• Podstawowa znajomość języka SQL
• Podstawowa znajomość dowolnego języka programowania

Główne tematy wyjaśnione w SQL Indexes Explained to:

• Dlaczego potrzebujemy indeksów baz danych SQL; wizualizacja planów wykonania za pomocą indeksów
• Projekt indeksu: które indeksy sprawiają, że zapytanie jest szybkie i wydajne
• Jak możemy napisać zapytanie, aby efektywnie korzystać z indeksów
• Wpływ wykorzystania indeksów w SQL na wydajność odczytu/zapisu
• Pokrycie indeksów
• Partycjonowanie, jego wpływ na czytanie i pisanie oraz kiedy go używać

To nie jest tylko samouczek dotyczący indeksów SQL — to głębokie zagłębienie się w zrozumienie podstawowej mechaniki indeksów.

Dowiemy się, w jaki sposób RDBMS wykorzystuje indeksy, wykonując ćwiczenia i analizując nasze metody rozwiązywania problemów. Nasz materiał do ćwiczeń składa się z Arkuszy Google tylko do odczytu. Aby wykonać ćwiczenie, możesz skopiować Arkusz Google ( Plik → Utwórz kopię ) lub skopiować jego zawartość do własnego Arkusza Google.

W każdym ćwiczeniu pokażemy zapytanie SQL używające składni Oracle. W przypadku dat użyjemy formatu ISO 8601, YYYY-MM-DD .

Ćwiczenie 1: Wszystkie zastrzeżenia klienta

Pierwszym zadaniem — nie rób tego jeszcze — jest znalezienie wszystkich wierszy w arkuszu rezerwacji dla konkretnego klienta systemu rezerwacji hotelowych i skopiowanie ich do własnego arkusza kalkulacyjnego, symulując wykonanie następującego zapytania:

 SELECT * FROM Reservations WHERE ClientID = 12;

Ale chcemy zastosować konkretną metodę.

Podejście 1: bez sortowania, bez filtrowania

Przy pierwszej próbie nie używaj żadnych funkcji sortowania ani filtrowania. Proszę zanotować spędzony czas. Wynikowy arkusz powinien zawierać 73 wiersze.

Ten pseudokod ilustruje algorytm wykonania zadania bez sortowania:

 For each row from Reservations If Reservations.ClientID = 12 then fetch Reservations.*

W tym przypadku musieliśmy sprawdzić wszystkie 841 wierszy, aby zwrócić i skopiować 73 wiersze spełniające warunek.

Podejście 2: Tylko sortowanie

Przy drugiej próbie posortuj arkusz według wartości w kolumnie ClientID . Nie używaj filtrów. Zapisz czas i porównaj go z czasem wykonania zadania bez sortowania danych.

Po posortowaniu podejście wygląda tak:

 For each row from Reservations If ClientID = 12 then fetch Reservations.* Else if ClientID > 12 exit

Tym razem musieliśmy sprawdzić „tylko” 780 wierszy. Gdybyśmy mogli jakoś przeskoczyć do pierwszego rzędu, zajęłoby to jeszcze mniej czasu.

Ale gdybyśmy musieli opracować program do tego zadania, to rozwiązanie byłoby jeszcze wolniejsze niż pierwsze. To dlatego, że najpierw musielibyśmy posortować wszystkie dane, co oznacza, że ​​każdy wiersz musiałby być dostępny przynajmniej raz. Takie podejście jest dobre tylko wtedy, gdy arkusz jest już posortowany w pożądanej kolejności.

Ćwiczenie 2: Liczba rezerwacji rozpoczynających się w danym dniu

Teraz zadaniem jest policzenie liczby meldunków w dniu 16.08.2020:

 SELECT COUNT (*) FROM Reservations WHERE DateFrom = TO_DATE('2020-08-16', 'YYYY-MM-DD')

Skorzystaj z arkusza kalkulacyjnego z ćwiczenia 1. Zmierz i porównaj czas poświęcony na wykonanie zadania z sortowaniem i bez sortowania. Prawidłowa liczba to 91.

Dla podejścia bez sortowania algorytm jest w zasadzie taki sam jak ten z ćwiczenia 1.

Podejście do sortowania jest również podobne do tego z poprzedniego ćwiczenia. Po prostu podzielimy pętlę na dwie części:

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 16th of August 2020. Repeat Read next row Until DateFrom = '2020-08-16' -- Calculate the count While DateFrom = '2020-08-16' Increase the count Read the next row

Ćwiczenie 3: Dochodzenie karne

Inspektor policji prosi o wgląd do listy gości, którzy przybyli do hotelu w dniach 13 i 14 sierpnia 2020 roku.

 SELECT ClientID FROM Reservations WHERE DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND HotelID = 3;

Podejście 1: Sortowanie tylko według daty

Inspektor chce szybko otrzymać listę. Już wiemy, że lepiej posortować tabelę/arkusz kalkulacyjny według daty przyjazdu. Jeśli właśnie skończyliśmy Ćwiczenie 2, mamy szczęście, że stół jest już posortowany. Stosujemy więc podejście podobne do tego z ćwiczenia 2.

Proszę spróbować zanotować godzinę, liczbę wierszy, które musiałeś przeczytać i liczbę pozycji na liście.

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 13th of August 2020. Repeat Read next row Until DateFrom >= '2020-08-13' -- Prepare the list While DateFrom < '2020-08-15' If HotelID = 3 then write down the ClientID Read the next row

Stosując takie podejście, musieliśmy przeczytać 511 wierszy, aby sporządzić listę 46 gości. Gdybyśmy potrafili precyzyjnie zsunąć się w dół, nie musielibyśmy właściwie wykonywać 324 odczytów z cyklu powtórnego tylko po to, aby zlokalizować pierwszy przyjazd 13 sierpnia. Jednak nadal musieliśmy przeczytać ponad 100 wierszy, aby sprawdzić, czy gość przybył do hotelu z HotelID 3 .

Inspektor czekał przez cały ten czas, ale nie byłby zadowolony: zamiast nazwisk gości i innych istotnych danych dostarczyliśmy tylko listę bezsensownych dowodów.

Wrócimy do tego aspektu w dalszej części serii. Najpierw znajdźmy sposób na szybsze przygotowanie listy.

Podejście 2: Posortowane według hotelu, a następnie według daty

Aby posortować wiersze według HotelID , a następnie DateFrom , możemy zaznaczyć wszystkie kolumny, a następnie skorzystać z opcji menu Arkuszy Google Data → Sort range .

 -- Assumption: Sorted according to HotelID and DateFrom -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Find the first arrival at the hotel on 13th of August While HotelID = 3 and DateFrom < '2020-08-13' Read the next row -- Prepare the list While HotelID = 3 and DateFrom < '2020-08-15' Write down the ClientID Read the next row

Musieliśmy pominąć pierwsze 338 przylotów, zanim znaleźliśmy pierwszy w naszym hotelu. Następnie przeszliśmy przez 103 wcześniejsze przyjazdy, aby zlokalizować pierwszy 13 sierpnia. Ostatecznie skopiowaliśmy 46 kolejnych wartości ClientID . Pomogło nam to, że w trzecim kroku udało nam się skopiować blok kolejnych identyfikatorów. Szkoda, że ​​nie mogliśmy jakoś przeskoczyć do pierwszego rzędu z tego bloku.

Podejście 3: Sortowanie tylko według hotelu

Teraz wypróbuj to samo ćwiczenie, używając tylko arkusza kalkulacyjnego uporządkowanego według HotelID .

Algorytm zastosowany do tabeli uporządkowanej tylko według HotelID jest mniej wydajny niż przy sortowaniu według HotelID i DateFrom (w tej kolejności):

 -- Assumption: Sorted according to HotelID -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Prepare the list While HotelID = 3 If DateFrom between '2020-08-13' and '2020-08-14' Write down the ClientID Read the next row

W tym przypadku musimy odczytać wszystkie 166 przyjazdów do hotelu z HotelID 3 i dla każdego sprawdzić, czy DateFrom należy do żądanego przedziału.

Podejście 4: Posortowane według daty, następnie hotelu

Czy to naprawdę ma znaczenie, czy najpierw sortujemy według HotelID , a potem DateFrom czy odwrotnie? Dowiedzmy się: spróbuj najpierw posortować według DateFrom , a następnie według HotelID .

 -- Assumption: Sorted according to DateFrom and HotelID -- Find the first arrival on 13th of August While DateFrom < '2020-08-13' Read the next row --Find the first arrival at the Hotel While HotelID < 3 and DateFrom < '2020-08-15' Read the next row Repeat If HotelID = 3 Write down the ClientID Read the next row Until DateFrom > '2020-08-14' or (DateFrom = '2020-08-14' and HotelID > 3)

Zlokalizowaliśmy pierwszy rząd z odpowiednią datą, a następnie czytaj więcej, aż zlokalizujemy pierwszy przyjazd do hotelu. Następnie dla kilku rzędów spełnione zostały oba warunki, poprawna data i odpowiedni hotel. Jednak po przybyciu do Hotelu 3 mieliśmy przyjazdy do hoteli 4, 5 i tak dalej, na ten sam dzień. Po nich musieliśmy ponownie czytać wiersze na następny dzień dla hoteli 1 i 2, aż mogliśmy odczytać kolejne przyjazdy do naszego interesującego hotelu.

Jak widać, wszystkie podejścia mają jeden kolejny blok danych w środku pełnego zestawu wierszy, reprezentujących częściowo dopasowane dane. Podejścia 2 i 4 są jedynymi, w których logika pozwala nam całkowicie zatrzymać algorytm, zanim dojdziemy do końca dopasowań częściowych.

Podejście 4 ma w pełni dopasowane dane w dwóch blokach, ale Podejście 2 jest jedynym, w którym wszystkie docelowe dane znajdują się w jednym kolejnym bloku.

Z liczb widać, że Podejście 2 ma w tym przypadku najwięcej zalet.

Wyjaśnienie indeksów SQL: wnioski i co dalej

Wykonanie tych ćwiczeń powinno wyjaśnić następujące kwestie:

1. Czytanie z odpowiednio posortowanej tabeli jest szybsze.
2. Jeśli tabela nie jest jeszcze posortowana, sortowanie zajmuje więcej czasu niż czytanie z nieposortowanej tabeli.
3. Znalezienie sposobu na przejście do pierwszego wiersza pasującego do warunku wyszukiwania w posortowanej tabeli zaoszczędziłoby wiele odczytów.
4. Pomocne byłoby wcześniejsze posortowanie tabeli.
5. Pomocne byłoby zachowanie posortowanych kopii tabeli dla najczęstszych zapytań.

Teraz posortowana kopia tabeli brzmi prawie jak indeks bazy danych. W następnym artykule w SQL Indexes Explained omówiono podstawową implementację indeksu. Dziękuje za przeczytanie!