recherche
Maisonbase de donnéestutoriel mysqlComment optimiser MySQL ? optimisation liée à MySQL

1 Optimisation liée à la couche matérielle

1.1, liée au CPU

Dans les paramètres du BIOS du serveur, vous pouvez ajuster les configurations suivantes afin de maximiser les performances du CPU , ou Évitez les problèmes NUMA classiques :

1. Sélectionnez le mode Performance Per Watt Optimized (DAPC) pour maximiser les performances du processeur. N'envisagez pas d'économiser lors de l'exécution de services comme DB qui nécessitent généralement. une grande quantité de calculs. Éteignez

2. Désactivez les options telles que C1E et C States, également pour améliorer l'efficacité du processeur ; > 3. Fréquence de fréquence de la mémoire) sélectionnez Performances maximales (meilleures performances)

4. Dans le menu des paramètres de mémoire, activez l'entrelacement de nœuds pour éviter les problèmes NUMA ; 🎜> 1.2. Liés aux E/S disque

Voici quelques mesures qui peuvent être optimisées pour les E/S disque dans l'ordre d'amélioration des performances IOPS :

1. Utilisez des périphériques SSD ou PCIe SSD pour obtenir au moins des centaines, voire des milliers de fois d'amélioration des IOPS

2. Achetez une carte réseau équipée de CACHE et Modules BBU, qui peuvent améliorer considérablement les IOPS (se réfère principalement aux disques mécaniques, à l'exclusion des SSD ou SSD PCIe. Dans le même temps, l'état de santé des modules CACHE et BBU doit être vérifié régulièrement pour garantir que les données ne seront pas perdues dans un accident);

 3 . Lorsqu'il y a une carte réseau, définissez la stratégie d'écriture du tableau sur WB, ou même FORCE WB (s'il y a une double protection d'alimentation ou si les exigences de sécurité des données sont remplies). pas particulièrement élevé), il est strictement interdit d'utiliser la stratégie WT. Et la stratégie de lecture anticipée de matrice fermée est fondamentalement inutile et de peu d'utilité

 4. Utilisez autant que possible RAID-10 au lieu de RAID-5

 ; 5. Si vous utilisez un disque mécanique, essayez d'en choisir un à haute vitesse, par exemple, choisissez un disque de 15 KRPM au lieu d'un disque de 7,2 KRPM, ce qui ne vaut pas quelques dollars

2 Optimisations liées à la couche système


2.1. Optimisation de la couche du système de fichiers

Au niveau de la couche du système de fichiers, les mesures suivantes peuvent améliorer considérablement les performances IOPS :

1. Utilisez les deux planificateurs d'E/S date limite/noop. N'utilisez pas cfq (il ne convient pas à l'exécution de services DB)

2. . Lorsque vous utilisez le système de fichiers xfs, n'utilisez pas ext3 ; ext4 est à peine disponible, mais si le volume d'activité est important, vous devez utiliser xfs ; paramètre ajouté : options noatime, nodiratime, nobarrier (nobarrier est unique au système de fichiers xfs)


 2.2, autres optimisations des paramètres du noyau

Définissez des valeurs appropriées pour les paramètres clés du noyau afin de réduire la tendance au swap et d'éviter de grandes fluctuations de la mémoire et des E/S du disque, entraînant des pics de charge instantanés :

1 . Réglez vm.swappiness sur environ 5-10, voire 0 (veillez à le définir sur 0 pour RHEL 7 ou supérieur, à moins que vous n'autorisiez la suppression du MOO) pour réduire les risques d'utilisation de SWAP ;
 2. Définissez vm.dirty_background_ratio sur 5-10 et définissez vm.dirty_ratio sur environ le double pour garantir que les données sales peuvent être continuellement vidées sur le disque et éviter les écritures d'E/S instantanées. similaire à innodb_max_dirty_pages_pct dans MySQL);

3. Définissez net.ipv4.tcp_tw_recycle et net.ipv4.tcp_tw_reuse sur 1 pour réduire TIME_WAIT et améliorer l'efficacité de TCP

4. Quant aux deux paramètres de read_ahead_kb et nr_requests transmis sur Internet, après tests, j'ai constaté qu'ils ont peu d'impact sur l'environnement OLTP où la lecture et l'écriture sont principalement mélangées (cela devrait être sur la lecture et l'écriture). Les scènes sensibles sont plus efficaces), mais peut-être qu'il y a quelque chose qui ne va pas avec ma méthode de test, vous pouvez l'ajuster à votre propre discrétion

