MySQL의 조인 쿼리에 대해 간략하게 설명합니다.-MySQL 튜토리얼-php.cn

이 기사는 조인 쿼리와 관련된 문제를 주로 소개하는 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="//m.sbmmt.com/course/list/51.html" target="_blank" textvalue="mysql视频教程">mysql视频教程</a></p>

로그인 후 복사

위 내용은 MySQL의 조인 쿼리에 대해 간략하게 설명합니다.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!