Un article pour vous aider à comprendre les principes sous-jacents de MYSQL

La colonne

tutoriel vidéo mysql présente les principes sous-jacents.

MYSQL

Un processus d'exécution SQL

Premier aperçu d'une requête SQL

• (Voici la documentation officielle de chaque moteur de stockage Moteur de stockage Mysql)

Une exécution SQL de mise à jour

L'exécution de la mise à jour démarre à partir de 客户端 => ··· => 执行引擎 C'est le même processus, vous devez d'abord retrouver ces données puis les mettre à jour. Pour comprendre le processus UPDATE, jetons d’abord un coup d’œil au modèle architectural d’Innodb.

Architecture Innodb

Dernier schéma d'architecture InnoDB officiel MYSQL :

Module interne

Connecteur (JDBC, ODBC, etc.) =>

[MYSQL Interne

[Connection Pool] (授权、线程复用、连接限制、内存检测等)
=>

[SQL Interface] (DML、DDL、Views等) [Parser] (Query Translation、Object privilege) [Optimizer] (Access Paths、 统计分析) [Caches & Buffers]
=>

[Pluggable Storage Engines]复制代码

]

=> [Fichier]

Structure de la mémoire

Là est un point clé ici. Lorsque nous interrogeons les données, nous prendrons d'abord le page que nous interrogeons actuellement et irons à buffer pool pour vérifier si 当前page est dans 缓冲池. Si c'est le cas, obtenez-le directement.

et si c'est update操作, la valeur en Buffer sera modifiée directement. À l'heure actuelle, les données dans buffer pool sont les mêmes que les données réellement stockées sur notre disque 不一致, qui s'appelle 脏页. De temps en temps, le moteur de stockage Innodb videra 脏页数据 sur le disque. De manière générale, lors de la mise à jour d'une donnée, nous devons lire les données dans buffer pour les modifier, puis les réécrire sur le disque pour terminer une opération 落盘IO.

Afin d'améliorer les performances de fonctionnement de update, Mysql a été optimisé en mémoire. Comme vous pouvez le constater, il y a une zone dans 架构图的缓冲池 appelée : change buffer. Comme son nom l'indique, 给change后的数据，做buffer的, lors de la mise à jour d'une donnée sans unique index, placez directement les données modifiées dans change buffer, puis terminez la mise à jour via l'opération merge, réduisant ainsi l'opération 落盘的IO.

• Il y a une condition comme nous l'avons mentionné ci-dessus : 没有唯一索引的数据更新时, pourquoi 没有唯一索引的数据更新时 doit-il être placé directement dans change buffer ? S'il y a 唯一约束的字段, après avoir mis à jour les données, les données mises à jour peuvent dupliquer les données existantes, de sorte que le caractère unique ne peut être déterminé qu'à partir du disque 把所有数据读出来比对.
• Ainsi, lorsque nos données sont 写多读少, nous pouvons ajuster la proportion de innodb_change_buffer_max_size dans change buffer en ajoutant buffer pool La valeur par défaut est 25 (soit : 25%)

.

La question revient, comment fonctionne la fusion ?

Il y a quatre situations :

1. S'il y a un autre accès, si l'on accède aux données de la page courante, il fusionnera avec le disque
2. Fusion programmée du fil d'arrière-plan
3. Avant que le système ne s'arrête normalement, fusionnez une fois
4. redo logLorsqu'il est plein, fusionnez avec le disque

1. Qu'est-ce que redo log

Quand il s'agit de refaire, nous devons parler de crash safe d'Innodb, qui est implémenté à l'aide de WAL (write Ahead Logging, enregistrez le journal avant d'écrire)

De cette façon, lorsque la base de données plante, les données peuvent être restaurées directement à partir de redo log pour garantir l'exactitude des données

Le journal redo est stocké dans deux fichiers par défaut ib_logfile0. Ces deux fichiers sont ib_logfile1. Pourquoi avez-vous besoin d'une taille fixe ? 固定大小的

Cela est dû aux caractéristiques de

de redo log, qui doit être un espace de stockage continu 顺序读取

2. Lecture et écriture aléatoires et lecture et écriture séquentielles

Jetez un oeil Photo

Généralement nos données sont dispersées sur le disque :

La séquence de lecture et d'écriture du disque dur mécanique est :

Localiser la piste
1. En attente de rotation vers le secteur correspondant
2. Commencer à lire et à écrire

Lecture et écriture à l'état solide :

1. Localisez directement la puce de mémoire flash (c'est pourquoi l'état solide est plus rapide que la mécanique)
2. Commencez la lecture et l'écriture

En fait, indépendamment de la mécanique ou du solide état, quand nous allons au magasin, ils traitent tous le disque via 文件系统, et ils le traitent de deux manières. 随机读写 et 顺序读写

