Comment optimiser MySQL: index, requêtes lentes, configuration

MySQL reste la base de données relationnelle la plus populaire au monde, mais c'est aussi la base de données la plus simple à être utilisée de manière inefficace - de nombreuses personnes utilisent les paramètres par défaut sans autres recherches. Cet article passera en revue certaines des techniques d'optimisation MySQL introduites auparavant et les combinent avec les dernières améliorations.

Points de base

• Optimiser la configuration MySQL en ajustant les paramètres de clés tels que innodb_buffer_pool_size, innodb_log_file_size et innodb_flush_method pour mieux utiliser les ressources du serveur et améliorer les performances de la base de données.
• Utiliser efficacement les index pour accélérer le traitement des requêtes; envisager des index uniques, des index de clés principaux et des index de texte complet en fonction des exigences de requête et de l'unicité des données.
• Utilisez des outils tels que Percona Toolkit pour MySQL afin d'identifier et de résoudre automatiquement les problèmes tels que les index en double ou inutilisés pour optimiser l'efficacité de la base de données.
• Utilisez des outils tels que les journaux de requête lents de MySQL et pt-query-digest pour surveiller et analyser les requêtes lentes pour détecter les goulots d'étranglement et optimiser les performances de la requête.
• Exécutez régulièrement le tuner MySQL et d'autres outils de surveillance des performances pour recueillir des informations sur les opérations de base de données et affiner la configuration en fonction des modèles d'utilisation réels.

Optimisation de la configuration

L'amélioration des performances la première et la plus négligée à MySQL est d'ajuster la configuration. La version 5.7 (version actuelle) a de meilleures valeurs par défaut que les versions précédentes, mais des améliorations peuvent toujours être apportées.

Nous supposons que vous utilisez un hôte basé sur Linux ou une machine virtuelle Vagrant comme notre propriété améliorée, votre fichier de configuration est donc situé à /etc/mysql/my.cnf. Votre installateur peut charger le fichier de configuration auxiliaire, alors vérifiez-le si le fichier my.cnf n'est pas beaucoup, il peut être un fichier /etc/mysql/mysql.conf.d/mysqld.cnf.

Modifier la configuration

Vous devez vous familiariser avec l'utilisation de la ligne de commande. Même si vous n'y avez pas été exposé auparavant, c'est un bon moment.

Si vous modifiez localement une machine virtuelle Vagrant, vous pouvez utiliser la commande cp /etc/mysql/my.cnf /home/vagrant/Code pour copier le fichier dans un dossier partagé dans le système de fichiers principal, modifier à l'aide d'un éditeur de texte normal, puis le copier à son lieu d'origine après l'achèvement. Sinon, utilisez un éditeur de texte simple comme vim et exécutez la commande sudo vim /etc/mysql/my.cnf.

Remarque: Modifiez le chemin d'accès ci-dessus pour correspondre à l'emplacement réel du fichier de configuration - il peut en fait être situé dans /etc/mysql/mysql.conf.d/mysqld.cnf

Ajustement manuel

Les ajustements manuels suivants doivent être effectués immédiatement. En fonction de ces conseils, ajoutez ce qui suit au fichier de configuration dans la section

: [mysqld]

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

