MySQL의 조인 쿼리에 대해 간략하게 설명합니다.

이 기사는 조인 쿼리와 관련된 문제를 주로 소개하는 mysql에 대한 관련 지식을 함께 살펴보겠습니다.

추천 학습: mysql 동영상 튜토리얼

조인 쿼리에 대한 인덱스의 영향

데이터 준비

두 개의 테이블 t1과 t2가 있고 두 테이블 모두 기본 키 인덱스 ID와 인덱스 필드가 있다고 가정합니다. a와 b 필드에는 인덱스가 없으며 t1 테이블에 100행의 데이터를 삽입하고 실험을 위해 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%EC%9D%98%20%EC%A1%B0%EC%9D%B8%20%EC%BF%BC%EB%A6%AC%EC%97%90%20%EB%8C%80%ED%95%B4%20%EA%B0%84%EB%9E%B5%ED%95%98%EA%B2%8C%20%EC%84%A4%EB%AA%85%ED%95%A9%EB%8B%88%EB%8B%A4..php.cn/upload/article/000/000/067/64b9ac3a02f2e5095eba59bdfbcd23d6-2.png" alt="图 1" loading="lazy"></mysql> 그림 1</p><p> T1이 구동 테이블로 사용되고 t2가 구동 테이블로 사용되는 것을 볼 수 있습니다. 위 그림의 설명은 이 쿼리가 t2 테이블의 인덱스 필드를 사용한다는 것을 보여줍니다. 이 명령문의 프로세스는 다음과 같아야 합니다. </p>

• From t1 테이블에서 데이터 r의 행을 읽습니다

• 데이터 r에서 테이블 t2까지 필드 a를 검색하여 일치

• 테이블에서 정규화된 행을 제거합니다. t2, 그리고 결과 세트의 일부로 r을 포함하는 행을 형성합니다

• 테이블 t1이 데이터를 루프할 때까지 1-3단계를 반복합니다

이 프로세스를 Index Nested-Loop Join이라고 합니다. 이 프로세스에서 드라이버 테이블 t1은 t1 테이블 Data에 100개의 행을 삽입했기 때문에 전체 테이블 스캔을 수행하므로 이번에 스캔한 행 수는 100개입니다. 조인 쿼리를 수행할 때 t1 테이블의 각 행을 t2 테이블에서 검색해야 합니다. 우리가 구성하는 데이터는 일대일 대응이므로 각 검색은 하나의 행만 스캔합니다. 즉, t2 테이블은 총 100개의 행을 스캔하고 전체 동안 스캔된 총 행 수입니다. 쿼리 프로세스는 100+100=200행입니다.

인덱스 없는 쿼리 프로세스

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

그림 2

t2 테이블의 B 필드에 인덱스가 없기 때문에 위의 실행시 t1에서 t2를 매칭할 때마다 매번 전체 테이블 스캔을 해야 함을 알 수 있다 이렇게 계산된 SQL은 t2를 최대 100회까지 스캔할 수 있으며, 총 스캔 횟수는 100*1000 = 100,000행이다.

물론, 이 쿼리 결과는 여전히 우리가 구축한 두 테이블이 작다는 사실을 기반으로 하고 있습니다. 100,000행 정도의 테이블이라면 100억 행을 스캔해야 하므로 정말 끔찍합니다!

2. Block Nested-Loop Join

Block Nested-Loop JoinQuery process

이제 구동 테이블에는 인덱스가 없는데 어떻게 이런 일이 발생한 걸까요?

실제로 구동되는 테이블에 사용 가능한 인덱스가 없는 경우 알고리즘 흐름은 다음과 같습니다.

• t1의 데이터를 스레드 메모리 Join_buffer로 읽어옵니다. 위에서 작성한 내용은 select * from이므로 입니다. 전체 t1을 넣는 것과 같습니다. 테이블이 메모리에 저장됩니다.

• t2를 스캔하는 프로세스는 실제로 t2의 각 행을 꺼내어 조인_버퍼의 데이터와 비교하는 것입니다. 결과 세트의 일부로 반환됩니다.

전체 프로세스 동안 테이블 t1과 t2에 대해 전체 테이블 스캔이 수행되었으므로 스캔된 행 수는 100개였습니다. +1000 = 1100개 행, Join_buffer가 순서가 지정되지 않은 배열로 구성되어 있으므로 테이블 t2의 각 행에 대해 100번의 판단이 이루어져야 합니다. 메모리에서 수행해야 하는 전체 판단 횟수는 100*1000=100,000번입니다. 이러한 10만번의 횟수가 메모리에서 발생하기 때문에 속도도 훨씬 빠르고 성능도 좋아집니다.

Join_buffer

위에서 알 수 있듯이 MySQL은 인덱스 없이 루프 판단을 위해 데이터를 메모리로 읽어 들이므로 이 메모리는 확실히 무제한으로 사용할 수 없습니다. 매개변수 Join_buffer_size, 이 값의 기본 크기는 아래와 같이 256k입니다.

SHOW VARIABLES LIKE '%join_buffer_size%';

그림 4

쿼리된 데이터가 너무 커서 한 번에 로드할 수 없고 데이터의 일부(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 중국어 웹사이트의 기타 관련 기사를 참조하세요!