Résumé des enjeux pour consolider les fondations de MySQL

Cet article vous apporte des connaissances pertinentes sur mysql. Il résume principalement certains problèmes courants et les résout, y compris les problèmes conventionnels, ainsi que du contenu connexe sur les classes d'index, les classes principales et les classes de framework. J'espère qu'il vous sera utile. Tout le monde est utile.

Apprentissage recommandé : Tutoriel vidéo mysql

Partie régulière

1. Parlez des trois paradigmes majeurs des bases de données ?

Première forme normale : atomicité du champ, deuxième forme normale : les lignes sont uniques et ont des colonnes de clé primaire, troisième forme normale : chaque colonne est liée à la colonne de clé primaire.

Dans les applications réelles, un petit nombre de champs redondants seront utilisés pour réduire le nombre de tables associées et améliorer l'efficacité des requêtes.

2. Une seule donnée est interrogée, mais l'exécution est très lente. Quelles sont les raisons courantes ?

• La base de données MySQL elle-même est bloquée, par exemple : ressources système ou réseau insuffisantes
• Les instructions SQL sont bloquées, telles que : les verrous de table, les verrous de ligne, etc., empêchant le moteur de stockage d'exécuter les instructions SQL correspondantes
• Il s'agit bien d'une mauvaise utilisation des index, pas d'indexation En raison des caractéristiques des données dans la table, l'indexation est utilisée, mais le nombre de retours de table est énorme 3. Différences dans les méthodes d'implémentation de count(*), count. (0) et compte(id) ?

Pour les fonctions de comptage sous la forme de count(*), count(constant), count(primary key), l'optimiseur peut sélectionner l'index avec le coût d'analyse le plus faible pour exécuter la requête afin d'améliorer l'efficacité. Leurs processus d'exécution sont les mêmes.

Pour count (colonne non-index), l'optimiseur choisit une analyse complète de la table, ce qui signifie qu'il ne peut analyser que séquentiellement les nœuds feuilles de l'index clusterisé.

count (colonne d'index secondaire) ne peut sélectionner que l'index contenant les colonnes que nous spécifions pour exécuter la requête, ce qui peut faire en sorte que le coût d'exécution de l'index sélectionné par l'optimiseur ne soit pas le plus petit.

• count(*)、count(常数)、count(主键)形式的count函数来说，优化器可以选择扫描成本最小的索引执行查询，从而提升效率，它们的执行过程是一样的。
• 而对于count(非索引列)来说，优化器选择全表扫描，说明只能在聚集索引的叶子结点顺序扫描。
• count(二级索引列)

4. Que dois-je faire si je supprime accidentellement des données ?

1) Si la quantité de données est relativement importante, utilisez la sauvegarde physique xtrabackup. Effectuez régulièrement des sauvegardes complètes de la base de données et vous pouvez également effectuer des sauvegardes incrémentielles.

2) Si la quantité de données est petite, utilisez mysqldump ou mysqldumper, puis utilisez binlog pour récupérer ou configurez une méthode maître-esclave pour récupérer les données. Vous pouvez récupérer à partir des points suivants :

.

• Déclaration de mauvaise opération DML : vous pouvez utiliser le flashback pour analyser d'abord l'événement binlog, puis l'inverser.
• Mauvais fonctionnement de l'instruction DDL : les données ne peuvent être restaurées que via une sauvegarde complète + l'application de binlog. Une fois que la quantité de données est relativement importante, le temps de récupération sera particulièrement long.
• Suppression rm : utilisez la sauvegarde pour enregistrer dans les salles informatiques, ou de préférence dans les villes.

5. La différence entre supprimer, tronquer et supprimer

• Le processus de suppression par l'instruction DELETE consiste à supprimer une ligne de la table à la fois, et en même temps, l'opération de suppression de la ligne est enregistré dans le journal en tant qu'enregistrement de transaction pour un traitement ultérieur.
• TRUNCATE TABLE supprime toutes les données de la table en même temps et n'enregistre pas les enregistrements d'opérations de suppression individuelles dans le journal. Les lignes supprimées ne peuvent pas être récupérées. Et le déclencheur de suppression lié à la table ne sera pas activé pendant le processus de suppression, et la vitesse d'exécution est rapide.
• L'instruction drop libère tout l'espace occupé par la table.

