plik

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

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

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

Wybór optymalnego rodzaju danych

100

Podstawy indeksowania

115

Strategie indeksowania w celu osiñgniöcia maksymalnej wydajnoĈci

125

Studium przypadku z zakresu indeksowania

150

Obsäuga indeksu oraz tabeli

155

Uwagi dotyczñce silników magazynowania danych

168

Spis tre

ļci

4. Optymalizacja wydajno

ļci zapytaħ ...........................................................................171

Podstawy powolnych zapytaþ: optymalizacja dostöpu do danych

171

Sposoby restrukturyzacji zapytaþ

176

Podstawy wykonywania zapytaþ

179

Ograniczenia optymalizatora zapytaþ MySQL

198

Optymalizacja okreĈlonego rodzaju zapytaþ

207

Zmienne zdefiniowane przez uĔytkownika

217

5. Zaawansowane funkcje MySQL ................................................................................223

Bufor zapytaþ MySQL

223

Przechowywanie kodu wewnñtrz MySQL

236

Funkcje zdefiniowane przez uĔytkownika

248

System kodowania znaków i kolejnoĈè sortowania

255

Ograniczenia klucza zewnötrznego

270

Tabele Merge i partycjonowane

271

Transakcje rozproszone (XA)

280

6. Optymalizacja konfiguracji serwera .........................................................................283

Podstawy konfiguracji

284

Skäadnia, zasiög oraz dynamizm

285

Ogólne dostrajanie

289

Dostrajanie zachowania operacji I/O w MySQL

299

Dostosowanie wspóäbieĔnoĈci MySQL

314

7. Optymalizacja systemu operacyjnego i osprz

ýtu ....................................................325

Co ogranicza wydajnoĈè MySQL?

326

W jaki sposób wybraè procesor dla MySQL?

326

Wybór osprzötu komputerowego dla serwera podlegäego

337

Optymalizacja wydajnoĈci macierzy RAID

338

Urzñdzenia Storage Area Network oraz Network Attached Storage

345

UĔywanie woluminów skäadajñcych siö z wielu dysków

347

Stan systemu operacyjnego

356

8. Replikacja ...................................................................................................................363

Ogólny opis replikacji

363

Konfiguracja replikacji

367

Szczegóäy kryjñce siö za replikacjñ

375

Topologie replikacji

382

Replikacja i planowanie pojemnoĈci

397

Administracja replikacjñ i jej obsäuga

399

Problemy zwiñzane z replikacjñ i sposoby ich rozwiñzywania

409

Jak szybka jest replikacja?

428

Spis tre

ļci

9. Skalowalno

ļë i wysoka dostýpnoļë .......................................................................... 431

Terminologia

432

SkalowalnoĈè MySQL

434

Wysoka dostöpnoĈè

469

10. Optymalizacja na poziomie aplikacji ........................................................................479

Ogólny opis wydajnoĈci aplikacji

479

Kwestie zwiñzane z serwerem WWW

482

11. Kopia zapasowa i odzyskiwanie ...............................................................................495

Ogólny opis

496

Wady i zalety rozwiñzania

500

Zarzñdzanie kopiñ zapasowñ binarnych dzienników zdarzeþ i jej tworzenie

510

Tworzenie kopii zapasowej danych

512

Odzyskiwanie z kopii zapasowej

523

SzybkoĈè tworzenia kopii zapasowej i odzyskiwania

535

Narzödzia säuĔñce do obsäugi kopii zapasowej

536

Kopie zapasowe za pomocñ skryptów

543

12. Bezpiecze

ħstwo .........................................................................................................547

Terminologia

547

Podstawy dotyczñce kont

548

Bezpieczeþstwo systemu operacyjnego

566

Bezpieczeþstwo sieciowe

567

Szyfrowanie danych

575

MySQL w Ĉrodowisku chroot

579

13. Stan serwera MySQL ................................................................................................. 581

Zmienne systemowe

581

SHOW STATUS

582

SHOW INNODB STATUS

589

SHOW PROCESSLIST

602

SHOW MUTEX STATUS

603

Stan replikacji

604

INFORMATION_SCHEMA

605

14. Narz

ýdzia zapewniajéce wysoké wydajnoļë ...........................................................607

Narzödzia interfejsu

607

Narzödzia monitorowania

609

Narzödzia analizy

619

Narzödzia MySQL

622

đródäa dalszych informacji

625

171

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.

172

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

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.

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
sieci

, 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.

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.

Podstawy powolnych zapyta

ħ: optymalizacja dostýpu do danych

_ 173

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).

174

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

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 = 1\G
*************************** 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:

Podstawy powolnych zapyta

ħ: optymalizacja dostýpu do danych

_ 175

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 = 1\G
*************************** 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()

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.

Wiöcej informacji na ten temat przedstawiono w podrozdziale „Optymalizacja zapytaþ COUNT()”, znajdujñcym

siö w dalszej czöĈci rozdziaäu.

176

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

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

Sposoby restrukturyzacji zapyta

177

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 serwer

(silniki magazynu danych obsäugujñce transakcje mogñ

osiñgnñè lepszñ wydajnoĈè podczas wykonywania mniejszych zapytaþ). Dobrym rozwiñzaniem

Narzödzie mk-archiver z pakietu Maatkit bardzo äatwo wykonuje takie zadania.

178

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

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

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).

Podstawy wykonywania zapyta

_ 179

Podsumowanie. Kiedy przeprowadzanie z

ĥéczeħ w aplikacji

Ŝ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.

Klient wysyäa polecenie SQL do serwera.

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.

Serwer analizuje, przetwarza i optymalizuje SQL na postaè planu wykonania zapytania.

Silnik wykonywania zapytaþ realizuje plan poprzez wykonanie wywoäaþ do API silnika
magazynu danych.

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.

180

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

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.

Podstawy wykonywania zapyta

181

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 zapytania

. 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ñ

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.

}
?>

JeĔeli zapytanie bödzie zbyt duĔe, serwer odmówi przyjöcia kolejnych danych i nastñpi wygenerowanie bäödu.

Rozwiñzaniem problemu moĔe byè opcja

SQL_BUFFER_RESULT

, która zostanie przedstawiona w dalszej czöĈci

rozdziaäu.

182

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

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.

Podstawy wykonywania zapyta

_ 183

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.

184

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

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.

Podstawy wykonywania zapyta

_ 185

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
wykonywania

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

186

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

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

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:

Podstawy wykonywania zapyta

_ 187

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

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 |
+----+...+-----------------------------------------------------+

188

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

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ów

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

, 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

to O(n) w wielkoĈci listy (np. wyszukiwanie

przeprowadzane jest znacznie wolniej w przypadku ogromnych list).

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”.

Podstawy wykonywania zapyta

_ 189

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, przeprojektowanie schematu lub
dodanie indeksów.

Dane statystyczne dotycz

éce tabeli i indeksu

Warto przypomnieè sobie róĔne warstwy w architekturze serwera MySQL, które zostaäy po-
kazane na rysunku 1.1. Warstwa serwera zawierajñca optymalizator zapytaþ nie przechowuje
danych statystycznych dotyczñcych danych i indeksów. To jest zadanie dla silników magazynu
danych, poniewaĔ kaĔdy silnik moĔe przechowywaè róĔne dane statystyczne (lub obsäugi-
waè je w odmienny sposób). Niektóre silniki, np. Archive, w ogóle nie przechowujñ danych
statystycznych!

PoniewaĔ  serwer  nie  przechowuje  danych  statystycznych,  optymalizator  zapytaþ  MySQL
musi uzyskaè od silnika dane statystyczne dotyczñce tabel, które znajdujñ siö w zapytaniu.
Silnik  moĔe  dostarczyè  optymalizatorowi  dane  statystyczne,  takie  jak  liczba  stron  w  tabeli
lub indeksie, liczebnoĈè tabel i indeksów, däugoĈè rekordów i kluczy oraz  informacje  o  roz-
proszeniu klucza. Otrzymane dane statystyczne optymizator moĔe wykorzystaè podczas wyboru
najlepszego planu wykonania zapytania. W kolejnych podrozdziaäach pokazano, jak dane te
wpäywajñ na decyzje podejmowane przez optymalizator.

Strategia MySQL w trakcie wykonywania z

ĥéczeħ

Baza danych MySQL uĔywa pojöcia „zäñczenie” w znacznie szerszym kontekĈcie, niĔ moĔna
siö spodziewaè. Ogólnie rzecz ujmujñc, baza traktuje jak zäñczenie kaĔde zapytanie — nie tylko
zapytanie dopasowujñce rekordy z dwóch tabel, ale wszystkie zapytania (äñcznie z podzapy-
taniami, a nawet zapytaniami

SELECT

wzglödem pojedynczej tabeli). W konsekwencji bardzo

waĔne jest, aby dokäadnie zrozumieè, w jaki sposób serwer MySQL wykonuje zäñczenia.

Warto rozwaĔyè przykäad zapytania

UNION

. Serwer MySQL wykonuje klauzulö

UNION

jako

seriö zapytaþ, których wyniki sñ umieszczane w tabeli tymczasowej, a nastöpnie ponownie
z niej odczytywane. Wedäug MySQL kaĔde poszczególne zapytanie jest zäñczeniem — po-
dobnie jak akt odczytania ich z wynikowej tabeli tymczasowej.

190

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

Na tym etapie strategia wykonywania zäñczeþ przez MySQL jest prosta: kaĔde zäñczenie jest
traktowane jak zagnieĔdĔona pötla zäñczenia. Oznacza to, Ĕe baza danych MySQL wykonuje
pötlö w celu wyszukania rekordu w tabeli, a nastöpnie wykonuje zagnieĔdĔonñ tabelö, szu-
kajñc dopasowania rekordu w kolejnej tabeli. Proces jest kontynuowany aĔ do chwili znale-
zienia dopasowania rekordu w kaĔdej tabeli zäñczenia. Nastöpnym krokiem jest zbudowanie
i zwrócenie rekordu z kolumn wymienionych na liĈcie polecenia

SELECT

. Baza próbuje zbu-

dowaè kolejny rekord poprzez znalezienie nastöpnych dopasowanych rekordów w ostatniej
tabeli. JeĔeli Ĕaden nie zostanie znaleziony, wówczas baza wraca tñ samñ drogñ do poprzed-
niej tabeli, szukajñc w niej kolejnych rekordów. Powrót trwa aĔ do chwili znalezienia do-
pasowanego rekordu w dowolnej tabeli. Wówczas nastöpuje wyszukiwanie dopasowania
w kolejnej tabeli itd.

Proces wyszukiwania rekordów, sprawdzania kolejnej tabeli, a nastöpnie powrotu moĔe zostaè
zapisany w postaci zagnieĔdĔonych pötli w planie wykonywania — stñd nazwa „zäñczenia
zagnieĔdĔonych pötli”. Warto spojrzeè na poniĔsze proste zapytanie:

mysql> SELECT tbl1.col1, tbl2.col2
-> FROM tbl1 INNER JOIN tbl2 USING(col3)
-> WHERE tbl1.col1 IN(5,6);

Przy zaäoĔeniu, Ĕe baza danych MySQL zadecyduje o zäñczeniu tabel w kolejnoĈci przedsta-
wionej w zapytaniu, w poniĔszym pseudokodzie pokazano, jak baza danych MySQL mogäaby
wykonaè to zapytanie:

outer_iter = iterator over tbl1 where col1 IN(5,6)
outer_row  = outer_iter.next
while outer_row
   inner_iter = iterator over tbl2 where col3 = outer_row.col3
   inner_row  = inner_iter.next
   while inner_row
      output [outer_row.col1, inner_row.col2]
      inner_row = inner_iter.next
   end
   outer_row = outer_iter.next
end

PowyĔszy plan wykonania ma zastosowanie zarówno do prostego zapytania pojedynczej tabeli,
jak i zapytania obejmujñcego wiele tabel. Dlatego teĔ nawet zapytania do pojedynczej tabeli
mogñ byè uznawane za zäñczenia — zäñczenia w pojedynczych tabelach sñ prostymi opera-
cjami, które skäadajñ siö na bardziej zäoĔone zäñczenia. Obsäugiwane sñ równieĔ klauzule

OUTER JOIN

. Przykäadowo zapytanie moĔna zmieniè na nastöpujñcñ postaè:

mysql> SELECT tbl1.col1, tbl2.col2
-> FROM tbl1 LEFT OUTER JOIN tbl2 USING(col3)
-> WHERE tbl1.col1 IN(5,6);

PoniĔej znajduje siö odpowiadajñcy mu pseudokod, w którym zmienione fragmenty zostaäy
pogrubione:

Jak to zostaäo pokazane w dalszej czöĈci rozdziaäu, wykonywanie zapytania MySQL nie jest takie proste. Istnieje

wiele róĔnych optymalizacji komplikujñcych ten proces.

Podstawy wykonywania zapyta

191

      while inner_row
         output [outer_row.col1, inner_row.col2]
         inner_row = inner_iter.next
      end
   else
      output [outer_row.col1, NULL]
   end
   outer_row = outer_iter.next
end

Innym sposobem wizualizacji planu wykonania zapytania jest uĔycie tego, co osoby zajmujñ-
ce siö optymalizacjñ nazywajñ „wykres swim-lane”. Na rysunku 4.2 pokazano wykres swim-
lane dotyczñcy poczñtkowego zapytania

INNER JOIN

. Wykres odczytuje siö od lewej do prawej

strony, od góry do doäu.

Rysunek 4.2. Wykres swim-lane pokazuj

ñcy pobieranie rekordów za pomocñ zäñczenia

W zasadzie serwer MySQL wykonuje kaĔdy rodzaj zapytania w taki sam sposób. Przykäa-
dowo podzapytania w klauzuli

FROM

sñ wykonywane w pierwszej kolejnoĈci, a ich wyniki

zostajñ umieszczone w tabeli tymczasowej

. Nastöpnie tabela tymczasowa jest traktowana jak

zwykäa tabela (stñd nazwa „tabela pochodna”). W zapytaniach

UNION

baza danych MySQL

takĔe stosuje tabele tymczasowe, a wszystkie zapytania

RIGHT OUTER JOIN

sñ przepisywane

na ich odpowiedniki

LEFT OUTER JOIN

. W skrócie mówiñc, MySQL zmusza kaĔdy rodzaj za-

pytania do „wpasowania siö” w przedstawiony plan wykonywania.

Jednak niemoĔliwe jest wykonanie w ten sposób kaĔdego poprawnego zapytania SQL. Przy-
käadowo zapytanie

FULL OUTER JOIN

nie moĔe byè wykonane za pomocñ zagnieĔdĔonych

pötli oraz powracania po dotarciu do tabeli, w której nie znaleziono dopasowanych rekordów,
poniewaĔ zapytanie moĔe rozpoczynaè siö od tabeli nieposiadajñcej pasujñcych rekordów.
To wyjaĈnia, dlaczego MySQL nie obsäuguje zapytaþ

FULL OUTER JOIN

. Nadal wszystkie po-

zostaäe zapytania mogñ byè wykonywane za pomocñ zagnieĔdĔonych pötli, ale wynik takich
operacji jest opäakany. Wiöcej informacji na ten temat znajduje siö w dalszej czöĈci rozdziaäu.

