Maîtriser MYSQL Avancé-tutoriel mysql-php.cn

Maîtriser MYSQL Avancé

Recommandations d'apprentissage gratuites : tutoriel vidéo MySQL

Répertoire d'articles

1 Avant-propos
- 1.1 Architecture de la base de données
- 1.2 Informations de surveillance
2 Impact Facteurs de base de données
- 2.1 QPS et TPS ultra-élevés
- 2.2 Grandes quantités de concurrence et utilisation ultra-élevée du processeur
- 2.3 E/S disque
- 2.4 Trafic de la carte réseau
- 2.5 Grande table
- - 2.5.1 Impact d'une grande table sur la requête
  - 2.5.2 Grande table sur DDL Impact des opérations
  - 2.5.3 Comment gérer les grandes tables dans la base de données
- 2.6 Transactions volumineuses
- - 2.6.1 Qu'est-ce qu'une transaction
  - 2.6.2 Atomicité de la transaction (ATOMICITY)
  - 2.6.3 Cohérence de la transaction (CONSISTENCE)
  - 2.6.4 Isolement de la transaction (ISOLATION)
  - 2.6.5 Durabilité des transactions (DURABILITÉ)
  - 2.6.7 Qu'est-ce qu'une grosse transaction

1 Avant-propos

Une grande partie de la pression sur le serveur provient des performances de la base de données. S'il n'y a pas de base de données et d'environnement de serveur stables, le serveur est sujet à certaines pannes, voire à des temps d'arrêt. , provoquant Les conséquences sont également incommensurables, l'optimisation des performances de la base de données est donc essentielle.

1.1 Architecture de la base de données

L'architecture générale de la base de données est un serveur maître, qui est équipé de plusieurs ou d'une douzaine de serveurs esclaves pour la synchronisation maître-esclave lorsque le serveur maître. Après un crash, le programmeur doit sélectionner manuellement un serveur esclave avec les dernières données pour prendre le relais du serveur maître, puis synchroniser le nouveau serveur maître. Cependant, parfois en raison du grand nombre de serveurs esclaves, ce processus prend beaucoup de temps et constitue également un défi pour la capacité de la carte réseau.

1.2 Informations de surveillance

QPS & TPS : Plus la valeur est élevée, mieux c'est.
Concurrence : le nombre de demandes traitées en même temps.
Utilisation du processeur : plus elle est faible, mieux c'est.
Disk IO : plus les performances de lecture et d'écriture sont élevées, mieux c'est.
Remarque : Il est généralement conseillé aux entreprises de ne pas effectuer de sauvegardes de base de données sur la base de données principale avant et après des promotions majeures, ni d'annuler de tels plans avant des événements à grande échelle, car cela affecterait sérieusement les performances du serveur.

2 Facteurs qui affectent la base de données

De nombreux facteurs affectent la base de données, tels que : la vitesse des requêtes SQL, le matériel du serveur, le trafic de la carte réseau, les E/S du disque, etc., nous le ferons plus tard, j'entrerai dans les détails. Voici une introduction à certaines des informations qui nous sont renvoyées dans les informations de surveillance et à la manière dont nous devrions les optimiser.

2.1 QPS et TPS ultra-élevés

En raison de l'inefficacité du SQL, il existe souvent des risques de QPS et TPS ultra-élevés. Pendant une période de promotion générale, le nombre de visites sur le site Web sera considérablement augmenté, ainsi que le QPS et le TPS de la base de données.
Qu'est-ce que QPS : requêtes traitées par seconde. Par exemple, si nous avons un processeur et pouvons traiter un SQL en 10 ms, nous pouvons traiter 100 SQL en 1 seconde, QPS<=100. Cependant, si nous ne traitons qu'un SQL en 100 ms, nous pouvons traiter 10 SQL en 1. deuxièmement, QPS< =10, ces deux situations sont très différentes, essayez donc d'optimiser les performances SQL.

2.2 Concurrence massive et utilisation ultra-élevée du processeur

