SQL best practices

26 grudnia, 2021Articles

jak opisano w planach wykonywania zapytań, kompilator Cloud Spanner ’ SQL przekształca instrukcję SQL w plan wykonywania zapytań, który jest wykorzystywany do uzyskania wyników zapytania. Ta strona opisuje najlepsze praktyki konstruowania instrukcji SQL, aby pomóc Cloud Spanner znaleźć wydajne plany wykonawcze.

przykładowe instrukcje SQL pokazane na tej stronie wykorzystują przykładowy schemat poniżej:

CREATE TABLE Singers ( SingerId INT64 NOT NULL, FirstName STRING(1024), LastName STRING(1024), SingerInfo BYTES(MAX),) PRIMARY KEY (SingerId);CREATE TABLE Albums ( SingerId INT64 NOT NULL, AlbumId INT64 NOT NULL, AlbumTitle STRING(MAX), ReleaseDate DATE,) PRIMARY KEY (SingerId, AlbumId),INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

aby uzyskać pełne odniesienie do SQL, zapoznaj się ze składnią,funkcjami i operatorami oraz strukturą i składnią leksykalną.

użyj parametrów zapytania, aby przyspieszyć często wykonywane zapytania

sparametryzowane zapytania są techniką wykonywania zapytań, która oddziela querystring od wartości parametrów zapytania. Załóżmy na przykład, że Twoja aplikacja musi pobierać piosenkarzy, którzy wydali albumy z określonymi tytułami w danym roku. Możesz napisać instrukcję SQL, jak w poniższym przykładzie, aby pobrać wszystkie albumy zatytułowane „Love”, które zostały wydane w 2017:

SELECT a.SingerIdFROM Albums AS aWHERE a.AlbumTitle = 'Love' AND a.ReleaseDate >= '2017-01-01'

w innym zapytaniu możesz zmienić wartość tytułu albumu na „Peace”:

SELECT a.SingerIdFROM Albums AS aWHERE a.AlbumTitle = 'Peace' AND a.ReleaseDate >= '2017-01-01'

jeśli Twoja aplikacja musi wykonać wiele zapytań podobnych do tego,w których tylko literalna wartość zmienia się w kolejnych zapytaniach, powinieneś użyć symbolu zastępczego aparameter dla tej wartości. Otrzymane zapytanie parametryczne może zostać usunięte i ponownie użyte, co zmniejsza koszty kompilacji.

na przykład, przepisane zapytanie poniżej zastępuje Love parametrem o nazwietitle:

SELECT a.SingerIdFROM Albums AS aWHERE a.AlbumTitle = @title AND a.ReleaseDate >= '2017-01-01'

uwagi na temat użycia parametru zapytania:

odniesienie do parametru w zapytaniu wykorzystuje znak @, po którym następuje nazwa parametru, który może zawierać dowolną kombinację liter, cyfr i znaków.
parametry mogą pojawić się wszędzie tam, gdzie oczekiwana jest literalna wartość.
ta sama nazwa parametru może być użyta więcej niż raz w jednym SQLstatement.
Określ parametr zapytania i wartość, z którą ma być powiązany, w polu paramsinterfejsu API żądaniaExecuteSQL lub ExecuteStreamingSQL.
dowiedz się więcej o składni parametru zapytania syntaxinSQL struktura leksykalna i składnia.

podsumowując, parametry zapytania korzystają z wykonania zapytania w następujący sposób:

wstępnie zoptymalizowane plany: zapytania wykorzystujące parametry mogą być wykonywane szybciej oneach wywołania, ponieważ parametryzacja ułatwia forcloud Spanner buforować plan wykonania.
uproszczony skład zapytań: nie musisz opuszczać wartości łańcuchów podczas ich przedstawiania w parametrach zapytania. Parametry zapytania również zmniejszają ryzyko błędów syntax.
bezpieczeństwo: parametry zapytania zwiększają bezpieczeństwo zapytań, chroniąc Cię przed różnymi atakami SQL injection. Ochrona ta jest szczególnie ważna dla zapytań konstruowanych na podstawie danych wejściowych użytkownika.

dowiedz się, jak Cloud Spanner wykonuje zapytania

