Apprentissage avancé MySQL : compréhension approfondie des trois algorithmes de jointure

Cet article est une étude avancée de MySQL. Il présentera en détail le principe de la connexion par jointure et les trois algorithmes de jointure.

Nous utilisons souvent join pour connecter plusieurs tables lors de l'interrogation de plusieurs tables. En fait, l'efficacité de la jointure n'est pas bonne, nous devrions donc essayer d'éviter de l'utiliser. Son essence est la correspondance circulaire entre chaque table. Un algorithme de jointure, Nested-Loop Join, possède de nombreuses variantes de l'algorithme, ce qui améliore réellement l'efficacité d'exécution de la jointure. [Recommandations associées : Tutoriel vidéo MySQL]

1. Simple Nested-Loop Join (Simple Nested Loop Join)

Algorithme de jointure simple Nested-Loop (NLJ) à partir de la première table de la boucle Lire une ligne à la fois, en passant chaque ligne à une boucle imbriquée qui correspond aux données par souci de cohérence. Par exemple, le SQL de la table pilote User et de la table pilotée UserInfo est select * from User u left join User_info info on u.id = info.user_id. boucle. La logique du pseudo-code devrait être C'est un select * from User u left join User_info info on u.id = info.user_id，其实就是我们常用的for循环，伪代码的逻辑应该是

for(User u:Users){
    for(UserInfo info:UserInfos){
        if(u.id == info.userId){
            // 得到匹配数据
        }
    }
}

简单粗暴的算法，每次从User表中取出一条数据，然后扫描User_info中的所有记录匹配，最后合并数据返回。

假如驱动表User有10条数据，被驱动表UserInfo也有10条数据，那么实际上驱动表User会被扫描10次，而被驱动表会被扫描10*10=100次(每扫描一次驱动表，就会扫描全部的被驱动表)，这种效率是很低的，对数据库的开销比较大，尤其是被驱动表。每一次扫描其实就是从硬盘中读取数据加载到内存中,也就是一次IO，目前IO是最大的瓶颈

2. Index Nested-Loop Join(索引嵌套循环连接)

索引嵌套循环是使用索引减少扫描的次数来提高效率的，所以要求非驱动表上必须有索引才行。

在查询的时候，驱动表(User) 会根据关联字段的索引进行查询，当索引上找到符合的值，才会进行回表查询。如果非驱动表(User_info)的关联字段(user_id)是主键的话，查询效率会非常高(主键索引结构的叶子结点包含了完整的行数据(InnoDB))，如果不是主键，每次匹配到索引后都需要进行一次回表查询(根据二级索引(非主键索引)的主键ID进行回表查询)，性能肯定弱于主键的查询。

上图中的索引查询之后不一定会回表，什么情况下会回表，这个要看索引查询到的字段能不能满足查询需要的字段，具体可以参考之前的文章：你需要知道的一些索引基础知识 和 B+树的索引知识

3. Block Nested-Loop Join(缓存块嵌套循环连接)

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了，每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录 然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了IO的次数。为了减少被驱动表的IO次数，就出现了Block Nested-Loop Join的方式。

不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区，将驱动表join相关的部分数据列(大小是join buffer的限制)缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次，降低了非驱动表的访问频率。

驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况下join_buffer_size=256k,查询的时候Join Buffer 会缓存所有参与查询的列而不是只有join的列，在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段，可以让join buffer中可以存放更多的列。

可以调整join_buffer_size的缓存大小show variables like '%join_buffer%'rrreee

algorithme simple et brut. Chaque fois qu'une donnée est extraite de la table User, tous les enregistrements de User_info sont analysés pour vérifier leur correspondance, et enfin les données sont fusionnées. et revint.

Si la table pilote User a 10 éléments de données et que la table pilotée UserInfo contient également 10 éléments de données, alors la table pilote User sera en fait analysée 10 fois et la table pilotée sera analysée 10*10 = 100 fois. (chaque fois que le pilote analyse la table, toutes les tables pilotées seront analysées), cette efficacité est très faible et la surcharge sur la base de données est relativement importante, en particulier les tables pilotées.

