MySQL5.6 Block Nested Loop 조인과 Batched Key Access 결합

8.2.1.14 Block Nested Loop 조인과 Batched Key Access 결합

MySQL 5.6에서는 결합 된 테이블과 조인 버퍼에 모두 인덱스 액세스를 사용하는 Batched Key Access (BKA) 조인 알고리즘을 사용할 수있게되었습니다. BKA 알고리즘은 중첩 된 외부 조인을 포함한 내부 조인, 외부 조인 및 준 병합 작업을 지원합니다. BKA는 테이블 스캔의 효율성 향상에 의한 결합 성능 향상이라는 장점도 있습니다. 또한 이전 내부 조인에만 사용 된 Block Nested Loop (BNL) 결합 알고리즘이 확장되어 중첩 된 외부 결합을 포함하는 외부 조인 연합 병합 작업에도 채용 할 수 있습니다.

다음 섹션에서는 원래 BNL 알고리즘의 확장의 기초에 결합 버퍼 관리, 확장 BNL 알고리즘 및 BKA 알고리즘에 대해 설명합니다. 준 결합 전략에 대해서는 " 섹션 8.2.1.18.1 '준 조인 변환에 의한 서브 쿼리의 최적화 " "를 참조하십시오.

8.2.1.14.1 Block Nested Loop 및 Batched Key Access 알고리즘의 결합 버퍼 관리

MySQL 5.6에서 MySQL Server는 내부 테이블에 인덱스 액세스없이 내부 결합뿐만 아니라 외부 조인과 서브 쿼리의 평평 뒤에 보이는 준 결합을 수행하기 위해 결합 버퍼를 사용할 수 있습니다. 또한 내부 테이블에 인덱스 액세스가있는 경우, 결합 버퍼를 효율적으로 사용할 수 있습니다.

결합 버퍼 관리 코드는 원하는 행 컬럼의 값을 저장할 때 결합 버퍼 공간을 조금 효율적으로 이용합니다. 행 컬럼의 값이 NULL 의 경우 행 열 버퍼의 추가 바이트를 할당하고, VARCHAR 형의 값은 최소 수의 바이트가 할당됩니다.

코드는 표준 및 증분의 두 가지 종류의 버퍼를 지원합니다. 결합 테이블 t1 과 t2 에 결합 버퍼 B1 이 사용되며이 작업의 결과가 결합 버퍼 B2 를 사용하여 테이블 t3 과 결합된다고합니다.

표준 결합 버퍼는 각 결합 피연산자의 컬럼이 포함됩니다. B2 가 표준 결합 버퍼 인 경우, B2 에 들어가는 행 r 은 B1 에서 행 r1 의 컬럼과 테이블 t2 에서 일치하는 행 r2 의 대상 컬럼으로 구성됩니다.
증분 결합 버퍼는 두 번째 결합 피연산자에 의해 생성 된 테이블의 행에서 컬럼 만 포함됩니다. 즉, 그것은 첫 번째 피연산자 버퍼에서 행 증가합니다. B2 가 증가 결합 버퍼 인 경우 그것에는 B1 의 행 r1 에 대한 링크와 함께 행 r2 의 대상 컬럼이 포함됩니다.

증분 결합 버퍼는 항상 이전 병합 작업에서 결합 버퍼에 상대적인 증가하므로 첫 번째 조인 조작에서 버퍼는 항상 표준 버퍼가됩니다. 이전 예에서는 테이블 t1 및 t2 를 결합하는 데 사용되는 버퍼 B1 은 표준 버퍼이어야합니다.

조인 연산에 사용되는 증분 버퍼의 각 행은 결합 된 테이블에서 행의 대상 컬럼 만 포함됩니다. 이러한 열은 첫 번째 조인 피연산자에 의해 생성 된 테이블에서 일치하는 행의 대상 컬럼에 대한 참조가 추가됩니다. 증분 버퍼의 여러 행에서 열이 이전 결합 버퍼에 저장되어있는 같은 행 r 을 볼 수 있습니다. 그러나 이러한 모든 행이 행 r 에 일치하는 경우에 한합니다.