;

3 optimisations liées aux couches MySQL


3.1. À propos de la sélection de version

La version officielle s'appelle ORACLE MySQL Il n'y a rien à dire à ce sujet je crois. la plupart des gens le sélectionneront.

Personnellement, je recommande fortement de choisir la version de la branche Percona. Il s'agit d'une version de branche MySQL relativement mature et excellente qui a apporté de nombreuses améliorations en termes d'amélioration des performances, de fiabilité et de gestion. Il est fondamentalement entièrement compatible avec la version officielle d'ORACLE MySQL et ses performances ont été améliorées d'environ 20 % ou plus, je le recommande donc en premier et je l'utilise depuis 2008.

Une autre version de branche importante est MariaDB. Il est en fait inapproprié de dire que MariaDB est une version de branche car son objectif est de remplacer ORACLE MySQL. Il apporte principalement de nombreuses améliorations au niveau du code source sur la couche originale du serveur MySQL, et constitue également une version de branche très fiable et excellente. Cependant, cela a également entraîné de nouvelles fonctionnalités représentées par GTID qui sont incompatibles avec la version officielle (à partir de MySQL 5.7, le mode GTID est également pris en charge pour être activé ou désactivé dynamiquement en ligne, étant donné que la grande majorité des gens suivront toujours). la version officielle, par conséquent, MariaDB n'est pas recommandée en premier.


3.2. Suggestions pour ajuster les options des paramètres les plus importants

Il est recommandé d'ajuster les paramètres clés suivants pour obtenir de meilleures performances (vous pouvez utiliser le générateur my.cnf génère un modèle de fichier de configuration) :

1. Si vous choisissez la version Percona ou MariaDB, il est fortement recommandé d'activer la fonctionnalité de pool de threads, qui peut améliorer les performances en cas de concurrence élevée. conditions. Il n’y aura pas de grosse baisse. De plus, il existe la fonction extra_port, très pratique et qui peut sauver des vies dans les moments critiques. Une autre fonctionnalité importante est la fonction QUERY_RESPONSE_TIME, qui nous permet également d'avoir une idée intuitive de la distribution globale du temps de réponse SQL

 2. Définissez default-storage-engine=InnoDB ; Le moteur InnoDB est utilisé par défaut. Il est fortement déconseillé d'utiliser le moteur MyISAM. Le moteur InnoDB peut certainement répondre à plus de 99% des scénarios métiers

3. Ajustez le innodb_buffer_pool_size ; size S'il s'agit d'une seule instance et absolument. Si la plupart d'entre elles sont des tables du moteur InnoDB, vous pouvez envisager de les définir à environ 50 % à 70 % de la mémoire physique