W tabeli tymczasowej nie ma indeksów i naleĔy o tym pamiötaè podczas tworzenia skomplikowanych zäñczeþ

wzglödem podzapytaþ w klauzuli

FROM

. Dotyczy to równieĔ zapytaþ

UNION

192

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

Plan wykonywania

Baza danych MySQL nie generuje kodu bajtowego w celu wykonania zapytania, co ma miejsce
w wielu innych bazach danych. Plan wykonania zapytania w rzeczywistoĈci jest drzewem
instrukcji, które silnik wykonywania zapytania realizuje w celu otrzymania wyniku zapytania.
Plan ostateczny zawiera iloĈè informacji wystarczajñcñ do zrekonstruowania poczñtkowego
zapytania. JeĔeli zapytanie jest wykonywane z uĔyciem polecenia

EXPLAIN EXTENDED

poprze-

dzonego przez

SHOW WARNINGS

, wówczas moĔna zobaczyè zrekonstruowane zapytanie

KaĔde zapytanie obejmujñce wiöcej niĔ jednñ tabelö moĔe zostaè przedstawione jako drzewo.
Przykäadowo zapytanie obejmujñce operacjö zäñczenia czterech tabel moĔna wykonaè tak, jak
pokazano na rysunku 4.3.

Rysunek 4.3. Jeden ze sposobów przeprowadzenia operacji z

äñczenia na wielu tabelach

Naukowcy nazywajñ je drzewem zrównowaĔonym. Jednak nie jest to sposób, w jaki MySQL
wykonuje zapytanie. Jak wspomniano w poprzednim podpunkcie, baza danych MySQL zawsze
rozpoczyna wykonywanie zapytania od jednej tabeli i wyszukuje pasujñce rekordy w kolejnej.
Dlatego teĔ plan wykonywania zapytania w MySQL zawsze przybiera postaè drzewa lewo-
stronnie zagnie

ĔdĔonego, co pokazano na rysunku 4.4.

Rysunek 4.4. Sposób przeprowadzania przez MySQL z

äñczeþ obejmujñcych wiele tabel

Serwer generuje dane wyjĈciowe na podstawie planu wykonania zapytania. Dlatego teĔ znajduje siö w nich
taka sama semantyka jak w zapytaniu poczñtkowym, ale niekoniecznie ten sam tekst.

Podstawy wykonywania zapyta

_ 193

Optymalizator z

ĥéczeħ

NajwaĔniejszñ czöĈciñ optymalizatora zapytaþ MySQL jest optymalizator zäñczeþ, który decy-
duje o najlepszej kolejnoĈci wykonywania zapytaþ obejmujñcych wiele tabel. Zazwyczaj ope-
racje zäñczeþ tabel moĔna przeprowadziè w odmiennej kolejnoĈci, wciñĔ otrzymujñc te same
wyniki. Optymalizator zäñczeþ oszacowuje koszt róĔnych planów, a nastöpnie stara siö wybraè
najtaþszy i dajñcy te same wyniki.

PoniĔej przedstawiono zapytanie, którego tabele mogñ zostaè zäñczone w róĔnej kolejnoĈci
bez zmiany otrzymanych wyników:

mysql> SELECT film.film_id, film.title, film.release_year, actor.actor_id,
    ->    actor.first_name, actor.last_name
    ->    FROM sakila.film
    ->    INNER JOIN sakila.film_actor USING(film_id)
    ->    INNER JOIN sakila.actor USING(actor_id);

Czytelnik prawdopodobnie myĈli o kilku róĔnych planach wykonania zapytania. Przykäa-
dowo baza danych MySQL mogäaby rozpoczñè od tabeli

film

, uĔyè indeksu obejmujñcego

kolumny

film_id

film_actor

w celu wyszukania wartoĈci

actor_id

, a nastöpnie znaleĒè re-

kordy w kluczu podstawowym tabeli

actor

. Takie rozwiñzanie byäoby efektywne, nieprawdaĔ?

Warto wiöc uĔyè polecenia

EXPLAIN

i przekonaè siö, w jaki sposób baza danych MySQL wy-

konuje to zapytanie:

*************************** Rekord 1. ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
        Extra:
*************************** Rekord 2. ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
         type: ref
possible_keys: PRIMARY,idx_fk_film_id
          key: PRIMARY
      key_len: 2
          ref: sakila.actor.actor_id
         rows: 1
        Extra: Using index
*************************** Rekord 3. ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: sakila.film_actor.film_id
         rows: 1
        Extra:

194

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

Jak widaè, to nieco odmienny plan od zasugerowanego w poprzednim akapicie. Baza danych
MySQL rozpoczyna od tabeli

actor

(wiemy o tym, poniewaĔ zostaäa wyĈwietlona w pierw-

szej grupie danych wyjĈciowych polecenia

EXPLAIN

), a nastöpnie porusza siö w odwrotnej

kolejnoĈci. Czy takie rozwiñzanie naprawdö jest efektywne? Warto to sprawdziè. Säowo klu-
czowe

STRAIGHT_JOIN

wymusza przeprowadzanie operacji zäñczeþ w kolejnoĈci okreĈlonej

przez zapytanie. PoniĔej przedstawiono dane wyjĈciowe polecenia

EXPLAIN

dla zmodyfiko-

wanego zapytania:

mysql> EXPLAIN SELECT STRAIGHT_JOIN film.film_id...\G
*************************** Rekord 1. ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 951
        Extra:
*************************** Rekord 2. ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
         type: ref
possible_keys: PRIMARY,idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: sakila.film.film_id
         rows: 1
        Extra: Using index
*************************** Rekord 3. ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: sakila.film_actor.actor_id
         rows: 1
        Extra:

To pokazuje, dlaczego MySQL stosuje odwrotnñ kolejnoĈè zäñczeþ: dziöki temu serwer musi
przeanalizowaè mniejszñ iloĈè rekordów w pierwszej tabeli

. W obu przypadkach moĔliwe

jest przeprowadzenie szybkich wyszukiwaþ indeksu w tabelach drugiej i trzeciej. RóĔnica
dotyczy liczby wymienionych wyszukiwaþ indeksu, które muszñ zostaè wykonane.

UĔycie tabeli

film

jako pierwszej wymaga 951 prób w tabelach

film_actor

actor

, po

jednej dla kaĔdego rekordu w pierwszej tabeli.

JeĔeli serwer jako pierwszñ skanuje tabelö

actor

, w kolejnych tabelach bödzie musiaä

wykonaè tylko okoäo dwustu wyszukiwaþ indeksu.

A mówiñc dokäadniej, baza danych MySQL nie próbuje zredukowaè liczby odczytywanych rekordów. Zamiast
tego próbuje przeprowadziè optymalizacjö pozwalajñcñ na odczyt mniejszej liczby stron. Liczba rekordów
czösto moĔe w przybliĔeniu podaè koszt zapytania.

Podstawy wykonywania zapyta

_ 195

Innymi säowy, odwrotna kolejnoĈè wykonywania zäñczeþ bödzie wymagaäa mniejszej liczby
operacji powrotów oraz ponownego odczytu. Aby dwukrotnie sprawdziè wybór optymalizatora,
autorzy wykonali dwie wersje zapytania i sprawdzili wartoĈè zmiennej

Last_query_cost

dla

kaĔdego z nich. Oszacowany koszt zmodyfikowanego zapytania wyniósä 241, podczas gdy
oszacowany koszt zapytania wymuszajñcego zachowanie kolejnoĈci zäñczeþ wyniósä 1154.

To prosty przykäad na to, jak optymalizator zäñczeþ w MySQL moĔe zmieniè kolejnoĈè operacji
w zapytaniu, aby jego wykonanie staäo siö taþsze. Zmiana kolejnoĈci operacji zäñczeþ zwykle
jest bardzo efektywnñ formñ optymalizacji. Zdarzajñ siö sytuacje, w których optymalizator
nie wybiera optymalnego planu, ale wówczas moĔna uĔyè säowa kluczowego

STRAIGHT_JOIN

oraz napisaè zapytanie w kolejnoĈci uznawanej przez programistö za najlepszñ. Takie sytu-
acje jednak wystöpujñ bardzo rzadko. W wiökszoĈci przypadków optymalizator zäñczeþ wy-
grywa z czäowiekiem.

Optymalizator zäñczeþ próbuje zbudowaè drzewo planu wykonania zapytania o najniĔszym
moĔliwym do zaakceptowania koszcie. Kiedy bödzie to moĔliwe, analizuje wszystkie poten-
cjalne kombinacje poddrzew, a rozpoczyna od wszystkich planów obejmujñcych jednñ tabelö.

Niestety, operacja zäñczeþ n tabel bödzie miaäa n! (silnia) kombinacji kolejnoĈci zäñczeþ do
przeanalizowania. Wspóäczynnik ten nosi nazwö przestrzeni przeszukiwana dla wszystkich
moĔliwych planów zapytania i zwiöksza siö bardzo szybko. Zäñczenie dziesiöciu tabel moĔe
byè przeprowadzone na maksymalnie 3628800 róĔnych sposobów! Kiedy przestrzeþ prze-
szukiwania za bardzo siö rozrasta, wtedy optymalizacja zapytania moĔe wymagaè zbyt duĔej
iloĈci czasu. Serwer zatrzymuje wiöc wykonywanie peänej analizy. W zamian stosuje skróty,
np. szukanie „zachäanne”, kiedy liczba tabel przekroczy wartoĈè granicznñ ustalonñ przez
zmiennñ

optimizer_search_depth

Baza danych MySQL dysponuje zgromadzonymi w czasie lat badaþ i eksperymentów wieloma
algorytmami heurystycznymi, które sñ uĔywane do przyĈpieszenia dziaäania fazy optymalizacji.
Wprawdzie jest to korzystne, ale równoczeĈnie oznacza, Ĕe serwer MySQL moĔe (sporadycznie)
pominñè plan optymalny i wybraè nieco mniej optymalny, poniewaĔ nie bödzie próbowaä
przeanalizowaè kaĔdego moĔliwego do wykonania planu.

Zdarzajñ siö sytuacje, gdy zmiana kolejnoĈci w zapytaniu jest niemoĔliwa. Optymalizator
zäñczeþ moĔe wykorzystaè ten fakt do zmniejszenia przestrzeni przeszukiwania poprzez eli-
minacjö okreĈlonych rozwiñzaþ. Klauzula

LEFT JOIN

jest dobrym przykäadem, poniewaĔ skäa-

da siö ze skorelowanych podzapytaþ (wiöcej informacji na ten temat znajduje siö w dalszej
czöĈci rozdziaäu). WiñĔe siö to z faktem, Ĕe wyniki jednej tabeli zaleĔñ od danych otrzymanych
z innej. Tego rodzaju zaleĔnoĈci pomagajñ optymalizatorowi zäñczeþ w redukcji przeszukiwanej
przestrzeni poprzez eliminacjö pewnego rodzaju rozwiñzaþ.

Optymalizacja sortowania

Sortowanie wyników moĔe byè kosztownñ operacjñ, stñd czösto moĔna poprawiè wydajnoĈè
poprzez unikanie sortowania bñdĒ sortowanie mniejszej liczby rekordów.

Wykorzystanie indeksów podczas sortowania zostaäo przedstawione w rozdziale 3. Kiedy
baza danych MySQL nie moĔe uĔyè indeksu w celu zbudowania posortowanego wyniku,
wówczas samodzielnie musi posortowaè rekordy. Operacjö moĔna przeprowadziè w pamiöci
lub na dysku twardym, ale proces zawsze nosi nazwö sortowania pliku, nawet jeĈli w rzeczy-
wistoĈci nie uĔywa pliku.

196

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

JeĔeli wartoĈci przeznaczone do sortowania mieszczñ siö w buforze sortowania, MySQL
moĔe przeprowadziè sortowanie caäkowicie w pamiöci za pomocñ szybkiego sortowania. JeĈli
MySQL nie moĔe wykonaè sortowania w pamiöci, wtedy operacja jest przeprowadzana na
dysku poprzez sortowanie wartoĈci fragmentami. Proces wykorzystuje szybkie sortowanie
w celu posortowania kaĔdego fragmentu, a kaĔdy posortowany fragment jest doäñczany
do wyniku.

Istniejñ dwa algorytmy sortowania pliku.

Dwuprzebiegowy (stary)

Odczytuje wskaĒniki rekordów oraz kolumny

ORDER BY

, sortuje je, a nastöpnie skanuje

posortowanñ listö i ponownie odczytuje rekordy w celu utworzenia danych wyjĈciowych.
Algorytm dwuprzebiegowy moĔe byè bardzo kosztowny, poniewaĔ rekordy sñ odczy-
tywane z tabeli dwukrotnie, a drugi odczyt powoduje wykonanie duĔej iloĈci losowych
operacji I/O. Proces jest szczególnie kosztowny w silniku MyISAM, który do pobrania
kaĔdego rekordu uĔywa wywoäaþ systemowych (poniewaĔ MyISAM polega na buforze
systemu operacyjnego przechowujñcego dane). Jednak w trakcie sortowania przechowy-
wana jest minimalna iloĈè danych. Tak wiöc rekordy sñ sortowane caäkowicie w pamiöci,
a przechowywanie mniejszej iloĈci danych i ponowny odczyt rekordów w celu wygene-
rowania wyniku koþcowego moĔe okazaè siö taþszym rozwiñzaniem.

Jednoprzebiegowy (nowy)

Odczytuje wszystkie kolumny wymagane przez zapytanie, sortuje je wedäug kolumn

ORDER BY

, a nastöpnie skanuje posortowanñ listö i wyĈwietla dane wyjĈciowe wskaza-

nych kolumn.

Ten  algorytm  sortowania  jest  dostöpny  jedynie  w  MySQL  4.1  i  nowszych.  MoĔe  byè
znacznie  efektywniejszy  zwäaszcza  dla  ogromnych  zbiorów  danych  opierajñcych  siö  na
operacjach  I/O,  poniewaĔ  unika  dwukrotnego  odczytywania  rekordów  z  tabel  i  zastö-
puje losowe operacje I/O bardziej ciñgäymi operacjami I/O. Jednak  potencjalnie moĔe
uĔywaè duĔej iloĈci miejsca, poniewaĔ przechowuje wszystkie poĔñdane kolumny z kaĔ-
dego rekordu, a nie tylko kolumny wymagane do  posortowania  rekordów.  Oznacza  to,
Ĕe mniejsza iloĈè zbiorów elementów zmieĈci siö w buforze sortowania, a samo sortowanie
pliku bödzie musiaäo wykonaè wiöcej operacji äñczenia wyników sortowania.

MySQL moĔe na potrzeby sortowania pliku wykorzystywaè znacznie wiöcej przestrzeni
tymczasowej, niĔ moĔna przypuszczaè, bo przy sortowaniu kaĔdego zbioru elementów alo-
kuje rekord o staäej däugoĈci. Te rekordy sñ na tyle olbrzymie, aby pomieĈciè najwiökszy
moĔliwy zbiór danych, äñcznie z kaĔdñ kolumnñ

