Prenons MySQL comme exemple pour vous aider à comprendre les réglementations militaires de ces bases de données.

La base de données est toujours la partie la plus critique de l'application. Dans le même temps, plus le niveau de concurrence est élevé, plus la base de données devient souvent un goulot d'étranglement si les tables et les index de la base de données ne sont pas bien conçus au début, puis plus tard. s'étendra horizontalement et les sous-bases de données et les tableaux seront perturbés.

Pour les sociétés Internet, la base de données MySQL est généralement utilisée.

1. L'architecture globale de la base de données

Examinons d'abord l'architecture globale des données MySQL comme suit :

C'est une image très classique Le diagramme de l'architecture du système MySQL montre les fonctions de chaque partie de MySQL à travers ce diagramme.

Lorsque le client se connecte à la base de données, il fait d'abord face au pool de connexions, qui est utilisé pour gérer les connexions des utilisateurs et effectuer certaines authentifications et autorisations.

Après s'être connecté à la base de données, le client enverra des instructions SQL et le module d'interface SQL devra accepter les instructions SQL de l'utilisateur.

Les instructions SQL doivent souvent se conformer à des règles de grammaire strictes, un analyseur de grammaire est donc nécessaire pour analyser l'instruction. Le principe de l'analyse de la grammaire est celui appris dans le principe de compilation, d'une instruction à un arbre syntaxique.

Les requêtes auxquelles appartient l'utilisateur peuvent être optimisées afin que le chemin de requête le plus rapide puisse être sélectionné. C'est le rôle de l'optimiseur.

Afin d'accélérer la requête, il existe un module de cache de requêtes. Si le cache de requêtes a un résultat de requête positif, l'instruction de requête peut récupérer directement les données du cache de requêtes.

Tous les composants ci-dessus constituent la couche de service de base de données, suivie de la couche du moteur de base de données. Le moteur de base de données principal actuel est InnoDB.

Pour toute modification apportée à la base de données, la couche de service de base de données aura un journal binaire enregistré, qui constitue la base de la réplication principale et secondaire.

Pour la couche moteur de base de données, un schéma célèbre est le suivant :

Dans la couche moteur de stockage, il y a aussi les caches et les logs, et le final les données sont déposées sur le disque de.

Le cache de la couche moteur de stockage est également utilisé pour améliorer les performances, mais il est différent du cache de la couche service de base de données. Le cache de la couche service de base de données est un cache de requêtes, tandis que le cache de la couche service de base de données est un cache de requêtes. La couche du moteur de base de données met en cache la lecture et l'écriture. Le cache de la couche de service de base de données est basé sur une logique de requête, tandis que le cache du moteur de base de données est basé sur des pages de données, que l'on peut qualifier de physiques.

Même si les données sont uniquement écrites dans le cache de la couche moteur de base de données, pour la couche de service de base de données, même si elles ont été conservées, bien sûr, cela entraînera la page de cache et la page sur le disque dur Le disque doit être endommagé. L'incohérence des données est assurée par des journaux au niveau de la couche moteur de base de données pour garantir l'intégrité.

Ainsi, les logs de la couche moteur de base de données sont différents de ceux de la couche service de base de données. Les logs de la couche service enregistrent la logique de modification une par une, tandis que les logs de la couche moteur enregistrent les différences physiques entre. pages de cache et pages de données.

2. Workflow de la base de données

Lors de la réception d'une requête, les différents composants de l'architecture MySQL fonctionnent comme ceci :

Le client établit une connexion TCP avec la couche de service de base de données. Le module de gestion des connexions établira la connexion et demandera un thread de connexion. S'il existe un thread de connexion inactif dans le pool de connexions, il est alloué à cette connexion. Dans le cas contraire, un nouveau thread de connexion est créé pour prendre en charge ce client sans dépasser le nombre maximum de connexions.

Avant le fonctionnement réel, le module utilisateur doit être appelé pour une vérification d'autorisation afin de vérifier si l'utilisateur dispose des autorisations. Après le passage, le service est fourni et le thread de connexion commence à recevoir et à traiter l'instruction SQL du client.

