Ostatnio miałem chwilę aby zerknąć na najnowszą MariaDB – wersja 5.3.2 dokładnie. To, co mnie zainteresowało, to kwestia optymalizacji SELECTów, które wykorzystują podzapytania. W gałęzi 5.3 twórcy MariaDB wprowadzili do optimizera kilka opcjonalnych modyfikacji, które pozwalają w pewnych przypadkach na znaczne przyspieszenie działania tego typu SELECTów. Jak to wygląda w praktyce?
Zgodnie z dokumentacją, do tego celu wykorzystali SEMI JOIN – jest to rodzaj JOIN’a dwóch tabel. Obie są połączone jakąś kolumną, tak jak w tradycyjnym JOINie. Jeśli w jednej tabeli, w łączącej kolumnie występuje wartość, która występuje także w łączącej kolumnie w drugiej tabeli, to zwracany jest cały rekord z pierwszej tabeli, zawierający tą wartość. Trochę to zagmatwane, ładniejszy opis znajduje się na Wikipedii.
Ok, jak to w praktyce działa? Testy wykonałem na bazie World.
W przypadku MariaDB zapytanie wygląda następująco:
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: Country
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 254
filtered: 100.00
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: <subquery2>
type: eq_ref
possible_keys: distinct_key
key: distinct_key
key_len: 3
ref: func
rows: 1
filtered: 100.00
Extra:
*************************** 3. row ***************************
id: 2
select_type: SUBQUERY
table: City
type: ALL
possible_keys: CountryCode
key: NULL
key_len: NULL
ref: NULL
rows: 4278
filtered: 100.00
Extra: Using where
3 rows in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select sql_no_cache `world`.`Country`.`IndepYear` AS `IndepYear` from `world`.`Country` semi join (`world`.`City`) where ((`world`.`City`.`Population` > ((5 * 1000) * 1000)))
1 row in set (0.00 sec)
W przypadku Percona Server 5.5 EXPLAIN jest następujący:
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: Country
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 262
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: City
type: index_subquery
possible_keys: CountryCode
key: CountryCode
key_len: 3
ref: func
rows: 1
filtered: 100.00
Extra: Using where
2 rows in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select sql_no_cache `world`.`Country`.`IndepYear` AS `IndepYear` from `world`.`Country` where <in_optimizer>(`world`.`Country`.`Code`,<exists>(<index_lookup>(<cache>(`world`.`Country`.`Code`) in City on CountryCode where ((`world`.`City`.`Population` > <cache>(((5 * 1000) * 1000))) and (<cache>(`world`.`Country`.`Code`) = `world`.`City`.`CountryCode`)))))
1 row in set (0.00 sec)
Na pierwszy rzut oka w drugim przypadku ilość rekordów do sprawdzenia jest mniejsza. Jak to się jednak wygląda jeśli chodzi o wydajność? Wykonałem w pętli 10000 takich zapytań:
Percona Server:
real 0m27.591s
user 0m0.779s
sys 0m0.206s
MariaDB:
real 0m16.760s
user 0m0.770s
sys 0m0.362s
Jak widać, różnica jest spora – ponad 30% szybciej wykonały się zapytania w przypadku MariaDB. W slowlogach również widać różnicę:
Percona Server:
# Thread_id: 67272 Schema: world Last_errno: 0 Killed: 0
# Query_time: 0.002619 Lock_time: 0.000028 Rows_sent: 18 Rows_examined: 239 Rows_affected: 0 Rows_read: 18
# Bytes_sent: 249 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# InnoDB_trx_id: 2A00FA
# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0
# InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000
# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000
# InnoDB_pages_distinct: 30
SET timestamp=1320311088;
select sql_no_cache IndepYear from Country where Country.Code IN (SELECT CountryCode from City where Population > 5*1000*1000);
MariaDB:
# Thread_id: 46 Schema: world QC_hit: No
# Query_time: 0.001541 Lock_time: 0.000040 Rows_sent: 18 Rows_examined: 4336
SET timestamp=1320311192;
select sql_no_cache IndepYear from Country where Country.Code IN (SELECT CountryCode from City where Population > 5*1000*1000);
Widać, że czas w slowlogu przekłada się dość dokładnie na czas w jakim wykonywany jest cały zestaw zapytań.
Pozostaje jeszcze jedna sprawa – w przypadku MariaDB EXPLAIN wskazuje na możliwość zastosowania indeksu na kolumnę Population w tabeli City. Dodałem ten indeks na obie testowane bazy. Jakie są tego efekty?
Percona Server:
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: Country
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 262
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: City
type: index_subquery
possible_keys: CountryCode,idx_Population
key: CountryCode
key_len: 3
ref: func
rows: 7
filtered: 100.00
Extra: Using where
2 rows in set, 1 warning (0.00 sec)
real 0m28.208s
user 0m0.755s
sys 0m0.228s
Tu bez większych zmian, indeks dodał pewnien niewielki narzut.
MariaDB:
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <subquery2>
type: ALL
possible_keys: distinct_key
key: NULL
key_len: NULL
ref: NULL
rows: 24
filtered: 100.00
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: Country
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: world.City.CountryCode
rows: 1
filtered: 100.00
Extra:
*************************** 3. row ***************************
id: 2
select_type: SUBQUERY
table: City
type: range
possible_keys: CountryCode,idx_Population
key: idx_Population
key_len: 4
ref: NULL
rows: 24
filtered: 100.00
Extra: Using where
3 rows in set, 1 warning (0.00 sec)
real 0m3.523s
user 0m0.710s
sys 0m0.281s
W przypadku MariaDB indeks faktycznie został wykorzystany i przyspieszenie jest znaczne – z 16,7 sekundy do 3,5. Percona Server, dla przypomnienia, to 27,6 sekundy.
Oczywiście, to nie jest rozwiązanie na wszystkie przypadłości podzapytań. Przykładowo, w tym momencie dodanie jakiegoś innego warunku do WHERE uniemożliwia zastosowanie tej optymalizacji. Zapytanie takie jak:
już będzie wykonywane “tradycyjnie”. MariaDB w wersji 5.3 to także nie jest jeszcze wersja stabilna – na ten moment zakwalifikowana jest jako beta, co w rozumieniu twórców oznacza, że mogą występować błędy w przypadku nowo dodanych funkcjonalności. Czyli na przykład w przypadku tego typu modyfikacji optymizera. Dlatego z resztą w domyślnej konfiguracji optymalizacje te są wyłączone – trzeba uruchomić je ręcznie. Warto jednak pamiętać o tej funkcjonalności.
O problemach z podzapytaniami w liście IN() pisałem już wcześniej na tym blogu. Podawałem tam sposoby na przepisanie tych zapytań tak, aby działały optymalnie w przypadku standardowego MySQL (testy wykonywałem na Percona Server, ale dokładnie to samo tyczy się oczywiście standardowej dystrybucji MySQL). Tak więc problem jest do rozwiązania, ale wymaga modyfikacji zapytań. Administrator baz danych pracujący w środowisku takim jak ja, czyli hostingu, często takiej możliwości nie ma. MariaDB udostępni mu dodatkowe opcje – jeśli przepisanie zapytania nie jest możliwe, może uda się uzyskać odpowiednią wydajność przy pomocy zmiany bazy danych z MySQL na MariaDB. Zawsze lepiej mieć więcej możliwości wyjścia z danej sytuacji. Trzymam więc kciuki za rozwój MariaDB, liczę też po cichu że gdy MariaDB 5.3 stanie się wersją stabilną, Percona (a może i Oracle) wykorzysta te modyfikacje we własnych produktach.
Komentarze