MySQL の結合クエリについて簡単に説明します

この記事では、mysql に関する関連知識を提供します。主に結合クエリに関する関連問題を紹介します。一緒に見てみましょう。皆様のお役に立てれば幸いです。

推奨される学習: mysql ビデオ チュートリアル

結合クエリに対するインデックスの影響

データ準備

2 つのテーブル t1 と t2 があるとします。どちらのテーブルにも主キーのインデックス ID とインデックス フィールド a および b フィールドがあり、インデックスはありません。次に、100 行のデータを t1 テーブルとt2 テーブル。実験用に 1000 行のデータを挿入します。

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>インデックス クエリ プロセスがあります。</strong></h3><p>クエリ SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.a) を使用します。 =t2.a); 結合クエリ MYSQL オプティマイザーが希望どおりに実行できない可能性があるため、分析では、より直観的に観察するために代わりに STRAIGHT_JOIN を使用することを選択します。</p><p><mysql src="https://MySQL%20%E3%81%AE%E7%B5%90%E5%90%88%E3%82%AF%E3%82%A8%E3%83%AA%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6%E7%B0%A1%E5%8D%98%E3%81%AB%E8%AA%AC%E6%98%8E%E3%81%97%E3%81%BE%E3%81%99.php.cn/upload/article/000/000/067/64b9ac3a02f2e5095eba59bdfbcd23d6-2.png" alt="图 1" loading="lazy"></mysql> 図 1</p> <p>「t1 を駆動テーブルとして使用し、t2 を駆動テーブルとして使用していることがわかります。上の図の説明は、このクエリが t2 テーブルのインデックスであるフィールドを使用していることを示しているため、このステートメントの実行プロセスは次のようになります。 </p>

• テーブル t1 からデータ r の行を読み取ります

• #マッチングのためにデータ r からテーブル t2 にフィールド a を取得します

• t2 テーブル内の条件を満たす行を取り出し、結果セットの一部として r を含む行を形成します

• 手順 1 ～ 3 を繰り返します。テーブル t1 がデータをループするまで

このプロセスはインデックス ネストループ結合と呼ばれます。このプロセスでは、ドライバー テーブル t1 はテーブル全体のスキャンを実行します。 t1 テーブル、このスキャン行の数は 100 で、結合クエリを実行するときは、t1 テーブルの各行を t2 テーブルで検索する必要があります。インデックス ツリー検索が使用されます。構築するデータは 1 対であるため、 -one 対応、各検索は 1 行のみをスキャンします。つまり、t2 テーブルも合計 100 行をスキャンします。クエリ プロセス全体でスキャンされる行の合計数は 100 100=200 行です。

#No Index query process

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

図 2

t2 テーブルのフィールド B にはインデックスがないことがわかります。 , 上記の SQL によると、実行中に、t1 が t2 と一致するたびにテーブル全体のスキャンを実行する必要があります。このように、t2 は最大 100 回スキャンする必要があり、スキャンの合計数は 100*1000 = 100,000 行になります。

もちろん、このクエリ結果は、構築した 2 つのテーブルが小さいという事実に基づいています。テーブルが 100,000 行ある場合、100 億行をスキャンする必要があります。これはひどいことです。

2. 理解する

ネストされたループ結合のブロック

##ネストされたループ結合のブロック

クエリ プロセス つまり、駆動テーブルにはインデックスがありません。なぜこのようなことが起こったのでしょうか?

実際、駆動テーブルに利用可能なインデックスがない場合、アルゴリズム プロセスは次のようになります:

t1 のデータをスレッド メモリの join_buffer に読み取ります。上で書いたのは select * from なので、t1 テーブル全体をメモリに入れるのと同じです;

• t2 をスキャンするプロセスは、実際には t2 の各行を取り出して照合します。 join_buffer 内の行とデータが比較され、結合条件を満たすデータが結果セットの一部として返されます。

したがって、結合バッファーの使用を説明する図 2 の追加部分と組み合わせると、この手がかりを見つけることができます。プロセス全体で、両方のテーブル t1と t2 が完了しました。テーブル全体のスキャンなので、スキャンされる行数は 100 1000 = 1100 行です。join_buffer は順序付けされていない配列で構成されているため、テーブル t2 の各行に対して 100 回の判断を行う必要があり、合計100回の判定をメモリ内で行う必要があり、判定回数は100*1000=10万回となりますが、この10万回をメモリ内で行うため、非常に高速でパフォーマンスが良くなります。

Join_buffer

上記のことからわかるように、インデックスがないと、MySQL はループ判定のためにデータをメモリに読み込むため、このメモリは決して無制限ではありません。使用する場合は、パラメータ join_buffer_size を使用する必要があります。値のデフォルト サイズは、以下に示すように 256k です。

SHOW VARIABLES LIKE '%join_buffer_size%';

図 4

If query データ一度に読み込むには大きすぎて、一部のデータ（80件）しか読み込めない場合、クエリ処理は次のようになります

• 扫描表 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>

以上がMySQL の結合クエリについて簡単に説明しますの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。