Une fois que le fil de connexion a reçu l'instruction SQL, il transmet l'instruction au module d'analyse des instructions SQL pour l'analyse syntaxique et l'analyse sémantique.

S'il s'agit d'une instruction de requête, vous pouvez d'abord vérifier s'il y a des résultats dans le cache de requête. S'il y a des résultats, ils peuvent être renvoyés directement au client.

S'il n'y a aucun résultat dans le cache de requêtes, vous devez réellement interroger la couche du moteur de base de données, elle est donc envoyée à l'optimiseur SQL pour l'optimisation des requêtes. S'il s'agit d'un changement de table, il sera transmis aux modules de traitement d'insertion, de mise à jour, de suppression, de création et de modification pour traitement.

L'étape suivante consiste à demander la couche moteur de base de données, à ouvrir la table et à obtenir le verrou correspondant si nécessaire.

Le processus de traitement suivant va à la couche moteur de base de données, telle qu'InnoDB.

Au niveau du moteur de base de données, vous devez d'abord demander s'il existe des données correspondantes dans la page de cache. Si c'est le cas, elles peuvent être renvoyées directement. Sinon, elles doivent être lues à partir du disque.

Lorsque les données correspondantes sont trouvées sur le disque, elles seront chargées dans le cache, ce qui rendra les requêtes ultérieures plus efficaces. En raison de la mémoire limitée, les tables LRU flexibles sont souvent utilisées pour gérer les pages de cache afin de garantir la fiabilité du cache. Ce sont des données fréquemment consultées.

Après avoir obtenu les données, renvoyez-les au client, fermez la connexion, relâchez le fil de connexion et le processus se termine.

3. Principe de l'index de base de données

Dans l'ensemble du processus, le point de goulot d'étranglement le plus facilement appelé est la lecture et l'écriture des données, ce qui signifie souvent lire et écrire le disque de manière séquentielle ou aléatoire, et la lecture et l'écriture sur les disques ont tendance à être plus lentes.

Et si on accélérait ce processus ? Je crois que tout le monde a deviné qu'il s'agissait de créer un index.

Pourquoi l’indexation peut-elle accélérer ce processus ?

Je crois que tout le monde a visité la ville gastronomique. Il y a de nombreux restaurants. Si vous n'êtes pas pressé, n'avez pas faim et n'avez aucune exigence en matière de performances de recherche, vous pouvez prendre votre temps dans le centre commercial et. naviguez d'un restaurant à l'autre, sachez trouver le restaurant que vous souhaitez manger. Mais lorsque vous avez faim ou que vous avez pris rendez-vous dans un restaurant, vous souhaitez absolument vous rendre directement à ce restaurant. À ce moment-là, vous consulterez souvent la carte d'étage pour trouver rapidement l'emplacement de votre restaurant cible. vous le trouvez, allez directement au sujet. Cela vous fera gagner beaucoup de temps, c'est le rôle de l'index.

L'index doit donc trouver rapidement son emplacement grâce à la valeur, afin qu'il soit accessible rapidement.

Une autre fonction de l'index est que vous pouvez porter des jugements sans réellement regarder les données. Par exemple, s'il y a un certain restaurant dans le centre commercial, vous pouvez le savoir en regardant l'index sans y aller. le centre commercial. , et si vous voulez trouver tous les restaurants du Sichuan, il vous suffit de regarder l'index, et vous n'êtes pas obligé de passer d'un restaurant du Sichuan à l'autre.

Alors, comment fonctionnent les index dans MySQL ?

La structure d'index de MySQL est souvent un arbre B+.

Un arbre B+ d'ordre M a les propriétés suivantes :