1. Les données stockées par lecture et écriture aléatoires sont réparties dans différents 块 (par défaut 1bloc=8 secteurs=4K)
2. et stockage séquentiel, Comme son nom l'indique, les données sont réparties dans 一串连续的块, la vitesse de lecture est donc grandement améliorée

3. Retour à notre schéma d'architecture

Voir le buffer pool dans Log Buffer, qui est le tampon qui existait avant d'écrire le redo log

Ici, il existe trois stratégies d'exécution spécifiques pour le redo log :

0. Là Il n'est pas nécessaire d'écrire Log Buffer. Il vous suffit d'écrire les données du disque de rétablissement une fois par seconde. Les performances sont élevées, mais cela entraînera des problèmes de cohérence des données en 1 seconde. Applicable à 强实时性, 弱一致性, comme 评论区评论
1. écriture Log Buffer et écriture sur disque en même temps, avec les pires performances et la cohérence la plus élevée. Applicable à 弱实时性, 强一致性, tel que 支付场景
2. écrit Log Buffer et écrit sur os buffer en même temps (il appellera fsync toutes les secondes pour vider les données sur le disque ), avec de bonnes performances. La sécurité est également élevée. Il s'agit de 实时性适中 一致性适中, tel que 订单类.

Nous pouvons définir la stratégie d'exécution via innodb_flush_log_at_trx_commit. La valeur par défaut est 1

Résumé de la structure de la mémoire

1. Le pool de tampons est utilisé pour accélérer la lecture
2. Le tampon de changement est utilisé sans écriture non accélérée d'index uniques
3. Log Buffer est utilisé pour accélérer l'écriture du journal redo
4. 自适应Hash索引 est principalement utilisé pour accélérer les requêtes 页. Lors de l'interrogation, Innodb détermine si la requête en cours peut aller Hash索引 en surveillant le mécanisme de recherche d'index. Par exemple, l'opérateur LIKE et le caractère générique % ne peuvent pas être utilisés.

Structure du disque dur

1. L'espace de table système

est stocké dans un fichier appelé ibdata1, qui contient :

1. Le dictionnaire de données InnoDB stocke les métadonnées, telles que les informations sur la structure des tables, les index, etc.
2. Tampon à double écriture Lorsque Buffer Pool écrit une page de données, elle n'est pas écrite directement dans le fichier, mais d'abord dans cette zone. L'avantage de ceci est qu'une fois le système d'exploitation, le système de fichiers ou MySQL bloqué, les données peuvent être obtenues directement à partir de ce Buffer.
3. Changer le tampon Lorsque Mysql s'arrête, les modifications seront stockées sur le disque
4. Les journaux d'annulation enregistrent les opérations de modification des transactions

2.

Chaque table possède un fichier .ibd pour stocker les données et les index.

1. Avec 每表文件表空间, les performances de ALTER TABLE et TRUNCATE TABLE peuvent être grandement améliorées. Par exemple, ALTER TABLE sera effectué lors de la modification d'une table par rapport à une table résidant dans un espace table partagé, ce qui peut augmenter le 表复制操作 occupé par l'espace table. De telles opérations peuvent nécessiter autant d'espace supplémentaire que les données de la table et des index. L'espace n'est pas libéré vers le système d'exploitation comme 磁盘空间量. 每表文件表空间
Des fichiers de données d'espace de table par table peuvent être créés sur des périphériques de stockage distincts pour l'optimisation des E/S, la gestion de l'espace ou la sauvegarde. Cela signifie que les données et les structures des tables peuvent facilement migrer entre différentes bases de données.
2. Lorsque des données sont corrompues, que les sauvegardes ou les journaux binaires ne sont pas disponibles, ou qu'une instance de serveur MySQL ne peut pas être redémarrée, les tables stockées dans un seul fichier de données d'espace de table peuvent gagner du temps et augmenter les chances de réussite de la récupération.

Bien sûr, il y a des avantages et des inconvénients :

1. Le taux d'utilisation de l'espace de stockage est faible, il y aura une fragmentation, ce qui affectera les performances lorsque Drop table (sauf si vous gérez vous-même la fragmentation)
2. Car chaque table est divisée en ses propre fichier de table, le système d'exploitation ne peut pas fsync vider les données dans le fichier en même temps
3. mysqld continuera à maintenir 文件句柄 de chaque fichier de table pour fournir un accès continu au fichier

3. Tablespaces généraux

1. L'espace de table général est également appelé 共享表空间 Il peut stocker des 多个表 données
2. Si le même nombre de tables est stocké, la consommation de stockage. is每表表空间 小
3. La prise en charge du placement de partitions de table dans des espaces de table normaux est obsolète dans MySQL 5.7.24 et ne sera plus prise en charge dans une future version de MySQL.

4. Les tablespaces temporaires