4. Définissez les valeurs de innodb_flush_log_at_trx_commit et sync_binlog en fonction des besoins réels. Si les données ne peuvent pas être perdues, définissez les deux sur 1. Si une légère perte de données est autorisée, ils peuvent être définis respectivement sur 2 et 10. Et s'il n'est pas nécessaire de se soucier de la perte des données (par exemple, sur l'esclave, elles seront refaites de toute façon), alors tout peut être mis à 0. Le degré dans lequel les performances de la base de données sont affectées par ces trois valeurs de paramètre est : élevé, moyen et faible, c'est-à-dire que la première rendra la base de données la plus lente et la dernière sera l'inverse

 5, définissez innodb_file_per_table = 1, utilisez un espace table indépendant, je ne vois vraiment aucun avantage à utiliser l'espace table partagé. N'utilisez pas les 10 M par défaut, sinon ce sera grandement ; affecté lorsqu'il y a un nombre élevé de transactions simultanées ;

7. Définissez innodb_log_file_size=256M, définissez innodb_log_files_in_group=2, ce qui peut essentiellement satisfaire 90 % ou plus de scénarios
8. Définissez long_query_time = 1. Dans la version 5.5 et supérieure, il peut être défini à moins de 1. Il est recommandé de le définir sur 0,05 (50 millisecondes) pour enregistrer ces exécutions SQL plus lent, utilisé pour l'analyse ultérieure et dépannage ;

9. En fonction des besoins réels de l'entreprise, ajustez de manière appropriée max_connection (nombre maximum de connexions) et max_connection_error (nombre maximum d'erreurs, il est recommandé de le définir sur 10 Plus de dix mille, et les paramètres open_files_limit, innodb_open_files, table_open_cache et table_definition_cache peuvent être définis à environ 10 fois la taille de max_connection

10. Un malentendu courant est de définir tmp_table_size et max_heap_table_size est relativement grand, et je l'ai vu défini sur 1G. Ces deux options sont allouées pour chaque session de connexion, donc ne les définissez pas trop grandes, sinon cela provoquera facilement d'autres options au niveau de la session de connexion telles que : sort_buffer_size, join_buffer_size, vous également ; il faut faire attention à ne pas définir read_buffer_size, read_rnd_buffer_size, etc. trop grand ;

11. Puisqu'il a été recommandé de ne plus utiliser le moteur MyISAM, vous pouvez définir key_buffer_size sur environ 32M, et il est fortement recommandé de le fermer. fonction de cache de requêtes


 3.3. À propos des spécifications de conception de schéma et des suggestions d'utilisation de SQL

Ce qui suit répertorie plusieurs spécifications de conception de schéma courantes et des suggestions d'utilisation de SQL qui peuvent aider à améliorer l'efficacité de MySQL :

1. Toutes les tables InnoDB sont conçues avec une colonne à incrémentation automatique qui n'a aucun objectif commercial comme clé primaire. Cela est vrai pour la plupart des scénarios. Peu de tables InnoDB sont véritablement en lecture seule. . Si c'est le cas, il est préférable d'utiliser TokuDB

2. Tant que la longueur du champ répond aux besoins, choisissez la plus petite longueur possible. De plus, essayez d'ajouter des contraintes NOT NULL aux attributs de champ, ce qui peut améliorer les performances dans une certaine mesure

3. Essayez de ne pas utiliser les types TEXT/BLOB si nécessaire ; Il est recommandé de diviser le tableau en sous-types, ne le rassemblez pas avec le tableau principal pour éviter de mauvaises performances de lecture lors de SELECT *.

4. Lors de la lecture des données, sélectionnez uniquement les colonnes dont vous avez besoin. Ne sélectionnez pas * à chaque fois pour éviter de graves problèmes de lecture aléatoire, notamment lors de la lecture de certaines colonnes TEXTE/BLOB ;

5. Lors de la création d'un index sur une colonne VARCHAR(N), il suffit généralement de prendre environ 50 % (ou même moins) de sa longueur pour créer un index de préfixe pour atteindre plus de 80 % des besoins de requête. , il n'est pas nécessaire de créer un index complet pour la colonne entière

6. Dans des circonstances normales, les performances des sous-requêtes sont relativement médiocres, et c'est le cas. Il est recommandé de le remplacer par l'écriture JOIN ;

 7. Lors d'une requête avec des jointures multi-tables, les types de champs associés doivent être aussi cohérents que possible et tous doivent avoir des index ; cela fait référence à l'ensemble de résultats filtré, pas nécessairement à la petite quantité de données dans la table entière) en tant que table pilote

9. Lorsque plusieurs tables sont jointes et triées, le champ de tri ; Il doit être dans la table motrice, sinon la colonne de tri ne peut pas utiliser l'index

