Considérations d'optimisation MySQL-tutoriel php-php.cn

Lors de l'exploitation de la base de données, nous devons optimiser MySQL. Cet article parle des précautions d'optimisation.

Le premier point est que le matériel est trop ancien
Nous parlerons ici principalement du matériel sous les trois aspects du CPU, de la mémoire et du disque. Il y a aussi certains facteurs tels que les cartes réseau, les réseaux de la salle informatique, etc. En raison de la longueur de l'article, nous ne les présenterons pas un par un. Oui, il y aura une autre occasion de discuter dans le futur.
Tout d'abord, examinons les caractéristiques d'utilisation du processeur de MySQL :
5.1 peut utiliser 4 cœurs, 5.5 peut utiliser 24 cœurs et 5.6 peut utiliser 64 cœurs
Par exemple, MySQL5.6 peut utiliser s'il en a plus de 48 CORE et fonctionne bien, 64 CORE peuvent être utilisés (entre 48CORE-64CORE, l'annonce officielle est 48 CORE, et dans mon test actuel, il peut atteindre 64 CORE).
MySQL 5.6 peut utiliser 48 cœurs+
* Avant MySQL 5.1, il peut utiliser jusqu'à 4 cœurs **
Aujourd'hui, les serveurs de l'environnement de production général sont de 32 CORE ou plus.
Je recommande donc à tout le monde ici d'utiliser MySQL5.5 ou MySQL5.6 autant que possible, à moins que le serveur de votre entreprise n'utilise un très ancien serveur avec seulement 4 cœurs ou 1 cœur.
Parce qu'avant la version 5.1 (la même que la version 5.0), elle était codée en dur dans le code interne et était basée sur le moteur de stockage innobase, la base de données avait donc une mauvaise utilisation du matériel. Après avoir évolué vers le moteur InnoDB, il est devenu bien meilleur.
Chaque connexion est un thread (pas un pool de threads), et chaque requête ne peut utiliser qu'un seul cœur.
De plus, dans MySQL, chaque requête ne peut utiliser qu'un seul processeur.
Oracle utilise du SQL parallèle et des requêtes parallèles. Ce type de fonction n'existe pas dans MySQL.
Pas de cache de plan d'exécution (pas de précompilation du plan d'exécution SQL)
Deuxièmement, il n'y a pas de précompilation SQL dans MySQL. Par conséquent, il n’existe pas de structure comparable au cache de bibliothèque dans la structure de mémoire d’Oracle. Par conséquent, MySQL n'a qu'une analyse dure, il n'y a pas d'analyse douce, encore moins d'analyse douce.
MySQL connaîtra une dégradation des performances à mesure que le nombre de connexions augmente
C'est aussi une faille de MySQL, mais avec l'évolution des versions de MySQL, de nombreuses solutions ont vu le jour.
Par exemple : le pool de threads officiellement lancé, appelé TP. Il s'agit de résoudre le problème du nombre trop élevé de connexions simultanées, mais il s'agit d'un composant supplémentaire de MySQL, et le TP officiel nécessite de l'argent supplémentaire pour l'acheter.
De plus, il y a une personne en Chine nommée Lou Fangxin qui a développé un middleware OneSQL pour résoudre des problèmes similaires.
Il existe un cache de résultats, mais il est inutile.
MySQL a également un cache de résultats similaire à celui d'Oracle, appelé Query Cache, mais c'est une fonction relativement inutile et rarement utilisée.
Étant donné que la plupart des environnements de production actuels sont des systèmes OLTP avec des opérations de mise à jour et de modification fréquentes, ce cache de requêtes dégradera sérieusement les performances de MySQL lorsqu'il est utilisé dans un environnement où les données sont fréquemment mises à jour et modifiées. Par conséquent, il est généralement rarement utilisé.
Maintenant que MySQL est utilisé, le moteur de stockage InnoDB est essentiellement utilisé. Les moteurs MyISAM précédents sont rarement utilisés. (Qu'est-ce qu'un moteur de stockage ? Si vous ne le savez pas, vous pouvez gg)
Il n'est pas nécessaire d'activer le cache de requêtes dans le moteur InnoDB, car il s'agit d'un moteur de stockage transactionnel, et utiliser InnoDB revient à utilisez sa capacité de traitement des transactions, des mises à jour et des modifications fréquentes des données se produiront certainement.
Regardons à nouveau les caractéristiques d'utilisation de la mémoire de MySQL
Le serveur avec un système d'exploitation 64 bits peut utiliser la mémoire ((2^64-1)/1024/1024/1024)G
Dans un niveau élevé Environnement simultané à grande vitesse, il s'agit essentiellement de s'appuyer sur la mise en cache de la mémoire pour réduire l'impact des E/S sur le disque
Habituellement, la mémoire est planifiée en fonction de 15 à 20 % des données réelles. Si les données sont particulièrement chaudes, une plus grande proportion. doit être pris en compte pour mettre les données en cache
Ces 15 % à 20 % Les données sont généralement appelées données chaudes. (C'est aussi une valeur d'expérience courante)
Par exemple, si vous estimez que le volume total de données de votre MySQL est d'environ 500 Go, alors la mémoire fournie par MySQL peut être de 75 Go (5 000,15), alors vous aurez peut-être besoin d'une machine de 128 Go. serveur de mémoire.
De plus, certaines entreprises disposeront de quantités de données particulièrement chaudes et importantes (il est possible de dépasser largement la fourchette de 15 à 20 %), comme QQ Farm.
Je crois que tout le monde a déjà joué à des jeux de vol de nourriture, comme QQ Farm, Happy Farm et autres. (Il existe également un site Internet 12306 pour réserver des billets).
Ce type d'entreprise est très préoccupant dans notre secteur. Les caractéristiques de ce type d'entreprise sont que lorsque les données sont chaudes, il s'agit essentiellement de données chaudes à 100 %. Par exemple : lorsque tout le monde joue à QQ Farm, elles apparaissent. Chaque jour, pour s'amuser, ils venaient voler des légumes de temps en temps. Beaucoup de gens volaient une poignée de légumes lorsqu'ils se levaient au milieu de la nuit pour aller aux toilettes.
La configuration mémoire de la base de données MySQL pour ce type d'entreprise doit donc être augmentée. 15 à 20 % ne suffisent pas.
Résumé : ****Les affaires générales 15 % à 20 % sont utilisées pour planifier les données chaudes, telles que : le centre utilisateur, les commandes et autres activités courantes. Pour certaines autres entreprises particulières, la situation spécifique doit être analysée en détail.
L'allocation de conseils peut être effectuée en fonction du temps de réponse des requêtes
Lorsque nous planifions et concevons cette base de données d'architecture en ligne à grande échelle,
le temps de réponse des requêtes SQL est également un indicateur très important.
Dans un système aussi vaste, il doit transporter des millions, voire des dizaines de millions d'utilisateurs pour faire des affaires en ligne en même temps. Le temps de réponse des requêtes SQL (requête) doit être strictement contrôlé. doit être contrôlé dans un certain laps de temps.
Par exemple, pour notre bibliothèque principale, j'exige que le temps de réponse de la requête (réponse moyenne) soit inférieur à 30 ms. S'il dépasse 30 ms, nous pensons que la base de données a peut-être atteint la limite de charge et qu'elle doit être étendue.
De plus, un suivi des indicateurs à long terme de ce temps de réponse aux requêtes est nécessaire.
Il s'agit de la bibliothèque principale. S'il existe d'autres bibliothèques auxiliaires moins importantes, telles que des bibliothèques qui stockent des journaux, ou certaines bibliothèques dont les exigences de performances ne sont pas trop élevées, nous pouvons assouplir le temps de réponse des requêtes à 1 ou 2 secondes.
Déterminez le seuil de ce temps de réponse aux requêtes en fonction de l'importance de l'entreprise.
Il s'agit d'un principe directeur très important : planifiez votre capacité de performance en fonction du temps de réponse aux requêtes.
Il existe deux types de capacité : la capacité de performance et la capacité spatiale. La capacité spatiale est très simple, c'est-à-dire combien de données SIZE sont placées et combien de T.
La capacité de performance est plus importante et détermine si elle peut gérer la pression et la charge de votre entreprise.
Tout le monde doit se rappeler : si l'entreprise avec laquelle vous souhaitez traiter compte des millions d'utilisateurs actifs, et non des centaines d'utilisateurs, la performance est reine et répondre aux besoins de l'entreprise est la chose la plus importante.
Peu importe à quel point vos fonctions sont géniales, quelle que soit la qualité de votre produit, les performances sont inégalées. Tout le reste est absurde. Des centaines de milliers de personnes peuvent faire tomber l'ensemble de votre système et de votre projet en quelques secondes, puis votre propre projet. l'entreprise sera aveuglée.
Les utilisateurs qui ont travaillé si dur seront également perdus en grand nombre, et les pertes seront lourdes.
La performance est la base. L’architecture dans son ensemble n’a de sens que si les performances peuvent y résister. Si les performances ne sont pas satisfaisantes, il sera inutile d’envisager ultérieurement la haute disponibilité.
Caractéristiques d'utilisation du disque de MySQL
Binlog, redo log, undo log séquentiel IO
MySQL a différents types d'IO.
Binlog, redolog, undolog, ce sont des écritures d'E/S séquentielles.
Il n'est pas vraiment nécessaire de mettre ce genre de choses sur un SSD. L'écriture séquentielle sur des disques mécaniques traditionnels est également très rapide. Le mettre sur un SSD est un peu inutile. De plus, le SSD a des problèmes de perte d'écriture et de durée de vie en écriture. il n'est pas nécessaire de le mettre sur SSD sur SSD. Le mettre sur un disque SAS traditionnel suffit. Il n'est pas nécessaire de mettre un SSD.
SSD est utilisé pour stocker le fichier de données. Étant donné que la plupart des E/S qui se produisent sur le fichier de données sont des E/S aléatoires, il est très avantageux pour le SSD d'exécuter des E/S aléatoires. Le disque SSD SSD + le disque SAS traditionnel sont mélangés pour le stockage. De plus, n'utilisez pas de SSD pour les disques de sauvegarde.
Les E/S aléatoires du fichier de données et les E/S séquentielles combinées
Les E/S séquentielles sont toujours plus rapides. Dans la conception de bases de données, ce qui détermine si vous êtes un administrateur de base de données génial ou un architecte génial dépend de votre capacité à concevoir une entreprise avec des E/S séquentielles autant que possible tout en réduisant les E/S aléatoires. Par exemple : lors de la conception d'une entreprise de relations amicales, j'espère qu'une requête pourra supprimer la relation amicale via des IO séquentielles. Alors, comment la concevoir ?
Dans InnoDB de MySQL, nous pouvons profiter d'une fonctionnalité d'InnoDB : les tables d'index clusterisées. (Similaire à l'IOT d'Oracle).
Grâce à cette fonctionnalité, les données des amis de l'utilisateur peuvent être rassemblées autant que possible sur une seule page ou sur plusieurs pages adjacentes. Lors de la lecture, une lecture séquentielle IO peut être effectuée et les performances sont grandement améliorées.
La structure de la table des relations amicales est la suivante (la table préalable est le moteur InnoDB) :
owner_idfriend_id (friend id)
Les deux champs ci-dessus sont utilisés comme clé primaire La clé primaire de. InnoDB est l'index clusterisé. Ensuite, lisez ces deux champs, IO peut gérer les champs dans un certain ordre.
Dans tous les livres de conception de bases de données du passé, il était toujours mentionné que chaque table devait ajouter une spécification pour une clé primaire auto-incrémentée. En fait, la spécification est morte et la réponse est vivante. L'exemple ci-dessus n'est d'aucune utilité. Au lieu d'ajouter une clé primaire supplémentaire, deux champs métier qui ont des attributs métier et sont fréquemment lus sont utilisés comme clés primaires, ce qui entraîne de meilleures performances.
Par conséquent, lorsque vous étudiez, ne mémorisez pas les normes et réglementations de ces livres, mais vous devriez vraiment comprendre les principes de quelque chose, comme apprendre les principes internes d'InnoDB, puis dans le travail réel, avec le soutien. des principes, utiliser Le principe est de tirer des conclusions d'un cas à d'autres cas.
Les principes d'InnoDB constituent un énorme morceau de connaissances et nécessitent un apprentissage au fil du temps. Vous pouvez prêter plus d'attention à mon compte officiel, et certains articles sur InnoDB seront publiés les uns après les autres.
Les activités OLTP nécessitent plus d'E/S aléatoires
Vous pouvez utiliser la mémoire pour la mise en cache, réduisant ainsi les E/S aléatoires
Les activités OLAP nécessitent plus d'E/S séquentielles
La mise en cache de la mémoire n'est pas très utile
Avant MySQL 5.6, elle était La modification de page n'est pas prise en charge et la valeur par défaut est 16 Ko.
MySQL5.6 peut être modifié après MySQL5.6. Ce paramètre est innodb_page_size, mais MySQL5.6 ne peut être modifié qu'en 8K ou 4K, et ne peut pas être augmenté en 32K ou 64K avant MySQL5.7 ou. au-dessus de.
Pour les systèmes OLAP, des pages plus grandes contribueront à améliorer les performances, car les systèmes OLAP ont des requêtes relativement volumineuses et analysent beaucoup de données.
Deuxième point : La conception de la base de données n'est pas bonne
Par exemple, de nombreuses fonctionnalités de la base de données sont utilisées, telles que des déclencheurs, des partitions, de nombreuses procédures stockées, des fonctions, etc.
On dit souvent que petit est beau, ce qui signifie que la simplicité est ce qu'il y a de mieux. Si vous utilisez toutes les fonctions de la base de données, les performances de la base de données seront naturellement ralenties et les risques d'éventuels bugs et échecs sous-jacents augmenteront.
Tout le monde doit donc comprendre qu'une bonne conception de projet de base de données est petite, belle, simple et concise. De plus, la base de données ne constitue qu'une partie du projet global. Des éléments tels que Trigger et les procédures stockées peuvent certainement être implémentés à l'aide du code d'application dans le projet global.
Ainsi, lorsque nous utilisons MySQL, nous utilisons simplement ses fonctionnalités puissantes, telles que les tables, les index et les transactions, plutôt que d'utiliser toutes ses fonctions.
Un autre point est qu'avant MySQL 5.6, les sous-requêtes n'étaient pas autorisées dans la base de données principale de l'environnement de production.
Les performances des sous-requêtes avant MySQL 5.6 étaient particulièrement médiocres. (La syntaxe est prise en charge, mais les performances SQL sont très médiocres).
Par exemple, si vous utilisez Oracle maintenant et souhaitez migrer Oracle vers MySQL, il est recommandé d'utiliser la version MySQL 5.6. MySQL 5.6 a apporté de grandes améliorations en termes de prise en charge et de performances des sous-requêtes.
Les performances de la sous-requête MySQL 5.6 seront grandement améliorées.
Le troisième point : le programme est trop mal écrit

