MySQL InnoDB multicolumn indexes

TLTR; Znajdziesz tutaj moje zapiski o zabawach z indeksami wielokolumnowymi. Traktuj ten wpis jako wprowadzenie do tematu, indeksy wielokolumnowe w relacyjnych bazach danych to temat tak szeroki jak ocean.

Każdy kto na codzień pracuje z relacyjnymi bazami danych, staje przed problemem ilości indeksów, raz jest ich za mało, czasem za dużo. Z jednej strony miło mieć indeksy pokrywające warunki zapytania, z drugiej każdy indeks to narzut wydajnościowy przy INSERT/UPDATE/DELETE.

W pewnym momencie dociera do nas, że można mieć jeden indeks z kilkoma kolumnami. Idea wydaje się wspaniała i na pierwszy rzut oka pozwala na rozwiązanie sporej cześci problemów.

W tym przykładzie chciałbym pokazać wam jak w MySQL InnoDB indeksy wielokolumnowe wpływają na pokrycie warunków WHERE.

Środowisko testowe

Jako środowiska testowego użyłem dockera z MySQL 8.3.0 oraz bazy Sakila, która jest do pobrania z

https://dev.mysql.com/doc/index-other.html

docker run -v SCIEZKA_DO/sakila-db/:/tmp-db/ -e MYSQL_ROOT_PASSWORD=password -d mysql

mysql> source /tmp-db/sakila-schema.sql
mysql> source /tmp-db/sakila-data.sql

Mamy gotowe środowisko.

Usuńmy jeszcze jeden index, który będzie nam przeszkadzał w naszych ćwiczeniach oraz stwórzmy multikolumn index.

mysql> drop index idx_actor_last_name on actor;
mysql> create index idx_actor_first_and_last_name on actor (first_name,last_name);

mysql> show indexes from actor;
+-------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name                      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| actor |          0 | PRIMARY                       |            1 | actor_id    | A         |         200 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| actor |          1 | idx_actor_first_and_last_name |            1 | first_name  | A         |         128 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| actor |          1 | idx_actor_first_and_last_name |            2 | last_name   | A         |         199 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

Test NR 1

Select * from actor where last_name=’WALKEN’ and first_name=’BELA’;

Kolumna last_name będzie pierwsza, druga kolumna będzie first_name.

mysql> explain select * from actor where last_name='WALKEN' and first_name='BELA';
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys                 | key                           | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | actor | NULL       | ref  | idx_actor_first_and_last_name | idx_actor_first_and_last_name | 364     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Jak widać na wyniku, silnik bazy danych użył indeksu idx_actor_first_and_last_name o długości 364.

Test NR 2

select * from actor where first_name=’BELA’ and last_name=’WALKEN’;

To samo zapytanie, zmienilismy tylko kolejności kolumn.

mysql> explain select * from actor where first_name='BELA' and last_name='WALKEN';
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys                 | key                           | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | actor | NULL       | ref  | idx_actor_first_and_last_name | idx_actor_first_and_last_name | 364     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Róźnicy nie ma, ten sam indeks o tej samej długości.

Test NR 3

select * from actor where first_name=’BELA’ and last_name=’WALKEN’ and actor_id=196;

Do poprzedniego zapytania dodaliśmy dodatkowy WHERE w postaci actor_id.

mysql> explain select * from actor where first_name='BELA' and last_name='WALKEN' and actor_id=196;
+----+-------------+-------+------------+-------+---------------------------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys                         | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------------------------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | actor | NULL       | const | PRIMARY,idx_actor_first_and_last_name | PRIMARY | 2       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------------------------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

Jak widać na wyniku, planner pomimo WHERE z warunkami first_name i last_name użył klucza głównego.

Test NR 4

select * from actor where first_name=’BELA’;

Sprawdzmy, teraz jak zachowa się indeks multikolum jeżeli w warunku WHERE użyjemy tylko jednej kolumny.

mysql> explain select * from actor where first_name='BELA';
+----+-------------+-------+------------+------+---------------------------------------------+-------------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys                               | key                           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------------------------------+-------------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | actor | NULL       | ref  | idx_actor_first_and_last_name,id_first_name | idx_actor_first_and_last_name | 182     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------------------------------------+-------------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

Test NR5

select * from actor where last_name=’WALKEN’;

Tutaj również sprawdzamy pojednyńczą kolumnę z indeksu wielokolumnowego.

mysql> explain select * from actor where last_name='WALKEN';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  200 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

WNIOSKI

• Testy NR 1 i NR 2 wykazały, że kolejność warunków w WHERE nie ma znaczenia, silnik DB wybrał ten sam indeks i czas wykonania był jednakowy.
• Test NR 3 z dodatkowym parametrem actor_id wykazał, że planer wybierze klucz główny, w tym przypadku jest to clustered index.
• Test NR 4 pokazała, że indeks wielokolumnowy pokryje warunek WHERE jeżeli jest to pierwsza kolumna w indeksie. Dla przypomnienia jak został stworzony indeks (first_name,last_name)
• Test NR 5, indeks wielokolumnowy nie pokrywa warunku WERE jeśli kolumna po której szukamy nie jest pierwsza w indeksie. W tym teście planer wybra Full Table Scan.

Jak widać i tak źle i tak nie dobrze :) Nie ma jednego indeksu, który pokryje wszystkie nasze warunki.

Jeśli ciagle czujesz niedosyt polecam książkę

Relational Database Index Design and the Optimizers

by Tapio Lahdenmaki (Author), Mike Leach (Author)

Tomasz Gintowt jest Architektem/DevOps/DBA/Trenerem, głównie skupiony na dostarczaniu rozwiązań składowania i przetwarzania danych. Nie są mu obce wszelkiej maści bazy danych, systemy real-time data i streamingu. Obecnie pracuje z Apache Kafka, RabbitMQ, Elastic Stack ,MySQL, PostgreSQL. Organizator spotkań DataOps Poland.

https://www.linkedin.com/in/tomasz-gintowt/

https://dataops-academy.pl — kursy i szkolenia.