Avant MySQL 5.5, le moteur de stockage par défaut était MylSAM, et après la version 5.5, il est devenu InnoDB.
L'index de hachage pris en charge par InnoDB est adaptatif. InnoDB générera automatiquement un index de hachage pour la table en fonction de l'utilisation de la table. L'intervention humaine n'est pas autorisée pour générer un index de hachage dans une table.
À partir de MySQL 5.6, InnoDB prend en charge l'indexation de texte intégral.
17. Comment choisir un moteur de stockage ?
Vous pouvez grossièrement choisir comme ceci :
- Dans la plupart des cas, utiliser InnoDB par défaut est suffisant. Si vous souhaitez fournir des fonctionnalités de sécurité des transactions (compatibilité ACID) pour la validation, la restauration et la récupération, et nécessiter un contrôle de concurrence, InnoDB est le premier choix.
- Si la table de données est principalement utilisée pour insérer et interroger des enregistrements, le moteur MyISAM offre une efficacité de traitement plus élevée.
- Si les données ne sont stockées que temporairement, que la quantité de données n'est pas importante et qu'une sécurité élevée des données n'est pas requise, vous pouvez choisir de sauvegarder les données dans le moteur MEMORY dans la mémoire. MySQL pour stocker les résultats intermédiaires de la requête.
Le moteur à utiliser peut être choisi de manière flexible en fonction des besoins, car le moteur de stockage est basé sur des tables, de sorte que plusieurs tables d'une base de données peuvent utiliser différents moteurs pour répondre à diverses performances et besoins réels. L'utilisation du moteur de stockage approprié améliorera les performances de l'ensemble de la base de données.
18.Quelles sont les principales différences entre InnoDB et MylSAM ?
PS : MySQL8.0 devient lentement populaire S'il ne s'agit pas d'une interview, vous n'avez pas besoin d'en savoir beaucoup sur MylSAM.
1. Structure de stockage : Chaque MyISAM est stocké dans trois fichiers sur le disque ; toutes les tables InnoDB sont stockées dans le même fichier de données (il peut également s'agir de plusieurs fichiers, ou d'un fichier d'espaces de table indépendant), de la taille. de la table InnoDB n'est limité que par la taille du fichier du système d'exploitation, qui est généralement de 2 Go.
2. Prise en charge des transactions : MyISAM ne fournit pas de prise en charge des transactions ; InnoDB fournit une prise en charge des transactions et dispose de fonctionnalités de sécurité des transactions (validation), de restauration (rollback) et de récupération après incident (capacités de récupération après incident).
3 Granularité minimale du verrouillage : MyISAM ne prend en charge que les verrous au niveau de la table. La table entière sera verrouillée pendant les mises à jour, ce qui entraînera le blocage des autres requêtes et mises à jour.
4. Type d'index : l'index de MyISAM est un index clusterisé et la structure de données est un arbre B ; l'index d'InnoDB est un index non clusterisé et la structure de données est un arbre B+.
5. La clé primaire est requise : MyISAM permet aux tables sans aucun index ni clé primaire d'exister ; si InnoDB ne définit pas de clé primaire ou d'index unique non vide, générera automatiquement une clé primaire de 6 octets ( invisible pour l'utilisateur), Les données font partie de l'index principal et l'index supplémentaire enregistre la valeur de l'index principal.
6. Le nombre spécifique de lignes dans le tableau : MyISAM enregistre le nombre total de lignes dans le tableau. Si vous sélectionnez count() dans le tableau ;, la valeur sera extraite directement ; nombre total de lignes dans la table. Si vous utilisez select count() from table; parcourra toute la table mais après avoir ajouté la condition wehre, MyISAM et InnoDB la gèrent de la même manière.
7. Prise en charge des clés étrangères : MyISAM ne prend pas en charge les clés étrangères ; InnoDB prend en charge les clés étrangères.
Journaux
19. Que sont les fichiers journaux MySQL ? Introduire les fonctions respectivement ?
Il existe de nombreux fichiers journaux MySQL, notamment :
-
Journal des erreurs (journal des erreurs) : le fichier journal des erreurs enregistre les processus de démarrage, d'exécution et d'arrêt de MySQL et peut aider à localiser les problèmes MySQL.
-
Slow query log (slow query log) : Le journal des requêtes lentes est utilisé pour enregistrer les instructions de requête dont le temps d'exécution dépasse la longueur définie par la variable long_query_time. Grâce au journal des requêtes lentes, vous pouvez découvrir quelles instructions de requête ont une faible efficacité d'exécution pour l'optimisation.
-
Journal des requêtes générales (journal général) : Le journal des requêtes générales enregistre toutes les informations demandées pour la base de données MySQL, que la requête soit exécutée correctement ou non.
-
Binary log (bin log) : Concernant le log binaire, il enregistre toutes les instructions DDL et DML exécutées par la base de données (sauf les instructions de requête de données select, show, etc.), enregistrées sous forme d'événements et sauvegardées en binaire fichiers.
Il existe également deux fichiers journaux spécifiques au moteur de stockage InnoDB :
-
Redo log (redo log) : les redo logs sont cruciaux car ils enregistrent les journaux de transactions pour le moteur de stockage InnoDB.
-
Rollback log (undo log) : Le journal de restauration est également un journal fourni par le moteur InnoDB. Comme son nom l'indique, la fonction du journal de restauration est de restaurer les données. Lorsqu'une transaction modifie la base de données, le moteur InnoDB enregistrera non seulement le journal de rétablissement, mais générera également le journal d'annulation correspondant ; si l'exécution de la transaction échoue ou si un rollback est appelé, provoquant l'annulation de la transaction, les informations contenues dans le journal d'annulation ; peut être utilisé pour restaurer les données jusqu'à ce qu'elles étaient avant la modification.
20. Quelle est la différence entre binlog et redo log ?
- bin log enregistrera tous les enregistrements de journaux liés à la base de données, y compris les journaux des moteurs de stockage tels que InnoDB et MyISAM, tandis que le journal redo enregistre uniquement les journaux du moteur de stockage InnoDB.
- Le contenu enregistré est différent. Le journal bin enregistre le contenu opérationnel spécifique d'une transaction, c'est-à-dire que le journal est un journal logique. Le journal redo enregistre les modifications physiques apportées à chaque page (Page).
- Le temps d'écriture est différent. Le journal bin n'est validé qu'avant la validation de la transaction, c'est-à-dire qu'il n'est écrit qu'une seule fois sur le disque. Pendant que la transaction est en cours, les tentatives de rétablissement sont constamment écrites dans le journal de rétablissement.
- Les méthodes d'écriture sont également différentes. Le journal Redo consiste en une écriture et un effacement cycliques, tandis que le journal bin ajoute une écriture et n'écrasera pas les fichiers déjà écrits.
21. Comprenez-vous comment exécuter une instruction de mise à jour ?
L'exécution de l'instruction de mise à jour est complétée par la coopération de la couche serveur et de la couche moteur. En plus d'écrire les données dans la table, les journaux correspondants doivent également être enregistrés.
L'exécuteur cherche d'abord le moteur pour obtenir la ligne ID=2. L'ID est la clé primaire et le moteur de stockage récupère les données et trouve cette ligne. Si la page de données où se trouve la ligne avec ID=2 est déjà dans la mémoire, elle sera renvoyée directement à l'exécuteur sinon, elle devra d'abord être lue dans la mémoire à partir du disque puis renvoyée ;
L'exécuteur récupère les données de ligne fournies par le moteur, ajoute 1 à cette valeur, par exemple, c'était N avant, mais maintenant c'est N+1, obtient une nouvelle ligne de données, puis appelle le moteur interface pour écrire cette nouvelle ligne de données.
Le moteur met à jour cette nouvelle ligne de données dans la mémoire et enregistre l'opération de mise à jour dans le journal redo. À ce moment, le journal redo est à l'état de préparation. Informez ensuite l'exécuteur testamentaire que l'exécution est terminée et que la transaction peut être soumise à tout moment.
L'exécuteur génère le binlog de cette opération et écrit le binlog sur le disque.
L'exécuteur appelle l'interface de transaction de validation du moteur, et le moteur modifie le journal de rétablissement qui vient d'être écrit à l'état de validation, et la mise à jour est terminée.
Comme le montre la figure ci-dessus, lorsque MySQL exécute l'instruction de mise à jour, il analyse et exécute l'instruction dans la couche de service, extrait et stocke les données dans la couche moteur en même temps, écrit le binlog dans le fichier binlog ; couche de service, et le journal Redo est écrit dans InnoDB.
De plus, il y a deux étapes de soumission lors de l'écriture du journal redo. L'une est l'écriture de l'état prepare avant l'écriture du binlog, et l'autre est commit après l'écriture du binlog. .code> écriture du statut. <code>prepare状态的写入，二是binlog写入之后commit状态的写入。
22.那为什么要两阶段提交呢？
为什么要两阶段提交呢？直接提交不行吗？
我们可以假设不采用两阶段提交的方式，而是采用“单阶段”进行提交，即要么先写入redo log，后写入binlog；要么先写入binlog，后写入redo log。这两种方式的提交都会导致原先数据库的状态和被恢复后的数据库的状态不一致。
先写入redo log，后写入binlog：
在写完redo log之后，数据此时具有crash-safe能力，因此系统崩溃，数据会恢复成事务开始之前的状态。但是，若在redo log写完时候，binlog写入之前，系统发生了宕机。此时binlog没有对上面的更新语句进行保存，导致当使用binlog进行数据库的备份或者恢复时，就少了上述的更新语句。从而使得id=2这一行的数据没有被更新。
先写入binlog，后写入redo log：
写完binlog之后，所有的语句都被保存，所以通过binlog复制或恢复出来的数据库中id=2这一行的数据会被更新为a=1。但是如果在redo log写入之前，系统崩溃，那么redo log中记录的这个事务会无效，导致实际数据库中id=2
22. Alors pourquoi y a-t-il une soumission en deux étapes ?
Pourquoi une soumission en deux étapes ? Tu ne peux pas simplement le soumettre directement ? 
Nous pouvons supposer qu'au lieu d'utiliser une méthode de soumission en deux phases, nous adoptons une soumission « en une seule phase », c'est-à-dire soit écrire d'abord le redo log, puis écrire le binlog, soit écrire d'abord le binlog, puis écrire le redo log. La soumission de ces deux manières entraînera une incohérence entre l'état de la base de données d'origine et l'état de la base de données restaurée. 🎜Écrivez d'abord le journal de rétablissement, puis écrivez le journal binaire : 🎜🎜🎜Après avoir écrit le journal de rétablissement, les données ont une capacité sécurité en cas de crash à ce moment-là, de sorte que le système plante et les données seront restaurées dans le état précédent du début de la transaction. Cependant, si le système plante lorsque le journal redo est terminé et avant que le journal binaire ne soit écrit, le système plante. Pour le moment, binlog n'enregistre pas l'instruction de mise à jour ci-dessus, ce qui entraîne l'absence de l'instruction de mise à jour ci-dessus lorsque binlog est utilisé pour sauvegarder ou restaurer la base de données. Par conséquent, les données de la ligne id=2 ne sont pas mises à jour. 🎜🎜🎜🎜🎜 Écrivez d'abord dans binlog, puis dans refaire le journal : 🎜🎜🎜Après avoir écrit binlog, toutes les instructions sont enregistrées, donc les données de la ligne id=2 dans la base de données copiées ou restaurées via binlog seront mises à jour en a=1 . Cependant, si le système tombe en panne avant l'écriture du journal redo, la transaction enregistrée dans le journal redo sera invalide, ce qui entraînera la non mise à jour des données de la ligne id=2 dans la base de données réelle. 🎜🎜🎜🎜🎜En termes simples, le redo log et le binlog peuvent être utilisés pour représenter l'état de validation d'une transaction, et la validation en deux phases consiste à maintenir les deux états logiquement cohérents. 🎜23.redo log怎么刷入磁盘的知道吗？
redo log的写入不是直接落到磁盘，而是在内存中设置了一片称之为redo log buffer的连续内存空间，也就是redo 日志缓冲区。
什么时候会刷入磁盘？
在如下的一些情况中，log buffer的数据会刷入磁盘：
log buffer 的大小是有限的，如果不停的往这个有限大小的 log buffer 里塞入日志，很快它就会被填满。如果当前写入 log buffer 的redo 日志量已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。
在事务提交时，为了保证持久性，会把log buffer中的日志全部刷到磁盘。注意，这时候，除了本事务的，可能还会刷入其它事务的日志。
有一个后台线程，大约每秒都会刷新一次log buffer中的redo log到磁盘。
重做日志缓存、重做日志文件都是以块（block） 的方式进行保存的，称之为重做日志块（redo log block） ,块的大小是固定的512字节。我们的redo log它是固定大小的，可以看作是一个逻辑上的 log group，由一定数量的log block 组成。
它的写入方式是从头到尾开始写，写到末尾又回到开头循环写。
其中有两个标记位置：
write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到磁盘。
当write_pos追上checkpoint时，表示redo log日志已经写满。这时候就不能接着往里写数据了，需要执行checkpoint规则腾出可写空间。
所谓的checkpoint规则，就是checkpoint触发后，将buffer中日志页都刷到磁盘。
SQL 优化
24.慢SQL如何定位呢？
慢SQL的监控主要通过两个途径：
-
慢查询日志：开启MySQL的慢查询日志，再通过一些工具比如mysqldumpslow去分析对应的慢查询日志，当然现在一般的云厂商都提供了可视化的平台。
-
服务监控：可以在业务的基建中加入对慢SQL的监控，常见的方案有字节码插桩、连接池扩展、ORM框架过程，对服务运行中的慢SQL进行监控和告警。
25.有哪些方式优化慢SQL？
慢SQL的优化，主要从两个方面考虑，SQL语句本身的优化，以及数据库设计的优化。
避免不必要的列
这个是老生常谈，但还是经常会出的情况，SQL查询的时候，应该只查询需要的列，而不要包含额外的列，像slect * 这种写法应该尽量避免。
分页优化
在数据量比较大，分页比较深的情况下，需要考虑分页的优化。
例如：
select * from table where type = 2 and level = 9 order by id asc limit 190289,10;
优化方案：
-
延迟关联
先通过where条件提取出主键，在将该表与原数据表关联，通过主键id提取数据行，而不是通过原来的二级索引提取数据行
例如：
select a.* from table a,
(select id from table where type = 2 and level = 9 order by id asc limit 190289,10 ) b
where a.id = b.id
-
书签方式
书签方式就是找到limit第一个参数对应的主键值，根据这个主键值再去过滤并limit
例如：
select * from table where id >
(select * from table where type = 2 and level = 9 order by id asc limit 190
索引优化
合理地设计和使用索引，是优化慢SQL的利器。
利用覆盖索引
InnoDB使用非主键索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引
例如对于如下查询：
select name from test where city='上海'
我们将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取
alter table test add index idx_city_name (city, name);
低版本避免使用or查询
在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，高版本引入了索引合并，解决了这个问题。
避免使用 != 或者 操作符
SQL中，不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即使比较的字段上有索引
解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描
例如，把column’aaa’，改成column>’aaa’ or column，就可以使用索引了
适当使用前缀索引
适当地使用前缀所云，可以降低索引的空间占用，提高索引的查询效率。
比如，邮箱的后缀都是固定的“@xxx.com”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引
alter table test add index index2(email(6));
PS:需要注意的是，前缀索引也存在缺点，MySQL无法利用前缀索引做order by和group by 操作，也无法作为覆盖索引
避免列上函数运算
要避免在列字段上进行算术运算或其他表达式运算，否则可能会导致存储引擎无法正确使用索引，从而影响了查询的效率
select * from test where id + 1 = 50;
select * from test where month(updateTime) = 7;
正确使用联合索引
使用联合索引的时候，注意最左匹配原则。
JOIN优化
优化子查询
尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大
小表驱动大表
关联查询的时候要拿小表去驱动大表，因为关联的时候，MySQL内部会遍历驱动表，再去连接被驱动表。
比如left join，左表就是驱动表，A表小于B表，建立连接的次数就少，查询速度就被加快了。
select name from A left join B ;
适当增加冗余字段
增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略
避免使用JOIN关联太多的表
《阿里巴巴Java开发手册》规定不要join超过三张表，第一join太多降低查询的速度，第二join的buffer会占用更多的内存。
如果不可避免要join多张表，可以考虑使用数据异构的方式异构到ES中查询。
排序优化
利用索引扫描做排序
MySQL有两种方式生成有序结果：其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的
但是如果索引不能覆盖查询所需列，就不得不每扫描一条记录回表查询一次，这个读操作是随机IO，通常会比顺序全表扫描还慢
因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行
例如：
--建立索引（date,staff_id,customer_id）
select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id;
只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序
UNION优化
条件下推
MySQL处理union的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在union查询中都会失效，因为它无法利用索引
最好手工将where、limit等子句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化
此外，除非确实需要服务器去重，一定要使用union all，如果不加all关键字，MySQL会给临时表加上distinct选项，这会导致对整个临时表做唯一性检查，代价很高。
26.怎么看执行计划（explain），如何理解其中各个字段的含义？
explain是sql优化的利器，除了优化慢sql，平时的sql编写，也应该先explain，查看一下执行计划，看看是否还有优化的空间。
直接在 select 语句之前增加explain关键字，就会返回执行计划的信息。
id Colonne : MySQL attribuera une valeur d'identifiant unique à chaque instruction de sélection
select_type Colonne, type de requête, classée selon l'association, l'union, la sous-requête, etc. Les types de requêtes courants sont SIMPLE, PRIMAIRE.
table Colonne : indique à quelle table une ligne d'explication accède.
-
type Colonne : Une des colonnes les plus importantes. Représente le type d'association ou le type d'accès que MySQL détermine pour rechercher des lignes dans la table.
Les performances du meilleur au pire sont : système > eq_ref > fulltext > index_merge >
système
-
système : lorsque la table n'a qu'une seule ligne d'enregistrements (table système), la quantité de données est très faible, les E/S disque ne sont souvent pas nécessaires et la vitesse est très rapide
system： 当表仅有一行记录时(系统表)，数据量很少，往往不需要进行磁盘IO，速度非常快
-
const
const：表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量(const)值。这类扫描效率极高，返回数据量少，速度非常快。
-
eq_ref
eq_ref：查询时命中主键primary key 或者 unique key索引， type 就是 eq_ref。
-
ref_or_null
ref_or_null：这种连接类型类似于 ref，区别在于 MySQL会额外搜索包含NULL值的行。
-
index_merge
index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。
-
unique_subquery
unique_subquery：替换下面的 IN子查询，子查询返回不重复的集合。
-
index_subquery
index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。
-
range
range：使用索引选择行，仅检索给定范围内的行。简单点说就是针对一个有索引的字段，给定范围检索数据。在where语句中使用 bettween...and、、<code>>、、<code>in 等条件查询 type 都是 range。
-
index
index：Index 与ALL 其实都是读全表，区别在于index是遍历索引树读取，而ALLconst
const : indique que la requête atteint la clé primaire primary key ou l'index unique unique, ou que la partie connectée est une constante (const) valeur . Ce type d'analyse est extrêmement efficace, renvoie une petite quantité de données et est très rapide. -
eq_ref
eq_ref : appuyez sur l'index de la clé primaire clé primaire ou de la clé unique pendant la requête, tapez est eq_ref.
ref_or_null ref_or_null : ce type de jointure est similaire à ref, sauf que MySQL recherchera en plus les lignes contenant des valeurs NULL.
index_mergeindex_merge : la méthode d'optimisation de fusion d'index est utilisée et la requête utilise plus de deux index.
unique_subqueryunique_subquery : remplacez la sous-requête IN suivante, et la sous-requête renvoie un ensemble unique.
index_subqueryindex_subquery : différent de unique_subquery, il est utilisé pour les index non uniques et peut renvoyer des valeurs en double.
rangerange : sélectionnez les lignes à l'aide de l'index, en récupérant uniquement les lignes dans la plage donnée. Pour faire simple, il s’agit de récupérer des données dans une plage donnée pour un champ indexé. Utilisez entre...et, , <code>>, where =, in et d'autres requêtes conditionnelles type sont toutes range.
indexindex : Index et ALL lisent en fait la table entière, la différence est index Il est lu en parcourant l'arborescence d'index, tandis que ALL est lu à partir du disque dur.
TOUS- Inutile de dire, analyse complète du tableau.
-
-
possible_keys Colonne : affiche les index que la requête peut utiliser pour rechercher, ce qui est plus important lors de l'utilisation d'index pour optimiser SQL. Colonne
key : Cette colonne indique quel index mysql utilise réellement pour optimiser l'accès à la table et est couramment utilisée pour déterminer si l'index est invalide. 
key_len- Colonne : affiche l'utilisation de MySQL
-
- ref
- Colonne : la colonne ref affiche la valeur équivalente à la colonne d'index. Les plus courantes sont : const (constante), func, NULL, Nom du champ.
rows
Colonne : Il s'agit également d'un champ important, sur la base d'informations statistiques, l'optimiseur de requête MySQL estime le nombre de lignes de données que SQL doit analyser et lire pour trouver l'ensemble de résultats. l'efficacité de SQL , en principe, moins il y a de lignes, mieux c'est. 🎜🎜🎜🎜🎜Extra🎜Colonne : affiche des informations supplémentaires qui ne rentrent pas dans d'autres colonnes. Bien qu'elles soient appelées extra, il y a aussi des informations importantes : 🎜🎜🎜🎜🎜Utilisation d'un index : indique que MySQL utilisera un index de couverture pour. éviter les retours de table🎜 🎜Utiliser où : Indique que le filtrage sera effectué après la récupération du moteur de stockage 🎜🎜Utiliser temporaire : Indique qu'une table temporaire sera utilisée lors du tri des résultats de la requête. 🎜🎜🎜Index🎜🎜L'index peut être considéré comme la priorité absolue de l'entretien MySQL, et vous devez le gagner à fond. 🎜🎜27. Pouvez-vous expliquer brièvement la classification des indices ? 🎜🎜Classez les index de trois dimensions différentes : 🎜🎜🎜🎜🎜Par exemple, du point de vue de l'utilisation de base : 🎜🎜🎜Index de clé primaire : la clé primaire InnoDB est l'index par défaut et les colonnes de données ne peuvent pas être répétées ou NULL . Une table ne peut avoir qu’une seule clé primaire. 🎜🎜Index unique : la duplication des colonnes de données n'est pas autorisée, les valeurs NULL sont autorisées et une table permet à plusieurs colonnes de créer des index uniques. 🎜🎜Index normal : type d'index de base, aucune restriction d'unicité, valeurs NULL autorisées. 🎜🎜Index combiné : plusieurs valeurs de colonnes forment un index pour la recherche combinée, ce qui est plus efficace que la fusion d'index🎜🎜🎜28 Pourquoi l'utilisation d'un index accélère-t-elle les requêtes ? 🎜🎜La méthode de requête traditionnelle parcourt la table dans l'ordre. Quel que soit le nombre de données interrogées, MySQL doit parcourir les données de la table du début à la fin. 🎜Après avoir ajouté l'index, MySQL génère généralement un fichier d'index via l'algorithme BTREE. Lors de l'interrogation de la base de données, nous trouvons le fichier d'index à parcourir, recherchons dans les données d'index relativement petites, puis le mappons aux données correspondantes, ce qui peut. améliorer considérablement l’efficacité de la recherche.
C’est la même chose que lorsque l’on cherche le contenu correspondant à travers la table des matières du livre.
29. Quels sont les points à noter lors de la création d'un index ?
Bien que l'index soit un outil puissant pour l'optimisation des performances SQL, la maintenance de l'index nécessite également des coûts, donc lors de la création d'un index, vous devez également faire attention à :
-
L'index doit être construit sur des champs fréquemment utilisés dans les applications de requête
Il est utilisé pour où jugement et ordre Créez un index sur le champ (on) du tri et de la jointure.
-
Le nombre d'index doit être approprié
Les index doivent occuper de l'espace ; ils doivent également être maintenus lors des mises à jour.
-
Ne créez pas d'index pour les champs peu différenciés, comme le sexe.
Pour les champs avec une dispersion trop faible, le nombre de lignes scannées sera limité.
-
N'utilisez pas de valeurs fréquemment mises à jour comme clés primaires ou index
La maintenance des fichiers d'index coûte de l'argent, cela entraînera également des fractionnements de pages et une augmentation des temps d'E/S.
-
L'index combiné place les valeurs avec un hachage élevé (haute distinction) devant
Afin de satisfaire le principe de correspondance des préfixes les plus à gauche
-
créez un index combiné au lieu de modifier un index à une seule colonne.
L'index combiné remplace plusieurs index à colonne unique (pour les index à colonne unique, MySQL ne peut en principe utiliser qu'un seul index, il est donc plus approprié d'utiliser des index combinés lorsque plusieurs requêtes de condition sont souvent utilisées)
Pour les champs qui sont trop long, utilisez des index de préfixe. Lorsque la valeur du champ est relativement longue, l'indexation consommera beaucoup d'espace et la recherche sera très lente. Nous pouvons créer un index en interceptant la partie précédente du champ, appelée index préfixe.
-
Il n'est pas recommandé d'utiliser des valeurs non ordonnées (telles que les cartes d'identité, UUID) comme index
Lorsque la clé primaire est incertaine, cela entraînera une division fréquente des nœuds feuilles et une fragmentation du stockage sur disque
30. Quels sont les index ? Dans quel cas va-t-il échouer ?
- La condition de requête contient ou, ce qui peut provoquer un échec de l'index
- Si le type de champ est une chaîne, le où doit être mis entre guillemets, sinon l'index sera invalide en raison d'une conversion de type implicite
- comme les caractères génériques peuvent provoquer un index échec.
- Index conjoint, la colonne de condition lors de l'interrogation n'est pas la première colonne de l'index conjoint et l'index devient invalide.
- L'utilisation de la fonction intégrée de MySQL sur la colonne d'index rendra l'index invalide.
- Lorsque vous effectuez des opérations sur des colonnes indexées (telles que +, -, *, /), l'index devient invalide.
- Lors de l'utilisation de (!= ou , not in) sur un champ d'index, cela peut provoquer un échec de l'index.
- L'utilisation de est nul ou non nul sur les champs d'index peut entraîner un échec de l'index.
- Le format d'encodage des champs associés à la requête de jointure gauche ou à la requête de jointure droite est différent, ce qui peut entraîner un échec de l'index.
- L'optimiseur MySQL estime que l'utilisation d'une analyse complète de la table est plus rapide que l'utilisation d'un index, donc l'index n'est pas utilisé.
31. Pour quels scénarios les index ne conviennent-ils pas ?
- Les tableaux contenant une quantité relativement faible de données ne conviennent pas à l'indexation
- Les champs mis à jour plus fréquemment ne conviennent pas à l'indexation
- Les champs à faible discrétion ne conviennent pas à l'indexation (comme le sexe)
32. L’index est-il construit plus longtemps ?
Bien sûr que non.
- L'index occupera de l'espace disque
-
Bien que l'index améliore l'efficacité des requêtes, il réduira l'efficacité de la mise à jour de la table. Par exemple, chaque fois qu'une table est ajoutée, supprimée ou modifiée, MySQL doit non seulement sauvegarder les données, mais également sauvegarder ou mettre à jour le fichier d'index correspondant.
33.Savez-vous quelle structure de données l'index MySQL utilise ?
Le moteur de stockage par défaut de MySQL est InnoDB, qui utilise un index de structure arborescente B+.
- Arbre B+ : seuls les nœuds feuilles stockent les données et les nœuds non-feuilles ne stockent que les valeurs clés. Les nœuds feuilles sont connectés à l'aide de pointeurs bidirectionnels, et les nœuds feuilles les plus bas forment une liste chaînée ordonnée dans les deux sens.
Sur cette photo, il y a deux points importants :
- Le bloc le plus externe, nous l'appelons un bloc de disque. Vous pouvez voir que chaque bloc de disque contient plusieurs éléments de données (indiqués en rose) et des pointeurs (affichés en jaune/gris). Par exemple, le disque du nœud racine contient les éléments de données 17). et 35 contiennent les pointeurs P1, P2 et P3 représentent les blocs de disque inférieurs à 17, P2 représente les blocs de disque entre 17 et 35 et P3 représente les blocs de disque supérieurs à 35. Les données réelles existent dans les nœuds feuilles, à savoir 3, 4, 5..., 65. Les nœuds non-feuilles ne stockent pas de données réelles, mais uniquement les éléments de données qui guident la direction de recherche. Par exemple, 17 et 35 n'existent pas réellement dans la table de données.
- Les nœuds feuilles sont connectés à l'aide de pointeurs bidirectionnels. Le nœud feuille le plus bas forme une liste chaînée ordonnée dans les deux sens, qui peut être interrogée par plage.
34. Combien de données un arbre B+ peut-il stocker ?
Supposons que le champ d'index soit de type bigint et que la longueur soit de 8 octets. La taille du pointeur est définie sur 6 octets dans le code source InnoDB, soit un total de 14 octets. Les nœuds non-feuilles (une page) peuvent stocker 16384/14=1170 de ces unités (valeurs clés + pointeurs), ce qui signifie qu'il y a 1170 pointeurs.
Lorsque la profondeur de l'arbre est de 2, il y a 1170^2 nœuds feuilles et les données qui peuvent être stockées sont 1170117016=21902400.
Lors d'une recherche de données, une recherche sur une page représente une IO. En d'autres termes, pour une table d'environ 20 millions, l'interrogation des données nécessite jusqu'à trois accès au disque.
Ainsi, la profondeur de l'arborescence B+ dans InnoDB est généralement de 1 à 3 couches, ce qui peut satisfaire des dizaines de millions de stockage de données.
35. Pourquoi utiliser un arbre B+ au lieu d'un arbre binaire ordinaire ?
Vous pouvez examiner ce problème sous plusieurs dimensions, si la requête est suffisamment rapide, si l'efficacité est stable, la quantité de données stockées et le nombre de recherches sur le disque.
Pourquoi ne pas utiliser des arbres binaires ordinaires ?
Les arbres binaires ordinaires sont dégénérés S'ils dégénèrent en une liste chaînée, cela équivaut à une analyse de table complète. Par rapport aux arbres de recherche binaires, les arbres binaires équilibrés ont une efficacité de recherche plus stable et une vitesse de recherche globale plus rapide.
Pourquoi ne pas équilibrer l'arbre binaire ?
Lors de la lecture des données, elles sont lues du disque vers la mémoire. Si une structure de données telle qu'un arbre est utilisée comme index, chaque fois que vous recherchez des données, vous devez lire un nœud sur le disque, qui est un bloc de disque, mais un arbre binaire équilibré ne stocke qu'une seule valeur de clé et des données par nœud. S'il s'agit d'un arbre B+, davantage de données de nœuds peuvent être stockées et la hauteur de l'arborescence sera également réduite, de sorte que le nombre de lectures du disque sera réduit et l'efficacité des requêtes sera plus rapide.
36. Pourquoi utiliser l’arbre B+ au lieu de l’arbre B ?
B+ présente ces avantages par rapport au B-tree :
-
C'est une variante du B-Tree. Il peut résoudre tous les problèmes que B-Tree peut résoudre.
Deux problèmes majeurs résolus par B Tree : chaque nœud stocke plus de mots-clés ; plus de chemins
-
Des capacités d'analyse de base de données et de table plus puissantes
Si nous voulons effectuer une analyse complète de la table, il nous suffit de parcourir les nœuds Leaf suffisent, il n’est pas nécessaire de parcourir l’intégralité du B+Tree pour obtenir toutes les données.
-
B+Tree a des capacités de lecture et d'écriture sur disque plus puissantes que B Tree, et a moins de temps d'E/S
Le nœud racine et les nœuds de branche n'enregistrent pas les zones de données, donc un nœud peut enregistrer plus de mots-clés et charger le disque en une seule fois. time Il y a plus de mots-clés et moins de temps d'E/S.
-
Capacité de tri plus forte
Parce qu'il y a un pointeur vers la zone de données suivante sur le nœud feuille, les données forment une liste chaînée.
-
L'efficacité est plus stable
B+Tree obtient toujours des données au niveau des nœuds feuilles, donc le nombre d'IO est stable.
37. Quelle est la différence entre l'index Hash et l'index arborescent B+ ?
- L'arbre B+ peut effectuer une requête de plage, l'index de hachage ne le peut pas.
- L'arbre B+ prend en charge le principe le plus à gauche de l'index conjoint, mais l'index de hachage ne le prend pas en charge.
- L'arbre B+ prend en charge l'ordre par tri, mais l'index de hachage ne le prend pas en charge.
- L'index Hash est plus efficace que l'arbre B+ sur des requêtes équivalentes.
- Lorsque l'arbre B+ utilise like pour une requête floue, les mots après like (comme commencer par %) peuvent jouer un rôle d'optimisation, et l'index Hash ne peut pas du tout effectuer de requête floue.
38. Quelle est la différence entre un index clusterisé et un index non clusterisé ?
Comprenez d'abord que l'index clusterisé n'est pas un nouvel index, mais une méthode de stockage de données. Le clustering signifie que les lignes de données et les valeurs clés adjacentes sont stockées ensemble de manière compacte. Les deux moteurs de stockage que nous connaissons : MyISAM utilise des index non clusterisés et InnoDB utilise des index clusterisés.
Cela peut être dit comme ceci :
- La structure des données de l'index est un arbre. L'index et les données de l'index clusterisé sont stockés dans un arbre. Les nœuds feuilles de l'arbre sont les données. de l’index non clusterisé ne se trouvent pas dans la même arborescence.
- 一个表中只能拥有一个聚簇索引，而非聚簇索引一个表可以存在多个。
- 聚簇索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
- 聚簇索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；
39.回表了解吗？
在InnoDB存储引擎里，利用辅助索引查询，先通过辅助索引找到主键索引的键值，再通过主键值查出主键索引里面没有符合要求的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。
例如:select * from user where name = ‘张三’;
40.覆盖索引了解吗？
在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用辅助索引中就能够取得，不用去查主键索引，这时候使用的索引就叫做覆盖索引，避免了回表。
比如，select name from user where name = ‘张三’;
41.什么是最左前缀原则/最左匹配原则？
注意：最左前缀原则、最左匹配原则、最左前缀匹配原则这三个都是一个概念。
最左匹配原则：在InnoDB的联合索引中，查询的时候只有匹配了前一个/左边的值之后，才能匹配下一个。
根据最左匹配原则，我们创建了一个组合索引，如 (a1,a2,a3)，相当于创建了（a1）、(a1,a2)和 (a1,a2,a3) 三个索引。
为什么不从最左开始查，就无法匹配呢？
比如有一个user表，我们给 name 和 age 建立了一个组合索引。
ALTER TABLE user add INDEX comidx_name_phone (name,age);
组合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的 (name 在左边，age 在右边)。
从这张图可以看出来，name 是有序的，age 是无序的。当 name 相等的时候， age 才是有序的。
这个时候我们使用where name= ‘张三‘ and age = ‘20 ‘去查询数据的时候， B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name 相同的时候再比较age。但是如果查询条件没有 name，就不知道下一步应该查哪个 节点，因为建立搜索树的时候 name 是第一个比较因子，所以就没用上索引。
42.什么是索引下推优化？
索引条件下推优化（Index Condition Pushdown (ICP) ）是MySQL5.6添加的，用于优化数据查询。
- 不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQL Server，MySQL Server进行过滤条件的判断。
- 当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL Server将这一部分判断条件下推给存储引擎，然后由存储引擎通过判断索引是否符合MySQL Server传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。
例如一张表，建了一个联合索引（name, age），查询语句：select * from t_user where name like '张%' and age=10;，由于name使用了范围查询，根据最左匹配原则：
不使用ICP，引擎层查找到name like '张%'的数据，再由Server层去过滤age=10这个条件，这样一来，就回表了两次，浪费了联合索引的另外一个字段age。
但是，使用了索引下推优化，把where的条件放到了引擎层执行，直接根据name like '张%' and age=10的条件进行过滤，减少了回表的次数。
索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。
锁
43.MySQL中有哪几种锁，列举一下？
如果按锁粒度划分，有以下3种：
- Verrouillage de table : faible surcharge, verrouillage rapide ; force de verrouillage élevée, forte probabilité de conflit de verrouillage, concurrence la plus faible ;
- Verrouillage de ligne : surcharge élevée, blocage lent ; faible granularité de verrouillage, faible probabilité de conflit de verrouillage et concurrence élevée.
- Verrouillage de page : la surcharge et la vitesse de verrouillage se situent entre le verrouillage de la table et le verrouillage des lignes ; un blocage se produira ; la granularité du verrouillage se situe entre le verrouillage de la table et le verrouillage des lignes, et la concurrence est moyenne
Si, selon la compatibilité, il existe deux types ,
- le verrouillage partagé (S Lock), également appelé verrouillage en lecture (read lock), ne se bloque pas.
- Verrouillage exclusif (X Lock), également appelé verrouillage en écriture (verrouillage d'écriture), le verrouillage exclusif bloque. Dans un certain laps de temps, une seule requête peut effectuer l'écriture et empêcher d'autres verrous de lire les données en cours d'écriture.
44. Parlez de l'implémentation du verrouillage de ligne dans InnoDB ?
Nous utilisons une telle table utilisateur pour représenter les verrous au niveau de la ligne, dans laquelle 4 lignes de données sont insérées et les valeurs de clé primaire sont 1, 6, 8 et 12. Simplifiez maintenant Sa structure d'index clusterisé ne conserve que les enregistrements de données.
La principale implémentation du verrouillage de ligne d'InnoDB est la suivante :
- Verrouillage d'enregistrement Verrouillage d'enregistrement
Le verrouillage d'enregistrement consiste à verrouiller directement un enregistrement de ligne. Lorsque nous utilisons des index uniques (y compris des index uniques et des index clusterisés) pour effectuer des requêtes équivalentes et faire correspondre avec précision un enregistrement, l'enregistrement sera directement verrouillé. Par exemple, select * from t which id =6 for update; verrouillera l'enregistrement de id=6. select * from t where id =6 for update;就会将id=6的记录锁定。
间隙锁(Gap Locks) 的间隙指的是两个记录之间逻辑上尚未填入数据的部分,是一个左开右开空间。
间隙锁就是锁定某些间隙区间的。当我们使用用等值查询或者范围查询，并且没有命中任何一个record，此时就会将对应的间隙区间锁定。例如select * from t where id =3 for update;或者select * from t where id > 1 and id 就会将(1,6)区间锁定。
临键指的是间隙加上它右边的记录组成的左开右闭区间。比如上述的(1,6]、(6,8]等。
临键锁就是记录锁(Record Locks)和间隙锁(Gap Locks)的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。当我们使用范围查询，并且命中了部分record记录，此时锁住的就是临键区间。注意，临键锁锁住的区间会包含最后一个record的右边的临键区间。例如select * from t where id > 5 and id 会锁住(4,7]、(7,+∞)。mysql默认行锁类型就是<code>临键锁(Next-Key Locks)。当使用唯一性索引，等值查询匹配到一条记录的时候，临键锁(Next-Key Locks)会退化成记录锁；没有匹配到任何记录的时候，退化成间隙锁。
间隙锁(Gap Locks)和临键锁(Next-Key Locks)都是用来解决幻读问题的，在已提交读（READ COMMITTED）隔离级别下，间隙锁(Gap Locks)和临键锁(Next-Key Locks)
Gap Lock Gap Lock
espace ouvert à gauche et ouvert à droite.
Écart Le verrou sert à verrouiller certains intervalles d'espacement. Lorsque nous utilisons une requête d'égalité ou une requête par plage et que nous n'atteignons aucun enregistrement, l'intervalle d'intervalle correspondant sera verrouillé. Par exemple, select * from t Where id =3 for update; ou select * from t Where id > 1 et id changera (1 , 6) Verrouillage par intervalles. <img src="https://img.php.cn/upload/image/262/626/329/16559766103188866%20questions%20dentretien%20pour%20vous%20aider%20%C3%A0%20trier%20les%20points%20de%20connaissances%20MySQL%C2%A0!" title="16559766103188866 questions dentretien pour vous aider à trier les points de connaissances MySQL !" alt="366 questions dentretien pour vous aider à trier les points de connaissances MySQL !">
Verrouillage de la touche suivante
La touche suivante fait référence à 🎜l'intervalle ouvert à gauche et fermé à droite🎜 composé de l'espace plus l'enregistrement à sa droite. Par exemple, ce qui précède (1,6], (6,8], etc. 🎜🎜🎜🎜Les verrous Pro-key sont une combinaison de verrous d'enregistrement (Record Locks) et de verrous d'espacement (Gap Locks), c'est-à-dire en plus de verrouiller l'enregistrement lui-même. , il verrouille également l'espace d'indexation. Lorsque nous utilisons une requête par plage et que nous atteignons une partie de l'enregistrement record, ce qui est verrouillé est l'intervalle de clé temporaire. Notez que l'intervalle verrouillé par le verrouillage de clé temporaire inclura. le côté droit du dernier enregistrement. Par exemple, sélectionnez * à partir de t où id verrouillera (4,7], (7, +∞). Verrouillage de ligne par défaut MySQL Le type est <code>Next-Key Locks Lorsqu'un index unique est utilisé et qu'une requête de valeur égale correspond à un enregistrement, les Next-Key Locks dégénèrent en verrous d'enregistrement. l'enregistrement correspond, il dégénère en verrous d'espacement 🎜🎜Gap Locks et Next-Key Locks sont tous deux utilisés pour résoudre le problème de lecture fantôme, sous le READ COMMITTED</code.>
</blockquote>, Gap Locks et Next- Key Locks) seront invalides ! 🎜🎜🎜Les trois algorithmes ci-dessus sont les trois algorithmes d'implémentation des verrous de ligne. De plus, il existe également des verrous d'intention d'insertion sur les lignes 🎜🎜🎜🎜Insert Intention Lock🎜🎜🎜 🎜Lorsqu'une transaction insère un enregistrement, elle doit déterminer si la position d'insertion est verrouillée par une autre transaction. Si tel est le cas, l'insertion. L'opération doit attendre que la transaction contenant le verrou d'espace soit validée. Cependant, la transaction doit également attendre en attendant. Une structure de verrouillage doit être générée en mémoire, indiquant qu'une transaction souhaite insérer un nouvel enregistrement dans un certain espace, mais. est maintenant en attente. Ce type de verrou est nommé Insérer des verrous d'intention, c'est-à-dire que si nous avons un verrou d'intention, la transaction T1 ajoute un verrou d'intention à l'intervalle (1,6). Il y a maintenant une transaction T2 qui veut insérer. une donnée avec un identifiant de 4. Elle acquerra un verrou d'intention d'insertion pour l'intervalle (1,6), et il y a une autre transaction T3 qui souhaite insérer une donnée avec l'ID 3. Elle acquerra également une intention d'insertion verrouiller dans la plage (1,6). Cependant, les deux verrous d’intention d’insertion ne s’excluent pas mutuellement. 🎜🎜🎜🎜🎜45. Savez-vous ce qu'est le verrouillage d'intention ? 🎜🎜Le verrouillage d'intention est un verrouillage au niveau de la table, à ne pas confondre avec le verrouillage d'intention d'insertion. 🎜🎜Les verrous d'intention semblent prendre en charge les verrous multi-granularité d'InnoDB. Cela résout le problème de la coexistence des verrous de table et des verrous de ligne. 🎜Lorsque nous devons ajouter un verrou de table à une table, nous devons déterminer si des lignes de données dans la table sont verrouillées pour déterminer si l'ajout peut réussir.
S'il n'y a pas de verrouillage d'intention, alors nous devons parcourir toutes les lignes de données de la table pour déterminer s'il y a un verrouillage de ligne.
Avec le verrouillage d'intention, un verrouillage au niveau de la table, nous pouvons juger directement une fois pour savoir ; si des lignes de données dans la table sont verrouillées.
Avec le verrouillage d'intention, avant que la transaction A à exécuter ne demande un verrouillage de ligne (verrouillage en écriture), la base de données demandera automatiquement un verrouillage exclusif d'intention sur la table pour la transaction A. Lorsque la transaction B demande un verrou mutex sur la table, elle échouera car il existe un verrou exclusif intentionnel sur la table et la transaction B sera bloquée lorsqu'elle demandera le verrou mutex sur la table.
46. Comprenez-vous le verrouillage optimiste et le verrouillage pessimiste de MySQL ?
-
Contrôle de concurrence pessimiste :
Le verrouillage pessimiste estime que les données protégées par celui-ci sont extrêmement dangereuses et peuvent être modifiées à tout moment. Une fois qu'une transaction a obtenu le verrouillage pessimiste, toute autre transaction ne peut pas modifier les données et ne peut que modifier les données. attendez que le verrou soit libéré avant de l'exécuter.
Les verrous de ligne, les verrous de table, les verrous de lecture et les verrous d'écriture dans la base de données sont tous des verrous pessimistes.
- Contrôle de concurrence optimiste
Le verrouillage optimiste estime que les données ne changeront pas trop fréquemment.
Le verrouillage optimiste est généralement implémenté en ajoutant une version (version) ou un horodatage (horodatage) au tableau, parmi lesquels la version est la plus couramment utilisée.
Lorsqu'une transaction récupère des données de la base de données, elle supprime également la version des données (v1). Lorsque la transaction termine les modifications apportées aux données et souhaite les mettre à jour dans la table, elle supprime la version v1. précédemment avec les données. Par rapport à la dernière version v2, si v1=v2, cela signifie que pendant la période de changement de données, aucune autre transaction ne modifie les données. À ce moment, les transactions sont autorisées à modifier les données de la table, ainsi que les données. La version sera augmentée de 1 lors de la modification. Cela indique que les données ont été modifiées.
Si v1 n'est pas égal à v2, cela signifie que les données ont été modifiées par d'autres transactions pendant la période de modification des données. À ce stade, les données ne sont pas autorisées à être mises à jour dans le tableau. et laissez-les ré-opérer. Contrairement au verrouillage pessimiste, le verrouillage optimiste est généralement mis en œuvre par les développeurs.
47.Avez-vous déjà rencontré un problème de blocage dans MySQL ? Comment l'avez-vous résolu ?
Les étapes générales de dépannage des blocages sont les suivantes :
(1) Vérifiez le journal des blocages show engine innodb status
(2) Découvrez le blocage sql
(3) Analysez la situation de verrouillage SQL
(4) ) Simulez un cas de blocage
(5) Analysez le journal des blocages
(6) Analysez le résultat du blocage
Bien sûr, ce n'est qu'une simple description du processus. En fait, les blocages en production sont toutes sortes d'étranges, et c'est le cas. est difficile de les dépanner et de les résoudre.
Transactions
48. Quelles sont les quatre caractéristiques majeures des transactions MySQL ?
- Atomicité : La transaction est exécutée dans son ensemble, et soit toutes les opérations sur la base de données qu'elle contient sont exécutées, soit aucune n'est exécutée.
- Cohérence : signifie que les données ne seront pas détruites avant le début et après la fin de la transaction. Si le compte A transfère 10 yuans vers le compte B, le montant total de A et B restera inchangé quel que soit le succès ou l'échec.
- Isolement : lorsque plusieurs transactions accèdent simultanément, les transactions sont isolées les unes des autres, c'est-à-dire qu'une transaction n'affecte pas les effets en cours des autres transactions. Bref, cela signifie qu’il n’y a pas de conflit entre les affaires.
- Persistance : indique qu'une fois la transaction terminée, les modifications opérationnelles apportées par la transaction à la base de données seront définitivement enregistrées dans la base de données.
49. Alors sur quelle garantie s'appuie ACID ?
- L'isolation des transactions est obtenue grâce au mécanisme de verrouillage de la base de données.
- La cohérencede la transaction est garantie par le journal d'annulation : le journal d'annulation est un journal logique qui enregistre les opérations d'insertion, de mise à jour et de suppression de la transaction. Lors du rollback, les opérations de suppression, de mise à jour et d'insertion opposées sont. effectué pour restaurer les données.
- L'atomicité et la durabilité de la transaction sont garanties par le redo log : le redo log est appelé redo log, qui est un journal physique lorsqu'une transaction est soumise, tous les logs de la transaction doivent d'abord être écrits dans le redo log. pour la persistance. La transaction n’est terminée qu’après l’opération de validation.
50. Quels sont les niveaux d'isolement des transactions ? Quel est le niveau d'isolement par défaut de MySQL ?
- Lecture non validée
- Lecture validée
- Lecture répétable
- Sérialisable
Le niveau d'isolement des transactions par défaut de MySQL est Lecture répétable.
51.Que sont les lectures fantômes, les lectures sales et les lectures non répétables ?
- Les transactions A et B sont exécutées alternativement et la transaction A lit les données non validées de la transaction B. C'est une lecture sale.
- Dans le cadre d'une transaction, deux requêtes identiques lisent le même enregistrement mais renvoient des données différentes. Il s'agit d'une lecture non répétable.
- La transaction A interroge l'ensemble de résultats d'une plage, et une autre transaction simultanée B insère/supprime des données dans cette plage et les valide silencieusement. Ensuite, la transaction A interroge à nouveau la même plage, et l'ensemble de résultats obtenu par les deux lectures est différent. C'est pareil, c'est une lecture fantôme.
Différents niveaux d'isolement, problèmes pouvant survenir lors de transactions simultanées :
| Niveau d'isolement |
Lecture sale |
Lecture non répétable |
Lecture fantôme |
| Lecture non validée Lecture validée Engagé |
est |
|
Serialzable Serialisable |
Non
| Non |
Non |
|
52. Comment les différents niveaux d’isolement des transactions sont-ils mis en œuvre ?
Lecture non engagée
Lire non engagée, il va sans dire que le principe de lecture sans verrouillage est adopté.
- La lecture transactionnelle ne verrouille pas et ne bloque pas la lecture et l'écriture d'autres transactions.
- L'écriture transactionnelle bloque l'écriture d'autres transactions, mais ne bloque pas la lecture d'autres transactions.
La lecture est validée et répétable. lecture
Lecture Les niveaux de lecture validés et répétables profitent de ReadView et de MVCC, c'est-à-dire que chaque transaction ne peut lire que la version qu'elle peut voir (ReadView). ReadView和MVCC，也就是每个事务只能读取它能看到的版本（ReadView）。
- READ COMMITTED：每次读取数据前都生成一个ReadView
- REPEATABLE READ ： 在第一次读取数据时生成一个ReadView
串行化
串行化的实现采用的是读写都加锁的原理。
串行化的情况下，对于同一行事务，写会加写锁，读会加读锁。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
53.MVCC了解吗？怎么实现的？
MVCC(Multi Version Concurrency Control)，中文名是多版本并发控制，简单来说就是通过维护数据历史版本，从而解决并发访问情况下的读一致性问题。关于它的实现，要抓住几个关键点，隐式字段、undo日志、版本链、快照读&当前读、Read View。
版本链
对于InnoDB存储引擎，每一行记录都有两个隐藏列DB_TRX_ID、DB_ROLL_PTR
-
DB_TRX_ID，事务ID，每次修改时，都会把该事务ID复制给DB_TRX_ID；
-
DB_ROLL_PTR，回滚指针，指向回滚段的undo日志。
假如有一张user表，表中只有一行记录，当时插入的事务id为80。此时，该条记录的示例图如下：
接下来有两个DB_TRX_ID分别为100、200的事务对这条记录进行update操作，整个过程如下：
由于每次变动都会先把undo日志记录下来，并用DB_ROLL_PTR指向undo日志地址。因此可以认为，对该条记录的修改日志串联起来就形成了一个版本链，版本链的头节点就是当前记录最新的值。如下：
ReadView
对于Read Committed和Repeatable Read隔离级别来说，都需要读取已经提交的事务所修改的记录，也就是说如果版本链中某个版本的修改没有提交，那么该版本的记录时不能被读取的。所以需要确定在Read Committed和Repeatable Read隔离级别下，版本链中哪个版本是能被当前事务读取的。于是就引入了ReadView
LECTURE COMMITTED : Générez un ReadView à chaque fois avant de lire les donnéesLECTURE RÉPÉTABLE : Générez un ReadView lors de la première lecture des données
 Sérialisation
- La sérialisation est mise en œuvre en utilisant le principe de verrouillage à la fois en lecture et en écriture.
- Dans le cas de la sérialisation, pour une même ligne de transactions,
write ajoutera un write lock, et read ajoutera un lire le verrou. Lorsqu'un conflit de verrouillage en lecture-écriture se produit, la transaction accédée ultérieurement doit attendre la fin de la transaction précédente avant de pouvoir continuer à s'exécuter.
53.Comprenez-vous MVCC ? Comment y parvient-on ?
- MVCC (Multi Version Concurrency Control), le nom chinois est contrôle de concurrence multi-versions. En termes simples, il résout le problème de cohérence de lecture sous accès simultané en conservant les versions historiques des données. Concernant sa mise en œuvre, nous devons saisir plusieurs points clés,
- Champs implicites, journaux d'annulation, chaînes de versions, lecture d'instantanés et lecture actuelle, et Read View
.
Chaîne de versions🎜🎜🎜Pour le moteur de stockage InnoDB, chaque ligne d'enregistrements a deux colonnes cachées🎜DB_TRX_ID, DB_ROLL_PTR🎜🎜🎜🎜DB_TRX_ID, ID de transaction, à chaque modification, le L'ID de transaction est copié dans DB_TRX_ID ; 🎜🎜DB_ROLL_PTR, le pointeur d'annulation pointe vers le journal d'annulation du segment d'annulation. 🎜🎜🎜🎜🎜 Supposons qu'il existe une table user avec une seule ligne d'enregistrements et que l'ID de transaction inséré à ce moment-là est 80. À l'heure actuelle, l'exemple d'image de cet enregistrement est le suivant : 🎜🎜🎜🎜Ensuite, il y a deux transactions avec DB_TRX_ID étant 100 et 200 à effectuer mise à jour, l'ensemble du processus est le suivant : 🎜🎜🎜🎜Puisque chaque modification sera d'abord enregistrée dans le journal Annuler, et utilisez DB_ROLL_PTR pour pointer vers le Adresse du journal annuler. Par conséquent, on peut considérer que 🎜les journaux de modifications de cet enregistrement sont concaténés pour former une chaîne de versions, et que le nœud principal de la chaîne de versions est la dernière valeur de l'enregistrement actuel🎜. Comme suit : 🎜🎜 🎜 🎜🎜ReadView🎜🎜🎜Pour les niveaux d'isolement Lecture validée et Lecture répétable, il est nécessaire de lire les enregistrements modifiés par la transaction soumise, c'est-à-dire dire Si les modifications d'une certaine version dans la chaîne de versions ne sont pas validées, les enregistrements de cette version ne peuvent pas être lus. Par conséquent, il est nécessaire de déterminer quelle version de la chaîne de versions peut être lue par la transaction en cours sous les niveaux d'isolement Lecture validée et Lecture répétable. Le concept de ReadView a donc été introduit pour résoudre ce problème. 🎜🎜🎜Read View est la vue de lecture générée lorsqu'une transaction est exécutée 🎜snapshot read🎜, qui équivaut à un instantané enregistré dans un certain calendrier, grâce à cet instantané, nous pouvons obtenir : 🎜🎜🎜🎜🎜🎜m_ids : indique quand. le ReadView est généré. Liste des ID de transaction des transactions de lecture et d'écriture actives dans le système actuel. 🎜🎜min_trx_id : indique le plus petit identifiant de transaction parmi les transactions de lecture et d'écriture actives dans le système actuel lorsque le ReadView est généré, c'est-à-dire la plus petite valeur en m_ids. 🎜🎜max_trx_id : indique la valeur d'identifiant qui doit être attribuée à la prochaine transaction dans le système lors de la génération de ReadView. 🎜🎜creator_trx_id : Indique l'identifiant de la transaction qui a généré le ReadView. Avec ce ReadView, lors de l'accès à un enregistrement, il vous suffit de suivre les étapes ci-dessous pour déterminer si une certaine version de l'enregistrement est visible : 🎜
- Si la valeur de l'attribut DB_TRX_ID de la version consultée est la même que la valeur Creator_trx_id dans ReadView, cela signifie que la transaction en cours accède à ses propres enregistrements modifiés, cette version est donc accessible par la transaction en cours.
- Si la valeur de l'attribut DB_TRX_ID de la version accédée est inférieure à la valeur min_trx_id dans ReadView, cela indique que la transaction qui a généré cette version a été validée avant que la transaction en cours ne génère ReadView, cette version est donc accessible par la transaction en cours.
- Si la valeur de l'attribut DB_TRX_ID de la version accédée est supérieure à la valeur max_trx_id dans ReadView, cela signifie que la transaction qui a généré cette version a été ouverte après la transaction en cours générée par ReadView, cette version n'est donc pas accessible par la transaction en cours.
- Si la valeur de l'attribut DB_TRX_ID de la version consultée est comprise entre min_trx_id et max_trx_id de ReadView, alors vous devez déterminer si la valeur de l'attribut trx_id est dans la liste m_ids. Si c'est le cas, cela signifie que la transaction qui a généré cette version lorsque. le ReadView créé est toujours actif. Cette version n'est pas accessible ; sinon, cela signifie que la transaction qui a généré cette version lors de la création du ReadView a été validée et que cette version est accessible.
Si une certaine version des données n'est pas visible pour la transaction en cours, suivez la chaîne de versions pour trouver la prochaine version des données, continuez à suivre les étapes ci-dessus pour déterminer la visibilité, et ainsi de suite, jusqu'à la dernière version dans la chaîne de versions. Si la dernière version n'est pas visible, cela signifie que l'enregistrement est complètement invisible pour la transaction et que le résultat de la requête n'inclut pas l'enregistrement.
Dans MySQL, une très grande différence entre les niveaux d'isolement READ COMMITTED et REPEATABLE READ est qu'ils génèrent ReadView à des moments différents.
READ COMMITTED génère un ReadView avant chaque lecture de données, afin que vous puissiez vous assurer que vous pouvez lire les données soumises par d'autres transactions à chaque fois ; REPEATABLE READ génère un ReadView lors de la première lecture des données Cela garantit que les résultats des lectures ultérieures sont tout à fait cohérents.
Haute disponibilité/performance
54. Comprenez-vous la séparation de la lecture et de l'écriture de la base de données ?
Le principe de base de la séparation en lecture et en écriture est de disperser les opérations de lecture et d'écriture de la base de données sur différents nœuds. Voici le schéma d'architecture de base :
L'implémentation de base de la séparation en lecture et en écriture est :
- La base de données. Le serveur construit un cluster maître-esclave. Un maître et un esclave, ou un maître et plusieurs esclaves peuvent être utilisés.
- L'hôte de la base de données est responsable des opérations de lecture et d'écriture, et l'esclave est uniquement responsable des opérations de lecture.
- L'hôte de la base de données synchronise les données avec la machine esclave via la réplication, et chaque serveur de base de données stocke toutes les données commerciales.
- Le serveur d'entreprise envoie des opérations d'écriture à l'hôte de la base de données et des opérations de lecture à l'esclave de la base de données.
55. Comment réaliser l'allocation de la séparation en lecture et en écriture ?
Pour séparer les opérations de lecture et d'écriture, puis accéder à différents serveurs de bases de données, il existe généralement deux manières : l'encapsulation du code du programme et l'encapsulation du middleware.
1. Encapsulation du code du programme
L'encapsulation du code du programme fait référence à l'abstraction d'une couche d'accès aux données dans le code (c'est pourquoi certains articles appellent également cette méthode "encapsulation de la couche intermédiaire") pour obtenir la séparation des opérations de lecture et d'écriture et de la connexion au serveur de base de données. gestion. Par exemple, une simple encapsulation basée sur Hibernate peut réaliser une séparation lecture-écriture :
Parmi les solutions d'implémentation open source actuelles, le TDDL (Taobao Distributed Data Layer, surnom : La tête est trop grosse) de Taobao est relativement célèbre.
2. Encapsulation middleware
L'encapsulation middleware fait référence à un système indépendant qui réalise la séparation des opérations de lecture et d'écriture et la gestion des connexions au serveur de base de données. Le middleware fournit un protocole compatible SQL au serveur d'entreprise, et le serveur d'entreprise n'a pas besoin de séparer lui-même la lecture et l'écriture.
Pour le serveur d'entreprise, il n'y a pas de différence entre accéder au middleware et accéder à la base de données. En fait, du point de vue du serveur d'entreprise, le middleware est un serveur de base de données.
La structure de base est :
56 Comprenez-vous le principe de la réplication maître-esclave ?
- Le maître écrit les données et met à jour le binlog
- le maître crée un thread de vidage pour pousser le binlog vers l'esclave
- Lorsque l'esclave se connecte au maître, il créera un thread IO pour recevoir le binlog et l'enregistrera dans le journal de relais du journal de relais
- esclave Ensuite ouvrez un thread SQL pour lire l'événement du journal du relais et exécutez-le sur l'esclave pour terminer la synchronisation. L'esclave enregistre son propre binglog 57. Comment gérer le délai de synchronisation maître-esclave ?
- La raison du retard de synchronisation maître-esclave
Un serveur ouvre N liens pour que les clients se connectent, il y aura donc de grandes opérations de mise à jour simultanées. Cependant, il n'y a qu'un seul thread pour lire le binlog du serveur lorsqu'un certain SQL est exécuté sur le serveur esclave, cela prend un peu. Ou parce qu'un certain SQL doit verrouiller la table, cela entraînera un retard important de SQL sur le serveur maître et ne sera pas synchronisé avec le serveur esclave. Cela conduit à une incohérence maître-esclave, c'est-à-dire un retard maître-esclave.
Solutions au délai de synchronisation maître-esclave
Il existe plusieurs méthodes courantes pour résoudre le délai de réplication maître-esclave :
Par exemple, l'enregistrement du compte est terminé. Enfin, l'opération de lecture pour lire le compte lors de la connexion est également envoyée au serveur de base de données principal. Cette méthode est fortement liée à l'entreprise et a une plus grande intrusion et un plus grand impact sur l'entreprise. Si un nouveau programmeur ne sait pas écrire du code de cette manière, cela provoquera un bug.
C'est ce qu'on appelle communément "lecture secondaire". La lecture secondaire n'est pas liée à l'entreprise et doit uniquement encapsuler l'API pour accéder à la base de données sous-jacente. Ça y est, le coût de mise en œuvre est faible. L'inconvénient est que s'il y a beaucoup de lectures secondaires, la pression de l'opération de lecture sur l'hôte sera considérablement augmentée. Par exemple, si un pirate informatique pirate violemment un compte, cela entraînera un grand nombre d'opérations de lecture secondaires. L'hôte pourrait ne pas être en mesure de résister à la pression des opérations de lecture et s'effondrer.
Par exemple, pour un système de gestion des utilisateurs, les opérations d'enregistrement et de connexion de lecture et d'écriture sont toutes accéder à l'hôte, l'introduction de l'utilisateur, les entreprises telles que l'amour et le niveau peuvent utiliser la séparation lecture-écriture, car même si l'utilisateur modifie son auto-présentation, il verra que l'auto-présentation est toujours la même lors de l'interrogation. L'impact commercial. est beaucoup plus petit que de ne pas pouvoir se connecter, et c'est tolérable.
58. Comment divisez-vous habituellement la base de données ?
- Répartition verticale de la base de données : sur la base des tables, différentes tables sont divisées en différentes bases de données en fonction des différentes propriétés de l'entreprise.
- Répartition horizontale de la base de données : en fonction des champs et en suivant certaines stratégies (hachage, plage, etc.), les données d'une base de données sont divisées en plusieurs bases de données.
59. Alors comment répartir les montres ?
- Répartition horizontale des tables : divisez les données d'une table en plusieurs tables en fonction des champs et selon certaines stratégies (hachage, plage, etc.).
- Répartition verticale du tableau : En fonction des champs et selon l'activité des champs, les champs du tableau sont répartis en différentes tables (table principale et table étendue).
60. Quelles sont les méthodes de routage pour le fractionnement horizontal des tables ?
Qu'est-ce que le routage ? C'est dans quel tableau les données doivent être divisées.
Il existe trois méthodes de routage principales pour le partitionnement horizontal de tables :
-
Routage de plage : sélectionnez des colonnes de données ordonnées (par exemple, mise en forme, horodatage, etc.) comme conditions de routage, et différents segments sont dispersés dans différentes tables de base de données.
Nous pouvons observer certains systèmes de paiement et constater que nous ne pouvons vérifier les enregistrements de paiement que dans un délai d'un an. Cela peut être dû au fait que la société de paiement a divisé les tableaux en fonction du temps.
La complexité de la conception du routage de plage se reflète principalement dans la sélection de la taille du segment. Si le segment est trop petit, cela entraînera trop de sous-tableaux après la segmentation et augmentera la complexité de la maintenance si le segment est trop grand. , cela peut donner lieu à une seule table. Il existe toujours des problèmes de performances. Il est généralement recommandé que la taille du segment soit comprise entre 1 million et 20 millions. La taille de segment appropriée doit être sélectionnée en fonction de l'activité.
L'avantage du routage par plage est que les nouvelles tables peuvent être étendues en douceur à mesure que les données augmentent. Par exemple, si le nombre actuel d'utilisateurs est de 1 million, si ce nombre passe à 10 millions, il vous suffit d'ajouter une nouvelle table et les données d'origine n'ont pas besoin d'être modifiées. Un inconvénient relativement implicite du routage par plage est une répartition inégale. Si la table est divisée en tables selon 10 millions, il est possible que la quantité réelle de données stockées dans un segment ne soit que de 1 000, alors que la quantité réelle de données stockée dans un autre segment. est 900. Dix mille.
-
Routage de hachage : sélectionnez la valeur d'une certaine colonne (ou une combinaison de certaines colonnes) pour l'opération de hachage, puis distribuez-la à différentes tables de base de données en fonction du résultat du hachage.
En prenant également l'identifiant de la commande comme exemple, si nous planifions 4 tables de base de données dès le début, l'algorithme de routage peut simplement utiliser la valeur de l'identifiant % 4 pour représenter le numéro de table de base de données à laquelle appartiennent les données. 12 est placé dans le nombre Dans la sous-table de 50, la commande avec l'identifiant 13 est placée dans la table de mots numérotée 61.
La complexité de la conception du routage de hachage se reflète principalement dans la sélection du nombre initial de tables. Trop de tables seront difficiles à maintenir, et trop peu de tables peuvent entraîner des problèmes de performances avec une seule table. Après avoir utilisé le routage de hachage, il est très difficile d'augmenter le nombre de sous-tables et toutes les données doivent être redistribuées. Les avantages et les inconvénients du routage par hachage sont fondamentalement opposés à ceux du routage par plage. L'avantage du routage par hachage est que les tables sont relativement uniformément réparties. L'inconvénient est qu'il est difficile d'étendre de nouvelles tables et que toutes les données doivent être redistribuées.
-
Configuration du routage : La configuration du routage est une table de routage, utilisant une table indépendante pour enregistrer les informations de routage. En prenant l'identifiant de commande comme exemple, nous ajoutons une nouvelle table order_router. Cette table contient deux colonnes : orderjd et tablejd. La table_id correspondante peut être interrogée en fonction de orderjd.
La configuration du routage est simple dans sa conception et très flexible à utiliser, notamment lors de l'extension de la table. Il vous suffit de migrer les données spécifiées, puis de modifier la table de routage.
L'inconvénient de la configuration du routage est qu'il doit être interrogé plus d'une fois, ce qui affectera les performances globales ; et si la table de routage elle-même est trop volumineuse (par exemple, des centaines de millions de données), les performances peuvent Deviendra également un goulot d'étranglement. Si nous divisons à nouveau la table de routage en bases de données. Si vous divisez la table, vous serez confronté à un problème de sélection d'algorithme de routage en boucle infinie.
61. Comment réaliser une expansion de capacité sans temps d’arrêt ?
En fait, l'expansion sans temps d'arrêt est une opération très fastidieuse et risquée. Bien entendu, l'entretien est beaucoup plus simple à répondre.
62 Quels sont les middlewares couramment utilisés pour le partitionnement de bases de données et de tables ?
63 Alors, selon vous, quels problèmes seront causés par la sous-base de données et la sous-table ?
Du point de vue de la sous-base de données :
Un gros avantage de l'utilisation d'une base de données relationnelle est qu'elle garantit l'intégrité des transactions.
Une fois la base de données divisée, les transactions sur une seule machine ne sont plus nécessaires et doivent être résolues à l'aide de transactions distribuées.
- Problème JOIN entre bases de données
Lorsque nous sommes dans une base de données, nous pouvons également utiliser JOIN pour joindre des requêtes de table, mais après avoir traversé des bases de données, nous ne pouvons pas utiliser JOIN.
La solution à ce stade est de corréler dans le code d'entreprise, c'est-à-dire de vérifier d'abord les données d'une table, puis de vérifier une autre table à travers les résultats obtenus, puis d'utiliser le code pour corréler pour obtenir le résultat final .
Cette méthode est légèrement plus compliquée à mettre en œuvre, mais elle est acceptable.
Certains champs peuvent également être redondants de manière appropriée. Par exemple, la table précédente stockait un ID de corrélation, mais l'entreprise exigeait souvent que le nom correspondant ou d'autres champs soient renvoyés. À ce stade, ces champs peuvent être ajoutés de manière redondante à la table actuelle pour supprimer les opérations nécessitant une association.
Une autre méthode est l'hétérogénéité des données Grâce à la synchronisation binlog et à d'autres méthodes, les données qui nécessitent une jointure entre bases de données sont hétérogènes dans une structure de stockage telle que ES et sont interrogées via ES.
Du point de vue des sous-tableaux :
- Les problèmes de comptage de nœuds croisés, de tri, de regroupement et d'agrégation
ne peuvent être implémentés que par le code métier ou utiliser un middleware pour résumer les données dans chaque table, trier, paginer et revenir.
- Migration des données, planification de la capacité, expansion et autres problèmes
La migration des données, comment planifier la capacité, si une expansion pourrait être à nouveau nécessaire à l'avenir, etc., sont autant de questions qui doivent être prises en compte.
Une fois la table de base de données divisée, elle ne peut plus s'appuyer sur le propre mécanisme de génération de clé primaire de la base de données, des moyens sont donc nécessaires pour garantir que la clé primaire globale est unique.
Il s'agit toujours d'une incrémentation automatique, mais la taille du pas d'incrémentation automatique est définie. Par exemple, il existe maintenant trois tables, la taille du pas est définie sur 3 et les valeurs d'identification initiales des trois tables sont respectivement 1, 2 et 3. De cette façon, la croissance ID de la première table est de 1, 4 et 7. Le deuxième tableau est 2, 5, 8. Le troisième tableau est 3, 6, 9, il n'y aura donc pas de duplication. UUID, c'est le plus simple, mais l'insertion discontinue de clé primaire entraînera de graves fractionnements de pages et de mauvaises performances. ID distribué, le plus célèbre est l'algorithme open source sonwflake snowflake de Twitter
Exploitation et maintenance
64 Comment supprimer des données avec plus d'un million de niveaux ?
À propos de l'index : étant donné que l'index nécessite des coûts de maintenance supplémentaires, le fichier d'index étant un fichier distinct, lorsque nous ajoutons, modifions ou supprimons des données, des opérations supplémentaires sur le fichier d'index se produiront et ces opérations nécessiteront des coûts supplémentaires. réduira l’efficacité d’exécution de l’ajout/modification/suppression.
Ainsi, lorsque nous supprimons des millions de données dans la base de données, nous consultons le manuel officiel de MySQL et apprenons que la vitesse de suppression des données est directement proportionnelle au nombre d'index créés.
Ainsi, lorsque nous voulons supprimer des millions de données, nous pouvons d'abord supprimer l'index puis supprimer les données inutiles Une fois la suppression terminée, recréer l'index La création de l'index est également très. rapide
65. Millions Comment ajouter des champs à une grande table avec des dizaines de millions de tables ?
Lorsque la quantité de données de la base de données en ligne atteint des millions ou des dizaines de millions, ajouter un champ n'est pas si simple car la table peut être verrouillée pendant une longue période.
Pour ajouter des champs à une grande table, il existe généralement ces méthodes :
-
Convertissez-la via une table intermédiaire
Créez une nouvelle table temporaire, copiez complètement la structure de l'ancienne table, ajoutez des champs, puis copiez le données de l'ancienne table. Supprimez l'ancienne table et nommez la nouvelle table avec le nom de l'ancienne table. Cette méthode peut entraîner la perte de certaines données.
-
Utiliser pt-online-schema-change
pt-online-schema-change est un outil développé par la société percona. Il permet de modifier la structure des tables en ligne. Son principe passe également par des tables intermédiaires.
-
Ajoutez-le d'abord à la base de données esclave, puis effectuez la commutation maître-esclave
Si une table contient une grande quantité de données et est une table chaude (la lecture et l'écriture sont particulièrement fréquentes), vous pouvez envisager de l'ajouter à la base de données esclave d'abord, puis effectuez la commutation maître-esclave. Ajoutez ensuite des champs à plusieurs autres nœuds.
66. Que dois-je faire si le processeur de la base de données MySQL augmente ?
Processus de dépannage :
(1) Utilisez la commande top pour observer et déterminer si cela est causé par mysqld ou d'autres raisons.
(2) Si cela est causé par mysqld, affichez la liste des processus, vérifiez l'état de la session et déterminez si du SQL consommateur de ressources est en cours d'exécution.
(3) Découvrez le SQL à forte consommation et voyez si le plan d'exécution est précis, si l'index est manquant et si la quantité de données est trop importante.
Traitement :
(1) Tuez ces threads (et observez si l'utilisation du processeur diminue),
(2) Effectuez les ajustements correspondants (tels que l'ajout d'index, la modification de SQL, la modification des paramètres de mémoire)
(3) Exécutez-les Encore des SQL.
Autres situations :
Il est également possible que chaque instruction SQL ne consomme pas beaucoup de ressources, mais du coup, un grand nombre de connexions de session arrivent, provoquant une surtension du CPU. Dans ce cas, vous devez travailler avec l'application. pour analyser pourquoi le nombre de connexions augmente. Ensuite, effectuez les ajustements correspondants, comme limiter le nombre de connexions, etc.
[Recommandations associées : tutoriel vidéo mysql]
|
Le rôle des trois paradigmes majeurs est de contrôler la redondance de la base de données et de gagner de la place. En fait, les conceptions des sociétés Internet généralistes sont anti-paradigme, ce qui évite les tables croisées et les bases de données croisées par redondance. data , en utilisant l’espace pour le temps pour améliorer les performances. 