Je pense que les étudiants qui ont été DBA auraient dû faire l'expérience de cela. Dans les petites et moyennes entreprises, le niveau des programmeurs varie.
Surtout lorsque vous rencontrez de nombreux programmeurs qui viennent d'entrer dans l'industrie (fraîches diplômés), il est plus probable que ces programmeurs qui viennent d'entrer dans l'industrie répondront également à des besoins très urgents. Il est difficile d’imaginer un programme développé dans un tel environnement.
Bien sûr, ce n’est pas la faute de nos programmeurs, nous ne pouvons pas leur en vouloir.
La principale raison de mon phénomène ci-dessus est l'environnement de développement national. Il n'y a aucun moyen. Les besoins de développement sont urgents (les produits sont activés tous les jours) et les programmeurs sont occupés à se précipiter au travail (heures supplémentaires à long terme). soyez seulement occupé à mettre en œuvre des programmes commerciaux, et il n'y a aucun moyen d'optimiser le programme.
Bien sûr, dans cet environnement, c'est une opportunité pour nous, DBA. Le mauvais SQL et le SQL complexe écrit par les programmeurs ont provoqué un ralentissement, voire un crash du système. Ensuite, notre administrateur de base de données est intervenu pour optimiser et transformer ces mauvais SQL et ce SQL lent, et le système est revenu à la normale et est devenu de plus en plus stable. C’est aussi quelque chose de très enrichissant et qui sera respecté par les collègues et les dirigeants.
Dans le même temps, les administrateurs de base de données peuvent également renforcer la formation des programmeurs afin d'améliorer leur capacité à écrire rapidement du bon SQL. Laissez-les passer moins de temps et écrire des instructions SQL avec de meilleures performances et des performances plus fluides. De cette manière, cela peut également réduire la charge pesant sur le DBA.
Personnellement, je préfère parler de formation aux programmeurs. Premièrement, tout le monde peut gagner quelque chose en échangeant de la technologie. Deuxièmement, cela aide à construire une bonne relation s'il y a des questions au travail qui doivent être négociées à l'avenir. sera facile à discuter. C'est mieux que de leur offrir un repas.
Nous avons principalement les solutions suivantes pour les programmes mal écrits :
Pour que les applications utilisent des pools de connexions de bases de données, en particulier dans les applications à grande échelle et à haute concurrence développées sur la base de JAVA, des pools de connexions doivent être utilisés.
L'avantage d'utiliser un pool de connexions est qu'il permet de limiter le nombre de connexions dans l'application. De plus, il n'est pas nécessaire de créer chaque connexion supplémentaire. Le coût de création d'une connexion pour MySQL est également important, car la création. une nouvelle connexion équivaut à la création par MySQL d'un nouveau thread.
Je viens également de mentionner que MySQL connaîtra une dégradation des performances à mesure que le nombre de connexions augmentera.
Les étudiants qui ont écrit du code de programme doivent également savoir que sur notre ordinateur portable ordinaire (généralement 4CORE), vous créez 400 threads, et chaque thread fait 1+1+1+1+... un travail simple, dormez à nouveau et voyez si votre PC est bloqué ou non. Vous constaterez que le processeur de votre PC est presque plein. Si vous osez créer 600 threads, alors votre machine redémarrera bientôt. En effet, le processeur est entièrement occupé en raison de la surcharge des threads.
Instructions SQL complexes
Comme je l'ai dit tout à l'heure, le SQL écrit par les programmeurs présente généralement de nombreux problèmes. Après tout, ils sont trop occupés pour considérer les performances et le fonctionnement de ce SQL. Dans certains cas, le SQL épissé par le programmeur peut directement faire tomber l'ensemble du système.
Laissez-moi vous donner un exemple simple : une de nos applications crée 10 connexions à la base de données (nombre maximum de connexions = 10). Chacune de ces 10 connexions exécute le même SQL complexe en même temps. secondes pour exécuter ce SQL complexe, alors ces 10 connexions ne peuvent exécuter ce SQL complexe que dans un délai de 10 minutes, et tous les autres SQL suivants seront bloqués.
Par conséquent, la plupart des applications seront indisponibles pendant 10 minutes, n'est-ce pas ? Et cela pourrait provoquer une avalanche et provoquer l’effondrement du système.
L'optimisation du SQL complexe est également un travail très important pour les DBA. Il est nécessaire de découvrir ces SQL complexes, ces SQL lents et ces mauvais SQL grâce à des méthodes de surveillance, puis de donner des suggestions d'optimisation aux programmeurs (le DBA doit effectuer des tests de comparaison des performances). ). Ce n'est qu'en permettant aux programmeurs de modifier le code que le système peut réellement fonctionner de manière fluide et en parallèle, comme une autoroute sans embouteillages.
Certaines personnes pourraient se demander, les programmeurs de notre entreprise ne sont que des méchants. Ils ne changeront pas le code SQL même s'ils meurent, ils ne l'optimiseront pas même s'ils meurent et ils ne peuvent pas communiquer. Alors que devons-nous faire ?
Nous avons encore un moyen. Nous pouvons également créer une bibliothèque esclave dédiée (Slave library) pour la gérer. Vous pouvez modifier la bibliothèque pour la requête.
Par exemple, dans notre entreprise, notre système backend qui génère des rapports est connecté à la base de données esclave pour les requêtes, et ne se connecte pas à la base de données principale.
Logique invalide
Analyse complète de la table
Par exemple : update t set a = a + 1 ; J'ai oublié d'ajouter la condition Where.
Si vous souhaitez que votre système prenne en charge des millions d'utilisateurs en ligne, vous devez ajouter un système SQL Review pour éliminer SQL avec une logique non valide et SQL avec des analyses de table complètes.
SQL ne peut être publié en ligne qu'après avoir été examiné et approuvé par le DBA.
De plus, ce type de grande mise à jour SQL doit être mis à jour par lots et la grande tâche SQL doit être divisée en petites tâches à exécuter. Dans MySQL, cela nécessite une attention particulière.
Pourquoi mettre à jour par lots ?
**Raison 1. **Comme mentionné ci-dessus, une requête MySQL ne peut utiliser qu'un seul CORE. Les transactions SQL sont trop volumineuses et complexes et prennent beaucoup de temps à s'exécuter, ce qui provoque facilement une congestion.
Raison 2. Dans l'environnement en ligne, MySQL a généralement une architecture maître/esclave Si une transaction de mise à jour importante de 1 million de lignes se produit dans le maître, il est probable que l'ESCLAVE y reste bloqué, car l'ESCLAVE est. une structure monothread, provoquant des retards de synchronisation.
Écrivez du SQL dans MySQL et créez de petites transactions SQL, qui peuvent être exécutées et soumises rapidement. Laissez chaque requête se terminer plus rapidement et la connexion sera libérée plus rapidement.