6. Pourquoi la requête MySQL sur les grandes tables n'explose-t-elle pas la mémoire ?

• MySQL "envoie en lecture", ce qui signifie que si le client reçoit lentement, le serveur MySQL ne pourra pas envoyer les résultats et le temps d'exécution de la transaction deviendra plus long.
• Le serveur n'a pas besoin de sauvegarder un ensemble de résultats complet. Les processus d'obtention et d'envoi de données sont tous opérés via un next_buffer.
• Les pages de données mémoire sont gérées dans Buffer Pool (BP).
• InnoDB gère Buffer Pool à l'aide d'un algorithme LRU amélioré, qui est implémenté à l'aide d'une liste chaînée. Dans l'implémentation d'InnoDB, l'ensemble de la liste chaînée LRU est divisée en zone jeune et zone ancienne selon un rapport de 5:3 pour garantir que les données chaudes ne seront pas emportées lorsque les données froides sont chargées en gros lots.

7. Comment gérer la pagination profonde (pagination extrêmement volumineuse) ?

• Utiliser l'optimisation de l'identifiant : recherchez d'abord l'ID maximum de la dernière pagination, puis utilisez l'index sur l'identifiant pour interroger, de la même manière que pour sélectionner * de l'utilisateur dont l'identifiant est supérieur à 1 000 000, limite 100.
• Optimiser avec l'index de couverture : lorsqu'une requête MySQL atteint complètement l'index, on parle d'index de couverture, ce qui est très rapide, car la requête n'a besoin que de rechercher sur l'index, et peut ensuite revenir directement sans revenir à la table. pour obtenir les données. Par conséquent, nous pouvons d'abord trouver l'ID de l'index, puis obtenir les données en fonction de l'ID.
• Limiter le nombre de pages si les affaires le permettent

8. Comment optimiser SQL dans le développement quotidien ?

• Ajoutez des index appropriés : créez un index sur les champs utilisés comme conditions de requête et triez-les, considérez plusieurs champs de requête pour créer un index combiné, faites attention à l'ordre des champs d'index combinés et placez les colonnes les plus couramment utilisées. comme conditions limites à la fin A gauche, par ordre décroissant, les indices ne doivent pas être trop nombreux, généralement dans les 5.
• Optimiser la structure de la table : les champs numériques sont meilleurs que les types de chaîne, les types de données plus petits sont généralement meilleurs, essayez d'utiliser NOT NULL
• Optimiser les instructions de requête : analyser le plan d'exécution SQL, s'il atteint l'index, etc. Si le SQL est très complexe, optimisez la structure SQL, si la quantité de données dans la table est trop importante, pensez à diviser la table

9. Quelle est la différence entre les connexions simultanées et les requêtes simultanées dans MySQL ?

• À la suite de l'exécution de show processlist, j'ai vu des milliers de connexions, ce qui fait référence à des connexions simultanées.
• L'instruction "en cours d'exécution" est une requête simultanée.
• Un grand nombre de connexions simultanées affecte la mémoire.
• Les requêtes concurrentes trop élevées sont mauvaises pour le processeur. Une machine dispose d'un nombre limité de cœurs de processeur et si tous les threads s'y précipitent, le coût du changement de contexte sera trop élevé.
• Il convient de noter qu'après qu'un thread entre dans l'attente de verrouillage, le nombre de threads simultanés est réduit de un, de sorte que les threads en attente de verrous de ligne ou de verrous d'espacement ne sont pas inclus dans la plage de nombre. C'est-à-dire que le thread en attente du verrou ne consomme pas de CPU, empêchant ainsi le blocage de l'ensemble du système.

10. Comment MySQL met-il à jour la valeur du champ à la valeur d'origine ? Comment fonctionne-t-il en interne ?

• Aucune mise à jour ne sera effectuée pour les mêmes données.
• Cependant, différents formats de journaux binaires ont des méthodes de traitement des journaux différentes :
  • 1) Lorsqu'elle est basée sur le mode ligne, la couche serveur correspond à l'enregistrement à mettre à jour et constate que la nouvelle valeur est cohérente avec l'ancienne valeur. Elle renvoie directement sans mise à jour. , et il n'enregistre pas non plus le journal binaire.
  • 2) Lorsqu'il est basé sur une instruction ou un format mixte, MySQL exécute l'instruction de mise à jour et enregistre l'instruction de mise à jour dans binlog.