VARCHAR

o peänej däugoĈci. Ponadto podczas

stosowania kodowania UTF-8 serwer MySQL alokuje trzy bajty dla kaĔdego znaku. Autorzy
spotkali siö z kiepsko zoptymalizowanymi schematami, które powodowaäy, Ĕe przestrzeþ
tymczasowa uĔywana podczas sortowania byäa wielokrotnie wiöksza niĔ wielkoĈè caäej tabeli
na dysku twardym.

Podczas sortowania zäñczeþ baza danych MySQL moĔe w trakcie wykonywania zapytania
przeprowadziè sortowanie pliku na dwóch etapach. JeĔeli klauzula

ORDER BY

odnosi siö je-

dynie do kolumn w pierwszej tabeli zäñczenia, MySQL moĔe przeprowadziè sortowanie tej
tabeli, a nastöpnie wykonaè operacjö zäñczenia. W takim przypadku dane wyjĈciowe polecenia

EXPLAIN

zawierajñ w kolumnie

Extra

ciñg tekstowy „Using filesort”. W przeciwnym razie

baza danych MySQL musi przechowywaè wynik zapytania w tabeli tymczasowej, a nastöpnie

Podstawy wykonywania zapyta

_ 197

przeprowadziè na niej sortowanie pliku po zakoþczeniu operacji zäñczenia. W takim przy-
padku dane wyjĈciowe polecenia

EXPLAIN

zawierajñ w kolumnie

Extra

ciñg tekstowy „Using

temporary; Using filesort”. JeĔeli w zapytaniu znajduje siö klauzula

LIMIT

, bödzie zastoso-

wana po operacji sortowania pliku, a wiöc tabela tymczasowa moĔe byè ogromna, natomiast
operacja sortowania pliku bardzo kosztowna.

Wiöcej informacji na temat dostrajania serwera pod kñtem sortowania pliku oraz wpäywu
uĔywanego przez serwer algorytmu przedstawiono w rozdziale 6., w podrozdziale „Opty-
malizacja sortowania pliku”.

Silnik wykonywania zapyta

W wyniku etapu analizy i optymalizacji powstaje plan wykonania zapytania, który silnik
wykonywania zapytaþ MySQL stosuje w celu przetworzenia zapytania. Plan jest strukturñ
danych, a nie wykonywalnym kodem bajtowym, jak ma to miejsce w wielu innych bazach
danych.

Faza  wykonania  zapytania,  w  przeciwieþstwie  do  fazy  optymalizacji,  zwykle  nie  jest  tak
skomplikowana: MySQL po prostu podñĔa za instrukcjami znajdujñcymi siö w planie wyko-
nania  zapytania.  WiökszoĈè  operacji  w  planie  polega  na  wywoäaniu  metod  zaimplemento-
wanych  przez  interfejs  silnika  magazynu  danych,  znany  równieĔ  pod  nazwñ  API  procedury
obs

äugi. KaĔda tabela w zapytaniu jest przedstawiana jako egzemplarz procedury obsäugi.

JeĔeli np. tabela wystöpuje w zapytaniu trzykrotnie, serwer utworzy trzy egzemplarze pro-
cedury obsäugi. ChociaĔ nie byäo to eksponowane wczeĈniej, trzeba powiedzieè, Ĕe MySQL
w rzeczywistoĈci tworzy egzemplarze procedury obsäugi wczeĈniej, w fazie optymalizacji.
Optymalizator wykorzystuje je w celu pobrania informacji dotyczñcych tabel, np. nazw kolumn
oraz danych statystycznych indeksu.

Interfejs silnika magazynu danych ma duĔñ liczbö funkcji, ale do wykonania wiökszoĈci zapytaþ
wymaga jedynie okoäo dziesiöciu operacji typu „bloku budulcowego”. Przykäadowo istnieje
operacja säuĔñca do odczytania pierwszego rekordu z indeksu oraz inna operacja do od-
czytania kolejnego rekordu z indeksu. To wystarczy w zapytaniu, które przeprowadza ska-
nowanie indeksu. Taka uproszczona metoda wykonywania zapytania jest moĔliwa dziöki
architekturze silnika magazynu danych MySQL, ale jednoczeĈnie nak

äada pewne ograniczenia

w zakresie optymalizacji, co zostaäo opisane wczeĈniej.

Nie wszystko jest operacjñ procedury obsäugi. Przykäadowo to serwer zarzñdza blo-
kadami tabel. Procedura obsäugi moĔe implementowaè wäasne blokowanie niĔszego
poziomu, tak jak w przypadku InnoDB i jego blokowania na poziomie rekordu, ale
to nie zastöpuje wäasnej implementacji blokowania znajdujñcej siö w serwerze. Jak
wyjaĈniono w rozdziale 1., wszystko, co jest wspóädzielone przez wszystkie silniki
magazynu danych, zostaäo zaimplementowane w serwerze, np. funkcje daty i godziny,
widoki i wyzwalacze.

W celu wykonania zapytania serwer po prostu powtarza instrukcje aĔ do chwili, gdy nie bödzie
kolejnych rekordów do przeanalizowania.

198

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

Zwrot klientowi wyników zapytania

Ostatnim krokiem w trakcie wykonywania zapytania jest zwrot wyników klientowi. Nawet
zapytania niezwracajñce zbioru wynikowego teĔ udzielajñ klientowi odpowiedzi, która za-
wiera informacje o zapytaniu, np. o liczbie rekordów, których dotyczyäo zapytanie.

JeĔeli zapytanie moĔna buforowaè, na tym etapie baza danych MySQL umieĈci jego wyniki
w buforze zapytania.

Serwer generuje i wysyäa wyniki w sposób przyrostowy. Warto w tym miejscu przypomnieè
sobie omówionñ wczeĈniej metodö wielu zäñczeþ. Kiedy baza danych MySQL przetworzy
ostatniñ tabelö i z powodzeniem wygeneruje rekord, moĔe i powinna wysäaè ten rekord
klientowi. Takie rozwiñzanie niesie ze sobñ dwie korzyĈci: umoĔliwia serwerowi unikniöcie
koniecznoĈci przechowywania rekordu w pamiöci oraz oznacza, Ĕe klient bödzie otrzymywaä
wyniki tak szybko, jak to moĔliwe

Ograniczenia optymalizatora zapyta

ħ MySQL

PodejĈcie MySQL, okreĈlane mianem „wszystko jest zäñczeniem zagnieĔdĔonych pötli”, sto-
sowane podczas wykonywania zapytaþ nie jest idealnym rozwiñzaniem optymalizacji kaĔ-
dego rodzaju zapytaþ. Na szczöĈcie, istnieje tylko niewielka liczba sytuacji, w których optyma-
lizator zapytaþ MySQL siö nie sprawdza. Zazwyczaj moĔliwe jest wówczas ponowne napisanie
zapytaþ, aby dziaäaäy znacznie efektywniej.

Informacje przedstawione w tym podrozdziale majñ zastosowanie do wersji serwera
MySQL dostöpnych w trakcie pisania ksiñĔki, czyli do wersji 5.1 MySQL. Niektóre
z omówionych zapytaþ prawdopodobnie zostanñ zäagodzone lub caäkowicie usuniöte
w przyszäych wersjach serwera. CzöĈè zostaäa juĔ poprawiona i umieszczona w wersjach,
które nie sñ jeszcze oficjalnie dostöpne. W kodzie Ēródäowym MySQL 6 wprowadzono
pewnñ liczbö optymalizacji podzapytaþ, a nad wieloma kolejnymi trwajñ prace.

Podzapytania skorelowane

MySQL czasami kiepsko optymalizuje podzapytania. Najtrudniej poddajñ siö optymalizacji
podzapytania

IN()

w klauzuli

WHERE

. Przeanalizujmy przykäadowe zapytanie majñce za za-

danie znalezienie wszystkich filmów w tabeli

sakila.film

bazy danych

Sakila

, w których

wystöpuje aktorka Penelope Guiness (

actor_id=1

). Naturalne wydaje siö utworzenie nastö-

pujñcego podzapytania:

mysql> SELECT * FROM sakila.film
-> WHERE film_id IN(
-> SELECT film_id FROM sakila.film_actor WHERE actor_id = 1);

JeĔeli trzeba, programista moĔe wpäynñè na takie zachowanie bazy danych, stosujñc wskazówkö zwiñzanñ
z

SQL_BUFFER_RESULT

. Wiöcej informacji na ten temat przedstawiono w podrozdziale „Wskazówki dotyczñce

optymalizatora zapytaþ”, znajdujñcym siö w dalszej czöĈci rozdziaäu.

Ograniczenia optymalizatora zapyta

ħ MySQL

_ 199

Kuszñce wydaje siö zaäoĔenie, Ĕe baza danych MySQL wykona powyĔsze zapytanie na odwrót,
poprzez wyszukanie listy wartoĈci

actor_id

i zastosowanie ich w liĈcie

IN()

. WczeĈniej

wspomniano, Ĕe lista

IN()

w zasadzie jest bardzo szybka, a wiöc moĔna spodziewaè siö, Ĕe

zapytanie zostanie zoptymalizowane np. w nastöpujñcy sposób:

-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);

Niestety, mamy do czynienia z sytuacjñ odwrotnñ. MySQL próbuje „pomóc” podzapytaniu
poprzez wepchniöcie do niego korelacji z zewnötrznej tabeli, Ĕeby efektywniej mogäo wyszu-
kiwaè rekordy. Nowo napisane zapytanie jest wiöc nastöpujñce:

SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);

Po powyĔszej modyfikacji podzapytanie wymaga kolumny

film_id

z zewnötrznej tabeli

film

i nie moĔe byè wykonane w pierwszej kolejnoĈci. Dane wyjĈciowe polecenia

EXPLAIN

przedstawiajñ wynik jako

DEPENDENT SUBQUERY

(moĔna wykonaè polecenie

EXPLAIN EXTENDED

w celu dokäadnego zobaczenia, jak zapytanie zostaäo napisane na nowo):

Zgodnie z danymi wyjĈciowymi polecenia

EXPLAIN

, baza danych MySQL przeprowadzi

skanowanie tabeli

film

i wykona podzapytanie wzglödem kaĔdego znalezionego rekordu.

W przypadku maäej tabeli nie przeäoĔy siö to na zbyt duĔe obniĔenie wydajnoĈci, ale jeĈli
zewnötrzna tabela bödzie ogromna, spadek wydajnoĈè bödzie katastrofalny. Na szczöĈcie,
nowe zapytanie moĔna bardzo äatwo napisaè, np. z uĔyciem klauzuli

JOIN

mysql> SELECT film.* FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE actor_id = 1;

Innym dobrym rozwiñzaniem optymalizacyjnym jest röczne wygenerowanie listy

IN()

poprzez

wykonanie podzapytania jako oddzielnego zapytania z funkcjñ

GROUP_CONCAT()

. Czasami

bödzie to szybsze rozwiñzanie niĔ uĔycie klauzuli

JOIN

Baza danych MySQL byäa mocno krytykowana za ten szczególny rodzaj planu wykonywania
podzapytania. ChociaĔ niewñtpliwie wymaga on poprawienia, krytycy czösto mylili dwa
róĔne elementy: kolejnoĈè wykonywania oraz buforowanie. Wykonanie zapytania na odwrót
jest jednñ z form jego optymalizacji, natomiast buforowanie wyniku zapytania to inna forma.
Samodzielne ponowne napisanie zapytania daje kontrolö nad obydwoma aspektami. Przy-
szäe wersje MySQL powinny zapewniaè lepszñ optymalizacjö tego rodzaju zapytaþ, choè nie
jest to zadanie äatwe do wykonania. Wystöpujñ znacznie gorsze przypadki zwiñzane z do-
wolnym planem wykonania zapytania, äñcznie z zastosowaniem odwrotnego planu wykona-
nia zapytania, o którym sñdzi siö, Ĕe bödzie prostszy do optymalizacji.

200 _

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

Kiedy podzapytanie skorelowanie jest dobre?

Baza danych MySQL nie zawsze bäödnie optymalizuje podzapytania skorelowane. JeĔeli
czytelnik usäyszy poradö, aby zawsze ich unikaè, nie naleĔy jej säuchaè! W zamian warto
przeprowadziè testy wydajnoĈci i samodzielnie podjñè decyzjö. W niektórych sytuacjach
podzapytanie skorelowane jest caäkiem rozsñdnym lub nawet optymalnym sposobem otrzy-
mania wyniku. Warto spojrzeè na poniĔszy przykäad:

mysql> EXPLAIN SELECT film_id, language_id FROM sakila.film
    -> WHERE NOT EXISTS(
    ->    SELECT * FROM sakila.film_actor
    ->    WHERE film_actor.film_id = film.film_id
    ->)\G
*************************** Rekord 1. ***************************
           id: 1
  select_type: PRIMARY
        table: film
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 951
        Extra: Using where
*************************** Rekord 2. ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: film_actor
         type: ref
possible_keys: idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: film.film_id
         rows: 2
        Extra: Using where; Using index

Standardowñ poradñ dla takiego zapytania jest zastosowanie w nim klauzuli

LEFT OUTER

JOIN

zamiast uĔycia podzapytania. Teoretycznie, baza danych MySQL powinna zastosowaè

w obu przypadkach taki sam plan wykonania zapytania. Warto to sprawdziè:

mysql> EXPLAIN SELECT film.film_id, film.language_id
    -> FROM sakila.film
    ->    LEFT OUTER JOIN sakila.film_actor USING(film_id)
    -> WHERE film_actor.film_id IS NULL\G
*************************** Rekord 1. ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 951
        Extra:
*************************** Rekord 2. ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
         type: ref

Ograniczenia optymalizatora zapyta

ħ MySQL

_ 201

possible_keys: idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: sakila.film.film_id
         rows: 2
        Extra: Using where; Using index; Not exists

Plany sñ niemal identyczne, ale wystöpujñ miödzy nimi pewne róĔnice.

Zapytanie

SELECT

wzglödem tabeli

film_actor

jest rodzaju

DEPENDENT SUBQUERY

w jed-

nym zapytaniu, natomiast

SIMPLE

— w drugim. RóĔnica po prostu odzwierciedla skäadniö,

poniewaĔ pierwsze zapytanie uĔywa podzapytania, a drugie nie. Pod wzglödem operacji
procedury obsäugi nie ma miödzy nimi zbyt duĔej róĔnicy.

W kolumnie

Extra

drugiego zapytania tabela

film

nie jest opisana ciñgiem tekstowym

„Using where”. To nie ma znaczenia: klauzula

USING

uĔyta w drugim zapytaniu i tak

oznacza to samo, co klauzula

WHERE

W kolumnie

Extra

drugiego zapytania tabela

film_actor

jest opisana ciñgiem tekstowym

„Not exists”. To jest przykäad dziaäania algorytmu wczesnego zakoþczenia zapytania
omówionego we wczeĈniejszej czöĈci rozdziaäu. Oznacza to, Ĕe baza danych MySQL
chciaäa uĔyè nieistniejñcej optymalizacji w celu unikniöcia odczytu wiöcej niĔ jednego rekordu
z indeksu

