MySQL – Multi Range Read (MRR)와 관련된 매뉴얼 Bug 및 소스 디버깅을 통한 검증

MySQL의 Multi Range Read (MRR)를 연구하던 중에 이해할 수 없는 내용을 매뉴얼에서 접했습니다. 몇 가지 Test와 Source Debugging을 통해, 아래 내용은 Manual Bug로 자체 판단했습니다. 자세한 내용은 여기를 참고해 주세요.

Without MRR, an index scan covers all index tuples for the key_part1 range from 1000 up to 2000, regardless of the key_part2 value in these tuples. The scan does extra work to the extent that tuples in the range contain key_part2 values other than 10000.

With MRR, the scan is broken up into multiple ranges, each for a single value of key_part1 (1000, 1001, … , 1999). Each of these scans need look only for tuples with key_part2 = 10000. If the index contains many tuples for which key_part2 is not 10000, MRR results in many fewer index tuples being read.

 

Derived Table을 이용해서 NL 조인 시에 발생하는 Block Nested-Loop (BNL)를 튜닝하는 방법

이번 시간에 살펴볼 내용은 Derived Table을 이용해서 Nested Loop 조인 시에 발생하는 BNL에 대한 튜닝 방법입니다. 이 내용은 이전 포스트인 “MySQL – Block Nested-Loop (BNL)에 대한 이해 “와 연결된 내용입니다.

이번 포스팅에서 다룰 내용이 길지 않음에도 별도의 포스트로 분리한 이유는, 나름 참신한 방법으로 Nested Loop 시에 발생하는 BNL을 튜닝할 수 있는 방법이기 때문입니다. 결론부터 말하면, Derived Table의 특징을 이용하면 인덱스를 생성하지 않고도 BNL에 대한 성능 튜닝이 가능합니다. 왜냐면 TEMPTABLE 알고리즘을 이용하는 Derived Table은 조인 칼럼에 대해서 내부적으로 임시 인덱스를 생성하기 때문입니다. 이 원리를 이용하면 매우 극적인 튜닝이 가능합니다. 예제를 통해 살펴보겠습니다.

(more…)

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

이번 시간에 살펴볼 내용은 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에서 테스트한 결과도 첨부합니다.

(more…)