MySQL index cardinality and selectivity

4 min readJun 10, 2024

Wyjaśnijmy sobie dwa pojęcia, którymi dziś bedziemy się zajmować:

cardinality — moc zbioru matematycznego

selectivity — selektywność, wybiórczość

Jeśli często pracujecie z bazami danych, warto te dwa pojęcia powtarzać jak mantrę. Kiedy poznacie ich zastosowanie w praktyce, będzie mogli tworzyć lepsze indeksy, a tym samym wasze zapytania będą dużo szybsze.

Wszystkie przykłady są z bazy Sakila.

Przyjrzyjmy się tabeli ACTOR.

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.01 sec)

To co zwraca naszą uwagę to jest kolumna last_update w której wszystkie rekordy mają jednakową wartość.

Zobaczmy jaki plan zostanie wybrany przez silnik bazy danych dla zapytania z warunkiem WHERE dla dwóch kolumn: first_name oraz last_update.



mysql> explain select * from actor where first_name = 'ADAM' and last_update = '2006-02-15 04:34:33'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: id_last_update,id_first_name
          key: id_first_name
      key_len: 182
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR:

Planner miał do wyboru dwa indeksy id_last_update,id_first_name wybrał indeks id_first_name. Dlaczego tak się stało ?

mysql> select count(distinct first_name) from actor;
+----------------------------+
| count(distinct first_name) |
+----------------------------+
|                        128 |
+----------------------------+
1 row in set (0.01 sec)

mysql> select count(distinct last_update) from actor;
+-----------------------------+
| count(distinct last_update) |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

W kolumnie first_name mamy 128 unikalny wartości, natomiast w last_update tylko jedną. Planner bardzo sprytnie wybrał indeks id_first_name ponieważ jest w nim więcej nikalnych wartości, tym samym łatwiej jest mu trafić w szukaną wartość. Mówiąc bardzo dosłownie z całego zbioru danych łatwiej jest mu wyszukać te unikalne wartości, których potrzebuje.

Wracajac do naszych pojęć z początku tego bloga, to jest właśnie cardinality — ilość unikalnych wartości w danych zbiorze. W kolumnie jest 200 wszystkich rekordów, z czego 128 to nikalne wartości.

Selectivity to ilość unikalnych wartości do wszystkich rekordów w danym zbiorze. Dla kolumny last_update selectivity jest bardzo niska, ponieważ jest tam jedna wielokrotnie powtarzająca się wartość.

Możemy policzyć to tak:

mysql> select count(distinct last_update)/count(*) from actor;
+--------------------------------------+
| count(distinct last_update)/count(*) |
+--------------------------------------+
|                               0.0050 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select count(distinct first_name)/count(*) from actor;
+-------------------------------------+
| count(distinct first_name)/count(*) |
+-------------------------------------+
|                              0.6400 |
+-------------------------------------+
1 row in set (0.00 sec)

Dla porówniania idealnym indeksem z najwyższa wartością selectivity zawsze powinien być indeks główny.

mysql> select count(distinct actor_id)/count(*) from actor;
+-----------------------------------+
| count(distinct actor_id)/count(*) |
+-----------------------------------+
|                            1.0000 |
+-----------------------------------+
1 row in set (0.00 sec)

W naszym przypadku będzie to kolumna actor_id, selectivity osiągnie wartość 1. Czytaj wszystkie wartości w tej kolumnie są uniklane, nie ma potwórzeń. Dla 200 rekordów, mamy 200 różnych rekordów, zero powtórzeń.

Wartośc cardinality możemy również sprawdzić wykonując polecenie SHOW INDEXES:

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      |         |               | YES     | NULL       |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)

Popatrz na kolumnę Cardinality która podaje bardzo podbne wyniki, które my uzyskaliśmy chwilę wcześniej. Wartości z SHOW INDEXES są generowane na podstawie statystyk i mogą się zmieniać po wykonaniu komendy ANALYZE TABLE .

Wnioski

Znając pojęcia cardinality oraz selectivity już wiesz, że nie ma większego sensu zakładać indeksu na kolumnie gdzie duża większość wartości jest powtarzalna. Natomiast, świetnie sprawdzają się indeksy na kolumnach gdzie selectivity osiąga wartość powyżej 0.5 ( Tak przynajmniej wynika z mojego doświadczenia).

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.

MySQL index cardinality and selectivity

Wnioski

Written by Tomasz Gintowt