idx_fk_film

tabeli

film_actor

. Odpowiada to funkcji

NOT EXISTS()

skorelo-

wanego podzapytania, poniewaĔ zatrzymuje przetwarzanie bieĔñcego rekordu tuĔ po
znalezieniu dopasowania.

Tak wiöc teoretycznie MySQL wykona powyĔsze zapytania niemal identycznie. W rzeczywi-
stoĈci przeprowadzenie testów wydajnoĈci to jedyny sposób stwierdzenia, które z wymie-
nionych rozwiñzaþ jest szybsze. Autorzy przeprowadzili testy wydajnoĈci na obu zapyta-
niach, korzystajñc ze standardowych ustawieþ. Wyniki zostaäy przedstawione w tabeli 4.1.

Tabela 4.1. Zapytanie NOT EXISTS kontra LEFT OUTER JOIN

Zapytanie

Wynik w liczbie zapyta

ħ na sekundý (QPS)

podzapytanie

NOT EXISTS

360 QPS

LEFT OUTER JOIN

425 QPS

A zatem podzapytanie jest nieco wolniejsze!

Jednak nie zawsze tak bywa. Zdarzajñ siö sytuacje, gdy podzapytanie moĔe byè szybsze.
Przykäadowo moĔe sprawdzaè siö doskonale, kiedy trzeba bödzie po prostu wskazaè rekordy
z jednej tabeli dopasowane do rekordów w drugiej. ChociaĔ brzmi to jak doskonaäy opis zäñ-
czenia, nie zawsze jest nim. Przedstawione poniĔej zäñczenie zaprojektowane w celu wyszukania
kaĔdego filmu, w którym wystöpuje aktor, zwróci powielone rekordy, poniewaĔ w niektórych
filmach wystöpuje wielu aktorów:

mysql> SELECT film.film_id FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id);

Aby wyeliminowaè powielone rekordy, trzeba uĔyè klauzul

DISTINCT

lub

GROUP BY

mysql> SELECT DISTINCT film.film_id FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id);

202

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

Czy to naprawdö jest próba przyĈpieszenia zapytania i czy wynika z kodu SQL? Operator

EXISTS

wyraĔa logicznñ koncepcjö „ma dopasowanie”, bez tworzenia powielajñcych siö rekor-

dów, oraz unika operacji

GROUP BY

lub

DISTINCT

, które mogäyby wymagaè tabeli tymczasowej.

PoniĔej przedstawiono nowo napisane zapytanie zawierajñce podzapytanie zamiast zäñczenia:

mysql> SELECT film_id FROM sakila.film
-> WHERE EXISTS(SELECT * FROM sakila.film_actor
-> WHERE film.film_id = film_actor.film_id);

TakĔe w tym przypadku autorzy przeprowadzili testy wydajnoĈci sprawdzajñce obie strategie.
Wyniki zostaäy przedstawione w tabeli 4.2.

Tabela 4.2. Zapytanie EXISTS kontra INNER JOIN

Zapytanie

Wynik w liczbie zapyta

ħ na sekundý (QPS)

INNER JOIN

185 QPS

podzapytanie

EXISTS

325 QPS

PowyĔszy przykäad pokazuje, Ĕe podzapytanie zostaäo wykonane szybciej niĔ operacja zäñczenia.

Ten  däugi  przykäad  zostaä  zaprezentowany  w  celu  ilustracji  dwóch  aspektów.  Po  pierwsze,
nie  naleĔy  zwaĔaè  na  kategoryczne  porady  dotyczñce  podzapytaþ.  Po  drugie,  trzeba  prze-
prowadzaè testy wydajnoĈci  majñce  na  celu  potwierdzenie  zaäoĔeþ  dotyczñcych  planów  za-
pytania i szybkoĈci ich wykonywania.

Ograniczenia klauzuli UNION

Baza danych MySQL moĔe czasami „wciñgnñè” do Ĉrodka warunki z zewnötrznej klauzuli

UNION

gdzie bödñ mogäy zostaè uĔyte w celu ograniczenia liczby wyników lub wäñczenia dodatkowych
optymalizacji.

JeĔeli programista sñdzi, Ĕe dowolne z poszczególnych zapytaþ poäñczonych klauzulñ

UNION

moĔe odnieĈè korzyĈci z uĔycia klauzuli

LIMIT

lub stanie siö podmiotem dziaäania klauzuli

ORDER BY

po poäñczeniu z innymi zapytaniami, wówczas klauzulö

LIMIT

trzeba umieĈciè we-

wnñtrz kaĔdej czöĈci konstrukcji

UNION

. JeĔeli np. dwie ogromne tabele sñ powiñzane klauzulñ

UNION

, a wynik zostaje ograniczony do dwudziestu rekordów za pomocñ klauzuli

LIMIT

wtedy baza danych MySQL bödzie przechowywaäa obie ogromne tabele w tabeli tymczasowej,
a nastöpnie pobierze z niej dwadzieĈcia rekordów. MoĔna uniknñè takiej sytuacji poprzez
umieszczenie klauzuli

LIMIT

wewnñtrz kaĔdego zapytania tworzñcego konstrukcjö

UNION

Optymalizacja po

ĥéczonych indeksów

Wprowadzone w MySQL 5.0 algorytmy äñczenia indeksów pozwalajñ bazie danych MySQL
na uĔywanie w zapytaniu wiöcej niĔ tylko jednego indeksu na tabelö. WczeĈniejsze wersje
MySQL mogäy uĔywaè tylko pojedynczego indeksu. Dlatego teĔ kiedy pojedynczy indeks nie
byä wystarczajñco dobry, aby poradziè sobie z wszystkimi ograniczeniami w klauzuli

WHERE

, baza

danych MySQL czösto wybieraäa opcjö skanowania tabeli. Przykäadowo tabela

film_actor

ma indeks obejmujñcy kolumnö

film_id

oraz indeks obejmujñcy kolumnö

actor_id

, ale Ĕaden

z nich nie jest dobrym wyborem dla dwóch warunków klauzuli

WHERE

w poniĔszym zapytaniu:

mysql> SELECT film_id, actor_id FROM sakila.film_actor
-> WHERE actor_id = 1 OR film_id = 1;

Ograniczenia optymalizatora zapyta

ħ MySQL

_ 203

We wczeĈniejszych wersjach MySQL powyĔsze zapytanie spowodowaäoby skanowanie tabeli,
o ile nie zostaäoby napisane od nowa w postaci dwóch zapytaþ poäñczonych klauzulñ

UNION

mysql> SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1
    -> UNION ALL
    -> SELECT film_id, actor_id FROM sakila.film_actor WHERE film_id = 1
    ->    AND actor_id <> 1;

Jednak w bazie danych MySQL 5.0 i nowszych zapytanie to moĔe uĔyè obu indeksów rów-
noczeĈnie, skanujñc je i äñczñc wyniki. Istniejñ trzy odmiany algorytmu: unia dla warunków

czöĈè wspólna dla warunków

AND

oraz unia czöĈci wspólnych dla poäñczenia dwóch wymie-

nionych. Przedstawione poniĔej zapytanie uĔywa unii operacji skanowania dwóch indeksów,
co moĔna zobaczyè podczas analizy kolumny

Extra

mysql> EXPLAIN SELECT film_id, actor_id FROM sakila.film_actor
    -> WHERE actor_id = 1 OR film_id = 1\G
*************************** Rekord 1. ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
         type: index_merge
possible_keys: PRIMARY,idx_fk_film_id
          key: PRIMARY,idx_fk_film_id
      key_len: 2,2
          ref: NULL
         rows: 29
        Extra: Using union(PRIMARY,idx_fk_film_id); Using where

Baza danych MySQL moĔe wykorzystaè tö technikö w skomplikowanych klauzulach

WHERE

Dlatego teĔ w niektórych zapytaniach kolumna

Extra

bödzie wyĈwietlaäa zagnieĔdĔone ope-

racje. Mechanizm zazwyczaj dziaäa bardzo dobrze, ale czasami przeprowadzane przez algorytm
buforowanie, sortowanie i operacje äñczenia uĔywajñ duĔej iloĈci zasobów procesora i pamiöci.
Dotyczy to zwäaszcza sytuacji, gdy nie wszystkie indeksy zapewniajñ wysoki poziom selek-
tywnoĈci, a wiöc równoczesne skanowanie zwraca duĔñ iloĈè rekordów poddawanych opera-
cji äñczenia. Warto sobie w tym miejscu przypomnieè, Ĕe optymalizator nie wlicza tego do
kosztu — po prostu optymalizuje liczbö losowych odczytów stron. W ten sposób koszt za-
pytania jest „niedoszacowany” i wykonanie takiego zapytania moĔe byè wolniejsze niĔ zwy-
käe skanowanie tabeli. Ogromne zuĔycie zasobów pamiöci i mocy obliczeniowej procesora
moĔe mieè takĔe wpäyw na równoczeĈnie wykonywane inne zapytania, ale efekt ten nie bödzie
widoczny, kiedy zapytanie jest wykonywane w izolacji. To kolejny powód skäaniajñcy do
projektowania realistycznych testów wydajnoĈci.

JeĔeli zapytanie jest wykonywane znacznie wolniej z powodu tego ograniczenia optymalizatora,
moĔna sobie poradziè, wyäñczajñc okreĈlone indeksy za pomocñ polecenia

IGNORE INDEX

lub

po prostu wracajñc do starej taktyki z uĔyciem klauzuli

UNION

Szerzenie równo

ļci

W niektórych przypadkach szerzenie równoĈci moĔe nieĈè ze sobñ nieoczekiwane koszty.
Przykäadowo warto rozwaĔyè ogromnñ listö

IN()

obejmujñcñ kolumnö, która wg informacji

optymalizatora bödzie równa okreĈlonym kolumnom w innych tabelach z powodu klauzul

WHERE

lub

USING

ustanawiajñcych równoĈè kolumn wobec siebie.

204 _

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

Optymalizator bödzie „wspóädzieliä” listö poprzez skopiowanie jej do odpowiednich kolumn
we wszystkich powiñzanych tabelach. Zwykle jest to pomocne, poniewaĔ daje optymalizato-
rowi zapytaþ oraz silnikowi wykonywania wiöcej moĔliwoĈci w zakresie miejsca faktycznego
przeprowadzenia sprawdzenia listy

IN()

. Kiedy jednak lista jest ogromna, skutkiem moĔe

byè mniej efektywna optymalizacja i wolniejsze wykonanie zapytania. W trakcie pisania tej
ksiñĔki nie byäo dostöpne Ĕadne wbudowane w bazö rozwiñzanie pozwalajñce na ominiöcie
tego problemu — w takim przypadku trzeba po prostu zmodyfikowaè kod Ēródäowy. (Problem
jednak nie wystöpuje u wiökszoĈci uĔytkowników).

Wykonywanie równoleg

ĥe

Baza danych MySQL nie potrafi wykonywaè pojedynczego zapytania równoczeĈnie na wielu
procesorach. Taka funkcja jest oferowana przez niektóre serwery baz danych, ale nie przez
MySQL. Autorzy wspominajñ, aby nie poĈwiöcaè zbyt duĔej iloĈci czasu na próbö odkrycia,
jak w MySQL uzyskaè wykonywanie zapytania równoczeĈnie na wielu procesorach.

ĥéczenia typu hash

Podczas pisania ksiñĔki baza danych MySQL nie przeprowadzaäa prawdziwych zäñczeþ typu
hash — wszystko pozostaje zäñczeniem w postaci zagnieĔdĔonej pötli. Jednak zäñczenia typu
hash moĔna emulowaè za pomocñ indeksów typu hash. JeĔeli uĔywany silnik magazynu da-
nych jest inny niĔ Memory, trzeba takĔe emulowaè indeksy typu hash. Wiöcej informacji
na ten temat przedstawiono w sekcji „Budowa wäasnych indeksów typu hash”, znajdujñcej
siö w rozdziale 3.

Śne skanowanie indeksu

Baza  danych  MySQL  znana  jest  z  tego,  Ĕe  nie  umoĔliwia  przeprowadzenia  luĒnego  skano-
wania  indeksu,  które  polega  na  skanowaniu  nieprzylegajñcych  do  siebie  zakresów  indeksu.
Ogólnie rzecz biorñc, skanowanie indeksu w MySQL wymaga zdefiniowania punktu poczñt-
kowego  oraz  koþcowego  w  indeksie  nawet  wtedy,  jeĈli  kilka  nieprzylegajñcych  do  siebie
rekordów w Ĉrodku jest naprawdö poĔñdanych w danym zapytaniu. Baza danych MySQL
bödzie skanowaäa caäy zakres rekordów wewnñtrz zdefiniowanych punktów koþcowych.

Przykäad pomoĔe w zilustrowaniu tego problemu. Zakäadamy, Ĕe tabela zawiera indeks
obejmujñcy kolumny

(a, b)

, a programista chce wykonaè poniĔsze zapytanie:

mysql> SELECT ... FROM tbl WHERE b BETWEEN 2 AND 3;

PoniewaĔ indeks rozpoczyna siö od kolumny

, ale klauzula

WHERE

zapytania nie zawiera

kolumny

, baza danych MySQL przeprowadzi skanowanie tabeli oraz za pomocñ klauzuli

WHERE

wyeliminuje nieprzylegajñce do siebie rekordy, co pokazano na rysunku 4.5.

Bardzo äatwo moĔna dostrzec, Ĕe istniejñ szybsze sposoby wykonania tego zapytania. Struktura
indeksu (ale nie API silnika magazynu danych MySQL) pozwala na wyszukanie poczñtku
kaĔdego zakresu wartoĈci, skanowanie aĔ do koþca zakresu, a nastöpnie przejĈcie na poczñtek
kolejnego zakresu. Na rysunku 4.6 pokazano, jak taka strategia mogäaby wyglñdaè, gdyby
zostaäa zaimplementowana w MySQL.

206 _

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

Warto zwróciè uwagö na brak klauzuli

WHERE

, która staäa siö zbödna, poniewaĔ sam indeks

pozwala na pomijanie niepotrzebnych rekordów. (Przypominamy ponownie, Ĕe baza danych
MySQL nie ma jeszcze takich moĔliwoĈci).

Jest to, co prawda, uproszczony przykäad i przedstawione zapytanie moĔna äatwo zoptyma-
lizowaè poprzez dodanie innego indeksu. Jednak istnieje wiele sytuacji, gdy dodanie innego
indeksu nie stanowi rozwiñzania. Jednym z takich przypadków jest zapytanie, które zawiera
warunek zakresu wzglödem pierwszej kolumny indeksu oraz warunek równoĈci wzglödem
drugiej kolumny indeksu.

Poczñwszy od MySQL w wersji 5.0, operacja luĒnego skanowania indeksu jest moĔliwa w pew-
nych ĈciĈle okreĈlonych sytuacjach, np. w zapytaniach wyszukujñcych wartoĈci maksymalnñ
i minimalnñ w zgrupowanym zapytaniu:

mysql> EXPLAIN SELECT actor_id, MAX(film_id)
    -> FROM sakila.film_actor
    -> GROUP BY actor_id\G
