Problemy z wydajnością SQL wynikające z „niepotrzebnej, ale wymaganej pracy”

Poziom wiedzy wymagany do zrozumienia artykułu: ogólna znajomość baz danych i SQL, pewne praktyczne doświadczenie z DBMS. Prawdopodobnie najważniejszą rzeczą, której możesz nauczyć się pisać efektywne zapytania SQL, jest indeksowanie. Jednak na drugim miejscu, bardzo blisko, jest świadomość, że wielu klientów SQL wymaga od bazy danych wykonania wielu „niepotrzebnych, ale niezbędnych prac” . Powtarzaj za mną: Co to jest „praca niepotrzebna, ale obowiązkowa”? Jak mówi nam Kapitan Oczywisty, ona:

Niepotrzebny

Niech nasza aplikacja kliencka potrzebuje następujących danych: Nic niezwykłego. Pracujemy z bazą danych filmów (taką jak baza danych Sakila ) i chcemy wyświetlać użytkownikom tytuły i oceny wszystkich filmów. Poniższe zapytanie może dać wynik, którego potrzebujemy:

SELECT title, rating
FROM film

Jednak nasza aplikacja (lub nasz ORM) zamiast tego wykonuje to zapytanie:

SELECT *
FROM film

Co w efekcie otrzymamy? Zgadywać. Otrzymujemy wiele bezużytecznych informacji: Po prawej stronie widać nawet ładowanie złożonego formatu JSON:

• z dysku
• do buforowania
• drutem
• w pamięci klienta
• i ostatecznie wyrzucony [jako niepotrzebny]

Tak, wyrzucamy większość tych informacji. Wszelkie działania podjęte w celu wydobycia tych informacji okazały się całkowicie bezużyteczne. Czy to prawda? Czy to prawda.

Obowiązkowy

A teraz – najgorsza część. Chociaż optymalizatory mogą teraz wiele zrobić, te działania są obowiązkowe dla bazy danych. Baza danych nie ma możliwości dowiedzenia się, że aplikacja kliencka nie potrzebuje 95% tych danych. A to tylko najprostszy przykład. Wyobraź sobie połączenie kilku tabel... No i co, mówisz, ale bazy danych są szybkie? Pozwól, że oświecę Cię w kilku kwestiach, o których prawdopodobnie nie pomyślałeś. Oczywiście czas realizacji indywidualnego żądania tak naprawdę na nic nie wpływa. OK, poszło półtora razy wolniej, ale damy radę, prawda? Dla wygody? Czasami to prawda. Ale jeśli zawsze poświęcisz wydajność na rzecz wygody , te małe rzeczy zaczną się sumować. Nie będziemy już mówić o wydajności (szybkości realizacji poszczególnych żądań), ale o przepustowości (czasie reakcji systemu), a wtedy zaczną się poważne problemy, które nie są tak łatwe do rozwiązania. Wtedy tracisz skalowalność. Przyjrzyjmy się planom wykonania, w tym przypadku Oracle DBMS:

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 |   166K|
|   1 |  TABLE ACCESS FULL| FILM |  1000 |   166K|
--------------------------------------------------

w porównaniu z:

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 | 20000 |
|   1 |  TABLE ACCESS FULL| FILM |  1000 | 20000 |
--------------------------------------------------

Uruchamiając zapytanie SELECT * zamiast tytułu SELECT, ocena zużywa 8 razy więcej pamięci w bazie danych. Nic nieoczekiwanego, prawda? Wiedzieliśmy, że tak się stanie. Jednak nadal zgadzamy się na to w przypadku wielu naszych żądań, w których po prostu nie potrzebujemy wszystkich tych danych. Tworzymy niepotrzebną, ale obowiązkową pracę dla bazy danych , która ciągle się piętrzy. Zużywamy 8 razy więcej pamięci niż potrzeba (mnożnik oczywiście się zmieni). Tymczasem na wszystkich pozostałych etapach (we/wy dysku, przesyłanie danych przez sieć, zużycie pamięci przez klienta) problemy są dokładnie takie same, ale pominę je i zamiast tego przyjrzę się...

Korzystanie z indeksów

Większość współczesnych baz danych doceniła już koncepcję zakrywania indeksów . Indeks pokrywający sam w sobie nie jest specjalnym typem indeksu. Może się jednak okazać, że jest to „specjalny indeks” dla konkretnego zapytania, „przez przypadek” lub dlatego, że taki był zamierzenie. Rozważ następujące zapytanie:

SELECT *
FROM actor
WHERE last_name LIKE 'A%'

W jego realizacji nie ma nic nieoczekiwanego. To jest prosta prośba. Wyświetl zakres według indeksu, przejdź do tabeli i gotowe:

-------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR               |     8 |
|*  2 |   INDEX RANGE SCAN          | IDX_ACTOR_LAST_NAME |     8 |
-------------------------------------------------------------------

Dobry plan, prawda? Cóż, jeśli naprawdę tego potrzebowaliśmy, to nie: Oczywiście marnujemy pamięć itp. Rozważmy to zapytanie jako alternatywę:

SELECT first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'

Jego plan jest taki:

----------------------------------------------------
| Id  | Operation        | Name            | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT |                 |     8 |
|*  1 |  INDEX RANGE SCAN| IDX_ACTOR_NAMES |     8 |
----------------------------------------------------

Udało nam się całkowicie wyeliminować dostęp do tabeli, dzięki obecności indeksu, który spełnia wszystkie potrzeby naszego zapytania... indeksu pokrywającego. Czy to ważne? I jak! Takie podejście pozwala przyspieszyć niektóre zapytania o rząd wielkości (lub spowolnić je o rząd wielkości, gdy indeks nie pokrywa już po pewnych zmianach). Nie zawsze można zastosować indeksy kryjące. Za indeksy trzeba płacić i nie należy ich dodawać zbyt wiele. Ale w tym przypadku wszystko jest oczywiste. Oceńmy wydajność:

SET SERVEROUTPUT ON
DECLARE
  v_ts TIMESTAMP;
  v_repeat CONSTANT NUMBER := 100000;
BEGIN
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Наихудший вариант wniosekа: перерасход памяти ПЛЮС доступ к таблице
      SELECT *
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 1 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Улучшенный wniosek, но все равно с доступом к таблице
      SELECT /*+INDEX(actor(last_name))*/
        first_name, last_name
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 2 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Оптимальный wniosek: покрывающий индекс
      SELECT /*+INDEX(actor(last_name, first_name))*/
        first_name, last_name
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 3 : ' || (SYSTIMESTAMP - v_ts));
END;
/

Należy zauważyć, że tabela aktorów ma tylko 4 kolumny, więc różnica w wydajności między instrukcjami 1 i 2 nie jest aż tak duża, ale nadal znacząca. Zauważę również, że użyłem wskazówek optymalizatora Oracle, aby optymalizator wybrał jeden lub inny konkretny indeks dla zapytania. Operator 3 jest niekwestionowanym zwycięzcą naszego wyścigu. Jego wydajność jest znacznie lepsza, a mówimy o wyjątkowo prostym zapytaniu. Ponownie, pisząc SELECT *, tworzymy niepotrzebną, ale obowiązkową pracę dla bazy danych , której nie może ona zoptymalizować. Nie wybierze indeksu pokrywającego, ponieważ wiąże się on z nieco większym obciążeniem niż wybrany przez nią indeks LAST_NAME, a poza tym, między innymi, nadal musi uzyskać dostęp do tabeli, aby na przykład pobrać bezużyteczną kolumnę LAST_UPDATE. Ale im głębiej analizujemy SELECT *, tym gorzej się dzieje. Porozmawiajmy o...

Konwersje SQL

Optymalizatory działają tak dobrze, ponieważ przekształcają zapytania SQL ( opowiadałem, jak to działa podczas mojego ostatniego wykładu na Voxxed Days w Zurychu ). Na przykład istnieje niezwykle potężna transformacja „wyjątek JOIN”. Rozważ następujący widok pomocniczy, który musieliśmy utworzyć, aby uniknąć ręcznego łączenia wszystkich tych tabel za każdym razem:

CREATE VIEW v_customer AS
SELECT
  c.first_name, c.last_name,
  a.address, ci.city, co.country
FROM customer c
JOIN address a USING (address_id)
JOIN city ci USING (city_id)
JOIN country co USING (country_id)

Widok ten po prostu wykonuje wszystkie połączenia „...do jednego” pomiędzy tabelą klienta CUSTOMER a różnymi tabelami części jego adresu. Dziękuję, normalizacja. Wyobraź sobie, że po krótkiej pracy z tym widokiem przyzwyczailiśmy się do niego i zapomnieliśmy o tabelach leżących u jego podstaw. A teraz wykonujemy następujące zapytanie:

SELECT *
FROM v_customer

W rezultacie otrzymujemy bardzo imponujący plan:

----------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost |
----------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   599 | 47920 |   14 |
|*  1 |  HASH JOIN           |          |   599 | 47920 |   14 |
|   2 |   TABLE ACCESS FULL  | COUNTRY  |   109 |  1526 |    2 |
|*  3 |   HASH JOIN          |          |   599 | 39534 |   11 |
|   4 |    TABLE ACCESS FULL | CITY     |   600 | 10800 |    3 |
|*  5 |    HASH JOIN         |          |   599 | 28752 |    8 |
|   6 |     TABLE ACCESS FULL| CUSTOMER |   599 | 11381 |    4 |
|   7 |     TABLE ACCESS FULL| ADDRESS  |   603 | 17487 |    3 |
----------------------------------------------------------------

Ależ oczywiście. Baza danych wykonuje wszystkie te łączenia i pełne skanowanie tabel, ponieważ tak jej kazaliśmy - pobierać wszystkie dane. A teraz wyobraźmy sobie, że tak naprawdę potrzebowaliśmy tylko tego: A co, poważnie, prawda? Teraz zaczynasz rozumieć o czym mówię. Wyobraź sobie jednak, że nauczyliśmy się czegoś na błędach z przeszłości i wykonaj to, bardziej optymalne zapytanie:

SELECT first_name, last_name
FROM v_customer

Sprawdźmy teraz, co się stało!

------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost  |
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   599 | 16173 |     4 |
|   1 |  NESTED LOOPS      |             |   599 | 16173 |     4 |
|   2 |   TABLE ACCESS FULL| CUSTOMER    |   599 | 11381 |     4 |
|*  3 |   INDEX UNIQUE SCAN| SYS_C007120 |     1 |     8 |     0 |
------------------------------------------------------------------

Drastyczne zmiany na lepsze jeśli chodzi o wykonanie. Złączenia zostały wyeliminowane, ponieważ optymalizator widzi teraz, że są one bezużyteczne i jeśli to widzi (a nie ustawiłeś tej pracy jako obowiązkowej, wybierając *), to po prostu nie może wykonać całej tej pracy. Dlaczego tak jest w tym przypadku? Klucz obcy CUSTOMER.ADDRESS_ID do klucza podstawowego ADDRESS.ADDRESS_ID gwarantuje dokładnie jedną wartość tego ostatniego, co oznacza, że ​​operacja JOIN będzie złączeniem „...do jednego”, które nie zwiększa ani nie zmniejsza liczby wierszy . A ponieważ w ogóle nie wybieramy ani nie żądamy żadnych wierszy, nie ma sensu ich w ogóle ładować. Usunięcie JOIN prawdopodobnie w ogóle nie wpłynie na wynik zapytania. Bazy danych robią to cały czas. Możesz uruchomić następujące zapytanie w prawie każdej bazie danych:

-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual

-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (SELECT 1 / 0)

W takim przypadku możesz spodziewać się zgłoszenia wyjątku arytmetycznego, tak jak podczas wykonywania następującego zapytania:

SELECT 1 / 0 FROM dual

Ale to się nie zdarza. Optymalizator (lub nawet parser) może zapewnić, że żaden element listy wyboru w predykacie EXISTS (SELECT ..) nie zmieni wyniku zapytania, więc nie ma potrzeby jego wykonywania. Lubię to!

Tymczasem...

Jednym z najbardziej irytujących problemów z ORM-ami jest to, że tak łatwo jest pisać zapytania SELECT *. W rzeczywistości na przykład w HQL / JPQL są one powszechnie używane domyślnie. Możemy całkowicie pominąć klauzulę SELECT, ponieważ pobierzemy całą encję, prawda? Na przykład:

FROM v_customer