증분 버퍼에 의해 이전 결합 작업에 사용 된 버퍼에서 열 복사의 빈도를 줄일 수 있습니다. 이는 일반적으로 첫 번째 조인 피연산자에 의해 생성 된 행이 두 번째 결합 피연산자에 의해 생성되는 여러 행과 일치 할 가능성이 있기 때문에 버퍼 공간이 절약됩니다. 첫 번째 피연산자의 행의 복사본을 여러 번 할 필요가 없습니다. 또한 증분 버퍼에 의해 복사 시간 단축을위한 처리 시간도 절약됩니다.

MySQL 5.6.3 현재 optimizer_switch 시스템 변수 block_nested_loop 및 batched_key_access 플래그는 최적화가 어떻게 Block Nested Loop 조인 알고리즘과 Batched Key Access 조인 알고리즘을 사용할지 여부를 제어합니다. 기본적으로 block_nested_loop 는 on 으로 batched_key_access 은 off 입니다. 섹션 8.8.5.2 "전환 가능한 최적화 제어" 를 참조하십시오.

MySQL 5.6.3 이전에서는 optimizer_join_cache_level 시스템 변수에 의해 결합 버퍼 관리를 제어합니다. 이 변수의 가능한 값과 그 의미 내용은 섹션 5.1.4 "서버 시스템 변수" 의 설명을 참조하십시오.

준 결합 전략에 대해서는 " 섹션 8.2.1.18.1 '준 조인 변환에 의한 서브 쿼리의 최적화 " "를 참조하십시오.

8.2.1.14.2 외부 조인 연합 결합 Block Nested Loop 알고리즘

MySQL 5.6에서는 BNL 알고리즘의 원래 구현이 외부 조인 및 준 병합 작업을 지원하도록 확장되어 있습니다.

결합 버퍼를 사용하여이 작업을 수행하면 버퍼링 된 각 행에 일치 플래그가 부가됩니다.

결합 버퍼를 사용하여 외부 조인 작업이 실행 된 경우 두 번째 피연산자에 의해 생성 된 테이블의 각 행에서 조인 버퍼의 각 행에 대한 일치가 확인됩니다. 일치하는 항목이 새로 확장 된 행이 형성되고 (원래 행에 두 번째 피연산자의 컬럼을 추가) 나머지 결합 조작에 의한 추가적인 확장을 위해 보내집니다. 또한 버퍼 내의 일치하는 행의 일치 플래그가 활성화됩니다. 결합 된 테이블의 모든 행이 조사 된 후, 결합 버퍼가 검색됩니다. 활성화 된 일치 플래그가없는 버퍼의 각 행은 NULL 의 보완 (두 번째 피연산자의 각 컬럼의 NULL 값)에 의해 확장되고 나머지 결합 조작에 의한 추가적인 확장을 위해 보내집니다.

MySQL 5.6.3 현재 optimizer_switch 시스템 변수 block_nested_loop 플래그는 최적화가 Block Nested Loop 알고리즘을 사용하는 방법을 제어합니다. 기본적으로 block_nested_loop 은 on 입니다. 섹션 8.8.5.2 "전환 가능한 최적화 제어" 를 참조하십시오.

EXPLAIN 출력에서 Extra 값 Using join buffer (Block Nested Loop) 를 포함하고 type 값이 ALL , index 또는 range 의 경우 테이블에 BNL의 사용이 표시됩니다.

준 결합 전략에 대해서는 " 섹션 8.2.1.18.1 '준 조인 변환에 의한 서브 쿼리의 최적화 " "를 참조하십시오.

8.2.1.14.3 Batched Key Access 결합

MySQL 5.6.3에서는 Batched Key Access (BKA) 결합 알고리즘라는 테이블의 결합 방법을 구현하고 있습니다. BKA는 두 번째 결합 피연산자에 의해 생성 된 테이블에 대한 인덱스 액세스가있는 경우에 적용 할 수 있습니다. BNL 결합 알고리즘뿐만 아니라 BKA 결합 알고리즘은 결합 버퍼를 사용하여 병합 작업의 첫 번째 피연산자에 의해 생성 된 행의 대상 열을 누적합니다. 그런 BKA 알고리즘은 버퍼 내의 모든 행에 조인 된 테이블에 액세스하기위한 키를 구축하고 이러한 키를 인덱스 조회를 위해 데이터베이스 엔진에 일괄 전송합니다. 키는 Multi-Range Read (MRR) 인터페이스를 통해 엔진에 전송됩니다 ( 섹션 8.2.1.13 "Multi-Range Read 최적화" 를 참조하십시오). 키를 보낸 후 MRR 엔진 기능은 최적의 방법으로 인덱스의 조회를 실행하고이 키에 의해 발견 결합 된 테이블 행을 반입하고 BKA 결합 알고리즘에 일치하는 행의 제공을 시작 합니다. 일치하는 각 줄은 조인 버퍼의 행에 대한 참조가 결합됩니다.

