Heading

W tym artykule skupimy się na najprostszych i najlepszych wskazówkach, które programiści mogą zastosować w codziennej pracy nad wydajnością SQL. W tym artykule skupimy się na wydajności zapytań MySQL, ale te same koncepcje mogą być stosowane do wielu innych relacyjnych baz danych.

Teraz bardziej niż kiedykolwiek, inżynierowie oprogramowania muszą posiadać szeroką wiedzę w zakresie strojenia wydajności SQL.
Zmiana następuje zarówno w małych startupach, jak i dużych przedsiębiorstwach. Obecnie to programiści piszą zapytania SQL i warstwę dostępu do bazy danych.

Nie ma znaczenia, czy używasz warstwy abstrakcji bazy danych (Hibernate, JOOQ, Entity Framework, Sqlalchemy, Django lub innych), czy piszesz natywne zapytania SQL, w końcu będziesz musiał zmierzyć się z dostrojeniem zapytań wysyłanych do bazy danych.

Co więc możesz zrobić aby zoptymalizować swoje zapytania SQL?

Twórz indeksy, ale rób to mądrze

Indeksowanie jest prawdopodobnie najważniejszą częścią procesu dostrajania zapytań. Dlatego też, najpierw upewnij się, że znasz różne aspekty, które powinieneś rozważyć przy wyborze optymalnych indeksów dla swojej bazy danych.

Przy wyborze indeksów do utworzenia, powinieneś zwrócić szczególną uwagę na klauzulę WHERE oraz JOIN tabeli, ponieważ te stwierdzenia zawierają krytyczne, indeksowalne części zapytania.

Ponadto, główne wąskie gardła mogą pochodzić z części GROUP BY oraz ORDER BY. Potencjalną czkawką może być to, że w niektórych przypadkach nie będziesz w stanie ich zaindeksować, co wyjaśniliśmy tutaj. W związku z tym, być może będziesz musiał ponownie przemyśleć projekt zapytania przed utworzeniem indeksów, aby upewnić się, że piszesz świetne zapytania, ale także piszesz zapytania, które można indeksować.

Gdy już zrozumiesz indeksowanie dla jednego zapytania, nie zatrzymuj się tam. Poszerz swój widok i przyjrzyj się innym ważnym zapytaniom w twojej aplikacji. Upewnij się, że łączysz indeksy kiedy to tylko możliwe i usuwasz indeksy, które nie są używane. Spojrzenie na cały zakres aplikacji zawsze będzie lepsze niż spojrzenie na zakres pojedynczego zapytania.

Powinno się również pamiętać, że tworzenie większej ilości indeksów niż jest to potrzebne może również przynieść odwrotny skutek, ponieważ mogą one spowolnić operacje zapisu (takie jak instrukcje INSERT / UPDATE). Tak więc twórz indeksy aby zoptymalizować wydajność zapytań SQL, ale rób to mądrze.

Nie stawaj na drodze indeksom

Dużo klientów zwraca się do nas z pytaniem „dlaczego baza danych nie używa mojego indeksu?”. Cóż, to świetne pytanie, z nieskończoną ilością możliwych odpowiedzi. Ale w tym artykule postaramy się omówić kilka typowych scenariuszy, które często widzimy, więc miejmy nadzieję, że przydadzą Ci się one w Twoim własnym przypadku użycia.

Przykład #1 – Unikaj zawijania indeksowanych kolumn funkcjami

Rozważmy to zapytanie, które zlicza liczbę hot dogów zakupionych w USA w 2018 roku. Na wszelki wypadek, gdybyś był ciekawy, 18 000 000 000 hot dogów zostało sprzedanych w USA w 2018 roku.

SELECT COUNT(*)FROM us_hotdog_purchasesWHERE YEAR(purchase_time) = ‘2018’

Jak widać, używamy funkcji YEAR, aby złapać część roku z kolumny purchase_time. To wywołanie funkcji uniemożliwi bazie danych użycie indeksu do przeszukiwania kolumny purchase_time, ponieważ zaindeksowaliśmy wartość purchase_time, ale nie wartość zwracaną przez YEAR(purchase_time).

Aby pokonać to wyzwanie i dostroić to zapytanie SQL, możesz zaindeksować wynik funkcji, używając Generated Columns, które są dostępne od MySQL 5.7.5.

Innym rozwiązaniem może być znalezienie alternatywnego sposobu na napisanie tego samego zapytania, bez użycia wywołania funkcji. W tym przykładzie, możemy przekształcić ten warunek w dwukierunkowy warunek zakresu, który zwróci te same wyniki:

SELECT COUNT(*)FROM us_hotdog_purchasesWHERE purchased_at >= '2018-01-01' AND purchased_at < '2019-01-01'

Przykład #2 – unikaj warunków OR

Rozważmy to zapytanie, które wybiera ilość postów na Facebooku opublikowanych po sylwestrze, lub opublikowanych przez użytkownika o imieniu Mark.

SELECT COUNT(*)FROM fb_postsWHERE username = ‘Mark’ OR post_time > ‘2018-01-01’

Posiadanie indeksu zarówno na kolumnie username jak i post_time może wydawać się pomocne, ale w większości przypadków, baza danych nie będzie z niego korzystać, przynajmniej nie w całości. Powodem będzie połączenie pomiędzy dwoma warunkami – operator OR, który sprawia, że baza danych pobiera wyniki każdej części warunku osobno.

