Les compétences de débogage et d'optimisation MySQL les plus complètes

MySQL est une puissante base de données open source. Avec de plus en plus d'applications basées sur des bases de données, les utilisateurs ont poussé MySQL dans ses retranchements. Voici 101 conseils pour régler et optimiser votre installation MySQL. Certains conseils sont spécifiques à un environnement d'installation spécifique, mais les idées sont générales. Je les ai divisés en plusieurs catégories pour vous aider à maîtriser davantage de techniques de réglage et d'optimisation de MySQL.

Réglage du matériel et du système d'exploitation du serveur MySQL :

1. Avoir suffisamment de mémoire physique pour charger l'intégralité du fichier InnoDB en mémoire - lors de l'accès au fichier en mémoire. La vitesse est. beaucoup plus rapide que lors d'un accès sur le disque dur.
2. Évitez à tout prix d’utiliser Swap – l’échange se fait à partir du disque dur et est très lent.
3. Utilisez de la RAM alimentée par batterie (Remarque : la RAM est une mémoire vive).
4. Utilisez un RAID avancé (Remarque : matrices redondantes de disques bon marché, c'est-à-dire matrice de disques) – de préférence RAID10 ou supérieur.
5. Évitez RAID5 (Remarque : une solution de stockage qui équilibre les performances de stockage, la sécurité des données et les coûts de stockage) – il y a un prix à payer pour la vérification afin de garantir l'intégrité de la base de données.
6. Séparez le système d'exploitation et les partitions de données, non seulement logiquement, mais aussi physiquement - les opérations de lecture et d'écriture du système d'exploitation affecteront les performances de la base de données.
7. Placez l'espace temporaire MySQL, les journaux et les données de réplication dans différentes partitions - lorsque l'arrière-plan de la base de données lit et écrit à partir du disque, cela affectera les performances de la base de données.
8. Plus d’espace disque équivaut à des vitesses plus rapides.
9. Des disques meilleurs et plus rapides.
10. Utilisez SAS (Remarque : Serial Attached SCSI, Serial Attached SCSI) au lieu de SATA (Remarque : SATA, Serial Hard Drive).
11. Les disques durs plus petits sont plus rapides que les disques durs plus grands, en particulier dans les configurations RAID.
12. Utilisez un contrôleur RAID de cache alimenté par batterie.
13. Évitez d'utiliser des baies de disques logicielles.
14. Envisagez d'utiliser des cartes IO à semi-conducteurs (et non des lecteurs de disque) pour les partitions de données : ces cartes sont capables de prendre en charge des vitesses d'écriture de 2 Go/s pour presque n'importe quelle quantité de données.
15. Définissez la valeur de swappiness sur 0 sous Linux – Il n'y a aucune raison de mettre en cache les fichiers dans le serveur de base de données, ce qui constitue un avantage pour un serveur ou un ordinateur de bureau.
16. Si possible, utilisez noatime et nodirtime pour monter le système de fichiers - il n'y a aucune raison de mettre à jour l'heure de modification des fichiers de base de données consultés.
17. Utilisez le système de fichiers XFS - un système de fichiers plus rapide et plus petit que ext3 et doté de nombreuses options de journalisation, et il a été démontré qu'ext3 présente des problèmes de double mise en mémoire tampon avec MySQL.
18. Ajustez le journal du système de fichiers XFS et les variables de tampon – pour les normes de performances les plus élevées.
19. Dans les systèmes Linux, utilisez le planificateur planifié NOOP ou DEADLINE IO – Par rapport aux planificateurs planifiés NOOP et DEADLINE, les planificateurs planifiés CFQ et ANTICIPATORY sont très lents.
20. Utilisez un système d'exploitation 64 bits – Pour MySQL, la prise en charge et l'utilisation de la mémoire seront plus importantes.
21. Supprimez les packages d'installation et les démons inutilisés sur le serveur – moins d'utilisation des ressources.
22. Placez l'hôte utilisant MySQL et votre hôte MySQL dans un fichier hosts - pas de recherche DNS.
23. Ne forcez jamais l'arrêt d'un processus MySQL - vous endommageriez la base de données et le programme exécutant la sauvegarde.
24. Contribuez le serveur à MySQL – les processus en arrière-plan et autres services peuvent réduire le temps CPU occupé par la base de données.

Configuration MySQL :

25. Lors de l'écriture, utilisez innodb_flush_method=O_DIRECT pour éviter la double mise en mémoire tampon.
26. Évitez d'utiliser les systèmes de fichiers O_DIRECT et EXT3 – vous sérialiserez tout ce que vous écrivez.
27. Allouez suffisamment de innodb_buffer_pool_size pour charger l'intégralité du fichier InnoDB en mémoire – moins de lecture à partir du disque.
28. Ne définissez pas le paramètre innodb_log_file_size trop grand, cela vous permettra d'être plus rapide et de disposer de plus d'espace disque - supprimer plus de journaux est généralement une bonne chose et peut réduire le temps de restauration de la base de données après un crash de la base de données.
29. Ne mélangez pas les paramètres innodb_thread_concurrency et thread_concurrency – ces 2 valeurs sont incompatibles.
30. Allouez un très petit nombre au paramètre max_connections - trop de connexions utiliseront de la RAM et bloqueront le service MySQL.
31. Gardez thread_cache à un nombre relativement élevé, autour de 16 – pour éviter toute lenteur lors de l’ouverture des connexions.
32. Utilisez le paramètre skip-name-resolve – supprimez la recherche DNS.
33. Si vos requêtes sont répétées et que les données ne changent pas souvent, vous pouvez utiliser la mise en cache des requêtes. Mais si vos données changent fréquemment, l’utilisation du cache de requêtes vous coûtera cher.
34. Augmentez la valeur temp_table_size pour empêcher l'écriture sur le disque
35. Augmentez la valeur max_heap_table_size pour empêcher l'écriture sur le disque
36. Ne définissez pas la valeur sort_buffer_size trop élevée, sinon votre mémoire le fera. sera bientôt épuisé
37. Déterminez la taille de key_buffer en fonction des valeurs key_read_requests et key_reads. De manière générale, key_read_requests doit être supérieure à la valeur key_reads, sinon vous ne pouvez pas utiliser key_buffer efficacement
38. mais si vous souhaitez conserver la valeur par défaut (1), vous devez alors garantir l'intégrité des données et vous devez également vous assurer que la réplication ne prend pas de retard.
39. Vous devez disposer d'un environnement de test pour tester votre configuration et la redémarrer fréquemment sans affecter la production normale.

Optimisation du mode MySQL :

40. Gardez votre base de données organisée.
41. Archivage des anciennes données – supprimez les lignes redondantes pour les requêtes de retour ou de recherche.
42. Indexez vos données.
43. N'abusez pas des index, des comparaisons et des requêtes.
44. Compressez les types de données texte et BLOB – pour économiser de l'espace et réduire les lectures sur le disque.
45. UTF 8 et UTF16 ont une efficacité d'exécution inférieure à celle de latin1.
46. Utilisez les déclencheurs avec parcimonie.
47. Réduisez les données redondantes au minimum – ne répétez pas les données inutiles.
48. Utilisez des tables liées au lieu de lignes étendues. .
49. Faites attention aux types de données et utilisez le plus petit possible dans vos données réelles.
50. Si d'autres données sont souvent utilisées dans les requêtes, et si les données BLOB/TEXT ne le sont pas, séparez le BLOB/. Données TEXTE à partir d'autres données.
51. Vérifiez et optimisez fréquemment la table.
52. Réécrivez fréquemment l'optimisation de la table InnoDB.
53. Parfois, lors de l'ajout, supprimez l'index lorsque la colonne est ajoutée, puis ajoutez l'index, ce qui sera plus rapide.
54. Utilisez différents moteurs de stockage pour différents besoins.
55. Utilisez les tables de journaux du moteur de stockage d'archives ou les tables d'audit - c'est une session d'écriture plus efficace.
56. les données sont stockées dans le cache (memcache) plutôt que dans MySQL – le cache permet le remplissage automatique et vous empêche de créer des données spatio-temporelles difficiles à lire et à écrire sur MySQL.
57. Utilisez VARCHAR au lieu de CHAR lors du stockage de variables. Chaînes de longueur – économise de l'espace car CHAR est de longueur fixe, tandis que VARCHAR n'est pas de longueur fixe (UTF8 n'est pas affecté par cela).
Changements de modèle progressifs – Un petit changement peut avoir un impact énorme.
. 59. Testez tous les modes dans un environnement de développement, en reflétant les changements de production.
60 Ne modifiez pas les valeurs de vos fichiers de configuration de manière aléatoire, cela peut avoir un impact désastreux.
Parfois, moins c'est plus dans MySQL. configs.
62. En cas de doute, utilisez un fichier de configuration MySQL commun.

Optimisation des requêtes :

63. Utilisez les journaux de requêtes lentes pour découvrir les requêtes lentes.
64. Utilisez le plan d'exécution pour déterminer si la requête s'exécute normalement.
65. Testez toujours vos requêtes pour voir si elles fonctionnent de manière optimale – les performances changeront toujours avec le temps.
66. Évitez d'utiliser count(*) sur toute la table, cela pourrait verrouiller toute la table.
67. Rendre les requêtes cohérentes afin que les requêtes similaires ultérieures puissent utiliser le cache de requêtes.
68. Utilisez GROUP BY au lieu de DISTINCT le cas échéant.
69. Utilisez des colonnes indexées dans les clauses WHERE, GROUP BY et ORDER BY.
70. Gardez les index simples et n’incluez pas la même colonne dans plusieurs index.
71. Parfois, MySQL utilisera le mauvais index, dans ce cas, utilisez USE INDEX.
72. Vérifiez le problème d'utilisation de SQL_MODE=STRICT.
73. Pour les champs d'index avec moins de 5 enregistrements, utiliser LIMIT dans UNION n'est pas OR
74 Afin d'éviter SELECT avant la mise à jour, utilisez INSERT ON DUPLICATE KEY ou INSERT IGNORE, et n'utilisez pas UPDATE. accomplir.
75. N'utilisez pas MAX, utilisez les champs d'index et la clause ORDER BY.
76. Évitez d'utiliser ORDER BY RAND().
77. LIMIT M, N peut en fait ralentir les requêtes dans certains cas, à utiliser avec parcimonie.
78. Utilisez UNION au lieu de la sous-requête dans la clause WHERE.
79. Pour les MISES À JOUR, utilisez le MODE PARTAGE pour empêcher les verrouillages exclusifs.
80. Avant de redémarrer MySQL, pensez à réchauffer votre base de données pour vous assurer que vos données sont en mémoire et que les requêtes sont rapides.
81. Utilisez DROP TABLE, CREATE TABLE DELETE FROM pour supprimer toutes les données de la table.
82. Lorsque vous interrogez les données dont vous avez besoin avec des données minimisées, l'utilisation de * prend beaucoup de temps.
83. Envisagez des connexions persistantes au lieu de connexions multiples pour réduire les frais généraux.
84. Requêtes de référence, y compris l'utilisation de la charge sur le serveur, parfois une simple requête peut affecter d'autres requêtes.
85. À mesure que la charge augmente sur votre serveur, utilisez SHOW PROCESSLIST pour afficher les requêtes lentes et problématiques.
86. Testez toutes les requêtes suspectes sur les données d'image générées dans l'environnement de développement.

Processus de sauvegarde MySQL :

87. Sauvegarde à partir du serveur de réplication secondaire.
88. Arrêtez la réplication pendant les sauvegardes pour éviter les incohérences dans les dépendances de données et les contraintes de clé étrangère.
89. Arrêtez complètement MySQL et effectuez une sauvegarde du fichier de base de données.
90. Si vous utilisez le dump MySQL pour la sauvegarde, veuillez également sauvegarder les fichiers journaux binaires - assurez-vous qu'il n'y a pas d'interruption dans la réplication.
91. Ne faites pas confiance aux instantanés LVM – cela risque de créer des incohérences de données qui vous causeront des problèmes à l’avenir.
92. Pour faciliter la récupération d'une seule table, exportez les données dans des unités de table – si les données sont isolées des autres tables.
93. Veuillez utiliser –opt lorsque vous utilisez mysqldump.
94. Vérifiez et optimisez les tables avant de sauvegarder.
95. Pour une importation plus rapide, les contraintes de clé étrangère sont temporairement désactivées lors de l'importation.
96. Pour une importation plus rapide, la détection d'unicité est temporairement désactivée lors de l'importation.
97. Calculez la taille de la base de données, des tables et des index après chaque sauvegarde pour mieux surveiller la croissance de la taille des données.
98. Surveillez les instances de réplication pour détecter les erreurs et la latence via des scripts de planification automatisés.
99. Effectuez des sauvegardes régulières.
100. Testez régulièrement vos sauvegardes.
Dernier 101 : Exécuter la surveillance MySQL : Monitis dévoile la première surveillance MySQL gratuite à la demande au monde.

Recommandations associées :

Partage d'expérience de débogage de point d'arrêt js à voir absolument

Partage de 5 compétences de débogage nécessaires au débogage JavaScript

Quelles sont les méthodes de débogage js

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!