sont stockés dans un fichier appelé ibtmp1. Dans des circonstances normales, Mysql créera un espace table temporaire au démarrage et supprimera l'espace table temporaire à l'arrêt. Et il peut s'étendre automatiquement.

5. Annuler les tablespaces

1. Fournir des opérations de modification 原子性, c'est-à-dire que lorsqu'une exception se produit au milieu d'une modification, vous pouvez revenir en arrière dans le journal d'annulation.
2. Il stocke les données d'origine avant le début de la transaction et cette opération de modification.
3. Le journal d'annulation existe dans le segment d'annulation et le segment d'annulation existe dans 系统表空间``撤销表空间``临时表空间, comme indiqué dans le diagramme d'architecture.

Redo Log

Comme mentionné précédemment

Pour résumer, que se passera-t-il lorsque nous exécutons une instruction SQL de mise à jour

1. requête vers us La donnée à modifier, nous l'appelons ici origin, est renvoyée à l'exécuteur
2. Dans l'exécuteur, la modification de la donnée s'appelle modification
3. et clignote <.> Mémoire, modificationBuffer PoolChange Buffer
Couche moteur : enregistrement du journal d'annulation (implémenter l'atomicité des transactions)
4. Couche moteur : enregistrement du journal de rétablissement (utilisé pour la récupération après incident)
5. Couche de service : enregistrement du journal du bac (enregistrement DDL)
6. Renvoyer le résultat de la mise à jour réussie
7. Les données sont en attente d'être vidées sur le disque par le thread de travail

Journal de la corbeille

mentionné

, Undo et d'ailleurs Redo.Bin log

Ce journal n'a pas grand-chose à voir avec le
1. moteur, comme nous l'avons mentionné plus tôt. Les deux journaux mentionnés se trouvent tous deux au niveau de la couche moteur innodb. Et innodb est dans Bin log. Il peut donc être utilisé par tous les moteurs 服务层
Quelle est sa fonction principale ? Tout d'abord,
2. enregistre chaque Bin log déclaration sous la forme d'un événement. C'est un journal au sens logique. DDL DML
peut implémenter
3. , et le serveur 主从复制 obtient le journal 从 du serveur 主 puis l'exécute. bin log
Faites
4. , récupérez le journal d'une certaine période et exécutez-le à nouveau. 数据恢复

Après avoir suivi une instruction SQL pour terminer l'aperçu global, regardons en arrière et enrichissons le SQL. Ajoutons une

Try索引

Magnifique ligne de segmentation

Index

---

Si vous voulez comprendre complètement ce qu'est

, vous devez comprendre son

InnoDB中的索引文件存储级别Stockage de fichiers Innodb Divisé en quatre niveaux

Pages , étendues, segments et espaces de table

Leur relation est :

Par défaut La taille de

est
• , c'est-à-dire, extent morceaux de 1M. La taille de page généralement mentionnée par notre système de fichiers est 64, contenant 16KB Page secteurs. 4KB8512ByteStructure de stockage B tree variante B+ tree

Donc parfois, on nous demande pourquoi la clé primaire doit être commandée, c'est-à-dire si nous. Sur un champ ordonné, créez un index puis insérez des données. Lors du stockage, innodb les stockera sur un par un dans l'ordre lorsqu'une page est pleine, il demandera une nouvelle page, puis continuera à enregistrer.

Mais si nos champs ne sont pas ordonnés, les emplacements de stockage seront sur des pages différentes. Lorsque nos données sont stockées sur un 页 qui a été

, cela provoquera

, formant ainsi 存满. 页

Plusieurs formes d'organisation d'index différentes

1. index clusterisé, comme le montre la B+树 figure ci-dessus, 行数据 est stocké sur le nœud enfant, et les index 排列的顺序 et 索引键值顺序 S'ils sont cohérents, c'est 聚簇索引. L'index de clé primaire est un index clusterisé. À l'exception de l'index de clé primaire, tous les autres sont des 辅助索引
2. index auxiliaires. Si nous créons un 辅助索引, seuls 自己的值 et 主键索引的值 sont stockés sur. ses nœuds feuilles. Cela signifie que si nous interrogeons toutes les données via l'index auxiliaire, nous rechercherons d'abord 辅助索引 dans 主键键值, puis irons dans 主键索引 pour trouver le 数据 associé. Ce processus s'appelle 回表
3. rowid Et s'il n'y a pas de 主键索引 ?
   1. n'a pas de clé primaire, mais a une clé unique et n'est pas nulle, alors 聚簇索引 sera créé sur la base de cette clé.
   2. Si vous n'avez aucun des éléments ci-dessus, ne vous inquiétez pas, innodb maintient un élément appelé rowid et le crée en fonction de cet identifiant 聚簇索引

Comment fonctionnent les index