11. Quelle est la différence entre datetime et timestamp ?

• La plage de dates de datetime est de 1001 à 9999 ; la plage de temps de l'horodatage est de 1970 à 2038
• La durée de stockage de la date et de l'heure n'a rien à voir avec le fuseau horaire. La durée de stockage de l'horodatage est également liée au fuseau horaire ; dépend du fuseau horaire
• L'espace de stockage de datetime est de 8 octets ; l'espace de stockage de timestamp est de 4 octets
• La valeur par défaut de datetime est nulle ; le champ de timestamp n'est pas vide par défaut (non nul), et le la valeur par défaut est l'heure actuelle (current_timestamp)

12. Quels sont les niveaux d'isolement des transactions ?

• "Lecture non validée" est le niveau le plus bas et ne peut en aucun cas être garanti
• "Lecture validée" peut éviter l'apparition de lectures sales
• "Lecture répétable" peut éviter l'apparition de lectures sales et de lectures non répétables
• "Sérialisable" peut éviter l'apparition de lectures sales, de lectures non répétables et de lectures fantômes
• Le niveau d'isolation des transactions par défaut de Mysql est "Lecture répétable"

13 Il existe deux commandes kill dans MySQL

.
• kill query + thread id, ce qui signifie mettre fin à l'instruction en cours d'exécution dans ce thread
• kill connection + thread id, où la connexion peut être définie par défaut, indiquant la déconnexion La connexion de ce thread

Index Chapter

1. Quelles sont les catégories d’index ?

• Selon le contenu du nœud feuille, le type d'index est divisé en index de clé primaire et index de clé non primaire.
• Les nœuds feuilles de l'index de clé primaire stockent la ligne entière de données. Dans InnoDB, l'index de clé primaire est également appelé index clusterisé.
• Le contenu des nœuds feuilles dans les index de clé non primaire est la valeur de la clé primaire. Dans InnoDB, les index de clé non primaire sont également appelés index secondaires.

2. Quelle est la différence entre un index clusterisé et un index non clusterisé ?

• Index clusterisé : Un index clusterisé est un index créé avec la clé primaire. L'index clusterisé stocke les données dans la table dans les nœuds feuilles.

  

• Index non clusterisé : Un index créé par une clé non primaire stocke la clé primaire et la colonne d'index dans le nœud feuille. Lorsque vous utilisez l'index non clusterisé pour interroger les données, vous pouvez obtenir la clé primaire. clé sur la feuille, puis recherchez les données que vous souhaitez trouver. (Le processus d'obtention de la clé primaire puis de recherche de celle-ci est appelé retour de table).

  

• Indice de couverture : En supposant que les colonnes interrogées se trouvent être les colonnes correspondant à l'index, et qu'il n'est pas nécessaire de revenir à la table pour rechercher, alors cette colonne d'index est appelée un index de couverture .

3. Pourquoi InnoDB conçoit-il un arbre B+ au lieu de B-Tree, Hash, arbre binaire et arbre rouge-noir ?

• L'index de hachage peut gérer l'ajout, la suppression, la modification et l'interrogation d'une seule ligne de données à une vitesse O(1), mais lorsqu'il est confronté à des requêtes par plage ou à un tri, il conduira aux résultats d'une analyse complète de la table.
• B-tree peut stocker des données dans des nœuds non-feuilles. Étant donné que tous les nœuds peuvent contenir des données cibles, nous devons toujours parcourir le sous-arbre vers le bas à partir du nœud racine pour trouver les lignes de données qui remplissent les conditions. E/S aléatoires, entraînant une dégradation des performances.
• Toutes les lignes de données de l'arborescence B+ sont stockées dans des nœuds feuilles, et ces nœuds feuilles peuvent être connectés dans l'ordre via des « pointeurs ». Lorsque nous parcourons les données dans l'arborescence B+ comme indiqué ci-dessous, nous pouvons passer directement entre plusieurs nœuds enfants. . Jump, ce qui peut économiser beaucoup de temps d'E/S disque.

• Arbre binaire : la hauteur de l'arbre est inégale et ne peut pas s'auto-équilibrer. L'efficacité de la recherche est liée aux données (la hauteur de l'arbre) et le coût d'E/S est élevé.