1 Les nœuds sont divisés en nœuds d'index et nœuds de données. Le nœud d'index est équivalent au nœud interne du B-tree. Tous les nœuds d'index forment un B-tree et possèdent toutes les caractéristiques du B-tree. Dans le nœud d'index, la clé et le pointeur sont stockés et aucun élément spécifique n'est stocké. Le nœud de données est équivalent au nœud externe de l'arbre B. Le nœud externe de l'arbre B est vide et est utilisé dans l'arbre B+ pour stocker des éléments de données réels. Il contient la clé et d'autres informations de l'élément, mais il existe. pas de pointeur.

2. Le B-tree composé de l'ensemble du nœud d'index est uniquement utilisé pour trouver à quel nœud externe se trouve l'élément de données avec une certaine clé. Une fois la clé trouvée dans le nœud d'index, le problème n'est pas terminé. Vous devez continuer à trouver le nœud de données, puis lire les éléments dans le nœud de données, ou effectuer une recherche binaire ou une analyse séquentielle pour trouver les données réelles. éléments.

3. L'ordre M est uniquement utilisé pour contrôler le degré de la partie du nœud d'index. Quant au nombre d'éléments que contient chaque nœud de données, cela n'a rien à voir avec M.

4. Il existe également une liste chaînée qui regroupe tous les nœuds de données et est accessible de manière séquentielle.

Cette définition est relativement abstraite, regardons un exemple précis.

Sur l'image, nous pouvons voir qu'il s'agit d'un arbre B+ d'ordre 3 et qu'un nœud de données externe contient jusqu'à 5 éléments. Si les données insérées se trouvent dans le nœud de données et ne provoquent pas de fractionnement ni de fusion, l'arbre B composé de nœuds d'index ne changera pas.

Si un élément 76 est inséré dans le nœud externe de 71 à 75, cela provoquera une division 71, 72 et 73 deviendront un nœud de données, et 74, 75 et 76 deviendront un nœud de données, ce qui équivaut au nœud d'index en cours d'insertion d'une clé de 74.

Si 43 est supprimé des nœuds externes 41 à 43, cela provoquera une fusion 41, 42, 61, 62 et 63 seront fusionnés en un seul nœud, cela équivaut à. le processus de suppression de la clé 60. .

Lors de la recherche, la hauteur de la couche d'arbre B+ étant très faible, elle peut être positionnée relativement rapidement. Par exemple, si l'on veut retrouver la valeur 62, si elle se révèle supérieure à 40 à la racine. nœud, nous accéderons au côté droit, s'il est inférieur à 70, nous accéderons au côté gauche, et s'il est supérieur à 60, nous accéderons au côté droit. Visitez ensuite le côté droit, au niveau du deuxième nœud feuille, vous en trouverez 62 et vous réussirez à le localiser.

Dans InnoDB de MySQL, il existe deux types d'index arborescents B+, l'un est appelé index clusterisé et l'autre est appelé index secondaire.

Les nœuds feuilles de l'index clusterisé sont des nœuds de données, souvent la clé primaire est utilisée comme index clusterisé. Les nœuds feuilles de l'index secondaire stockent le champ KEY plus la valeur de la clé primaire. Par conséquent, l’accès aux données via un index secondaire nécessite d’accéder à l’index deux fois.

Il existe également une forme d'index appelée index combiné, ou index composé, qui peut indexer sur plusieurs colonnes.

La règle de tri de ce type d'index est de comparer d'abord la première colonne, et si la première colonne est égale, de comparer la deuxième colonne, et ainsi de suite.

4. Avantages et inconvénients de l'index de base de données

L'avantage le plus évident de l'index de base de données est de réduire les E/S. Plusieurs scénarios sont analysés ci-dessous.

Pour les champs avec des conditions =, vous pouvez rechercher directement l'arbre B+ et utiliser un très petit nombre de lectures sur le disque dur (équivalent à la hauteur de l'arbre B+) pour atteindre les nœuds feuilles puis localiser directement les données . Emplacement.

Pour les champs de plage, puisque l'arbre B+ est trié, la plage peut être rapidement positionnée à travers l'arbre.

De même pour orderby, group by, distinct/max, min, puisque l'arbre B+ est trié, les résultats peuvent être obtenus rapidement.

