{"id":7325,"date":"2019-05-15T13:16:24","date_gmt":"2019-05-15T12:16:24","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=7325"},"modified":"2019-09-13T10:42:56","modified_gmt":"2019-09-13T09:42:56","slug":"nouveautes-mysql-8-0-les-indexes-invisibles","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/","title":{"rendered":"Nouveaut\u00e9s MySQL 8.0 : Les indexes invisibles"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

Ce troisi\u00e8me article aura pour sujet l’utilisation des indexes invisibles en MySQL 8.0.14<\/h3>\n

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\u00e9cution de la requ\u00eate.<\/p>\n

Les indexes sont visibles par d\u00e9faut. L’index invisible \u00e9tant toujours maintenu en arri\u00e8re-plan et mis \u00e0 jour \u00e0 mesure que les donn\u00e9es seront modifi\u00e9es, cela permet \u00e0 un DBA\/Dev de tester l’effet de la suppression d’un index sur les performance d’une requ\u00eate.<\/p>\n

M\u00eame l’utilisation d’un hint FORCE INDEX dans la requ\u00eate ne pourra pas marcher pour un index invisible.<\/p>\n

Cela a \u00e9galement pour avantage de ne pas avoir d’effet destructeur si l’index mis en invisible \u00e9tait finalement n\u00e9cessaire \u00e0 certaines requ\u00eates. De plus, la suppression d’un index en toute s\u00e9r\u00e9nit\u00e9 si aucun ralentissement sur la requ\u00eate n’a \u00e9t\u00e9 constat\u00e9.<\/p>\n

Attention \u00e0 ne pas confondre les indexes invisibles avec les indexes d\u00e9sactiv\u00e9s qui sont impl\u00e9ment\u00e9s dans le moteur MyISAM et qui interrompent la maintenance de l’index.<\/p>\n

 <\/p>\n

Voici diff\u00e9rents tests de cr\u00e9ation de table et d’indexes invisibles :<\/strong><\/p>\n

On va cr\u00e9er une table de test qui va s’appeler ‘personne <\/em>‘ avec diff\u00e9rents indexes.<\/p>\n

mysql> create table personne (\r\n    -> personne_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT,\r\n    -> prenom char(20) NOT NULL,\r\n    -> nom char(20) NOT NULL,\r\n    -> ville char(20) NOT NULL,\r\n    -> age tinyint(3) unsigned NOT NULL,\r\n    -> maj timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\r\n    -> PRIMARY KEY (personne_id),\r\n    -> INDEX idx_personne_prenom (prenom) INVISIBLE,\r\n    -> INDEX idx_personne_nom (nom) INVISIBLE\r\n    -> ) ENGINE=InnoDB;\r\nQuery OK, 0 rows affected (0.83 sec)\r\n\r\nmysq> show indexes from personne;\r\n+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |\r\n+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n| personne | 0 | PRIMARY | 1 | personne_id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |\r\n| personne | 1 | idx_personne_prenom | 1 | prenom | A | 0 | NULL | NULL | | BTREE | | | NO | NULL |\r\n| personne | 1 | idx_personne_nom | 1 | nom | A | 0 | NULL | NULL | | BTREE | | | NO | NULL |\r\n+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n3 rows in set (1.28 sec)<\/pre>\n
Maintenant, on peut cr\u00e9er un index invisible directement lors de la cr\u00e9ation d’une table mais \u00e9galement plus tard lors d’une cr\u00e9ation d’index ou de modification de la table.<\/p>\n
Avant de rendre des changements permanents apr\u00e8s une op\u00e9ration importante sur une base de production, il serait int\u00e9ressant d’observer les cons\u00e9quences de ces changements.<\/p>\n
L’utilisation d’un index invisible permet de pouvoir observer ces changements en production comme si on d\u00e9pla\u00e7ait notre index dans la “Corbeille<\/em>“. Du coup, si nous nous \u00e9tions tromp\u00e9 et que l’index \u00e9tait important et utilis\u00e9 en production, nous pourrions le rendre visible \u00e0 nouveau et cela est plus rapide que de recr\u00e9er ou restaurer cet index via un backup.<\/p>\n
 <\/p>\n
mysql> create index idx_personne_ville on personne (ville) INVISIBLE;\r\nQuery OK, 0 rows affected (1.18 sec)\r\nRecords: 0 Duplicates: 0 Warnings: 0\r\n\r\nmysql> alter table personne add index idx_personne_age (age) INVISIBLE;\r\nQuery OK, 0 rows affected (0.58 sec)\r\nRecords: 0 Duplicates: 0 Warnings: 0\r\n\r\nmysql> SELECT * FROM sys.schema_unused_indexes;\r\n+---------------+-------------+---------------------+\r\n| object_schema | object_name | index_name |\r\n+---------------+-------------+---------------------+\r\n| world | personne | idx_personne_prenom |\r\n| world | personne | idx_personne_nom |\r\n| world | personne | idx_personne_ville |\r\n| world | personne | idx_personne_age |\r\n+---------------+-------------+---------------------+\r\n4 rows in set, 3 warnings (0.01 sec)<\/pre>\n
On peut voir les indexes non utilis\u00e9s avec la table schema_unused_indexes du sch\u00e9ma sys.<\/p>\n
Pour autant, ces indexes non utilis\u00e9s peuvent \u00eatre li\u00e9s \u00e0 un reboot du serveur qui est arriv\u00e9 r\u00e9cemment et soient donc importants pour de futur traitements !<\/p>\n
 <\/p>\n
mysql> show indexes from personne;\r\n+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |\r\n+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n| personne | 0 | PRIMARY | 1 | personne_id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |\r\n| personne | 1 | idx_personne_prenom | 1 | prenom | A | 0 | NULL | NULL | | BTREE | | | NO | NULL |\r\n| personne | 1 | idx_personne_nom | 1 | nom | A | 0 | NULL | NULL | | BTREE | | | NO | NULL |\r\n| personne | 1 | idx_personne_ville | 1 | ville | A | 0 | NULL | NULL | | BTREE | | | NO | NULL |\r\n| personne | 1 | idx_personne_age | 1 | age | A | 0 | NULL | NULL | | BTREE | | | NO | NULL |\r\n+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n5 rows in set (0.08 sec)\r\n\r\nmysql> 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';\r\n+--------------+------------+---------------------+-------------+-------------+------------+------------+\r\n| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY | INDEX_TYPE | IS_VISIBLE |\r\n+--------------+------------+---------------------+-------------+-------------+------------+------------+\r\n| world | personne | idx_personne_age | age | 0 | BTREE | NO |\r\n| world | personne | idx_personne_nom | nom | 0 | BTREE | NO |\r\n| world | personne | idx_personne_prenom | prenom | 0 | BTREE | NO |\r\n| world | personne | idx_personne_ville | ville | 0 | BTREE | NO |\r\n| world | personne | PRIMARY | personne_id | 0 | BTREE | YES |\r\n+--------------+------------+---------------------+-------------+-------------+------------+------------+\r\n5 rows in set (0.01 sec)<\/pre>\n
On peut voir \u00e9galement si un index est visible ou non gr\u00e2ce \u00e0 show indexes ou avec la table statistics de information_schema.<\/p>\n
Quand on cr\u00e9\u00e9e un nouvel index sur une table, cela peut changer\/modifier le plan d’ex\u00e9cution de plusieurs requ\u00eates li\u00e9es \u00e0 cette table. Cette modification du plan d’ex\u00e9cution de plusieurs requ\u00eates peut avoir des cons\u00e9quences ind\u00e9sirables sur le co\u00fbt et le temps d’ex\u00e9cution.<\/p>\n
On peut donc d\u00e9ployer des indexes \u00e9tapes par \u00e9tapes, c’est \u00e0 dire en en les cr\u00e9ant invisibles puis en les passant visibles \u00e0 un moment de faible charge sur le serveur de base de donn\u00e9es afin d’observer les changements li\u00e9s \u00e0 cet index sans trop impacter la production.<\/p>\n
On modifie un index invisible afin de le rendre visible :<\/p>\n
mysql> alter table personne alter index idx_personne_age visible;\r\nQuery OK, 0 rows affected (0.26 sec)\r\nRecords: 0 Duplicates: 0 Warnings: 0\r\n\r\nmysql> show indexes from personne;\r\n+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |\r\n+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n| personne | 0 | PRIMARY | 1 | personne_id | A | 10 | NULL | NULL | | BTREE | | | YES | NULL |\r\n| personne | 1 | idx_personne_prenom | 1 | prenom | A | 10 | NULL | NULL | | BTREE | | | NO | NULL |\r\n| personne | 1 | idx_personne_nom | 1 | nom | A | 10 | NULL | NULL | | BTREE | | | NO | NULL |\r\n| personne | 1 | idx_personne_ville | 1 | ville | A | 10 | NULL | NULL | YES | BTREE | | | NO | NULL |\r\n| personne | 1 | idx_personne_age | 1 | age | A | 6 | NULL | NULL | | BTREE | | | YES | NULL |\r\n+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n5 rows in set (0.00 sec)\r\n\r\nmysql> 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';\r\n+--------------+------------+---------------------+-------------+-------------+------------+------------+\r\n| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY | INDEX_TYPE | IS_VISIBLE |\r\n+--------------+------------+---------------------+-------------+-------------+------------+------------+\r\n| world | personne | idx_personne_age | age | 0 | BTREE | YES |\r\n| world | personne | idx_personne_nom | nom | 0 | BTREE | NO |\r\n| world | personne | idx_personne_prenom | prenom | 0 | BTREE | NO |\r\n| world | personne | idx_personne_ville | ville | 0 | BTREE | NO |\r\n| world | personne | PRIMARY | personne_id | 0 | BTREE | YES |\r\n+--------------+------------+---------------------+-------------+-------------+------------+------------+\r\n5 rows in set (0.00 sec)<\/pre>\n
Un index peut donc \u00eatre rendu visible et invisible assez facilement afin de tester l’effet de cr\u00e9ation ou suppression d’un index sur les performances d’une requ\u00eate.<\/p>\n
J’ai ins\u00e9r\u00e9 10 lignes dans la table world.personne afin de faire diff\u00e9rents tests sur l’utilisation ou non d’index dans les plans d’ex\u00e9cutions.<\/p>\n
On teste l’utilisation ou non d’index sur des crit\u00e8res filtrants en regardant le plan d’ex\u00e9cution :<\/p>\n
mysql> explain select prenom, nom, ville from world.personne where age=27;\r\n+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+\r\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\r\n+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+\r\n| 1 | SIMPLE | personne | NULL | ref | idx_personne_age | idx_personne_age | 1 | const | 2 | 100.00 | NULL |\r\n+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+\r\n1 row in set, 1 warning (0.00 sec)\r\n\r\nmysql> explain select prenom, nom, ville from world.personne where age=36;\r\n+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+\r\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\r\n+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+\r\n| 1 | SIMPLE | personne | NULL | ref | idx_personne_age | idx_personne_age | 1 | const | 3 | 100.00 | NULL |\r\n+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+\r\n1 row in set, 1 warning (0.00 sec)<\/pre>\n
On rend l’index idx_personne_age invisible.<\/p>\n
mysql> alter table world.personne alter index idx_personne_age invisible;\r\nQuery OK, 0 rows affected (3.81 sec)\r\nRecords: 0  Duplicates: 0  Warnings: 0\r\n\r\nmysql> explain select prenom, nom, ville from world.personne where age=27;\r\n+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+\r\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\r\n+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+\r\n| 1 | SIMPLE | personne | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 16.67 | Using where |\r\n+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+\r\n1 row in set, 1 warning (0.00 sec)\r\n\r\nmysql> explain select prenom, nom, ville from world.personne where age=36;\r\n+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+\r\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\r\n+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+\r\n| 1 | SIMPLE | personne | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 16.67 | Using where |\r\n+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+\r\n1 row in set, 1 warning (0.00 sec)<\/pre>\n
On constate bien que le plan d’ex\u00e9cution n’utilise plus l’index et va r\u00e9cup\u00e9rer toutes les 10 lignes de la table personne avec un faible pourcentage de filtrage.<\/p>\n
On remet l’index visible.<\/p>\n
mysql> alter table world.personne alter index idx_personne_age visible;\r\nQuery OK, 0 rows affected (0.25 sec)\r\nRecords: 0 Duplicates: 0 Warnings: 0\r\nmysql> explain select prenom, nom, age from world.personne where ville in ('Treillieres','La Haye Fouassiere','Saint Sebastien','Bougenay','Nantes','Orvault','Carquefou','Thouar\u00e9 sur Loire');\r\n+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+\r\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\r\n+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+\r\n| 1 | SIMPLE | personne | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 50.00 | Using where |\r\n+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+\r\n1 row in set, 1 warning (0.00 sec)\r\n\r\nmysql> explain select prenom, nom, age from world.personne where ville in ('Treillieres','La Haye Fouassiere','Saint Sebastien','Bougenay');\r\n+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+\r\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\r\n+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+\r\n| 1 | SIMPLE | personne | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 40.00 | Using where |\r\n+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+\r\n1 row in set, 1 warning (0.00 sec)<\/pre>\n
On rend l’index idx_personne_ville visible.<\/p>\n
mysql> alter table world.personne alter index idx_personne_ville visible;\r\nQuery OK, 0 rows affected (0.35 sec)\r\nRecords: 0 Duplicates: 0 Warnings: 0\r\nmysql> explain select prenom, nom, age from world.personne where ville in ('Treillieres','La Haye Fouassiere','Saint Sebastien','Bougenay','Nantes','Orvault','Carquefou','Thouar\u00e9 sur Loire');\r\n+----+-------------+----------+------------+------+--------------------+------+---------+------+------+----------+-------------+\r\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\r\n+----+-------------+----------+------------+------+--------------------+------+---------+------+------+----------+-------------+\r\n| 1 | SIMPLE | personne | NULL | ALL | idx_personne_ville | NULL | NULL | NULL | 10 | 80.00 | Using where |\r\n+----+-------------+----------+------------+------+--------------------+------+---------+------+------+----------+-------------+\r\n1 row in set, 1 warning (0.00 sec)\r\n\r\nmysql> explain select prenom, nom, age from world.personne where ville in ('Treillieres','La Haye Fouassiere','Saint Sebastien','Bougenay');\r\n+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+\r\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\r\n+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+\r\n| 1 | SIMPLE | personne | NULL | range | idx_personne_ville | idx_personne_ville | 81 | NULL | 4 | 100.00 | Using index condition |\r\n+----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+\r\n1 row in set, 1 warning (0.00 sec)<\/pre>\n
On constate bien que le plan d’ex\u00e9cution utilise maintenant l’index mais va r\u00e9cup\u00e9rer quand m\u00eame toutes les 10 lignes de la table personne malgr\u00e9 un fort pourcentage de filtrage, et cela \u00e0 cause du nombre important de filtres (8) sur les 10 villes pr\u00e9sentes.<\/p>\n
Pour au temps, quand nous faisons un filtrage sur seulement 4 valeurs des 10 villes de la table personne, le plan d’ex\u00e9cution utilise bien l’index en condition, ne renvoyant que les 4 lignes et avec 100% de filtrage.<\/p>\n
mysql> show indexes from world.personne;\r\n+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |\r\n+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n| personne | 0 | PRIMARY | 1 | personne_id | A | 10 | NULL | NULL | | BTREE | | | YES | NULL |\r\n| personne | 1 | idx_personne_prenom | 1 | prenom | A | 10 | NULL | NULL | | BTREE | | | NO | NULL |\r\n| personne | 1 | idx_personne_nom | 1 | nom | A | 10 | NULL | NULL | | BTREE | | | NO | NULL |\r\n| personne | 1 | idx_personne_ville | 1 | ville | A | 10 | NULL | NULL | YES | BTREE | | | YES | NULL |\r\n| personne | 1 | idx_personne_age | 1 | age | A | 6 | NULL | NULL | | BTREE | | | YES | NULL |\r\n+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n5 rows in set (0.00 sec)<\/pre>\n
Voici donc les indexes visibles ou non.<\/p>\n
Attention par contre aux tables poss\u00e9dant un seul index primaire ou sur une colonne NOT NULL car on ne pourra pas le rendre invisible.<\/p>\n
mysql> create table ville (\r\n    -> nom char(20) not null,\r\n    -> code_postal mediumint(8) unsigned not null,\r\n    -> UNIQUE INDEX idx_ville_cp (code_postal)\r\n    -> ) ENGINE=InnoDB;\r\nQuery OK, 0 rows affected (0.30 sec)\r\n\r\nmysql> desc ville;\r\n+-------------+-----------------------+------+-----+---------+-------+\r\n| Field | Type | Null | Key | Default | Extra |\r\n+-------------+-----------------------+------+-----+---------+-------+\r\n| nom | char(20) | NO | | NULL | |\r\n| code_postal | mediumint(8) unsigned | NO | PRI | NULL | |\r\n+-------------+-----------------------+------+-----+---------+-------+\r\n2 rows in set (0.00 sec)\r\n\r\nmysql> show indexes from ville;\r\n+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |\r\n+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n| ville | 0 | idx_ville_cp | 1 | code_postal | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |\r\n+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n1 row in set (0.03 sec)\r\n\r\nmysql> alter table ville alter index idx_ville_cp invisible;\r\nERROR 3522 (HY000): A primary key index cannot be invisible<\/pre>\n
Cependant, on peut le rendre invisible s’il existe un deuxi\u00e8me indexes de type cl\u00e9e primaire sur la table en question.<\/p>\n
mysql> alter table ville add primary key (nom);\r\nQuery OK, 0 rows affected (0.83 sec)\r\nRecords: 0 Duplicates: 0 Warnings: 0\r\n\r\nmysql> show indexes from ville;\r\n+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |\r\n+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n| ville | 0 | PRIMARY | 1 | nom | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |\r\n| ville | 0 | idx_ville_cp | 1 | code_postal | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |\r\n+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n2 rows in set (0.02 sec)\r\n\r\nmysql> alter table ville alter index idx_ville_cp invisible;\r\nQuery OK, 0 rows affected (0.09 sec)\r\nRecords: 0 Duplicates: 0 Warnings: 0\r\n\r\nmysql> show indexes from ville;\r\n+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |\r\n+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n| ville | 0 | PRIMARY | 1 | nom | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |\r\n| ville | 0 | idx_ville_cp | 1 | code_postal | A | 0 | NULL | NULL | | BTREE | | | NO | NULL |\r\n+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+\r\n2 rows in set (0.00 sec)<\/pre>\n
On peut donc voir l’utilit\u00e9 ou non d’un index visible ou invisible pour certaines requ\u00eates via son plan d’ex\u00e9cution !<\/p>\n
A vous maintenant de faire ces diff\u00e9rents 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.<\/p>\n
A bient\u00f4t pour de nouveaux articles sur MySQL !<\/p>\n
Erwan Ollitrault<\/p>\n\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>","protected":false},"excerpt":{"rendered":"
Ce troisi\u00e8me 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\u00e9cution de la requ\u00eate.… Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":7800,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[222,312,310,308],"class_list":["post-7325","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","tag-index","tag-indexes-invisibles","tag-mysql-8-0","tag-nouveautes"],"yoast_head":"\nNouveaut\u00e9s MySQL 8.0 : Les indexes invisibles - Capdata TECH BLOG<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Nouveaut\u00e9s MySQL 8.0 : Les indexes invisibles - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"Ce troisi\u00e8me 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\u00e9cution de la requ\u00eate.… Continuer la lecture →\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2019-05-15T12:16:24+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-09-13T09:42:56+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/05\/indx_inv.png\" \/>\n\t<meta property=\"og:image:width\" content=\"648\" \/>\n\t<meta property=\"og:image:height\" content=\"405\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Capdata team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Capdata team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"15 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/\"},\"author\":{\"name\":\"Capdata team\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9\"},\"headline\":\"Nouveaut\u00e9s MySQL 8.0 : Les indexes invisibles\",\"datePublished\":\"2019-05-15T12:16:24+00:00\",\"dateModified\":\"2019-09-13T09:42:56+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/\"},\"wordCount\":2923,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"keywords\":[\"index\",\"indexes invisibles\",\"mysql 8.0\",\"nouveautes\"],\"articleSection\":[\"MySQL\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/\",\"name\":\"Nouveaut\u00e9s MySQL 8.0 : Les indexes invisibles - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2019-05-15T12:16:24+00:00\",\"dateModified\":\"2019-09-13T09:42:56+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Nouveaut\u00e9s MySQL 8.0 : Les indexes invisibles\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.capdata.fr\/#website\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"name\":\"Capdata TECH BLOG\",\"description\":\"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting\",\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.capdata.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blog.capdata.fr\/#organization\",\"name\":\"Capdata TECH BLOG\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"contentUrl\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"width\":800,\"height\":254,\"caption\":\"Capdata TECH BLOG\"},\"image\":{\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9\",\"name\":\"Capdata team\",\"sameAs\":[\"https:\/\/www.capdata.fr\"],\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Nouveaut\u00e9s MySQL 8.0 : Les indexes invisibles - Capdata TECH BLOG","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/","og_locale":"fr_FR","og_type":"article","og_title":"Nouveaut\u00e9s MySQL 8.0 : Les indexes invisibles - Capdata TECH BLOG","og_description":"Ce troisi\u00e8me 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\u00e9cution de la requ\u00eate.… Continuer la lecture →","og_url":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2019-05-15T12:16:24+00:00","article_modified_time":"2019-09-13T09:42:56+00:00","og_image":[{"width":648,"height":405,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/05\/indx_inv.png","type":"image\/png"}],"author":"Capdata team","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"Capdata team","Dur\u00e9e de lecture estim\u00e9e":"15 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/"},"author":{"name":"Capdata team","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9"},"headline":"Nouveaut\u00e9s MySQL 8.0 : Les indexes invisibles","datePublished":"2019-05-15T12:16:24+00:00","dateModified":"2019-09-13T09:42:56+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/"},"wordCount":2923,"commentCount":0,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"keywords":["index","indexes invisibles","mysql 8.0","nouveautes"],"articleSection":["MySQL"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/","url":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/","name":"Nouveaut\u00e9s MySQL 8.0 : Les indexes invisibles - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2019-05-15T12:16:24+00:00","dateModified":"2019-09-13T09:42:56+00:00","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-les-indexes-invisibles\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"Nouveaut\u00e9s MySQL 8.0 : Les indexes invisibles"}]},{"@type":"WebSite","@id":"https:\/\/blog.capdata.fr\/#website","url":"https:\/\/blog.capdata.fr\/","name":"Capdata TECH BLOG","description":"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting","publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.capdata.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/blog.capdata.fr\/#organization","name":"Capdata TECH BLOG","url":"https:\/\/blog.capdata.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/","url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","contentUrl":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","width":800,"height":254,"caption":"Capdata TECH BLOG"},"image":{"@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/"]},{"@type":"Person","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9","name":"Capdata team","sameAs":["https:\/\/www.capdata.fr"],"url":"https:\/\/blog.capdata.fr\/index.php\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/7325","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=7325"}],"version-history":[{"count":19,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/7325\/revisions"}],"predecessor-version":[{"id":7543,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/7325\/revisions\/7543"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/7800"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=7325"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=7325"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=7325"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}