• Arbre rouge-noir : la hauteur de l'arbre augmente à mesure que la quantité de données augmente, et le coût d'E/S est élevé.

4. Parlons d’index clusterisé et d’index non clusterisé ?

• Dans InnoDB, le nœud feuille de l'index B+ Tree stocke la ligne entière de données à l'aide de l'index de clé primaire, également appelé index clusterisé, qui rassemble le stockage des données et l'index. Si vous trouvez l'index, vous le trouverez. trouver les données.
• Les nœuds feuilles de l'index B+Tree stockent la valeur de la clé primaire, qui est un index de clé non primaire, également appelé index non clusterisé ou index secondaire.
• Le premier index est généralement une IO séquentielle, et l'opération de retour à la table est une IO aléatoire. Plus nous devons revenir à la table, c'est-à-dire plus nous avons besoin de fois d'E/S aléatoires, plus nous avons tendance à utiliser l'analyse complète de la table.

5. L'index non clusterisé retournera-t-il définitivement la requête de table ?

• Pas nécessairement, cela implique que tous les champs requis par l'instruction de requête atteignent l'index. Si tous les champs atteignent l'index, il n'est pas nécessaire d'effectuer une requête vers la table. Un index contient (couvre) les valeurs de tous les champs qui doivent être interrogés et est appelé « index de couverture ».

6. Parlez-moi du principe du préfixe le plus à gauche de MySQL ?

