• MySQL매뉴얼
    • MySQL 5.6 매뉴얼
    • MySQL 5.1 매뉴얼
    • MySQL 5.0 매뉴얼
    • MySQL HA 매뉴얼
  • 기술문서
    • Xtrabackup 구성
    • 메모리 사용량 모니터링
  • 라이선스
  • 온라인문의
  • 회사소개
  • → 목 록 (MySQL5.6 한글메뉴얼) [close]
  • 1. MySQL 5.6 새로운 기능
  • 2. MySQL 설치 및 업그레이드
  • 3. MySQL Tutorial
  • 4. MySQL 프로그램
  • 5. MySQL 서버관리
  • 6. 보안
  • 7. 백업 및 복구
  • 8. 최적화
  • 1. 최적화 개요
    2. SQL문 최적화
    1. SELECT문 최적화
    1. SELECT문 속도
    2. MySQL WHERE절 최적화 방법
    3. range 최적화
    4. 인덱스 병합 최적화
    5. 엔진 조건문 푸시 다운 최적화
    6. 인덱스 조건문 푸시 다운 최적화
    7. 인덱스 확장의 사용
    8. IS NULL 최적화
    9. LEFT JOIN과 RIGHT JOIN 최적화
    10. Nested Loop 조인 알고리즘
    11. 중첩 된 결합의 최적화
    12. 외부 조인의 단순화
    13. Multi-Range Read 최적화
    14. Block Nested Loop 조인과 Batched Key Access 결합
    15. ORDER BY 최적화
    16. GROUP BY 최적화
    17. DISTINCT 최적화
    18. 서브 쿼리의 최적화
    19. LIMIT 쿼리의 최적화
    20. 풀 테이블 스캔을 피하는 방법
    2. DML문 최적화
    3. 데이터베이스 권한 최적화
    4. INFORMATION_SCHEMA 쿼리 최적화
    5. 기타 최적화 Tips
    3. 최적화 및 인덱스
    4. 데이터베이스 구조의 최적화
    5. InnoDB 테이블의 최적화
    6. MyISAM 테이블의 최적화
    7. MEMORY 테이블 최적화
    8. 쿼리 실행 계획의 이해
    9. 버퍼링과 캐시
    10. 잠금 작업의 최적화
    11. MySQL 서버의 최적화
    12. 성능 측정
  • 9. Language Structure(언어구조)
  • 10. Character Sets(Globalization)
  • 11. 데이터형(Data Types)
  • 12. 함수와 연산자
  • 13. SQL 문법
  • 14. InnoDB 스토리지 엔진
  • 15. 기타 스토리지 엔진
  • 16. 고가용성 및 확장성
  • 17. 리플리케이션
  • 18. MySQL Cluster
  • 19. 파티셔닝
  • 20. Stored Programs and Views
  • 21. INFORMATION_SCHEMA
  • 22. PERFORMANCE SCHEMA
  • 23. 컨넥터 및 API
  • 24. MySQL 확장
  • 25. MySQL Enterprise Edition
  • 26. MySQL Workbench
  • 27. 제약 및 제한
  • 28. MySQL 5.7 새로운 기능

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 "전환 가능한 최적화 제어" 를 참조하십시오.

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

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 결합 알고리즘뿐만 아니라 B​​KA 결합 알고리즘은 결합 버퍼를 사용하여 병합 작업의 첫 번째 피연산자에 의해 생성 된 행의 대상 열을 누적합니다. 그런 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의 사용이 표시됩니다.


서울시 강남구 영동대로 602 6층
TEL: 02-6061-0006  /  E: csr@mysqlkorea.com
주식회사 이노클러스터  등록번호 : 727-86-02261
Copyright © innocluster Co. ltd. all rights reserved