Différence entre l'index cluster et l'index non cluster (index secondaire) dans InnODB.

La différence entre les index en cluster et les index non cluster est: 1. Les index en cluster stocke les lignes de données dans la structure d'index, ce qui convient à la requête par la clé primaire et la plage. 2. L'index non cluster stocke les valeurs de clé d'index et les pointeurs vers les lignes de données, et convient aux requêtes de colonne de clés non primaires.

introduction

Lors de l'exploration du mystère du moteur de stockage InNODB, l'indexation est sans aucun doute un pic que nous devons surmonter. Aujourd'hui, nous allons creuser dans les différences entre les index en cluster et les index non cluster (index non groupé, également appelé index secondaires, index de deuxième niveau). Ce n'est pas seulement une exploration technologique, mais aussi une collision d'idées sur l'optimisation des performances de la base de données. En lisant cet article, vous maîtriserez les différences de base entre ces deux index et serez en mesure de mieux concevoir et optimiser votre structure de base de données.

Examen des connaissances de base

Dans InNODB, l'indexation est la clé de l'optimisation des performances de la base de données. Les index sont comme la bibliographie de la bibliothèque, nous aidant à trouver rapidement les informations dont nous avons besoin. Les index en cluster et les index non cluster sont deux types d'index différents, et leurs concepts de conception et leurs scénarios d'utilisation ont leurs propres avantages.

Le concept de base de l'indexation en cluster est de stocker les lignes de données directement dans la structure de l'index, ce qui signifie que l'index et les données sont étroitement liés. Un index non cluster est différent, ce n'est qu'un pointeur vers une rangée de données, similaire à une carte de bibliographie dans une bibliothèque, pointant vers un livre réel.

Analyse du concept de base ou de la fonction

Définition et fonction de l'index en grappe

La définition des index en cluster est simple et puissante: il combine des structures d'index et des lignes de données pour former une structure de stockage complète. Dans InNODB, chaque tableau a un index en cluster, généralement une clé primaire. Si aucune clé primaire n'est définie explicitement, InnodB sélectionne un index unique en tant qu'index en cluster, ou dans des cas extrêmes, génère un index cluster caché.

Le rôle des index en cluster est évident: il rend la requête et la requête de plage par la clé primaire extrêmement efficace. Étant donné que les données ont été triées par la clé principale, l'opération de recherche peut être effectuée directement sur l'arborescence d'index sans étapes de recherche supplémentaires.

Un exemple d'index en cluster simple:

 Créer des employés de table (
    id int clé primaire,
    nom varchar (100),
    Décimal salarial (10, 2)
));

- Les index en cluster sont automatiquement créés sur le champ ID

Définition et fonction de l'index non cluster

Les index non cluster sont plus flexibles, ce qui nous permet de créer des index sur n'importe quelle colonne de la table. Un index non cluster contient des valeurs de clé d'index et un pointeur vers une ligne de données, et non les données elle-même. Cela signifie que les index non cluster peuvent avoir plusieurs, tandis que les index en cluster ne peuvent en avoir qu'un.

Le rôle de l'index non groupé est d'améliorer les performances de requête des colonnes de clés non primaires. Par exemple, si nous interrogeons souvent des informations sur la base des noms des employés, la création d'un index non cluster sur name améliorera considérablement l'efficacité de la requête.

Un exemple d'index non cluster:

 Créer des employés de table (
    id int clé primaire,
    nom varchar (100),
    décimal salarial (10, 2),
    Index idx_name (nom)
));

- L'index non cluster IDX_NAME est créé sur le champ Nom

Comment ça marche

Le principe de travail de l'indexation en grappe est de stocker les données via une structure de B-Tree, et les index et lignes de données sont stockés physiquement en continu. Cela signifie que lorsque nous faisons des requêtes à portée, nous pouvons traverser directement l'arbre d'index, en évitant des opérations d'E / S supplémentaires.

