Parlons brièvement de la requête de jointure dans MySQL

Cet article vous apporte des connaissances pertinentes sur mysql, qui présente principalement des problèmes liés aux requêtes de jointure. Examinons-le ensemble, j'espère qu'il sera utile à tout le monde.

Apprentissage recommandé : Tutoriel vidéo MySQL

L'impact de l'index sur la requête de jointure

Préparation des données

Supposons qu'il y ait deux tables t1 et t2, les deux tables ont un identifiant d'index de clé primaire et un champ d'index. il n'y a pas d'index dans les champs a et b, puis insérez 100 lignes de données dans la table t1 et 1000 lignes de données dans la table t2 pour l'expérience

CREATE TABLE `t2` (
 `id` int NOT NULL,
 `a` int DEFAULT NULL,
 `b` int DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `t2_a_index` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE PROCEDURE **idata**()
BEGIN
  DECLARE i INT;
  SET i = 1;
  WHILE (i <h3 data-id="heading-3"><strong>Il existe un processus de requête d'index</strong></h3><p>Nous utilisons la requête SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1. a=t2.a); Parce que l'optimiseur de requête de jointure MYSQL peut ne pas être en mesure de s'exécuter selon nos souhaits, donc pour l'analyse, nous choisissons d'utiliser STRAIGHT_JOIN à la place, afin d'observer de manière plus intuitive</p> <p><parlons bri de la requ jointure dans mysql src="https://Parlons%20bri%C3%A8vement%20de%20la%20requ%C3%AAte%20de%20jointure%20dans%20MySQL.php.cn/upload/article/000/000/067/64b9ac3a02f2e5095eba59bdfbcd23d6-2.png" alt="图 1" loading="lazy"></parlons> Figure 1</p><p> Vous pouvez voir que we T1 est utilisé comme table pilote et t2 est utilisé comme table pilotée. L'explication dans la figure ci-dessus montre que cette requête utilise le champ un index de la table t2, donc l'exécution. Le processus de cette instruction doit être le suivant : </p>

• De t1 Lire une ligne de données r de la table

• Récupérer le champ a des données r vers la table t2 pour la correspondance

• Supprimer les lignes qualifiées dans la table t2, et formez une ligne avec r dans le cadre de l'ensemble de résultats

• Répétez les étapes 1 à 3 jusqu'à ce que la table t1 boucle les données

Ce processus est appelé Index Nested-Loop Join Dans ce processus, la table pilote t1. effectue une analyse complète de la table car nous avons inséré 100 lignes dans les données de la table t1, donc le nombre de lignes analysées cette fois est de 100. Lors de l'exécution d'une requête de jointure, chaque ligne de la table t1 doit être recherchée dans la table t2. La recherche est utilisée, car les données que nous construisons ont une correspondance biunivoque, donc chaque recherche n'analyse qu'une seule ligne, c'est-à-dire que la table t2 analyse un total de 100 lignes et le nombre total de lignes analysées pendant toute la durée. le processus de requête est de 100+100=200 lignes.

Processus de requête sans index

SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.a = t2.b);

Figure 2

On peut voir que puisqu'il n'y a pas d'index sur le champ B de la table t2, une analyse complète de la table doit être effectuée à chaque fois lors de la correspondance de t2 à t1 lors de l'exécution de ce qui précède SQL, calculé de cette manière, t2 peut être analysé jusqu'à 100 fois et le nombre total d'analyses est de 100*1 000 = 100 000 lignes.

Bien sûr, ce résultat de requête est toujours basé sur le fait que les deux tables que nous avons construites sont petites. S'il s'agit d'une table de l'ordre de 100 000 lignes, il faudra scanner 10 milliards de lignes, ce qui est terrible !

2. Comprendre Bloquer la jointure en boucle imbriquée

Bloquer la jointure en boucle imbriquéeProcessus de requête

Il n'y a donc pas d'index sur la table pilotée, comment tout cela s'est-il produit ?

En fait, lorsqu'il n'y a pas d'index disponible sur la table pilotée, le déroulement de l'algorithme est le suivant :

• Lire les données de t1 dans la mémoire du thread join_buffer Parce que ce que nous avons écrit ci-dessus est select * from, c'est le cas. équivaut à mettre l'intégralité du t1 La table est mise en mémoire ;

• Le processus d'analyse de t2 consiste en fait à extraire chaque ligne de t2 et à la comparer avec les données du join_buffer. Si les conditions de jointure sont remplies, ce sera le cas. être renvoyé dans le cadre de l’ensemble de résultats.

Nous pouvons donc trouver cet indice basé sur la partie Extra de la figure 2 en utilisant le tampon de jointure. Pendant tout le processus, une analyse complète des tables t1 et t2 a été effectuée, le nombre de lignes analysées était donc de 100. +1 000 = 1 100 lignes, car join_buffer est organisé dans un tableau non ordonné, donc pour chaque ligne du tableau t2, 100 jugements doivent être effectués. Le nombre total de jugements à effectuer en mémoire est de 100*1 000 = 100 000 fois, mais. Étant donné que ces 100 000 fois se produisent en mémoire, la vitesse est beaucoup plus rapide et les performances sont meilleures.

Join_buffer

Comme vous le savez d'après ce qui précède, sans index, MySQL lit les données dans la mémoire pour le jugement des boucles, donc cette mémoire n'est certainement pas illimitée pour votre utilisation. Pour le moment, nous devons utiliser un. Paramètre join_buffer_size, la taille par défaut de cette valeur est de 256 Ko, comme indiqué ci-dessous :

SHOW VARIABLES LIKE '%join_buffer_size%';

Figure 4

Si les données interrogées sont trop volumineuses pour être chargées en une seule fois, et seulement une partie des données (80 éléments) peut être chargé, alors le processus de requête devient Cela ressemble à ceci

• 扫描表 t1，顺序读取数据行放入 join_buffer 中，直至加载完第 80 行满了

• 扫描表 t2，把 t2 表中的每一行取出来跟 join_buffer 中的数据做对比，将满足条件的数据作为结果集的一部分返回

• 清空 join_buffer

• 继续扫描表 t1，顺序读取剩余的数据行放入 join_buffer 中，执行步骤 2

这个流程体现了算法名称中 Block 的由来，分块 join，可以看出虽然查询过程中 t1 被分成了两次放入 join_buffer 中，导致 t2 表被扫描了 2次，但是判断等值条件的次数还是不变的，依然是（80+20）*1000=10 万次。

所以这就是有时候 join 查询很慢，有些大佬会让你把 join_buffer_size 调大的原因。

如何正确的写出 join 查询

驱动表的选择

• 有索引的情况下

在这个 join 语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。

假设被驱动表的行数是 M，每次在被驱动表查询一行数据，先要走索引 a，再搜索主键索引。每次搜索一棵树近似复杂度是以 2为底的 M的对数，记为 log2M，所以在被驱动表上查询一行数据的时间复杂度是 2*log2M。

假设驱动表的行数是 N，执行过程就要扫描驱动表 N 行，然后对于每一行，到被驱动表上 匹配一次。因此整个执行过程，近似复杂度是 N + N2log2M。显然，N 对扫描行数的影响更大，因此应该让小表来做驱动表。

• 那没有索引的情况

上述我知道了，因为 join_buffer 因为存在限制，所以查询的过程可能存在多次加载 join_buffer，但是判断的次数都是 10 万次，这种情况下应该怎么选择？

假设，驱动表的数据行数是 N，需要分 K 段才能完成算法流程，被驱动表的数据行数是 M。这里的 K不是常数，N 越大 K就越大，因此把 K 表示为λ*N，显然λ的取值范围 是 (0,1)。

扫描的行数就变成了 N+λNM，显然内存的判断次数是不受哪个表作为驱动表而影响的，而考虑到扫描行数，在 M和 N大小确定的情况下，N 小一些，整个算是的结果会更小，所以应该让小表作为驱动表

总结：真相大白了，不管是有索引还是无索引参与 join 查询的情况下都应该是使用小表作为驱动表。

什么是小表

还是以上面表 t1 和表 t2 为例子：

SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.b = t2.b WHERE t2.id <p>上面这两条 SQL 我们加上了条件 t2.id </p><p>再看另一组：</p><pre class="brush:php;toolbar:false">SELECT t1.b,t2.* FROM t1 STRAIGHT_JOIN t2 ON t1.b = t2.b WHERE t2.id <p>这个例子里，表 t1 和 t2 都是只有 100 行参加 join。 但是，这两条语句每次查询放入 join_buffer 中的数据是不一样的: 表 t1 只查字段 b，因此如果把 t1 放到 join_buffer 中，只需要放入字段 b 的值; 表 t2 需要查所有的字段，因此如果把表 t2 放到 join_buffer 中的话，就需要放入三个字 段 id、a 和 b。</p><p>这里，我们应该选择表 t1 作为驱动表。也就是说在这个例子里，”只需要一列参与 join 的 表 t1“是那个相对小的表。</p><p>结论：</p><p>在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过 滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”， 应该作为驱动表。</p><p>推荐学习：<a href="https://www.php.cn/course/list/51.html" target="_blank" textvalue="mysql视频教程">mysql视频教程</a></p>

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!