• innodb_buffer_pool_size - Le pool de tampons est une zone de stockage utilisée pour mettre en mémoire les données et les index. Il est utilisé pour garder les données fréquemment accessibles en mémoire, et il est logique d'allouer le plus de RAM à cette partie de l'application lorsque vous exécutez un serveur ou un serveur virtuel dédié et que la base de données est souvent un goulot d'étranglement. Nous y allouons donc 50 à 70% de tout RAM. Un guide de redimensionnement du pool de tampon est fourni dans la documentation MySQL.
• La taille du fichier journal est bien expliquée ici, mais en bref, il s'agit de la quantité de données à stocker dans le journal avant de les effacer. Notez que dans ce cas, le journal n'est pas un journal d'erreur ou un journal auquel vous pouvez être utilisé, mais indique plutôt le temps de point de contrôle, car pour MySQL, les écritures se produisent en arrière-plan, mais affectent toujours les performances de premier plan. Des fichiers journaux plus grands signifient de meilleures performances, car moins de nouveaux points de contrôle plus petits sont créés, mais un temps de récupération plus long en cas de plantages (besoin de réécrire davantage dans la base de données).
• innodb_flush_log_at_trx_commit Il y a une explication ici, qui indique ce qui arrive au fichier journal. Avec 1, nous avons la configuration la plus sûre car les journaux sont rincés sur le disque après chaque transaction. En utilisant 0 ou 2, il a moins de performances acides mais des performances plus élevées. Dans ce cas, la différence ne suffit pas pour dépasser l'avantage de stabilité de l'ensemble 1.
• innodb_flush_method - Pour terminer le travail de rafraîchissement, définissez-le sur O_DIRECT pour éviter la double tampon. Cela doit toujours être fait à moins que le système d'E / S ne fonctionne très mal. Sur la plupart des serveurs gérés, tels que les gouttelettes DigitalOcean, vous aurez un SSD, donc les performances de votre système d'E / S seront élevées.

Il existe un autre outil de Percona qui peut nous aider à trouver automatiquement les problèmes restants. Notez que si nous l'exécutons sans les ajustements manuels ci-dessus, seuls 1 des 4 correctifs peuvent être identifiés manuellement, car les 3 autres dépendent des préférences des utilisateurs et de l'environnement de l'application.

Inspecteur variable

Pour installer l'inspecteur de variable sur Ubuntu:

<code class="language-bash">wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit</code>

Pour d'autres systèmes, suivez les instructions.

Ensuite, exécutez la boîte à outils avec la commande suivante:

<code class="language-bash">pt-variable-advisor h=localhost,u=homestead,p=secret</code>

Vous devriez voir la sortie similaire à ce qui suit:

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

Aucun de ces problèmes n'est des problèmes clés et n'a pas besoin d'être corrigé. La seule chose que nous pouvons ajouter est la journalisation binaire pour la réplication et les instantanés.

REMARQUE: Dans les versions plus récentes, la taille du binlog sera par défaut à 1G et PT ne le remarquera pas.

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

• max_binlog_size Définir pour déterminer la taille du journal binaire. Ces journaux enregistrent vos transactions et requêtes et créent des points de contrôle. Si la transaction est supérieure au maximum, le journal peut être supérieur au maximum lorsqu'il est enregistré sur le disque - autrement, MySQL le maintiendra dans cette limite.
L'option
• log_bin permette une journalisation binaire complètement. Sans cela, il n'y a pas d'instantané ni de copie. Veuillez noter que cela peut exercer beaucoup de pression sur l'espace disque. L'ID de serveur est une option nécessaire lorsqu'il est activé pour la journalisation binaire, afin que les journaux sachent de quel serveur ils proviennent (pour la réplication) et le format est exactement le moyen d'écrire au journal.

Comme vous pouvez le voir, le nouveau MySQL a des valeurs par défaut raisonnables qui rendent les choses en production presque immédiatement. Bien sûr, chaque application est différente et il y a des ajustements personnalisés supplémentaires qui s'appliquent.

MySQL tuner

tuner surveillera la base de données à des intervalles plus longs (exécutez une fois par semaine sur une application en direct) et suggère des modifications en fonction de ce qu'elle voit dans le journal.

Téléchargez et installez-le simplement:

<code class="language-bash">wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit</code>

L'exécuter avec ./mysqltuner.pl demandera le nom d'utilisateur et le mot de passe de l'administrateur de votre base de données et publiera une analyse rapide. Par exemple, voici ma section InnoDB:

<code class="language-bash">pt-variable-advisor h=localhost,u=homestead,p=secret</code>

Encore une fois, il est important de noter que cet outil devrait s'exécuter environ une fois par semaine après l'exécution du serveur. Après avoir modifié la valeur de configuration et redémarré le serveur, il doit être exécuté une semaine à partir de là. Il est préférable de configurer un travail cron pour le faire pour vous et vous envoyer des résultats régulièrement.

---

Après chaque fois que vous modifiez la configuration, assurez-vous de redémarrer MySQL Server:

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

Index