Cloud Spanner umożliwia odpytywanie baz danych za pomocą deklaratywnych oświadczeń SQL, które określają, jakie dane chcesz pobrać. Jeśli chcesz również zrozumieć, w jaki sposób Cloud Spanner uzyskuje wyniki, powinieneś użyć planów wykonywania zapytań. Aquery execution plan wyświetla koszt obliczeniowy związany z każdym krokiem zapytania. Korzystając z tych kosztów, możesz debugować problemy z wydajnością zapytań i optymalizować swoje zapytanie.

możesz pobrać plany wykonywania zapytań za pośrednictwem konsoli w chmurze lub bibliotek klienckich.

aby uzyskać plan zapytań za pomocą konsoli w chmurze:

Otwórz stronę instancje Cloud Spanner.

przejdź do Cloud Spanner instances
kliknij nazwę instancji Cloud Spanner i bazy danych, do której chcesz wysłać zapytanie.
Kliknij Zapytanie.
wpisz zapytanie w polu tekstowym, a następnie kliknij Uruchom zapytanie.
Kliknij Wyjaśnienie.
Konsola w chmurze wyświetla Plan visualexecution dla Twojego zapytania.

aby uzyskać więcej informacji na temat planów wizualnych, zobacz strojenie zapytania za pomocą wizualizatora planu zapytań.

aby uzyskać pełny opis planu zapytań, zobacz plany wykonania zapytania.

użyj drugorzędnych indeksów, aby przyspieszyć typowe zapytania

podobnie jak inne relacyjne bazy danych, Cloud Spanner oferuje drugorzędne indeksy, których możesz użyć do pobierania danych za pomocą instrukcji SQL lub interfejsu odczytu cloud Spanner. Bardziej powszechnym sposobem pobierania danych z anindex jest użycie interfejsu zapytań SQL. Użycie indeksu wtórnego w zapytaniu SQL pozwala określić, w jaki sposób chcesz uzyskać wyniki Cloud Spanner.Podanie indeksu wtórnego może przyspieszyć wykonywanie zapytań.

Załóżmy na przykład, że chciałeś pobrać identyfikatory wszystkich śpiewaków z nazwiskiem. Jednym ze sposobów napisania takiego zapytania SQL jest:

SELECT s.SingerIdFROM Singers AS sWHERE s.LastName = 'Smith';

to zapytanie zwróci oczekiwane wyniki, ale zwrócenie wyników może zająć trochę czasu. Czas zależy od liczby wierszy w tabeli Singers i od tego, ile spełnia predykatWHERE s.LastName = 'Smith'. Jeśli nie ma drugorzędnego indeksu, który zawiera kolumnę LastName do odczytania, Plan zapytań odczytałby tabelę Singers, aby znaleźć wiersze pasujące do predykatu. Odczyt całej tabeli nazywa się pełnym skanowaniem tabeli, a Pełne skanowanie tabeli jest kosztownym sposobem uzyskania wyników, jeśli tabela zawiera tylko niewielki procent Singers o tej nazwie.

możesz poprawić wydajność tego zapytania, definiując indeks wtórny w kolumnie nazwisko:

CREATE INDEX SingersByLastName on Singers (LastName);

ponieważ indeks wtórny SingersByLastName zawiera indeksowaną kolumnę tabeli LastName i kolumnę klucza podstawowego SingerId, Cloud Spanner może wykryć wszystkie dane z znacznie mniejszej tabeli indeksów zamiast skanować pełną tabelę Singers.

w tym scenariuszu Cloud Spanner prawdopodobnie automatycznie użyje secondaryindex SingersByLastName podczas wykonywania zapytania. Jednak najlepiej jest pośrednio powiedzieć Cloud Spanner, aby używał tego indeksu, określając dyrektywę indeksu w klauzuli FROM :

SELECT s.SingerIdFROM [email protected]{FORCE_INDEX=SingersByLastName} AS sWHERE s.LastName = 'Smith';

przypuśćmy, że oprócz nazwiska chciałeś podać imię wokalisty. Nawet jeśli kolumna FirstName nie jest zawarta w indeksie, powinieneś określić dyrektywę index jak wcześniej:

SELECT s.SingerId, s.FirstNameFROM [email protected]{FORCE_INDEX=SingersByLastName} AS sWHERE s.LastName = 'Smith';

