Jak na razie, ruch na blogu jest na tyle niski, że jest możliwość przeanalizowania tego, o co się pytają poszczególni goście, którzy trafili na tę stronę za pomocą Google. Dziś odpowiedź, mam nadzieję że przydatna, na jedno z takich pytań. Gdyby ktoś potrzebował informacji na jakiś temat, zapraszam bezpośrednio do mnie, do zakładki Kontakt. Chętnie się dowiem, na jakiego typu posty jest zapotrzebowanie.
Co pewien czas w wyniku zapytania EXPLAIN, w kolumnie Extra pojawia się wpis: „Using filesort”. Co to dokładnie znaczy?
Nazwa filesort jest, mówiąc delikatnie, niefortunna. W praktyce, filesort nie ma nic wspólnego z sortowaniem na dysku. Jest to po prostu sortowanie wyników, które może odbywać się z wykorzystaniem dysku, ale wcale nie musi. MySQL, jeśli tylko jest w stanie, podczas sortowania wyników zapytania stara się wykorzystać indeksy. Jeśli nie jest w stanie skorzystać z żadnego – stosowany jest właśnie filesort. Filesort to w praktyce dwa różne algorytmy, oparte na algorytmie quicksort. Starszy, dwuprzebiegowy z nich ma mniejsze zapotrzebowanie na pamięć, przez co większa ilość rekordów mieści się w buforze sortowania, generuje natomiast znacznie większe obciążenie dysku losowymi operacjami odczytu i zapisu. Nowszy, dostępny od MySQL 4.1, ma większe zapotrzebowanie na pamięć, natomiast nie wymaga takiej ilości operacji dyskowych. Administrator MySQL ma pewne możliwości sterowania tym, jaki algorytm ma być stosowany. Jeśli wielkość zestawu danych do sortowania przekracza wartość zmiennej konfiguracyjnej max_length_for_sort_data, stosowany jest filesort dwuprzebiegowy, tak więc zwiększając wartość tej zmiennej zwiększamy preferencję stosowania nowszego, jednoprzebiegowego algorytmu.
Jeśli zestaw danych do sortowania jest większy niż wartość zmiennej sort_buffer_size, dane do sortowania dzielone są na mniejsze części, a następnie łączone na dysku. Nie jest to jednak powodem (powtarzam, nie jest to powodem!) aby zwiększać wartość tej zmiennej. Akurat ta zmienna jest doskonałym przykładem na to, że więcej nie znaczy lepiej. Są przypadki, w których zwiększenie buforu zwiększa wydajność. Są też przypadki, kiedy dzieje się zupełnie na odwrót, a w których najlepszym rozwiązaniem jest zmniejszenie bufora. Wiąże się to po części ze sposobem alokacji pamięci przez bibliotekę libc – przy pewnej wielkości (pewien czas temu było to 256KB) zmienia się funkcja służąca do alokacji pamięci z malloc() na mmap(). Każdej zmianie konfiguracji musi towarzyszyć dokładne przetestowanie szybkości wykonywania zapytań, tak aby mieć pewność, że faktycznie zmiany przyniosły oczekiwany efekt.
Ok, co zrobić, żeby przyspieszyć działanie zapytania, które wykorzystuje obecnie filesort? Załóżmy taką strukturę tabeli:
*************************** 1. row ***************************
Table: actor_tmp
Create Table: CREATE TABLE `actor_tmp` (
`actor_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`actor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=530402 DEFAULT CHARSET=utf8
1 row in set (0,00 sec)
i następujące zapytanie:
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
| 1 | SIMPLE | actor_tmp | ALL | NULL | NULL | NULL | NULL | 530278 | Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
1 row in set (0,00 sec)
Pisałem wyżej, że filesort jest stosowany jeśli MySQL nie jest w stanie posortować dane przy pomocy indeksu. Dodamy więc indeks, który będzie mógł zostac wykorzystany i sprawdźmy czy faktycznie coś to zmieniło:
Query OK, 0 rows affected (4,08 sec)
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM actor_tmp ORDER BY first_name, last_name LIMIT 100;
+----+-------------+-----------+-------+---------------+--------------------------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+--------------------------+---------+------+------+-------+
| 1 | SIMPLE | actor_tmp | index | NULL | idx_first_name_last_name | 274 | NULL | 100 | |
+----+-------------+-----------+-------+---------------+--------------------------+---------+------+------+-------+
1 row in set (0,00 sec)
Jak widać, nowo założony indeks jest wykorzystywany. Co ważne, wyniki sortujemy po dwóch kolumnach – `first_name` i `last_name`, dlatego też indeks obejmuje obie te kolumny. Wydajność? Po wprowadzeniu powyższej optymalizacji czas wykonywania tego zapytania spadł z ok. 0,7 sekundy do mniej niż 0,01 sekundy.
Czasami może się zdarzyć, że optimizer MySQL nie będzie chętny do wykorzystania możliwości, jakie daje mu indeks. W pewnych sytuacjach ma rację i pełen skan tabeli, który jest sekwencyjnym odczytem z dysku faktycznie będzie szybszy. W tej, nie koniecznie. Poniżej plan zapytania zaproponowany przez optimizer. Widać, że nie korzystamy z żadnego indeksu:
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
| 1 | SIMPLE | actor_tmp | ALL | NULL | NULL | NULL | NULL | 530278 | Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
1 row in set (0,00 sec)
mysql> SELECT SQL_NO_CACHE * FROM actor_tmp ORDER BY first_name, last_name LIMIT 4000,1;
+----------+------------+------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+------------+---------------------+
| 196152 | 1014397715 | 1582554623 | 2010-04-09 12:07:46 |
+----------+------------+------------+---------------------+
1 row in set (1,45 sec)
Jeśli jesteśmy pewni, że optimizer się myli, możemy go zmusić do użycia indeksu:
+----+-------------+-----------+-------+---------------+--------------------------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+--------------------------+---------+------+------+-------+
| 1 | SIMPLE | actor_tmp | index | NULL | idx_first_name_last_name | 274 | NULL | 4001 | |
+----+-------------+-----------+-------+---------------+--------------------------+---------+------+------+-------+
1 row in set (0,00 sec)
mysql> SELECT SQL_NO_CACHE * FROM actor_tmp FORCE INDEX(idx_first_name_last_name) ORDER BY first_name, last_name LIMIT 4000,1;
+----------+------------+------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+------------+---------------------+
| 196152 | 1014397715 | 1582554623 | 2010-04-09 12:07:46 |
+----------+------------+------------+---------------------+
1 row in set (0,09 sec)
Wykorzystałem do tego strukturę FORCE INDEX(nazwa_indeksu), która wymusza na optimizerze użycie wskazanego indeksu (o ile jest to oczywiście wykonalne w danej sytuacji). W efekcie zapytanie przyspieszyło z 1,45 sekundy do 0,09 sekundy.
Nie zawsze, szczególnie w przypadku bardziej skomplikowanych zapytań jesteśmy w stanie uniknąć sortowania wyników przy pomocy filesortu, jeśli jednak da się użyć odpowiedni indeks, to warto to przemyśleć.
Komentarze