jesteś w nowej pracy jako Administrator bazy danych lub Inżynier danych i po prostu zgubiłeś się próbując dowiedzieć się, co te szalenie wyglądające zapytania mają znaczyć i robić. Dlaczego istnieje 5 złączeń i dlaczego istnieje ORDER BY używany w podquery zanim jedno z złączeń w ogóle się dzieje? Pamiętaj, że zostałeś zatrudniony z jakiegoś powodu-najprawdopodobniej ten powód ma również związek z wieloma zawiłymi zapytaniami, które zostały utworzone i edytowane w ciągu ostatniej dekady.
EXPLAIN słowo kluczowe jest używane w różnych bazach danych SQL i zawiera informacje o tym, jak twoja baza danych SQL wykonuje zapytanie. W MySQL, EXPLAIN może być używany przed zapytaniem zaczynającym się od SELECTINSERTDELETEREPLACE oraz UPDATE. Dla prostego zapytania wyglądałoby to następująco:
EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas';zamiast zwykłego wyniku, MySQL wyświetli plan wykonania instrukcji, wyjaśniając, które procesy mają miejsce w jakiej kolejności podczas wykonywania instrukcji.
Uwaga: JeśliEXPLAIN nie działa dla ciebie, twój użytkownik bazy danych może nie mieć uprawnieńSELECT dla tabel lub widoków, których używasz w instrukcji.
EXPLAIN jest doskonałym narzędziem do szybkiego usuwania powolnych zapytań. Chociaż z pewnością może Ci pomóc, nie pozbędzie się potrzeby myślenia strukturalnego i dobrego przeglądu istniejących modeli danych. Często najprostszą i najszybszą radą jest dodanie indeksu do konkretnych kolumn tabeli, jeśli są one używane w wielu zapytaniach z problemami z wydajnością. Uważaj jednak, nie używaj zbyt wielu indeksów, ponieważ może to przynieść efekt przeciwny do zamierzonego. Odczyt indeksu i tabeli ma sens tylko wtedy, gdy tabela zawiera znaczną ilość wierszy i potrzebujesz tylko kilku punktów danych. Jeśli pobierasz ogromny zestaw wyników z tabeli i często odpytywasz różne kolumny, indeks w każdej kolumnie nie ma sensu i bardziej utrudnia wydajność niż pomaga. Aby uzyskać więcej informacji na temat rzeczywistych obliczeń indeksu vs indeksu no, przeczytaj Szacowanie wydajności w oficjalnej dokumentacji MySQL.
rzeczy, których chcesz uniknąć w miarę możliwości i zastosowania, to sortowanie i obliczenia w zapytaniach. Jeśli uważasz, że nie możesz uniknąć obliczeń w ramach zapytań: tak, możesz. Napisz wynik ustawiony gdzie indziej i Oblicz punkt danych poza zapytaniem, spowoduje to mniejsze obciążenie bazy danych, a tym samym będzie ogólnie lepszy dla Twojej aplikacji. Po prostu upewnij się, że dokumentujesz, dlaczego obliczasz w swojej aplikacji, a nie od razu generujesz wynik w SQL. W przeciwnym razie pojawi się następny Administrator bazy danych lub programista i będzie miał wspaniały pomysł użycia obliczeń w zapytaniu w stylu: „o spójrz, mój poprzednik nawet nie wiedział, że możesz to zrobić w SQL!”Niektóre zespoły programistów, które nie miały jeszcze nieuniknionego problemu z umieraniem baz danych, mogą stosować obliczenia w zapytaniach dla różnic liczbowych między datami lub podobnymi punktami danych.
ogólna zasada dla zapytań SQL jest następująca:
bądź precyzyjny i generuj tylko potrzebne wyniki.
sprawdźmy nieco bardziej skomplikowane zapytanie…
SELECT site_options.domain, sites_users.user, site_taxes.monthly_statement_fee, site.name, AVG(price) AS average_product_price FROM sites_orders_products, site_taxes, site, sites_users, site_options WHERE site_options.site_id = site.id AND sites_users.id = site.user_id AND site_taxes.site_id = site.id AND sites_orders_products.site_id = site.id GROUP BY site.id ORDER BY site.date_modified desc LIMIT 5;+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| domain | user | monthly_statement_fee | name | average_product_price |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| www.xxxxxxxxxxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxx | 3.254781 || www.xxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | 9.471022 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxx | 8.646297 || | [email protected] | 0.00 | xxxxxxxxxxxxxxx | 9.042460 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxxx | 6.679182 |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+5 rows in set (0.00 sec)…i jegoEXPLAIN wyjście.
+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| 1 | SIMPLE | sites | index | PRIMARY,user_id | PRIMARY | 4 | NULL | 858 | Using temporary; Using filesort || 1 | SIMPLE | sites_options | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_taxes | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_users | eq_ref | PRIMARY | PRIMARY | 4 | service.sites.user_id | 1 | || 1 | SIMPLE | sites_orders_products | ref | site_id | site_id | 4 | service.sites.id | 4153 | |//+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+5 rows in set (0.00 sec)kolumny w wyjściuEXPLAIN z tymi, które wymagają szczególnej uwagi przy identyfikowaniu problemów pogrubioną czcionką są:
- id (query id)
- select_type (Typ instrukcji)
- table (odwołuje się do tabeli)
- type (join type)
- possible_keys (których kluczy można było użyć)
- key (klucz, który został użyty)
- key_len (długość używanego klucza)
- ref (kolumny w stosunku do indeksu)
- rows (ilość przeszukiwanych wierszy)
- extra (informacje dodatkowe)
im większa ilość przeszukiwanych wierszy, tym lepszy musi być poziom optymalizacji pod względem indeksów i precyzji zapytań, aby zmaksymalizować wydajność. Dodatkowa kolumna pokazuje możliwe działania, na których można się skupić, aby poprawić swoje zapytanie, jeśli ma to zastosowanie.
pokazuje Ostrzeżenia;
Jeśli zapytanie, którego użyłeś z EXPLAIN nie przetwarza poprawnie, możesz wpisać SHOW WARNINGS; do edytora zapytań MySQL, aby wyświetlić informacje o ostatniej instrukcji, która została uruchomiona i nie była diagnostyczna, tzn. nie wyświetli informacji dla instrukcji takich jak SHOW FULL PROCESSLIST;. Chociaż nie może podać właściwego planu wykonania zapytania, takiego jakEXPLAIN, może dać ci wskazówki dotyczące tych fragmentów zapytań, które może przetworzyć. Załóżmy, że używamy zapytania EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas'; dla dowolnej bazy danych, która w rzeczywistości nie ma tabeli foo. Wyjście MySQL byłoby następujące:
ERROR 1146 (42S02): Table 'db.foo' doesn't existjeśli wpiszemy SHOW WARNINGS; wyjście wygląda następująco:
+-------+------+-------------------------------------+| Level | Code | Message |+-------+------+-------------------------------------+| Error | 1146 | Table 'db.foo' doesn't exist |+-------+------+-------------------------------------+1 row in set (0.00 sec)spróbujmy z celowym błędem składni.
EXPLAIN SELECT * FROM foo WHERE name = ///;To generuje następujące ostrzeżenia:
> SHOW WARNINGS;+-------+------+---------------------------------------------------------------------+| Level | Code | Message |+-------+------+---------------------------------------------------------------------+| Error | 1064 | You have an error in your SQL syntax; (...) near '///' at line 1 |+-------+------+---------------------------------------------------------------------+ to wyjście ostrzeżeń jest dość proste i wyświetlane przez MySQL jako wyjście wyjściowe od razu, ale w przypadku bardziej skomplikowanych zapytań, które nie analizują, nadal można przyjrzeć się, co dzieje się w tych fragmentach zapytań, które można przeanalizować. SHOW WARNINGS; zawiera specjalne znaczniki, które mogą dostarczać przydatne informacje, takie jak:
-
<index_lookup>(query fragment): wyszukiwanie indeksu nastąpiłoby, gdyby zapytanie zostało poprawnie przeanalizowane -
<if>(condition, expr1, expr2): warunek if występuje w tej konkretnej części zapytania -
<primary_index_lookup>(query fragment): wyszukiwanie indeksu odbywa się za pomocą klucza podstawowego -
<temporary table>: tutaj zostanie utworzona wewnętrzna tabela do zapisywania tymczasowych wyników, na przykład w zapytaniach podrzędnych przed dołączeniem
aby dowiedzieć się więcej o tych specjalnych znacznikach, przeczytaj Rozszerzone Wyjaśnienie format wyjściowy w oficjalnej dokumentacji MySQL.
długoterminowa poprawka
istnieje kilka sposobów naprawienia głównej przyczyny złej wydajności bazy danych. Pierwszym punktem, na który należy zwrócić uwagę, jest model danych, tj. jak są ustrukturyzowane Dane i czy korzystasz z odpowiedniej bazy danych? W przypadku wielu produktów baza danych SQL jest w porządku. Ważną rzeczą do zapamiętania jest zawsze oddzielanie logów dostępu od zwykłej bazy produkcyjnej, co niestety nie zdarza się w wielu firmach. Głównie w tych przypadkach firma zaczęła być mała, rosła i zasadniczo nadal korzysta z tej samej bazy danych, co oznacza, że ma dostęp do tej samej bazy danych zarówno dla funkcji rejestrowania, jak i innych transakcji. To znacznie zmniejsza ogólną wydajność, zwłaszcza gdy firma rośnie. Dlatego bardzo ważne jest stworzenie modelu danych, który pasuje i jest zrównoważony.
model danych
wybór modelu danych najprawdopodobniej ujawni również właściwą formę bazy danych. O ile twój produkt nie jest bardzo prosty, prawdopodobnie będziesz mieć kilka baz danych dla kilku przypadków użycia – jeśli chcesz pokazać prawie w czasie rzeczywistym numery dzienników dostępu, najprawdopodobniej będziesz chciał wysoce wydajnej hurtowni danych, podczas gdy regularne transakcje mogą odbywać się za pośrednictwem bazy danych SQL, a możesz mieć bazę danych Wykresów, która gromadzi odpowiednie punkty danych obu baz danych w silniku rekomendującym.
Architektura oprogramowania całego produktu jest tak samo ważna jak sama baza danych, ponieważ zły projekt spowoduje wąskie gardła, które idą w kierunku bazy danych i spowalniają wszystko zarówno od strony oprogramowania, jak i tego, co baza danych może generować. Będziesz musiał wybrać, czy kontenery są odpowiednie dla Twojego produktu, czy monolit jest lepszym sposobem na obsługę rzeczy, czy chcesz mieć rdzeń monolitu z kilkoma mikroserwisami ukierunkowanymi na inne funkcje rozproszone w innych miejscach oraz w jaki sposób uzyskujesz dostęp, zbierasz, przetwarzasz i przechowujesz dane.
Sprzęt
tak samo ważny jak ogólna struktura, sprzęt jest kluczowym elementem wydajności bazy danych. Exoscale oferuje różne opcje instancji, których można użyć w zależności od ilości transakcji i pamięci masowej, a także żądanego czasu reakcji.
ważne jest, aby określić okresy szczytowe aplikacji, a zatem wiedzieć, kiedy pominąć wolniejsze zapytania administracyjne, jeśli to możliwe. Statystyki We/Wy dysków i sieci muszą być również brane pod uwagę podczas projektowania czasu transakcji w bazie danych i analizy.
podsumowanie
podsumowując, oto główne punkty dla wyników długoterminowych podsumowane:
- stwórz trwały model danych, który odpowiada potrzebom Twojej firmy
- wybierz odpowiednią formę bazy danych
- użyj architektury oprogramowania, która pasuje do Twojego produktu
- przechodź regularne iteracje przeglądania struktury zapytań i używaj
EXPLAINna bardziej zawiłych, zoptymalizuj wykorzystanie wybranych baz danych, również pod kątem aktualizacji baz danych i ich wpływu na Ciebie /li> - wybierz instancje, które najlepiej odpowiadają potrzebom aplikacji i bazy danych zgodnie z wydajnością i przepustowością