Introduction détaillée au principe de MySQL JOIN

1. Présentation de la syntaxe de jointure

join est utilisé pour connecter des champs dans plusieurs tables. La syntaxe est la suivante :

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona

table1 : table de gauche ; table2 : table de droite.

JOIN est grossièrement divisé en trois catégories suivantes selon ses fonctions :

INNER JOIN (inner join, ou jointure équivalente) : obtient des enregistrements avec une relation de correspondance de connexion dans deux tables.

LEFT JOIN (jointure gauche) : obtenez les enregistrements complets de la table de gauche (table1), c'est-à-dire qu'il n'y a aucun enregistrement correspondant correspondant dans la table de droite (table2).

RIGHT JOIN (right join) : contrairement à LEFT JOIN, des enregistrements complets de la table de droite (table2) sont obtenus, c'est-à-dire qu'il n'y a aucun enregistrement correspondant correspondant dans la table de gauche (table1).

Remarque : MySQL ne prend pas en charge la jointure complète, mais vous pouvez utiliser le mot-clé UNION pour combiner LEFT JOIN et RIGHT JOIN afin de simuler une jointure FULL.

Regardez d'abord les deux tables de l'expérience. :

Commentaires du tableau, le nombre total de lignes est de 28856

Commentaires du tableau_pour, le nombre total de lignes est de 57, comments_id est indexé et l'ID La colonne est la clé primaire.

Les deux tableaux ci-dessus constituent la base de notre test. Jetez ensuite un œil à l'index. La table comments_for comments_id est indexée et l'ID. est la clé primaire.

Récemment, un développeur de l'entreprise m'a posé des questions sur MySQL JOIN JOIN Après l'avoir détaillé, j'ai dit que ma compréhension de MySQL JOIN n'était pas très approfondie, j'ai donc également vérifié de nombreux documents et je l'ai finalement publié. sur le compte officiel InsideMySQL. J'ai vu deux articles d'analyse sur JOIN et j'ai trouvé qu'ils étaient très bien écrits. Permettez-moi de partager mon test actuel de JOIN. Commençons par présenter l'algorithme JOIN de MySQL, qui est divisé en trois types (source : InsideMySQL) :

MySQL ne prend en charge qu'un seul algorithme JOIN, Nested-Loop Join (lien de boucle imbriquée), contrairement à d'autres entreprises. La base de données peut prendre en charge le hachage liens et fusionner les connexions, mais la jointure Nested-Loop Join de MySQL (lien de boucle imbriquée) a également de nombreuses variantes, qui peuvent aider MySQL à effectuer les opérations JOIN plus efficacement :

(1) Simple Nested- Loop Join (photo prise depuis InsideMySQL )

Cet algorithme est relativement simple. R1 est extrait de la table des pilotes pour correspondre à toutes les colonnes de la table S, puis R2, R3, jusqu'à ce que toutes les colonnes soient sélectionnées. les données de la table R sont mises en correspondance, puis les données sont fusionnées. Vous pouvez voir que cet algorithme nécessite des accès RN à la table S. Bien qu'il soit simple, la surcharge est encore trop élevée

(2) Index. Nested-Loop Join, la méthode d'implémentation est la suivante :

Connexion imbriquée d'index Puisqu'il y a des index sur la table non pilotée, lors de la comparaison Au lieu de comparer les enregistrements un par un, les index peuvent être utilisés pour réduire les comparaisons, accélérant ainsi les requêtes. C'est l'une des principales raisons pour lesquelles nous exigeons généralement que les champs associés aient des index lors des requêtes associées.

Lorsque cet algorithme effectue une requête de lien, la table du pilote effectuera une recherche en fonction de l'index du champ associé lorsqu'une valeur correspondante est trouvée sur l'index, elle reviendra à la table pour la requête, c'est-à-dire. seulement lorsque l'index correspond. Ce n'est qu'alors que la table sera renvoyée. Quant à la sélection de la table des pilotes, l'optimiseur MySQL choisira généralement la table des pilotes avec un petit nombre d'enregistrements. Cependant, lorsque le SQL est particulièrement complexe, des sélections incorrectes ne peuvent être exclues.

En mode lien imbriqué index, si la clé associée de la table non pilotée est la clé primaire, les performances seront très élevées. Si ce n'est pas la clé primaire, le nombre de lignes renvoyées sera. très élevée si elle est associée, l'efficacité sera particulièrement faible car plusieurs opérations de retour de table sont nécessaires. Associez d’abord l’index, puis effectuez l’opération de retour de table en fonction de l’ID de clé primaire de l’index secondaire. Dans ce cas, les performances seront relativement médiocres.

(3) Block Nested-Loop Join, implémenté comme suit :

Lorsqu'il y a un index, MySQL essaiera d'utiliser l'index imbriqué -Algorithme de jointure en boucle. Dans certains cas, la colonne Join peut ne pas avoir d'index. Dans ce cas, le choix de MySQL ne sera certainement pas l'algorithme Simple Nested-Loop Join introduit en premier, mais donnera la priorité à l'algorithme Block Nested-Loop Join. .