Le principe de travail des index non cluster est plus complexe. Il recherche d'abord la correspondance des valeurs de clé d'index sur l'arborescence d'index, puis passe à la ligne de données réelle via le pointeur. Cette méthode ajoute une opération d'E / S, mais est toujours très efficace pour les requêtes de clés non primaires.

Une compréhension approfondie des principes de travail de ces deux index peut nous aider à mieux concevoir des structures de base de données et optimiser les performances de la requête.

Exemple d'utilisation

Utilisation de base des index en grappe

L'utilisation la plus courante des index en cluster est de remettre en question par les clés primaires. Supposons que nous recherchons des informations sur les employés avec 100 ID:

 Sélectionnez * parmi les employés où id = 100;

Cela consultera directement l'index en cluster, ce qui est très efficace.

Utilisation de base des index non cluster

L'utilisation de base des index non cluster consiste à interroger via des champs d'index. Par exemple, nous voulons trouver un employé nommé "John Doe":

 Sélectionnez * dans les employés où name = 'John Doe';

Cela recherchera d'abord la valeur name correspondante sur idx_name , puis trouvera la ligne de données réelle via le pointeur.

Utilisation avancée

L'utilisation avancée des index en cluster comprend la requête et le tri de la portée. Par exemple, nous voulons trouver des employés avec un salaire entre 5 000 et 10 000:

 Sélectionner * parmi les employés où un salaire entre 5000 et 10000 commande par id;

Cela utilisera les caractéristiques de tri des indices en cluster pour améliorer l'efficacité de la requête.

L'utilisation avancée des index non cluster comprend des index combinés et des index d'écrasement. Par exemple, nous créons un index composite sur name et les champs salary :

 Créer un index idx_name_salary sur les employés (nom, salaire);

Cela nous permettra de faire des requêtes efficaces par nom et salaire:

 Sélectionnez * dans les employés où name = 'John Doe' et salaire> 5000;

Erreurs courantes et conseils de débogage

Les erreurs courantes lors de l'utilisation des index incluent:

• La sélection de colonne d'index inappropriée entraîne une mauvaise performance de requête.
• La surutilisation des indices augmente les coûts de maintenance et les frais généraux des opérations d'insertion / de mise à jour.

Les compétences de débogage comprennent:

• Utilisez EXPLAIN pour analyser les plans de requête et comprendre l'utilisation des index.
• Surveillez et ajustez régulièrement l'index pour s'assurer qu'il reste valide.

Optimisation des performances et meilleures pratiques

Dans les applications pratiques, l'optimisation de l'indexation est la clé pour améliorer les performances de la base de données. Les index en cluster et les index non cluster ont leurs propres avantages et inconvénients, et nous devons choisir en fonction de nos besoins commerciaux spécifiques.

L'avantage des index en cluster est leurs capacités de requête et de tri de portée efficaces, mais l'inconvénient est qu'il ne peut y avoir qu'un seul indice en cluster, et une mauvaise sélection peut entraîner des goulots d'étranglement des performances. L'avantage des index non cluster est leur flexibilité et peut être créé sur n'importe quelle colonne, mais l'inconvénient est que des opérations d'E / S supplémentaires sont ajoutées qui peuvent affecter les performances de la requête.

Les meilleures pratiques incluent:

• Sélectionnez la touche primaire appropriée comme index en cluster, généralement l'ID d'incrémentation automatique ou UUID.
• Créez des index non cluster sur des colonnes fréquemment interrogées, mais évitez les index.
• Maintenez et optimisez régulièrement l'indice pour vous assurer qu'il reste valide.

En comprenant profondément les différences entre les index en cluster et non cluster, nous pouvons mieux concevoir et optimiser les structures de base de données et améliorer les performances de la requête. Ce n'est pas seulement une exploration technologique, mais aussi une collision d'idées sur l'optimisation des performances de la base de données. J'espère que cet article pourra vous apporter une nouvelle inspiration et une nouvelle réflexion.

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!