W poprzednich postach pisałem o algorytmie Index Merge i wykonałem krótki test jego wydajności. Tym razem przyglądniemy się jak wygląda wydajność tego algorytmu gdy danych jest dużo. Tabela, do jakiej będziemy wykonywać zapytania ma następującą strukturę:

mysql> SHOW CREATE TABLE tab1_big\G
*************************** 1. row ***************************
Table: tab1_big
Create Table: CREATE TABLE `tab1_big` (
`a` int(10) NOT NULL AUTO_INCREMENT,
`b` int(10) DEFAULT NULL,
`c` int(10) DEFAULT NULL,
`d` int(10) DEFAULT NULL,
`e` int(10) DEFAULT NULL,
`x` varchar(255) DEFAULT NULL,
`y` varchar(255) DEFAULT NULL,
`z` varchar(255) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `idx_b` (`b`),
KEY `idx_c` (`c`),
KEY `idx_d` (`d`),
KEY `idx_e` (`e`),
KEY `idx_x` (`x`),
KEY `idx_y` (`y`),
KEY `idx_z` (`z`),
KEY `idx_b_x_y_z` (`b`,`x`,`y`,`z`),
KEY `idx_b_c` (`b`,`c`)
) ENGINE=MyISAM AUTO_INCREMENT=20094993 DEFAULT CHARSET=latin2
1 row in set (0.00 sec)


Ponad 20 milionów rekordów, łącznie jest to 15022M danych i 1850M indeksów. Wykonywane będą dwa rodzaje zapytań o następujących planach:
Pierwsze zapytanie:

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM tab1_big WHERE b=24 AND c=67\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1_big
type: ref
possible_keys: idx_b,idx_c,idx_b_x_y_z,idx_b_c
key: idx_b_c
key_len: 10
ref: const,const
rows: 1587
Extra: Using where
1 row in set (0.00 sec)

Jak widać, zapytanie to korzysta z dwukolumnowego indeksu na kolumny `b` i `c`. Drugie zapytanie to:

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM tab1_big WHERE b=24 AND d=67\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1_big
type: index_merge
possible_keys: idx_b,idx_d,idx_b_x_y_z,idx_b_c
key: idx_d,idx_b
key_len: 5,5
ref: NULL
rows: 557
Extra: Using intersect(idx_d,idx_b); Using where
1 row in set (0.00 sec)

Tu z kolei wykorzystywany jest algorytm Index Merge – szukana jest część wspólna indeksów na kolumny `d` i `b`.

Uzyskałem następujące wyniki. W przypadku drugiego zapytania średni czas wykonywania to 236 sekund – prawie dwie minuty. W przypadku zastosowania indeksu na dwie kolumny, średni czas wykonania SELECTa spadł do 16 sekund. Różnica, choć spora, nie jest już tak duża, jak w przypadku mniejszej tabeli. Wtedy różnica była stukrotna, teraz dwudziestokrotna. Powodem jest po prostu duża ilość danych, która nie mieści się już w cache dyskowym (o buforze na indeksy nie wspominając) – w efekcie szybkość dwukolumnowego indeksu jest częściowo niwelowana przez wolny dostęp do dysku.

Przy okazji tego krótkiego benchmarku nasunęła mi się taka uwaga. Tabela wielkości 17GB to nie jest coś normalnego. Co prawda, zdarza mi się takie rzeczy widzieć w zastosowaniu produkcyjnym, ale to jest złe. Operacje na takiej tabeli trwają po prostu wieki – REPAIR, ANALYZE, OPTIMIZE, dodawanie i usuwanie indeksów (ostatni, dodawany przeze mnie indeks zakładał się ponad godzinę). MySQL udostępnia sporo mechanizmów, które znakomicie mogą rozwiązać problem przechowywania takiej ilości danych (partycjonowanie, silnik MERGE) – nie koniecznie w jednej tabeli. Trzeba z nich korzystać żeby ewentualny pad serwera i uszkodzenie tabeli nie wiązało się z kilkugodzinnym przestojem.