0

Nouveautés MySQL 8.0 : Les indexes invisibles

Posted on by Capdata team
twitterlinkedinmail

Ce troisième article aura pour sujet l’utilisation des indexes invisibles en MySQL 8.0.14

MySQL 8.0 permet d’utiliser maintenant les indexes invisibles. Un index invisible n’est pas pris en compte par l’optimizer quand il fait le plan d’exécution de la requête.

Les indexes sont visibles par défaut. L’index invisible étant toujours maintenu en arrière-plan et mis à jour à mesure que les données seront modifiées, cela permet à un DBA/Dev de tester l’effet de la suppression d’un index sur les performance d’une requête.

Même l’utilisation d’un hint FORCE INDEX dans la requête ne pourra pas marcher pour un index invisible.

Cela a également pour avantage de ne pas avoir d’effet destructeur si l’index mis en invisible était finalement nécessaire à certaines requêtes. De plus, la suppression d’un index en toute sérénité si aucun ralentissement sur la requête n’a été constaté.

Attention à ne pas confondre les indexes invisibles avec les indexes désactivés qui sont implémentés dans le moteur MyISAM et qui interrompent la maintenance de l’index.

 

Voici différents tests de création de table et d’indexes invisibles :

On va créer une table de test qui va s’appeler ‘personne ‘ avec différents indexes.

mysql> create table personne (
    -> personne_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    -> prenom char(20) NOT NULL,
    -> nom char(20) NOT NULL,
    -> ville char(20) NOT NULL,
    -> age tinyint(3) unsigned NOT NULL,
    -> maj timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> PRIMARY KEY (personne_id),
    -> INDEX idx_personne_prenom (prenom) INVISIBLE,
    -> INDEX idx_personne_nom (nom) INVISIBLE
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.83 sec)

mysq> show indexes from personne;
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| personne | 0 | PRIMARY | 1 | personne_id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| personne | 1 | idx_personne_prenom | 1 | prenom | A | 0 | NULL | NULL | | BTREE | | | NO | NULL |
| personne | 1 | idx_personne_nom | 1 | nom | A | 0 | NULL | NULL | | BTREE | | | NO | NULL |
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (1.28 sec)

Maintenant, on peut créer un index invisible directement lors de la création d’une table mais également plus tard lors d’une création d’index ou de modification de la table.

Avant de rendre des changements permanents après une opération importante sur une base de production, il serait intéressant d’observer les conséquences de ces changements.

L’utilisation d’un index invisible permet de pouvoir observer ces changements en production comme si on déplaçait notre index dans la “Corbeille“. Du coup, si nous nous étions trompé et que l’index était important et utilisé en production, nous pourrions le rendre visible à nouveau et cela est plus rapide que de recréer ou restaurer cet index via un backup.

 