korzystanie z indeksu nadal przynosi korzyści wydajnościowe, ponieważ funkcja Spannerdoes w chmurze nie musi wykonywać pełnego skanowania tabeli podczas wykonywania planu zapytań. Zamiast tego wybiera podzbiór wierszy, które spełniają predykat z indeksu SingersByLastName, a następnie wyszukuje z tabeli bazowej Singers, aby pobrać pierwszą nazwę tylko dla tego podzbioru wierszy.

jeśli chcesz uniknąć konieczności pobierania wierszy z tabeli bazowej w chmurze, możesz opcjonalnie zapisać kopię kolumny FirstName w samym indeksie:

CREATE INDEX SingersByLastName on Singers (LastName) STORING (FirstName);

Korzystanie z klauzuli STORING kosztuje dodatkowe miejsce, ale zapewnia następujące korzyści dla zapytań i wywołań odczytu za pomocą indeksu:

zapytania SQL wykorzystujące kolumny index I select zapisane w klauzuli STORING nie wymagają dodatkowego połączenia z tabelą bazową.
odczytywanie wywołań, które używają indeksu, może odczytywać kolumny zapisane w klauzuli STORING.

poprzednie przykłady ilustrują, jak indeksy wtórne mogą przyspieszyć zapytania, gdy Wiersze wybrane przez klauzulę WHERE zapytania mogą być szybko zidentyfikowane za pomocą indeksu wtórnego. Innym scenariuszem, w którym wtórne indeksy mogą oferować korzyści z wydajności, jest dla niektórych zapytań, które zwracają zamówione wyniki. Na przykład załóżmy, że chcesz pobrać wszystkie tytuły albumów i ich daty wydania i zwrócić je w kolejności rosnącej od daty wydania i malejącej według tytułu albumu. Możesz napisać takie zapytanie SQL:

SELECT a.AlbumTitle, a.ReleaseDateFROM Albums AS aORDER BY a.ReleaseDate, a.AlbumTitle DESC;

bez wtórnego indeksu, to zapytanie wymaga potencjalnie kosztownego sortowania w planie wykonania. Możesz przyspieszyć wykonywanie zapytań, definiując ten indeks:

CREATE INDEX AlbumsByReleaseDateTitleDesc on Albums (ReleaseDate, AlbumTitle DESC);

następnie przepisz zapytanie, aby użyć indeksu wtórnego:

SELECT a.AlbumTitle, a.ReleaseDateFROM [email protected]{FORCE_INDEX=AlbumsByReleaseDateTitleDesc} AS aORDER BY a.ReleaseDate, a.AlbumTitle DESC;

należy pamiętać, że to zapytanie i definicja indeksu spełniają oba poniższe kryteria:

lista kolumn w klauzuli ORDER BY jest prefiksem listy kluczy indeksowych.
wszystkie kolumny tabeli użyte w zapytaniu są objęte indeksem.

ponieważ oba te warunki są spełnione, powstały Plan zapytań usuwa krok sortowania i wykonuje się szybciej.

chociaż indeksy drugorzędne mogą przyspieszyć typowe zapytania, należy pamiętać, że indeksy drugorzędne mogą dodawać opóźnienia do operacji zatwierdzania, ponieważ każdy indeks drugorzędny zazwyczaj wymaga użycia dodatkowego węzła w każdym zatwierdzeniu. Formost obciążenia, mając kilka drugorzędnych indeksów jest w porządku. Należy jednak zastanowić się, czy zależy Ci bardziej na opóźnieniach odczytu lub zapisu i zastanowić się, które operacje są najbardziej krytyczne dla obciążenia pracą. Powinieneś również przeprowadzić benchmark yourworkload, aby upewnić się, że działa zgodnie z oczekiwaniami.

aby uzyskać pełne odniesienie do indeksów wtórnych, patrz indeksy wtórne.

Pisz wydajne zapytania do wyszukiwania kluczy zakresu

powszechnym zastosowaniem zapytania SQL jest odczytywanie wielu wierszy z Cloud Spanner na podstawie listy znanych kluczy.

Oto najlepsze praktyki pisania wydajnych zapytań podczas pobierania danych przez zakres kluczy:

jeśli lista kluczy jest rzadka i nie sąsiaduje, użyj parametrów zapytania iUNNEST do skonstruowania zapytania.