Chaque analyse lit en fait les données du disque dur et les charge dans la mémoire, ce qui est une IO Actuellement, l'IO est le plus gros goulot d'étranglement🎜🎜🎜🎜🎜🎜🎜2. Index Nested-Loop Join (indexation de boucle imbriquée)🎜🎜🎜🎜Index Imbriqué L'ensemble de boucles utilise des index pour réduire le nombre d'analyses afin d'améliorer l'efficacité, la table non pilotée doit donc avoir un index. 🎜🎜Lors de l'interrogation, la table pilote (Utilisateur) interrogera en fonction de l'index du champ associé Lorsqu'une valeur correspondante est trouvée sur l'index, la requête de table sera effectuée. Si le champ associé (user_id) de la table non pilotée (User_info) est la clé primaire, l'efficacité de la requête sera très élevée (les nœuds feuilles de la structure d'index de clé primaire contiennent des données de ligne complètes (InnoDB)). pas la clé primaire, l'index sera mis en correspondance à chaque fois. Enfin, une requête de retour de table est requise (une requête de retour de table est effectuée en fonction de l'ID de clé primaire de l'index secondaire (index de clé non primaire)), et les performances sont nettement plus faible que la requête de clé primaire. 🎜🎜🎜🎜Up La requête d'index dans la figure ne renvoie pas nécessairement la table. Dans quelles circonstances elle renverra la table Cela dépend si les champs interrogés par l'index peuvent répondre aux champs requis par la requête. Pour plus de détails, veuillez vous référer à l'article précédent. : 🎜Quelques bases de l'index que vous devez connaître Connaissance et connaissance de l'index de l'arbre B+🎜🎜🎜🎜🎜3 Block Nested-Loop Join (connexion en boucle imbriquée par bloc de cache)🎜🎜🎜🎜S'il existe un index, alors la méthode d'indexation le fera. être utilisé pour joindre, si la colonne de la jointure n'a pas d'index, la table pilotée doit être analysée trop de fois. Chaque fois que vous accédez à la table pilotée, les enregistrements de la table seront chargés dans la mémoire, puis. un enregistrement est extrait de la table pilote pour le faire correspondre. Une fois la correspondance terminée, la mémoire est effacée, puis chargez un enregistrement de la table pilote, puis chargez l'enregistrement de la table pilotée dans la mémoire pour la correspondance. et encore une fois, augmentant considérablement le nombre d’IO. Afin de réduire le nombre d’E/S sur la table pilotée, la méthode Block Nested-Loop Join a émergé. 🎜🎜Il n'obtient plus les données de la table pilote une par une, mais les obtient pièce par pièce. Le tampon de jointure est introduit pour mettre en cache certaines colonnes de données liées à la jointure de la table pilote (la taille est la limite de la jointure). tampon) dans le tampon de jointure, puis analyse complète de la table pilotée, chaque enregistrement de la table pilotée est mis en correspondance avec tous les enregistrements de la table pilote dans le tampon de jointure en même temps (opération en mémoire), comparaisons multiples dans une simple boucle imbriquée sont fusionnés en un seul, réduisant ainsi le coût de non-fréquence d'accès à la table des pilotes. 🎜🎜🎜🎜Pilote La possibilité de charger la table en même temps dépend de la capacité du tampon de jointure à stocker toutes les données. Par défaut, join_buffer_size=256k, lors de l'interrogation, le tampon de jointure mettra en cache toutes les colonnes participant à la requête. des seules colonnes de jointure, dans un SQL avec N associations de jointure, N-1 tampons de jointure seront alloués. Par conséquent, lors de l'interrogation, essayez de réduire les champs inutiles afin que davantage de colonnes puissent être stockées dans le tampon de jointure. 🎜🎜Vous pouvez ajuster la taille du cache de join_buffer_size afficher les variables comme '%join_buffer%' Cette valeur peut être modifiée en fonction de la situation réelle. 🎜🎜🎜🎜

Pour utiliser l'algorithme Block Nested-Loop Join, vous devez activer le paramètre optimiseur_switch de la configuration de gestion de l'optimiseur block_nested_loop sur on, qui est activé par défaut. Disponible via show variables like '%optimizer_switch%' 查看block_nested_loop statut.

Vous pouvez comprendre les trois algorithmes ci-dessus. En fait, dans le travail réel, tant que nous pouvons faire bon usage des index, ce sera bien. Même pour les connexions de jointure, nous devons faire attention à savoir si les champs associés. sont indexés, ou nous devons savoir utiliser les index pour assurer l'efficacité des requêtes.

Adresse originale : https://juejin.cn/post/7014105037517357093

Auteur : M. Ji

Pour plus de connaissances liées à la programmation, veuillez visiter : Introduction à la programmation ! !

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!