본문 바로가기
Database/MYSQL

MySQL Block Nested Loop (BNL)에 대한 이해

by 반화넬 2018. 6. 20.
반응형

이번 시간에 살펴볼 내용은 Block Nested-Loop (이하 BNL)입니다.

 

MySQL이 BNL을 제공하는 이유는 Nested Loop 조인만 지원하는 한계점을 보완하기 위해서입니다. 예를 들어, 조인 칼럼 인덱스가 없다고 가정해보죠. 이 경우, ORACLE과 PostgreSQL과 같은 DBMS는 해시 조인 또는 머지 조인을 사용합니다. 그런데 Nested Loop 조인만 지원하는 MySQL은 이 문제를 어떻게 처리할까요?

 

만일 BNL 방식이 없다면, Driving 테이블의 건수만큼 Inner 테이블을 스캔 (또는 Index Full Scan)해야만 합니다. 이것은 어마어마한 성능 저하를 초래합니다. 이 문제를 어느 정도 완화하기 위해서 MySQL은 BNL 방식을 고안했습니다.

 

BNL 방식은 프로세스 내에 별도의 버퍼 (이를 조인 버퍼라고 합니다)에 Driving 테이블의 레코드를 저장한 후에 Inner 테이블을 스캔하면서 조인 버퍼를 탐색하는 방식입니다. 따라서 BNL 방식을 사용하면 BNL 방식을 사용하지 않는 것에 비해서는 빠릅니다. 하지만 BNL 방식은 Nested Loop 조인의 한계를 개선하기 위한 차선책일 뿐 근본적인 해결책은 아닙니다. Sort Merge 조인이나 Hash 조인에 비해서 턱없이 느린 방식이기 때문입니다. 이 때문에 MySQL에서도 가능한 빨리 Sort Merge나 Hash 조인을 지원하면 좋겠지만, MySQL 8에서도 아직 지원 예정은 없는 것 같습니다.

 

그럼 예제를 통해 살펴보겠습니다. 참고로, Sort Merge, Hash 조인의 성능을 간접적으로 비교하기 위해서 PostgreSQL과 ORACLE에서 테스트한 결과도 첨부합니다.


Extra 칼럼에 “Using Join buffer (Block Nested Loop)“가 표시되면 BNL 방식으로 수행된 것입니다. BNL 방식은 조인 칼럼에 적절한 인덱스가 없을 때 수행될 수 있는 방식이므로, BNL 방식으로 수행될 때의 type은 ALL (Table Full Scan), index (Index Full Scan) 또는 range 입니다.



만약 BNL를 사용 못하게 할려면 설정을 변경해야한다.


## Switchable Optimizations 항목

mysql> SELECT @@optimizer_switch\G

index_merge=on,index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on

, engine_condition_pushdown=on, index_condition_pushdown=on, mrr=on,mrr_cost_based=on, block_nested_loop=on

,batched_key_access=off, materialization=on,semijoin=on,loosescan=on, firstmatch=on

, subquery_materialization_cost_based=on, use_index_extensions=on



# 해당 기능 해제 :데몬 재구동시 초기화 됨

SET GLOBAL optimizer_switch='block_nested_loop=off';
SET SESSION optimizer_switch='block_nested_loop=off';


# 설정파일 my.cnf 추가시

[mysqld]
optimizer_switch=block_nested_loop=off

 ###### 참고 설명 ######
