Comment utiliser l'optimisation de l'index MySQL

Habituellement, les premières pages de chaque livre sont une table des matières et les dernières pages auront un index de mots clés.

Pour la base de données, la table système (telle que : sysobjects, etc.) est le répertoire, et l'index sur le champ marqué est comme l'index de mots-clés à la fin du livre.

Dans la base de données, la différence entre le répertoire (dictionnaire de données) et l'index : le répertoire est vertical et l'index est horizontal.

1. Facteurs affectant la fonction d'index

Discrimination (taux de récupération)

L'optimiseur génère un plan d'exécution basé sur des informations statistiques Si la base de données ne collecte pas d'informations statistiques d'index, L'optimiseur n'a aucun moyen de démarrer et ne peut exécuter la requête que étape par étape via une analyse complète de la table. Par conséquent, l'index nouvellement créé doit réexécuter les statistiques, sinon l'index sera invalide.

Par exemple, il y a une table TABLE1, dans laquelle se trouve un champ COL1 avec trois valeurs : "1", "2" et "3". Le résultat de l'exécution des statistiques est d'informer la base de données. les champs dans les données du TABLEAU1. La proportion de différentes valeurs de COL1. La représentation est la suivante :

« 1 » - 12%

« 2 » - 66%

« 3 » - 22 % ;

Supposons qu'il y ait une valeur de champ COL2 et que le pourcentage de données soit le suivant :

« A » - 50 %

« B » - 50 % ;

Puis interrogez l'instruction 1 :

sélectionnez * dans TABLE1 où COL1 = "1" et COL2 = "A",

L'optimiseur de base de données donnera la priorité à la sélection du champ COL1 L'index est utilisé pour localiser les données dans le tableau, car l'ensemble de résultats peut être rapidement localisé dans une petite plage de 12 % grâce à l'index sur COL1. Au contraire, pour l'instruction de requête 2 :

select * from TABLE1 où COL1 = "2" et COL2 = "A",

la base de données donnera la priorité à l'index sur COL2, car pour l'instruction L'index sur la condition de requête COL2 de 2 a une meilleure discrimination.

Comme le montre ce qui précède, l'optimiseur de base de données donne généralement la priorité aux index avec une discrimination plus élevée (pour les conditions de requête, l'index sélectionné peut être différent pour différentes conditions).

Les données de la base de données changent, de sorte que les informations statistiques collectées à un certain moment peuvent devenir obsolètes après un certain temps, ou même induire en erreur l'optimiseur de base de données, ce qui entraînera également de faibles performances de fonctionnement. Ainsi, en plus de la nécessité d'exécuter des statistiques lors de la création initiale de l'index, des statistiques doivent également être exécutées lorsque les données de la table changent. Expérience : lorsque la quantité de données dans le tableau change de 10 %, les statistiques doivent être réexécutées.

2. Degré d'agrégation

Analyse de plage

Taille de la table :

Petite table

Table moyenne et grande

Très grande table

Type d'entreprise

OLTP et OLAP

Fonction et index

Fonction, comme instruction. . .

Substring(col_name,1, 3) vs. Substring(col_name, 3, 3)

like 'QQQ% vs. like '%QQQ'

Index surcharge

Outil de performance

Épée à double tranchant

L'impact des index sur les opérations d'insertion (Oracle)

L'impact des index sur les opérations d'insertion (MySQL)

Comparez l'impact des index et des activateurs sur les performances

Résumé de l'index

Utilisez des index pour obtenir un accès efficace aux données critiques. Mais vous devez être conscient que chaque index entraînera une surcharge supplémentaire pour la mise à jour de la base de données. Cela signifie que des index inefficaces peuvent entraîner un désastre pour la base de données.

Pour les bases de données, nous devons nous concentrer sur la lecture des données critiques et leur fournir le chemin d'accès le plus efficace. La stratégie de base pour cela consiste à créer des index. Bien que l'index fournisse un accès efficace, il entraîne également une surcharge système supplémentaire. La surcharge est divisée en surcharge d’espace disque et surcharge de processeur. Nous discutons ensuite de la surcharge du processeur. Chaque fois qu'un enregistrement est inséré ou supprimé d'une table, tous les index de la table doivent être ajustés en conséquence. Cet ajustement se produit également chaque fois qu'une mise à jour est effectuée sur un champ indexé. Par exemple, si l'insertion de données dans une table non indexée prend 100 unités de temps, chaque index supplémentaire ajoutera 100 à 250 unités de temps. Il est intéressant de noter que les frais généraux liés à la maintenance de l’index sont à peu près équivalents aux frais généraux d’un simple déclencheur.

Présentation de certaines des informations les plus populaires en première ligne de l'indexation. Ces informations proviennent de developWorks. Ces informations sont répertoriées car je pense qu'elles méritent généralement d'être consultées :

1. Lorsque la requête se termine dans un délai raisonnable, vous devez éviter d'ajouter des index, car ceux-ci peuvent ralentir les opérations de mise à jour et consommer de l'espace supplémentaire. Parfois, il peut y avoir des index volumineux couvrant plusieurs requêtes.

1. Les colonnes avec une cardinalité plus grande sont très adaptées à l'indexation.

3. Compte tenu de la surcharge de gestion, évitez d'utiliser plus de 5 colonnes dans l'index.

4. Pour les index multi-colonnes, placez les colonnes les plus référencées dans la requête au début de la définition.

5. Évitez d'ajouter des index similaires aux index existants. Cela entraînera plus de travail pour l'optimiseur et ralentira l'opération de mise à jour. Au lieu de cela, nous devrions modifier l'index existant pour inclure les colonnes supplémentaires. Par exemple, supposons qu'il existe un index i1 sur une table (c1, c2). Vous remarquez que "wherec2=?" est utilisé dans la requête, vous créez donc un index i2 sur (c2). Mais cet index similaire n'ajoute rien, c'est juste une redondance pour i1, et maintenant c'est une surcharge supplémentaire.

6. Si la table est en lecture seule et contient de nombreuses lignes, vous pouvez essayer de définir un index et utiliser la clause INCLUDE dans CREATE INDEX pour que l'index inclue toutes les colonnes référencées dans la requête (incluses par la clause INCLUDE). la colonne ne fait pas partie de l'index, mais est stockée uniquement dans le cadre de la page d'index pour éviter des EXCURSIONS de données supplémentaires).

Pour l'entrepôt de données (base de données du système de requête), davantage d'index peuvent être établis (le rapport index/données peut être de 1:1).

Lorsque vous décidez d'utiliser ou non un index, vous pouvez vous concentrer sur le taux de récupération. Autrement dit, la base permettant de juger de l'efficacité de l'index est le pourcentage de données récupérées en utilisant la valeur clé comme condition unique. Plus le pourcentage est faible, plus l’indice est efficace. Cette conclusion repose sur certaines hypothèses, telles que les performances relatives de l'accès au disque.

Le fait que les emplacements physiques des enregistrements liés à la valeur de la clé d'index soient adjacents est également important, car les données sont manipulées via des blocs. Après la création de l'index, si les enregistrements pointés par la clé d'index sont dispersés dans la table, même si ces enregistrements représentent une petite proportion dans la table, les performances de l'index seront considérablement réduites car ils sont dispersés dans l'ensemble de la table. disque.

Il convient également de noter que les fonctions et les conversions de types peuvent provoquer un échec de l'index.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!