Après avoir compris ce qu'est un index et quelle est sa structure. Voyons quand nous devons utiliser des index. Les comprendre peut mieux nous aider à créer des index corrects et efficaces

1. Ne créez pas d'index si la dispersion est faible, c'est-à-dire les données. S’il n’y a pas beaucoup de différence entre eux, il n’est pas nécessaire de créer un index. (En raison de la création de l'index, lors de l'interrogation, la plupart des données dans innodb sont les mêmes. Si je vais à l'index et qu'il n'y a aucune différence entre la table entière, ce sera 全表查询 directement). Par exemple, le domaine du genre. Cela gaspille beaucoup d’espace de stockage.

2. index de champ commun, tel que idx(name, class_name)

   1. Lors de l'exécution d'une requête select * from stu where class_name = xx and name = lzw, l'index idx peut également être utilisé, car le optimiseur Optimiser SQL pour name = lzw and class_name = xx
   2. Lorsque select ··· where name = lzw est nécessaire, il n'est pas nécessaire de créer un index name séparé. L'index idx
   3. 覆盖索引 sera. utilisé directement. Si tous les 所有数据 que nous interrogeons cette fois sont inclus dans l'index, il n'est plus nécessaire d'interroger 回表. Par exemple : select class_name from stu where name =lzw

3. index_condition_pushdown)

   1. Il existe un tel SQL, select *  from stu where name = lzw and class_name like '%xx'
   2. S'il existe non 索引条件下推, car il est suivi de la condition de requête de like '%xx', donc ici on passe d'abord par name basé sur idx联合索引 pour interroger plusieurs données, puis 回表 pour interroger 全量row数据, et puis procédez dans server层 Si les données trouvées par le filtrage des likes sont
   3. , alors les likes sont également filtrées directement sur 引擎层, ce qui équivaut à effectuer l'server层 opération de filtrage 下推到引擎层. Comme le montre la figure :

Notes sur l'indexation

1. Sur où, commander, rejoindre Lorsqu'il est utilisé fréquemment, des champs d'index
2. à forte dispersion peuvent être ajoutés pour créer un index
3. L'index conjoint place les champs à forte dispersion en premier (car il est d'abord mis en correspondance en fonction du premier champ, qui est mis en correspondance en premier). peut rapidement localiser l'emplacement des données. )
4. Les champs qui sont fréquemment mis à jour ne peuvent pas être indexés (en raison de 页分裂, l'index est stocké dans l'ordre. Si la page de stockage est pleine, son insertion à nouveau entraînera des fractionnements de page. )
5. Lors de l'utilisation de 函数 comme replace, sum, count, etc., l'index ne sera pas utilisé, il n'est donc pas nécessaire de créer un
6. supplémentaire lorsqu'une conversion implicite se produit. , comme la conversion d'une chaîne en int, l'index
n'est pas nécessaire
7. Pour les champs particulièrement longs, vous pouvez intercepter les premiers chiffres pour créer un index (vous pouvez utiliser select count(distinct left(name, 10))/count(*) pour voir la dispersion et décider. pour extraire les premiers chiffres)

• conseils : Exécutez un SQL, je ne peux pas dire exactement s'il peut utiliser l'index, après tout, c'est tout 优化器决定的. Par exemple, si vous utilisez l'optimiseur basé sur les coûts Cost Base Optimizer, utilisez l'optimisation ayant le coût le plus bas.

Après avoir compris l'index, nous pourrons ouvrir la copie du chapitre de verrouillage

Une autre magnifique ligne de démarcation

---

Chapitre de verrouillage

Quatre fonctionnalités majeures

Passons d'abord en revue quelques concepts de base que nous connaissons :

1. Atomicité (implémentée par Undo log)
2. Cohérence
3. Isolement
4. Persistance (récupération après crash, réalisée par Redo log + double écriture)

Les problèmes de cohérence de lecture doivent être résolus par le niveau d'isolation des transactions de la base de données (norme SQL92)

Prérequis, dans une transaction :