Il existe également un scénario courant appelé index couvrant les données. Par exemple, deux champs A et B sont utilisés comme champs de condition, et A=a ET B=b apparaissent souvent. Lors de la sélection simultanée de C et D, un index conjoint (A, B) est souvent construit, qui est un. index secondaire, donc lors de la recherche, les nœuds feuilles et les enregistrements correspondants peuvent être rapidement trouvés via l'arborescence B+ de l'index secondaire, mais certains enregistrements contiennent les ID de l'index clusterisé, vous devez donc rechercher l'arborescence B+ de l'index clusterisé une fois pour trouver les vrais enregistrements dans le tableau, puis dans l'enregistrement, lisez C et D. Si l'index conjoint est (A, B, C, D) lors de l'établissement de l'index conjoint, alors toutes les données seront dans l'arborescence B+ de l'index secondaire et pourront être renvoyées directement, réduisant ainsi le processus de recherche dans l'arborescence.

Bien sûr, l’indexation doit avoir un prix, il n’y a pas de repas gratuit au monde.

La plupart des avantages apportés par les index sont l'amélioration de l'efficacité de lecture, tandis que le prix apporté par les index est la réduction de l'efficacité d'écriture.

L'insertion et la modification de données peuvent entraîner des changements d'index.

Lors de l'insertion, un index clusterisé est souvent construit sur la clé primaire, il est donc préférable d'utiliser l'auto-incrémentation pour la clé primaire, afin que les données insérées soient toujours à la fin et soient séquentielles, ce qui est plus efficace. N'utilisez pas d'UUID pour les clés primaires, cela entraînerait des écritures aléatoires et une mauvaise efficacité. N'utilisez pas de clés primaires liées à l'entreprise, car le fait d'être lié à l'entreprise signifie qu'elles seront mises à jour et risquent d'être supprimées et réinsérées, ce qui entraînera une mauvaise efficacité.

Grâce à l'introduction ci-dessus du principe de l'arbre B+, nous pouvons voir que le coût du fractionnement dans l'arbre B+ est encore relativement élevé et que le fractionnement se produit souvent pendant le processus d'insertion.

Quant à la modification des données, elle équivaut fondamentalement à supprimer et réinsérer, et le coût est relativement élevé.

Les index secondaires sur certaines colonnes de chaînes provoquent souvent une écriture et une lecture aléatoires et exercent une plus grande pression sur les E/S.

5. Interpréter les principes derrière les réglementations militaires des bases de données

En comprenant les principes de ces deux index, nous pouvons expliquer pourquoi de nombreuses réglementations militaires dites bases de données ressemblent à ceci. Expliquons-les un par un ci-dessous.

Dans quelles circonstances un index combiné doit-il être utilisé au lieu d'un index séparé ?

Supposons qu'il existe une instruction conditionnelle A=a AND B=b Si A et B sont deux index distincts, un seul index fonctionnera sous la condition AND, il doit être jugé un par un. et si une combinaison est utilisée, l'index (A, B) n'a besoin que de parcourir un arbre, ce qui augmente considérablement l'efficacité. Mais pour A=a OR B=b, en raison de la relation OR, l'index combiné ne fonctionne pas, donc un index séparé peut être utilisé. À ce stade, les deux index peuvent fonctionner en même temps.

Pourquoi l'index doit-il être différencié ? Dans l'index combiné, l'index différencié doit être placé en premier ?

S'il n'y a pas de distinction, comme l'utilisation du sexe, cela équivaut à diviser l'ensemble de la grande table en deux parties. La recherche de données nécessite toujours de parcourir la moitié de la table pour les trouver, ce qui rend l'index dénué de sens.

S'il existe un index composite, ai-je toujours besoin d'un index à une seule colonne ?

Si l'index combiné est (A, B), alors pour la condition A=a, cet index combiné peut être utilisé, car l'index combiné est d'abord trié selon la première colonne, il n'est donc pas nécessaire pour A Créez un index séparé, mais cela n'est pas utile pour B=b, car la deuxième colonne n'est comparée que lorsque la première colonne est la même, donc la deuxième colonne est la même et peut être distribuée sur différents nœuds. Way Positionnement rapide.