Ensuite, concentrons-nous sur l'indexation - les principaux points de douleur de nombreux administrateurs de la base de données amateurs! Surtout ceux qui ont sauté dans ORM immédiatement et n'ont donc jamais vraiment touché le SQL d'origine.

REMARQUE: Les touches de terme et les index peuvent être utilisées de manière interchangeable.

Vous pouvez comparer l'index MySQL avec l'index dans le livre, qui vous permet de trouver facilement la page correcte contenant le sujet que vous recherchez. Sans index, vous devez lire le livre entier pour rechercher des pages contenant le sujet.

Comme vous pouvez l'imaginer, la recherche par index est beaucoup plus rapide que d'avoir à traverser chaque page. Par conséquent, l'ajout d'index à une base de données peut accélérer les requêtes de sélection. Cependant, les index doivent également être créés et stockés. Par conséquent, les requêtes de mise à jour et d'insertion seront plus lentes et prendront plus d'espace disque. Généralement, si vous indexez correctement le tableau, vous ne remarquerez pas les différences de mises à jour et d'inserts, il est donc recommandé d'ajouter des index au bon endroit.

Les tables contenant seulement quelques lignes ne bénéficient pas réellement de l'indexation. Comme vous pouvez l'imaginer, la recherche de 5 pages n'est pas plus lente que d'aller indexer d'abord, d'obtenir des numéros de page puis d'ouvrir une page spécifique.

Alors, comment pouvons-nous découvrir les index à ajouter et quels types d'index existent?

Index de clé unique / primaire

L'indice de clé primaire est l'index des données et est le moyen par défaut de traiter les données. Pour les comptes d'utilisateurs, il peut s'agir d'un ID utilisateur ou d'un nom d'utilisateur, ou même d'un e-mail principal. L'indice de clé primaire est unique. Le seul index est un index qui ne peut pas être répété dans un ensemble de données.

Par exemple, si l'utilisateur sélectionne un nom d'utilisateur spécifique, personne d'autre ne devrait pouvoir l'utiliser. L'ajout d'un index "unique" à la colonne du nom d'utilisateur résout ce problème. Si quelqu'un d'autre essaie d'insérer une ligne avec un nom d'utilisateur existant, MySQL rapportera une erreur.

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

Les clés / index primaires sont généralement définis à la création du tableau, et le seul index est défini en modifiant le tableau.

Les clés primaires et uniques peuvent être créées sur une ou plusieurs colonnes. Par exemple, si vous souhaitez vous assurer qu'il n'y a qu'un seul nom d'utilisateur par pays à définir, vous pouvez créer un index unique sur les deux colonnes, comme ceci:

<code class="language-bash">wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit</code>

L'indice unique est ajouté aux colonnes auxquelles vous accédez fréquemment. Donc, si vous demandez fréquemment un compte d'utilisateur et qu'il existe de nombreux comptes d'utilisateurs dans la base de données, il s'agit d'un bon cas d'utilisation.

Index général

L'index général simplifie la recherche. Ils sont utiles lorsque vous devez trouver rapidement des données pour une colonne ou une combinaison spécifique de colonnes, mais ces données n'ont pas besoin d'être uniques.

<code class="language-bash">pt-variable-advisor h=localhost,u=homestead,p=secret</code>

Les opérations ci-dessus accéléreront la recherche de noms d'utilisateur par pays.

Les index

aident également à améliorer les vitesses de tri et de regroupement.

Index du texte intégral

L'index de texte complet est utilisé pour la recherche en texte intégral. Seuls les moteurs de stockage InnoDB et Myisam prennent en charge l'indexation du texte intégral et seules les colonnes char, varchar et text sont prises en charge.

Ces index sont très utiles pour toutes les recherches de texte que vous devrez peut-être effectuer. L'indexation du texte intégral est bonne pour trouver des mots dans le corps du texte. Si vous permettez souvent de rechercher des publications, des commentaires, des descriptions, des commentaires, etc. Dans l'application, utilisez ces index sur ces contenus.

Index descendant

n'est pas un type spécial, mais un changement. À partir de la version 8.0, MySQL prend en charge l'indexation descendante, ce qui signifie qu'il peut stocker des index dans l'ordre descendant. Cela est utile lorsque vous avez de grandes tables ou des entrées prioritaires qui doivent souvent récupérer les dernières données ajoutées en premier. Il peut toujours être trié par ordre décroissant, mais cela entraînera un peu de pénalité de performance. Cela accélère davantage.

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

