Index MySQL - quelles sont les meilleures pratiques ?

Question

J'utilise des index sur mes bases de données MySQL depuis un certain temps, mais je n'ai jamais vraiment appris à leur sujet. En général, je place un index sur tous les champs que je vais rechercher ou sélectionner à l'aide d'une balise WHERE mais parfois ça ne semble pas si noir et blanc.

Quelles sont les meilleures pratiques pour les index MySQL ?

Exemples de situations/dilemmes :

• Si une table comporte six colonnes et que toutes sont consultables, dois-je les indexer toutes ou aucune ?

• Quels sont les impacts négatifs de l'indexation sur les performances ?

• Si j'ai une colonne VARCHAR 2500 qui est consultable à partir de certaines parties de mon site, dois-je l'indexer ?

Answer 1

Vous devez absolument passer du temps à vous documenter sur l'indexation, il y a beaucoup d'écrits à ce sujet, et il est important de comprendre ce qui se passe.

De manière générale, un index impose un ordre aux lignes d'une table.

Pour simplifier, imaginons qu'un tableau soit un gros fichier CSV. Chaque fois qu'une ligne est insérée, elle est insérée à la fin . Ainsi, l'ordre "naturel" de la table est simplement l'ordre dans lequel les lignes ont été insérées.

Imaginez que vous ayez chargé ce fichier CSV dans un tableur très rudimentaire. Tout ce que fait ce tableur est d'afficher les données et de numéroter les lignes dans un ordre séquentiel.

Imaginez maintenant que vous devez trouver toutes les lignes qui ont la valeur "M" dans la troisième colonne. Compte tenu de ce dont vous disposez, vous n'avez qu'une seule option. Vous parcourez le tableau en vérifiant la valeur de la troisième colonne pour chaque ligne. Si vous avez beaucoup de lignes, cette méthode (un "balayage du tableau") peut prendre beaucoup de temps !

Imaginez maintenant qu'en plus de cette table, vous avez un index. Cet index particulier est l'index des valeurs de la troisième colonne. L'index répertorie toutes les valeurs de la troisième colonne, dans un ordre significatif (par exemple, par ordre alphabétique) et, pour chacune d'entre elles, fournit une liste des numéros de ligne où cette valeur apparaît.

Vous avez maintenant une bonne stratégie pour trouver toutes les lignes où la valeur de la troisième colonne est "M". Par exemple, vous pouvez effectuer une recherche binaire ! Alors que le balayage de la table nécessite la recherche de N lignes (où N est le nombre de lignes), la recherche binaire ne nécessite que la recherche de log-n entrées d'index, dans le pire des cas. C'est beaucoup plus facile !

Bien sûr, si vous avez cet index et que vous ajoutez des lignes à la table (à la fin, puisque c'est ainsi que fonctionne notre table conceptuelle), vous devez mettre à jour l'index à chaque fois. Vous effectuez donc un peu plus de travail lorsque vous écrivez de nouvelles lignes, mais vous gagnez un temps fou lorsque vous recherchez quelque chose.

Donc, en général, l'indexation crée un compromis entre l'efficacité de la lecture et l'efficacité de l'écriture. Sans index, les insertions peuvent être très rapides : le moteur de la base de données ajoute simplement une ligne à la table. Lorsque vous ajoutez des index, le moteur doit mettre à jour chaque index tout en effectuant l'insertion.

D'autre part, la lecture devient beaucoup plus rapide.

J'espère que cela répond à vos deux premières questions (comme d'autres l'ont fait - vous devez trouver le bon équilibre).

Votre troisième scénario est un peu plus compliqué. Si vous utilisez LIKE, les moteurs d'indexation vous aideront généralement à accélérer la lecture jusqu'au premier "%". En d'autres termes, si vous effectuez une SÉLECTION WHERE column LIKE 'foo%bar%', la base de données utilisera l'index pour trouver toutes les lignes où la colonne commence par "foo", puis devra analyser ce jeu de lignes intermédiaire pour trouver le sous-ensemble qui contient "bar". SELECT ... WHERE column LIKE '%bar%' ne peut pas utiliser l'index. J'espère que vous comprenez pourquoi.

Enfin, vous devez commencer à penser aux index sur plus d'une colonne. Le concept est le même et se comporte de la même manière que le LIKE. En gros, si vous avez un index sur (a,b,c), le moteur continuera à utiliser l'index de gauche à droite du mieux qu'il peut. Ainsi, une recherche sur la colonne a peut utiliser l'index (a,b,c), tout comme une recherche sur (a,b). Cependant, le moteur devra effectuer un balayage complet de la table si vous recherchez WHERE b=5 AND c=1).

J'espère que cela vous aidera à y voir plus clair, mais je dois répéter qu'il est préférable de passer quelques heures à chercher de bons articles qui expliquent ces choses en profondeur. Il est également conseillé de lire la documentation de votre serveur de base de données. La façon dont les indices sont implémentés et utilisés par les planificateurs de requêtes peut varier assez largement.

Answer 2

Consultez des présentations comme Plus d'informations sur la maîtrise de l'art de l'indexation .

Mise à jour 12/2012 : J'ai mis en ligne une nouvelle de mes présentations : Comment concevoir des indices, vraiment . Je l'ai présenté en octobre 2012 à la ZendCon de Santa Clara, et en décembre 2012 au Percona Live de Londres.

