Résumé des principes MySQL : jointure gauche, jointure droite, jointure interne et jointure de hachage

Cet article vous apporte des connaissances pertinentes sur mysql, qui présente principalement les principes de fonctionnement de la jointure gauche, de la jointure droite, de la jointure interne et de la jointure de hachage, et analyse la différence entre sous-requête et jointure, et fournit des informations basées sur ce que vous avez appris. . Jetons un coup d'œil aux compétences pratiques qui doivent être maîtrisées au travail. J'espère que cela sera utile à tout le monde.

Apprentissage recommandé : Tutoriel vidéo mysql

1. Connexion JOIN à la base de données MySQL

Créez des index sur plusieurs tables, et les instructions JOIN et sous-requêtes de plusieurs tables sont relativement difficiles. De nombreux développeurs croient inconsciemment que JOIN réduira l'efficacité des performances de SQL, ils divisent donc le SQL multi-table en requêtes à table unique, pensant que cela affectera l'efficacité de l'exécution de SQL car les développeurs ne comprennent pas le processus d'implémentation de JOIN.

Les associations de tables entre les jointures utilisent des index pour la correspondance, en supposant que les tables R et S sont jointes.

La table R est appelée table pilote. Les données filtrées par la condition WHERE dans la table R seront interrogées une à une sur l'index correspondant de la table S. Si la quantité de données alimentant la table R n’est pas importante, l’algorithme ci-dessus est très efficace.

Quelle table est la table pilote pour chacun des trois types JOIN suivants :

SELECT * FROM R LEFT JOIN S ON R.x = S.x WEHRE ...
SELECT * FROM R RIGHT JOIN S ON R.x = S.x WEHRE ...
SELECT * FROM R INNER JOIN S ON R.x = S.x WEHRE ...

1, jointure interne

Pour INNER JOIN, la table pilote peut être la table R ou la table S. Affiche les données partagées par les côtés gauche et droit.

Dans ce scénario, celui qui a besoin d'interroger la plus petite quantité de données pilotera la table. Regardons les exemples suivants

SELECT * FROM R INNER JOIN S  ON R.x = S.x  WHERE R.y = ? AND S.z = ?

2, LEFT JOIN

Pour la jointure gauche ci-dessus, la table pilote est la table gauche R dans la jointure droite, la table pilote est la table droite S. Il s'agit du type JOIN qui détermine si les données de la table de gauche ou de la table de droite doivent être interrogées.

Renvoie tous les enregistrements de la table de gauche et les enregistrements avec des champs de jointure égaux dans la table de droite. Renvoie toutes les lignes du tableau de gauche même s'il n'y a aucune correspondance dans le tableau de droite.

SELECT * FROM R LEFT JOIN S  ON R.x = S.x  WHERE R.y = ? AND S.z = ?

3. RIGHT JOIN

Pour la jointure gauche ci-dessus, la table pilote est la table de gauche R dans la jointure droite, la table pilote est la table de droite S. Il s'agit du type JOIN qui détermine si les données de la table de gauche ou de la table de droite doivent être interrogées.

Renvoie tous les enregistrements dans la table de droite et les enregistrements avec des champs de jointure égaux dans la table de gauche. Renvoie toutes les lignes du tableau de droite même s'il n'y a aucune correspondance dans le tableau de gauche.

SELECT * FROM R RIGHT JOIN S  ON R.x = S.x  WHERE R.y = ? AND S.z = ?

2. Hash Join de base de données MySQL

1. Qu'est-ce que Hash Join

Le deuxième JOIN dans MySQL est Hash JOIN, qui est utilisé lorsque la condition de connexion entre deux tables n'a pas d'index. algorithme.

S'il n'y a pas de connexion, est-il possible de créer un index ?

Si certaines colonnes sont des index à faible sélectivité, les données doivent être triées lors de la création de l'index pour importer les données, ce qui affectera les performances d'importation ; l'index secondaire aura des problèmes pour renvoyer la table. Si la quantité de données filtrées est importante, une analyse directe de la table complète sera plus rapide.

Pour les requêtes commerciales OLAP (OLAP signifie Online Analytical Processing, utilisé pour l'analyse des données, il nous permet d'analyser simultanément les informations de plusieurs systèmes de bases de données), la jointure par hachage est une fonctionnalité essentielle. MySQL 8.0 commence à prendre en charge l'algorithme Hash Join, renforçant ainsi la prise en charge des activités OLAP.

Par conséquent, si la quantité de données que vous interrogez n'est pas trop importante et que le temps de réponse de la requête est requis à l'échelle de la minute, vous pouvez utiliser une seule instance de MySQL 8.0 pour terminer la requête de Big Data.

2. Comment fonctionne Hash JOIN

Hash JOIN apparaît dans le plan d'exécution de MySQL 8.0. Hash JOIN analyse les deux tables associées : d'abord, une table de hachage est créée lors du processus d'analyse de la table du lecteur ; Lors de l'analyse de la deuxième table, chaque enregistrement associé est recherché dans la table de hachage. S'il est trouvé, le dossier sera restitué.

La table des pilotes de sélection de jointure de hachage et l'algorithme de jointure de boucle imbriquée, les deux sont fondamentalement les mêmes. Les deux tables plus petites sont utilisées comme tables de pilotes. Si la table de pilotes est grande et que la table de hachage créée dépasse la taille de la mémoire, MySQL videra automatiquement les résultats sur le disque.

3. Sous-requête

J'ai constaté que de nombreux étudiants en développement, y compris moi-même, préfèrent écrire des sous-requêtes au lieu des instructions JOIN traditionnelles.

La logique de la sous-requête est très claire. Bien que JOIN puisse également répondre aux besoins, il n'est pas facile à comprendre car LEFT JOIN est une relation algébrique et les sous-requêtes sont plus susceptibles d'être comprises du point de vue de la pensée humaine.

Cependant, dans MySQL 8.0, l'optimiseur optimisera automatiquement la sous-requête in dans un plan d'exécution JOIN, ce qui améliorera considérablement les performances.

Nous devons uniquement nous concentrer sur le plan d'exécution SQL, si les deux plans d'exécution sont identiques, il n'y aura aucune différence de performances.

Avant MySQL 8.0, MySQL n'optimisait pas complètement les sous-requêtes. Par conséquent, vous verrez l'indice DEPENDENT SUBQUERY dans le plan d'exécution de la sous-requête, qui indique qu'il s'agit d'une sous-requête dépendante et que la sous-requête doit s'appuyer sur l'association de la table externe. LA SOUS-REQUÊTE DÉPENDANTE peut être très lente à exécuter et la plupart du temps, vous devez la convertir manuellement en jointure entre deux tables.

Donc le blogueur ici conseilsTout le monde, si votre version actuelle de MySQL 8.0 peut écrire des sous-requêtes, car l'optimisation des sous-requêtes est assez complète

Pour les versions MySQL antérieures à MySQL 8.0, vous devez visualiser le plan d'exécution SQL de tous les sous-requêtes ; . Conseils pour DEPENDENT SUBQUERY doit être optimisé, sinon cela aura un impact significatif sur les performances de l'entreprise ; l'optimisation de DEPENDENT SUBQUERY est généralement réécrite en tant que table dérivée pour les jointures de tables.

Apprentissage recommandé : 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!