MySQL의 조인 기능이 너무 약한가요?
- coldplay.xixi앞으로
- 2020-11-12 17:23:511995검색
오늘의 mysql tutorial 칼럼에서는 Join 기능을 소개합니다.
MySQL의 조인에 관해 많은 "일화"를 알고 계셨을 것입니다. 예를 들어, 2개의 테이블 조인은 큰 테이블을 구동하기 위해 작은 테이블이 필요하며, Alibaba 개발자 사양에서는 3개 이상의 테이블에 대한 조인 작업을 금지합니다. .MySQL의 조인 기능이 너무 약하다는 등의 문제가 있습니다. 이러한 규범이나 설명은 사실일 수도 있고 거짓일 수도 있으며, 때로는 옳을 수도 있고 때로는 틀릴 수도 있습니다. 명확하게 이해하려면 조인에 대한 심층적인 이해가 필요합니다.
이제 MySQL의 조인 작업을 전체적으로 살펴보겠습니다.
Text
일별 데이터베이스 쿼리에서는 여러 테이블의 병합된 데이터를 한 번에 얻기 위해 여러 테이블에 대해 조인 작업을 수행해야 하는 경우가 많습니다. 이를 위해서는 데이터베이스의 조인 구문을 사용해야 합니다. 조인은 데이터 필드에서 두 개의 데이터 세트를 병합하는 매우 일반적인 작업입니다. 이에 대해 더 자세히 알면 MySQL, Oracle, PostgreSQL 및 Spark가 모두 이 작업을 지원한다는 것을 알 수 있습니다. 본 글의 주인공은 MySQL이다. 아래에 특별한 설명이 없으면 MySQL의 조인을 주요 주제로 삼는다. Oracle, PostgreSQL 및 Spark는 MySQL보다 알고리즘 최적화 및 조인 구현이 더 뛰어납니다.
MySQL의 조인에는 많은 규칙이 있습니다. 주의하지 않으면 잘못된 조인 문으로 인해 특정 테이블의 전체 테이블 쿼리가 발생할 수 있을 뿐만 아니라 데이터베이스의 캐시에 영향을 주어 대부분의 데이터가 핫스팟을 일으킬 수 있습니다. 교체되어 전체 데이터베이스 성능이 저하됩니다.
그래서 업계에서는 작은 테이블이 큰 테이블을 구동하고 3개 이상의 테이블에 대한 조인 작업을 금지하는 등 MySQL 조인에 대한 많은 규범이나 원칙을 요약했습니다. 아래에서는 MySQL 조인 알고리즘을 차례로 소개하고, 이를 Oracle 및 Spark의 조인 구현과 비교하며, 위의 사양이나 원칙이 왜 형성되는지에 대한 답변을 정리해 보겠습니다.조인 연산을 구현하는 데에는 아마도 세 가지 일반적인 알고리즘이 있을 것입니다: Nested Loop Join, Hash Join 및 Sort Merge Join은 각각 고유한 장점과 단점 및 적용 가능한 조건을 가지고 있으며 이를 차례로 소개합니다. 다음.
MySQL에서 중첩 루프 조인 구현중첩 루프 조인은 레코드를 읽을 때마다 조인 관련 필드의 인덱스에 따라 구동 테이블에서 해당 데이터를 쿼리합니다. 연결될 데이터의 하위 집합이 작은 시나리오에 적합하며, MySQL 조인의 유일한 알고리즘 구현이기도 합니다. 자세한 내용은 다음에 설명하겠습니다. MySQL에는 Nested Loop Join 알고리즘의 두 가지 변형, 즉 Index Nested-Loop Join과 Block Nested-Loop Join이 있습니다. Index Nested-Loop Join 알고리즘다음으로 해당 테이블 구조와 데이터를 초기화해 보겠습니다CREATE TABLE `t1` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`) ) ENGINE=InnoDB; delimiter ;; # 定义存储过程来初始化t1 create procedure init_data() begin declare i int; set i=1; while(i위 명령을 보면 두 테이블 모두 기본 키 인덱스 id와 인덱스 a가 있고 필드가 없음을 알 수 있습니다. b 색인. 저장 프로시저 init_data는 테이블 t1에 10,000행의 데이터를 삽입하고 테이블 t2에 500행의 데이터를 삽입합니다. <p></p>MySQL 옵티마이저가 해당 테이블을 구동 테이블로 선택하여 SQL 문 분석 실행 프로세스에 영향을 미치는 것을 방지하기 위해 직접적으로 Straight_join을 사용하여 MySQL이 쿼리에 고정된 연결 테이블 순서를 사용하도록 합니다. 다음 문에서 t1은 다음과 같습니다. 구동 테이블이고 t2는 구동 테이블입니다. <p></p><pre class="brush:php;toolbar:false">select * from t2 straight_join t1 on (t2.a=t1.a);复制代码이 문장의 실행 계획을 보려면 이전 기사에서 소개한 explain 명령어를 사용하세요.
- t2 테이블 데이터 L1에서 행을 읽습니다.
- L1의 a 필드를 사용하여 t1 테이블에서 조건으로 쿼리합니다.
- t1의 조건을 충족하는 행을 꺼내서 L1과 일치하는 행을 구성합니다.
- Complete t2 테이블을 스캔할 때까지 실행을 반복합니다.
2log2M입니다. 분명히 N은 스캔되는 행 수에 더 큰 영향을 미치므로 이 경우 작은 테이블을 구동 테이블로 사용해야 합니다.
물론, 이 모든 것의 전제는 조인의 관련 필드가 a이고 t1 테이블의 a 필드에 인덱스가 있다는 것입니다.如果没有索引时，再用上图的执行流程时，每次到 t1 去匹配的时候，就要做一次全表扫描。这也导致整个过程的时间复杂度编程了 N * M，这是不可接受的。所以，当没有索引时，MySQL 使用 Block Nested-Loop Join 算法。
Block Nested-Loop Join
Block Nested-Loop Join的算法，简称 MySQL의 조인 기능이 너무 약한가요?，它是 MySQL 在被驱动表上无可用索引时使用的 join 算法，其具体流程如下所示：
- 把表 t2 的数据读取当前线程的 join_buffer 中，在本篇文章的示例 SQL 没有在 t2 上做任何条件过滤，所以就是讲 t2 整张表 放入内存中；
- 扫描表 t1，每取出一行数据，就跟 join_buffer 中的数据进行对比，满足 join 条件的，则放入结果集。
比如下面这条 SQL
select * from t2 straight_join t1 on (t2.b=t1.b);复制代码
这条语句的 explain 结果如下所示。可以看出
可以看出，这次 join 过程对 t1 和 t2 都做了一次全表扫描，并且将表 t2 中的 500 条数据全部放入内存 join_buffer 中，并且对于表 t1 中的每一行数据，都要去 join_buffer 中遍历一遍，都要做 500 次对比，所以一共要进行 500 * 10000 次内存对比操作，具体流程如下图所示。
主要注意的是，第一步中，并不是将表 t2 中的所有数据都放入 join_buffer，而是根据具体的 SQL 语句，而放入不同行的数据和不同的字段。比如下面这条 join 语句则只会将表 t2 中符合 b >= 100 的数据的 b 字段存入 join_buffer。
select t2.b,t1.b from t2 straight_join t1 on (t2.b=t1.b) where t2.b >= 100;复制代码
join_buffer 并不是无限大的，由 join_buffer_size 控制，默认值为 256K。当要存入的数据过大时，就只有分段存储了，整个执行过程就变成了：
- 扫描表 t2，将符合条件的数据行存入 join_buffer，因为其大小有限，存到100行时满了，则执行第二步；
- 扫描表 t1，每取出一行数据，就跟 join_buffer 中的数据进行对比，满足 join 条件的，则放入结果集；
- 清空 join_buffer；
- 再次执行第一步，直到全部数据被扫描完，由于 t2 表中有 500行数据，所以一共重复了 5次
这个流程体现了该算法名称中 Block 的由来，分块去执行 join 操作。因为表 t2 的数据被分成了 5 次存入 join_buffer，导致表 t1 要被全表扫描 5次。
| 全部存入 | 分5次存入 | |
|---|---|---|
| 内存操作 | 10000 * 500 | 10000 * (100 + 100 + 100 + 100 + 100) |
| 扫描行数 | 10000 + 500 | 10000 * 5 + 500 |
위와 같이 Join_buffer에 모두 저장할 수 있는 테이블 데이터와 비교해 보면 메모리 판단 횟수는 두 테이블의 행 번호를 곱한 10000*500입니다. Driven 테이블은 여러 번 스캔되며, 매번 한 번 더 저장하면 Driven 테이블을 다시 스캔해야 하므로 최종 실행 효율성에 영향을 미칩니다.
위의 두 가지 알고리즘을 바탕으로 다음과 같은 결론을 내릴 수 있는데, 이는 인터넷에 있는 대부분의 MySQL 조인 문의 표준이기도 합니다.
Driven 테이블에 인덱스가 있습니다. 즉, Index Nested-Loop Join 알고리즘을 사용할 수 있는 경우 조인 연산을 사용할 수 있습니다.
Index Nested-Loop Join 알고리즘이든 Block Nested-Loop Join 알고리즘이든 작은 테이블을 구동 테이블로 사용해야 합니다.
위의 두 조인 알고리즘의 시간 복잡도는 적어도 관련된 테이블의 행 수와 1차 관계에 있고 메모리 공간도 많이 차지하므로 Alibaba 개발자 사양에서는 세 개의 테이블을 엄격히 금지합니다. 위의 조인 작업도 이해할 수 있습니다.
그러나 위의 두 알고리즘은 조인 알고리즘 중 하나일 뿐이며 Hash Join 및 Sorted Merged Join과 같은 보다 효율적인 조인 알고리즘도 있습니다. 안타깝게도 이 두 가지 알고리즘은 현재 주류 버전의 MySQL에서는 제공되지 않지만 Oracle, PostgreSQL, Spark는 모두 이를 지원합니다. 이것이 MySQL에 대한 온라인 불만이 매우 약한 이유입니다(MySQL 버전 8.0은 Hash 조인을 지원하지만 8.0은 아직 지원하지 않습니다.) 주류 버전).
실제로 알리바바 개발자 사양에도 Oracle에서 MySQL로 마이그레이션할 때 MySQL의 조인 연산 성능이 너무 나빠서 3개 이상의 테이블에 대한 조인 연산을 금지한다고 규정하기도 했습니다.
해시 조인 알고리즘
해시 조인은 드라이버 테이블을 스캔하고 조인의 관련 필드를 사용하여 메모리에 해시 테이블을 생성한 다음 구동 테이블을 스캔하여 데이터의 각 행을 읽고 테이블에서 해당 데이터를 찾습니다. 해시 테이블. 대규모 데이터 세트 연결 작업을 위한 일반적인 방법으로, 테이블에서 구동되는 데이터의 양이 적고 메모리에 배치될 수 있는 시나리오에 적합합니다. 인덱스가 없는 대규모 테이블 및 병렬에 최고의 성능을 제공할 수 있습니다. 쿼리. 불행하게도 이는 a.id = where b.a_id와 같은 동등 조인 시나리오에만 적용됩니다.
여전히 위 두 테이블의 조인 문입니다. 실행 과정은 다음과 같습니다.
- 드라이버 테이블 t2에서 정규화된 데이터를 꺼내고, 각 행의 조인 필드 값에 대해 해시 연산을 수행합니다. 그런 다음 해시 테이블에 저장합니다.
- 구동 테이블 t1을 탐색하고 조건에 맞는 데이터 행을 꺼낼 때마다 조인 필드 값도 해시되어 결과가 검색됩니다. 메모리의 해시 테이블에서 일치하는 항목이 발견되면 결과 집합의 일부가 됩니다.
이 알고리즘은 순서가 지정되지 않은 조인 버퍼가 해시 테이블 해시 테이블로 변경되어 데이터 매칭에 더 이상 조인 버퍼의 데이터가 필요하지 않다는 점을 제외하면 Block Nested-Loop Join과 유사하다는 것을 알 수 있습니다. 모든 것을 한 번 탐색하면 해싱을 직접 사용하여 O(1)에 가까운 시간 복잡도로 일치하는 행을 얻을 수 있습니다. 이렇게 하면 두 테이블을 결합하는 속도가 크게 향상됩니다. 그러나 해시의 특성상 이 알고리즘은 동등한 연결 시나리오에만 적용될 수 있으며, 이 알고리즘은 다른 연결 시나리오에서는 사용할 수 없습니다.
Sort Merge Join 알고리즘
Sort Merge Join은 먼저 조인의 관련 필드에 따라 두 테이블을 정렬합니다. (필드에 인덱스가 있는 경우와 같이 이미 정렬된 경우 다시 정렬할 필요가 없습니다.) , 두 테이블이 정렬됩니다. 두 테이블이 정렬된 경우 정렬 병합 조인을 수행할 때 다시 정렬할 필요가 없습니다. 이 경우 Merge Join이 Hash Join보다 성능이 좋습니다. 병합 조인은 비동등 조인(>, =, )에 적용할 수 있습니다.
연결된 필드에 이미 인덱스가 있는 경우, 즉 정렬된 경우 병합 작업을 직접 수행할 수 있지만 연결된 필드에 인덱스가 없는 경우 실행 프로세스는 다음과 같습니다. 아래 그림에 나와 있습니다.
- 테이블 t1을 트래버스하여 조건에 맞는 데이터를 읽어서 정렬합니다. 연결 필드의 값에 따라 정렬
- 두 개의 정렬된 데이터를 병합하여 결과 세트를 얻습니다.
- Sorted Merge Join 알고리즘의 주요 시간 소모는 두 테이블의 정렬 작업이므로 두 테이블을 연결 필드에 따라 정렬했다면 이 알고리즘은 Hash Join 알고리즘보다 훨씬 빠릅니다. 어떤 경우에는 이 알고리즘이 Nested Loop Join 알고리즘보다 빠릅니다.
이제 위 세 가지 알고리즘의 차이점과 장점, 단점을 요약해 보겠습니다.
| Nested Loop Join | Hash Join | Sorted Merge Join | |
|---|---|---|---|
| 모든 조건에 적용 가능 | Equivalent Join(=)에만 적용 가능 | (>, =, ' 제외 | |
| CPU, 디스크 I/O | 메모리, 임시 공간 | 메모리, 임시 공간 | |
| 선택도가 높은 인덱스나 제한적인 검색이 있을 때 더 효율적이며 첫 번째 검색 결과를 빠르게 반환할 수 있습니다 | 인덱스가 부족하거나 인덱스 조건이 모호할 때 Hash Join보다 효과적입니다. 중첩 루프. 일반적으로 Merge Join보다 빠릅니다. 데이터 웨어하우스 환경에서는 테이블에 레코드 수가 많으면 효율성이 높습니다 | 인덱스가 부족하거나 인덱스 조건이 모호한 경우에는 Nested Loop보다 Sort Merge Join이 더 효과적입니다. 연결 필드에 인덱스가 있거나 미리 정렬되어 있는 경우 해시 조인보다 빠르며 더 많은 연결 조건을 지원합니다 | |
| 인덱스가 없거나 테이블 레코드가 많을 경우 비효율적입니다 | 해시 구축 테이블은 많은 메모리를 필요로 하며, 1장 결과를 한 번 반환하는 것은 더 느립니다 | 모든 테이블을 정렬해야 합니다. 최적의 처리량을 위해 설계되었으며 모든 결과가 발견될 때까지 데이터를 반환하지 않습니다 | |
| 예(인덱싱을 사용하지 않는 것은 너무 비효율적입니다) | No | 아니요 |
이후 조인 관련 알고리즘에 대해 이야기하면서 조인 작업에 대한 비즈니스 이해에 대해서도 이야기해 보겠습니다.
비즈니스가 복잡하지 않은 경우 대부분의 조인은 대체 불가능하지 않습니다. 예를 들어, 주문 기록에는 일반적으로 주문 사용자의 user_id만 포함됩니다. 정보를 반환할 때 가능한 구현 솔루션은 다음과 같습니다.
하나의 데이터베이스 작업, 조인 작업을 사용하여 테이블과 사용자 테이블은 사용자 이름과 함께 결합됩니다. 함께 반환- 두 개의 데이터베이스 작업, 두 개의 쿼리, 처음에는 주문 정보와 user_id를 가져오고 두 번째는 user_id를 기반으로 이름을 가져옵니다. 정보를 병합하는 프로그램
- 중복된 사용자 이름을 사용하거나 ES 등에서 비관계형 데이터베이스에서 읽습니다.
- 위의 솔루션은 모두 데이터 집계 문제를 해결할 수 있으며 프로그램 코드를 기반으로 처리되므로 데이터베이스 조인보다 디버깅 및 최적화가 더 쉽습니다. 예를 들어 사용자 이름은 데이터베이스에서 검색되지 않지만 먼저 검색됩니다. 캐시에서 검색했습니다.
물론 조인 작업에는 장점이 있으므로 기술에는 사용 시나리오가 있습니다. 위의 솔루션이나 규칙은 인터넷 개발 팀에서 요약한 것이며 높은 동시성, 가벼운 쓰기 및 대량 읽기, 배포 및 단순성에 적합합니다. 비즈니스 논리 이러한 시나리오는 일반적으로 데이터 일관성에 대한 요구 사항이 높지 않으며 더티 읽기도 허용됩니다.
그러나 금융 뱅킹이나 금융과 같은 엔터프라이즈 애플리케이션 시나리오에서는 조인 작업이 필수적입니다. 이러한 애플리케이션은 일반적으로 동시성이 낮고 복잡한 데이터 쓰기가 빈번하며 IO 집약적보다는 CPU 집약적이며 주요 비즈니스 로직은 다음을 통해 처리됩니다. 데이터베이스에는 많은 수의 저장 프로시저가 포함되어 있고 일관성과 무결성에 대한 요구 사항이 높은 시스템도 포함됩니다.
더 많은 관련 무료 학습 권장사항:mysql 튜토리얼(동영상)
위 내용은 MySQL의 조인 기능이 너무 약한가요?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!