Index candidats à la suppression

6 juillet 2011 par stephane

Après avoir constaté dans les articles précédents que les index inutiles causent des baisses de performances non négligeables, nous allons voir dans cet article qu’il n’est pas aussi simple qu’il y paraît de déterminer si un index est utile ou non, même si dans certains cas la réponse semble évidente.

A première vue, trois catégories d’index sont bien placés pour être qualifiés d’inutiles : les index en doublon, les index redondants et les index à faible cardinalité. Regardons chaque catégorie en détail.

Index en doublon

Les index en doublon sont simplement ceux qui sont définis plusieurs fois. Un exemple simple pour commencer :
CREATE TABLE t (
  id int(11) DEFAULT NULL,
  KEY a (id),
  KEY b (id)
);

Notons que MySQL n’empêche en aucun cas ce genre de définition erronée.

Un autre exemple, un peu plus compliqué :
CREATE TABLE u (
  id int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY (id),
  KEY b (id)
);

La logique était : je crée une contrainte d’unicité sur la colonne id, puis un index pour accélérer les requêtes. Sauf qu’avec MySQL, toutes les contraintes sont vérifiées avec des index, ce qui signifie que la création de la contrainte d’unicité crée implicitement un index (idem pour PRIMARY KEY et FOREIGN KEY).

Un dernier exemple :
CREATE TABLE v (
  id int(11) NOT NULL DEFAULT '0',
  col varchar(10) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY a (col),
  FULLTEXT KEY b (col)
) ENGINE=MyISAM;

Ici, nous avons bien deux index sur col, mais ce ne sont pas des index de même type, ils ne sont donc pas en doublon. Attention aux excès de zèle !

Les index en doublon n’apportent rien, il est donc toujours utile de pouvoir les repérer pour ensuite les supprimer. Un bon outil pour ce travail est mk-duplicate-key-checker de Maatkit. L’outil est très simple d’usage et la documentation complète.

Par exemple, pour vérifier si notre table u (dans la base test) contient des index en doublon, on peut utiliser la ligne suivante :
$ mk-duplicate-key-checker h=localhost,u=root,D=test -t u
# ##################################
# test.u
# ##################################

# b is a duplicate of PRIMARY
# Key definitions:
# KEY `b` (`id`)
# PRIMARY KEY (`id`),
# Column types:
# `id` int(11) not null default '0'
# To remove this duplicate index, execute:
ALTER TABLE `test`.`u` DROP INDEX `b`;

# ##################################
# Summary of indexes
# ##################################

# Size Duplicate Indexes 0
# Total Duplicate Indexes 1
# Total Indexes 2

L’outil suggère avec raison que l’index b est en doublon de la clé primaire et propose sous la forme d’un ALTER TABLE une requête pour supprimer cet index.

La même ligne de commande, en version longue :
$ mk-duplicate-key-checker --host localhost --user root --databases test --tables t

Il est bien sûr possible d’examiner en une seule passe toutes les tables de la base test :
$ mk-duplicate-key-checker --host localhost --user root --databases test
# ##################################
# test.t
# ##################################

# a is a duplicate of b
# Key definitions:
# KEY `a` (`id`),
# KEY `b` (`id`)
# Column types:
# `id` int(11) default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`t` DROP INDEX `a`;

# ##################################
# test.u
# ##################################

# b is a duplicate of id
# Key definitions:
# KEY `b` (`id`)
# UNIQUE KEY `id` (`id`),
# Column types:
# `id` int(11) not null default '0'
# To remove this duplicate index, execute:
ALTER TABLE `test`.`u` DROP INDEX `b`;

# ##################################
# Summary of indexes
# ##################################

# Size Duplicate Indexes 0
# Total Duplicate Indexes 2
# Total Indexes 7

Parfait, mk-duplicate-key-checker a bien identifié les doublons et ne s’est pas fait piéger par l’index FULLTEXT de la table v.

Index redondants

Pour les index de type BTREE (c’est-à-dire tous les index sauf les index HASH de Memory et les index FULLTEXT de MyISAM), l’optimiseur de requêtes de MySQL est capable d’utiliser un préfixe gauche d’un index composite. Tous les index portant sur des préfixes gauche d’un index composite ne sont donc théoriquement pas utiles.

Pas clair ? Regardez la table suivante :
CREATE TABLE w (
  id int(11) NOT NULL DEFAULT '0',
  col varchar(10) DEFAULT NULL,
  col2 varchar(10) DEFAULT NULL,
  KEY id (id),
  KEY id_col (id,col)
);