• Le principe du préfixe le plus à gauche est la priorité la plus à gauche. Lors de la création d'un index multi-colonnes, selon les besoins de l'entreprise, la colonne la plus fréquemment utilisée dans la clause Where est placée à l'extrême gauche.
• MySQL continuera à correspondre à droite jusqu'à ce qu'il rencontre une requête de plage (>, <, between, like), puis cessera de correspondre, comme a = 1 et b = 2 et c > .Si (a, Index dans l'ordre de b, c, d), d n'est pas utilisé pour l'indexation. Si vous créez un index de (a, b, d, c), il peut être utilisé. b, d peuvent être ajustés arbitrairement.
• = et in peuvent être dans le désordre, comme a = 1 et b = 2 et c = 3. L'index (a, b, c) peut être établi dans n'importe quel ordre. L'optimiseur de requêtes de MySQL vous aidera à l'optimiser. une forme que l'index peut reconnaître.

7. Qu'est-ce que le pushdown d'index ?

• Lorsque le principe du préfixe le plus à gauche est respecté, le préfixe le plus à gauche peut être utilisé pour localiser les enregistrements dans l'index.
• Avant MySQL 5.6, vous ne pouviez renvoyer les tables qu'une par une en commençant par l'ID. Recherchez la ligne de données sur l'index de clé primaire, puis comparez les valeurs de champ.
• L'optimisation du pushdown d'index (pushdown de condition d'index) introduite dans MySQL 5.6 peut d'abord juger les champs inclus dans l'index pendant le processus de parcours d'index, filtrer directement les enregistrements qui ne remplissent pas les conditions et réduire le nombre de retours de table.

8. Pourquoi Innodb utilise-t-il un identifiant à incrémentation automatique comme clé primaire ?

• Si la table utilise une clé primaire à incrémentation automatique, chaque fois qu'un nouvel enregistrement est inséré, les enregistrements seront ajoutés séquentiellement à la position suivante du nœud d'index actuel. Lorsqu'une page est pleine, une nouvelle page sera ajoutée. être automatiquement ouvert. Si vous utilisez une clé primaire non auto-croissante (telle qu'un numéro d'identification ou un numéro d'étudiant, etc.), puisque la valeur de la clé primaire insérée à chaque fois est approximativement aléatoire, chaque nouvel enregistrement doit être inséré quelque part au milieu de la clé primaire. page d'index existante, les opérations de déplacement et de pagination provoquaient une grande fragmentation et aboutissaient à une structure d'index qui n'était pas assez compacte. Par la suite, OPTIMIZE TABLE (optimiser la table) a dû être utilisée pour reconstruire la table et optimiser les pages remplies.

9. Comment la fonctionnalité ACID de transaction est-elle implémentée ?

• "Atomicité": Il est implémenté à l'aide du journal d'annulation. Si une erreur se produit lors de l'exécution de la transaction ou si l'utilisateur exécute une restauration, le système renvoie l'état du démarrage de la transaction via le journal d'annulation.
• « Persistance » : utilisez le journal de rétablissement pour y parvenir. Tant que le journal de rétablissement est persistant, lorsque le système tombe en panne, les données peuvent être récupérées via le journal de rétablissement.
• "Isolation" : utilisez des verrous et MVCC pour isoler les transactions les unes des autres.
• « Cohérence » : la cohérence est obtenue grâce à la restauration, à la récupération et à l'isolement dans des situations simultanées.

10. Quelle est la différence entre la façon dont MyISAM et InnoDB implémentent l'index B-tree ?

• Moteur de stockage InnoDB : Les nœuds feuilles de l'index de l'arborescence B+ enregistrent les données elles-mêmes

• Moteur de stockage MyISAM : Les nœuds feuilles de l'index de l'arborescence B+ enregistrent l'adresse physique des données ;

• InnoDB, son fichier de données lui-même est un fichier d'index. Par rapport à MyISAM, le fichier d'index et le fichier de données sont séparés. Son fichier de données de table lui-même est une structure d'index organisée par B+Tree. Enregistrements de données complets, la clé de cet index est la clé primaire de la table de données, donc le fichier de données de la table InnoDB lui-même est l'index primaire, qui est appelé "index clusterisé" ou index clusterisé, et les index restants sont utilisés comme auxiliaires index.Le champ de données de l'index auxiliaire La valeur de la clé primaire de l'enregistrement correspondant est stockée à la place de l'adresse, qui est également différente de MyISAM.

11. Quelles sont les catégories d'index ?

• Selon le contenu des nœuds feuilles, le type d'index est divisé en index de clé primaire et index de clé non primaire.
• Les nœuds feuilles de l'index de clé primaire stockent la ligne entière de données. Dans InnoDB, l'index de clé primaire est également appelé index clusterisé.
• Le contenu des nœuds feuilles dans les index de clé non primaire est la valeur de la clé primaire. Dans InnoDB, les index de clé non primaire sont également appelés index secondaires.

12. Quels scénarios entraîneront un échec de l'index ?

Contexte : La capacité de positionnement rapide fournie par l'arbre B+ provient de l'ordre des nœuds frères sur la même couche. Par conséquent, si cet ordre est détruit, il échouera très probablement :

.

• Utilisez la correspondance floue gauche ou gauche sur l'index : c'est-à-dire comme %xx ou comme %xx%. Ces deux méthodes entraîneront un échec de l'index. La raison en est que les résultats de la requête peuvent être "Chen Lin, Zhang Lin, Zhou Lin" et ainsi de suite, nous ne savons donc pas avec quelle valeur d'index commencer à comparer, nous ne pouvons donc interroger que via une analyse complète de la table.

• Utiliser des fonctions pour les index/calculer des expressions pour les index : étant donné que l'index enregistre la valeur d'origine du champ d'index, et non la valeur calculée par la fonction, il n'y a naturellement aucun moyen d'utiliser l'index.

• Conversion de type implicite pour l'index : équivalent à l'utilisation d'une nouvelle fonction

• La signification de OR dans la clause WHERE est qu'une seule des deux conditions est satisfaite, il est donc inutile qu'une seule colonne conditionnelle soit une colonne d'index Oui, tant que la colonne conditionnelle n'est pas une colonne d'index, une analyse complète de la table sera effectuée.

Proposition

1. Il existe un système qui n'est pas divisé en bases de données et en tables. Comment le concevoir pour que le système puisse basculer dynamiquement vers des bases de données et des tables ?

• Arrêter l'expansion (non recommandé)
• Plan de migration à double écriture : concevez le plan de structure de table étendue, puis implémentez la double écriture pour la base de données unique et la sous-base de données. Après avoir observé pendant une semaine qu'il n'y a aucun problème. , fermez le trafic de lecture de la base de données unique. Observez pendant un moment. Une fois qu'il continue de se stabiliser, fermez le trafic d'écriture de la base de données unique et passez en douceur à la sous-base de données et à la sous-table.

2. Comment concevoir un schéma de sous-base de données et de table capable d'étendre et de réduire dynamiquement la capacité ?

Principes

1. Quelles sont les étapes pour exécuter une instruction MySQL ?

• Les étapes permettant à la couche serveur d'exécuter SQL en séquence sont :
• Demande du client -> Connecteur (vérifier l'identité de l'utilisateur, accorder les autorisations) -> Requête du cache (retourner directement si le cache existe, effectuer les opérations suivantes s'il n'existe pas existent) - > Analyseur (analyse lexicale et analyse syntaxique de SQL) -> Optimiseur (sélectionne principalement la meilleure méthode de plan d'exécution pour exécuter l'optimisation SQL) -> , alors seulement vous pourrez utiliser l'interface fournie par ce moteur) -> Accédez à la couche moteur pour obtenir les retours de données (si la mise en cache des requêtes est activée, les résultats de la requête seront mis en cache).

2. Quel est le principe interne de l'ordre par tri ?

• MySQL allouera une mémoire (sort_buffer) pour chaque thread pour le tri. La taille de la mémoire est sort_buffer_size.
• Si la quantité de données à trier est inférieure à sort_buffer_size, le tri sera effectué en mémoire.
• Si la quantité de données triées est importante et ne peut pas être stockée en mémoire, les fichiers temporaires sur le disque seront utilisés pour faciliter le tri, également appelé tri externe.
• Lors de l'utilisation du tri externe, MySQL sera divisé en plusieurs fichiers temporaires distincts pour stocker les données triées, puis fusionnera ces fichiers en un seul gros fichier.

3.Principe de mise en œuvre du MVCC ?

• MVCC (Multiversion Concurrency Control) est un moyen de conserver plusieurs versions des mêmes données, réalisant ainsi un contrôle de concurrence. Lors de l'interrogation, recherchez les données de la version correspondante via la vue de lecture et la chaîne de versions.
• Fonction : Améliorer les performances de concurrence. Pour les scénarios à forte concurrence, MVCC est moins coûteux que les verrous au niveau des lignes.
• L'implémentation de MVCC repose sur la chaîne de versions, qui est implémentée via trois champs cachés de la table.
  • 1) DB_TRX_ID : identifiant de transaction actuel, la séquence temporelle de la transaction est jugée par la taille de l'identifiant de transaction.
  • 2) DB_ROLL_PRT : le pointeur de restauration pointe vers la version précédente de l'enregistrement de ligne actuel. Grâce à ce pointeur, plusieurs versions des données sont connectées ensemble pour former une chaîne de versions de journal d'annulation.
  • 3) DB_ROLL_ID : Clé primaire. Si la table de données n'a pas de clé primaire, InnoDB générera automatiquement une clé primaire.