10. Utilisez plus d'index composés et moins d'index indépendants multiples, surtout si une cardinalité est trop petite ; ( Par exemple, si le nombre total de valeurs uniques de la colonne est inférieur à 255, ne créez pas d'index indépendant ; Ensemble de résultats, l'efficacité sera bien supérieure ;


 3.4. Autres suggestions

D'autres suggestions sur la gestion et la maintenance de MySQL sont :

1. Habituellement, une seule table Le la taille physique ne dépasse pas 10 Go, le nombre de lignes dans une seule table ne dépasse pas 100 millions et la longueur moyenne des lignes ne dépasse pas 8 Ko. Si les performances de la machine sont suffisantes, MySQL peut gérer complètement cette quantité de données. vous devez vous soucier des problèmes de performances. Cette suggestion consiste principalement à considérer que le DDL EN LIGNE est plus cher

2. Ne vous inquiétez pas trop du fait que le processus mysqld prend trop de mémoire ; tant que la suppression du MOO ne se produit pas et que beaucoup de SWAP sont utilisés, tout ira bien

3. Dans le passé, le but d'exécuter plusieurs instances sur une seule machine était de maximiser l'utilisation des ressources informatiques. Si une seule instance peut déjà consommer la plupart des ressources informatiques, il n'est pas nécessaire d'exécuter plusieurs instances

 4. Utilisez régulièrement pt-duplicate-key ; -checker pour vérifier et supprimer les index en double. Utilisez régulièrement l'outil pt-index-usage pour vérifier et supprimer les index avec une très faible fréquence d'utilisation

5. Collectez régulièrement le journal des requêtes lentes et utilisez l'outil pt-query-digest pour l'analyse ; , qui peut être combiné avec Le système Anemometer effectue une gestion des requêtes lentes pour analyser les requêtes lentes et effectuer un travail d'optimisation ultérieur

6. Vous pouvez utiliser pt-kill pour supprimer les requêtes SQL à long terme ; . Il existe une option dans la version Percona. innodb_kill_idle_transaction peut également implémenter cette fonction

7. Utilisez pt-online-schema-change pour compléter les exigences DDL EN LIGNE

8. Utilisez régulièrement pt-table-checksum et pt-table-sync pour vérifier et réparer les différences de données dans la réplication maître-esclave MySQL

Écrit à la fin : Cette référence d'optimisation, grande Dans certains cas, j'ai introduit des scénarios applicables. Si votre scénario d'application est différent de ce qui est décrit dans cet article, il est recommandé de l'ajuster en fonction de la situation réelle au lieu de le copier. cela mécaniquement. Les questions et les suggestions sont les bienvenues, mais les résistances habituelles sans passer par le cerveau sont rejetées.

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!

Déclaration
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn
MySQL: Compétences essentielles pour les débutants à maîtriserMySQL: Compétences essentielles pour les débutants à maîtriserApr 18, 2025 am 12:24 AM

MySQL convient aux débutants pour acquérir des compétences de base de données. 1. Installez les outils MySQL Server et Client. 2. Comprendre les requêtes SQL de base, telles que SELECT. 3. 掌握数据操作: : 创建表、插入、更新、删除数据。 4. 学习高级技巧: : 子查询和窗口函数。 5. 调试和优化: : 检查语法、使用索引、避免 Sélectionner * , 并使用 Limite。

MySQL: données structurées et bases de données relationnellesMySQL: données structurées et bases de données relationnellesApr 18, 2025 am 12:22 AM

MySQL gère efficacement les données structurées par la structure de la table et la requête SQL, et met en œuvre des relations inter-tableaux à travers des clés étrangères. 1. Définissez le format de données et tapez lors de la création d'une table. 2. Utilisez des clés étrangères pour établir des relations entre les tables. 3. Améliorer les performances par l'indexation et l'optimisation des requêtes. 4. Bases de données régulièrement sauvegarde et surveillent régulièrement la sécurité des données et l'optimisation des performances.

MySQL: fonctionnalités et capacités clés expliquésMySQL: fonctionnalités et capacités clés expliquésApr 18, 2025 am 12:17 AM

MySQL est un système de gestion de base de données relationnel open source qui est largement utilisé dans le développement Web. Ses caractéristiques clés incluent: 1. Prend en charge plusieurs moteurs de stockage, tels que InNODB et Myisam, adaptés à différents scénarios; 2. Fournit des fonctions de réplication à esclave maître pour faciliter l'équilibrage de la charge et la sauvegarde des données; 3. Améliorez l'efficacité de la requête grâce à l'optimisation des requêtes et à l'utilisation d'index.

Le but de SQL: interagir avec les bases de données MySQLLe but de SQL: interagir avec les bases de données MySQLApr 18, 2025 am 12:12 AM

SQL est utilisé pour interagir avec la base de données MySQL pour réaliser l'ajout de données, la suppression, la modification, l'inspection et la conception de la base de données. 1) SQL effectue des opérations de données via des instructions SELECT, INSERT, UPDATE, DELETE; 2) Utiliser des instructions Create, Alter, Drop pour la conception et la gestion de la base de données; 3) Les requêtes complexes et l'analyse des données sont mises en œuvre via SQL pour améliorer l'efficacité de la prise de décision commerciale.

MySQL pour les débutants: commencer la gestion de la base de donnéesMySQL pour les débutants: commencer la gestion de la base de donnéesApr 18, 2025 am 12:10 AM

Les opérations de base de MySQL incluent la création de bases de données, les tables et l'utilisation de SQL pour effectuer des opérations CRUD sur les données. 1. Créez une base de données: CreatedAtAbaseMy_First_DB; 2. Créez un tableau: CreateTableBooks (idIntauto_inCmentPrimaryKey, TitleVarchar (100) notnull, AuthorVarchar (100) notnull, publied_yearint); 3. Données d'insertion: INSERTINTOBOOKS (titre, auteur, publié_year) VA