다음 목록은 최적화 전략별로 그룹화 된 허용 가능한 opt_name 플래그 이름을 설명합니다.

  • Batched Key Access Flags 일괄 처리 된 키 액세스 플래그

    • batched_key_access (default off ) batched_key_access (기본 off )

      Controls use of BKA join algorithm. BKA 조인 알고리즘의 사용을 제어합니다.

    For batched_key_access to have any effect when set to on , the mrr flag must also be on . on 설정했을 때 batched_key_access 가 효과를 가지려면 mrr 플래그도 on 있어야합니다. Currently, the cost estimation for MRR is too pessimistic. 현재 MRR에 대한 비용 추정은 너무 비관적입니다. Hence, it is also necessary for mrr_cost_based to be off for BKA to be used. 따라서 BKA를 사용하려면 mrr_cost_based 가 off 있어야합니다.

    For more information, see Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins” . 자세한 내용 은 8.2.1.11, "중첩 루프 및 일괄 키 액세스 조인 차단"을 참조하십시오.

  • Block Nested-Loop Flags 중첩 루프 플래그 차단

    • block_nested_loop (default on ) block_nested_loop (기본 on )

      Controls use of BNL join algorithm. BNL 조인 알고리즘 사용을 제어합니다.

    For more information, see Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins” . 자세한 내용 은 8.2.1.11, "중첩 루프 및 일괄 키 액세스 조인 차단"을 참조하십시오.

  • Engine Condition Pushdown Flags 엔진 상태 푸시 다운 플래그

    • engine_condition_pushdown (default on ) engine_condition_pushdown (기본 on )

      Controls engine condition pushdown. 엔진 상태 푸시 다운을 제어합니다.

    For more information, see Section 8.2.1.4, “Engine Condition Pushdown Optimization” . 자세한 내용 은 8.2.1.4 절. "엔진 조건 푸시 다운 최적화"를 참조하십시오.

  • Index Condition Pushdown Flags 인덱스 조건 푸시 다운 플래그

    • index_condition_pushdown (default on ) index_condition_pushdown (기본 on )

      Controls index condition pushdown. 인덱스 조건 푸시 다운을 제어합니다.

    For more information, see Section 8.2.1.5, “Index Condition Pushdown Optimization” . 자세한 내용 은 8.2.1.5 절. "색인 조건 푸시 다운 최적화"를 참조하십시오.

  • Index Extensions Flags 색인 확장 플래그

    • use_index_extensions (default on ) use_index_extensions (기본 on )

      Controls use of index extensions. 색인 확장의 사용을 제어합니다.

    For more information, see Section 8.3.9, “Use of Index Extensions” . 자세한 내용 은 8.3.9 절 . "색인 확장 사용"을 참조하십시오.

  • Index Merge Flags 인덱스 병합 플래그

    • index_merge (default on ) index_merge (기본 on )

      Controls all Index Merge optimizations. 모든 인덱스 병합 최적화를 제어합니다.

    • index_merge_intersection (default on ) index_merge_intersection (기본 on )

      Controls the Index Merge Intersection Access optimization. 인덱스 병합 교차 액세스 최적화를 제어합니다.

    • index_merge_sort_union (default on ) index_merge_sort_union (기본 on )

      Controls the Index Merge Sort-Union Access optimization. 인덱스 병합 정렬 유니온 액세스 최적화를 제어합니다.

    • index_merge_union (default on ) index_merge_union (기본 on )

      Controls the Index Merge Union Access optimization. 통합 색인 통합 병합을 제어합니다.

    For more information, see Section 8.2.1.3, “Index Merge Optimization” . 자세한 내용 은 8.2.1.3 절. "인덱스 병합 최적화"를 참조하십시오.

  • Multi-Range Read Flags 다중 범위 읽기 플래그

    • mrr (default on ) mrr (기본 on )

      Controls the Multi-Range Read strategy. 다중 범위 읽기 전략을 제어합니다.

    • mrr_cost_based (default on ) mrr_cost_based (기본 on )

      Controls use of cost-based MRR if mrr=on . mrr=on 이면 비용 기반 MRR 사용을 제어합니다.

    For more information, see Section 8.2.1.10, “Multi-Range Read Optimization” . 자세한 내용 은 8.2.1.10 절. "다중 범위 읽기 최적화"를 참조하십시오.

  • Semi-Join Flags 반 결합 플래그

    • semijoin (default on ) semijoin (기본 on )

      Controls all semi-join strategies. 모든 semi-join 전략을 제어합니다.

    • firstmatch (default on ) firstmatch (기본 on )

      Controls the semi-join FirstMatch strategy. 세미 조인 FirstMatch 전략을 제어합니다.

    • loosescan (default on ) loosescan (기본값)

      Controls the semi-join LooseScan strategy (not to be confused with LooseScan for GROUP BY ). semi-join LooseScan 전략을 제어합니다 ( GROUP BY LooseScan과 혼동하지 마십시오).

    The semijoin flag controls whether semi-joins are used. semijoin 플래그는 세미 조인의 사용 여부를 제어합니다. If it is set to on , the firstmatch and loosescan flags enable finer control over the permitted semi-join strategies. on 으로 설정된 경우 firstmatch 및 loosescan 플래그를 사용하여 허용 된 semi-join 전략을보다 세밀하게 제어 할 수 있습니다.

    If semijoin and materialization are both on , semi-joins also use materialization where applicable. 세미 semijoin 과 materialization 가 모두 실행 on 경우 세미 조인은 적용 가능한 경우 구체화를 사용합니다. These flags are on by default. 이 플래그는 기본적으로 설정됩니다.

    For more information, see Section 8.2.2.1, “Optimizing Subqueries with Semi-Join Transformations” . 자세한 내용 은 8.2.2.1, "Semi-Join Transformations로 서브 쿼리 최적화하기" 에서 참조하십시오.

  • Subquery Materialization Flags 하위 쿼리 구체화 플래그

    • materialization (default on ) materialization (기본 on )

      Controls materialization (including semi-join materialization). materialization을 제어합니다 (semi-join materialization 포함).

    • subquery_materialization_cost_based (default on ) subquery_materialization_cost_based (기본 on )

      Use cost-based materialization choice. 비용 기반 구체화 선택 사용.

    The materialization flag controls whether subquery materialization is used. materialization 플래그는 하위 쿼리 구체화를 사용할지 여부를 제어합니다. If semijoin and materialization are both on , semi-joins also use materialization where applicable. 세미 semijoin 과 materialization 가 모두 실행 on 경우 세미 조인은 적용 가능한 경우 구체화를 사용합니다. These flags are on by default. 이 플래그는 기본적으로 설정됩니다.

    The subquery_materialization_cost_based flag enables control over the choice between subquery materialization and IN -to- EXISTS subquery transformation. subquery_materialization_cost_based 플래그를 사용하면 하위 쿼리 구체화와 IN - EXISTS 하위 쿼리 변환 중에서 선택을 제어 할 수 있습니다. If the flag is on (the default), the optimizer performs a cost-based choice between subquery materialization and IN -to- EXISTS subquery transformation if either method could be used. 플래그가 설정된 경우 (기본값) 옵티마이 저는 두 가지 방법 중 하나를 사용할 수있는 경우 하위 쿼리 구체화와 IN - EXISTS 하위 쿼리 변환간에 비용 기반 선택을 수행합니다. If the flag is off , the optimizer chooses subquery materialization over IN -to- EXISTS subquery transformation. 플래그가 off 있으면 최적화 프로그램은 IN - EXISTS 하위 쿼리 변환보다 하위 쿼리 구체화를 선택합니다. 
     



# 참고 사이트

http://blog.naver.com/PostView.nhn?blogId=parkjy76&logNo=221069454499&categoryNo=14&parentCategoryNo=0&viewDate=¤tPage=1&postListTopCurrentPage=1&from=postView


https://dev.mysql.com/doc/refman/5.6/en/bnl-bka-optimization.html

반응형

관련글

티스토리툴바