2024-07-12
한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina
Le contenu des logs MySQL est très important et est souvent demandé lors des entretiens. Dans le même temps, la maîtrise des connaissances liées aux journaux nous aidera également à comprendre les principes sous-jacents de MySQL et nous aidera à dépanner et à résoudre les problèmes si nécessaire.
Les types de journaux courants dans MySQL incluent principalement les catégories suivantes (pour le moteur de stockage InnoDB) :
Le journal binaire (binlog) et le journal des transactions (redo log et undo log) sont plus importants et nécessitent notre attention.
Le journal des requêtes lentes enregistre toutes les instructions de requête dont le temps d'exécution dépasse long_query_time (la valeur par défaut est 10 s, généralement définie sur 1 s). Il est souvent utilisé lors de la résolution d'un problème de requête SQL lente (le temps d'exécution SQL est trop long).
La recherche de SQL lent est la première étape pour optimiser les performances des instructions SQL. Utilisez ensuite la commande EXPLAIN pour analyser le SQL lent et obtenir des informations sur le plan d'exécution.
Vous pouvez utiliser les variables show comme la commande "slow_query_log" ; pour vérifier si le journal des requêtes lentes est activé. Il est désactivé par défaut.
Peut être activé par SET GLOBAL slow_query_log=ON
Le paramètre long_query_time définit le temps nécessaire à une requête avant de pouvoir être définie comme une requête lente. La valeur par défaut est 10 s. Vous pouvez la visualiser via la commande SHOW VARIABLES LIKE'%long_query_time%' ;
Il peut également être modifié : set global long_query_time = 12
Dans les projets réels, le journal des requêtes lentes peut être relativement volumineux et il n'est pas pratique de l'analyser directement. Nous pouvons utiliser l'outil officiel d'analyse et de réglage des requêtes lentes MySQL. mysqldumpslow . Mon blog est aussi simplement connecté à l'outil mysqldumpslow :
[MySQL] Outil mysqldumpslow - résumant les fichiers journaux de requêtes lentes-CSDN Blog
Il existe une variable dans MySQL qui enregistre le nombre actuel d'instructions de requête lentes. Vous pouvez utiliser show global status comme « %slo ».
w_queries%'; commande à afficher.
MySQL nous fournitEXPLIQUERcommande pour obtenir des informations sur le plan d’exécution.
Le plan d'exécution fait référence à la méthode d'exécution spécifique d'une instruction SQL après avoir été optimisée par l'optimiseur de requêtes MySQL. Les plans d'exécution sont généralement utilisés dans des scénarios tels que l'analyse et l'optimisation des performances SQL. Grâce aux résultats d'EXPLAIN, vous pouvez apprendre des informations telles que la séquence de requête de la table de données, le type d'opération de requête de données, les index qui peuvent être atteints, les index qui seront réellement atteints, le nombre de lignes d'enregistrement dans chaque donnée. Les tables sont interrogées, ainsi que d'autres informations. Plus précisément, nous pouvons optimiser SQL grâce à certaines méthodes courantes ci-dessous :
1. Évitez d'utiliser SELECT *
SELECT * consomme plus de CPU.
SELECT *Les champs inutiles augmentent la consommation des ressources de bande passante du réseau et le temps de transmission des données, en particulier les champs volumineux (tels que varchar,
blob, texte).
SELECT * ne peut pas utiliser l'optimiseur MySQL pour couvrir l'optimisation de l'index (la stratégie « index de couverture » basée sur l'optimiseur MySQL est extrêmement rapide et efficace, et constitue une méthode d'optimisation des requêtes hautement recommandée dans l'industrie)
SELECT <field list> peut réduire l'impact des modifications de la structure de la table.
2. Optimisation de la pagination
La pagination ordinaire prend un temps relativement court lorsque la quantité de données est faible.
Si la quantité de données devient importante, atteignant des millions, voire des dizaines de millions, la radiomessagerie ordinaire prendra beaucoup de temps.
Comment optimiser ? Vous pouvez modifier l'instruction SQL ci-dessus en une sous-requête.
Nous interrogeons d'abord la valeur de clé primaire correspondant au premier paramètre de limite, puis filtrons et limitons en fonction de cette valeur de clé primaire, afin que l'efficacité soit plus rapide. Cependant, cette méthode ne fonctionne que si les identifiants sont dans un ordre positif.
Cependant, le résultat de la sous-requête générera une nouvelle table, ce qui affectera les performances. Il convient d'éviter une utilisation intensive des sous-requêtes. De plus, cette méthode n’est applicable que lorsque l’ID est en séquence positive. Dans les scénarios de pagination complexes, il est souvent nécessaire de filtrer les ID qui remplissent les conditions via des conditions de filtrage. À ce stade, les ID sont discrets et discontinus.
3. Faites moins de jointures
Manuel de développement Alibaba :
Vous pouvez lire la discussion sur Zhihu :
https://www.zhihu.com/question/68258877
https://www.zhihu.com/question/682588774. Il est recommandé de ne pas utiliser de clés étrangères et de cascades
Manuel de développement Alibaba Java :
5. Choisissez le type de champ approprié
6. Essayez d'utiliser UNION ALL au lieu de UNION
UNION placera toutes les données des deux jeux de résultats dans une table temporaire, puis effectuera l'opération de déduplication, qui prend plus de temps et consomme plus de ressources CPU.
UNION ALL ne dupliquera plus l'ensemble de résultats et les données obtenues contiennent des éléments en double.
Toutefois, si les données en double ne sont pas autorisées dans le scénario commercial réel, UNION peut toujours être utilisé.
7. Opérations par lots
Pour les mises à jour des données dans la base de données, si des opérations par lots peuvent être utilisées, utilisez-les autant que possible pour réduire le nombre de requêtes sur la base de données et améliorer les performances.
8. Utilisez correctement les index
Il y a beaucoup de contenu dans cette section, qui sera présenté ultérieurement dans un blog séparé.
binlog (le journal binaire est un fichier journal binaire) enregistre principalement toutes les opérations qui ont été modifiées sur la base de données MSQL (toutes les instructions DDL et DML exécutées par la base de données), y compris les modifications de la structure de la table (CREATE, ALTER, DROP TABLE.), les données de la table. modifications ( INSERT.UPDATE, DELETE..), mais n'inclut pas SELECT, SHOW et autres opérations qui n'entraînent pas de modifications de la base de données.
Vous pouvez utiliser la commande show binaire logs pour afficher une liste de tous les journaux binaires :
Il existe 3 types de méthodes d'enregistrement binaire :
Par rapport au mode Row, le fichier journal en mode instruction est plus petit, la pression d'E/S du disque est également plus petite et les performances sont meilleures. Cependant, sa précision est pire que le mode Row.
Avant MySQL 5.1.5, le format de binlog était uniquement STATEMENT. 5.1.5 a commencé à prendre en charge binlog au format ROW À partir de la version 5.1.8, MySQL a commencé à prendre en charge binlog au format MIXED. Avant MySQL 5.7.7, le mode Statement était utilisé par défaut. MySQL5.7.7 utilise le mode Row par défaut.
Vous pouvez utiliser des variables show comme « %binlog format% » pour afficher le format utilisé par binlog ;
Le principal scénario d'application de binlog est la réplication maître-esclave, maître-maître et maître-esclave sont tous indissociables de binlog. Il faut s'appuyer sur Binlog pour synchroniser les données et garantir leur cohérence.
Le principe de la réplication maître-esclave est illustré dans la figure ci-dessous :
1. La bibliothèque principale écrit les modifications apportées aux données de la base de données dans binlog
2. Connectez la bibliothèque esclave à la bibliothèque principale
3. La bibliothèque esclave créera un thread I0 pour demander le binlog mis à jour à la bibliothèque principale.
4. La bibliothèque principale créera un thread de vidage du journal binaire pour envoyer le journal binaire, et le thread I/0 de la bibliothèque esclave est responsable de la réception. 5. Le thread I/0 de la bibliothèque esclave écrira le journal binaire reçu dans le relais. enregistrer.
6. Lisez le journal de relais à partir du thread SQL de la bibliothèque et synchronisez les données localement (c'est-à-dire exécutez à nouveau le SQL)
Pour le moteur de stockage InnoDB, lors de l'exécution d'une transaction, le journal sera d'abord écrit dans le binlogcache. Ce n'est que lorsque la transaction est soumise que le journal dans le binlogcache sera conservé dans le fichier binlog sur le disque. L'écriture en mémoire est plus rapide, et cela également pour des raisons d'efficacité.
Étant donné que le journal binaire d'une transaction ne peut pas être divisé, quelle que soit la taille de la transaction, il doit être écrit une fois, de sorte que le système alloue un bloc de mémoire à chaque thread en tant que cache du journal binaire. Nous pouvons contrôler la taille binlogcache d'un seul thread via le paramètre binlog_cache_size si le contenu du stockage dépasse ce paramètre, il doit être temporairement stocké sur le disque (swap).
Alors, quand le binlog est-il vidé sur le disque ? Vous pouvez contrôler le timing de vidage du biglog via le paramètre sync_binlog. La plage de valeurs est 0-N et la valeur par défaut est 0 :
·0 : Aucune exigence obligatoire, le système décidera quand écrire sur le disque.
·1 : Chaque fois qu'une transaction est soumise, le binlog doit être écrit sur le disque :
·N : Binlog sera écrit sur le disque toutes les N transactions.Risque de perte
Avant MySQL5.7, la valeur par défaut de sync_binlog était 0. Après MySQL5.7, la valeur par défaut de sync_binlog est 1. Généralement, il n'est pas recommandé de définir la valeur de sync_binlog sur 0. Si les exigences de performances sont relativement élevées ou si un goulot d'étranglement d'E/S disque se produit, la valeur de sync_binlog peut être augmentée de manière appropriée. Cependant, cela augmentera le risque de perte de données.
Lorsque vous rencontrez les trois situations suivantes, MySQL régénérera un nouveau fichier journal et le numéro de série du fichier sera incrémenté.
Nous savons que le moteur de stockage InnoD8 gère l'espace de stockage en unités de pages. Les données que nous insérons dans MySQL existent finalement dans la page. Pour être précis, c'est le type de page de données. Afin de réduire la surcharge des E/S du disque, il existe également une zone appelée Buffer Pool, qui existe en mémoire. Lorsque la page correspondant à nos données n'existe pas dans le Buffer Pool, MSQL mettra d'abord en cache la page sur le disque dans le Buffer Pool, afin que nous exploitions ensuite directement la page dans le Buffer Pool, ce qui améliore considérablement les performances de lecture et d'écriture. .
Une fois qu'une transaction est validée, nos modifications apportées à la page correspondante dans le pool de tampons peuvent ne pas être conservées sur le disque. À ce stade, si MySQL plante soudainement, les modifications apportées à cette transaction disparaîtront-elles directement ?
Évidemment non, car cela violerait évidemment la pérennité de la transaction.
Le moteur MySQLInnoDB utilise le journal redo pour garantir la durabilité des transactions. La principale tâche du journal de rétablissement est d'enregistrer les modifications de page, telles que le nombre d'octets qui ont été modifiés à un certain décalage sur une certaine page et le contenu spécifique modifié. Chaque enregistrement du journal redo contient le numéro d'espace table, le numéro de page de données, le décalage, les données modifiées spécifiques et peut même enregistrer la longueur des données modifiées (en fonction du type de journal redo).
Lorsque la transaction est validée, nous viderons le journal redo sur le disque selon la stratégie de vidage. De cette façon, même si MySQL plante, les données qui n'ont pas pu être écrites sur le disque peuvent être récupérées après le redémarrage, garantissant ainsi la durabilité. de la transaction. En d’autres termes, le journal redo offre des capacités de récupération en cas de crash de MySQL.
Redo Log enregistre toutes les opérations de modification dans la base de données. Lorsque la base de données effectue des opérations d'écriture (INSERT, UPDATE, DELETE), ces opérations seront d'abord enregistrées dans le Redo Log puis appliquées au fichier de données. De cette façon, même si le système tombe en panne avant que l'opération de modification des données ne soit complètement écrite sur le disque, Redo Log peut garantir que les données ne sont pas perdues. Lors de la récupération, la base de données refaire ces opérations de modification inachevées à partir du Redo Log pour garantir la cohérence des données.
Redo Log aide la base de données à retrouver un état cohérent après une panne du système ou une panne de courant inattendue. Pendant le processus de récupération, la base de données vérifiera les enregistrements dans le journal redo et réappliquera toutes les modifications de données soumises mais non persistantes aux fichiers de données pour récupérer les données.
Afin d'améliorer les performances des opérations d'écriture, les bases de données utilisent souvent des mécanismes de mise en cache (tels que des pools de mémoire tampon) pour stocker temporairement les opérations de modification en mémoire au lieu de les écrire immédiatement sur le disque. L'existence du Redo Log rend ce mécanisme de mise en cache possible, car tant que la persistance du Redo Log est assurée, il n'y a aucun risque de perte de données même si les données du cache n'ont pas été écrites sur le disque.
Lors de la validation d'une transaction, le journal redo dans le tampon de journal sera vidé sur le disque. Vous pouvez utiliser innodb_flush_log_at.
valider le contrôle des paramètres. Nous devons faire attention à définir la bonne politique de vidage innodb_flush_log_at_trx_commit. Selon la stratégie de vidage configurée dans MySQL, il peut y avoir des problèmes mineurs de perte de données après la panne de MySQL.
innodb_flush_log_at_trx_commit Il s'agit d'un paramètre de configuration important dans le moteur de stockage MySQL InnoDB. Il détermine les stratégies de vidage (flush) et d'écriture (écriture) du journal lorsqu'une transaction est soumise, affectant ainsi la durabilité et les performances des données. Il a trois valeurs, à savoir 0, 1 et 2. Chaque valeur représente une stratégie de brossage différente.
innodb_flush_log_at_trx_commit = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_log_at_trx_commit = 2
Résumé de la stratégie du pinceau
1. Le journal redo est écrit dans le tampon de journal mais n'a pas encore été écrit dans le cache de pages. À ce moment, la base de données tombe en panne et une perte de données se produit (cette perte de données peut se produire lorsque la valeur de la politique de vidage innodb_flush log_at trx_commit est égale à 1). 0);
2. Le journal redo a été écrit dans le cache de pages mais n'a pas encore été écrit sur le disque. Le système d'exploitation va planter et une perte de données peut se produire (cette perte de données peut se produire lorsque la valeur de la politique de vidage innodb2 flush log_at trx_commit est 2).
Undo Log (rollback log) est un journal utilisé dans le système de base de données pour enregistrer les opérations de modification des données. Il enregistre les opérations inverses (c'est-à-dire les opérations d'annulation) de toutes les opérations de modification sur les données pendant l'exécution de la transaction. Undo Log joue un rôle clé dans l'annulation des transactions. Grâce à Undo Log, les données peuvent être restaurées à l'état avant le début de la transaction, garantissant ainsi l'atomicité de la transaction.
L'atomicité d'une transaction signifie que toutes les opérations de la transaction sont soit toutes exécutées, soit aucune n'est exécutée. Undo Log garantit l'atomicité des transactions via les mécanismes suivants :
Enregistrer les opérations d'annulation Lors de l'exécution de la transaction, toute opération de modification des données enregistrera l'opération d'annulation correspondante dans le journal d'annulation avant la modification proprement dite. Par exemple, si une transaction met à jour la valeur d'une ligne d'enregistrements, l'ancienne valeur sera enregistrée dans le journal d'annulation avant la mise à jour.
Transaction d'annulation Si la transaction échoue pour une raison quelconque (telle qu'une erreur ou une annulation explicite), le système de base de données lit le journal d'annulation et restaure les données à l'état avant le démarrage de la transaction, conformément à l'opération d'annulation enregistrée. De cette façon, vous pouvez vous assurer que la transaction échouée n'a aucun impact sur la base de données, garantissant ainsi l'atomicité de la transaction.
Citation:
Il se consacre à la recherche technologique depuis plus de 30 ans et maîtrise divers langages tels que java, linux, javascript, php, css, etc. Il a apporté de nombreuses contributions dans le domaine de l'open source. station de documentation pour les développeurs pour partager certains problèmes de développement technologique pour référence future.