Est-ce que plus il y a d'index, mieux c'est ?

Bien sûr que non, ajoutez des index uniquement lorsque cela est nécessaire. Les index réduiront non seulement l'efficacité de l'insertion et de la modification, mais auront également un optimiseur de requête lors de l'interrogation. Trop d'index confondront l'optimiseur. pour trouver le chemin de requête correct, un index lent est donc choisi.

Pourquoi utiliser des clés primaires à incrémentation automatique ?

Étant donné que les clés primaires de chaîne et les clés primaires aléatoires entraîneront une insertion aléatoire de données, ce qui est moins efficace, les clés primaires doivent être mises à jour moins fréquemment pour éviter les arbres B+ et les fusions et scissions fréquentes.

Pourquoi essayer de ne pas utiliser NULL ?

NULL est plus difficile à gérer dans un arbre B+ et nécessite souvent une logique spéciale pour être traité, ce qui à son tour réduit l'efficacité.

Pourquoi ne pas créer des index sur des champs fréquemment mis à jour ?

La mise à jour d'un champ signifie que l'index correspondant doit également être mis à jour. La mise à jour signifie souvent supprimer puis insérer. L'index est à l'origine une certaine structure de données formée à l'avance, ce qui rend l'étape de lecture plus efficace. . De manière élevée, mais si un champ est écrit davantage et lu moins, il n'est pas recommandé d'utiliser un index.

Pourquoi ne pas utiliser des fonctions dans les conditions de requête ?

Par exemple, pour la condition ID+1=10, l'index est généré lorsqu'il est écrit à l'avance. Lors de la phase de requête, l'index est incompétent pour des opérations telles que ID+1. pour ajouter tous les index en premier. Faites un calcul et comparez. C'est trop cher, donc ID=10-1 doit être utilisé.

Pourquoi ne pas utiliser des conditions de requête négatives telles que NON ?

Vous imaginez bien que pour un arbre B+, le nœud de base est 40. Si votre condition est égale à 20, allez à gauche pour vérifier. Si votre condition est égale à 50, allez à droite pour vérifier. . Mais votre condition S'il n'est pas égal à 66, que dois-je faire de l'index ? Vous ne le savez pas tant que vous n’avez pas tout vécu.

Pourquoi les requêtes floues ne commencent-elles pas par des caractères génériques ?

Pour un arbre B+, si la racine est le caractère def, si le caractère générique est à la fin, comme abc%, alors le côté gauche doit être recherché, comme efg%, le côté droit doit être recherché, si le caractère générique est au début, %abc, alors vous ne savez pas. Quel que soit le côté vers lequel vous devez aller, il est préférable de tous les scanner.

Pourquoi devons-nous changer OR en IN, ou utiliser Union ?

Il est souvent difficile de trouver le meilleur chemin lors de l'optimisation des conditions de requête OR, surtout lorsqu'il existe de nombreuses conditions OR. C'est particulièrement vrai pour le même champ, il est préférable d'utiliser IN La base de données. les conditions sont triées et traitées uniformément grâce à la recherche binaire. Pour différents champs, l'utilisation d'Union permet à chaque sous-requête d'utiliser un index.

Pourquoi les types de données devraient-ils être aussi petits que possible ? Les types entiers sont souvent utilisés à la place des types de caractères. Les index de préfixe peuvent être envisagés pour les types de caractères longs ?

Étant donné que la base de données est stockée en pages, la taille de chaque page est la même. Si le type de données est plus grand, le nombre de pages sera plus grand, les données placées sur chaque page seront plus petites et le nombre de pages sera plus grand. La hauteur de l'arborescence sera plus petite. Elle est relativement élevée, donc le nombre d'E/S nécessaires pour lire les données de recherche sera plus grand, et les nœuds seront facilement divisés lors de l'insertion, et l'efficacité sera réduite. La raison pour laquelle on utilise des entiers au lieu des types de caractères est que les entiers sont plus efficaces pour l'indexation, comme les adresses IP. S'il existe des types de caractères longs qui doivent être interrogés à l'aide d'un index, afin de ne pas rendre l'index trop grand, vous pouvez envisager d'indexer le préfixe du champ au lieu du champ entier.

