Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00245 006309 13652596 na godz. na dobę w sumie
Wysoko wydajne MySQL. Optymalizacja, archiwizacja, replikacja. Wydanie II - książka
Wysoko wydajne MySQL. Optymalizacja, archiwizacja, replikacja. Wydanie II - książka
Autor: , , , , , Liczba stron: 712
Wydawca: Helion Język publikacji: polski
ISBN: 978-83-246-2055-5 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> bazy danych >> mysql - programowanie
Porównaj ceny (książka, ebook, audiobook).

Poznaj zaawansowane techniki i nieznane możliwości MySQL!

MySQL jest ciągle udoskonalanym i rozbudowywanym oprogramowaniem. Stale zwiększa się także liczba jego użytkowników, wśród których nie brak wielkich korporacji. Wynika to z niezawodności i ogromnej, wciąż rosnącej wydajności tego systemu zarządzania. MySQL sprawdza się także w bardzo wymagających środowiskach, na przykład aplikacjach sieciowych, ze względu na dużą elastyczność i możliwości, takie jak zdolność wczytywania silników magazynu danych jako rozszerzeń w trakcie działania bazy.

Książka 'Wysoko wydajne MySQL. Optymalizacja, archiwizacja, replikacja. Wydanie II' szczegółowo prezentuje zaawansowane techniki, dzięki którym można w pełni wykorzystać cały potencjał, drzemiący w MySQL. Omówiono w niej praktyczne, bezpieczne i pozwalające na osiągnięcie dużej wydajności sposoby skalowania aplikacji. Z tego przewodnika dowiesz się, w jaki sposób projektować schematy, indeksy i zapytania. Poznasz także zaawansowane funkcje MySQL, pozwalające na uzyskanie maksymalnej wydajności. Nauczysz się tak dostrajać serwer MySQL, system operacyjny oraz osprzęt komputerowy, aby wykorzystywać pełnię ich możliwości.

Twórz doskonale dostrojone aplikacje MySQL

Znajdź podobne książki Ostatnio czytane w tej kategorii

Darmowy fragment publikacji:

Wysoko wydajne MySQL. Optymalizacja, archiwizacja, replikacja. Wydanie II Autor: Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny, Arjen Lentz, Derek J. Balling T³umaczenie: Robert Górczyñski ISBN: 978-83-246-2055-5 Tytu³ orygina³u: High Performance MySQL: Optimization, Backups, Replication, and More, 2nd edition Format: 168x237, stron: 712 Poznaj zaawansowane techniki i nieznane mo¿liwoœci MySQL! • Jak za pomoc¹ MySQL budowaæ szybkie i niezawodne systemy? • Jak przeprowadzaæ testy wydajnoœci? • Jak optymalizowaæ zaawansowane funkcje zapytañ? MySQL jest ci¹gle udoskonalanym i rozbudowywanym oprogramowaniem. Stale zwiêksza siê tak¿e liczba jego u¿ytkowników, wœród których nie brak wielkich korporacji. Wynika to z niezawodnoœci i ogromnej, wci¹¿ rosn¹cej wydajnoœci tego systemu zarz¹dzania. MySQL sprawdza siê tak¿e w bardzo wymagaj¹cych œrodowiskach, na przyk³ad aplikacjach sieciowych, ze wzglêdu na du¿¹ elastycznoœæ i mo¿liwoœci, takie jak zdolnoœæ wczytywania silników magazynu danych jako rozszerzeñ w trakcie dzia³ania bazy. Ksi¹¿ka „Wysoko wydajne MySQL. Optymalizacja, archiwizacja, replikacja. Wydanie II” szczegó³owo prezentuje zaawansowane techniki, dziêki którym mo¿na w pe³ni wykorzystaæ ca³y potencja³, drzemi¹cy w MySQL. Omówiono w niej praktyczne, bezpieczne i pozwalaj¹ce na osi¹gniêcie du¿ej wydajnoœci sposoby skalowania aplikacji. Z tego przewodnika dowiesz siê, w jaki sposób projektowaæ schematy, indeksy i zapytania. Poznasz tak¿e zaawansowane funkcje MySQL, pozwalaj¹ce na uzyskanie maksymalnej wydajnoœci. Nauczysz siê tak dostrajaæ serwer MySQL, system operacyjny oraz osprzêt komputerowy, aby wykorzystywaæ pe³niê ich mo¿liwoœci. • Architektura MySQL • Testy wydajnoœci i profilowanie • Optymalizacja schematu i indeksowanie • Optymalizacja wydajnoœci zapytañ • Przechowywanie kodu • Umieszczanie komentarzy w kodzie sk³adowym • Konfiguracja serwera • Dostrajanie i optymalizacja wyszukiwania pe³notekstowego • Skalowalnoœæ i wysoka dostêpnoœæ • Wydajnoœæ aplikacji • Kopia zapasowa i odzyskiwanie • Interfejs SQL dla poleceñ spreparowanych • Bezpieczeñstwo Twórz doskonale dostrojone aplikacje MySQL Spis treści Przedmowa ....................................................................................................................7 Wprowadzenie ..............................................................................................................9 1. Architektura MySQL .................................................................................................... 19 19 22 24 31 32 Architektura logiczna MySQL Kontrola współbieżności Transakcje Mechanizm Multiversion Concurrency Control Silniki magazynu danych w MySQL 2. Określanie wąskich gardeł: testy wydajności i profilowanie .................................... 51 52 53 56 61 64 73 95 Dlaczego warto przeprowadzić testy wydajności? Strategie przeprowadzania testów wydajności Taktyki przeprowadzania testów wydajności Narzędzia do przeprowadzania testów wydajności Przykładowe testy wydajności Profilowanie Profilowanie systemu operacyjnego 3. Optymalizacja schematu i indeksowanie ...................................................................99 100 115 125 150 155 168 Wybór optymalnego rodzaju danych Podstawy indeksowania Strategie indeksowania w celu osiągnięcia maksymalnej wydajności Studium przypadku z zakresu indeksowania Obsługa indeksu oraz tabeli Uwagi dotyczące silników magazynowania danych 3 4. Optymalizacja wydajności zapytań ...........................................................................171 171 176 179 198 207 217 Podstawy powolnych zapytań: optymalizacja dostępu do danych Sposoby restrukturyzacji zapytań Podstawy wykonywania zapytań Ograniczenia optymalizatora zapytań MySQL Optymalizacja określonego rodzaju zapytań Zmienne zdefiniowane przez użytkownika 5. Zaawansowane funkcje MySQL ................................................................................223 223 236 248 255 270 271 280 Bufor zapytań MySQL Przechowywanie kodu wewnątrz MySQL Funkcje zdefiniowane przez użytkownika System kodowania znaków i kolejność sortowania Ograniczenia klucza zewnętrznego Tabele Merge i partycjonowane Transakcje rozproszone (XA) 6. Optymalizacja konfiguracji serwera .........................................................................283 284 285 289 299 314 Podstawy konfiguracji Składnia, zasięg oraz dynamizm Ogólne dostrajanie Dostrajanie zachowania operacji I/O w MySQL Dostosowanie współbieżności MySQL 7. Optymalizacja systemu operacyjnego i osprzętu ....................................................325 326 326 337 338 345 347 356 Co ogranicza wydajność MySQL? W jaki sposób wybrać procesor dla MySQL? Wybór osprzętu komputerowego dla serwera podległego Optymalizacja wydajności macierzy RAID Urządzenia Storage Area Network oraz Network Attached Storage Używanie woluminów składających się z wielu dysków Stan systemu operacyjnego 8. Replikacja ...................................................................................................................363 363 367 375 382 397 399 409 428 Ogólny opis replikacji Konfiguracja replikacji Szczegóły kryjące się za replikacją Topologie replikacji Replikacja i planowanie pojemności Administracja replikacją i jej obsługa Problemy związane z replikacją i sposoby ich rozwiązywania Jak szybka jest replikacja? 4 | Spis treści 9. Skalowalność i wysoka dostępność .......................................................................... 431 432 434 469 Terminologia Skalowalność MySQL Wysoka dostępność 10. Optymalizacja na poziomie aplikacji ........................................................................479 479 482 Ogólny opis wydajności aplikacji Kwestie związane z serwerem WWW 11. Kopia zapasowa i odzyskiwanie ...............................................................................495 496 500 510 512 523 535 536 543 Ogólny opis Wady i zalety rozwiązania Zarządzanie kopią zapasową binarnych dzienników zdarzeń i jej tworzenie Tworzenie kopii zapasowej danych Odzyskiwanie z kopii zapasowej Szybkość tworzenia kopii zapasowej i odzyskiwania Narzędzia służące do obsługi kopii zapasowej Kopie zapasowe za pomocą skryptów 12. Bezpieczeństwo .........................................................................................................547 547 548 566 567 575 579 Terminologia Podstawy dotyczące kont Bezpieczeństwo systemu operacyjnego Bezpieczeństwo sieciowe Szyfrowanie danych MySQL w środowisku chroot 13. Stan serwera MySQL ................................................................................................. 581 581 582 589 602 603 604 605 Zmienne systemowe SHOW STATUS SHOW INNODB STATUS SHOW PROCESSLIST SHOW MUTEX STATUS Stan replikacji INFORMATION_SCHEMA 14. Narzędzia zapewniające wysoką wydajność ...........................................................607 607 609 619 622 625 Narzędzia interfejsu Narzędzia monitorowania Narzędzia analizy Narzędzia MySQL Źródła dalszych informacji Spis treści | 5 A Przesyłanie dużych plików ........................................................................................627 B Używanie polecenia EXPLAIN ................................................................................... 631 C Używanie silnika Sphinx w MySQL ...........................................................................647 D Usuwanie błędów w blokadach ................................................................................675 Skorowidz ..................................................................................................................685 6 | Spis treści ROZDZIAŁ 4. Optymalizacja wydajności zapytań W poprzednim rozdziale przeanalizowano sposoby optymalizacji schematu, która jest jed- nym z niezbędnych warunków osiągnięcia wysokiej wydajności. Jednak praca jedynie nad schematem nie wystarczy — trzeba również prawidłowo zaprojektować zapytania. Jeżeli za- pytania okażą się niewłaściwie przygotowane, nawet najlepiej zaprojektowany schemat bazy nie będzie działał wydajnie. Optymalizacja zapytania, optymalizacja indeksu oraz optymalizacja schematu idą ręka w rękę. Wraz z nabywaniem doświadczenia w tworzeniu zapytań MySQL czytelnik odkryje także, jak projektować schematy pozwalające na efektywną obsługę zapytań. Podobnie zdobyta wiedza z zakresu projektowania zoptymalizowanych schematów wpłynie na rodzaj zapytań. Ten proces wymaga czasu, dlatego też autorzy zachęcają, aby powrócić do rozdziałów bieżą- cego i poprzedniego po zdobyciu większej wiedzy. Rozdział ten rozpoczyna się od ogólnych rozważań dotyczących projektowania zapytań — omó- wione są tu elementy, na które powinno się zwrócić uwagę w pierwszej kolejności, jeśli zapyta- nia nie działają zgodnie z oczekiwaniami. Następnie nieco dokładniej zostaną przedstawione zagadnienia dotyczące optymalizacji zapytań oraz wewnętrznego działania serwera. Autorzy za- demonstrują, jak można poznać sposób wykonywania określonego zapytania przez MySQL, a także zmienić plan wykonywania zapytania. Wreszcie zostaną przedstawione fragmenty zapytań, w których MySQL nie przeprowadza zbyt dobrej optymalizacji. Czytelnik pozna również wzorce optymalizacji pomagające MySQL w znacznie efektywniejszym wykonywaniu zapytań. Celem autorów jest pomoc czytelnikowi w dokładnym zrozumieniu sposobu, w jaki MySQL faktycznie wykonuje zapytania. Pozwoli to na zorientowanie się, co jest efektywne lub nieefek- tywne, umożliwi wykorzystanie zalet bazy danych MySQL oraz ułatwi unikanie jej słabych stron. Podstawy powolnych zapytań: optymalizacja dostępu do danych Najbardziej podstawowym powodem słabej wydajności zapytania jest fakt, że obejmuje ono zbyt dużą ilość danych. Niektóre zapytania po prostu muszą dokładnie przebadać ogromną ilość danych, więc w takich przypadkach niewiele można zrobić. Jednak to nietypowa sytu- acja, większość błędnych zapytań można zmodyfikować, aby uzyskiwały dostęp do mniejszej ilości danych. Autorzy odkryli, że użyteczne jest analizowanie zapytań o słabej wydajności przy zastosowaniu dwóch kroków. Oto one. 171 1. Określenie, czy aplikacja pobiera więcej danych, niż potrzebuje. Zazwyczaj oznacza to uzyskanie dostępu do zbyt wielu rekordów, ale może również polegać na uzyskiwaniu dostępu do zbyt wielu kolumn. 2. Określenie, czy serwer MySQL analizuje więcej rekordów, niż potrzebuje. Czy zapytanie bazy danych obejmuje dane, które są niepotrzebne? Niektóre zapytania dotyczą większej ilości danych niż potrzeba, później część danych i tak jest odrzucana. Wymaga to dodatkowej pracy ze strony serwera MySQL, zwiększa obciążenie sieci1, a także zużywa pamięć i zasoby procesora serwera aplikacji. Poniżej przedstawiono kilka typowych błędów. Pobieranie liczby rekordów większej, niż to konieczne Najczęściej popełnianym błędem jest przyjęcie założenia, że MySQL dostarcza wyniki na żądanie, a nie generuje pełnego zbioru wynikowego i zwraca go. Autorzy często spotykali się z tym błędem w aplikacjach zaprojektowanych przez osoby znające zagadnienia związane z systemami baz danych. Programiści ci używali technik, takich jak wydawanie poleceń SELECT zwracających wiele rekordów, a następnie pobierających pierwsze N rekordów i zamykających zbiór wynikowy (np. pobranie stu ostatnich artykułów dla witryny in- formacyjnej, podczas gdy na stronie głównej było wyświetlanych tylko dziesięć z nich). Tacy programiści sądzą, że baza danych MySQL dostarczy im dziesięć rekordów, a na- stępnie zakończy wykonywanie zapytania. W rzeczywistości MySQL generuje pełny zbiór wynikowy. Biblioteka klienta pobiera wszystkie dane i odrzuca większość. Najlepszym rozwiązaniem jest dodanie do zapytania klauzuli LIMIT. Pobieranie wszystkich kolumn ze złączenia wielu tabel Jeżeli programista chce pobrać wszystkich aktorów występujących w filmie Academy Dinosaur, nie należy tworzyć zapytania w następujący sposób: mysql SELECT * FROM sakila.actor - INNER JOIN sakila.film_actor USING(actor_id) - INNER JOIN sakila.film USING(film_id) - WHERE sakila.film.title = Academy Dinosaur ; Powyższe zapytanie zwróci wszystkie kolumny z wszystkich trzech tabel. W zamian trzeba utworzyć następujące zapytanie: mysql SELECT sakila.actor.* FROM sakila.actor...; Pobieranie wszystkich kolumn Zawsze warto podejrzliwie spojrzeć na zapytania typu SELECT *. Czy naprawdę potrzebne są wszystkie kolumny? Prawdopodobnie nie. Pobieranie wszystkich kolumn uniemożliwia optymalizację w postaci np. zastosowania indeksu pokrywającego, a ponadto zwiększa obciążenie serwera wynikające z wykonywania operacji I/O, większego zużycia pamięci i mocy obliczeniowej procesora. Niektórzy administratorzy baz danych z wymienionych powyżej powodów w ogóle uniemożliwiają wykonywanie poleceń SELECT *. Takie rozwiązanie ogranicza również ryzyko wystąpienia problemów, gdy ktokolwiek zmieni listę kolumn tabeli. 1 Obciążenie sieci ma jeszcze poważniejsze znaczenie, jeżeli aplikacja znajduje się na serwerze innym od samego serwera MySQL. Jednak transfer danych między MySQL i aplikacją nie jest bez znaczenia nawet wtedy, kiedy i MySQL, i aplikacja znajdują się na tym samym serwerze. 172 | Rozdział 4. Optymalizacja wydajności zapytań Oczywiście, zapytanie pobierające ilość danych większą, niż faktycznie potrzeba, nie zawsze będzie złe. W wielu analizowanych przypadkach programiści twierdzili, że takie marno- trawne podejście upraszcza proces projektowania, a także pozwala na używanie tego samego fragmentu kodu w więcej niż tylko jednym miejscu. To dość rozsądne powody, przynajmniej tak długo, jak długo programista jest świadom kosztów mierzonych wydajnością. Pobieranie większej ilości danych, niż w rzeczywistości potrzeba, może być użyteczne także w przypadku stosowania w aplikacji pewnego rodzaju buforowania lub po uwzględnieniu innych korzyści. Pobieranie i buforowanie pełnych obiektów może być bardziej wskazane niż wykonywanie wielu oddzielnych zapytań, które pobierają jedynie fragmenty obiektu. Czy MySQL analizuje zbyt dużą ilość danych? Po upewnieniu się, że zapytania pobierają jedynie potrzebne dane, można zająć się zapytania- mi, które podczas generowania wyniku analizują zbyt wiele danych. W bazie danych MySQL najprostsze metody oceny kosztu zapytania to: • czas wykonywania zapytania, • liczba przeanalizowanych rekordów, • liczba zwróconych rekordów. Żadna z wymienionych miar nie jest doskonałym sposobem pomiaru kosztu zapytania, ale w przybliżeniu określają one ilość danych, do których MySQL musi wewnętrznie uzyskać dostęp, aby wykonać zapytanie. W przybliżeniu podają także szybkość wykonywania zapytania. Wszystkie trzy wymienione miary są rejestrowane w dzienniku wolnych zapytań. Dlatego też przejrzenie tego dziennika jest jednym z najlepszych sposobów wykrycia zapytań, które analizują zbyt wiele danych. Czas wykonywania zapytania Jak wspomniano w rozdziale 2., standardowa funkcja rejestrowania wolnych zapytań w MySQL 5.0 oraz wcześniejszych wersjach posiada wiele ograniczeń, m.in. brakuje obsługi bardziej szcze- gółowego poziomu rejestrowania. Na szczęście, istnieją poprawki pozwalające na rejestrowa- nie i analizowanie wolnych zapytań z dokładnością wyrażaną w mikrosekundach. Poprawki wprowadzono w MySQL 5.1, ale można je zastosować we wcześniejszych wersjach serwera, jeśli trzeba. Należy pamiętać, aby nie kłaść zbyt dużego nacisku na czas wykonywania za- pytania. Warto traktować go jak miarę obiektywną, która nie zachowuje spójności w różnych warunkach obciążenia. Inne czynniki — takie jak blokady silnika magazynu danych (blokady tabeli i rekordów), wysoki poziom współbieżności i używany osprzęt komputerowy — rów- nież mogą mieć istotny wpływ na czas wykonywania zapytania. Miara ta będzie użyteczna podczas wyszukiwania zapytań, które najbardziej wpływają na czas udzielenia odpowiedzi przez aplikację i najbardziej obciążają serwer, ale nie odpowie na pytanie, czy rzeczywisty czas udzielenia odpowiedzi jest rozsądny dla zapytania o podanym stopniu złożoności. (Czas wykonywania zapytania może być zarówno symptomem, jak i źródłem problemów, i nie zawsze jest oczywiste, z którym przypadkiem mamy do czynienia). Podstawy powolnych zapytań: optymalizacja dostępu do danych | 173 Rekordy przeanalizowane i rekordy zwrócone Podczas analizowania zapytań warto pochylić się nad liczbą rekordów sprawdzanych przez zapytanie, ponieważ dzięki temu można poznać efektywność zapytań w wyszukiwaniu po- trzebnych danych. Jednak, podobnie jak w przypadku czasu wykonywania zapytania, nie jest to doskonała miara w trakcie wyszukiwania błędnych zapytań. Nie wszystkie operacje dostępu do rekordów są takie same. Krótsze rekordy pozwalają na szybszy dostęp, a pobie- ranie rekordów z pamięci jest znacznie szybsze niż ich odczytywanie z dysku twardego. W idealnej sytuacji liczba przeanalizowanych rekordów powinna być równa liczbie zwróco- nych rekordów, ale w praktyce rzadko ma to miejsce. Przykładowo podczas budowania re- kordów w operacjach złączeń w celu wygenerowania każdego rekordu zbioru wynikowego serwer musi uzyskać dostęp do wielu innych rekordów. Współczynnik liczby rekordów prze- analizowanych do liczby rekordów zwróconych zwykle jest mały — powiedzmy między 1:1 i 10:1 — ale czasami może być większy o rząd wielkości. Rekordy przeanalizowane i rodzaje dostępu do danych Podczas zastanawiania się nad kosztem zapytania trzeba rozważyć także koszt związany ze znalezieniem pojedynczego rekordu w tabeli. Baza danych może używać wiele metod dostępu pozwalających na odszukanie i zwrócenie rekordu. Niektóre z nich wymagają przeanalizo- wania wielu rekordów, podczas gdy inne mogą mieć możliwość wygenerowania wyniku bez potrzeby analizowania jakiegokolwiek rekordu. Rodzaj metody (lub metod) dostępu jest wyświetlany w kolumnie type danych wyjściowych polecenia EXPLAIN. Zakres stosowanych rodzajów dostępu obejmuje zarówno pełne skano- wanie tabeli, jak i skanowanie indeksu, a także skanowanie zakresu, wyszukiwanie unikal- nego indeksu oraz stałych. Każda z nich jest szybsza od poprzedniej, ponieważ wymaga od- czytu mniejszej ilości danych. Czytelnik nie musi uczyć się na pamięć metod dostępu, ale powinien zrozumieć ogólną koncepcję skanowania tabeli, skanowania indeksu, dostępu do zakresu oraz dostępu do pojedynczej wartości. Jeżeli używana metoda dostępu jest nieodpowiednia, wówczas najlepszym sposobem roz- wiązania problemu zwykle będzie dodanie właściwego indeksu. Szczegółowe omówienie in- deksów przedstawiono w poprzednim rozdziale. Teraz widać, dlaczego indeksy są tak ważne podczas optymalizacji zapytań. Indeksy pozwalają bazie danych MySQL na wyszukiwanie rekordów za pomocą efektywniejszych metod dostępu, które analizują mniejszą ilość danych. Warto np. spojrzeć na proste zapytanie do przykładowej bazy danych Sakila: mysql SELECT * FROM sakila.film_actor WHERE film_id = 1; Powyższe zapytanie zwróci dziesięć rekordów, a polecenie EXPLAIN pokazuje, że w celu wy- konania zapytania MySQL stosuje metodę dostępu ref względem indeksu idx_fk_film: mysql EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1G *************************** Rekord 1. *************************** id: 1 select_type: SIMPLE table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: const rows: 10 Extra: 174 | Rozdział 4. Optymalizacja wydajności zapytań Dane wyjściowe polecenia EXPLAIN pokazują, że baza danych MySQL oszacowała na dziesięć liczbę rekordów, do których musi uzyskać dostęp. Innymi słowy, optymalizator wiedział, że wybrana metoda dostępu jest wystarczająca w celu efektywnego wykonania zapytania. Co się stanie, jeżeli dla zapytania nie zostanie znaleziony odpowiedni indeks? Serwer MySQL może wykorzystać mniej optymalną metodę dostępu, o czym można się przekonać, usuwając indeks i ponownie wydając to samo polecenie: mysql ALTER TABLE sakila.film_actor DROP FOREIGN KEY fk_film_actor_film; mysql ALTER TABLE sakila.film_actor DROP KEY idx_fk_film_id; mysql EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1G *************************** Rekord 1. *************************** id: 1 select_type: SIMPLE table: film_actor type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5073 Extra: Using where Zgodnie z przewidywaniami, metoda dostępu została zmieniona na pełne skanowanie tabeli (ALL) i baza danych MySQL oszacowała, że musi przeanalizować 5073 rekordy, aby wykonać zapytanie. Ciąg tekstowy „Using where” w kolumnie Extra wskazuje, że serwer MySQL używa klauzuli WHERE do odrzucenia rekordów po ich odczytaniu przez silnik magazynu danych. Ogólnie rzecz biorąc, MySQL może zastosować klauzulę WHERE na trzy wymienione niżej sposoby, od najlepszego do najgorszego. • Zastosowanie warunków w operacji przeszukiwania indeksu w celu wyeliminowania niepasujących rekordów. To zachodzi na poziomie silnika magazynu danych. • Użycie indeksu pokrywającego (ciąg tekstowy „Using index” w kolumnie Extra) w celu uniknięcia bezpośredniego dostępu do rekordu i odfiltrowanie niepasujących rekordów po pobraniu każdego wyniku z indeksu. To zachodzi na poziomie serwera, ale nie wymaga odczytywania rekordów z tabeli. • Pobranie rekordów z tabeli, a następnie odfiltrowanie niepasujących (ciąg tekstowy „Using where” w kolumnie Extra). To zachodzi na poziomie serwera i wymaga, aby serwer od- czytał rekordy z tabeli przed rozpoczęciem ich filtrowania. Powyższy przykład pokazuje więc, jak ważne jest tworzenie właściwych indeksów. Dobre indeksy pomagają zapytaniom w wyborze lepszej metody dostępu, a tym samym powodują analizowanie jedynie potrzebnych rekordów. Jednak dodanie indeksu nie zawsze oznacza, że baza danych MySQL uzyska dostęp i zwróci tę samą liczbę rekordów. Poniżej jako przykład przedstawiono zapytanie używające funkcji agregującej COUNT()2: mysql SELECT actor_id, COUNT(*) FROM sakila.film_actor GROUP BY actor_id; Powyższe zapytanie zwróci jedynie 200 rekordów, ale w celu zbudowania zbioru wynikowe- go musi ich odczytać tysiące. W takim zapytaniu indeks nie zredukuje liczby analizowanych rekordów. 2 Więcej informacji na ten temat przedstawiono w podrozdziale „Optymalizacja zapytań COUNT()”, znajdującym się w dalszej części rozdziału. Podstawy powolnych zapytań: optymalizacja dostępu do danych | 175 Niestety, baza danych MySQL nie wskaże programiście liczby rekordów, do których uzy- skała dostęp podczas budowy zbioru wynikowego, informuje jedynie o ogólnej liczbie rekor- dów, z których skorzystała. Wiele tych rekordów mogłoby zostać wyeliminowanych za po- mocą klauzuli WHERE, a tym samym nie brałoby udziału w budowaniu zbioru wynikowego. W poprzednim przykładzie po usunięciu indeksu z tabeli sakila.film_actor zapytanie sprawdzało każdy rekord tabeli, a klauzula WHERE odrzuciła wszystkie, poza dziesięcioma. A więc pozostawione dziesięć rekordów utworzyło zbiór wynikowy. Zrozumienie, ile rekor- dów serwer przeanalizuje i ile faktycznie zostanie użytych do zbudowania zbioru wyniko- wego, wymaga umiejętności wyciągania wniosków z zapytania. Jeżeli programista stwierdzi, że w celu zbudowania zbioru wynikowego obejmującego względ- nie małą liczbę rekordów jest analizowana duża liczba rekordów, wówczas można wypró- bować bardziej zaawansowane techniki, czyli: • użycie indeksów pokrywających przechowujących dane, wtedy silnik magazynu danych nie musi pobierać pełnych rekordów (indeksy pokrywające zostały omówione w po- przednim rozdziale), • zmianę schematu; można np. zastosować tabele podsumowań (omówione w poprzednim rozdziale), • przepisanie skomplikowanego zapytania, aby optymalizator MySQL mógł wykonać je w sposób optymalny (temat ten został przedstawiony w dalszej części rozdziału). Sposoby restrukturyzacji zapytań Podczas optymalizacji problematycznych zapytań celem powinno być odnalezienie alterna- tywnych sposobów otrzymania pożądanego wyniku — choć niekoniecznie oznacza to otrzy- manie takiego samego wyniku z bazy danych MySQL. Czasami zapytania udaje przekształcić się tak, aby uzyskać jeszcze lepszą wydajność. Jednak warto także rozważyć napisanie zapy- tania od nowa w celu otrzymania innych wyników, jeśli przyniesie to znaczące korzyści w zakresie wydajności. Być może programista będzie mógł ostatecznie wykonać to samo zadanie poprzez zmianę zarówno kodu aplikacji, jak i zapytania. W podrozdziale zostaną przedstawione techniki, które mogą pomóc w restrukturyzacji szerokiego zakresu zapytań, a także przykłady, kiedy można zastosować każdą z omówionych technik. Zapytanie skomplikowane kontra wiele mniejszych Oto jedno z najważniejszych pytań dotyczących projektu: „Czy bardziej pożądane jest podzielenie zapytania skomplikowanego na kilka prostszych?”. Tradycyjne podejście do projektu bazy danych kładzie nacisk na wykonanie maksymalnej ilości pracy za pomocą mi- nimalnej możliwej liczby zapytań. Takie podejście było w przeszłości uznawane za lepsze z powodu kosztu komunikacji sieciowej oraz obciążenia na etapie przetwarzania zapytania i optymalizacji. Jednak rada ta nie zawsze jest właściwa w przypadku bazy danych MySQL, ponieważ zo- stała ona zaprojektowana w celu efektywnej obsługi operacji nawiązywania i zamykania po- łączenia oraz szybkiego udzielania odpowiedzi na małe i proste zapytania. Nowoczesne sieci są również znacznie szybsze niż w przeszłości, co zmniejsza ich opóźnienie. Serwer MySQL 176 | Rozdział 4. Optymalizacja wydajności zapytań może wykonywać ponad 50000 prostych zapytań na sekundę, korzystając z przeciętnego osprzętu komputerowego, oraz ponad 2000 zapytań na sekundę poprzez pojedynczy port sieciowy o przepustowości gigabitu. Dlatego też wykonywanie wielu zapytań niekoniecznie musi być złym rozwiązaniem. Czas udzielenia odpowiedzi poprzez sieć nadal jest stosunkowo długi w porównaniu do liczby rekordów, które MySQL może wewnętrznie przekazywać w ciągu sekundy. Wymie- nioną liczbę szacuje się na milion w ciągu sekundy w przypadku danych znajdujących się w pamięci. Zatem nadal dobrym pomysłem jest stosowanie minimalnej liczby zapytań po- zwalającej na wykonanie zadania. Jednak czasami zapytanie może być bardziej efektywne po rozłożeniu na części i wykonaniu kilku prostych zapytań zamiast jednego złożonego. Nie należy się obawiać tego rodzaju sytuacji, najlepiej ocenić koszty, a następnie wybrać strategię wymagającą mniejszego nakładu pracy. Przykłady takiej techniki zostaną zaprezentowane w dalszej części rozdziału. Mając to na uwadze, warto pamiętać, że używanie zbyt wielu zapytań jest błędem często po- pełnianym w projekcie aplikacji. Przykładowo niektóre aplikacje wykonują dziesięć zapytań pobierających pojedynczy rekord danych z tabeli, zamiast użyć jednego pobierającego dziesięć rekordów. Autorzy spotkali się z aplikacjami pobierającymi oddzielnie każdą kolumnę, czyli wykonującymi wielokrotne zapytania do każdego rekordu! Podział zapytania Innym sposobem podziału zapytania jest technika „dziel i rządź”, w zasadzie oznaczająca to samo, ale przeprowadzana w mniejszych „fragmentach”, które każdorazowo wpływają na mniejszą liczbę rekordów. Usuwanie starych danych to doskonały przykład. Okresowe zadania czyszczące mogą mieć do usunięcia całkiem sporą ilość danych, a wykonanie tego za pomocą jednego ogromnego zapytania może na bardzo długi czas zablokować dużą ilość rekordów, zapełnić dziennik zdarzeń transakcji, zużyć wszystkie dostępne zasoby oraz zablokować małe zapytania, któ- rych wykonywanie nie powinno być przerywane. Podział zapytania DELETE i użycie zapytań o średniej wielkości może znacząco wpłynąć na zwiększenie wydajności oraz zredukować opóźnienie podczas replikacji tego zapytania. Przykładowo zamiast wykonywania przedsta- wionego poniżej monolitycznego zapytania: mysql DELETE FROM messages WHERE created DATE_SUB(NOW(),INTERVAL 3 MONTH); warto wykonać poniższy pseudokod: rows_affected = 0 do { rows_affected = do_query( DELETE FROM messages WHERE created DATE_SUB(NOW(),INTERVAL 3 MONTH) LIMIT 10000 ) } while rows_affected 0 Usunięcie jednorazowo dziesięciu tysięcy rekordów jest zazwyczaj na tyle dużym zadaniem, aby spowodować efektywne wykonanie każdego zapytania, i jednocześnie na tyle krótkim, aby zminimalizować jego wpływ na serwer3 (silniki magazynu danych obsługujące transakcje mogą osiągnąć lepszą wydajność podczas wykonywania mniejszych zapytań). Dobrym rozwiązaniem 3 Narzędzie mk-archiver z pakietu Maatkit bardzo łatwo wykonuje takie zadania. Sposoby restrukturyzacji zapytań | 177 może być również zastosowanie pewnego rodzaju przerwy między poleceniami DELETE. W ten sposób następuje rozłożenie obciążenia w czasie oraz zredukowanie okresu czasu, przez który są nałożone blokady. Podział złączeń Wiele witryn internetowych o wysokiej wydajności stosuje podział złączeń, który polega na rozdzieleniu jednego złączenia obejmującego wiele tabel na kilka zapytań obejmujących jedną tabelę, a następnie wykonaniu złączenia w aplikacji. I tak zamiast poniższego zapytania: mysql SELECT * FROM tag - JOIN tag_post ON tag_post.tag_id=tag.id - JOIN post ON tag_post.post_id=post.id - WHERE tag.tag= mysql ; można wykonać następujące: mysql SELECT * FROM tag WHERE tag= mysql ; mysql SELECT * FROM tag_post WHERE tag_id=1234; mysql SELECT * FROM post WHERE post.id in (123,456,567,9098,8904); Na pierwszy rzut oka wygląda to na marnotrawstwo, ponieważ zwiększono liczbę zapytań bez otrzymania innych wyników. Jednak tego rodzaju restrukturyzacja może w rzeczywistości przynieść wyraźne korzyści w zakresie wydajności. • Buforowanie może być efektywniejsze. Wiele aplikacji buforuje „obiekty”, które mapują bezpośrednio do tabel. W przedstawionym powyżej przykładzie aplikacja pominie pierwsze zapytanie, jeżeli obiekt ze znacznikiem mysql jest już buforowany. Jeżeli w bu- forze znajdą się posty o wartości identyfikatora id wynoszącej 123, 567 lub 9098, wtedy można usunąć je z listy IN(). Bufor zapytania także może skorzystać na takiej strategii. Jeśli częstym zmianom ulega tylko jedna tabela, podział złączenia może zredukować liczbę nieprawidłowości w buforze. • W tabelach MyISAM wykonywanie jednego zapytania na tabelę znacznie efektywniej stosuje blokady tabel: zapytania blokują tabele po kolei i na względnie krótki okres czasu, zamiast jednocześnie zablokować wszystkie na dłuższy okres czasu. • Przeprowadzanie złączeń w aplikacji znacznie ułatwia skalowalność bazy danych poprzez umieszczenie tabel w różnych serwerach. • Same zapytania również mogą być efektywniejsze. W powyższym przykładzie użycie listy IN() zamiast złączenia pozwala serwerowi MySQL na sortowanie identyfikatorów rekor- dów i bardziej optymalne pobieranie rekordów, niż byłoby to możliwe za pomocą złączenia. Zostanie to szczegółowo omówione w dalszej części rozdziału. • Istnieje możliwość zmniejszenia liczby nadmiarowych operacji dostępu do rekordów. Prze- prowadzenie złączenia w aplikacji oznacza, że każdy rekord jest pobierany tylko jednokrot- nie, podczas gdy złączenie w zapytaniu w zasadzie jest denormalizacją, która może wymagać wielokrotnego dostępu do tych samych danych. Z tego samego powodu restrukturyzacja taka może też zredukować ogólny poziom ruchu sieciowego oraz zużycie pamięci. • W pewnej mierze technikę tę można potraktować jako ręczną implementację złączenia typu hash zamiast algorytmu zagnieżdżonych pętli używanych przez MySQL do prze- prowadzenia złączenia. Takie złączenie typu hash może być efektywniejsze. (Strategie złączeń w MySQL zostały przeanalizowane w dalszej części rozdziału). 178 | Rozdział 4. Optymalizacja wydajności zapytań Podsumowanie. Kiedy przeprowadzanie złączeń w aplikacji może być efektywniejsze? Przeprowadzanie złączeń w aplikacji może być efektywniejsze, gdy: • buforowana i ponownie używana jest duża ilość danych z poprzednich zapytań, • używanych jest wiele tabel MyISAM, • dane są rozproszone na wielu serwerach, • w ogromnych tabelach złączenia są zastępowane listami IN(), • złączenie odwołuje się wielokrotnie do tej samej tabeli. Podstawy wykonywania zapytań Jeżeli programiście zależy na osiągnięciu wysokiej wydajności działania serwera MySQL, jedną z najlepszych inwestycji będzie poznanie sposobów, w jakie MySQL optymalizuje i wy- konuje zapytania. Po zrozumieniu tego zagadnienia większość procesów optymalizacji za- pytania stanie się po prostu kwestią wyciągania odpowiednich wniosków, a sama optymali- zacja zapytania okaże się procesem logicznym. W poniższej analizie autorzy zakładają, że czytelnik zapoznał się z rozdziałem 2., w którym przedstawiono m.in. silniki wykonywania zapytań w MySQL i podstawy ich działania. Na rysunku 4.1 pokazano ogólny sposób wykonywania zapytań przez MySQL. Korzystając z rysunku, można zilustrować procesy zachodzące po wysłaniu zapytania do MySQL. 1. Klient wysyła polecenie SQL do serwera. 2. Serwer sprawdza bufor zapytań. Jeżeli dane zapytanie znajduje się w buforze, wyniki są pobierane z bufora. W przeciwnym razie polecenie SQL zostaje przekazane do kolejnego kroku. 3. Serwer analizuje, przetwarza i optymalizuje SQL na postać planu wykonania zapytania. 4. Silnik wykonywania zapytań realizuje plan poprzez wykonanie wywołań do API silnika magazynu danych. 5. Serwer zwraca klientowi wyniki zapytania. Każdy z powyższych kroków wiąże się z pewnym poziomem złożoności, co będzie przeana- lizowane w kolejnych podrozdziałach. Ponadto zostaną przedstawione stany, w których za- pytanie znajduje się podczas realizacji poszczególnych kroków. Proces optymalizacji zapytania jest szczególnie złożony i jednocześnie najważniejszy do zrozumienia. Podstawy wykonywania zapytań | 179 Rysunek 4.1. Ogólny sposób wykonywania zapytania w MySQL Protokół klient-serwer MySQL Chociaż nie jest konieczne zrozumienie wewnętrznych szczegółów protokołu klient-serwer MySQL, jednak trzeba zrozumieć jego działanie na wysokim poziomie. Protokół jest półdu- pleksowy, co oznacza, że w danej chwili serwer MySQL może albo wysyłać, albo odbierać komunikaty, ale nie jedno i drugie jednocześnie. Oznacza to także brak możliwości skrócenia komunikatu. Protokół powoduje, że komunikacja MySQL jest prosta i szybka, ale równocześnie na pewne sposoby ją ogranicza. Z tego powodu brakuje kontroli przepływu — kiedy jedna strona wyśle komunikat, druga strona musi pobrać cały komunikat, zanim będzie mogła udzielić odpo- wiedzi. Przypomina to grę polegającą na rzucaniu piłki między uczestnikami: w danej chwili tylko jeden gracz ma piłkę, a więc inny gracz nie może rzucić piłką (wysłać komunikatu), zanim faktycznie jej nie otrzyma. 180 | Rozdział 4. Optymalizacja wydajności zapytań Klient wysyła zapytanie do serwera jako pojedynczy pakiet danych. To jest powód, dla któ- rego konfiguracja zmiennej max_packet_size ma tak istotne znaczenie, gdy wykonywane są ogromne zapytania4. Po wysłaniu zapytania przez klienta piłka nie znajduje się już po jego stronie i może jedynie czekać na otrzymanie wyników. Natomiast odpowiedź udzielana przez serwer, w przeciwieństwie do zapytania, zwykle składa się z wielu pakietów danych. Kiedy serwer udzieli odpowiedzi, klient musi otrzymać cały zbiór wynikowy. Nie może pobrać kilku rekordów, a następnie poprosić serwer o za- przestanie wysyłania pozostałych. Jeżeli klientowi potrzebne jest jedynie kilka pierwszych rekordów ze zbioru wynikowego, to albo może poczekać na otrzymanie wszystkich pakietów wysłanych przez serwer i odrzuć niepotrzebne, albo w sposób nieelegancki zerwać połączenie. Żadna z wymienionych możliwości nie jest dobrym rozwiązaniem i to kolejny powód, dla którego odpowiednie klauzule LIMIT mają tak istotne znaczenie. Oto inny sposób przedstawienia tego procesu: kiedy klient pobiera rekordy z serwera, wtedy sądzi, że je wyciąga. Jednak w rzeczywistości to serwer MySQL wypycha rekordy podczas ich generowania. Klient jest jedynie odbiorcą wypchniętych rekordów, nie ma możliwości naka- zania serwerowi, aby zaprzestał wysyłania rekordów. Używając innego porównania, można powiedzieć, że „klient pije z węża strażackiego”. (Tak, to jest pojęcie techniczne). Większość bibliotek nawiązujących połączenie z bazą danych MySQL pozwala na pobranie całego zbioru wynikowego i jego buforowanie w pamięci albo pobieranie poszczególnych rekordów, gdy będą potrzebne. Zazwyczaj zachowaniem domyślnym jest pobranie całego zbioru wynikowego i buforowanie go w pamięci. To jest bardzo ważne, ponieważ dopóki wszystkie rekordy nie zostaną dostarczone, dopóty serwer MySQL nie zwolni blokad oraz innych zasobów wymaganych przez dane zapytanie. Zapytanie będzie znajdowało się w stanie „Sending data” (stany zostaną omówione w kolejnym podrozdziale zatytułowanym „Stany zapytania”). Kiedy biblioteka klienta jednorazowo pobierze wszystkie rekordy, wtedy redukuje ilość pracy wykonywaną przez serwer: tzn. serwer może zakończyć wykonywanie zapytania i przeprowadzić czyszczenie po nim tak szybko, jak to możliwe. Większość bibliotek klienckich pozwala na traktowanie zbioru wynikowego tak, jakby był pobie- rany z serwera. Jednak w rzeczywistości rekordy są pobierane z bufora w pamięci biblioteki. W większości sytuacji takie rozwiązanie sprawdza się doskonale, ale nie jest odpowiednie dla ogromnych zbiorów wynikowych, ponieważ ich pobranie zabiera dużo czasu oraz wymaga du- żych ilości pamięci. Poprzez zakazanie bibliotece buforowania wyniku można użyć mniejszej ilo- ści pamięci oraz szybciej rozpocząć pracę ze zbiorem wynikowym. Wadą takiego rozwiązania są blokady oraz inne zasoby serwera otwarte w czasie, kiedy aplikacja współdziała z biblioteką5. Warto spojrzeć na przykład w języku PHP. W poniższym kodzie pokazano, w jaki sposób najczęściej następuje wykonanie zapytania MySQL z poziomu PHP: ?php $link = mysql_connect( localhost , user , p4ssword ); $result = mysql_query( SELECT * FROM HUGE_TABLE , $link); while ($row = mysql_fetch_array($result)) { // Dowolny kod przetwarzający wyniki zapytania. } ? 4 Jeżeli zapytanie będzie zbyt duże, serwer odmówi przyjęcia kolejnych danych i nastąpi wygenerowanie błędu. 5 Rozwiązaniem problemu może być opcja SQL_BUFFER_RESULT, która zostanie przedstawiona w dalszej części rozdziału. Podstawy wykonywania zapytań | 181 Kod wydaje się wskazywać, że w pętli while rekordy są pobierane jedynie wtedy, gdy są potrzebne. Jednak w rzeczywistości za pomocą wywołania funkcji mysql_query() kod po- biera cały zbiór wynikowy i umieszcza go w buforze. Pętla while po prostu przechodzi przez poszczególne elementy bufora. Natomiast poniższy kod w ogóle nie buforuje wyników, po- nieważ zamiast funkcji mysql_query() używa funkcji mysql_unbuffered_query(): ?php $link = mysql_connect( localhost , user , p4ssword ); $result = mysql_unbuffered_query( SELECT * FROM HUGE_TABLE , $link); while ($row = mysql_fetch_array($result)) { // Dowolny kod przetwarzający wyniki zapytania. } ? Języki programowania oferują różne sposoby uniknięcia buforowania. Przykładowo sterownik Perla DBD:mysql wymaga użycia atrybutu mysql_use_result w bibliotece języka C po stronie klienta (atrybutem domyślnym jest mysql_buffer_result). Poniżej przedstawiono przykład: #!/usr/bin/perl use DBI; my $dbh = DBI- connect( DBI:mysql:;host=localhost , user , p4ssword ); my $sth = $dbh- prepare( SELECT * FROM HUGE_TABLE , { mysql_use_result = 1 }); $sth- execute(); while (my $row = $sth- fetchrow_array()) { # Dowolny kod przetwarzający wyniki zapytania. } Warto zwrócić uwagę, że wywołanie funkcji prepare() zakłada użycie wyniku zamiast jego buforowania. Można to również określić podczas nawiązywania połączenia, które spowoduje, że żadne polecenie nie będzie buforowane: my $dbh = DBI- connect( DBI:mysql:;mysql_use_result=1 , user , p4ssword ); Stany zapytania Każde połączenie MySQL, czyli wątek, posiada stan wskazujący to, co dzieje się z nim w da- nej chwili. Istnieje kilka sposobów sprawdzenia tego stanu, ale najłatwiejszym pozostaje uży- cie polecenia SHOW FULL PROCESSLIST (stan jest wyświetlany w kolumnie Command). Wraz z postępem realizacji zapytania, czyli przechodzeniem przez cykl życiowy, stan zmienia się wielokrotnie, a samych stanów są dziesiątki. Podręcznik użytkownika MySQL jest odpo- wiednim źródłem informacji o wszystkich stanach, ale poniżej przedstawiono kilka z nich wraz z objaśnieniem znaczenia. Sleep Wątek oczekuje na nowe zapytanie od klienta. Query Wątek albo wykonuje zapytanie, albo odsyła klientowi wyniki danego zapytania. Locked Wątek oczekuje na nałożenie blokady tabeli na poziomie serwera. Blokady, które są implementowane przez silnik magazynu danych, np. blokady rekordów w InnoDB, nie powodują przejścia wątku w stan Locked. Analyzing oraz statistics Wątek sprawdza dane statystyczne silnika magazynu danych oraz optymalizuje zapytanie. 182 | Rozdział 4. Optymalizacja wydajności zapytań Copying to tmp table [on disk] Wątek przetwarza zapytanie oraz kopiuje wyniki do tabeli tymczasowej, prawdopodob- nie ze względu na klauzulę GROUP BY, w celu posortowania lub spełnienia klauzuli UNION. Jeżeli nazwa stanu kończy się ciągiem tekstowym „on disk”, wtedy MySQL konwertuje tabelę znajdującą się w pamięci na tabelę zapisaną na dysku twardym. Sorting result Wątek sortuje zbiór wynikowy. Sending data Ten stan może mieć kilka znaczeń: wątek może przesyłać dane między stanami zapytania, generować zbiór wynikowy bądź zwracać klientowi zbiór wynikowy. Przydatna jest znajomość przynajmniej podstawowych stanów zapytania, aby można było złapać sens „po czyjej stronie jest piłka”, czyli zapytanie. W przypadku bardzo obciążonych serwerów można zaobserwować, że niecodzienne lub zazwyczaj krótkotrwałe stany, np. statistics, zaczynają zabierać znaczące ilości czasu. Zwykle wskazuje to pewne nie- prawidłowości. Bufor zapytania Przed rozpoczęciem przetwarzania zapytania MySQL sprawdza, czy dane zapytanie znajduje się w buforze zapytań, o ile został włączony. Operacja ta jest wyszukiwaniem typu hash, w którym ma znaczenie wielkość liter. Jeżeli zapytanie różni się od zapytania znalezionego w buforze nawet tylko o pojedynczy bajt, nie zostanie dopasowane i proces przetwarzania zapytania przejdzie do kolejnego etapu. Jeżeli MySQL znajdzie dopasowanie w buforze zapytań, wówczas przed zwróceniem bufo- rowanych wyników musi sprawdzić uprawnienia. Ta czynność jest możliwa bez przetwarza- nia zapytania, ponieważ MySQL wraz z buforowanym zapytaniem przechowuje tabelę informacyjną. Gdy uprawnienia są w porządku, MySQL pobiera z bufora przechowywany wynik zapytania i wysyła go klientowi, pomijając pozostałe etapy procesu wykonywania za- pytania. To zapytanie nigdy nie będzie przetworzone, zoptymalizowane bądź wykonane. Więcej informacji na temat bufora zapytań znajduje się w rozdziale 5. Proces optymalizacji zapytania Kolejny krok w cyklu życiowym zapytania powoduje zmianę zapytania SQL na postać planu wykonywania przeznaczoną dla silnika wykonywania zapytań. Krok ten ma kilka etapów pośrednich: analizowanie, przetwarzanie oraz optymalizację. Błędy (np. błędy składni) mogą być zgłoszone w dowolnym miejscu tego procesu. Autorzy w tym miejscu nie próbują udo- kumentować wnętrza bazy danych MySQL, a więc pozwolą sobie na pewną swobodę, np. opisywanie etapów oddzielnie, nawet jeśli często są ze sobą łączone w całość bądź częściowo, ze względu na wydajność. Celem autorów jest po prostu pomoc czytelnikowi w zrozumieniu, jak MySQL wykonuje zapytania oraz jak można utworzyć lepsze. Podstawy wykonywania zapytań | 183 Analizator składni i preprocesor Na początek analizator MySQL dzieli zapytanie na tokeny i na ich podstawie buduje „drzewo analizy”. W celu interpretacji i weryfikacji zapytania analizator wykorzystuje gramatykę SQL bazy danych MySQL. Ten etap gwarantuje, że tokeny w zapytaniu są prawidłowe i znajdują się we właściwej kolejności. Ponadto następuje sprawdzenie pod kątem występowania błędów, takich jak ciągi tekstowe ujęte w cudzysłów, które nie zostały prawidłowo zakończone. Następnie preprocesor weryfikuje otrzymane drzewo analizy pod kątem dodatkowej seman- tyki, której analizator nie mógł zastosować. Przykładowo preprocesor sprawdza istnienie tabel i kolumn, a także nazwy i aliasy, aby upewnić się, że odniesienie nie są dwuznaczne. Kolejny etap to weryfikacja uprawnień przez preprocesor. Czynność zwykle jest bardzo szybka, chyba że serwer posiada ogromną liczbę uprawnień. (Więcej informacji na temat uprawnień i bezpieczeństwa znajduje się w rozdziale 12.). Optymalizator zapytania Na tym etapie drzewo analizy jest poprawne i przygotowane do tego, aby optymalizator prze- kształcił je na postać planu wykonywania zapytania. Zapytanie często może być wykonywane na wiele różnych sposobów, generując takie same wyniki. Zadaniem optymalizatora jest znale- zienie najlepszej opcji. Baza danych MySQL stosuje optymalizator kosztowy, co oznacza, że optymalizator próbuje przewidzieć koszt różnych wariantów planu wykonania i wybrać najtańszy. Jednostką kosztu jest odczytanie pojedynczej, losowo wybranej strony danych o wielkości czterech kilobajtów. Istnieje możliwość sprawdzenia oszacowanego przez optymalizator kosztu zapytania prze- znaczonego do wykonania poprzez wyświetlenie wartości zmiennej Last_query_cost: mysql SELECT SQL_NO_CACHE COUNT(*) FROM sakila.film_actor; +----------+ | count(*) | +----------+ | 5462 | +----------+ mysql SHOW STATUS LIKE last_query_cost ; +-----------------+-------------+ | Variable_name | Value | +-----------------+-------------+ | Last_query_cost | 1040.599000 | +-----------------+-------------+ Powyższy wynik oznacza, że optymalizator oszacował liczbę losowo odczytywanych stron danych koniecznych do wykonania zapytania na 1040. Wynik jest obliczany na podstawie danych statystycznych: liczby stron w tabeli bądź indeksie, liczebności (liczby odmiennych wartości) indeksów, długości rekordów i kluczy oraz rozproszenia klucza. W trakcie obliczeń optymalizator nie bierze pod uwagę wpływu jakiegokolwiek mechanizmu buforowania — zakłada, że każdy odczyt będzie skutkował operacją I/O na dysku twardym. Z wielu podanych niżej powodów optymalizator nie zawsze wybiera najlepszy plan. • Dane statystyczne mogą być błędne. Serwer polega na danych statystycznych dostarczanych przez silnik magazynu danych, a one mogą znajdować się w zakresie od ściśle dokładnych aż do zupełnie niedokładnych. Przykładowo silnik InnoDB nie zawiera dokładnych danych statystycznych na temat liczby rekordów w tabeli, co jest związane z jego architekturą MVCC. 184 | Rozdział 4. Optymalizacja wydajności zapytań • Koszt metryczny nie zawsze dokładnie odpowiada rzeczywistemu kosztowi wykonania zapytania. Dlatego też nawet wtedy, kiedy dane statystyczne są dokładne, wykonanie zapytania może być mniej lub bardziej kosztowne, niż wynika to z obliczeń MySQL. W nie- których sytuacjach plan odczytujący większą liczbę stron może faktycznie być tańszy, np. gdy odczyt danych jest ciągły, ponieważ wtedy operacje I/O na dysku są szybsze, lub jeśli odczytywane strony zostały wcześniej buforowane w pamięci. • Znaczenie optymalności dla MySQL nie musi pokrywać się z oczekiwaniami programisty. Programista prawdopodobnie dąży do osiągnięcia krótszego czasu wykonania zapytania, ale MySQL w rzeczywistości nie rozumie pojęcia „krótsze”. Rozumie jednak pojęcie „koszt” i, jak wcześniej pokazano, oszacowanie kosztu nie zawsze jest nauką ścisłą. • MySQL nie bierze pod uwagę innych zapytań wykonywanych w tym samym czasie, co jednak ma wpływ na szybkość wykonywania danego zapytania. • MySQL nie zawsze wykorzystuje optymalizację na podstawie kosztu. Czasami po prostu stosuje się do reguł, np. takiej: „Jeśli w zapytaniu znajduje się klauzula MATCH() dopasowania pełnotekstowego, użyj indeksu FULLTEXT, o ile taki istnieje”. Serwer wykona to nawet wtedy, kiedy szybszym rozwiązaniem będzie użycie innego indeksu oraz zapytanie innego rodzaju niż FULLTEXT, zawierające klauzulę WHERE. • Optymalizator nie bierze pod uwagę kosztów operacji pozostających poza jego kontrolą, ta- kich jak wykonanie procedur składowanych lub funkcji zdefiniowanych przez użytkownika. • W dalszej części rozdziału zostanie pokazane, że optymalizator nie zawsze oszacowuje każdy możliwy plan wykonywania, a więc istnieje niebezpieczeństwo pominięcia planu optymalnego. Optymalizator MySQL to bardzo skomplikowany fragment oprogramowania, który używa wielu optymalizacji w celu przekształcenia zapytania na postać planu wykonywania. Istnieją dwa rodzaje optymalizacji: statyczna i dynamiczna. Optymalizacja statyczna może być przepro- wadzona po prostu przez badanie drzewa analizy. Przykładowo optymalizator może prze- kształcić klauzulę WHERE na odpowiadającą jej inną formę za pomocą reguł algebraicznych. Optymalizacja statyczna dotyczy wartości niezależnych, np. wartości stałej w klauzuli WHERE. Ten rodzaj optymalizacji może być przeprowadzony jednokrotnie i pozostanie ważny nawet wtedy, kiedy zapytanie zostanie ponownie wykonane z użyciem innych wartości. Optymali- zację tę można traktować jak „optymalizację w trakcie kompilacji”. Optymalizacja dynamiczna, w przeciwieństwie do optymalizacji statycznej, bazuje na kontek- ście i może zależeć od wielu czynników, takich jak wartość w klauzuli WHERE lub liczba re- kordów w indeksie. Ten rodzaj optymalizacji musi być przeprowadzany w trakcie każdego wykonywania zapytania. Optymalizację tę można więc traktować jako „optymalizację w trakcie wykonywania zapytania”. Różnica między nimi jest istotna podczas wykonywania przygotowanych poleceń lub proce- dur składowanych. Optymalizację statyczną MySQL może przeprowadzić tylko jednokrotnie, ale optymalizację dynamiczną musi powtarzać w trakcie każdego wykonywania zapytania. Czasami zdarza się również, że MySQL ponownie optymalizuje zapytanie już w trakcie jego wykonywania6. 6 Przykładowo sprawdzenie zakresu planu wykonywania ponownie określa indeksy dla każdego rekordu złą- czenia (JOIN). Ten plan wykonywania można zobaczyć, szukając ciągu tekstowego „range checked for each record” w kolumnie Extra danych wyjściowych polecenia EXPLAIN. Taki plan zapytania zwiększa także o jednostkę wartość zmiennej serwera o nazwie Select_full_range_join. Podstawy wykonywania zapytań | 185 Poniżej przedstawiono kilka rodzajów optymalizacji, które MySQL może przeprowadzić. Zmiana kolejności złączeń Tabele nie zawsze muszą być złączone w kolejności wskazanej w zapytaniu. Określenie najlepszej kolejność złączeń jest bardzo ważnym rodzajem optymalizacji. Temat ten został dokładnie omówiony w podrozdziale „Optymalizator złączeń”, w tym rozdziale. Konwersja klauzuli OUTER JOIN na INNER JOIN Klauzula OUTER JOIN niekoniecznie musi być wykonana jako OUTER JOIN. Pewne czyn- niki, np. klauzula WHERE i schemat tabeli, mogą w rzeczywistości powodować, że klauzula OUTER JOIN będzie odpowiadała klauzuli INNER JOIN. Baza danych MySQL rozpoznaje takie sytuacje i przepisuje złączenie, co pozwala na zmianę ustawień. Zastosowanie algebraicznych odpowiedników reguł MySQL stosuje przekształcenia algebraiczne w celu uproszczenia wyrażeń i sprowadze- nia ich do postaci kanonicznej. Baza danych może również zredukować zmienne, elimi- nując niemożliwe do zastosowania ograniczenia oraz warunki w postaci zdefiniowanych stałych. Przykładowo wyrażenie (5=5 AND a 5) zostanie zredukowane do zwykłego a 5. Podobnie (a b ANB b=c) AND a=5 stanie się wyrażeniem b 5 AND b=c AND a=5. Te reguły są bardzo użyteczne podczas tworzenia zapytań warunkowych, które zostaną omówione w dalszej części rozdziału. Optymalizacja funkcji COUNT(), MIN() oraz MAX() Indeksy i kolumny akceptujące wartość NULL bardzo często mogą pomóc serwerowi MySQL w optymalizacji tych wyrażeń. Aby np. odnaleźć wartość minimalną kolumny wysuniętej najbardziej na lewo w indeksie B-Tree, MySQL może po prostu zażądać pierwszego rekordu indeksu. To może nastąpić nawet na etapie optymalizacji zapytania, a otrzymaną wartość serwer może potraktować jako stałą dla pozostałej części zapytania. Podobnie w celu znalezienia wartości maksymalnej w indeksie typu B-tree, serwer od- czytuje ostatni rekord. Jeżeli serwer stosuje taką optymalizację, wówczas w danych wyj- ściowych polecenia EXPLAIN znajdzie się ciąg tekstowy „Select tables optimized away”. Dosłownie oznacza to, że optymalizator usunął tabelę z planu wykonywania i zastąpił ją zmienną. Ponadto zapytania COUNT(*) bez klauzuli WHERE często mogą być optymalizowane w pewnych silnikach magazynu danych (np. MyISAM, który przez cały czas przechowuje dokładną liczbę rekordów tabeli). Więcej informacji na ten temat przedstawiono w podroz- dziale „Optymalizacja zapytań COUNT()”, znajdującym się w dalszej części rozdziału. Określanie i redukowanie wyrażeń stałych Kiedy MySQL wykryje, że wyrażenie może zostać zredukowane na postać stałej, wtedy taka operacja będzie przeprowadzona w trakcie optymalizacji. Przykładowo zmienna zdefiniowana przez użytkownika może być skonwertowana na postać stałej, jeśli nie ulega zmianie w zapytaniu. Wyrażenia arytmetyczne to kolejny przykład. Prawdopodobnie największym zaskoczeniem jest fakt, że na etapie optymalizacji nawet wyrażenie uważane za zapytanie może być zredukowane na postać stałej. Jednym z przy- kładów jest funkcja MIN() w indeksie. Można to nawet rozciągnąć na wyszukiwanie stałej w kluczu podstawowym lub unikalnym indeksie. Jeżeli w takim indeksie klauzula WHERE stosuje warunek w postaci stałej, wówczas optymizator „wie”, że MySQL może wyszu- kać wartość na początku zapytania. Wartość ta będzie traktowana jako stała w pozostałej części zapytania: 186 | Rozdział 4. Optymalizacja wydajności zapytań mysql EXPLAIN SELECT film.film_id, film_actor.actor_id - FROM sakila.film - INNER JOIN sakila.film_actor USING(film_id) - WHERE film.film_id = 1; +----+-------------+------------+-------+----------------+-------+------+ | id | select_type | table | type | key | ref | rows | +----+-------------+------------+-------+----------------+-------+------+ | 1 | SIMPLE | film | const | PRIMARY | const | 1 | | 1 | SIMPLE | film_actor | ref | idx_fk_film_id | const | 10 | +----+-------------+------------+-------+----------------+-------+------+ Powyższe zapytanie MySQL wykonuje w dwóch krokach, które odpowiadają dwóm rekordom danych wyjściowych. Pierwszym krokiem jest odszukanie pożądanego rekordu w tabeli film. Optymalizator MySQL wie, że to jest tylko jeden rekord, ponieważ ko- lumna film_id jest kluczem podstawowym. Poza tym, w trakcie optymalizacji zapytania indeks został już sprawdzony, aby przekonać się, ile rekordów będzie zwróconych. Ponieważ optymalizator znał ilość (wartość w klauzuli WHERE) używaną w zapytaniu, typ ref tej tabeli wynosi const. W drugim kroku MySQL traktuje kolumnę film_id z rekordu znalezionego w pierwszym kroku jako znaną ilość. Optymalizator może przyjąć takie założenie, ponieważ wiadomo, że gdy zapytanie dotrze do drugiego kroku, otrzyma wszystkie wartości z poprzedniego kroku. Warto zwrócić uwagę, że typ ref tabeli film_actor wynosi const, podobnie jak dla tabeli film. Inną sytuacją, w której można spotkać się z zastosowaniem warunku w postaci stałej, jest propagowanie wartości niebędącej stałą z jednego miejsca do innego, jeżeli występują klauzule WHERE, USING lub ON powodujące, że wartości są równe. W omawianym przy- padku optymalizator przyjmuje, że klauzula USING wymusza, aby kolumna film_id miała taką samą wartość w każdym miejscu zapytania — musi być równa wartości stałej podanej w klauzuli WHERE. Indeksy pokrywające Aby uniknąć odczytywania danych rekordów, MySQL może czasami użyć indeksu, ale indeks musi zawierać wszystkie kolumny wymagane przez zapytanie. Szczegółowe omówienie indeksów pokrywających przedstawiono w rozdziale 3. Optymalizacja podzapytania Baza danych MySQL może skonwertować niektóre rodzaje podzapytań na bardziej efektywne, alternatywne formy, redukując je do wyszukiwań indeksu zamiast oddzielnych zapytań. Wcześniejsze zakończenie zapytania MySQL może zakończyć przetwarzanie zapytania (lub etapu w zapytaniu), gdy tylko zo- stanie spełnione zapytanie albo jego etap. Oczywistym przykładem jest klauzula LIMIT, choć istnieje również kilka innych rodzajów wcześniejszego zakończenia zapytania. Jeżeli np. MySQL odkryje warunek niemożliwy do spełnienia, może przerwać wykonywanie całego zapytania. Taka sytuacja zachodzi w poniższym zapytaniu: mysql EXPLAIN SELECT film.film_id FROM sakila.film WHERE film_id = -1; +----+...+-----------------------------------------------------+ | id |...| Extra | +----+...+-----------------------------------------------------+ | 1 |...| Impossible WHERE noticed after reading const tables | +----+...+-----------------------------------------------------+ Podstawy wykonywania zapytań | 187 Zapytanie zostało przerwane na etapie optymalizacji, ale w niektórych sytuacjach MySQL może przerwać wykonywanie zapytania również wcześniej. Serwer może wyko- rzystać ten rodzaj optymalizacji, kiedy silnik wykonywania zapytań stwierdzi, że musi pobrać zupełnie inne wartości lub wymagana wartość nie istnieje. Przedstawione poniżej przykładowe zapytanie ma wyszukać wszystkie filmy, w których nie ma aktorów7: mysql SELECT film.film_id - FROM sakila.film - LEFT OUTER JOIN sakila.film_actor USING(film_id) - WHERE film_actor.film_id IS NULL; Powyższe zapytanie powoduje odrzucenie filmów, w których występują aktorzy. W każdym filmie może występować wielu aktorów, ale tuż po znalezieniu aktora następuje prze- rwanie przetwarzania bieżącego filmu i przejście do następnego. Dzieje się tak, ponieważ klauzula WHERE to informacja dla optymalizatora, że ma uniemożliwić wyświetlenie filmów, w których występują aktorzy. Podobny rodzaj optymalizacji, czyli „wartość odmienną lub nieistniejącą”, można zastosować w określonych rodzajach zapytań DISTINCT, NOT EXISTS() oraz LEFT JOIN. Propagowanie równości Baza danych MySQL rozpoznaje, kiedy zapytanie zawiera dwie kolumny, które są jedna- kowe — np. w warunku JOIN — i propaguje użycie klauzuli WHERE na takich kolumnach. Warto spojrzeć na poniższe przykładowe zapytanie: mysql SELECT film.film_id - FROM sakila.film - INNER JOIN sakila.film_actor USING(film_id) - WHERE film.film_id 500; Serwer MySQL przyjmuje, że klauzula WHERE ma zastosowanie nie tylko względem tabeli film, ale również tabeli film_actor, ponieważ użycie klauzuli USING wymusiło dopa- sowanie tych dwóch kolumn. Jeżeli byłby zastosowany inny serwer bazy danych, niewykonujący takiej czynności, pro- gramista mógłby zostać zachęcony do „udzielenia pomocy optymalizatorowi” poprzez ręczne podanie klauzuli WHERE dla obu tabel, np. w taki sposób: ... WHERE film.film_id 500 AND film_actor.film_id 500 W bazie danych MySQL jest to niepotrzebne. Taka modyfikacja powoduje, że zapytania stają się trudniejsze w obsłudze. Porównania list IN() W wielu serwerach baz danych IN() to po prostu synonim wielu klauzul OR, ponieważ pod względem logicznym obie konstrukcje są odpowiednikami. Nie dotyczy to bazy danych MySQL, która sortuje wartości w liście IN() oraz stosuje szybkie wyszukiwanie binarne w celu określenia, czy dana wartość znajduje się na liście. Jest to O(log n) w wielkości listy, podczas gdy odpowiednik serii klauzul OR to O(n) w wielkości listy (np. wyszukiwanie przeprowadzane jest znacznie wolniej w przypadku ogromnych list). 7 Autorzy zgadzają się, że film bez aktorów jest czymś dziwnym. Jednak przykładowa baza danych Sakila „twierdzi”, że w filmie Slacker Liaisons nie występują aktorzy. W opisie filmu można przeczytać „Dynamiczna opowieść o rekinie i studencie, który musi spotkać krokodyla w starożytnych Chinach”. 188 | Rozdział 4. Optymalizacja wydajności zapytań Przedstawiona powyżej lista jest żałośnie niekompletna, ponieważ MySQL może przeprowadzić znacznie więcej rodzajów optymalizacji, niż zmieściłoby się w całym rozdziale. Lista powin- na jednak pokazać stopień złożoności optymalizatora oraz trafność podejmowanych przez niego decyzji. Jeżeli czytelnik miałby zapamiętać tylko jedno z przedstawionej analizy, po- winno to być zdanie: Nie warto próbować być sprytniejszym od optymalizatora. Taka próba może po prostu zakończyć się klęską bądź znacznym zwiększeniem stopniem skomplikowania zapytań, które nie przyniesie żadnych korzyści, a same zapytania staną się trudniejsze w ob- słudze. Ogólnie rzecz biorąc, zadanie optymalizacji lepiej pozostawić optymalizatorowi. Oczywiście, nadal istnieją sytuacje, w których optymalizator nie zapewni najlepszych wyników. Czasami programista ma wiedzę na temat danych, której nie ma optymalizator, np. wie, że gwarancja ich poprawności wynika z logiki aplikacji. Ponadto czasami optymalizator po prostu nie ma niezbędnej funkcjonalności, np. indeksów typu hash. Z kolei w innych przypadkach, jak już wspomniano, wskutek oszacowanych przez niego kosztów preferowany będzie plan wykonywania, który okaże się kosztowniejszy niż inne możliwości. Jeżeli programista jest przekonany, że optymalizator nie wykonuje dobrze swojego zadania, i wie dlaczego, może spróbować mu pomóc. Niektóre dostępne możliwości obejmują dodanie wskazówki do zapytania, ponowne napisanie zapytania, przeprojekt
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Wysoko wydajne MySQL. Optymalizacja, archiwizacja, replikacja. Wydanie II
Autor:
, , , , ,

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


Prowadzisz stronę lub blog? Wstaw link do fragmentu tej książki i współpracuj z Cyfroteką: