Principes d'indexation - en prenant innodb comme exemple

1. Écrivez devant

Alors que les tâches de développement et de test touchent à leur fin, tout le monde s'occupe du travail préparatoire avant la sortie du projet. L'une des tâches importantes est de créer des instructions SQL. L'index est très nécessaire dans un environnement avec une concurrence élevée et un trafic élevé. La construction d'un bon index peut donc grandement améliorer l'efficacité des requêtes des instructions SQL. Qu'est-ce qu'un index et comment créer un bon index ? ? Cet article prend le moteur de stockage mysql Innodb comme exemple et explique comment créer un bon index basé sur des projets réels.

2. Définition de l'index

La définition officielle de l'index par MySQL est la suivante : L'index (Index) est une structure de données qui aide MySQL à obtenir des données efficacement. En extrayant le radical de la phrase, vous pouvez obtenir l’essence de l’index : l’index est une structure de données.
Nous savons que la requête de base de données est l'une des fonctions les plus importantes de la base de données, comme l'instruction SQL suivante : SELECT * FROM test_table WHERE id = 99 ; l'enregistrement de données avec l'identifiant 99 peut être obtenu à partir de la table test_table.
Nous voulons tous interroger les données le plus rapidement possible, c'est pourquoi les concepteurs de systèmes de bases de données optimiseront du point de vue des algorithmes de requête. L'algorithme de requête le plus basique est bien sûr la recherche linéaire. Il parcourt test_table puis recherche ligne par ligne si la valeur de id est 99. Cet algorithme avec une complexité de O(n) est évidemment mauvais lorsque la quantité de données est bonne. Le développement de l'informatique a fourni de nombreux meilleurs algorithmes de recherche, tels que la recherche binaire, la recherche par arbre binaire, etc. Si vous faites une petite analyse, vous constaterez que chaque algorithme de recherche ne peut être appliqué qu'à des structures de données spécifiques. Par exemple, la recherche binaire nécessite que les données récupérées soient ordonnées, tandis que la recherche par arbre binaire ne peut être appliquée qu'aux arbres de recherche binaires, mais. les données elles-mêmes La structure organisationnelle ne peut pas satisfaire complètement diverses structures de données (par exemple, il est théoriquement impossible d'organiser les deux colonnes dans l'ordre en même temps), donc en plus des données, le système de base de données maintient également des structures de données qui satisfont une recherche spécifique Les structures font référence (pointent vers) les données d'une manière ou d'une autre, ce qui permet d'implémenter des algorithmes de recherche avancés sur ces structures de données. Cette structure de données est un index.
L'exemple ci-dessus est principalement utilisé pour illustrer brièvement le rôle de l'index. La plupart des systèmes de bases de données et des systèmes de fichiers, y compris mysql Innodb, ne choisissent pas la structure arborescente binaire comme index, mais utilisent B-Tree ou sa variante B Tree. sert de structure d'index. Cette structure d'index peut minimiser le nombre d'accès aux E/S disque pendant le processus de recherche. Vous pouvez découvrir par vous-même ce qu'est B-Tree ou B Tree et les raisons de les choisir comme structures d'index de base de données. . Ci-dessous, nous présentons d'abord les deux index B Tree du moteur mysql Innodb.

3. Index Mysql Innodb B Tree

L'un est l'index de clé primaire, qui est l'index clusterisé (index de cluster). Il contient non seulement la clé primaire, mais également toutes les données. à laquelle appartient la clé primaire, donc dans Innodb, l'index de clé primaire est la donnée

L'un est un index de clé non primaire (index secondaire) dans lequel la valeur de la colonne est Key et la position de la clé primaire ; est la valeur, c'est-à-dire (valeur de la colonne, position de la clé primaire)

Innodb est un index -table organisée, et toutes les données sont suspendues sous le nœud feuille de clé primaire. Par conséquent, si l'ordre d'insertion des clés primaires ne peut pas être garanti, un grand nombre de divisions de nœuds de clé primaire se produiront, entraînant un grand nombre d'opérations d'E/S. De plus, Innodb stipule que la longueur d'un seul champ d'index ne doit pas dépasser 768 octets, sinon la longueur sera tronquée et non placée dans l'index. Les index de clé non primaire d'Innodb pointent tous vers l'index de clé primaire. La recherche de l'index de clé non primaire ne peut pas obtenir la ligne entière de données. Vous devez trouver l'emplacement de son index de clé primaire via le pointeur du nœud feuille pour obtenir le. ligne entière de données. Par conséquent, l'index de clé primaire doit être conçu aussi petit que possible, sinon l'index de clé non primaire sera très grand.

4.Principes d'indexation

Examinons ensuite les principes qui doivent être suivis pour construire un bon index, et illustrés par des exemples spécifiques
1 Le principe de correspondance du préfixe le plus à gauche, un principe très important, mysql correspondra toujours au préfixe le plus à gauche. jusqu'à ce qu'il rencontre Arrêter la correspondance lors de l'atteinte de la requête de plage (>, de (a, b, c, d), D n'est pas utilisé pour l'indexation. Si vous créez un index pour (a, b, d, c), il peut être utilisé. L'ordre de a, b, d peut être ajusté. arbitrairement.
2. = et in peuvent être dans le désordre, comme a = 1 et b = 2 et c = 3. Vous pouvez créer des index (a, b, c) dans n'importe quel ordre. L'optimiseur de requêtes MySQL vous aidera à optimiser. l'index sous une forme reconnaissable.
3. Essayez de choisir des colonnes avec une distinction élevée comme index. La formule de distinction est count(distinct col)/count(*), qui représente la proportion de champs qui ne sont pas répétés. nous analysons, et le degré d'unicité de la clé est de 1, tandis que le degré de distinction de certains champs de statut et de genre peut être de 0 face au Big Data. Alors quelqu'un peut se demander : y a-t-il une valeur empirique pour ce rapport ? Différents scénarios d'utilisation rendent cette valeur difficile à déterminer. Généralement, nous exigeons que les champs qui doivent être joints soient supérieurs à 0,1, soit une moyenne de 10 enregistrements par analyse
4. Les colonnes d'index ne peuvent pas participer aux calculs, conservez donc les colonnes. "clean", comme from_unixtime (create_time) = '2015-08-14' ne peut pas utiliser l'index. La raison est très simple. Le b-tree stocke toutes les valeurs de champ​​dans la table de données, mais lors de la récupération, vous devez appliquer des fonctions à tous les éléments pour comparer. Le coût est évidemment trop élevé. La déclaration doit donc être écrite sous la forme create_time = unix_timestamp('2015-08-14').
5. Développez l'index autant que possible, ne créez pas un nouvel index. Par exemple, il y a déjà un index pour a dans la table, et maintenant vous souhaitez ajouter un index pour (a, b), il vous suffit alors de modifier l'index d'origine.
6. Dans la clause order by ou group by, si vous souhaitez trier par index, l'ordre des colonnes d'index doit être cohérent avec l'ordre de la clause order by ou group by, et le sens de tri de toutes les colonnes. (ordre inverse ou séquence positive) sont les mêmes ; si la requête est associée à plusieurs tables, l'index ne peut être utilisé pour trier que lorsque les champs référencés par la clause order by proviennent tous de la première table ; Les instructions group by et les instructions de type requête sont les mêmes : le principe du préfixe le plus à gauche de l'index doit être satisfait ; sinon mysql effectuera une opération de tri et ne pourra pas utiliser l'index pour trier (il existe une situation où l'ordre par ou le groupe ; by ne satisfait pas au principe du préfixe le plus à gauche, c'est-à-dire que son leader est une constante. Quand, si Where ou join spécifient des constantes pour ces colonnes, cela peut compenser le manque d'index).

5. Exemple

Déclaration 1 :

Déclaration 2 :

Pour ces deux déclarations, si elles sont considérées séparément, vous pouvez créer deux index :
Create (status, netting_batch_no, Debor_agent_member_id) pour la déclaration 1
Pour la déclaration 2 Create (netting_batch_no, Debtor_agent_member_id,transaction_currency);
Si vous considérez tout cela, un seul index suffit en fait, c'est-à-dire (netting_batch_no,debtor_agent_member_id). Il n'est pas nécessaire de mettre les champs status ou transaction_currency dans l'index car ces deux La distinction entre les champs est). trop pauvre ;
Selon le principe d'indexation 2, l'instruction 1 peut aller à cet index
Selon le principe d'indexation 1, l'instruction 2 peut également aller à cet index ; Créer trop d'index augmentera la consommation de mémoire ou de disque de la base de données et affectera les performances des opérations telles que l'insertion et la suppression. Lors de l'établissement d'un index, vous devez suivre les principes de création d'index et tout considérer

. Ce qui précède sont les principes d'indexation - en prenant innodb comme exemple. Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (www.php.cn) !