Alternatywnym sposobem spojrzenia na to zapytanie może być 'rozdzielenie' warunku OR i 'połączenie' go za pomocą klauzuli UNION. Ta alternatywa pozwoli na indeksowanie każdego z warunków osobno, więc baza danych użyje indeksów do wyszukiwania wyników, a następnie połączy wyniki za pomocą klauzuli UNION.

SELECT …FROM …WHERE username = ‘Mark’ UNIONSELECT …FROM …WHERE post_time > ‘2018-01-01’

Pamiętaj, że jeśli nie masz nic przeciwko zduplikowanym rekordom w zbiorze wyników, możesz również użyć UNION ALL (który będzie działał lepiej niż domyślny UNION DISTINCT).

Przykład #3 – Unikaj sortowania z mieszanym porządkiem

Rozważmy to zapytanie, które wybiera wszystkie posty z Facebooka i sortuje je według nazwy użytkownika w porządku rosnącym, a następnie według daty postu w porządku malejącym.

SELECT username, post_typeFROM fb_postsORDER BY username ASC , post_type DESC

MySQL (i wiele innych relacyjnych baz danych), nie może używać indeksów podczas sortowania z mieszaną kolejnością (zarówno ASC jak i DESC w tej samej klauzuli ORDER BY). Zmieniło się to wraz z pojawieniem się funkcjonalności odwróconych indeksów i MySQL 8.x.

Co więc możesz zrobić, jeśli nie zaktualizowałeś jeszcze MySQL do najnowszej wersji? Po pierwsze, zalecamy ponowne rozważenie sortowania mieszanego. Czy naprawdę go potrzebujesz? Jeśli nie, unikaj go.

Więc zdecydowałeś, że go potrzebujesz, lub Twój menedżer produktu powiedział: „Nie ma mowy, żebyśmy sobie bez niego poradzili”? Inną opcją jest użycie Generowanych kolumn (dostępnych w MySQL 5.7.5+), aby stworzyć odwróconą kolumnę i sortować na niej zamiast na oryginalnej. Jako przykład, załóżmy, że sortujesz na kolumnie numerycznej, możesz utworzyć wygenerowaną kolumnę z ujemną wartością numeryczną, która koreluje z oryginalną liczbą i sortować na tej nowej kolumnie w odwrotnej kolejności. W ten sposób wszystkie kolumny będą miały taką samą kolejność sortowania w klauzuli ORDER BY, ale sortowanie odbędzie się w sposób pierwotnie zdefiniowany przez wymagania twojego produktu.

Ostatnie potencjalne rozwiązanie nie zawsze będzie opcją, więc ostatnią deską ratunku będzie aktualizacja do najnowszej wersji MySQL, która obsługuje sortowanie mieszane przy użyciu indeksów.

Przykład #4 – Unikaj warunków z różnymi typami kolumn

Rozważmy to zapytanie, które wybiera liczbę czerwonych owoców w lesie.

SELECT COUNT(*)FROM forestWHERE fruit_color = 5; /* 5 = red */

Zakładając, że typem kolumny fruit_color jest VARCHAR, lub po prostu cokolwiek innego niż numeryczne, indeksowanie tej kolumny nie będzie zbyt pomocne, ponieważ wymagany rzut domyślny uniemożliwi bazie danych użycie indeksu w procesie filtrowania.

Jak więc możesz zoptymalizować to zapytanie SQL? Masz dwie możliwości zoptymalizowania tego zapytania. Pierwszą z nich jest porównanie kolumny do stałej wartości, która pasuje do typu kolumny, więc jeśli jest to kolumna VARCHAR, porównaj ją do '5′ (z pojedynczym cudzysłowem), a nie do 5 (co jest porównaniem numerycznym, które spowoduje implicite cast).

Lepszą opcją będzie dostosowanie typu kolumny do najbardziej odpowiedniego typu dla wartości, które przechowuje kolumna. W tym przykładzie, kolumna powinna zostać zmieniona na typ INT. Proszę zauważyć, że zmiana typu kolumny może być skomplikowanym zadaniem, więc przeczytaj o wyzwaniach związanych z tym zadaniem zanim się do niego zabierzesz.

Unikaj wyszukiwania LIKE z przedrostkiem wieloznacznym

Rozważmy to zapytanie, które wyszukuje wszystkie posty na Facebooku z nazwy użytkownika, która zawiera ciąg 'Mar', więc szukamy wszystkich postów napisanych przez użytkowników o imieniu Mark, Marcus, Almar, itd.

SELECT *FROM fb_postsWHERE username LIKE '%Mar%'

Umieszczenie symbolu wieloznacznego '%' na początku wzorca uniemożliwi bazie danych użycie indeksu do przeszukiwania tej kolumny. Takie wyszukiwanie może zająć trochę czasu..

W tym przypadku istnieją dwie możliwości poprawienia wydajności tego zapytania. Pierwsza z nich jest banalna – zastanów się, czy przedrostek wieloznaczny jest wystarczająco ważny. Jeśli możesz poradzić sobie bez niego, pozbądź się go.

Inną opcją będzie użycie indeksów pełnotekstowych. Należy jednak pamiętać, że te indeksy i składnia MATCH … AGAINST nie są wolne od wyzwań i mają pewne różnice w porównaniu do znanych wyrażeń LIKE w MySQL.

Podsumowanie

W tej pierwszej części naszej serii optymalizacji zapytań SQL, omówiliśmy znaczenie mądrego indeksowania, przeszliśmy przez kilka przykładów możliwych przeszkód podczas używania indeksowanych kolumn w zapytaniach, a także wyszczególniliśmy kilka innych wskazówek i sztuczek, które mogą być pomocne dla lepszej wydajności zapytań. Do zobaczenia w następnym poście.