MySQL 고급 학습: 조인의 세 가지 알고리즘에 대한 심층적인 이해

이 글은 MySQL의 고급 연구로서 조인 연결의 원리와 조인의 세 가지 알고리즘을 자세히 소개합니다.

여러 테이블을 쿼리할 때 조인을 사용하는 경우가 많습니다. 실제로 조인의 효율성이 좋지 않으므로 사용을 피해야 합니다. MySQL은 각 테이블 간의 순환 매칭만 지원합니다. 조인 알고리즘인 Nested-Loop Join에는 실제로 조인의 실행 효율성을 향상시키는 다양한 변형 알고리즘이 있습니다. [관련 권장사항: mysql 동영상 튜토리얼]

1. Simple Nested-Loop Join(Simple Nested Loop Join)

루프의 첫 번째 테이블에서 가져온 NLJ(Simple Nested-Loop Join) 알고리즘 한 행 읽기 일관성을 위해 데이터와 일치하는 중첩 루프에 각 행을 전달합니다. 예를 들어, Driving 테이블 User와 Driven 테이블 UserInfo의 SQL은 select * from User u left Join User_info info on u.id = info.user_id입니다. 루프 의사 코드의 논리는 select * from User u left join User_info info on u.id = info.user_id，其实就是我们常用的for循环，伪代码的逻辑应该是

for(User u:Users){
    for(UserInfo info:UserInfos){
        if(u.id == info.userId){
            // 得到匹配数据
        }
    }
}

简单粗暴的算法，每次从User表中取出一条数据，然后扫描User_info中的所有记录匹配，最后合并数据返回。

假如驱动表User有10条数据，被驱动表UserInfo也有10条数据，那么实际上驱动表User会被扫描10次，而被驱动表会被扫描10*10=100次(每扫描一次驱动表，就会扫描全部的被驱动表)，这种效率是很低的，对数据库的开销比较大，尤其是被驱动表。每一次扫描其实就是从硬盘中读取数据加载到内存中,也就是一次IO，目前IO是最大的瓶颈

2. Index Nested-Loop Join(索引嵌套循环连接)

索引嵌套循环是使用索引减少扫描的次数来提高效率的，所以要求非驱动表上必须有索引才行。

在查询的时候，驱动表(User) 会根据关联字段的索引进行查询，当索引上找到符合的值，才会进行回表查询。如果非驱动表(User_info)的关联字段(user_id)是主键的话，查询效率会非常高(主键索引结构的叶子结点包含了完整的行数据(InnoDB))，如果不是主键，每次匹配到索引后都需要进行一次回表查询(根据二级索引(非主键索引)的主键ID进行回表查询)，性能肯定弱于主键的查询。

上图中的索引查询之后不一定会回表，什么情况下会回表，这个要看索引查询到的字段能不能满足查询需要的字段，具体可以参考之前的文章：你需要知道的一些索引基础知识 和 B+树的索引知识

3. Block Nested-Loop Join(缓存块嵌套循环连接)

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了，每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录 然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了IO的次数。为了减少被驱动表的IO次数，就出现了Block Nested-Loop Join的方式。

不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区，将驱动表join相关的部分数据列(大小是join buffer的限制)缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次，降低了非驱动表的访问频率。

驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况下join_buffer_size=256k,查询的时候Join Buffer 会缓存所有参与查询的列而不是只有join的列，在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段，可以让join buffer中可以存放更多的列。

可以调整join_buffer_size的缓存大小show variables like '%join_buffer%'rrreee

간단하고 조잡한 알고리즘입니다. User 테이블에서 데이터 조각을 가져올 때마다 User_info의 모든 레코드가 일치하는지 검사하고 마지막으로 데이터가 병합됩니다. 그리고 돌아왔다.

드라이버 테이블 User에 10개의 데이터가 있고, 구동 테이블 UserInfo에도 10개의 데이터가 있는 경우, 드라이버 테이블 User는 실제로 10번 스캔되고, 구동 테이블은 10*10=100번 스캔됩니다. (드라이버가 테이블을 스캔할 때마다 모든 구동 테이블이 스캔됩니다.) 이 효율성은 매우 낮으며 데이터베이스, 특히 구동 테이블의 오버헤드가 상대적으로 큽니다.