Quels risques cela entraînera-t-il ?
Sous une grande concurrence, le nombre de connexions à la base de données peut être saturé. Dans ce cas, essayez de définir le paramètre de base de données max_connections sur une valeur plus grande (la valeur par défaut est 100). ce sera Une erreur 500 se produit.
En cas d'utilisation extrêmement élevée du processeur, des temps d'arrêt peuvent survenir en raison de l'épuisement des ressources du processeur.

2.3 E/S disque

L'un des goulots d'étranglement de la base de données est l'E/S disque, ce qui entraînera les risques suivants :

Disque soudain baisse des performances IO
Cela se produit souvent lorsque les données chaudes sont plus volumineuses que la mémoire disponible du serveur. Habituellement, cette situation ne peut être résolue qu'en utilisant un périphérique de disque plus rapide.
Autres tâches planifiées qui consomment beaucoup de performances du disque
Nous l'avons également mentionné ci-dessus. Il est préférable d'éviter de sauvegarder la base de données principale avant la grande promotion, ou de l'effectuer sur le serveur esclave, et d'ajuster. les tâches planifiées. Faites du bon travail dans la maintenance des disques.

2.4 Trafic de la carte réseau

De toute évidence, un trafic excessif de la carte réseau entraîne le risque que les E/S de la carte réseau soient pleines.
La carte réseau générale est une carte réseau Gigabit (1000Mb/8 ≈ 100Mo)
Si le nombre de connexions dépasse la capacité maximale de la carte réseau, le service ne pourra pas se connecter Alors comment éviter de l'être. impossible de se connecter à la base de données ? Situation :

Réduisez le nombre de serveurs esclaves
Parce que chaque serveur esclave doit copier les journaux du serveur maître, plus il y a de serveurs esclaves, plus le trafic de la carte réseau est important.
Effectuer une mise en cache hiérarchique
Assurez-vous d'éviter l'augmentation soudaine des visites back-end causée par la défaillance soudaine du cache front-end.
Évitez d'utiliser select * pour interroger
Il s'agit de la méthode la plus élémentaire d'optimisation de base de données. L'interrogation des champs inutiles consommera également beaucoup de trafic réseau.
Séparez le réseau d'entreprise et le réseau de serveurs
Cela peut éviter que la synchronisation maître-esclave ou la sauvegarde réseau n'affecte les performances du réseau.

2.5 Grande table

Quel genre de table peut-on appeler une grande table ? En fait, tout est relatif et il y aura des restrictions différentes selon les moteurs de stockage. Le stockage des données comme NoSQL ne limite pas le nombre de lignes dans la table, en théorie, elles peuvent être stockées aussi longtemps que la capacité du disque le permet. Mais lorsque le nombre de lignes dans une table dépasse des dizaines de millions de lignes, cela aura un impact important sur les performances de la base de données. Nous pouvons ensuite résumer les caractéristiques des grandes tables :

Le nombre de lignes d'enregistrement est énorme, et une seule table dépasse des dizaines de millions de lignes
Le fichier de données de la table est énorme, et le fichier de données de la table dépasse 10G

Mais même s'il répond aux caractéristiques ci-dessus, cela peut ne pas avoir un grand impact sur les performances de notre base de données, donc cette déclaration est relative, comme le. table de journalisation d'une base de données générale, même si le nombre de lignes enregistrées est grand, le fichier La taille est grande, mais nous ne faisons généralement que l'ajouter et l'interroger, et n'implique pas un grand nombre d'opérations de modification et de suppression, donc ce sera n'a pas un grand impact sur les performances de la base de données.
Mais lorsqu'un jour en raison de changements commerciaux, il sera nécessaire d'ajouter des colonnes à cette table de journal de 10G, alors la quantité de travail sera sans aucun doute énorme.

2.5.1 L'impact des grandes tables sur les requêtes