mysql> create index idx_personne_ville on personne (ville) INVISIBLE;
Query OK, 0 rows affected (1.18 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table personne add index idx_personne_age (age) INVISIBLE;
Query OK, 0 rows affected (0.58 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM sys.schema_unused_indexes;
+---------------+-------------+---------------------+
| object_schema | object_name | index_name |
+---------------+-------------+---------------------+
| world | personne | idx_personne_prenom |
| world | personne | idx_personne_nom |
| world | personne | idx_personne_ville |
| world | personne | idx_personne_age |
+---------------+-------------+---------------------+
4 rows in set, 3 warnings (0.01 sec)

On peut voir les indexes non utilisés avec la table schema_unused_indexes du schéma sys.

Pour autant, ces indexes non utilisés peuvent être liés à un reboot du serveur qui est arrivé récemment et soient donc importants pour de futur traitements !

 

mysql> show indexes from personne;
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| personne | 0 | PRIMARY | 1 | personne_id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| personne | 1 | idx_personne_prenom | 1 | prenom | A | 0 | NULL | NULL | | BTREE | | | NO | NULL |
| personne | 1 | idx_personne_nom | 1 | nom | A | 0 | NULL | NULL | | BTREE | | | NO | NULL |
| personne | 1 | idx_personne_ville | 1 | ville | A | 0 | NULL | NULL | | BTREE | | | NO | NULL |
| personne | 1 | idx_personne_age | 1 | age | A | 0 | NULL | NULL | | BTREE | | | NO | NULL |
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.08 sec)

mysql> select table_schema, table_name, index_name, column_name, cardinality, index_type, is_visible from information_schema.statistics where table_schema = 'world' and table_name = 'personne';
+--------------+------------+---------------------+-------------+-------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY | INDEX_TYPE | IS_VISIBLE |
+--------------+------------+---------------------+-------------+-------------+------------+------------+
| world | personne | idx_personne_age | age | 0 | BTREE | NO |
| world | personne | idx_personne_nom | nom | 0 | BTREE | NO |
| world | personne | idx_personne_prenom | prenom | 0 | BTREE | NO |
| world | personne | idx_personne_ville | ville | 0 | BTREE | NO |
| world | personne | PRIMARY | personne_id | 0 | BTREE | YES |
+--------------+------------+---------------------+-------------+-------------+------------+------------+
5 rows in set (0.01 sec)

On peut voir également si un index est visible ou non grâce à show indexes ou avec la table statistics de information_schema.

Quand on créée un nouvel index sur une table, cela peut changer/modifier le plan d’exécution de plusieurs requêtes liées à cette table. Cette modification du plan d’exécution de plusieurs requêtes peut avoir des conséquences indésirables sur le coût et le temps d’exécution.

On peut donc déployer des indexes étapes par étapes, c’est à dire en en les créant invisibles puis en les passant visibles à un moment de faible charge sur le serveur de base de données afin d’observer les changements liés à cet index sans trop impacter la production.

On modifie un index invisible afin de le rendre visible :

mysql> alter table personne alter index idx_personne_age visible;
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show indexes from personne;
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| personne | 0 | PRIMARY | 1 | personne_id | A | 10 | NULL | NULL | | BTREE | | | YES | NULL |
| personne | 1 | idx_personne_prenom | 1 | prenom | A | 10 | NULL | NULL | | BTREE | | | NO | NULL |
| personne | 1 | idx_personne_nom | 1 | nom | A | 10 | NULL | NULL | | BTREE | | | NO | NULL |
| personne | 1 | idx_personne_ville | 1 | ville | A | 10 | NULL | NULL | YES | BTREE | | | NO | NULL |
| personne | 1 | idx_personne_age | 1 | age | A | 6 | NULL | NULL | | BTREE | | | YES | NULL |
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.00 sec)

mysql> select table_schema, table_name, index_name, column_name, cardinality, index_type, is_visible from information_schema.statistics where table_schema = 'world' and table_name = 'personne';
+--------------+------------+---------------------+-------------+-------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY | INDEX_TYPE | IS_VISIBLE |
+--------------+------------+---------------------+-------------+-------------+------------+------------+
| world | personne | idx_personne_age | age | 0 | BTREE | YES |
| world | personne | idx_personne_nom | nom | 0 | BTREE | NO |
| world | personne | idx_personne_prenom | prenom | 0 | BTREE | NO |
| world | personne | idx_personne_ville | ville | 0 | BTREE | NO |
| world | personne | PRIMARY | personne_id | 0 | BTREE | YES |
+--------------+------------+---------------------+-------------+-------------+------------+------------+
5 rows in set (0.00 sec)

Un index peut donc être rendu visible et invisible assez facilement afin de tester l’effet de création ou suppression d’un index sur les performances d’une requête.

J’ai inséré 10 lignes dans la table world.personne afin de faire différents tests sur l’utilisation ou non d’index dans les plans d’exécutions.

On teste l’utilisation ou non d’index sur des critères filtrants en regardant le plan d’exécution :

mysql> explain select prenom, nom, ville from world.personne where age=27;
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | personne | NULL | ref | idx_personne_age | idx_personne_age | 1 | const | 2 | 100.00 | NULL |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select prenom, nom, ville from world.personne where age=36;
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | personne | NULL | ref | idx_personne_age | idx_personne_age | 1 | const | 3 | 100.00 | NULL |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

On rend l’index idx_personne_age invisible.