1. Lecture sale ( Lire les données que d'autres n'ont pas validé, puis d'autres l'ont annulé)
2. Lecture non répétable (les données ont été lues pour la première fois, puis quelqu'un d'autre a modifié le commit, l'a relu et a vu que quelqu'un d'autre avait validé) données)
3. Lecture fantôme (lecture des données nouvellement ajoutées par d'autres lors d'une requête de plage)

La norme SQL92 stipule : (La concurrence diminue de gauche à droite)

• conseils : Dans Innodb, la lecture fantôme de Repeatable Read ne peut pas exister car elle la résout d'elle-même

Comment résoudre le problème des lectures fantômes en lecture répétable (RR) dans Innodb

Modèle de verrouillage

1. LBCC (Lock Based Concurrency Control) Ajouter un verrou avant la lecture, mais cela peut entraîner des problèmes de performances => Le verrouillage lors de la lecture empêchera d'autres transactions de lire et d'écrire, ce qui entraînera de faibles performances
2. MVCC (Multi Version Concurrency Control) Enregistrez l'heure actuelle lors de la lecture de l'instantané, les autres peuvent simplement lire le snapshot => Consommation de performances, consommation de stockage

Ces deux solutions sont utilisées ensemble dans Innodb. Voici une brève explication de RR 的 MVCC实现. La valeur initiale de l'identifiant de restauration dans la figure ne doit pas être 0 mais NULL Pour plus de commodité, elle est écrite sous la forme 0

.
• RC的MVCC实现是对 同一个事务的多个读 创建一个版本 RR 是 同一个事务任何一条都创建一个版本

En combinant MVCC avec LBCC, InnoDB peut résoudre la situation de lecture fantôme dans 不加锁 conditions. Au lieu de devoir laisser la transaction Serializable se dérouler comme 串行, sans aucun 并发.

Examinons en profondeur comment InnoDB锁 implémente le RR niveau d'isolation des transactions

Les verrous approfondissent la mise en œuvre de MVCC dans Innodb

1. Verrous Innodb

1. Verrous partagés et exclusifs=> (S, X)
2. Verrous d'intention=> (IS, IX) 表级别

Ce qui précède

est 四把锁最基本锁的类型

Record Locks record locks
3. Gap Locks Gap Locks
4. Verrous à clé suivante

Ces trois verrous sont compris comme les trois méthodes algorithmiques implémentées ci-dessus

Considérons-les temporairement ici. Ils sont appelés : 四把锁高阶锁

<.>Insérer des verrous d'intention Insérer des verrous d'intention

6. Verrous AUTO-INC Verrous à clé à incrémentation automatique
7. Verrous de prédicat pour les index spatiaux Utilisation des index
8. Les trois ci-dessus sont supplémentaires verrous étendus

2. Explication détaillée des verrous en lecture-écriture

Pour utiliser les verrous partagés, après l'instruction Ajoutez

. Les verrous exclusifs sont utilisés par défaut
1. . Afficher en utilisant lock in share mode après la déclaration. Insert、Update、Deletefor updateLes verrous d'intention sont maintenus par la base de données elle-même. (La fonction principale est de donner à la table
pour enregistrer si la table est verrouillée) => S'il n'y a pas de verrouillage, lorsque d'autres transactions veulent verrouiller la table, elles doivent analyser la table entière pour voir s'il y a un verrouillage, ce qui est trop faible. C'est pourquoi les verrous d'intention existent.
2. 打一个标记
Supplément : Qu'est-ce qui est verrouillé dans Mysql ?

Ce qui est verrouillé, c'est l'index, donc à ce moment-là, quelqu'un voudra peut-être demander : et si je ne crée pas d'index ?

Nous avons parlé de l'existence des index ci-dessus. Passons en revue ici. Il existe plusieurs situations :

Vous avez créé une clé primaire, qui est un index clusterisé (qui stocke).

)
1. 完整的数据Il n'y a pas de clé primaire, mais il y a une clé unique. Si aucune n'est nulle, elle sera créée sur la base de cette clé
2. 聚簇索引Si vous ne le faites pas. Si vous n'avez ni l'un ni l'autre des éléments ci-dessus, ne vous inquiétez pas, innodb lui-même maintient quelque chose appelé
et crée
3. rowid聚簇索引
basé sur cet identifiant. Par conséquent, il doit y avoir un index dans un. table, donc bien sûr il y a toujours un index pour verrouiller le verrou Lived.

Lorsque vous effectuez

sur une table pour laquelle vous n'avez pas explicitement créé

, la base de données ne sait en fait pas quelles données rechercher et la table entière peut être utilisée. Alors juste 索引. 加锁查询

• Si vous ajoutez un verrou en écriture à 辅助索引, par exemple, select * from where name = ’xxx‘ for update doit enfin 回表 vérifier les informations sur la clé primaire, donc à ce moment, en plus du verrouillage 辅助索引, vous devez également verrouiller 主键索引

3. Explication détaillée des verrous d'ordre élevé

Tout d'abord, parlons de trois concepts. Il existe un tel ensemble de données. : la clé primaire est 1, 3, 6, 9 Lors du stockage, il se présente comme suit : x 1 x 3 x x 6 x x x 9 x... Gap lock, verrouille l'espace d'enregistrement, chacun

, (-∞,1), (1,3), (3,6), (6,9), (9,+∞) Lors du verrouillage, ce qui est verrouillé est (-∞,1], (1,3], (3,6], (6,9], (9,+∞], l'intervalle ouvert à gauche et fermé à droite

x d'abord Ces trois types de verrous sont tous

, et verrouillage à clé temporaire = verrouillage d'enregistrement + verrouillage d'espacement

排它锁

Lorsque