4. Qu'est-ce que le tampon de changement et quelle est sa fonction ?

5. Comment MySQL garantit-il que les données ne sont pas perdues ?

• Tant que redolog et binlog garantissent les disques persistants, le mécanisme d'écriture binlog peut assurer la récupération des données après un redémarrage anormal de MySQL.
• redolog garantit que les données perdues peuvent être refaites après une exception système, et binlog archive les données pour garantir que les données perdues peuvent être récupérées.
• Écrivez le redolog avant l'exécution de la transaction. Pendant l'exécution de la transaction, le journal est d'abord écrit dans le cache binlog. Lorsque la transaction est soumise, le cache binlog est écrit dans le fichier binlog.

6. Pourquoi la taille du fichier du tableau reste-t-elle inchangée même après la suppression du tableau ?

• Après avoir supprimé l'élément de données, InnoDB marque la page A comme réutilisable
• Qu'en est-il de la commande delete pour supprimer les données entières de la table ? En conséquence, toutes les pages de données seront marquées comme réutilisables. Mais sur le disque, le fichier ne diminue pas.
• Les tableaux ayant subi un grand nombre d'ajouts, de suppressions et de modifications peuvent présenter des trous. Ces trous prennent également de la place, donc si ces trous peuvent être supprimés, l'objectif de réduire l'espace de la table peut être atteint.
• Reconstruire la table peut atteindre cet objectif. Vous pouvez utiliser la commande alter table A engine=InnoDB pour reconstruire la table.

7. Comparaison de trois formats de binlog

