W poprzednim poście pisałem o algorytmie Index Merge, który w pewnych, szczególnych wpadkach umożliwia skorzystanie z kilku indeksów jednocześnie. Jak wygląda wydajność tego algorytmu? Czy jest sens stosować go zamiast indeksów na wiele kolumn? Aby to sprawdzić przygotowałem środowisko testowe. Tabela ma następującą strukturę:

mysql> SHOW CREATE TABE tab1\G
*************************** 1. row ***************************
Table: tab1
Create Table: CREATE TABLE `tab1` (
`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,
PRIMARY KEY (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=2000001 DEFAULT CHARSET=latin2
1 row in set (0.00 sec)

Jak widać, jest w niej 2 miliony niewielkich rekordów. Kolumny `b`, `c`, `d` i `e` przyjmują pseudolosowo generowane wartości z zakresu 0 – 100.

Tabela w takiej postaci zajmuje: dane –  41016K, klucz główny – 20045K.

Dodałem następnie indeksy na wszystkie kolumny:

mysql> SHOW INDEXES FROM tab1\G
*************************** 1. row ***************************
Table: tab1
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: a
Collation: A
Cardinality: 2000000
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: tab1
Non_unique: 1
Key_name: idx_b
Seq_in_index: 1
Column_name: b
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: tab1
Non_unique: 1
Key_name: idx_c
Seq_in_index: 1
Column_name: c
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 4. row ***************************
Table: tab1
Non_unique: 1
Key_name: idx_d
Seq_in_index: 1
Column_name: d
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 5. row ***************************
Table: tab1
Non_unique: 1
Key_name: idx_e
Seq_in_index: 1
Column_name: e
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
5 rows in set (0.00 sec)

Indeksy zajmują już teraz 108544K – pięciokrotny wzrost zapotrzebowania na miejsce.

Dla porównania utworzyłem drugą tabelę, będącą kopią zawartości poprzedniej, tyle że ma jeszcze nałożone dodatkowe indeksy:

mysql> SHOW CREATE TABLE tab2\G
*************************** 1. row ***************************
Table: tab2
Create Table: CREATE TABLE `tab2` (
`a` int(10) NOT NULL DEFAULT '0',
`b` int(10) DEFAULT NULL,
`c` int(10) DEFAULT NULL,
`d` int(10) DEFAULT NULL,
`e` int(10) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `idx_a_b` (`a`,`b`),
KEY `idx_b_c` (`b`,`c`),
KEY `idx_b` (`b`),
KEY `idx_c` (`c`),
KEY `idx_d` (`d`),
KEY `idx_e` (`e`),
KEY `idx_b_c_d` (`b`,`c`,`d`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2
1 row in set (0.00 sec)

W tym przypadku indeksy zajmują już 213863K – dodatkowe trzy indeksy wielokolumnowe to drugie tyle indeksów.

Pierwszym zapytaniem, które sprawdzałem jest stosunkowo prosty SELECT:

SELECT * FROM tab1 WHERE b=xx AND c=yy;

Parametry xx i yy dobierane były losowo. Zapytanie wykonywane było najpierw na tabeli tab1, a potem na tabeli tab2. Plan tych zapytań wyglądał następująco:

mysql> EXPLAIN SELECT * FROM tab1 WHERE b=35 AND c=34\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1
type: index_merge
possible_keys: idx_b,idx_c
key: idx_c,idx_b
key_len: 5,5
ref: NULL
rows: 157
Extra: Using intersect(idx_c,idx_b); Using where
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM tab2 WHERE b=35 AND c=34\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab2
type: ref
possible_keys: idx_b_c
key: idx_b_c
key_len: 10
ref: const,const
rows: 208
Extra: Using where
1 row in set (0.00 sec)

Jeśli chodzi o wydajność, to liczby wyglądają następująco. W przypadku zapytania do tabeli `tab1` – 35.45 qps. Jeśli MySQL mógł skorzystać z wielokolumnowego indeksu, wydajność wzrosła do 3971.77 qps.

Drugie zapytanie wykorzystuje algorytm Union Merge. Aby porównanie miało sens, usuwam z tabeli `tab2` indeksy na kolumny `b` i `c`. Teraz plany zapytań wyglądają następująco:

mysql> EXPLAIN SELECT * FROM tab1 WHERE b=35 OR c=34\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1
type: index_merge
possible_keys: idx_b,idx_c
key: idx_b,idx_c
key_len: 5,5
ref: NULL
rows: 35444
Extra: Using union(idx_b,idx_c); Using where
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM tab2 WHERE b=35 OR c=34\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab2
type: ALL
possible_keys: idx_b_c
key: NULL
key_len: NULL
ref: NULL
rows: 2000000
Extra: Using where
1 row in set (0.00 sec)

W przypadku zastosowania OR w warunku WHERE nie ma możliwości zastosowania wielokolumnowych indeksów. Wydajność takiego zapytania wygląda następująco:

tabela `tab1` – 26.86 qps
tabela `tab2` – 12.53 qps

Jakie z powyższego można wyciągnąć wnioski? Po pierwsze, Index Merge w większości wypadków nie może zastąpić indeksów wielokolumnowych – różnica dwóch rzędów wielkości w wydajności jest różnicą ogromną. Co prawda, przykładowe tabele są dobrane bardzo tendencyjnie (mała ilość niewielkich kolumn przekłada się na niewielkie zapotrzebowanie na miejsce na dysku), ale nawet jeśli tabela byłaby większa, indeksy wielokolumnowe powinny być szybsze – sprawdzę to z resztą w przeciągu kilku następnych dni. Głównym minusem wielokolumnowych indeksów, mimo wszystko, jest jednak to, że zajmują sporo miejsca. Nie o wydajność tu mi teraz chodzi, co o to, że duży indeks to zapotrzebowanie na większą ilość pamięci i miejsca na dysku – serwery bazodanowe zazwyczaj działają na mniejszych, szybszych dyskach. Czy to SAS, czy to SSD – są to dyski małe (a jeśli duże, to drogie) – miejsce jest cenne a kolejny indeks na kilka kolumn to może być kilkaset mega lub więcej. Kwestią decyzji projektanta bazy jest to, czy opłaca się tworzyć dwukolumnowy indeks dla zapytań, które pojawiają się stosunkowo rzadko. Może się okazać, że te kilkadziesiąt milisekund na zapytanie jest do przyjęcia, bo wykonywane jest ono np. raz na pół godziny. W takiej sytuacji indeks wielokolumnowy może się okazać nieprzydatny.
Algorytm Index Merge może też być jedynym sposobem zastosowania indeksów – szczególnie jeśli chodzi o pojawienie się OR w liście warunków WHERE. Indeks wielokolumnowy w tym przypadku nie pomoże i zostaje pełen skan tabeli – dwukrotnie wolniejszy niż Index Merge. Podsumowując, możemy się cieszyć,  że tego typu feature jest dostępne. Nie jest to może rozwiązanie idealne, wydajne i w ogóle wspaniałe, ale w pewnych specyficznych sytuacjach może okazać się najlepszą z dostępnych opcji. Dobrze, że jest, czasami się przydaje.