na przykład, jeśli Twoja lista kluczy To {1, 5, 1000}, napisz zapytanie w ten sposób:
```
SELECT *FROM Table AS tWHERE t.Key IN UNNEST (@KeyList)
```
uwagi:
- operator array UNNEST spłaszcza tablicę aninput do rzędów elementów.
- @KeyList jest parametrem zapytania, który może przyspieszyć Twoje zapytanie, jak omówiono w poprzednich najlepszych praktykach.
jeśli lista kluczy jest sąsiadująca i mieści się w zakresie, określ dolną i wyższą granicę zakresu kluczy w klauzuli WHERE.

na przykład, jeśli Twoja lista kluczy To {1,2,3,4,5}, skonstruuj zapytanie tak, jak to:
```
SELECT *FROM Table AS tWHERE t.Key BETWEEN @min AND @max
```
gdzie @min i @max są parametrami zapytania, które są powiązane odpowiednio z wartościami 1 i 5.

zauważ, że to zapytanie jest bardziej wydajne tylko wtedy, gdy klucze w zakresie kluczy są ustawione. Innymi słowy, jeśli Twoja lista kluczy To {1, 5, 1000}, nie powinieneś określać dolnych i wyższych granic, jak w poprzednim zapytaniu, ponieważ zapytanie multiting przeskanowałoby każdą wartość z zakresu od 1 do 1000.

pisanie wydajnych zapytań dla połączeń

operacje łączenia mogą być kosztowne. Dzieje się tak, ponieważ JOINs może znacznie zwiększyć liczbę wierszy, które twoje zapytanie musi przeskanować, co powoduje spowolnienie zapytań. Oprócz technik, do których jesteś przyzwyczajony w innych relacyjnych bazach danych w celu optymalizacji zapytań join, oto kilka najlepszych praktyk dla wydajniejszego dołączania podczas korzystania z Cloud Spanner SQL:

jeśli to możliwe, łącz dane w tabelach z przeplotem za pomocą klucza podstawowego. Na przykład:
```
SELECT s.FirstName, a.ReleaseDateFROM Singers AS s JOIN Albums AS a ON s.SingerId = a.SingerId;
```
wiersze w tabeli z przeplotem Albums są gwarantowane w tych samych podziałach, co wiersz nadrzędny w Singers, co omówiono w schemacie i modelu danych. W związku z tym JOINs może zostać ukończone lokalnie bez wysyłania dużej ilości danych przez sieć.
użyj dyrektywy join, jeśli chcesz wymusić kolejność JOIN. Na przykład:
```
SELECT *FROM Singers AS s [email protected]{FORCE_JOIN_ORDER=TRUE} Albums AS aON s.SingerId = a.SingeridWHERE s.LastName LIKE '%x%' AND a.AlbumTitle LIKE '%love%';
```
dyrektywa join @{FORCE_JOIN_ORDER=TRUE} nakazuje Cloud Spanner używać kolejności join określonej w zapytaniu (to znaczy Singers JOIN Albums, a nieAlbums JOIN Singers). Zwracane wyniki są takie same, niezależnie od tego, który z nich wybierze Cloud Spanner. Możesz jednak użyć tego joindirective, jeśli zauważysz w planie zapytań, że Cloud Spanner zmienił kolejność łączenia i spowodował niepożądane wyniki, takie jak większe wyniki pośrednie lub stracił możliwości wyszukiwania wierszy.
użyj dyrektywy join, aby wybrać implementację join. Wybór odpowiedniego joinalgorithm dla zapytania może poprawić opóźnienie, zużycie pamięci, orboth. To zapytanie demonstruje składnię dla dyrektywy USINGA JOIN z podpowiedzią JOIN_METHOD, aby wybrać HASH JOIN:
```
SELECT *FROM Singers s [email protected]{JOIN_METHOD=HASH_JOIN} Albums AS aON a.SingerId = a.SingerId
```
jeśli używasz klauzuli HASH JOIN lub APPLY JOIN i jeśli masz klauzulę WHERE, która jest wysoce selektywna po jednej stronie JOIN, umieść tabelę, która tworzy najmniejszą liczbę wierszy jako pierwszą tabelę w klauzuli FROM join. Dzieje się tak dlatego, że obecnie w HASH JOIN, Cloud Spanner zawsze zaznacza stół po lewej stronie jako build, A stolik po prawej asprobe. Podobnie, dla APPLY JOIN, Cloud Spanner wybiera asouter po lewej stronie i stolik po prawej stronie jako wewnętrzny. Zobacz więcej informacji o typach join: Hash join I Apply join.