Et considérez la requête suivante :
SELECT * FROM w WHERE id = 1

EXPLAIN nous indique que l’index sur id est utilisé :
mysql> EXPLAIN SELECT * FROM w WHERE id = 1\G
********* 1. row ********
   id: 1
   select_type: SIMPLE
   table: w
   type: ref
   possible_keys: id,id_col
   key: id
   key_len: 4
   ref: const
   rows: 1
   Extra:

Mais si on demande à l’optimiseur de ne pas considérer l’index sur id, on voit que la requête va s’exécuter de manière aussi efficace en utilisant l’index sur (id,col) :

mysql> EXPLAIN SELECT * FROM w IGNORE INDEX(id) WHERE id = 1\G
******** 1. row ********
   id: 1
   select_type: SIMPLE
   table: w
   type: ref
   possible_keys: id_col
   key: id_col
   key_len: 4
   ref: const
   rows: 1
   Extra:

On peut donc éliminer sans problème l’index sur id.

Faut-il donc systématiquement supprimer les index redondants ? Eh bien non, il convient d’apporter quelques nuances. Disons que la majeure partie du temps, un index redondant est inutile et peut être supprimé, mais qu’il existe des situations où il faudra conserver tous les index.

Par exemple, ce sera souvent le cas quand les colonnes indexées sont de grande taille (que ceux qui n’utilisent jamais de VARCHAR(255) lèvent la main :) ). Pour avoir un cas concret de ce type de situation, vous pouvez lire cet article.

Comment savoir si un index redondant est utile ou non ? La seule solution utilisable est bien souvent de faire des tests.

Que dit mk-duplicate-key-checker sur les index redondants ?

$ mk-duplicate-key-checker h=localhost,u=root,D=test -t w
# ##################################
# test.w
# ##################################

# id is a left-prefix of id_col
# Key definitions:
# KEY `id` (`id`),
# KEY `id_col` (`id`,`col`)
# Column types:
# `id` int(11) not null default '0'
# `col` varchar(10) default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`w` DROP INDEX `id`;

# ##################################
# Summary of indexes
# ##################################

# Size Duplicate Indexes 16
# Total Duplicate Indexes 1
# Total Indexes 2

L’outil considère toujours les index redondants comme candidats à la suppression. A vous de voir s’il vaux mieux le conserver ou non.

Index à faible cardinalité

Arnaud a déjà parlé en détail de la notion de cardinalité d’un index. En résumé, la cardinalité est le nombre de valeurs uniques de l’index. Plus elle est élevée, plus l’index sera efficace dans son filtrage.

Si la cardinalité est faible, l’index perd de son intérêt. D’ailleurs, l’optimiseur de requêtes n’utilisera pas l’index s’il estime qu’il n’est pas utile. Voici donc de bons candidats à la suppression ! Cependant là encore, il convient de nuancer.

Imaginez un site de e-commerce avec une table contenant les commandes. Cette table va contenir un champ indiquant le statut des commandes, avec par exemple les valeurs ‘archivée’ et ‘en cours’. Au bout de quelque temps, il est certain que presque toutes les lignes de la table auront le statut ‘archivée’. Cependant, si vous écrivez une requête qui porte sur les commandes ‘en cours’, un index sur la colonne statut sera sûrement utile, même si sa cardinalité sera ridicule (2 en l’occurence).

Ce qui joue ici n’est pas seulement la cardinalité de l’index, mais aussi la distribution des valeurs et surtout les requêtes que vous faites. Si avec la même table des commandes, votre voisin ne s’intéresse qu’aux commandes archivées, l’index n’a plus aucun intérêt. C’est d’ailleurs une situation que l’on rencontre fréquemment : les requêtes que l’on peut faire en mode transactionnel ou en mode décisionnel sont radicalement différentes, exigeant souvent d’avoir des installations dédiées avec une indexation différente.

Conclusion

Cet article a montré que le repérage des index inutiles est loin d’être évident. Mis à part les index en doublon qu’on peut repérer et éliminer facilement, il est nécessaire d’avoir une bonne connaissance des requêtes qui sont exécutées afin de déterminer si un index est inutile. Le prochain article va se concentrer sur des méthodes plus systématiques d’identification des index inutiles.

Mots-clefs : , , ,

Laisser une réponse