Na przykład Vlad Mihalcea, ekspert i zwolennik programowania za pomocą Hibernate , zaleca używanie [kwalifikowanych] zapytań prawie zawsze, gdy masz pewność, że nie chcesz zapisywać żadnych zmian po realizacji transakcji. ORM znacznie ułatwiają rozwiązanie problemu trwałości grafów obiektowych. Uwaga: Trwałość. Zadania faktycznej modyfikacji wykresów obiektowych i zapisywania zmian są ze sobą nierozerwalnie powiązane. Ale jeśli nie zamierzasz tego zrobić, to po co zawracać sobie głowę wydobywaniem esencji? Dlaczego nie napisać [dopracowanej] prośby? Wyjaśnijmy sobie: z punktu widzenia wydajności napisanie zapytania specjalnie dostosowanego do konkretnego przypadku użycia jest oczywiście lepsze niż jakakolwiek inna opcja. Możesz się tym nie przejmować, ponieważ Twój zestaw danych jest mały i nie ma to znaczenia. Świetnie. Kiedy jednak w końcu zajdzie potrzeba skalowalności, przeprojektowanie aplikacji tak, aby korzystały z zapytań zamiast konieczności przeglądania grafu encji, będzie dość trudne. I bez tego będziesz miał co robić.

Liczenie wierszy, aby sprawdzić, czy coś jest obecne

Jednym z najgorszych marnotrawstw zasobów jest wykonywanie zapytań COUNT(*), aby sprawdzić, czy coś znajduje się w bazie danych. Musimy np. dowiedzieć się, czy dany użytkownik w ogóle ma zamówienia. I wykonujemy żądanie:

SELECT count(*)
FROM orders
WHERE user_id = :user_id

Podstawowy. Jeśli COUNT = 0, to nie ma żadnych zleceń. W przeciwnym razie tak. Wydajność nie będzie taka zła, ponieważ prawdopodobnie mamy indeks w kolumnie ORDERS.USER_ID. Ale jak myślisz, jaka będzie wydajność powyższego zapytania w porównaniu z następującą opcją:

-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT *
  FROM orders
  WHERE user_id = :user_id
) THEN 1 ELSE 0 END
FROM dual

-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (
  SELECT *
  FROM orders
  WHERE user_id = :user_id
)

Nie trzeba być naukowcem, aby dowiedzieć się, że predykat prawdziwego istnienia przestanie szukać dodatkowych ciągów, gdy tylko znajdzie pierwszy . Jeśli więc wynik okaże się „brak zamówień”, wówczas prędkość będzie porównywalna. Jeśli jednak wynik będzie brzmieć „tak, są zamówienia”, to w przypadku, gdy nie trzeba liczyć dokładnej ilości, odpowiedź zostanie otrzymana znacznie szybciej. Przecież nie interesuje nas dokładna liczba. Jednak powiedzieliśmy bazie danych, aby to obliczyła ( niepotrzebna praca ), a baza danych nie wie, że ignorujemy wszystkie wyniki większe niż 1 ( wymagana praca ). Oczywiście byłoby znacznie gorzej, gdybyśmy wywołali funkcję list.size() w kolekcji wspieranej przez JPA, aby osiągnąć te same wyniki. Pisałem już o tym wcześniej na swoim blogu, a testy porównawcze obu opcji przeprowadziłem na…

Wniosek

W tym artykule stwierdzamy rzeczy oczywiste. Nie zmuszaj bazy danych do wykonywania niepotrzebnej, ale wymaganej pracy . Nie jest to konieczne , ponieważ biorąc pod uwagę wymagania, wiadomo, że nie trzeba wykonywać określonej pracy. Jednak każesz bazie danych to zrobić. Jest to wymagane, ponieważ baza danych nie ma możliwości sprawdzenia, czy ta praca jest niepotrzebna . Informacje te są dostępne tylko dla klienta i nie są dostępne dla serwera. Zatem baza danych musi to wykonać. Artykuł skupiał się na SELECT *, głównie dlatego, że jest to obiekt tak wygodny do oglądania. Dotyczy to jednak nie tylko baz danych. Dotyczy to wszystkich algorytmów rozproszonych, w których klient każe serwerowi wykonać niepotrzebną, ale wymaganą pracę . Ile zadań N+1 znajduje się w przeciętnej aplikacji AngularJS, w której interfejs użytkownika przegląda wyniki usługi A, wielokrotnie wywołując usługę B, zamiast pakować wszystkie wywołania do B w jedno wywołanie? Jest to bardzo powszechne zjawisko. Rozwiązanie jest zawsze takie samo. Im więcej informacji przekażesz podmiotowi wykonującemu Twoje polecenia, tym szybciej (teoretycznie) wykona on te polecenia. Napisz optymalne zapytania. Zawsze. Cały Twój system będzie Ci za to wdzięczny. Oryginalny artykuł