6. Méthodologie d'optimisation des requêtes

Pour trouver les instructions SQL qui doivent être optimisées, vous devez d'abord collecter les instructions SQL problématiques.

La base de données MySQL fournit une fonction de journalisation SQL lente Grâce au paramètre slow_query_log, vous pouvez obtenir une liste de citations SQL dont le temps d'exécution dépasse un certain seuil.

Les instructions SQL qui n'utilisent pas d'index peuvent être activées via le paramètre long_queries_not_using_indexes.

min_examined_row_limit, seules les instructions SQL avec un nombre d'enregistrements analysés supérieur à cette valeur seront enregistrées dans le journal SQL lent.

Trouvez l'instruction problématique. L'étape suivante consiste à obtenir le plan d'exécution SQL via expliquerSQL. Que ce soit pour analyser l'enregistrement via l'index, vous pouvez créer un index pour optimiser l'efficacité de l'exécution. S'il y a trop d'enregistrements d'analyse. Si le verrou est maintenu trop longtemps ou s'il y a un conflit de verrouillage. Indique si le nombre d'enregistrements renvoyés est important.

Une optimisation personnalisée peut être effectuée ensuite. Pour les champs impliqués dans des conditions de filtre qui ne sont pas couverts par l'index, créez des index sur des champs avec une plus grande distinction. Si plusieurs champs sont impliqués, essayez de créer un index commun.

Le nombre d'enregistrements analysés est très important, mais le nombre d'enregistrements renvoyés n'est pas important et la discrimination est faible. Réévaluez les champs impliqués dans l'instruction SQL et sélectionnez plusieurs champs avec une discrimination élevée à créer. un indice.

Le nombre d'enregistrements analysés est très important, et le nombre d'enregistrements renvoyés est également très important. Les conditions de filtrage ne sont pas fortes. Ajoutez la condition de filtrage SQL

schema_redundant_indexes pour voir quels index redondants. il y a.

Si plusieurs index impliquent des champs dans le même ordre, vous pouvez former un index commun schema_unused_indexes pour voir quels index ne sont jamais utilisés.

7. Principe de séparation lecture-écriture

Les bases de données ont tendance à écrire moins et à lire plus, la première étape de l'optimisation des performances consiste donc à séparer la lecture et l'écriture.

La réplication maître-esclave est implémentée en fonction du journal de la couche de service sur le nœud maître, et il y a un thread IO sur le nœud esclave pour lire ce journal, puis écrivez-le localement. Un autre thread lit le journal local, puis le réexécute sur le nœud esclave.

L'image montre l'organigramme de la réplication asynchrone maître-esclave. Une fois que l'instance maître a écrit dans le moteur, elle renvoie le succès, puis envoie l'événement à l'instance esclave et l'exécute sur l'instance esclave. Cette méthode de synchronisation est plus rapide, mais lorsque le maître raccroche, s'il n'y a pas de réplication, des problèmes de perte de données peuvent survenir.

La réplication synchrone de la base de données est également différente. Elle revient au client après la suppression du nœud esclave. Bien sûr, cela réduira les performances des soumissions par l'équipe de base de données NetEase. Les technologies telles que la réplication parallèle améliorent les performances.

Avec la réplication maître-esclave, la stratégie de séparation lecture-écriture peut être définie au niveau de la couche DAO de la base de données, et cela peut également être fait via un middleware de base de données.

En fait, les logs de la base de données ont bien d'autres utilisations, comme utiliser Canal (projet open source Alibaba : abonnement & consommation incrémentielle basée sur la base de données MySQL Binlog) pour s'abonner au Binlog de la base de données, qui peut être utilisé pour mettre à jour le cache, etc.