*************************** Rekord 1. ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
         type: range
possible_keys: NULL
          key: PRIMARY
      key_len: 2
          ref: NULL
         rows: 396
        Extra: Using index for group-by

Informacja „Using index for group-by” wyĈwietlona w danych wyjĈciowych polecenia

EXPLAIN

wskazuje na zastosowanie luĒnego skanowania indeksu. To jest dobry rodzaj optymalizacji
w tym specjalnym przypadku, ale równoczeĈnie nie jest to ogólnego przeznaczenia luĒne
skanowanie indeksu. Lepiej byäoby, gdyby informacja miaäa postaè „loose index probe”.

Dopóki baza danych MySQL nie bödzie obsäugiwaäa ogólnego przeznaczenia luĒnego ska-
nowania indeksu, obejĈciem problemu jest zastosowanie staäej bñdĒ listy staäych dla pierw-
szych kolumn indeksu. W zaprezentowanym w poprzednim rozdziale studium przypadku
indeksowania przedstawiono kilka przykäadów osiñgniöcia dobrej wydajnoĈci za pomocñ takich
zapytaþ.

Funkcje MIN() i MAX()

Baza danych MySQL nie moĔe zbyt dobrze zoptymalizowaè pewnych zapytaþ wykorzystu-
jñcych funkcje

MIN()

lub

MAX()

. Oto przykäad takiego zapytania:

mysql> SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = 'PENELOPE';

PoniewaĔ nie ma indeksu obejmujñcego kolumnö

first_name

, powyĔsze zapytanie spowoduje

przeprowadzenie skanowania tabeli. JeĔeli MySQL skanuje klucz podstawowy, teoretycznie
moĔe zatrzymaè skanowanie po odczytaniu pierwszego dopasowanego rekordu, gdyĔ klucz
podstawowy jest w kolejnoĈci ĈciĈle rosnñcej i kaĔdy nastöpny rekord bödzie miaä wiökszñ
wartoĈè

actor_id

. Jednak w omawianym przypadku MySQL przeskanuje caäñ tabelö, o czym

moĔna siö przekonaè po sprofilowaniu zapytania. Rozwiñzaniem problemu jest usuniöcie
funkcji

MIN()

i napisanie zapytania z uĔyciem klauzuli

LIMIT

, np. nastöpujñco:

mysql> SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY)
-> WHERE first_name = 'PENELOPE' LIMIT 1;

Optymalizacja okre

ļlonego rodzaju zapytaħ

_ 207

Taka  strategia  bardzo  czösto  dziaäa  doskonale  w  innej  sytuacji,  kiedy  baza  danych  MySQL
wybraäa  skanowanie wiökszej  liczby  rekordów  niĔ  potrzeba.  PuryĈci  mogñ  uznaè,  Ĕe  tego
rodzaju zapytanie oznacza brak zrozumienia SQL. Z reguäy programista informuje serwer,
co chce uzyskaè, a serwer okreĈla, jak pobraè te dane. W omawianym zapytaniu programista
informuje serwer MySQL, jak wykonaè  dane  zapytanie.  Dlatego  teĔ  z  zapytania  nie  wynika
jasno, Ĕe szukane dane to wartoĈè minimalna. To prawda, ale czasami trzeba poĈwiöciè zasady
w imiö uzyskania wiökszej wydajnoĈci.

Równoczesne wykonywanie polece

ħ SELECT i UPDATE w tej samej tabeli

Baza danych MySQL nie pozwala na wykonywanie polecenia

SELECT

wzglödem tabeli, na

której jednoczeĈnie jest wykonywane polecenie

UPDATE

. Naprawdö nie jest to ograniczenie

wynikajñce z optymalizatora, ale wiedza o sposobie wykonywania zapytaþ przez MySQL
moĔe pomóc w obejĈciu tego problemu. PoniĔej przedstawiono przykäad niedozwolonego
zapytania, mimo Ĕe jest standardowym kodem SQL. Zapytanie powoduje uaktualnienie kaĔdego
rekordu liczbñ podobnych rekordów znajdujñcych siö w tabeli:

mysql> UPDATE tbl AS outer_tbl
    ->   SET cnt = (
    ->      SELECT count(*) FROM tbl AS inner_tbl
    ->      WHERE inner_tbl.type = outer_tbl.type
    ->   );
ERROR 1093 (HY000): You can't specify target table 'outer_tbl' for update in FROM
clause

Aby obejĈè to ograniczenie, moĔna wykorzystaè tabelö pochodnñ, poniewaĔ MySQL potrak-
tuje jñ jak tabelö tymczasowñ. W ten sposób faktycznie zostanñ wykonane dwa zapytania:
pierwsze to

SELECT

w podzapytaniu, drugie obejmuje wiele tabel

UPDATE

z poäñczonymi wy-

nikami tabeli oraz podzapytania. Podzapytanie bödzie otwieraäo i zamykaäo tabelö przed
otworzeniem jej przez zewnötrzne zapytanie

UPDATE

, a wiöc caäe zapytanie bödzie mogäo

zostaè wykonane:

mysql> UPDATE tbl
    ->    INNER JOIN(
    ->       SELECT type, count(*) AS cnt
    ->       FROM tbl
    ->       GROUP BY type
    ->    ) AS der USING(type)
    -> SET tbl.cnt = der.cnt;

Optymalizacja okre

ļlonego rodzaju zapytaħ

W podrozdziale zostanñ przedstawione wskazówki dotyczñce optymalizacji okreĈlonego ro-
dzaju zapytaþ. WiökszoĈè tych zagadnieþ zostaäa szczegóäowo omówiona w innych czöĈciach
ksiñĔki, ale autorzy chcieli utworzyè listö najczöĈciej spotykanych problemów optymalizacji,
do której moĔna äatwo powracaè.

WiökszoĈè wskazówek przedstawionych w podrozdziale jest uzaleĔniona od wersji serwera
i moĔe byè nieaktualna w przyszäych wersjach MySQL. Nie ma Ĕadnego powodu, aby pew-
nego dnia sam serwer nie uzyskaä moĔliwoĈci przeprowadzania niektórych bñdĒ wszystkich
z wymienionych optymalizacji.

208 _

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

Optymalizacja zapyta

ħ COUNT()

Funkcja agregujñca

COUNT()

i sposób optymalizacji zapytaþ wykorzystujñcych tö funkcjö to

prawdopodobnie jeden z dziesiöciu najbardziej niezrozumiaäych tematów w MySQL. Liczba
bäödnych informacji na ten temat, które autorzy znaleĒli w Internecie, jest wiöksza, niĔ moĔna
sñdziè.

Przed zagäöbieniem siö w zagadnienia optymalizacji bardzo waĔne jest zrozumienie, jak dziaäa
funkcja

COUNT()

Jakie jest dzia

ĥanie funkcji COUNT()?

COUNT()

to funkcja specjalna dziaäajñca na dwa odmienne sposoby: zlicza wartoĈci oraz rekordy.

WartoĈè jest wyraĔeniem innym niĔ

NULL

(poniewaĔ

NULL

oznacza brak wartoĈci). JeĔeli

w nawiasie zostanie podana nazwa kolumny lub inne wyraĔenie, funkcja

COUNT()

obliczy, ile

razy podane wyraĔenie ma wartoĈè. Dla wielu osób bödzie to bardzo mylñce, co po czöĈci
wynika z faktu, Ĕe same wartoĈci

NULL

sñ mylñce. JeĔeli czytelnik musi nauczyè siö, jak dziaäa

SQL, warto siögnñè pod dobrñ ksiñĔkö omawiajñcñ podstawy SQL. (Internet niekoniecznie jest
dobrym Ēródäem informacji na ten temat).

Inna forma funkcji

COUNT()

po prostu oblicza liczbö rekordów w wyniku. Jest to sposób dziaäania

bazy danych MySQL, kiedy wie, Ĕe wyraĔenie umieszczone w nawiasie nigdy nie bödzie

NULL

Najbardziej oczywistym przykäadem jest polecenie

COUNT(*)

bödñce specjalnñ formñ funkcji

COUNT()

. Nie powoduje ona rozszerzenia znaku wieloznacznego

na peänñ listö kolumn w tabeli,

jak moĔna by tego oczekiwaè. Zamiast tego caäkowicie ignoruje kolumny i zlicza rekordy.

Jednym z najczöĈciej popeänianych bäödów jest podawanie w nawiasie nazw kolumn, kiedy
programista chce, aby funkcja zliczyäa rekordy. Gdy trzeba obliczyè liczbö rekordów w wy-
niku, wtedy zawsze naleĔy uĔyè funkcji

COUNT(*)

. Taka postaè jasno wskazuje intencje pro-

gramisty i pozwala uniknñè kiepskiej wydajnoĈci.

Mity dotycz

éce MyISAM

Czösto popeänianym bäödem jest przeĈwiadczenie, Ĕe silnik MyISAM jest wyjñtkowo szybki
podczas wykonywania zapytaþ

COUNT()

. Wprawdzie jest szybki, ale tylko w wyjñtkowej

sytuacji: kiedy stosujemy funkcjö

COUNT(*)

bez klauzuli

WHERE

, która po prostu zlicza liczbö

rekordów w caäej tabeli. Baza danych MySQL moĔe zoptymalizowaè to zapytanie, poniewaĔ
silnik magazynu danych zawsze otrzymuje informacjö, ile rekordów znajduje siö w tabeli.
JeĔeli w MySQL okreĈlono, Ĕe

col

nigdy nie przyjmie wartoĈci

NULL

, wówczas moĔna rów-

nieĔ zoptymalizowaè wyraĔenie

COUNT(col)

poprzez wewnötrznñ konwersjö wyraĔenia

COUNT(*)

Silnik MyISAM nie ma Ĕadnych magicznych optymalizacji dotyczñcych zliczania rekordów,
kiedy zapytanie uĔywa klauzuli

WHERE

lub dla bardziej ogólnych przypadków zliczania

wartoĈci zamiast rekordów. W okreĈlonym zapytaniu moĔe dziaäaè szybciej niĔ inne silniki
magazynu danych, ale nie musi. Wszystko zaleĔy od wielu czynników.

Optymalizacja okre

ļlonego rodzaju zapytaħ

_ 209

Prosta optymalizacja

Czasami moĔna wykorzystaè zalety optymalizacji MyISAM w postaci

COUNT(*)

do zliczenia

wszystkiego, z wyjñtkiem maäej liczby rekordów, które zostaäy doskonale zindeksowane.
W przedstawionym poniĔej przykäadzie uĔyto standardowej bazy danych

World

w celu po-

kazania, jak moĔna efektywnie znaleĒè liczbö miast, których identyfikator

jest wiökszy niĔ

Takie zapytanie moĔna zapisaè nastöpujñco:

mysql> SELECT COUNT(*) FROM world.City WHERE ID > 5;

JeĔeli powyĔsze zapytanie zostanie sprofilowane za pomocñ polecenia

SHOW STATUS

, moĔna

przekonaè siö, Ĕe zapytanie przeskanowaäo 4079 rekordów. Po odwróceniu warunków i odjöciu
liczby miast, których identyfikator

ma wartoĈè mniejszñ lub równñ

od ogólnej liczby

miast, liczba analizowanych rekordów spada do piöciu:

mysql> SELECT (SELECT COUNT(*) FROM world.City) - COUNT(*)
-> FROM world.City WHERE ID <= 5;

PowyĔsza wersja zapytania odczytuje mniejszñ iloĈè rekordów, poniewaĔ w trakcie fazy opty-
malizacji podzapytanie jest zamieniane na staäñ, o czym moĔna przekonaè siö, przeglñdajñc
dane wyjĈciowe polecenia

EXPLAIN

+----+-------------+-------+...+------+------------------------------+
| id | select_type | table |...| rows | Extra                        |
+----+-------------+-------+...+------+------------------------------+
|  1 | PRIMARY     | City  |...|    6 | Using where; Using index     |
|  2 | SUBQUERY    | NULL  |...| NULL | Select tables optimized away |
+----+-------------+-------+...+------+------------------------------+

Czösto pojawiajñce siö pytanie na listach dyskusyjnych i kanaäach IRC dotyczy tego, jak po-
braè liczbö  odmiennych wartoĈci w tej samej  kolumnie  za  pomocñ  tylko  jednego  zapytania,
ograniczajñc w ten sposób ogólnñ liczbö wymaganych zapytaþ. ZaäóĔmy np., Ĕe programista
chce  utworzyè  pojedyncze  zapytanie,  które  zlicza  iloĈè  elementów  w  kilku  kolorach.  Nie
moĔna  uĔyè  klauzuli

(np.

SELECT COUNT(color = 'blue' OR color = 'red') FROM

items;

), poniewaĔ programista nie chce oddzieliè róĔnych liczników od odmiennych kolo-

rów. Kolorów nie moĔna takĔe umieĈciè w klauzuli

WHERE

(np.

SELECT COUNT(*) FROM items

WHERE color = 'blue' AND color = 'red';

), poniewaĔ kolory sñ wzajemnie wykluczajñce siö.

PoniĔej przedstawiono zapytanie rozwiñzujñce ten problem:

mysql> SELECT SUM(IF(color = 'blue', 1, 0)) AS blue,
SUM(IF(color = 'red', 1, 0)) ->AS red FROM items;

PoniĔej znajduje siö kolejne rozwiñzanie przedstawionego problemu, ale zamiast funkcji

SUM()

zastosowano w zapytaniu funkcjö

COUNT()

, przy upewnieniu siö, Ĕe wyraĔenie nie bödzie

miaäo wartoĈci, kiedy kryteria bödñ faäszywe:

mysql> SELECT COUNT(color = 'blue' OR NULL) AS blue, COUNT(color = 'red' OR NULL)
-> AS red FROM items;

Bardziej skomplikowana optymalizacja

Ogólnie rzecz biorñc, zapytania

COUNT()

sñ trudne do optymalizacji, poniewaĔ z reguäy

muszñ obliczaè duĔñ liczbö rekordów (np. dostöp do ogromnej iloĈci danych). Jedynñ innñ
opcjñ jest optymalizacja wewnñtrz samego serwera MySQL, tak aby uĔywaä indeksu pokry-
wajñcego. Takie rozwiñzanie przedstawiono w rozdziale 3. JeĔeli i to okaĔe siö niewystarczajñce,
trzeba bödzie wprowadziè zmiany w architekturze aplikacji. Warto rozwaĔyè uĔycie tabel

210

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

podsumowania (równieĔ omówione w rozdziale 3.) oraz zewnötrzny system buforowania,
taki jak memcached. Czytelnik prawdopodobnie stanie przed dobrze znanym dylematem:
„szybko, dobrze i prosto — wybierz dwa dowolne”.

Optymalizacja zapyta

ħ typu JOIN

Temat ten jest w rzeczywistoĈci poruszany w caäej ksiñĔce, ale w tym miejscu autorzy uwa-
Ĕajñ za stosowne, by wspomnieè o kilku aspektach.

NaleĔy siö upewniè, Ĕe indeksy obejmujñ kolumny uĔywane w klauzulach

lub

USING

