Comment optimiser votre base de données

La raison pour laquelle la base de données fonctionne trop lentement

Le premier point est que le matériel est trop ancien

Pour le matériel, nous principalement Concentrez-vous sur le processeur, la mémoire, parlons des trois aspects des disques. Il existe également certains facteurs tels que les cartes réseau, les réseaux de salle informatique, etc. En raison de la longueur de l'article, je ne les présenterai pas un par un. être des occasions d'en parler à l'avenir.

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 plus de 48 CORE, et s'il fonctionne bien, il peut utiliser 64 CORE (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+

MySQL 5.1 peut utiliser jusqu'à 4 cœurs auparavant

Maintenant, les serveurs de l'environnement de production général sont de 32 CORE ou plus.

Je recommande donc à tout le monde d'essayer d'utiliser MySQL5.5 ou MySQL5.6, à 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 (TP) officiellement lancé. 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, les opérations de mise à jour et de modification sont fréquentes. Ce cache de requêtes est utilisé dans un environnement où les données sont fréquemment mises à jour et modifiées, ce qui dégradera sérieusement les performances de MySQL. Il est donc généralement très à utiliser avec parcimonie.

Maintenant que j'utilise MySQL, j'utilise essentiellement le moteur de stockage InnoDB. 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 ce cache de requêtes dans le moteur InnoDB, car c'est un moteur de stockage transactionnel, et il est utilisé lors de l'utilisation d'InnoDB. Avec des capacités de traitement de transactions élevées, 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 environnement simultané à grande vitesse, la mise en cache de la mémoire est essentiellement utilisée 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. les données sont particulièrement chaudes, une plus grande proportion doit être prise en compte. Pour mettre les données en cache

Ces 15% à 20% de 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 que MySQL fournira peut être de 75 Go (500*0,15), alors vous pouvez Un serveur avec environ 128 Go de mémoire est requis.

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 sur QQ. Ferme, ils viennent ici pour jouer tous les jours, et ils viennent de temps en temps pour voler de la nourriture. Beaucoup de gens se lèvent au milieu de la nuit et volent de la nourriture lorsqu'ils vont 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é : 15 à 20 % des activités générales sont utilisées pour planifier des données chaudes, telles que le centre utilisateur, les commandes et d'autres activités courantes. Pour certaines autres entreprises particulières, la situation spécifique doit être analysée en détail.

L'allocation de guidage peut être effectuée en fonction du temps de réponse aux requêtes

Lorsque nous réalisons ce type d'architecture en ligne à grande échelle - planification et conception de grandes bases de données,

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é et votre système doit être strictement contrôlé. Le temps de réponse aux requêtes est contrôlé dans le délai imparti.

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 les bibliothèques de journalisation, 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 à moins de 1 seconde 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 et tout le reste est absurde. Des centaines de personnes peuvent faire tomber l'ensemble de votre système et de votre projet en quelques secondes, et alors vous les gars, l'entreprise a été 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, annulation du journal des E/S séquentielles

MySQL a différents types d'E/S.

binlog, redolog, undolog, ce sont toutes 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 présente des problèmes de perte d'écriture et d'écriture. vie. Il doit être placé 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 des fichiers 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 des fichiers 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 des prémisses est le moteur InnoDB) :

owner_idfriend_id (id de l'ami)

Les deux champs ci-dessus sont utilisés en tant que clé primaire, la clé primaire d'InnoDB C'est un index clusterisé, donc la lecture de ces deux champs doit se faire avec des E/S séquentielles.

Dans le passé, certains livres de conception de bases de données mentionnaient toujours que chaque table devait ajouter une spécification pour une clé primaire auto-incrémentée. En fait, la spécification est morte, mais la réponse est vivante. un exemple ci-dessus est : Au lieu d'utiliser une clé primaire auto-incrémentée, 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 devez vraiment comprendre les principes de quelque chose, comme bien apprendre les principes internes d'InnoDB, puis dans le travail réel. , vous pouvez utiliser les principes Support et utiliser des principes pour tirer des conclusions.

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.

L'activité OLTP nécessite 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

L'activité OLAP nécessite plus d'E/S séquentielles

mémoire Le cache est de peu d'utilité

Avant MySQL 5.6, la modification de page n'était pas prise en charge et la valeur par défaut était de 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 plus.

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, légère et simple. 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, les transactions, etc., plutôt que d'avoir à 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 MySQL5.6. MySQL5.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 : l'écriture de programmes est terrible

Je pense que les étudiants qui ont été DBA auraient dû vivre cela dans les petites et moyennes entreprises, au niveau des programmeurs. varie ensemble.

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 ce genre d’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 mentionné 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 (à long terme). heures supplémentaires). Ils ne peuvent être occupés qu'à mettre en œuvre des programmes commerciaux, ils n'ont pas le temps d'optimiser le programme.

Bien sûr, dans cet environnement, c'est une opportunité pour nous les 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 peut construire une bonne relation et faciliter la discussion sur toutes les questions au travail qui doivent être négociées. à l'avenir. 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 à des bases de données, en particulier dans les applications à grande échelle et à haute concurrence développées sur la base de JAVA, il faut utiliser la connexion mise en commun.

L'avantage d'utiliser un pool de connexions est qu'il permet de limiter le nombre de connexions pour 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 créer une nouvelle connexion équivaut à MySQL. Un thread est créé.

Je viens également de mentionner que MySQL connaîtra une dégradation des performances à mesure que le nombre de connexions augmente.

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+. Tâche 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 a 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 et l'exécute. SQL complexe. Cela prendra au moins 10 minutes. Ensuite, ces 10 connexions ne pourront exécuter ce SQL complexe que dans un délai de 10 minutes, et tous les autres SQL suivants seront bloqués.

Rendre la plupart des applications 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 (besoins du DBA). pour effectuer des tests de comparaison de performances), permettant aux programmeurs de modifier le code, afin que le système puisse 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 du chemin. 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, en prenant notre entreprise comme exemple, notre système d'arrière-plan 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 de révision SQL (SQL Review) pour éliminer le SQL avec une logique invalide et le 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. le SLAVE est une structure monothread, provoquant des retards de synchronisation.

MySQL écrit du SQL et crée de petites transactions SQL, qui sont rapidement exécutées et soumises rapidement. Laissez chaque requête se terminer plus rapidement et la connexion sera libérée plus rapidement.

Après l'optimisation basée sur le partage ci-dessus, votre base de données est-elle devenue plus rapide ?

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!