mysql> alter table world.personne alter index idx_personne_age invisible;
Query OK, 0 rows affected (3.81 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select prenom, nom, ville from world.personne where age=27;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | personne | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 16.67 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select prenom, nom, ville from world.personne where age=36;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | personne | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 16.67 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

On constate bien que le plan d’exécution n’utilise plus l’index et va récupérer toutes les 10 lignes de la table personne avec un faible pourcentage de filtrage.

On remet l’index visible.

mysql> alter table world.personne alter index idx_personne_age visible;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select prenom, nom, age from world.personne where ville in ('Treillieres','La Haye Fouassiere','Saint Sebastien','Bougenay','Nantes','Orvault','Carquefou','Thouaré sur Loire');
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | personne | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 50.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select prenom, nom, age from world.personne where ville in ('Treillieres','La Haye Fouassiere','Saint Sebastien','Bougenay');
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | personne | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 40.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

On rend l’index idx_personne_ville visible.

mysql> alter table world.personne alter index idx_personne_ville visible;
Query OK, 0 rows affected (0.35 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select prenom, nom, age from world.personne where ville in ('Treillieres','La Haye Fouassiere','Saint Sebastien','Bougenay','Nantes','Orvault','Carquefou','Thouaré sur Loire');
+----+-------------+----------+------------+------+--------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | personne | NULL | ALL | idx_personne_ville | NULL | NULL | NULL | 10 | 80.00 | Using where |
+----+-------------+----------+------------+------+--------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select prenom, nom, age from world.personne where ville in ('Treillieres','La Haye Fouassiere','Saint Sebastien','Bougenay');
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | personne | NULL | range | idx_personne_ville | idx_personne_ville | 81 | NULL | 4 | 100.00 | Using index condition |
+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

On constate bien que le plan d’exécution utilise maintenant l’index mais va récupérer quand même toutes les 10 lignes de la table personne malgré un fort pourcentage de filtrage, et cela à cause du nombre important de filtres (8) sur les 10 villes présentes.

Pour au temps, quand nous faisons un filtrage sur seulement 4 valeurs des 10 villes de la table personne, le plan d’exécution utilise bien l’index en condition, ne renvoyant que les 4 lignes et avec 100% de filtrage.

mysql> show indexes from world.personne;
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| personne | 0 | PRIMARY | 1 | personne_id | A | 10 | NULL | NULL | | BTREE | | | YES | NULL |
| personne | 1 | idx_personne_prenom | 1 | prenom | A | 10 | NULL | NULL | | BTREE | | | NO | NULL |
| personne | 1 | idx_personne_nom | 1 | nom | A | 10 | NULL | NULL | | BTREE | | | NO | NULL |
| personne | 1 | idx_personne_ville | 1 | ville | A | 10 | NULL | NULL | YES | BTREE | | | YES | NULL |
| personne | 1 | idx_personne_age | 1 | age | A | 6 | NULL | NULL | | BTREE | | | YES | NULL |
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.00 sec)

Voici donc les indexes visibles ou non.

Attention par contre aux tables possédant un seul index primaire ou sur une colonne NOT NULL car on ne pourra pas le rendre invisible.

mysql> create table ville (
    -> nom char(20) not null,
    -> code_postal mediumint(8) unsigned not null,
    -> UNIQUE INDEX idx_ville_cp (code_postal)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.30 sec)

mysql> desc ville;
+-------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------+------+-----+---------+-------+
| nom | char(20) | NO | | NULL | |
| code_postal | mediumint(8) unsigned | NO | PRI | NULL | |
+-------------+-----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show indexes from ville;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| ville | 0 | idx_ville_cp | 1 | code_postal | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.03 sec)

mysql> alter table ville alter index idx_ville_cp invisible;
ERROR 3522 (HY000): A primary key index cannot be invisible

Cependant, on peut le rendre invisible s’il existe un deuxième indexes de type clée primaire sur la table en question.

mysql> alter table ville add primary key (nom);
Query OK, 0 rows affected (0.83 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show indexes from ville;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| ville | 0 | PRIMARY | 1 | nom | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| ville | 0 | idx_ville_cp | 1 | code_postal | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.02 sec)

mysql> alter table ville alter index idx_ville_cp invisible;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show indexes from ville;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| ville | 0 | PRIMARY | 1 | nom | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| ville | 0 | idx_ville_cp | 1 | code_postal | A | 0 | NULL | NULL | | BTREE | | | NO | NULL |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

On peut donc voir l’utilité ou non d’un index visible ou invisible pour certaines requêtes via son plan d’exécution !

A vous maintenant de faire ces différents tests ou non sur l’utilisation d’index invisible/visible en recette, preprod ou production, durant une faible charge et n’impactant pas des traitements critiques.

A bientôt pour de nouveaux articles sur MySQL !

Erwan Ollitrault

Continuez votre lecture sur le blog :

twitterlinkedinmail

Capdata team

More Posts

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.