Partage de 101 compétences de débogage et d'optimisation dans MySQL

Avec de plus en plus d'applications alimentées par des bases de données , les gens ont poussé MySQL à ses limites. Voici 101 conseils pour régler et optimiser votreinstallation de 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 compétences de réglage et d'optimisation de MySQL

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 à ses limites. 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 : des matricesredondantes de disques bon marché, c'est-à-dire une 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 le coût du 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 une mise en cacheRAIDcontrôleur à grande vitesse et alimentée 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 retours de lignes redondants ou les requêtes recherche.
42. Indexez vos données.
43. N'abusez pas des index, des comparaisons et des requêtes.
44. Compressez le texte et les BLOBtypes de données – Pour enregistrer. espace et réduisez le nombre de lectures sur le disque.
45. UTF 8 et UTF16 sont tous deux inférieurs à l'efficacité d'exécution de latin1.
46. Utilisez les déclencheurs avec parcimonie.47. – pas de duplication inutile des données.
48. Utilisez des tableaux liés, pas des lignes étendues.
49. Faites attention aux types de données, utilisez le plus petit possible dans vos données réelles A.
50. fréquemment utilisé dans les requêtes, mais pas les données BLOB/TEXT, séparez les données BLOB/TEXT des autres données.
51 Vérifiez et optimisez fréquemment les tables.
52. Réécrivez fréquemment l'optimisation des tables InnoDB.
53. , supprimez l'index lors de l'ajout de colonnes, puis rajoutez l'index, ce qui sera plus rapide.
54. Utilisez différents moteurs de stockage pour différents besoins.
55. Utilisez des tables de journaux de moteur de stockage d'archives ou des tables d'audit - c'est le cas. plus efficace pour l'écriture.
56. Les données de session sont stockées dans le cache (memcache) au lieu de MySQL - le cache permet le remplissage automatique des valeurs 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 chaînes de longueur variable
- économise de l'espace sous forme de CHAR de longueur fixe, tandis que la longueur de VARCHAR n'est pas fixe (UTF8 n'est pas affecté par cela).58. le schéma change progressivement – ​​un petit changement peut avoir un impact énorme.59. Testez tous les schémas dans un environnement de développement, reflétez les modifications de production.
60. Ne modifiez pas les valeurs de manière aléatoire dans vos
fichiers de configuration.
, cela peut avoir des effets désastreux.61. Parfois, moins c'est plus dans les configurations MySQL 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. Gardez 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, n'utilisez pas UPDATE accompli.
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. Lorsque 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.
The Last 101 : Effectuer une surveillance MySQL : Monitis dévoile la première surveillance MySQL gratuite à la demande au monde.

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!