BKA가 사용되는 경우 join_buffer_size 값은 스토리지 엔진에 대한 개별 요청으로 키 배치의 크기가 정의됩니다. 버퍼가 클수록 결합 조작의 오른쪽 테이블에 순차적 액세스가 증가 해 성능을 크게 향상시킬 수 있습니다.

BKA을 사용하려면 optimizer_switch 시스템 변수 batched_key_access 플래그가 on 으로 설정되어 있어야합니다. BKA는 MRR을 사용하기 때문에 mrr 플래그를 on 으로 설정되어 있어야합니다. 현재 MRR 비용 견적은 매우 비관적입니다. 따라서 BKA을 사용하려면 mrr_cost_based 를 off 로해야합니다. 다음의 설정에 따라 BKA가 활성화됩니다.

 mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

MRR 함수가 실행되는 두 가지 시나리오가 있습니다.

첫 번째 시나리오는 InnoDB 와 MyISAM 같은 기존의 디스크 기반 스토리지 엔진에 사용됩니다. 이 엔진은 보통 결합 버퍼에서 모든 행의 키가 한 번에 MRR 인터페이스로 전송됩니다. 엔진 관련 MRR 함수는 전송 된 키의 인덱스 조회를 수행하고 그들로부터 행 ID (또는 기본 키)를 취득하여 BKA 알고리즘의 요청에 의해 이러한 선택된 모든 행 ID의 행을 하나씩 가져옵니다. 각 행은 결합 버퍼 내의 일치하는 행에 대한 액세스를 가능하게하는 협회 참조와 함께 반환됩니다. 행은 MRR 함수에 따라 최적의 방법으로 인출됩니다. 그들은 행 ID (기본 키) 순으로 인출됩니다. 이렇게하면 읽기가 임의의 순서가 아닌 디스크 순서로하므로 성능이 향상됩니다.
두 번째 시나리오는 NDB 등의 원격 스토리지 엔진으로 사용됩니다. 결합 버퍼에서 행의 일부 키의 패키지가 그 협회와 함께 MySQL Server (SQL 노드)을 통해 MySQL Cluster 데이터 노드로 전송됩니다. 응답에서 SQL 노드는 해당 연결이 결합 된 일치하는 행의 패키지 (또는 여러 패키지)를받습니다. BKA 결합 알고리즘은 이러한 행을 검색하고 새로 결합 된 행을 구축합니다. 그런 다음 새 키 세트가 데이터 노드에 전송되어 반환 된 패키지에서 행이 새로운 결합 된 행의 구축에 사용됩니다. 이 프로세스는 결합 버퍼에서의 마지막 키가 데이터 노드에 전송되고 SQL 노드가이 키에 일치하는 모든 행을 받아 결합 할 때까지 계속됩니다. 이렇게하면 SQL 노드가 데이터 노드로 전송되는 키를 포함한 패키지가 적을 수는 결합 작업을 수행하기 위해 그와 데이터 노드 간의 왕복이 적어진다는 것을 의미하기 때문에 성능이 향상 합니다.

첫 번째 시나리오에서는 결합 버퍼의 일부가 인덱스 조회에 의해 선택되어 MRR 함수에 매개 변수로 전달 된 행 ID (기본 키)를 저장하기 위해 예약됩니다.

결합 버퍼에서 행에 대해 생성되는 키를 저장하기위한 특별한 버퍼는 없습니다. 대신 버퍼의 다음 행의 키를 구축하는 함수가 MRR 함수에 매개 변수로 전달됩니다.

EXPLAIN 출력에서 Extra 값 Using join buffer (Batched Key Access) 를 포함하고 type 값이 ref 또는 eq_ref 의 경우 테이블에 BKA의 사용이 표시됩니다.