, un verrouillage d'enregistrement
1. select * from xxx where id = 3 for update est généré lorsque
, un verrou d'espacement est généré => (3,6) est verrouillé. Une chose à noter ici est qu'il n'y a pas de conflit entre les verrous d'espacement. Lorsque
2. select * from xxx where id = 5 for update, un verrou de clé temporaire est généré =. > lock. (3,6], mysql utilise des verrous à clé temporaires par défaut. Si les conditions 1 et 2 ne sont pas remplies, tous les verrous de ligne sont des verrous à clé temporaires
3. select * from xxx where id = 5 for update
Retour à la question de départ. , ici

empêche la modification ou la suppression d'autres transactions,

empêche l'ajout d'autres transactions et la combinaison
• forme une solution commune au Record Lock 行锁 problème de lecture fantôme lors de l'écriture des données Gap Lock 间隙锁. 🎜>Gap Lock 和 Record LockQuand il s'agit de verrous, nous devons parler de blocages Next-Key锁RR级别Vérifier après un blocage

Innodb_row_lock_current_waits Current Combien de verrous en attente

InnoDB_ROW_LOCK_TIME a attendu un temps total
1. show status like 'innodb_row_lock_%' InnoDB_ROW_LOCK_TIME_AVG moyenne
   InnoDB_ROW_LOW_TIME_MAX > Innodb_row_lock_waits Combien de fois sont apparues au total En attente de
   2. <.>
   3. peut afficher les transactions en cours et verrouillées
   5. =
   peut demander si
2. select * from information_schema.INNODB_TRX de
3. show full  processlistselect  *  from    information_schema.processlistPrévention des blocages 哪个用户在哪台机器host的哪个端口上连接哪个数据库Garantir l'ordre d'accès aux données 执行什么指令状态与时间 Évitez d'utiliser l'index lors de l'utilisation de Where (Cela verrouillera la table, ce qui non seulement rendra les blocages plus probables se produit, mais diminue également les performances)

Diviser une très grosse transaction en plusieurs petites transactions

Utilisez autant que possible des requêtes équivalentes (même si les requêtes Range doivent également être limitées à un intervalle, pas juste ouvert ou fermé. Par exemple, si id > 1, verrouillez tout ce qui suit)