Envisagez d'appliquer DESC aux index lors du traitement des journaux écrits dans la base de données, des publications et des commentaires chargés dans un ordre de retour sur le front et un contenu similaire.

Outils assistés: expliquez

L'outil Explication sera inestimable lors de la visualisation des requêtes d'optimisation. L'ajout d'explique avant qu'une simple requête le gérera de manière très profonde, l'analyse de l'indice utilisé et montrant le rapport des coups et des ratés. Vous remarquerez le nombre de lignes qu'il doit traiter pour obtenir le résultat que vous recherchez.

<code>max_binlog_size = 1G
log_bin = /var/log/mysql/mysql-bin.log
server-id=master-01
binlog-format = 'ROW'</code>

Vous pouvez l'étendre davantage en utilisant étendu:

<code class="language-bash">wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl</code>

Voir comment l'utiliser et appliquer la découverte en lisant cet excellent article détaillé.

Outil assisté: Percona est utilisé pour détecter les index en double

La boîte à outils Percona précédemment installée fournit également un outil pour détecter les index en double qui sont utiles lors de l'utilisation d'un CMS tiers ou de simplement vérifier si plus d'index sont ajoutés de façon inattendue que nécessaire. Par exemple, le WordPress par défaut installé dans le tableau wp_posts a un index en double:

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

Comme indiqué dans la dernière ligne, il fournit également des suggestions sur la façon de supprimer les index en double.

Outils assistés: Percona est utilisé pour détecter les index inutilisés

