MySQL Prefix Indexes

Tomasz Gintowt
5 min readJun 14, 2024

--

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 s

Warto 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.

--

--

Tomasz Gintowt

Architect, DevOps, SysOps, and DBA. Currently, I’m an IT Systems Engineer working with Apache Kafka, RabbitMQ, PostgreSQL, Elastic Stack in Real-Time Data Team.