• Le journal binlog au format de ligne enregistre l'identifiant de clé primaire de la ligne d'opération et la valeur réelle de chaque champ, il n'y aura donc aucune incohérence dans les données d'opération primaires et de sauvegarde.
• instruction : l'instruction SQL source enregistrée
• mixte : les deux premiers sont mélangés, pourquoi avez-vous besoin d'un fichier au format mixte Parce que certains binlogs au format d'instruction peuvent provoquer une incohérence entre le primaire et la sauvegarde, le format de ligne est donc utilisé. Mais l’inconvénient du format ligne est qu’il prend beaucoup de place. MySQL a pris un compromis. MySQL déterminera lui-même si cette instruction SQL peut provoquer une incohérence entre les serveurs principal et secondaire. Si possible, il utilisera le format de ligne, sinon il utilisera le format d'instruction.

8. Règles de verrouillage MySQL

• Principe 1 : L'unité de base du verrouillage est le verrouillage par clé suivante, et le verrouillage par clé suivante est l'intervalle d'ouverture avant et de fermeture arrière.
• Principe 2 : Seuls les objets accédés lors du processus de recherche seront verrouillés
• Optimisation 1 : Pour des requêtes équivalentes sur l'index, lors du verrouillage de l'index unique, le verrou de la clé suivante dégénère en verrou de ligne.
• Optimisation 2 : Pour les requêtes équivalentes sur l'index, lors d'un parcours vers la droite et que la dernière valeur ne remplit pas la condition d'équivalence, le verrou de la touche suivante dégénère en un verrou d'espacement
• Un bug : une requête de plage sur un unique l'index accédera jusqu'à la première valeur qui ne satisfait pas à la condition.

9. Que sont les lectures sales, les lectures non répétables et les lectures fantômes ?

• « Lecture sale » : la lecture sale fait référence à la lecture de données non validées provenant d'autres transactions. La désengagement signifie que les données peuvent être annulées, ce qui signifie qu'elles peuvent ne pas être enregistrées dans la base de données à la fin, c'est-à-dire qu'elles le sont. n’existe pas. La lecture de données qui pourraient éventuellement ne pas exister est appelée lecture sale.
• « Lecture non répétable » : La lecture non répétable fait référence à la situation dans laquelle, au sein d'une transaction, les données lues au début sont incohérentes avec le même lot de données lu à tout moment avant la fin de la transaction.
• « Lecture fantôme » : la lecture fantôme ne signifie pas que les ensembles de résultats obtenus par deux lectures sont différents. L'objectif de la lecture fantôme est que l'état des données du résultat obtenu par une certaine opération de sélection ne peut pas prendre en charge les opérations commerciales ultérieures. Pour être plus précis : sélectionnez si un certain enregistrement existe. S'il n'existe pas, préparez-vous à insérer l'enregistrement. Cependant, lors de l'exécution de l'insertion, il s'avère que l'enregistrement existe déjà et ne peut pas être inséré à ce moment-là. se produit.

10. De quels types de verrous MySQL dispose-t-il ? Un verrouillage comme celui ci-dessus n'entraverait-il pas l'efficacité de la concurrence ?

• En termes de types de verrous, il existe des verrous partagés et des verrous exclusifs.
  • 1) Verrou partagé : également appelé verrou de lecture Lorsque l'utilisateur souhaite lire des données, un verrou partagé est ajouté aux données. Plusieurs verrous partagés peuvent être ajoutés en même temps.
  • 2) Verrou exclusif : également appelé verrou en écriture. Lorsque l'utilisateur souhaite écrire des données, un verrou exclusif est ajouté aux données. Un seul verrou exclusif peut être ajouté, et il est exclusif avec d'autres verrous exclusifs et verrous partagés.
• La granularité des verrous dépend du moteur de stockage spécifique. InnoDB implémente des verrous au niveau de la ligne, des verrous au niveau de la page et des verrous au niveau de la table.

• Leurs frais généraux de verrouillage vont de grands à petits, et leurs capacités de concurrence vont également de grandes à petites.

Framework

1. Quel est le principe de la réplication maître-esclave Mysql ?