Wiöcej informacji na temat indeksowania przedstawiono w podrozdziale „Podstawy in-
deksowania”, znajdujñcym siö w rozdziale 3. Podczas dodawania indeksów warto roz-
waĔyè uĔycie kolejnoĈci stosowanej w zäñczeniach. JeĔeli za pomocñ kolumny

zäñczane

sñ tabele

, a optymalizator postanowi o zäñczeniu tabel w kolejnoĈci

B, A

, wówczas

nie trzeba indeksowaè kolumny w tabeli

. NieuĔywane indeksy stanowiñ dodatkowe

obciñĔenie. Ogólnie rzecz biorñc, indeksy warto dodaè jedynie do drugiej tabeli i zasto-
sowaè przy tym kolejnoĈè uĔytñ w zäñczeniu, o ile indeksy nie sñ potrzebne do jeszcze
innych zadaþ.

Warto podjñè próbö upewnienia siö, Ĕe kaĔde wyraĔenie

GROUP BY

ORDER BY

odnosi siö

do kolumn z pojedynczej tabeli. W ten sposób baza danych MySQL bödzie mogäa spró-
bowaè uĔycia indeksu podczas wykonywania tej operacji.

NaleĔy zachowaè ostroĔnoĈè podczas uaktualniania serwera MySQL, poniewaĔ skäadnia
zäñczeþ, kolejnoĈè operatorów oraz inne zachowania mogäy ulec zmianie. Czasami to, co
byäo zwykäym zäñczeniem, moĔe staè siö innym produktem, innym rodzajem zäñczenia
zwracajñcym odmienne wyniki bñdĒ nawet juĔ niewäaĈciwñ skäadniñ.

Optymalizacja podzapyta

NajwaĔniejszñ wskazówkñ dotyczñcñ podzapytaþ, jakiej moĔna udzieliè, jest zalecenie, aby
— gdy tylko jest to moĔliwe — stosowaè zäñczenia, przynajmniej w bieĔñcych wersjach MySQL.
Temat zostaä dokäadnie omówiony na poczñtku rozdziaäu.

Podzapytania sñ przedmiotem intensywnych prac zespoäu zajmujñcego siö optymalizatorem.
Dlatego teĔ przyszäe wersje MySQL mogñ posiadaè wiöcej moĔliwoĈci w zakresie optymali-
zacji podzapytaþ. Dopiero wówczas okaĔe siö, które optymalizacje znajdñ siö w wersji final-
nej oraz jakñ przyniosñ róĔnicö. Udzielona w tym miejscu wskazówka, Ĕe „warto stosowaè
zäñczenia”, nie musi byè aktualna w przyszäoĈci. Wraz z upäywem czasu serwer staje siö coraz
„sprytniejszy” i sytuacje, w których programista musi wskazywaè sposób rozwiñzania danego
problemu zamiast oczekiwanych wyników, bödñ coraz rzadsze.

Optymalizacja zapyta

ħ typu GROUP BY i DISTINCT

W wielu przypadkach baza danych MySQL optymalizuje te dwa rodzaje zapytaþ bardzo po-
dobnie. W rzeczywistoĈci, kiedy trzeba, podczas procesu optymalizacji po prostu przepro-
wadza wewnötrznñ konwersjö miödzy nimi. Dla obu rodzajów zapytaþ bardzo korzystne sñ
indeksy, a wiöc bödzie to najwaĔniejsza droga prowadzñca do ich optymalizacji.

Optymalizacja okre

ļlonego rodzaju zapytaħ

211

Kiedy nie moĔna uĔyè indeksów, MySQL ma dwa rodzaje strategii stosowania

GROUP BY

albo uĔycie tabeli tymczasowej, albo sortowanie plików w celu wykonania grupowania. KaĔda
z tych strategii jest efektywna w okreĈlonych zapytaniach. Za pomocñ

SQL_BIG_RESULT

oraz

SQL_SMALL_RESULT

moĔna wymusiè na optymalizatorze wybór danej metody.

JeĔeli trzeba zgrupowaè zäñczenie poprzez wartoĈè pochodzñcñ z przeszukiwanej tabeli, wtedy
zwykle efektywniejszym sposobem grupowania jest przeszukanie identyfikatora tabeli
zamiast wartoĈci. Przedstawione jako przykäad poniĔsze zapytanie nie jest tak efektywne, jak
mogäoby byè:

mysql> SELECT actor.first_name, actor.last_name, COUNT(*)
    -> FROM sakila.film_actor
    ->    INNER JOIN sakila.actor USING(actor_id)
    -> GROUP BY actor.first_name, actor.last_name;

Zapytanie bödzie efektywniejsze po zapisaniu w postaci:

mysql> SELECT actor.first_name, actor.last_name, COUNT(*)
    -> FROM sakila.film_actor
    ->    INNER JOIN sakila.actor USING(actor_id)
    -> GROUP BY film_actor.actor_id;

Grupowanie pod wzglödem kolumny

actor.actor_id

moĔe byè efektywniejsze niĔ grupo-

wanie pod wzglödem kolumny

film_actor.actor_id

. NaleĔy przeprowadziè profilowanie

i (lub) testy wydajnoĈci, aby zobaczyè, jak to wyglñda dla uĔywanych danych.

Zapytanie wykorzystuje fakt, Ĕe imiö i nazwisko aktora jest uzaleĔnione od wartoĈci pola

actor_id

, a wiöc zwróci te same wyniki. Jednak nie zawsze bödzie moĔliwe beztroskie wy-

branie niezgrupowanych kolumn i otrzymanie tych samych wyników. Opcja konfiguracyjna
serwera o nazwie

SQL_MODE

moĔe nawet uniemoĔliwiaè taki krok. Alternatywñ jest uĔycie

funkcji

MIN()

lub

MAX()

kiedy wiadomo, Ĕe wartoĈci w grupie bödñ odmienne, poniewaĔ

zaleĔñ od zgrupowanych kolumn. Ewentualnie, jeĈli nie ma znaczenia, która wartoĈè bödzie
otrzymana, moĔna wykonaè zapytanie:

mysql> SELECT MIN(actor.first_name), MAX(actor.last_name), ...;

PuryĈci mogñ spieraè siö, Ĕe grupowanie nastñpiäo wzglödem niewäaĈciwego elementu, i bödñ
mieli racjö. NiepoĔñdane funkcje

MIN()

lub

MAX()

sñ znakiem, Ĕe struktura zapytania jest

niewäaĈciwa. Jednak czasami jedynym celem jest to, aby serwer MySQL wykonywaä zapytanie
SQL tak szybko, jak to moĔliwe. PuryĈci bödñ usatysfakcjonowani po zapisaniu powyĔszego
zapytania w nastöpujñcej postaci:

mysql> SELECT actor.first_name, actor.last_name, c.cnt
    -> FROM sakila.actor
    ->    INNER JOIN (
    ->       SELECT actor_id, COUNT(*) AS cnt
    ->       FROM sakila.film_actor
    ->       GROUP BY actor_id
    ->    ) AS c USING(actor_id) ;

Czasami koszt utworzenia i wypeänienia tabeli tymczasowej wymaganej przez podzapytanie
jest bardzo wysoki w porównaniu z kosztem lekkiego wypaczenia teorii relacyjnoĈci. NaleĔy
pamiötaè, Ĕe tabela tymczasowa utworzona przez podzapytanie nie ma indeksów.

Ogólnie  rzecz  biorñc,  kiepskim  pomysäem  jest  wybór  niezgrupowanych  kolumn  w  zgrupo-
wanym  zapytaniu,  poniewaĔ  wyniki  bödñ  niedeterministyczne  oraz  äatwo  mogñ  ulec  zmia-
nie, jeĈli nastñpi modyfikacja indeksu  bñdĒ  optymalizator  zadecyduje  o  uĔyciu  odmiennej

212

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

strategii. WiökszoĈè tego rodzaju zapytaþ, z którymi spotkali siö autorzy, byäo czystym przy-
padkiem (poniewaĔ serwer nie zgäaszaä zastrzeĔeþ) lub wynikiem lenistwa, a nie celowym
projektem dotyczñcym optymalizacji. Znacznie lepiej zachowaè jasnoĈè. Autorzy zalecajñ
ustawienie zmiennej konfiguracyjnej serwera o nazwie

SQL_MODE

w taki sposób, aby zawieraäa

ONLY_FULL_GROUP_BY

. Dziöki temu serwer wyĈwietli komunikat bäödu i nie pozwoli progra-

miĈcie na utworzenie bäödnego zapytania.

Baza danych MySQL automatycznie ustala kolejnoĈè zgrupowanych zapytaþ, uwzglödniajñc
kolumny w klauzuli

GROUP BY

, o ile programista wyraĒnie nie zastosuje klauzuli

ORDER BY

JeĔeli programista nie przykäada wagi do kolejnoĈci i zauwaĔy zastosowanie operacji sorto-
wania pliku, moĔe uĔyè klauzuli

ORDER BY NULL

wyäñczajñcej automatyczne sortowanie.

Po klauzuli

GROUP BY

moĔna takĔe dodaè opcjonalne säowa kluczowe

DESC

lub

ASC

powodujñce

uäoĔenie wyników w poĔñdanym kierunku wzglödem kolumn klauzuli.

Optymalizacja zapyta

ħ typu GROUP BY WITH ROLLUP

Pewnñ odmianñ zgrupowanych zapytaþ jest nakazanie bazie danych MySQL przeprowadze-
nia superagregacji wewnñtrz wyników. W tym celu moĔna wykorzystaè klauzulö

WITH ROLLUP

ale takie rozwiñzanie moĔe nie zapewniè tak dobrej optymalizacji, jakiej oczekuje programista.
Za pomocñ polecenia

EXPLAIN

warto sprawdziè przyjötñ przez serwer metodö wykonania,

zwracajñc uwagö na to, czy grupowanie zostaäo przeprowadzone za pomocñ operacji sortowa-
nia pliku czy uĔycia tabeli tymczasowej. Warto równieĔ usunñè klauzulö

WITH ROLLUP

i zoba-

czyè, czy zostanie wykorzystana taka sama metoda grupowania. Istnieje moĔliwoĈè wymuszenia
uĔycia wskazanej metody grupowania poprzez zastosowanie wskazówek przedstawionych
we wczeĈniejszej czöĈci rozdziaäu.

Czasami przeprowadzenie superagregacji w aplikacji jest duĔo bardziej efektywnym rozwiñ-
zaniem, nawet jeĈli oznacza koniecznoĈè pobrania z serwera znacznie wiökszej liczby rekordów.
W klauzuli

FROM

moĔna takĔe zastosowaè zagnieĔdĔone podzapytanie lub uĔyè tabeli tym-

czasowej przechowujñcej wyniki poĈrednie.

Najlepszym rozwiñzaniem moĔe byè przeniesienie funkcjonalnoĈci

WITH ROLLUP

do kodu

aplikacji.

Optymalizacja zapyta

ħ typu LIMIT i OFFSET

Zapytania z klauzulami

LIMIT

OFFSET

sñ czösto stosowane w systemach implementujñcych

stronicowanie niemal zawsze w poäñczeniu z klauzulñ

ORDER BY

. Pomocne bödzie posiadanie

indeksu obsäugujñcego tö kolejnoĈè, poniewaĔ w przeciwnym razie serwer bödzie musiaä
wykonaè duĔñ liczbö operacji sortowania pliku.

Czöstym problemem jest wysoka wartoĈè przesuniöcia. JeĔeli zapytanie ma postaè

LIMIT

10000, 20

, wygeneruje 10020 rekordów, a nastöpnie odrzuci pierwsze 10000, co jest bardzo

kosztowne. Przy zaäoĔeniu, Ĕe wszystkie strony sñ uĔywane z podobnñ czöstotliwoĈciñ, za-
pytanie bödzie przeciötnie skanowaäo poäowö tabeli. W celu optymalizacji takich zapytaþ
moĔna albo ograniczyè liczbö stron wyĈwietlanych w widoku stronicowania, albo spowodowaè,
by wysoka wartoĈè przesuniöcia byäa znacznie bardziej efektywna.

Optymalizacja okre

ļlonego rodzaju zapytaħ

_ 213

Jednñ z prostych technik poprawy wydajnoĈci jest przeprowadzanie przesuniöcia w indeksie
pokrywajñcym zamiast na peänych rekordach. PóĒniej wynik moĔna poäñczyè z peänymi
rekordami i pobraè dodatkowe wymagane kolumny. Takie rozwiñzanie jest znacznie bardziej
efektywne. Warto przeanalizowaè poniĔsze zapytanie:

mysql> SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;

JeĔeli tabela bödzie ogromna, wówczas lepsza postaè powyĔszego zapytania jest nastöpujñca:

mysql> SELECT film.film_id, film.description
    -> FROM sakila.film
    ->    INNER JOIN (
    ->       SELECT film_id FROM sakila.film
    ->       ORDER BY title LIMIT 50, 5
    ->    ) AS lim USING(film_id);

Takie rozwiñzanie sprawdza siö, pozwala bowiem serwerowi na analizö tak maäej iloĈci
danych, jaka jest moĔliwa w indeksie, bez koniecznoĈci uzyskania dostöpu do rekordów.
Nastöpnie, po znalezieniu poĔñdanych rekordów przeprowadzane jest zäñczenie z peänñ tabelñ
w celu pobrania innych kolumn rekordów. Podobna technika ma zastosowanie w przypadku
zäñczeþ z klauzulami

LIMIT

Czasami moĔna równieĔ skonwertowaè ograniczenie na postaè zapytania pozycyjnego, które
serwer moĔe wykonaè jako skanowanie pewnego zakresu indeksu. Przykäadowo po wcze-
Ĉniejszym przeliczeniu i zindeksowaniu kolumny

position

zapytanie moĔna napisaè na nowo

w takiej postaci:

mysql> SELECT film_id, description FROM sakila.film
-> WHERE position BETWEEN 50 AND 54 ORDER BY position;

Ranking danych powoduje powstanie tego samego problemu, ale zapytania zwykle dokäadajñ
jeszcze klauzulö

GROUP BY

. Niemal na pewno programista bödzie musiaä wczeĈniej obliczyè

i przechowywaè ranking danych.

JeĔeli naprawdö trzeba zoptymalizowaè system stronicowania, prawdopodobnie naleĔy za-
stosowaè przygotowane wczeĈniej podsumowania. Alternatywñ jest uĔycie zäñczeþ wzglödem
nadmiarowych tabel, które bödñ zawieraäy jedynie klucz podstawowy oraz kolumny wymagane
przez klauzulö

ORDER BY

. MoĔna takĔe uĔyè mechanizmu Sphinx. Wiöcej informacji na jego

temat znajduje siö w dodatku C.

Optymalizacja za pomoc

é opcji SQL_CALC_FOUND_ROWS

Innñ powszechnie stosowanñ technikñ wyĈwietlania stronicowanych wyników jest dodanie
opcji

SQL_CALC_FOUND_ROWS

do zapytania z klauzulñ

LIMIT

. W ten sposób bödzie wiadomo,

ile rekordów zostaäoby zwróconych, gdyby w zapytaniu nie byäo klauzuli