unikaj dużych odczytów w transakcjach Odczyt-Zapis

transakcje Odczyt-Zapis pozwalają na sekwencję zero lub więcej zapytań SQL i mogą zawierać zestaw mutacji, przed wywołaniem commit. Aby zachować spójność danych, Cloud Spanneracquires locks podczas odczytu i zapisu wierszy w tabelach i indeksach (więcej szczegółów na temat blokowania w życiu odczytów i zapisów).

ze względu na sposób, w jaki blokowanie działa w Cloud Spanner, wykonanie odczytu lub SQLquery, które odczytuje dużą liczbę wierszy (na przykład SELECT * FROM Singers) oznacza, że żadne inne transakcje nie mogą zapisywać do przeczytanych wierszy, dopóki Twoja transakcja nie zostanie zatwierdzona lub przerwana. Ponadto, ponieważ Twoja transakcja przetwarza dużą liczbę wierszy, prawdopodobnie zajmie to dłużej niż transakcja, która odczytuje znacznie mniejszy zakres wierszy (na przykład SELECTLastName FROM Singers WHERE SingerId = 7), co jeszcze bardziej pogłębia problem i zmniejsza przepustowość systemu.

dlatego należy unikać dużych odczytów (na przykład: pełna tabela skanuje lub łączy operacje) wewnątrz transakcji, chyba że chcesz zaakceptować niższą przepływność zapisu. W niektórych przypadkach następujący wzór może przynieść lepsze wyniki:

wykonaj duży odczyt wewnątrz transakcji tylko do odczytu. (Notatak transakcje tylko do odczytu nie używają blokad, a tym samym pozwalają na większą przepustowość.)
jeśli musisz wykonać jakiekolwiek przetwarzanie danych, które właśnie przeczytałeś, zrób to.
rozpocznij transakcję odczytu i zapisu.
sprawdź, czy ważne wiersze nie zmieniły wartości od czasu wykonania transakcji tylko do odczytu w kroku 1.
1. jeśli wiersze się zmieniły, Cofnij transakcję i rozpocznij ponownie na kroku 1.
2. jeśli wszystko wygląda dobrze, zgłoś swoje mutacje.

jednym ze sposobów na uniknięcie dużych odczytów wewnątrz operacji read-writetransactions jest przyjrzenie się planom wykonania generowanym przez Twoje zapytania.

użyj ORDER BY, aby zapewnić kolejność wyników SQL

jeśli spodziewasz się określonego porządku dla wyników zapytania SELECT, powinieneś wyraźnie dołączyć klauzulę ORDER BY. Na przykład: jeśli chcesz ustawić wszystkie Śpiewniki w kolejności klucza podstawowego, użyj tego zapytania:

SELECT * FROM SingersORDER BY SingerId;

należy pamiętać, że Cloud Spanner gwarantuje zamawianie wyników tylko wtedy, gdy w zapytaniu znajduje się ORDER BY clauseise. Innymi słowy, rozważmy to zapytanie bez ORDERBY:

SELECT * FROM Singers;

Cloud Spanner nie gwarantuje, że wyniki tego zapytania będą w pierwotnej kolejności kluczy. Ponadto kolejność wyników może ulec zmianie w dowolnym momencie i nie jest gwarantowana, że będzie spójna od inwokacji do inwokacji.

użyj STARTS_WITH zamiast LIKE, aby przyspieszyć sparametryzowane zapytania SQL

ponieważ Cloud Spanner nie ocenia sparametryzowanych wzorcówLIKE do czasu wykonania, Cloud Spanner musi odczytać wszystkie wiersze i obliczyć je na podstawie wyrażenia LIKE, aby odfiltrować wiersze, które nie pasują.

w przypadkach, gdy wzorzec LIKE szuka dopasowań, które znajdują się na początku avalue, a kolumna jest indeksowana, użyj STARTS_WITHzamiast LIKE. Dzięki temu Cloud Spanner może skuteczniej optymalizować plan realizacji zapytań.

Nie polecam:

SELECT a.AlbumTitle FROM Albums aWHERE a.AlbumTitle LIKE @like_clause;

Polecane:

SELECT a.AlbumTitle FROM Albums aWHERE STARTS_WITH(a.AlbumTitle, @prefix);