Rôle de MySQL: Bases de données dans les applications WebRôle de MySQL: Bases de données dans les applications WebApr 17, 2025 am 12:23 AM

Le rôle principal de MySQL dans les applications Web est de stocker et de gérer les données. 1.MySQL traite efficacement les informations utilisateur, les catalogues de produits, les enregistrements de transaction et autres données. 2. Grâce à SQL Query, les développeurs peuvent extraire des informations de la base de données pour générer du contenu dynamique. 3.MySQL fonctionne basé sur le modèle client-serveur pour assurer une vitesse de requête acceptable.

MySQL: Construire votre première base de donnéesMySQL: Construire votre première base de donnéesApr 17, 2025 am 12:22 AM

Les étapes pour construire une base de données MySQL incluent: 1. Créez une base de données et une table, 2. Insérer des données et 3. Conduisez des requêtes. Tout d'abord, utilisez les instructions CreateDatabase et CreateTable pour créer la base de données et la table, puis utilisez l'instruction InsertInto pour insérer les données, et enfin utilisez l'instruction SELECT pour interroger les données.

MySQL: une approche adaptée aux débutants du stockage de donnéesMySQL: une approche adaptée aux débutants du stockage de donnéesApr 17, 2025 am 12:21 AM

MySQL convient aux débutants car il est facile à utiliser et puissant. 1.MySQL est une base de données relationnelle et utilise SQL pour les opérations CRUD. 2. Il est simple à installer et nécessite la configuration du mot de passe de l'utilisateur racine. 3. Utilisez l'insertion, la mise à jour, la suppression et la sélection pour effectuer des opérations de données. 4. OrderBy, où et jointure peut être utilisé pour des requêtes complexes. 5. Le débogage nécessite de vérifier la syntaxe et d'utiliser Expliquez pour analyser la requête. 6. Les suggestions d'optimisation incluent l'utilisation d'index, le choix du bon type de données et de bonnes habitudes de programmation.

See all articles

Outils d'IA chauds

Undresser.AI Undress

Undresser.AI Undress

Application basée sur l'IA pour créer des photos de nu réalistes

AI Clothes Remover

AI Clothes Remover

Outil d'IA en ligne pour supprimer les vêtements des photos.

Undress AI Tool

Undress AI Tool

Images de déshabillage gratuites

Clothoff.io

Clothoff.io

Dissolvant de vêtements AI

AI Hentai Generator

AI Hentai Generator

Générez AI Hentai gratuitement.

Article chaud

R.E.P.O. Crystals d'énergie expliqués et ce qu'ils font (cristal jaune)
1 Il y a quelques moisBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Meilleurs paramètres graphiques
1 Il y a quelques moisBy尊渡假赌尊渡假赌尊渡假赌
Will R.E.P.O. Vous avez un jeu croisé?
1 Il y a quelques moisBy尊渡假赌尊渡假赌尊渡假赌

Outils chauds

Bloc-notes++7.3.1

Bloc-notes++7.3.1

Éditeur de code facile à utiliser et gratuit

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Puissant environnement de développement intégré PHP

VSCode Windows 64 bits Télécharger

VSCode Windows 64 bits Télécharger

Un éditeur IDE gratuit et puissant lancé par Microsoft

MantisBT

MantisBT

Mantis est un outil Web de suivi des défauts facile à déployer, conçu pour faciliter le suivi des défauts des produits. Cela nécessite PHP, MySQL et un serveur Web. Découvrez nos services de démonstration et d'hébergement.

Listes Sec

Listes Sec

SecLists est le compagnon ultime du testeur de sécurité. Il s'agit d'une collection de différents types de listes fréquemment utilisées lors des évaluations de sécurité, le tout en un seul endroit. SecLists contribue à rendre les tests de sécurité plus efficaces et productifs en fournissant facilement toutes les listes dont un testeur de sécurité pourrait avoir besoin. Les types de listes incluent les noms d'utilisateur, les mots de passe, les URL, les charges utiles floues, les modèles de données sensibles, les shells Web, etc. Le testeur peut simplement extraire ce référentiel sur une nouvelle machine de test et il aura accès à tous les types de listes dont il a besoin.