MySQL Prefix Indexes
W tym wpisie dowiesz się jakie są zalety i wady użycia prefix indeksu.
Standardowo wszystkie przykłady są z bazy Sakila. Żeby dokładnie zrozumieć o czym będziesz czytał warto zapoznać się z moim blogiem MySQL index cardinality and selectivity.
W bazie Sakila znajdziesz tabele actor, która wygląda następująco:
mysql> select * from actor limit 10;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+--------------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 |
| 3 | ED | CHASE | 2006-02-15 04:34:33 |
| 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 |
| 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
| 6 | BETTE | NICHOLSON | 2006-02-15 04:34:33 |
| 7 | GRACE | MOSTEL | 2006-02-15 04:34:33 |
| 8 | MATTHEW | JOHANSSON | 2006-02-15 04:34:33 |
| 9 | JOE | SWANK | 2006-02-15 04:34:33 |
| 10 | CHRISTIAN | GABLE | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
10 rows in set (0.00 sec)Załóżmy, że chcemy wyszukać aktora po jego nazwisku, większość z nas całkiem słusznie zauważy, że przyda się indeks na kolumnie first_name. Będzie to tak zwany covering index, który pokrywa warunki zapytania.
mysql> select * from actor where last_name like 'GUINESS';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 90 | SEAN | GUINESS | 2006-02-15 04:34:33 |
| 179 | ED | GUINESS | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
3 rows in set (0.00 sec)
mysql> explain select * from actor where first_name like 'PENELOPE';
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | actor | NULL | range | id_first_name | id_first_name | 182 | NULL | 4 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sWarto zwrócić uwage na kolumnę key_len która wynosi 182.
Czy możemy to zrobić szybciej i lepiej ?
Znając pojęcia selectivity i cardinality możemy trochę pokombinować i użyć prefix indeksu. Wiemy, że naszym warunkiem jest first_name, co się stanie jeśli zamiast używać indeksu z całych wyrazów, założymy że kilka pierwszych liter pozwoli nam w szybki i łatwy sposób osiągnać ten sam rezultat ?
Sprawdźmy to:
mysql> select count(distinct first_name)/count(*) as original,
count(distinct LEFT(first_name, 1))/count(*) as left1,
count(distinct LEFT(first_name, 2))/count(*) as left2,
count(distinct LEFT(first_name, 3))/count(*) as left3,
count(distinct LEFT(first_name, 4))/count(*) as left4 from actor;
+----------+--------+--------+--------+--------+
| original | left1 | left2 | left3 | left4 |
+----------+--------+--------+--------+--------+
| 0.6400 | 0.1150 | 0.3600 | 0.5700 | 0.5950 |
+----------+--------+--------+--------+--------+
1 row in set (0.00 sec)Zapytanie, które pokazałem powyżej sprawdza, selectivity dla kilku pierwszych liter kolumny first_name. Szukając całego słowa osiągniemy
- selectivity na poziomie 0.64
- dla pierwszej litery będzie to 0.0115
- dla pierwszych dwóch liter będzie to 0.36
- dla pierwszych trzech liter będzie to 0.57
- dla pierwszych czterech liter będzie to 0.595
0.36 to trochę za mało, ale trzy pierwsze litery to 0.57, wygląda już bardzo obiecująco. Sprawdźmy to.
mysql> alter table actor add index idx_first_name_3(first_name (3));
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ANALYZE TABLE actor;
+--------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| sakila.actor | analyze | status | OK |
+--------------+---------+----------+----------+
1 row in set (0.02 sec)
mysql> alter table actor alter index id_first_name invisible;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warning
Wykonaliśmy trzy operacje:
- stworzyliśmy prefix indeks
- odświeżyliśmy statystyki tabeli
- indeks id_first_name uczyniliśmy niewidzialnym
O invisible index można poczytać tutaj.
Jak wygląda teraz plan zapytania ?
mysql> explain select * from actor where first_name like 'PENELOPE';
+----+-------------+-------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | actor | NULL | range | idx_first_name_3 | idx_first_name_3 | 14 | NULL | 4 | 100.00 | Using where |
+----+-------------+-------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)Warto zwrócić uwagę na key_len który spadł z 182 do 14.
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 | id_last_update | 1 | last_update | A | 1 | NULL | NULL | | BTREE | | | YES | NULL |
| actor | 1 | id_last_name | 1 | last_name | A | 121 | NULL | NULL | | BTREE | | | YES | NULL |
| actor | 1 | id_first_name | 1 | first_name | A | 128 | NULL | NULL | | BTREE | | | NO | NULL |
| actor | 1 | idx_first_name_3 | 1 | first_name | A | 114 | 3 | NULL | | BTREE | | | YES | NULL |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.00 sec)Ciekawostka, SHOW INDEXES w kolumnie Sub_part pokazuje wartość 3, która odpowiada naszemu warunkow, trzech pierwszych liter z każdego słowa.
Kiedy to się sprawdza:
- wyszukiwanie tekstu
- wyszukiwanie UUID
- wyszukiwanie HAS
Wszędzie tam gdzie pierwsze kilka liter pozwala nam z dużym prawdopodobieństwem wyszukać rekordy których potrzebujemy.
Kiedy się nie sprawdza:
- użycie ORDER BY lub GROUP BY
Jak zawsze warto sprawdzać i testować na swoich danych :)
Niech SELECT będzie z wami.
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.