La conception des meilleurs index est un processus qui doit correspondre aux requêtes que vous exécutez dans votre application.

Il est difficile de recommander des règles générales concernant les meilleures colonnes à indexer, ou si vous devez indexer toutes les colonnes, aucune colonne, quels index doivent couvrir plusieurs colonnes, etc. Tout dépend des requêtes que vous devez exécuter.

Oui, il y a une certaine surcharge et vous ne devriez pas créer des index inutilement. Mais vous devrait créer les index qui profitent aux requêtes que vous devez exécuter rapidement. Les frais généraux d'un index sont généralement largement compensés par ses avantages.

Pour une colonne de type VARCHAR(2500), il est préférable d'utiliser un fichier de type Index FULLTEXT ou un indice de préfixe :

CREATE INDEX i ON SomeTable(longVarchar(100));

Notez qu'un index conventionnel ne peut pas vous aider si vous recherchez des mots qui peuvent se trouver au milieu de ce long varchar. Pour cela, utilisez un index plein texte.

Answer 3

Je ne répéterai pas certains des bons conseils donnés dans d'autres réponses, mais j'ajouterai quelque chose :

Indices composés

Vous pouvez créer des index composés - un index qui inclut plusieurs colonnes. MySQL peut les utiliser à partir de gauche à droite . Donc si vous avez :

Table A
Id
Name
Category
Age
Description

si vous avez un index composé qui inclut Nom/Catégorie/Age dans cet ordre, ces clauses WHERE utiliseront l'index :

WHERE Name='Eric' and Category='A'

WHERE Name='Eric' and Category='A' and Age > 18

mais

WHERE Category='A' and Age > 18

n'utiliserait pas cet indice car tout doit être utilisé de gauche à droite.

Expliquer

Utilisez Explain / Explain Extended pour comprendre quels indices sont disponibles pour MySQL et lequel il sélectionne réellement. MySQL utilisera uniquement ONE clé par requête .

EXPLAIN EXTENDED SELECT * from Table WHERE Something='ABC'

Journal des requêtes lentes

Allumez le journal des requêtes lentes pour voir quelles requêtes sont lentes.

Colonnes larges

Si vous avez une colonne large où la PLUS grande partie de la distinction se fait dans les quelques premiers caractères, vous pouvez utiliser seulement les N premiers caractères dans votre index. Exemple : Nous avons une colonne ReferenceNumber définie comme varchar(255) mais dans 97% des cas, le numéro de référence est de 10 caractères ou moins. J'ai modifié l'index pour qu'il ne prenne en compte que les 10 premiers caractères et j'ai amélioré les performances de manière significative.

Answer 4

Si une table a six colonnes et que toutes sont consultables, dois-je les indexer toutes ou aucune ?

Effectuez-vous des recherches champ par champ ou certaines recherches utilisent-elles plusieurs champs ? Quels champs sont le plus sur lequel la recherche est effectuée ? Quels sont les types de champs ? (L'index fonctionne mieux sur les INT que sur les VARCHAR, par exemple). Avez-vous essayé d'utiliser EXPLAIN sur les requêtes en cours d'exécution ?

Quels sont les impacts négatifs de l'indexation sur les performances ?

Les UPDATE et INSERT seront plus lents. Il y a aussi les besoins supplémentaires en espace de stockage, mais c'est généralement sans importance de nos jours.

Si j'ai une colonne VARCHAR 2500 qui est consultable à partir de certaines parties de mon site, dois-je l'indexer ?

Non, sauf s'il est UNIQUE (ce qui signifie qu'il est déjà indexé) ou si vous recherchez uniquement exact correspond à ce champ (sans utiliser LIKE ou la recherche plein texte de mySQL).

En général, je place un index sur tous les champs que je vais rechercher ou sélectionner à l'aide d'une clause WHERE.

Normalement, j'indexe les champs qui font l'objet du plus grand nombre de requêtes, puis les champs INT/BOOLEAN/ENUM plutôt que les champs VARCHARS. N'oubliez pas que vous devez souvent créer un index sur des champs combinés, plutôt qu'un index sur un champ individuel. Utilisez EXPLAIN et vérifiez le journal des lenteurs.

Answer 5

Chargement efficace des données : Les index accélèrent les récupérations mais ralentissent les insertions et les suppressions, ainsi que les mises à jour des valeurs des colonnes indexées. En d'autres termes, les index ralentissent la plupart des opérations qui impliquent une écriture. Ceci est dû au fait que l'écriture d'une ligne nécessite non seulement l'écriture de la ligne de données, mais aussi la modification des index. Plus une table possède d'index, plus les modifications à apporter sont nombreuses et plus la dégradation moyenne des performances est importante. La plupart des tables reçoivent beaucoup de lectures et peu d'écritures, mais pour une table avec un pourcentage élevé d'écritures, le coût de la mise à jour des index peut être important.

Éviter les index : Si vous n'avez pas besoin d'un index particulier pour aider les requêtes à mieux fonctionner, ne le créez pas.

Espace disque : Un index occupe de l'espace disque, et les index multiples occupent un espace proportionnellement plus important. Vous risquez donc d'atteindre plus rapidement la taille limite d'une table que si vous n'avez pas d'index. Évitez les index dans la mesure du possible.

À retenir : Ne pas surindexer