Les grandes tables représentent souvent la génération de requêtes lentes Les requêtes lentes signifient qu'il est difficile de filtrer au sein d'un certain. période de temps. Sortez les données requises.
Par exemple, dans une table de journal contenant des dizaines de millions de données, il existe un champ appelé source de commande, qui enregistre la plateforme sur laquelle la commande a été générée. Si l'entreprise n'en a pas besoin au début, cela n'aura pas d'impact sur les performances de la base de données. Cependant, en raison des besoins ultérieurs de l'entreprise, il est nécessaire de vérifier le volume de commandes de quelle plateforme proviennent ces dizaines de millions de données. C'est une grande question.
Comme il n'y a que quelques canaux sources pour ces commandes, la distinction est très faible, donc interroger certaines données parmi des dizaines de millions de données consommera beaucoup d'E/S disque et réduira considérablement l'efficacité du disque. Chaque fois qu'un utilisateur consulte une commande, la source de la commande sera interrogée dans la base de données, ce qui générera un grand nombre de requêtes lentes.

2.5.2 L'impact des grandes tables sur les opérations DDL

L'impact des grandes tables sur les opérations DDL, quels risques cela nous apporte-t-il ?

La création d'un index prend beaucoup de temps
Avant la version 5.5 de MySQL, la base de données verrouille la table lors de la création de l'index, mais après la version 5.5, la table ne serait pas verrouillée. mais comme le mécanisme de réplication de MySQL est exécuté sur le nouvel hôte avant de pouvoir être envoyé à l'esclave via les journaux, cela entraînera un long délai maître-esclave et affectera les activités normales.
Modifier la structure de la table nécessite de verrouiller la table pendant une longue période
Verrouiller la table pendant le processus de modification de la structure de la table nous entraînera un risque de long délai maître-esclave. En raison du mécanisme de réplication maître-esclave de notre MySQL, toutes les opérations de structure de table sont souvent effectuées d'abord sur l'hôte, puis transmises à l'esclave via le mode journal pour la même opération, puis la réplication maître-esclave de la structure de table est terminée. .
Supposons que nous modifiions la structure d'une table et que le temps de modification sur le serveur maître soit de 480 s, alors notre temps de modification sur la base de données esclave est également de 480 s. Étant donné que MySQL utilise actuellement un seul thread pour la réplication maître-esclave, une fois qu'une grande table est modifiée, les autres opérations de modification des données ne peuvent pas être effectuées jusqu'à ce que les opérations pertinentes soient terminées sur le serveur esclave. Par conséquent, cela entraînera un délai maître-esclave d'au moins 1 000 heures. au moins 480.
En même temps, cela affectera le fonctionnement normal des données, ce qui entraînera le blocage de toutes les opérations d'insertion, le nombre de connexions augmentera considérablement et remplira le serveur, ce qui provoquera une erreur de connexion 500 sur le serveur .

2.5.3 Comment gérer les grandes tables dans la base de données

Sous base de données et table, divisez une grande table en plusieurs petites tables
Difficulté :
1. Sélection des clés primaires pour les tables fractionnées
2. Requêtes et statistiques des données inter-partitions après fractionnement des tables
Archivage des données historiques de grandes tables
Fonction : Réduire l'impact sur les activités front-end et back-end
Difficulté :
1. Sélection du moment d'archivage
2. Comment effectuer l'opération d'archivage

2.6 Transactions importantes

2.6.1 Qu'est-ce qu'une transaction

Les transactions sont des systèmes de bases de données différents de tous les autres systèmes de fichiers. Une des caractéristiques importantes.
Une transaction est un ensemble d'instructions SQL atomiques ou une unité de travail indépendante. +
Par conséquent, les transactions doivent répondre aux quatre caractéristiques suivantes : atomicité, cohérence, isolation et durabilité.

2.6.2 ATOMICITÉ des transactions

Définition : Une transaction doit être considérée comme une unité de travail minimale indivisible. Toutes les opérations de la transaction entière sont soit soumises avec succès, soit toutes échouent. Pour une transaction, il est impossible d'effectuer seulement une partie des opérations.
Exemple :
A veut transférer 1 000 yuans vers B. Lorsque 1 000 yuans sont retirés du compte de A, le solde de A dans la base de données est soustrait de 1 000, mais lorsqu'il est ajouté au solde de B, le serveur tombe en panne. A Les 1 000 yuans doivent être restitués sur le compte de A pour maintenir l'atomicité de la transaction, qu'elle réussisse ou échoue ensemble.

