Méthodes de suppression des index inutiles

5 septembre 2011 par stephane

Les vacances étant terminées, nous allons boucler notre tour de vue des index inutiles en voyant quels outils vont nous aider à découvrir les index qui peuvent être supprimés. Le dernier article présentait en effet des indications qui fonctionnent généralement bien mais qui ont l’inconvénient de demander beaucoup de travail manuel et de laisser de côté tout un pan d’index qui peuvent être inutiles : ceux qui ne sont pas en doublon ni redondants, qui n’ont pas une cardinalité faible mais qui ne sont tout simplement pas utilisés par l’application.

Idée générale

Si vous avez bien lu l’article précédent, vous avez probablement remarqué que la principale difficulté est qu’il n’existe quasiment jamais de règle absolue permettant de savoir à coup sûr qu’un index est inutile (exception notable : les index en doublon repérés par mk-duplicate-key-checker et qui peuvent être supprimés dans 99% des cas sans problème). Finalement, la seule méthode qui semble marcher est la suivante :
- Récupérer les requêtes exécutées sur le serveur
- Regarder le plan d’exécution de ces requêtes pour voir les index utilisés
- Comparer avec le schéma des tables pour en déduire les index non utilisés
- Supprimer les index repérés
La difficulté se situe surtout dans la 2ème étape (construire la liste des index utilisés à partir du plan d’exécution). Heureusement il existe au moins 2 manières, en fonction de votre version de MySQL, pour réussir cette étape.

Index_statistics

Les heureux utilisateurs de MariaDB ou Percona Server ont la chance d’avoir userstats v2, un patch exceptionnel développé à l’origine par Google. Ce patch ajoute un nombre incalculable de statistiques sur les utilisateurs, les tables mais aussi, ce qui nous intéresse ici, les index. Pour activer la fonctionnalité, il suffit de changer une variable :
mysql> SET GLOBAL userstat_running = 1;

A partir de ce moment, la table INDEX_STATISTICS de la base INFORMATION_SCHEMA va se remplir. Les colonnes sont très simples à comprendre : TABLE_SCHEMA, TABLE_NAME et INDEX_NAME localisent l’index et ROWS_READ donne le nombre de lignes lues dans l’index. Evidemment, pour obtenir des statistiques significatives et fiables, il faut attendre suffisamment longtemps avant de consulter le contenu de la table. Une bonne question est de savoir ce que signifie « suffisamment longtemps » : disons qu’il faut que chaque requête potentielle de l’application ait pu être exécutée, en pensant bien aux requêtes rares telles que les requêtes faites dans des cronjobs par exemple.

Un petit exemple sur des données réelles ?

mysql> SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS WHERE TABLE_NAME='main';
+--------------+------------+------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+--------------+------------+------------+-----------+
| biz          | main       | user_id    | 231751890 |
| biz          | main       | created    | 4456658   |
| biz          | main       | PRIMARY    | 1748023896 |
| biz          | main       | modified_idx    | 266636148 |
| biz          | main       | mobile_idx    | 550 |
+--------------+------------+---------------+------------+

et voyons en parallèle le schéma de la table :

mysql> SHOW CREATE TABLE main\G
********* 1. row *********
Table: main
Create Table: CREATE TABLE main (
  ...
  PRIMARY KEY (main_id),
  KEY user_id (user_id),
  KEY created (created),
  KEY language_idx (language),
  KEY mobile_idx (mobile,deleted,web,published),
  KEY country_mobile_idx (country,mobile),
  KEY modified_idx (modified)
) ENGINE=InnoDB;

Qu’en déduit-on ? En premier lieu, que les index language_idx et country_mobile_idx ne sont jamais utilisés, ils ne servent donc à rien et peuvent être supprimés. Ensuite, en comparant les chiffres, on voit que le serveur a lu 1,7 milliards de lignes dans la clé primaire, 550 dans l’index mobile_idx : aucun doute mobile_idx ne sert à rien lui non plus !

Et voilà, de manière très simple, nous avons trouvé des index qui peuvent être supprimés.
On peut même automatiser la découverte des index non utilisée avec une petite requête qui va bien.

Quelles sont les limitations de cette méthode ? Très simple, autant les index qui ne sont jamais utilisés sont visibles immédiatement, autant on peut se poser la question de la pertinence de certains index. Et malheureusement, les chiffres donnés par la table INDEX_STATISTICS ne sont pas suffisants. Exemple : l’index created affiche un nombre de lignes lues environ 500 fois plus faibles que la clé primaire : cela signifie-t-il qu’il n’est pas très utile ou qu’au contraire il est très utile mais seulement sur certaines requêtes ?

mk-index-usage

Quand on ne dispose de userstats, la seule solution consiste à collecter toutes les requêtes pendant une période suffisamment longue, par exemple en mettant long_query_time à 0 pour que toutes les requêtes aboutissent dans le slow query log, et à se tourner vers Maatkit en espérant y trouver notre bonheur. Ca tombe bien, mk-index-usage est justement un outil lisant un fichier de log et délivrant de nombreuses informations intéressantes (en déterminant en particulier le plan d’exécution des requêtes).

Par défaut, mk-index-usage prend en entrée un fichier de log au format slow query log et affiche une liste de requêtes SQL pour supprimer les index inutiles :

$ mk-index-usage slow.log
slow.log: 5% 08:28 remain
slow.log: 11% 07:20 remain
slow.log: 14% 08:36 remain
...
slow.log: 93% 00:37 remain
slow.log: 97% 00:12 remain

ALTER TABLE `main`.`adfit` DROP KEY `status`; — type:non-unique

ALTER TABLE `main`.`biz` DROP KEY `created`, DROP KEY `modified_idx`; — type:non-unique

Surprise ! Le script a bien identifié modified_idx comme inutile, mais pas mobile_idx. Et il a décidé que created pouvait être supprimé alors que nous avons vu que ce n'était pas forcément évident.

Comme d'habitude avec Maatkit, si vous êtes curieux et que vous lisez la documentation, vous verrez qu'il existe une multitude d'options permettant d'obtenir bien plus d'informations sur l'usage des index sur votre plate-forme. De plus, cette méthode est compatible avec toutes les versions de MySQL

Conclusion

Les deux méthodes présentées ici peuvent vous faire gagner beaucoup de temps en vous aidant à identifier les index candidats à la suppression. Bien entendu, comme souvent, il ne serait pas très prudent de faire confiance aveuglément à un script pour gérer votre plate-forme : votre travail consistera à vérifier s'il faut effectivement enlever les index qui sont susceptibles d'être supprimés. Normalement, ce type d'index ne devrait représenter qu'une petite portion des index de votre application, le gain de temps sera donc appréciable.

Mots-clefs : , , , ,

Un commentaire sur “Méthodes de suppression des index inutiles”

  1. Arnaud dit :

    Salut Stéphane,

    merci pour cette excellente série sur les index !

Laisser une réponse