각 스캔은 실제로 하드 디스크에서 데이터를 읽고 이를 메모리에 로드하는 것이며, 이는 현재 IO가 가장 큰 병목 현상입니다🎜🎜🎜🎜🎜🎜🎜2. 인덱스 중첩 루프 조인(인덱스 중첩 루프 조인)🎜🎜🎜🎜index 중첩 루프 집합은 인덱스를 사용하여 검색 횟수를 줄여 효율성을 향상시키므로 비구동 테이블에는 인덱스가 있어야 합니다. 🎜🎜쿼리 시 드라이버 테이블(사용자)은 관련 필드의 인덱스를 기반으로 쿼리하고, 인덱스에서 일치하는 값을 찾으면 테이블 쿼리를 수행합니다. 비구동 테이블(User_info)의 관련 필드(user_id)가 기본 키인 경우 쿼리 효율성이 매우 높습니다(기본 키 인덱스 구조의 리프 노드에 완전한 행 데이터(InnoDB)가 포함되어 있음). 기본키가 아닌 경우 매번 인덱스가 일치하게 되는데 마지막으로 테이블 반환 쿼리(보조 인덱스(비기본키 인덱스)의 기본키 ID를 기반으로 한 테이블 반환 쿼리)가 필요하며 성능은 확실히 약하다. 기본 키 쿼리보다 🎜🎜🎜🎜위로 그림의 인덱스 쿼리는 어떤 상황에서 테이블을 반환하지 않을 수 있습니다. 이는 인덱스에서 쿼리한 필드가 쿼리에서 요구하는 필드를 충족할 수 있는지 여부에 따라 다릅니다. : 🎜알아야 할 인덱스 기본 사항 B+ 트리에 대한 지식 및 인덱스 지식 🎜🎜🎜🎜🎜3. Block Nested-Loop Join (캐시 블록 중첩 루프 연결) 🎜🎜🎜🎜인덱스가 있으면 인덱스 방법은 조인 열에 인덱스가 없으면 구동 테이블에 액세스할 때마다 테이블의 레코드가 메모리에 로드된 다음 레코드를 너무 많이 스캔해야 합니다. 일치를 위해 드라이버 테이블에서 가져와 메모리를 비운 다음 드라이버 테이블에서 레코드를 로드한 다음 일치를 위해 구동되는 테이블의 레코드를 메모리에 로드하는 과정이 반복됩니다. 다시 말하면 IO 수가 크게 늘어납니다. 구동 테이블의 IO 수를 줄이기 위해 Block Nested-Loop Join 방법이 등장했습니다. 🎜🎜더 이상 드라이버 테이블의 데이터를 하나씩 가져오는 것이 아니라 하나씩 가져옵니다. 드라이버 테이블의 조인과 관련된 일부 데이터 열을 캐시하기 위해 조인 버퍼가 도입되었습니다. (크기는 조인의 한계입니다.) 버퍼)를 조인 버퍼에 넣은 다음 구동 테이블의 전체 테이블 스캔, 구동 테이블의 각 레코드를 조인 버퍼의 모든 구동 테이블 레코드와 동시에 일치(메모리 내 작업), 간단한 중첩 루프에서 다중 비교 드라이버 테이블에 액세스하는 빈도를 줄여 비용을 절감합니다. 🎜🎜🎜🎜드라이버 테이블을 한 번에 로드할 수 있는지 여부는 조인 버퍼가 모든 데이터를 저장할 수 있는지 여부에 따라 다릅니다. 기본적으로 join_buffer_size=256k, 쿼리 시 조인 버퍼는 대신 쿼리에 참여하는 모든 열을 캐시합니다. N개의 조인 연관이 있는 SQL에서는 조인 열만 N-1개의 조인 버퍼가 할당됩니다. 따라서 쿼리 시 불필요한 필드를 줄여서 조인 버퍼에 더 많은 컬럼을 저장할 수 있도록 노력하세요. 🎜🎜join_buffer_size '%join_buffer%'와 같은 변수 표시의 캐시 크기를 조정할 수 있습니다. 이 값은 실제 상황에 따라 변경될 수 있습니다. 🎜🎜🎜🎜

Block Nested-Loop Join 알고리즘을 사용하려면 기본적으로 켜져 있는 옵티마이저 관리 구성 block_nested_loop의 Optimizer_switch 설정을 on으로 켜야 합니다. show variables like '%optimizer_switch%' 查看block_nested_loop 상태를 통해 이용 가능합니다.

위의 세 가지 알고리즘을 이해하면 실제로 실제 작업에서는 인덱스를 잘 활용할 수 있다면 좋을 것입니다. 그렇지 않으면 쿼리 효율성을 제공하기 위해 인덱스를 잘 사용해야 합니다.

원본 주소: https://juejin.cn/post/7014105037517357093

저자: Mr. Ji

프로그래밍 관련 지식을 더 알고 싶으시면 프로그래밍 입문을 방문해 주세요! !

위 내용은 MySQL 고급 학습: 조인의 세 가지 알고리즘에 대한 심층적인 이해의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!