LIMIT

. MoĔna od-

nieĈè wraĔenie, Ĕe to pewnego rodzaju „magia”, kiedy serwer przewiduje liczbö rekordów,
które mógäby znaleĒè. Niestety, serwer tego nie robi, tzn. nie potrafi obliczyè liczby rekor-
dów, których faktycznie nie znajduje. Wymieniona opcja po prostu nakazuje serwerowi wy-
generowanie i odrzucenie pozostaäej czöĈci zbioru wynikowego, zamiast zakoþczyè dziaäanie
po znalezieniu poĔñdanej liczby rekordów. Dziaäanie to jest bardzo kosztowne.

Lepszym rozwiñzaniem jest konwersja programu stronicowania na äñcze „nastöpny”. Przy
zaäoĔeniu, Ĕe na stronie znajdzie siö dwadzieĈcia wyników wyszukiwania, zapytanie powinno
ograniczyè za pomocñ klauzuli

LIMIT

liczbö rekordów do 21, a nastöpnie wyĈwietliè jedynie 20.

214

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

JeĔeli w zbiorze wynikowym znajduje siö 21. rekord, oznacza on obecnoĈè kolejnej strony, a tym
samym moĔliwoĈè wygenerowania äñcza „nastöpny”.

Inna moĔliwoĈè to pobranie i buforowanie wiökszej liczby rekordów niĔ potrzeba — po-
wiedzmy 1000 — a nastöpnie pobieranie ich z bufora i umieszczanie na kolejnych stronach.
Ta strategia pozwala aplikacji „wiedzieè”, jak duĔy jest zbiór wynikowy. JeĔeli bödzie mniej-
szy niĔ 1000 rekordów, wówczas aplikacja moĔe obliczyè, ile powinna wygenerowaè äñczy.
Natomiast dla wiökszego zbioru wynikowego moĔe po prostu wyĈwietliè komunikat „Znale-
ziono ponad 1000 wyników”. Obie opisane strategie sñ znacznie efektywniejsze od nieustan-
nego generowania caäego zbioru wynikowego i odrzucania jego wiökszoĈci.

JeĈli nie moĔna uĔyè powyĔszych strategii, zastosowanie oddzielnego zapytania

COUNT(*)

w celu

okreĈlenia liczby rekordów moĔe byè szybsze niĔ wykorzystanie opcji

SQL_CALC_FOUND_ROWS

o ile moĔliwe jest uĔycie indeksu pokrywajñcego.

Optymalizacja klauzuli UNION

Baza danych MySQL zawsze wykonuje zapytania

UNION

poprzez utworzenie tabeli tymcza-

sowej i umieszczenie w niej wyników operacji

UNION

. Serwer MySQL nie potrafi zastosowaè

na zapytaniach

UNION

tak wielu optymalizacji, ilu mógäby uĔyè programista. Dlatego teĔ mo-

Ĕe siö zdarzyè, Ĕe bödzie trzeba udzieliè optymalizatorowi pomocy i röczne „wciñgnñè” klau-
zule

WHERE

LIMIT

ORDER BY

oraz inne warunki (np. kopiujñc je z zewnötrznego zapytania do

kaĔdego zapytania

SELECT

w konstrukcji

UNION

Zawsze trzeba uĔywaè klauzuli

UNION ALL

, chyba Ĕe nie jest wymagane usuniöcie przez ser-

wer powielajñcych siö rekordów. JeĔeli säowo kluczowe

ALL

zostanie pominiöte, MySQL do-

äñczy do tabeli tymczasowej opcjö

distinct

, która powoduje uĔywanie peänych rekordów

w celu ustalenia unikalnoĈci. To jest kosztowne. NaleĔy mieè na uwadze, Ĕe säowo kluczowe

ALL

nie powoduje wyeliminowania tabeli tymczasowej. Baza danych MySQL zawsze bödzie

umieszczaäa wyniki w tabeli tymczasowej, a nastöpnie ponownie je odczytywaäa, nawet jeĈli
nie bödzie to naprawdö konieczne (np. kiedy wyniki mogäyby zostaè bezpoĈrednio zwrócone
klientowi).

Opcje dotycz

éce optymalizatora zapytaħ

W bazie danych MySQL umieszczono kilka opcji optymalizatora, które moĔna wykorzystaè
w celu nadzorowania planu wykonywania zapytania, jeĈli programista nie jest zadowolony
z wyborów dokonanych przez optymalizator MySQL. PoniĔej przedstawiono opcje oraz
wskazano sytuacje, w których korzystne jest ich uĔycie. Odpowiedniñ opcjö naleĔy umieĈciè
w modyfikowanym zapytaniu; bödzie efektywna tylko w tym konkretnym zapytaniu. Skäad-
niö kaĔdej opcji moĔna znaleĒè w podröczniku uĔytkownika MySQL. Niektóre z nich sñ zde-
cydowanie powiñzane z wersjñ serwera bazy danych. Oto dostöpne opcje.

HIGH_PRIORITY

oraz

LOW_PRIORITY

Opcje informujñ MySQL, w jaki sposób ustalaè priorytet polecenia wzglödem innych poleceþ,
które próbujñ uzyskaè dostöp do tych samych tabel.

Opcja

HIGH_PRIORITY

informuje MySQL, Ĕe polecenia

SELECT

majñ byè wykonywane

przed wszelkimi innymi poleceniami, które mogñ oczekiwaè na moĔliwoĈè naäoĔenia
blokad, czyli chcñ zmodyfikowaè dane. W efekcie polecenia

SELECT

zostanñ umieszczone

Optymalizacja okre

ļlonego rodzaju zapytaħ

_ 215

na poczñtku kolejki, zamiast oczekiwaè na swojñ kolej. Ten modyfikator moĔna równieĔ
zastosowaè wzglödem poleceþ

INSERT

, co po prostu spowoduje zniwelowanie efektu

globalnego ustawienia serwera o nazwie

LOW_PRIORITY

Opcja

LOW_PRIORITY

jest przeciwieþstwem poprzedniej: powoduje umieszczenie polece-

nia na samym koþcu kolejki oczekiwania, jeĈli, oczywiĈcie, sñ inne polecenia, które chcñ
uzyskaè dostöp do tabel — nawet gdy inne polecenia zostaäy wydane póĒniej. Przypomi-
na to nadmiernie uprzejmñ osobö stojñcñ przed drzwiami restauracji: dopóki ktokolwiek
inny bödzie czekaä przed restauracjñ, uprzejma osoba bödzie przymieraäa gäodem. Opcjö tö
moĔna zastosowaè wzglödem poleceþ

SELECT

INSERT

UPDATE

REPLACE

oraz

DELETE

Opcje sñ efektywne w silnikach magazynu danych obsäugujñcych blokowanie na poziomie
tabeli, ale nigdy nie powinno siö ich stosowaè w InnoDB bñdĒ innych silnikach zapew-
niajñcych bardziej szczegóäowñ kontrolö blokowania i wspóäbieĔnoĈci. NaleĔy zachowaè
szczególnñ ostroĔnoĈè podczas uĔywania ich w silniku MyISAM, poniewaĔ mogñ wyäñ-
czyè moĔliwoĈè przeprowadzania operacji jednoczesnego wstawiania danych, a tym samym
znacznie zmniejszyè wydajnoĈè.

Czösto opcje

HIGH_PRIORITY

oraz

LOW_PRIORITY

sñ Ēródäem pewnego zamieszania. Nie

powodujñ zarezerwowania dla zapytaþ wiökszej bñdĒ mniejszej iloĈci zasobów, aby „pra-
cowaäy ciöĔej” lub „nie pracowaäy tak ciöĔko”. Wymienione opcje po prostu wpäywajñ na
sposób kolejkowania przez serwer zapytaþ, które oczekujñ na uzyskanie dostöpu do tabeli.

DELAYED

Opcja jest przeznaczona do uĔywania z poleceniami

INSERT

REPLACE

. Pozwala polece-

niu na natychmiastowe zwrócenie danych oraz umieszczenie wstawianych rekordów
w buforze, a nastöpnie wstawienie ich razem do tabeli, gdy tylko bödzie dostöpna. Taka
opcja jest najbardziej uĔyteczna podczas rejestrowania zdarzeþ oraz w podobnych apli-
kacjach, w których trzeba wstawiè duĔe iloĈci rekordów bez wstrzymywania klienta oraz
bez powodowania operacji I/O w przypadku kaĔdego zapytania. Istnieje kilka ograni-
czeþ zwiñzanych z tñ opcjñ, np. opóĒnione operacje wstawiania nie sñ zaimplementowane
we wszystkich silnikach magazynu danych. Poza tym, funkcja

LAST_INSERT_ID()

nie dziaäa

z opóĒnionymi operacjami wstawiania.

STRAIGHT_JOIN

Opcja ta pojawia siö albo tuĔ za säowem kluczowym

SELECT

w poleceniu

SELECT

, albo

miödzy dwiema äñczonymi tabelami w kaĔdym innym poleceniu. Pierwszy sposób uĔy-
cia wymusza, aby wszystkie tabele w zapytaniu byäy zäñczane z zachowaniem kolejnoĈci
ich przedstawienia w zapytaniu. Drugi sposób wymusza zachowanie kolejnoĈci podczas
zäñczenia dwóch tabel, miödzy którymi znajduje siö ta opcja.

Opcja

STRAIGHT_JOIN

jest uĔyteczna, w sytuacji kiedy baza danych MySQL nie wybiera

dobrej kolejnoĈci zäñczenia lub optymalizator wymaga duĔej iloĈci czasu na podjöcie de-
cyzji dotyczñcej stosowanej kolejnoĈci zäñczenia. W tym drugim przypadku wñtek spödza
duĔo czasu w stanie „Statistics”, a dodanie wymienionej opcji powoduje ograniczenie
optymalizatorowi przestrzeni wyszukiwania.

KolejnoĈè wybranñ przez optymalizator moĔna poznaè za pomocñ danych wyjĈciowych pole-
cenia

EXPLAIN

. NaleĔy napisaè nowe zapytanie w tej kolejnoĈci i dodaè opcjö

STRAIGHT_JOIN

Jest to bardzo dobry pomysä, przynajmniej tak däugo, jak däugo ustalona kolejnoĈè nie
skutkuje kiepskñ wydajnoĈciñ w niektórych klauzulach

WHERE

. Jednak po uaktualnieniu

serwera MySQL naleĔy ponownie przejrzeè takie polecenia, poniewaĔ mogñ pojawiè siö
nowe rodzaje optymalizacji, które bödñ uniewaĔniane przez opcjö

STRAIGHT_JOIN

216

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

SQL_SMALL_RESULT

oraz

SQL_BIG_RESULT

Opcje te sñ przeznaczone dla poleceþ

SELECT

. Informujñ optymalizator, jak i kiedy uĔywaè

tabel tymczasowych oraz sortowaè zapytania

GROUP BY

DISTINCT

. Opcja

SQL_SMALL_RESULT

informuje optymalizator, Ĕe zbiór wynikowy bödzie maäy i moĔe zostaè umieszczony
w zindeksowanej tabeli tymczasowej w celu unikniöcia sortowania dla grupowania. Z kolei
opcja

SQL_BIG_RESULT

wskazuje, Ĕe zbiór wynikowy bödzie ogromny i lepszym rozwiñ-

zaniem jest uĔycie tabel tymczasowych na dysku wraz z sortowaniem.

SQL_BUFFER_RESULT

Opcja  ta  nakazuje  optymalizatorowi  umieszczenie  wyników  w  tabeli  tymczasowej  oraz
zwolnienie  blokad  tabeli  tak  wczeĈnie,  jak  tylko  bödzie  to  moĔliwe.  To  zupeänie  inna
opcja  od  buforowania  po  stronie  klienta,  przedstawionego  w  podrozdziale  „Protokóä
klient-serwer MySQL”, we wczeĈniejszej czöĈci rozdziaäu. Buforowanie po stronie serwera
moĔe  byè  bardzo  uĔyteczne,  kiedy  nie  jest  stosowane  buforowanie  po  stronie  klienta,
pozwala bowiem uniknñè zuĔywania ogromnych iloĈci pamiöci po stronie klienta i nadal
powoduje bardzo szybkie zwalnianie blokad. Jednak oznacza równieĔ wykorzystanie
pamiöci serwera zamiast klienta.

SQL_CACHE

oraz

SQL_NO_CACHE

Opcje te informujñ serwer, Ĕe dane zapytanie jest lub nie jest kandydatem do buforowania
w buforze zapytaþ. Szczegóäowe informacje na temat uĔywania tych opcji zostaäy przed-
stawione w nastöpnym rozdziale.

SQL_CALC_FOUND_ROWS

Opcja nakazuje MySQL obliczenie peänego zbioru wynikowego, gdy stosowana jest klauzula

LIMIT

, choè zwracane bödñ jedynie rekordy wskazane przez tö klauzulö. Za pomocñ funkcji

FOUND_ROWS()

istnieje moĔliwoĈè pobrania caäkowitej liczby znalezionych rekordów. (Warto

powróciè do podrozdziaäu „Optymalizacja za pomocñ opcji SQL_CALC_FOUND_ROWS”
we wczeĈniejszej czöĈci rozdziaäy, aby przypomnieè sobie, dlaczego nie powinno uĔywaè siö
tej opcji).

FOR UPDATE

oraz

LOCK IN SHARE MODE

Opcje nadzorujñ blokady w poleceniach

SELECT

, ale jedynie w przypadku silników ma-

gazynu danych obsäugujñcych blokowanie na poziomie rekordu. Wymienione opcje po-
zwalajñ na naäoĔenie blokad na dopasowanych rekordach. Taka moĔliwoĈè moĔe byè
uĔyteczna, gdy programista chce zablokowaè rekordy, o których wiadomo, Ĕe bödñ póĒniej
uaktualniane, ewentualnie wtedy, kiedy chce uniknñè eskalacji blokad i po prostu nakäadaè
blokady na wyäñcznoĈè tak szybko, jak bödzie to moĔliwe.

Opcje nie sñ potrzebne w zapytaniach

INSERT ... SELECT

, które w MySQL 5.0 domyĈlnie

umieszczajñ blokady odczytu na rekordach Ēródäowych. (Istnieje moĔliwoĈè wyäñczenia
takiego zachowania, ale nie jest to dobry pomysä — wyjaĈnienie znajduje siö w rozdziaäach
8. i 11.). Baza danych MySQL 5.1 moĔe znieĈè to ograniczenie w pewnych warunkach.

W czasie pisania tej ksiñĔki jedynie silnik InnoDB obsäugiwaä te opcje i byäo jeszcze zbyt
wczeĈnie, aby stwierdziè, czy inne silniki magazynu danych oferujñce blokady na pozio-
mie rekordu bödñ w przyszäoĈci je obsäugiwaäy. Podczas uĔywania opcji w InnoDB naleĔy
zachowaè ostroĔnoĈè, poniewaĔ mogñ doprowadziè do wyäñczenia pewnych optymaliza-
cji, np. stosowania indeksu pokrywajñcego. Silnik InnoDB nie moĔe zablokowaè rekor-
dów na wyäñcznoĈè bez uzyskania dostöpu do klucza podstawowego, który jest miejscem
rekordu przechowujñcym informacje dotyczñce wersji.

Zmienne zdefiniowane przez u

Ŝytkownika

_ 217

USE INDEX

IGNORE INDEX

oraz

FORCE INDEX

Opcje informujñ optymalizator, które indeksy majñ byè uĔywane bñdĒ ignorowane pod-
czas wyszukiwania rekordów w tabeli (np. w trakcie decydowania o kolejnoĈci zäñcze-
nia). W bazie danych MySQL 5.0 i wczeĈniejszych wymienione opcje nie wpäywajñ na to,
które indeksy bödñ stosowane przez serwer podczas operacji sortowania i grupowania.
W MySQL 5.1 skäadnia moĔe pobieraè opcjonalnñ klauzulö

FOR ORDER BY

lub

FOR GROUP BY

Opcja

FORCE INDEX

jest taka sama jak

USE INDEX

, ale informuje optymalizator, Ĕe skano-

wanie tabeli jest wyjñtkowo kosztowne w porównaniu ze skanowaniem indeksu, nawet
jeĈli indeks nie jest zbyt uĔyteczny. Opcje moĔna zastosowaè, kiedy programista uwaĔa,
Ĕe optymalizator wybiera niewäaĈciwy indeks bñdĒ z jakiegokolwiek powodu majñ byè
wykorzystane zalety päynñce z uĔycia indeksu, np. jawne ustalenie kolejnoĈci bez uĔycia
klauzuli

ORDER BY

. Przykäad takiego rozwiñzania pokazano w podrozdziale „Optymali-

zacja zapytaþ typu LIMIT i OFFSET”, we wczeĈniejszej czöĈci rozdziaäu, podczas omawiania
efektywnego pobierania wartoĈci minimalnej za pomocñ klauzuli

LIMIT

W bazie danych MySQL 5.0 i nowszych wystöpujñ jeszcze pewne zmienne systemowe, które
majñ wpäyw na dziaäanie optymalizatora. Oto one.

optimizer_search_depth

Zmienna wskazuje optymalizatorowi, w jaki sposób wyczerpujñco analizowaè plany
czöĈciowe. JeĔeli wykonywanie zapytania w stanie „Statistics” zabiera bardzo duĔo czasu,
moĔna spróbowaè obniĔyè wartoĈè tej zmiennej.

optimizer_prune_level

Zmienna, domyĈlnie wäñczona, pozwala optymalizatorowi na pomijanie okreĈlonych
planów na podstawie liczby przeanalizowanych rekordów.

Obie opisane zmienne nadzorujñ skracanie dziaäania optymalizatora. Takie skróty sñ cenne
pod wzglödem wydajnoĈci w skomplikowanych zapytaniach, ale mogñ spowodowaè, Ĕe
serwer „przeoczy” optymalne plany w imiö efektywnoĈci. To jest powód, dla którego zmiana
tych opcji czasami ma sens.

Zmienne zdefiniowane przez u

Ŝytkownika

Bardzo äatwo zapomnieè o zmiennych zdefiniowanych przez uĔytkownika w MySQL, ale mogñ
one stanowiè technikö o potöĔnych moĔliwoĈciach, säuĔñcñ do tworzenia efektywnych zapytaþ.
Zmienne takie dziaäajñ szczególnie dobrze w przypadku zapytaþ odnoszñcych korzyĈci z poäñ-
czenia logik proceduralnej i relacyjnej. Czysto relacyjne zapytania traktujñ wszystko jak nie-
uporzñdkowane zbiory, którymi serwer w pewien sposób manipuluje, wszystkimi jednoczeĈnie.
Baza danych MySQL stosuje nieco bardziej pragmatyczne podejĈcie. MoĔna to uznaè za wadö,
ale równoczeĈnie moĔe okazaè siö zaletñ, gdy programista nauczy siö korzystaè z tej moĔliwoĈci.
A zmienne zdefiniowane przez uĔytkownika mogñ dodatkowo pomóc.

Zmienne zdefiniowane przez uĔytkownika sñ tymczasowymi magazynami dla wartoĈci, które
bödñ zachowane aĔ do zakoþczenia poäñczenia z serwerem. Definicja zmiennej polega po
prostu na przypisaniu jej warto

Ĉci za pomocñ poleceþ

SET

lub

SELECT

W niektórych sytuacjach przypisanie moĔna wykonaè za pomocñ zwykäego znaku równoĈci (

). Autorzy

uwaĔajñ jednak, Ĕe lepiej unikaè dwuznacznoĈci i zawsze stosowaè wyraĔenie

218

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

mysql> SET @one := 1;
mysql> SET @min_actor := (SELECT MIN(actor_id) FROM sakila.actor);
mysql> SET @last_week := CURRENT_DATE-INTERVAL 1 WEEK;

Zmiennñ moĔna zastosowaè w miejsce wyraĔenia, np.:

mysql> SELECT ... WHERE col <= @last_week;

Zanim zostanñ przedstawione zalety zmiennych zdefiniowanych przez uĔytkownika, warto
spojrzeè na pewne ich cechy i wady, aby przekonaè siö, kiedy nie moĔna z nich skorzystaè.

UniemoĔliwiajñ buforowanie zapytania.

Nie moĔna ich uĔyè w sytuacji, gdy wymagany jest literaä bñdĒ identyfikator, np. nazwa
tabeli lub kolumny albo w klauzuli

LIMIT

Zmienne te sñ przywiñzane do poäñczenia, a wiöc nie moĔna ich uĔyè w trakcie komuni-
kacji miödzy poäñczeniami.

JeĔeli stosowana jest pula poäñczeþ bñdĒ trwaäe poäñczenie, mogñ spowodowaè oddzia-

äywanie na siebie pozornie wyizolowanych fragmentów kodu.

W wersjach wczeĈniejszych niĔ MySQL 5.0 rozróĔniajñ wielkoĈè liter. NaleĔy wiöc zachowaè
ostroĔnoĈè i wystrzegaè siö problemów zwiñzanych ze zgodnoĈciñ kodu.

Nie moĔna jawnie zadeklarowaè rodzaju zmiennej, a punkt, w którym wybierany jest rodzaj
dla niezdefiniowanej zmiennej, jest odmienny w róĔnych wersjach MySQL. Najlepszym
wyjĈciem jest poczñtkowe przypisanie wartoĈci

zmiennym, które majñ byè uĔywane

z liczbami caäkowitymi,

0.0

dla liczb zmiennoprzecinkowych oraz

(pusty ciñg tekstowy)

dla ciñgów tekstowych. Rodzaj zmiennej ulega zmianie po przypisaniu jej wartoĈci.
Ustalanie typu zmiennej zdefiniowanej przez uĔytkownika jest w serwerze MySQL prze-
prowadzane dynamicznie.

W niektórych sytuacjach optymalizator moĔe pozbyè siö zmiennych, uniemoĔliwiajñc im
wykonanie zadaþ zaplanowanych przez programistö.

KolejnoĈè przypisania (a wröcz godzina przypisania) moĔe byè niedeterministyczna i za-
leĔeè od planu wykonania zapytania wybranego przez optymalizator. Jak czytelnik prze-
kona siö w dalszej czöĈci rozdziaäu, wyniki mogñ byè bardzo mylñce.

Operator przypisania

ma niĔsze pierwszeþstwo od wszelkich pozostaäych operatorów.

NaleĔy wiöc zachowaè szczególnñ ostroĔnoĈè i stosowaè nawiasy, jasno okreĈlajñc kolejnoĈè.

Niezdefiniowane zmienne nie powodujñ wygenerowania bäödu skäadni. Bardzo äatwo
popeäniè bäñd, nawet nie zdajñc sobie z tego sprawy.

Jednñ z najwaĔniejszych funkcji zmiennych jest fakt, Ĕe zmiennej moĔna przypisaè wartoĈè,
a nastöpnie zastosowaè wartoĈè otrzymanñ w wyniku operacji przypisania. Innymi säowy,
przypisanie jest L-wartoĈciñ. PoniĔej przedstawiono przykäad jednoczesnego obliczenia i wy-

Ĉwietlenia „liczby rekordów” dla danego zapytania:

mysql> SET @rownum := 0;
mysql> SELECT actor_id, @rownum := @rownum + 1 AS rownum
    -> FROM sakila.actor LIMIT 3;
+----------+--------+
| actor_id | rownum |
+----------+--------+
|        1 |      1 |
|        2 |      2 |
|        3 |      3 |
+----------+--------+

Zmienne zdefiniowane przez u

Ŝytkownika

_ 219

Przedstawiony przykäad zdecydowanie nie jest interesujñcy, poniewaĔ pokazuje, Ĕe powielono
klucz podstawowy tabeli. Jednak wciñĔ ma swoje zastosowanie: jednym z nich jest ranking.
W kolejnym przykäadzie zaprezentowano zapytanie zwracajñce dziesiöciu aktorów, którzy
wystñpili w najwiökszej liczbie filmów. UĔyta zostanie kolumna rankingu nadajñca aktorowi
tö samñ pozycjö, jeĈli zostaä zaangaĔowany. Trzeba rozpoczñè od zapytania wyszukujñcego
aktorów oraz liczbö filmów, w których wystñpili:

mysql> SELECT actor_id, COUNT(*) as cnt
    -> FROM sakila.film_actor
    -> GROUP BY actor_id
    -> ORDER BY cnt DESC
    -> LIMIT 10;
+----------+-----+
| actor_id | cnt |
+----------+-----+
|      107 |  42 |
|      102 |  41 |
|      198 |  40 |
|      181 |  39 |
|       23 |  37 |
|       81 |  36 |
|      106 |  35 |
|       60 |  35 |
|       13 |  35 |
|      158 |  35 |
+----------+-----+

Nastöpnie naleĔy dodaè ranking, który powinien byè taki sam dla wszystkich aktorów
wystöpujñcych w 35 filmach. W tym celu zostanñ uĔyte trzy zmienne: pierwsza Ĉledzñca bieĔñcñ
pozycjö w rankingu, druga przechowujñca poprzedniñ liczbö filmów, w których wystñpiä
aktor, oraz trzecia przechowujñca bieĔñcñ liczbö filmów, w których wystñpiä aktor. Pozycja
w rankingu zostanie zmieniona wraz ze zmianñ liczby filmów, w których zaangaĔowano danego
aktora. PoniĔej przedstawiono pierwsze podejĈcie do utworzenia takiego zapytania:

mysql> SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;
mysql> SELECT actor_id,
    ->    @curr_cnt := COUNT(*) AS cnt,
    ->    @rank     := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank) AS rank,
    ->    @prev_cnt := @curr_cnt AS dummy
    -> FROM sakila.film_actor
    -> GROUP BY actor_id
    -> ORDER BY cnt DESC
    -> LIMIT 10;