percona peut également détecter les indices inutilisés. Si vous enregistrez des requêtes lentes (voir la section des goulots d'étranglement ci-dessous), vous pouvez exécuter l'outil et il vérifiera si les requêtes de ces enregistrements utilisent des index dans les tableaux liés aux requêtes.

<code class="language-bash">wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit</code>

Voir ici pour une utilisation détaillée de cet outil.

goulot d'étranglement

Cette section explique comment détecter et surveiller les goulots d'étranglement dans la base de données.

<code class="language-bash">pt-variable-advisor h=localhost,u=homestead,p=secret</code>

Ce qui précède doit être ajouté à la configuration. Il surveille les requêtes qui ont un temps d'exécution de plus de 1 seconde et celles qui n'ont pas utilisé l'index.

Une fois que ce journal a des données, vous pouvez utiliser l'outil pt-index-usage susmentionné ou l'outil pt-query-digest pour analyser son utilisation d'index, qui produira les résultats suivants:

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

Si vous préférez analyser ces journaux manuellement, vous pouvez également le faire, mais vous devez d'abord exporter les journaux au format plus "analysé". Cela peut être fait par:

<code>max_binlog_size = 1G
log_bin = /var/log/mysql/mysql-bin.log
server-id=master-01
binlog-format = 'ROW'</code>

D'autres paramètres peuvent filtrer davantage les données et garantir que seul le contenu important est exporté. Par exemple: les 10 meilleures requêtes triées par le temps d'exécution moyen.

<code class="language-bash">wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl</code>

Voir la documentation des autres paramètres.

Conclusion

Dans cet article complet d'optimisation MySQL, nous examinons diverses façons de faire fonctionner MySQL plus rapidement.

Nous avons géré l'optimisation de la configuration, nous avons terminé l'index et nous nous sommes débarrassés de certains goulots d'étranglement. Cependant, cela est principalement théorique - pour que des cas d'utilisation pratiques appliquent ces technologies dans les applications du monde réel, veuillez faire attention à notre prochain projet Performance Boost!

Avons-nous manqué des techniques et des astuces? S'il vous plaît dites-nous!

Indexation MySQL et FAQ d'optimisation de requête lente (FAQ)

Quelle est l'importance de l'indexation de MySQL dans l'optimisation des requêtes?

Les indices MySQL sont essentiels pour l'optimisation des requêtes car ils peuvent accélérer considérablement la récupération des données. Ils fonctionnent similaires aux index du livre, permettant à la base de données de trouver et de récupérer des données sans scanner chaque ligne du tableau. Cela peut entraîner une exécution de requête plus rapide, en particulier dans les grandes bases de données. Cependant, il est important de noter que si les index augmentent la vitesse de lecture, ils peuvent ralentir les vitesses d'écriture, car l'index doit être mis à jour lors de l'insertion ou de la mise à jour des données.

Comment identifier les requêtes lentes dans MySQL?

MySQL fournit un outil utile appelé Slow Query Log. Cet outil enregistre des informations sur toutes les requêtes SQL qui ont été exécutées pendant plus que le temps spécifié. Vous pouvez l'activer dans le fichier de configuration MySQL et définir long_query_time au nombre de secondes que la requête devrait prendre avant qu'il ne soit considéré comme une requête lente.

Quels sont les différents types d'index MySQL?

MySQL prend en charge plusieurs types d'index, notamment B-Tree, Hash, R-Tree et Index Full-Text. Le B-Tree est le type d'index par défaut et convient à diverses requêtes. Les indices de hachage sont utilisés pour des comparaisons égales et sont plus rapides que B-Tree pour de telles requêtes. L'index R-Tree est utilisé pour les types de données spatiaux et l'index de texte complet est utilisé pour la recherche en texte intégral.

Comment optimiser ma configuration MySQL?

L'optimisation de la configuration MySQL implique de régler diverses variables de serveur pour les performances. Cela comprend le réglage de la taille du pool de tampon, la taille du fichier journal, la taille du cache de requête, etc. Il est important de surveiller régulièrement les performances du serveur et d'ajuster ces variables au besoin.

Quels outils peuvent m'aider à optimiser les requêtes et les index MySQL?

Il existe plusieurs outils disponibles pour la requête MySQL et l'optimisation d'index. Ces outils incluent les instructions Expliquez par MySQL qui fournissent des informations sur la façon dont MySQL effectue des requêtes, ainsi que des outils tiers tels que Percona Toolkit et MySQL Workbench.

Comment l'instruction Explication aide-t-elle à l'optimisation des requêtes?

L'instruction Explication dans MySQL fournit des informations sur la façon dont MySQL effectue des requêtes. Cela comprend des informations sur les tableaux accessibles, l'ordre dans lequel les tables sont accessibles, l'index spécifique utilisé et l'estimation du nombre de lignes lues. Ces informations peuvent aider à identifier les problèmes de performances potentiels et à guider l'optimisation de l'indice.

Quel impact l'indice a-t-il sur les opérations d'écriture dans MySQL?

Bien que les index améliorent considérablement les opérations de lecture en accélérant la récupération des données, cela peut ralentir les opérations d'écriture. En effet, chaque fois que les données sont insérées ou mises à jour, l'index correspondant doit être mis à jour. Par conséquent, lors de la création d'un indice, il est important de trouver un équilibre entre les opérations de lecture et d'écriture.

Comment utiliser l'optimisation de l'index pour les opérations de jointure dans MySQL?

Les index

peuvent améliorer considérablement les performances des opérations de jointure dans MySQL. En créant un index sur les colonnes utilisées dans la condition de jointure, MySQL peut rapidement trouver des lignes correspondantes dans la table connectée. Cela réduit le besoin de numérisation en texte intégral et se traduit par une exécution de requête plus rapide.

Quel est le rôle du cache de requête dans l'optimisation des performances MySQL?

Le cache de requête dans MySQL stocke les résultats de la requête sélectionnée ainsi que la requête elle-même. Lorsque la même requête est reçue, MySQL peut récupérer les résultats du cache au lieu d'exécuter à nouveau la requête. Cela peut améliorer considérablement les performances, en particulier pour les requêtes complexes ou les requêtes fréquemment exécutées.

Comment surveiller les performances de mon serveur MySQL?

MySQL fournit plusieurs outils pour surveiller les performances du serveur. Ces outils incluent des modèles de performances (fournissant des mesures de performances détaillées) et des modèles d'information (fournissant des informations sur les métadonnées de la base de données). De plus, la commande Show Status peut être utilisée pour obtenir des informations sur l'état d'exécution du serveur.

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!