• Les événements de mise à jour du Master (mise à jour, insertion, suppression) seront écrits dans bin-log dans l'ordre. Lorsque l'esclave est connecté au maître, la machine maître ouvrira le thread binlog dump pour l'esclave, et le thread lira le journal bin-log. bin-log中。当Slave连接到Master的后,Master机器会为Slave开启binlog dump线程,该线程会去读取bin-log日志。
• Slave连接到Master后,Slave库有一个I/O线程 通过请求binlog dump thread读取bin-log日志,然后写入从库的relay log日志中。
• Slave还有一个 SQL线程
Une fois l'esclave connecté au maître, la bibliothèque esclave dispose d'un thread d'E/S qui lit le journal bin-log en demandant le thread de vidage binlog, puis l'écrit dans le journal de relais dans le journal.

Slave dispose également d'un thread SQL, qui surveille le contenu du journal de relais en temps réel pour les mises à jour, analyse les instructions SQL dans le fichier et les exécute dans la base de données Slave.

2. Quelles sont les méthodes de synchronisation de réplication maître-esclave de Mysql ?
• Réplication asynchrone : La synchronisation maître-esclave MySQL est répliquée de manière asynchrone par défaut. Autrement dit, parmi les trois étapes ci-dessus, seule la première étape est synchrone (c'est-à-dire que Mater écrit le journal du journal bin), c'est-à-dire que la bibliothèque principale peut revenir avec succès au client après avoir écrit le journal binlog, sans attendre le journal binlog. log à transférer vers la bibliothèque esclave.
• Réplication synchrone : Pour la réplication synchrone, une fois que l'hôte maître envoie l'événement à l'hôte esclave, il déclenchera une attente jusqu'à ce que tous les nœuds esclaves (s'il y a plusieurs esclaves) renvoient des informations sur la réplication réussie des données vers le maître. Réplication semi-synchrone :
Pour la réplication semi-synchrone, une fois que l'hôte maître envoie l'événement à l'hôte esclave, il déclenchera une attente,

jusqu'à ce que l'un des nœuds esclaves

(s'il y a plusieurs esclaves) renvoie des informations sur la réplication réussie des données vers le maître.

3. Qu'est-ce qui cause le retard de synchronisation maître-esclave Mysql ? Comment l'optimiser ?
• Si le nœud maître exécute une transaction volumineuse, cela aura un impact plus important sur le délai maître-esclave

• Délai réseau, logs volumineux, trop d'esclaves

• Plusieurs threads sur le maître Écriture, le nœud esclave n'a qu'une synchronisation à un seul thread

• Problèmes de performances de la machine, si le nœud esclave utilise une "mauvaise machine"
  🎜🎜Des problèmes de conflit de verrouillage peuvent également entraîner une exécution lente du thread SQL de l'esclave🎜

4. Qu'est-ce qui cause le retard de synchronisation maître-esclave Mysql ? Comment l'optimiser ?

• Transactions importantes : divisez les transactions volumineuses en petites transactions et mettez à jour les données par lots
• Réduisez le nombre d'esclaves à 5 maximum et réduisez la taille d'une seule transaction
• Après Mysql 5.7, vous pouvez utiliser le multithread réplication, utilisez l'architecture de réplication MGR
• S'il y a des problèmes avec le disque, la carte raid ou la stratégie de planification, un seul délai d'E/S peut être très élevé. Vous pouvez utiliser la commande iostat pour vérifier la situation des E/S du disque de données DB et. puis portez des jugements supplémentaires
• Pour les problèmes de verrouillage, vous pouvez passer par Grab processlist et vérifier les tableaux liés aux verrous et aux transactions sous information_schema.

6. Que sont les journaux bin/redo log/undo log ?

• bin log est un fichier au niveau de la base de données Mysql. Il enregistre toutes les opérations qui modifient la base de données Mysql. Les instructions Select et show ne seront pas enregistrées.
• Les données à mettre à jour sont enregistrées dans le journal redo. Par exemple, si une donnée est soumise avec succès, elle ne sera pas immédiatement synchronisée sur le disque, mais sera d'abord enregistrée dans le journal redo. attendez le bon moment avant de vider le disque afin d'obtenir la durabilité du sexe de transaction.
• undo log est utilisé pour les opérations de rappel de données. Il conserve le contenu avant que l'enregistrement ne soit modifié. L'annulation des transactions peut être obtenue via le journal d'annulation, et MVCC peut être implémenté en remontant à une version spécifique des données basée sur le journal d'annulation.

Apprentissage recommandé : Tutoriel vidéo MySQL

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!