+----------+-----+------+-------+
| actor_id | cnt | rank | dummy |
+----------+-----+------+-------+
|      107 |  42 |    0 |     0 |
|      102 |  41 |    0 |     0 |
...

Ups! Zarówno ranking, jak i licznik nigdy nie przekraczajñ wartoĈci zero. Dlaczego tak siö staäo?

Nie moĔliwe jest udzielenie jednej odpowiedzi na takie pytanie. Problem moĔe byè bardzo
prosty i sprowadzaè siö do bäödnie zapisanej nazwy zmiennej (w tym przypadku jednak tak
nie jest) lub nieco bardziej skomplikowany. W omawianym przykäadzie dane wyjĈciowe po-
lecenia

EXPLAIN

pokazujñ, Ĕe uĔywana jest tabela tymczasowa oraz sortowanie pliku. Dlatego

teĔ zmienne sñ obliczane w zupeänie innym czasie, niĔ jest to oczekiwane.

Jest to ten rodzaj tajemniczego zachowania, którego moĔna czösto doĈwiadczyè w MySQL
podczas uĔywania zmiennych zdefiniowanych przez uĔytkownika. Usuwanie takich bäödów

Czytaj dalej...

220

Rozdzia

ĥ 4. Optymalizacja wydajnoļci zapytaħ

moĔe byè trudne, ale naprawdö opäacalne. Utworzenie rankingu w MySQL zwykle wymaga
algorytmu równania kwadratowego, np. zliczania róĔnych aktorów, którzy wystöpowali
w wiökszej liczbie filmów. Rozwiñzanie z uĔyciem zmiennej zdefiniowanej przez uĔytkownika
moĔe byè algorytmem liniowym — caäkiem spore usprawnienie.

W omawianym przypadku äatwym rozwiñzaniem jest dodanie do zapytania innego poziomu
tabel tymczasowych za pomocñ podzapytania w klauzuli

FROM

mysql> SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;
    -> SELECT actor_id,
    ->    @curr_cnt := cnt AS cnt,
    ->    @rank     := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank) AS rank,
    ->    @prev_cnt := @curr_cnt AS dummy
    -> FROM (
    ->    SELECT actor_id, COUNT(*) AS cnt
    ->    FROM sakila.film_actor
    ->    GROUP BY actor_id
    ->    ORDER BY cnt DESC
    ->    LIMIT 10
    -> ) as der;
+----------+-----+------+-------+
| actor_id | cnt | rank | dummy |
+----------+-----+------+-------+
|      107 |  42 |    1 |    42 |
|      102 |  41 |    2 |    41 |
|      198 |  40 |    3 |    40 |
|      181 |  39 |    4 |    39 |
|       23 |  37 |    5 |    37 |
|       81 |  36 |    6 |    36 |
|      106 |  35 |    7 |    35 |
|       60 |  35 |    7 |    35 |
|       13 |  35 |    7 |    35 |
|      158 |  35 |    7 |    35 |
+----------+-----+------+-------+

WiökszoĈè problemów dotyczñcych zmiennych zdefiniowanych przez uĔytkownika wiñĔe siö
z przypisywaniem im wartoĈci i odczytywaniem ich na róĔnych etapach zapytania. Przykäa-
dowo przewidywalnie nie bödzie dziaäaäo przypisanie zmiennej w poleceniu

SELECT

a od-

czytanie w klauzuli

WHERE

. Wydaje siö, Ĕe przedstawione poniĔej zapytanie zwróci po prostu

jeden rekord, ale to bäödne wyobraĔenie:

mysql> SET @rownum := 0;
mysql> SELECT actor_id, @rownum := @rownum + 1 AS cnt
    -> FROM sakila.actor
    -> WHERE @rownum <= 1;
+----------+------+
| actor_id | cnt  |
+----------+------+
|        1 |    1 |
|        2 |    2 |
+----------+------+

Wynika to z faktu, Ĕe polecenia

WHERE

SELECT

znajdujñ siö na róĔnych etapach procesu wy-

konywania zapytania. Stanie siö to bardziej oczywiste po dodaniu kolejnego etapu wykony-
wania zapytania przez doäñczenie klauzuli

ORDER BY

mysql> SET @rownum := 0;
mysql> SELECT actor_id, @rownum := @rownum + 1 AS cnt
    -> FROM sakila.actor
    -> WHERE @rownum <= 1
    -> ORDER BY first_name;