Comment optimiser les performances de MySQL ? Partage de conseils d'optimisation

Quand il s'agit d'optimiser les performances d'une base de données, le plus important est de choisir la bonne. Vous devez décider si votre application nécessite une base de données relationnelle ou non relationnelle. Même au sein d’un même genre, vous aurez le choix entre plusieurs options. Outre les bases de données relationnelles, vous pouvez trouver Oracle, MySQL, SQL Server et PostgreSQL. D'un autre côté, les bases de données non relationnelles ont introduit MongoDB, Cassandra et CouchDB.

Vous voudrez peut-être que je suggère d'utiliser une base de données non relationnelle pour des performances de lecture/écriture plus rapides. Cependant, avec quelques améliorations et ajustements, vous pouvez pousser une base de données relationnelle au-delà de ses limites connues. Ainsi, dans cet article, je vais vous présenter quelques conseils pour rendre votre base de données MySQL plus rapide.

Si vous vous demandez spécifiquement pourquoi vous devriez utiliser MySQL, la réponse est simple car il est gratuit, open source et très populaire dans la communauté PHP, alors qu'Oracle n'est pas largement utilisé en raison de son prix élevé. Les autres options sont moins populaires que MySQL.

- Configuration du serveur MySQL :

Eh bien, vous devez d'abord savoir où se trouvent les fichiers de configuration, en fonction de votre système d'exploitation. Sur les systèmes Linux, il se trouve dans "/etc/mysql/my.cnf".
Il est maintenant temps de choisir votre moteur InnoDB et MyISAM. Pour faciliter le choix, sachez qu'InnoDB est devenu le moteur par défaut dans MySQL 5.5 car il prend en charge "le verrouillage au niveau des lignes, les clés étrangères et les transactions", tandis que MyISAM ne prend en charge aucune des fonctionnalités mentionnées, ce qui le rend rarement utile dans programme d'applications modernes.
Après avoir sélectionné le bon moteur, il est temps de définir certaines variables de configuration dans le fichier my.cnf.

Variable max_connection : La variable

max_connection représente le nombre de connexions autorisées par l'application. La valeur par défaut est de 151 connexions, cependant, si vous obtenez le message d'erreur "Erreur MySQL, trop de connexions..." vous pouvez facilement augmenter ce nombre

最大连接数 = 170

innodb_buffer_pool_size variable :

Pour accélérer les choses, MySQL sera mis en cache dans la mémoire de votre serveur, cette variable indique à MySQL combien de gigaoctets il peut utiliser. Cette variable est utile si vous enregistrez de gros blobs dans la base de données. Vous pouvez définir cette valeur entre 80 et 90 % de la mémoire du serveur. Ainsi, si votre serveur dispose de 16 Go de mémoire, vous pouvez le définir sur 14 Go.

innodb_buffer_pool_size = 14GB

Variable innodb_io_capacity :

Cette variable indique à MySQL combien d'opérations d'entrée/sortie il peut utiliser, cela dépend de votre disque. Par exemple, un seul disque de 7 200 tr/min est limité à 200 E/S, tandis qu'un disque SSD d'entreprise est limité à 50 000 E/S. Vous pouvez facilement trouver les valeurs d'entrée/sortie via la ligne de commande de votre système d'exploitation et définir les variables à 90 % des E/S disponibles afin que MySQL n'utilise jamais trop d'opérations d'E/S.

innodb_io_capacity = 21000

Variablesquery_cache_limit et query_cache_size :

MySQL prend également en charge la mise en cache des données en mémoire, mais nous ne pouvons pas compter sur lui comme système de mise en cache, car chaque fois que votre programme écrit des données dans la table de la base de données, MySQL reconstruira l'intégralité du cache de requêtes. pour les tableaux. Donc, si votre programme a une charge élevée, le cache MySQL sera complètement inutile. Dans ce cas, il est préférable de définir les deux variables sur 0 pour économiser la surcharge du cache MySQL. Au lieu de cela, vous pouvez utiliser quelque chose comme Redis pour gérer le cache. cache.

query_cache_limit = 0

query_cache_size = 0

Journal des requêtes lentes :

Le journal des requêtes lentes vous montrera lesquelles de vos requêtes dépassent un seuil que vous définissez, éliminant ainsi le besoin de deviner quelle requête est la plus lente.
Tout d'abord, vous devez activer slow_query_log dans votre fichier de configuration. Sur le serveur Linux, ouvrez "/etc/mysql/my.cnf" ou le fichier équivalent sur votre système. slow_query_log 。在Linux服务器中，打开 「/etc/mysql/my.cnf」或者你系统上同等的文件。
并添加:

slow_query_log = 1

long_query_time = 1

那么，这两个选项将启用慢查询日志，并记录任何需要超过一秒的查询。如果你喜欢在一个表中而不是在一个文件中查看日志，你可以添加：

log_output = 'TABLE'

然后你可以在「slow_log」表中找到你的日志。你可以在那里看到所有执行超过一秒的慢查询的信息。这些信息包括查询的确切执行时间和受影响的行数，以及哪个用户执行的它。

查询优化

在你得到所有的慢查询后，你需要一种方式来优化它们，使它们更快。因此，你可以在查询语句前面加上「explain」关键词，从而获得相关查询的详情信息，例如：explain select * from users where active=1;Et ajoutez :

rrreee

Eh bien, ces deux options permettront une journalisation lente des requêtes et enregistreront toutes les requêtes qui prennent plus d'une seconde. Si vous préférez afficher les logs dans un tableau plutôt que dans un fichier, vous pouvez ajouter :

rrreee

Vous pourrez ensuite retrouver vos logs dans le tableau "slow_log". Vous pouvez y voir des informations sur toutes les requêtes lentes exécutées pendant plus d'une seconde. Ces informations incluent l'heure d'exécution exacte et le nombre de lignes affectées par la requête, ainsi que l'utilisateur qui l'a exécutée.

Optimisation des requêtes🎜🎜🎜Après avoir obtenu toutes vos requêtes lentes, vous avez besoin d'un moyen de les optimiser pour les rendre plus rapides. Par conséquent, vous pouvez ajouter le mot-clé "explain" devant l'instruction de requête pour obtenir des informations détaillées sur la requête concernée, par exemple : explain select * from users which active=1;🎜🎜"explain" mot-clé Peut vous aider à définir les index atteints par la requête et le nombre de lignes à interroger pour obtenir les données. Ces informations peuvent vous indiquer si vous devez créer davantage d'index ou restructurer les tables de la base de données. 🎜🎜🎜Dénormalisation et contraintes : 🎜🎜

La dénormalisation est le processus d'amélioration des performances de lecture en ajoutant des données redondantes ou en les regroupant. Par exemple, si vous avez une table « Produits » et une table « Catégorie », et chaque fois que vous interrogez la table « Produits », vous devez également obtenir le « Nom de catégorie » de chaque produit. Dans ce cas, vous pouvez utiliser « join » pour récupérer « category_name ». Cependant, cela signifie que chaque fois que l'utilisateur ouvre la page du produit, une requête de jointure complexe est exécutée. Par conséquent, vous pouvez envisager d'ajouter un « Nom de catégorie » au tableau « Produits ». Malgré les données redondantes, l'augmentation des performances de lecture en vaut la peine.

La méthode de non-normalisation peut rendre obsolète le « Nom de la catégorie » dans le tableau « Produit ». Vous devez donc définir une contrainte de "clé étrangère", mais vous devez être conscient qu'une "clé étrangère" ralentira légèrement les performances d'écriture car MySQL doit vérifier la contrainte avant d'écrire les données. Il est donc toujours de votre devoir de faire le meilleur choix.

Adresse originale en anglais : https://codeburst.io/database-performance-optimization-8d8407808b5b

Adresse de traduction : https://learnku.com/mysql/t/71571

[Recommandations associées : vidéo mysql Tutoriel】

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!