2.6.3 Cohérence des transactions (CONSISTENCE)

Définition : La cohérence signifie qu'une transaction convertit la base de données d'un état de cohérence à un autre état de cohérence, l'intégrité des données dans la base de données n'est pas compromis avant le début et après la fin de la transaction.
Exemple :
Les 1000 blocs de A en banque sont transférés à B, le solde de A est de 0, le solde du compte de B passe de 0 à 1000, mais du début à la fin, A+B = 1000 (solde de A) + 0 (solde de B) = 0 (solde de A) + 1000 (solde de B), c'est-à-dire que le solde total de A et B reste inchangé, il est toujours de 1 000 yuans du début à la fin.

2.6.4 Isolation des transactions (ISOLATION)

Définition : L'isolation nécessite que la modification des données d'une transaction dans la base de données ne soit pas terminée lorsque d'autres transactions ne sont pas validées. Invisible.
Exemple :
La banque A retire 500 yuans du solde de 1 000 yuans. Avant que la transaction de retrait ne soit soumise, une transaction est exécutée pour interroger le solde du compte A. Le résultat de la requête est toujours le solde de 1 000. yuans, car avant que la transaction de retrait ne soit soumise, son processus de transaction est invisible pour les autres entreprises.

Quatre niveaux d'isolement définis dans le standard SQL
- LECTURE UNCOMMITED
  - Non validé les transactions sont visibles pour le monde extérieur, ce que nous appelons souvent une lecture sale, et les données interrogées sont appelées données sales.
- LECTURE COMMITÉE
  - Le niveau d'isolement par défaut dans un grand nombre de données, les données ne peuvent être lues qu'après la soumission de la transaction, et cela c'est-à-dire que la transaction n'est pas visible pour le monde extérieur.
- Lecture répétable (LECTURE RÉPÉTABLE)
  - Un niveau supérieur à la lecture validée, dans la transaction de niveau d'isolement de lecture répétable Dans une transaction non validée, les données de la table sont interrogées et, dans une autre transaction, une donnée est insérée dans la table et soumise. Cependant, lors du retour à la transaction non validée, les données de la table sont à nouveau interrogées et les données de la table sont interrogées. encore une fois, le résultat est le même et la donnée qui vient d'être insérée n'est pas interrogée.
  - Mais vous pouvez trouver les données tout à l'heure dans le niveau d'isolement en lecture validée
  - Afficher l'énoncé du niveau d'isolement de la base de données actuelle :
    show variables like '%iso%'
  - Modifier la déclaration actuelle du niveau d'isolement de la base de données :
    set session tx_isolation='read-committed'
- Sérialisable (SERIALIZABLE)
  - Le niveau d'isolement le plus élevé. Pour faire simple, chaque élément de données lu est verrouillé, ce qui peut entraîner un grand nombre de délais d'attente de verrouillage et de problèmes d'occupation de verrouillage. Par conséquent, dans les affaires réelles, nous utilisons rarement ce niveau d'isolement. Nous n’envisagerons d’utiliser ce niveau d’isolement que si la cohérence des données est strictement requise et qu’aucune concurrence n’est acceptable.
- Isolement :
  - Lecture non validéeConcurrence :
- Définition : Une fois qu'une transaction est validée, ses modifications seront permanentes Enregistrer dans la base de données. Même si le système tombe en panne à ce moment-là, les données modifiées soumises ne seront pas perdues (à l'exclusion des facteurs physiques tels que les dommages au disque).
  - L'utilisateur A de la banque dépose 1 000 yuans sur son compte. Une fois la transaction soumise, même si le système bancaire tombe en panne, après récupération, à moins que A n'opère sur le solde, les 1 000 yuans du compte A sont. Ce n'est pas cela qui va changer, c'est la pérennité de la transaction.