1. Optimisation
2. Sous-base de données et sous-table
Sélection dynamique de sources de données
4. Couche d'encodage - implémentation de AbstractRoutingDataSource => Couche framework - implémentation du plugin Mybatis => Couche pilote - Sharding-JDBC (configurer plusieurs sources de données, stocker les données dans des bases de données et des tables séparées selon des stratégies de mise en œuvre personnalisées), analyse SQL => optimisation de l'exécution => diviser les tables et modifier les noms des tables)=>Exécution SQL=>fusion des résultats) => Couche proxy - Mycat (indépendant de toutes les connexions à la base de données. Toutes les connexions sont établies par Mycat et d'autres services accèdent à Mycat pour obtenir des données) => Couche de service - version SQL spéciale

Comment optimiser MYSQL

Après tout, nous apprenons tellement de connaissances pour mieux utiliser MYSQL, alors pratiquons-le et établissons un système d'optimisation complet

Si vous souhaitez obtenir de meilleures performances de requête, vous pouvez commencer à partir de ceci

1. Pool de connexions client

Ajouter un pool de connexions à. évitez-le à chaque foisAlors plus nous avons de pools de connexions, mieux c'est ? Les amis intéressés peuvent lire cet article : À propos du dimensionnement de la piscine

Je vais le résumer grossièrement : 查询执行过程

1. Notre exécution simultanée de SQL ne deviendra pas plus rapide à mesure que le nombre de connexions augmente. Pourquoi? Si j'ai 10 000 connexions exécutées simultanément, cela ne serait-il pas beaucoup plus rapide que vos 10 connexions. La réponse est non, non seulement ce n'est pas rapide, mais cela devient de plus en plus lent ?
   1. Dans les ordinateurs, nous savons tous que seul CPU peut réellement exécuter 线程. Parce que le système d'exploitation utilise la technologie 时间分片, cela nous fait penser qu'un CPU内核 a exécuté 多个线程.
   2. Mais en fait, le CPU précédent ne peut en exécuter qu'un seul 时间段 à un certain 线程, donc peu importe la façon dont nous augmentons la simultanéité, CPU ne peut toujours traiter qu'une quantité limitée de données pendant cette période période.
   3. Alors même si CPU ne peut pas traiter autant de données, pourquoi va-t-il ralentir ? Parce que 时间分片, lorsque plusieurs threads semblent être dans "同时执行", en fait, le 上下文切换 entre eux prend beaucoup de temps
   4. Par conséquent, une fois que le nombre de threads dépasse le nombre de cœurs de processeur. , augmentez le nombre de threads. Le système sera simplement plus lent, pas plus rapide.
2. Bien sûr, ce n'est que la raison principale. Le disque aura également un impact sur la vitesse, et il aura également un impact sur la configuration de notre numéro de connexion.
   1. Par exemple, avec le disque dur mécanique que nous utilisons, nous devons le faire pivoter vers un certain emplacement, puis effectuer l'opération I/O À ce moment, CPU peut découper le temps vers un autre , pour améliorer l'efficacité et la vitesse du traitement线程
   Donc, si vous utilisez un disque dur mécanique, nous pouvons généralement ajouter plus de connexions pour maintenir une concurrence élevée
   2. Mais que se passe-t-il si vous utilisez un SSD ? Parce que
   3. le temps d'attente est très court, on ne peut pas ajouter trop de connexions I/O
Autrement dit, vous devez suivre cette formule :
3. . Par exemple, une 线程数 = ((核心数 * 2) + 有效磁盘数) machine vaut 4 * 2 + 1 = 9 i7 4core 1hard disk
Je me demande si vous connaissez cette formule. Cela s'applique non seulement aux connexions à des bases de données, mais aussi à tout
4. tel que : Définissez le nombre maximum de threads, etc.很多CPU计算和I/O的场景

2. Plan global de conception de la base de données

Cache tiers

Si la concurrence est très importante, vous ne pouvez pas laisser ils accèdent tous à la base de données, lorsque le client se connecte à la base de données pour une requête, ajoutez un cache tiers tel que

Redis

déployez la base de données en mode cluster

Depuis l'une de nos bases de données ne peut pas résister à une énorme concurrence, pourquoi ne pas en ajouter quelques-uns supplémentaires. Où est la machine ? Diagramme schématique de réplication maître-esclave

À partir du diagramme, nous pouvons facilement voir les caractéristiques de la réplication maître-esclave Mysql

读写分离. 异步复制

conseils : après avoir écrit
• à Binary Log, relay log enregistrera la dernière lecture slave à Binary Log Position, et commencera directement à partir de cet endroit la prochaine fois. Allez le chercher. master info

Différentes méthodes de réplication maître-esclave

Un problème évident avec la

réplication maître-esclave ci-dessus est qu'elle n'est pas mise à jour à temps. Lorsqu'une donnée est écrite et immédiatement lue par un utilisateur, les données lues sont toujours les données précédentes, ce qui signifie qu'il y a un délai. Pour résoudre le problème de retard, vous devez introduire 异步事务

une réplication entièrement synchrone, qui est exécutée en mode transaction. Le nœud maître écrit en premier, puis tous les nœuds esclaves doivent écrire. les données. Une fois terminé, le succès de l'écriture sera renvoyé, ce qui affectera grandement les performances d'écriture
1. Réplication semi-synchrone, tant qu'il y a une seule salve qui écrit des données, elle est considérée comme réussie. (Si une réplication semi-synchrone est requise, les nœuds maître et esclave doivent installer les plug-ins semisync_mater.so et semisync_slave.so)
2. Réplication GTID (identités de transaction globales), lorsque la base de données maître se réplique dans en parallèle, la base de données esclave se réplique également en parallèle, résolu Le délai de réplication de synchronisation maître-esclave réalise l'action automatique
3. , c'est-à-dire que si le nœud maître raccroche et que le nœud esclave est élu, la perte de données peut être rapidement et automatiquement évitée . failover

Solution haute disponibilité de cluster

Maître-esclave HAPrxoy + keeplive
1. NDB
2. Glaera Cluster pour MySQL
3. MHA (Gestionnaire de réplication Master-Mater pour MySQL), MMM (MySQL Master High Available)
4. MGR (MySQL Group Replication) => MySQL Cluster

Table

Catégoriser et diviser les données en différentes tables pour réduire l'impact excessif sur une seule table

impactant les performances锁操作

Structure de la table

1. Concevoir des types de champs raisonnables
2. Concevoir une longueur de champ raisonnable

3. Optimiseur et moteur d'exécution

Lent. log

est activé show_query_log, et SQL dont le temps d'exécution dépasse la variable long_query_time sera enregistré. Vous pouvez utiliser mysqldumpslow /var/lib/mysql/mysql-slow.log, et il existe de nombreux plug-ins qui peuvent fournir une analyse plus élégante que celle-ci, je n'entrerai donc pas dans les détails ici.

Expliquez l'analyse SQL

Tout SQL doit être explainrévisé

après l'avoir écrit

1. Table des pilotes - Par exemple, un abus de left/right join entraîne de faibles performances

1. L'utilisation de left/right join spécifiera directement la table des pilotes Dans MYSQL, Nest loop join est utilisée par défaut. pour l'association de tables (c'est-à-dire via L'ensemble de résultats de 驱动表 est utilisé comme données de base du cycle, puis les données de la table associée suivante sont filtrées à travers chaque élément de données de cet ensemble, et enfin les résultats sont fusionnés pour obtenir ce que l'on appelle souvent 临时表).
2. Si les données de 驱动表 sont au niveau 百万千万, vous pouvez imaginer à quel point cette requête de table commune sera lente. Mais à l'inverse, si 小表 est utilisé comme 驱动表, la requête 千万级表 à l'aide de 索引 peut devenir très rapide.
3. Si vous n'êtes pas sûr de qui doit être utilisé comme 驱动表, veuillez laisser l'optimiseur décider, par exemple : select xxx from table1, table2, table3 where ···, l'optimiseur utilisera la table avec un petit nombre de lignes d'enregistrement de requête comme table de conduite.
4. Si vous souhaitez simplement préciser 驱动表 vous-même, veuillez tenir l'arme Explain Parmi les résultats de Explain, le premier est trié par 基础驱动表
5. . De même, il existe une grande différence de performances dans le tri des différents 表. Nous essayons de trier 驱动表 au lieu de 临时表，也就是合并后的结果集. Autrement dit, si using temporary apparaît dans le plan d'exécution, il doit être optimisé.

2. La signification de chaque paramètre du plan d'exécution

1. select_type (type de requête) : 普通查询 et 复杂查询 (requête conjointe, sous-requête, etc. .)
   1. SIMPLE, la requête ne contient pas de sous-requêtes ou UNION
   2. PRIMARY Si la requête contient la sous-structure de 复杂查询, alors vous devez utiliser la requête de clé primaire.
   3. SUBQUERY , inclure selectwhere子查询
   dans
   4. ou DERIVED, inclure la sous-requête from
   5. dans UNION RESULT, interroger la sous-requête from union table
2. table Le nom de la table utilisé
3. type (type d'accès), la manière de trouver les lignes requises, de haut en bas, la vitesse de requête 越来越快
   1. const或者system Analyse à niveau constant, le moyen le plus rapide d'interroger une table, le système est un cas particulier de const (il n'y a qu'une seule donnée dans la table)
   2. eq_ref Analyse d'index unique
   3. ref Analyse d'index non unique
   4. range Analyse de plage d'index, telle qu'entre, et d'autres requêtes de plage
   5. index (index complet) scan Tous les arbres d'index
   6. ALL Scannez la table entière
   7. NULL, pas besoin d'accéder à la table ou à l'index
4. possible_keys, indiquez quel index utiliser. Les enregistrements dans la table peuvent être trouvés. L'index 不一定使用
5. clé listée ici : enfin 哪一个索引被真正使用 est arrivée. NULL si non disponible
6. key_len : le nombre d'octets occupés par l'index utilisé
7. ref : quel champ ou constante est utilisé avec 索引（key）
8. lignes : total Comment de nombreuses lignes ont été analysées
9. filtrées (pourcentage) : quantité de données également filtrées au niveau de la couche serveur
10. Extra : informations supplémentaires
    1. only index Les informations doivent uniquement être obtenu à partir de l'index On constate qu'un index de couverture peut être utilisé, et la requête est très rapide
    2. using where Si la requête n'utilise pas d'index, elle sera filtrée au niveau de la couche server et puis utilisez where pour filtrer l'ensemble de résultats
    3. impossible where Rien n'a été trouvé
    4. using filesort, tant qu'il n'est pas trié par index, mais que d'autres méthodes de tri sont utilisées, il est un tri de fichiers
    5. using temporary (cela doit être fait via une table temporaire Stocker temporairement l'ensemble de résultats puis effectuer des calculs) De manière générale, dans ce cas, DISTINCT、排序、分组
    6. using index condition Le push-down d'index est effectué. Comme mentionné ci-dessus, cela est effectué par server层 Opération de filtrage 下推到引擎层

4. Moteur de stockage

<.> Lorsqu'il n'y a que beaucoup de
1. , vous pouvez utiliser 插入与查询 Moteur de stockage MyISAM
Lorsque seules des données temporaires sont utilisées, vous pouvez utiliser
2. memory
Quand
3. et d'autres nombres simultanés sont importants, vous pouvez utiliser 插入、更新、查询InnoDB

Résumé

Répondre à l'optimisation MYSQL à partir de cinq niveaux, de haut en bas

SQL et index
1. Moteur de stockage et structure des tables
2. Architecture de la base de données
3. Configuration MySQL
4. Matériel et système d'exploitation

En plus , l'interrogation des données est lente, nous ne devons donc pas simplement « optimiser » aveuglément la base de données, mais également commencer par analyser au niveau des applications métier. Par exemple, mettre en cache les données, limiter les requêtes, etc.

Rendez-vous dans le prochain article

Recommandations d'apprentissage gratuites associées : Tutoriel vidéo MySQL

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!