Par rapport à la jointure simple en boucle imbriquée, la jointure en boucle imbriquée par bloc a un processus de traitement intermédiaire supplémentaire, qui est le tampon de jointure. Utilisez le tampon de jointure pour mettre en mémoire tampon toutes les colonnes liées à la requête JOIN de la table du pilote. JOIN BUFFER, puis Comparez les lots avec des tables non pilotées. Si cela est également implémenté, plusieurs comparaisons peuvent être fusionnées en une seule, réduisant ainsi la fréquence d'accès aux tables non pilotées. Autrement dit, la table S ne doit être accédée qu’une seule fois. De cette façon, la table non pilotée ne sera pas accédée plusieurs fois, et ce n'est que dans ce cas que le tampon de jointure sera accédé.

Dans MySQL, nous pouvons définir la valeur du tampon de jointure via le paramètre join_buffer_size, puis effectuer l'opération. Par défaut join_buffer_size=256K, MySQL mettra en cache toutes les colonnes requises dans le tampon de jointure pendant la recherche, y compris les colonnes sélectionnées, au lieu de simplement mettre en cache les colonnes associées. Dans un SQL avec N associations JOIN, N-1 tampons de jointure seront alloués lors de l'exécution.

L'introduction ci-dessus est complète, jetons un œil aux exemples spécifiques

(1) Tableau complet JOIN

EXPLAIN SELECT * FROM comments gc
JOIN comments_for gcf ON gc.comments_id=gcf.comments_id;

Regardez les informations de sortie :

Vous pouvez voir comments_for pendant analyse complète de la table En tant que table pilotée, étant donné que les champs associés sont indexés, une analyse complète de l'index est effectuée sur l'index idx_commentsid pour faire correspondre les commentaires de la table non pilotée, et une ligne peut être mise en correspondance à chaque fois. À l'heure actuelle, la jointure en boucle imbriquée d'index est utilisée et la table entière est mise en correspondance via l'index. Nous pouvons voir que, comme la taille de la table comments_for est beaucoup plus petite que celle des commentaires, MySQL donne la priorité à la petite table comments_for comme moteur. tableau.

(2) Tableau complet JOIN + conditions de filtrage

SELECT * FROM comments gc
JOIN comments_for gcf ON gc.comments_id=gcf.comments_id
WHERE gc.comments_id =2056

À ce stade, l'index Nested-Loop Join est utilisé. Tout d'abord, la clé primaire des commentaires de la table pilote est filtrée pour correspondre à un élément. Ensuite, l'index idx_commentsid de la table non pilotée comments_for est recherché et mis en correspondance. le résultat final de la correspondance devrait affecter un élément.Il s'agit simplement d'une opération d'accès à l'index idx_commentsid de la table non pilotée, et l'efficacité est relativement élevée.

(3) Jetez un oeil à la situation où le champ associé n'a pas d'index :

EXPLAIN SELECT * FROM comments gc
JOIN comments_for gcf ON gc.order_id=gcf.product_id

Jetons un coup d'œil au plan d'exécution :

À partir du plan d'exécution, nous pouvons voir que cette table JOIN utilise Block Nested-Loop Join pour effectuer association de table.Tout d'abord, la petite table comments_for (seulement 57 lignes) est utilisée comme table pilote, puis les données requises de comments_for sont mises en cache dans le tampon JOIN et la table des commentaires est analysée par lots, c'est-à-dire une seule correspondance. est effectué. Le principe est que le tampon de jointure est suffisamment grand pour stocker les données mises en cache comments_for.

Et nous voyons une invite très claire dans le plan d'exécution : Using Where ; Using join buffer (Block Nested Loop)

Généralement, lorsque cela se produit, cela prouve que notre SQL doit être optimisé .

Il est à noter que dans ce cas, MySQL choisira également la méthode violente de Simple Nested-Loop Join. Je n'ai pas compris comment il choisit cet optimiseur, mais il utilise généralement Block Nested -Loop Join. , étant donné que le CBO est basé sur la surcharge, les performances de Block Nested-Loop Join sont bien meilleures que celles de Simple Nested-Loop Join.

(4) Jetez un œil à la jointure gauche

EXPLAIN SELECT * FROM comments gc
LEFT JOIN comments_for gcf ON gc.comments_id=gcf.comments_id

Regardez le plan d'exécution :

Dans ce cas, puisque nos champs associés sont indexés, nous appelons Index Nested-Loop Join, mais lorsqu'il n'y a pas de condition de filtre, la première table sera sélectionnée comme Utilisez la table pilote pour effectuer JOIN et associez l'index de la table non pilotée pour effectuer une jointure à boucle imbriquée d'index.

Si vous ajoutez la condition de filtre gc.comments_id =2056, cela filtrera une jointure à boucle imbriquée d'index pour la table non pilotée, ce qui est très efficace.

Si c'est le suivant :

EXPLAIN SELECT * FROM comments_for gcf
LEFT JOIN comments gc ON gc.comments_id=gcf.comments_id
WHERE gcf.comments_id =2056

Si vous filtrez à travers la table gcf, ce sera sélectionnée par défaut La table gcf est utilisée comme table des pilotes, car elle a évidemment été filtrée et il y aura très peu de conditions correspondantes. Pour plus de détails, vous pouvez consulter le plan d'exécution :

À ceci. point, la jointure est fondamentalement claire, et elle n'est pas encore terminée, tout le monde est invité à signaler les erreurs et je les corrigerai sérieusement. . . .

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!