9장 - 옵티마이저와 힌트 - 2편

고급 최적화

옵티마이저가 실행계획을 수립할 때 통계 정보와 옵티마이저 옵션을 결합하여 최적의 실행 계획을 수립합니다.

기본적으로 deafult 값이 제공되며 on, off를 통해 옵션을 활성화, 비활성화할 수 있습니다.

 

이 옵션은 MySQL 서버 전체(Global) 또는 현재 커넥션(Session)에만 적용할 수 있습니다.

 

Multi-Range Read(MRR)

보통 조인을 수행하면 드라이빙 테이블(조인에서 먼저 읽는 테이블)의 레코드를 한 건 읽어서 드리븐 테이블(조인되는 테이블들에서 드라이빙 테이블이 아닌 테이블들)의 일치하는 레코드를 찾아서 조인을 수행합니다.

 

이런 방식은 Nested Loop Join이라고 합니다.

조인 처리는 MySQL 엔진이, 실제 레코드를 검색하고 읽는 부분은 스토리지 엔진이 담당합니다.

이때 건별로 레코드를 찾으면 스토리지 엔진에서는 최적화를 수행할 수 없기 때문에 여러개의 레코드를 읽고 조인 버퍼에 버퍼링을 수행하고 스토리지 엔진으로 한 번에 요청합니다.

 

이로써 레코드들을 데이터 페이지에 정렬된 순서로 접근하여 디스크의 페이지 읽기를 최소화합니다.

 

이 방식을 응용한 방식이 Bacted Key Access(이하 BAK) 조인이 있지만 기본적으로 비활성화되어 있습니다.

이유는 부가적인 정렬 작업을 수행하면서 오히려 성능에 안 좋은 영향을 줄 수 있습니다.

 

Block Nested Loop

대부분의 조인은 Nested Loop Join인데 조인의 연결 조건이 되는 칼럼이 모두 인덱스가 있는 경우에 Block Nested Loop Join으로 동작합니다.

 

Nested Loop Join은 2중 포문과 같이 동작합니다.

반면 Block Nested Loop Join은 조인 버퍼를 내부적으로 사용합니다.

 

조인은 드라이빙 테이블에서 일치하는 레코드의 건수만큼 드리븐 테이블을 검색하면서 처리합니다.

즉, 드라이빙 테이블은 한 번에 쭉 읽지만, 드리븐 테이블은 여러 번 읽습니다.

 

예를 들어 드라이빙 테이블의 에서 읽은 레코드가 1,000건이고, 드리븐 테이블의 조인 조건에 인덱스가 없다면 1,000번의 풀 테이블 스캔이 발생합니다.

 

이로써 쿼리는 상당히 느려집니다.

 

다만 MySQL 8.0.20 버전부터는 Block Nested Loop Join은 더 이상 사용되지 않고 Hash Join으로 대체되었습니다.

 

Index Condition Pushdown

SELECT *
FROM employees
WHERE last_name = 'Action'
AND first_name LIKE % 'sal';

last_name과 first_name에 복합 인덱스가 걸려있다고 가정해 보겠습니다.

 

위의 쿼리의 실행계획은 "Using where"으로 표기됩니다.

last_name 은 인덱스 레인지 스캔을 사용할 수 있지만 fist_name 조건은 모든 데이터를 읽은 후 사용자가 원하는 결과인지 하나씩 비교해보아야 합니다.

 

이때 last_name으로 가져온 결괏값을 토대로 first_name을 필터링할 수 있습니다.

하지만 last_name = 'Action' 조건이 10만 건이나 되는데 first_name과 일치하는 조건이 1건이라면 99,999건의 레코드 읽기가 불필요한 작업이 됩니다.

 

그러면 왜 존재하는 first_name의 칼럼을 사용하지 않고 다시 레코드를 읽는 걸까요?

테이블의 레코드에서 first_name 조건을 비교하는 작업은 MySQL 엔진이 수행합니다.

MySQL 5.5 버전까지는 따라서 first_name 조건은 MySQL 엔진이 스토리지 엔진으로 아예 전달해 주지 않았습니다.

 

MySQL 5.6부터는 인덱스의 범위 제한 조건으로 사용하지 못하더라도 인덱스의 포함된 칼럼의 조건을 스토리지 엔진으로 전달할 수 있게 핸들러 API가 개선되면서 꼭 필요한 레코드 1건에 대해서만 테이블 읽기를 수행할 수 있게 됩니다.

 

Use Index Exceptions

세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 결정하는 옵션입니다.

 

InnoDB 스토리지 엔진은 PK를 클러스터링 키로 생성합니다.

따라서 모든 세컨더리 인덱스는 리프 노드에 PK 키 값을 가집니다.

 

만약 테이블의 PK가 (dept_no, emp_no)이고 세컨더리 인덱스는 from_date 칼럼이라고 가정해 보겠습니다.

최종적으로 인덱스는 (from_date, dept_no, emp_no) 조합으로 인덱스를 생성한 것과 흡사하게 동작할 수 있습니다.

 

EXPLAIN
SELECT *
FROM dept_emp
WHERE from_date='1987-07-25'
ORDER BY dept_no;

이를 통해 정렬을 사용해 보면 Extra 칼럼에 "Using Filesort"가 표시되지 않는다는 것을 알 수 있고 별도의 정렬 작업이 없다는 것을 알 수 있습니다.

 

Index Merge

인덱스를 이용해 쿼리를 실행하는 경우, 대부분 옵티마이저는 테이블별로 하나의 인덱스만 사용하여 실행 계획을 수립합니다.

하지만 Index Merge를 활용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리합니다.

 

Index Merge에는 3가지의 최적화 옵션이 존재합니다.

 

Index Merge Intersection

2개의 칼럼에 대해 인덱스를 가지고 있고 2개의 where 조건을 가지고 어떤 조건을 활용하더라도 인덱스를 활용할 수 있습니다.

 

이렇게 되면 실행계획에 "Using intersect"라고 표시되며 쿼리가 여러 개의 인덱스를 검색하여 그 결과의 교집합만 반환했다는 것을 의미합니다.

 

물론 옵티마이저가 실행계획을 수립할 때 하나의 칼럼의 조건으로 충분히 효율적이라면 2개의 인덱스를 모두 사용하지 않는 결정을 내릴 수도 있습니다.

 

 

Index Merge Union

실행 계획에는 "Using union"이라 표시되며 OR 조건에 대해 여러 개의 인덱스를 활용하는 것을 의미합니다.

OR 작업이기 때문에 내부적으로 중복 제거가 필요하며 두 결과 집합은 모두 PK를 기준으로 정렬되어 있습니다.

따라서 PK가 중복된 레코드들을 정렬 없이 걸러낼 수 있습니다.

 

2개의 조건이 AND로 연결된 경우에는 하나라도 인덱스를 사용할 수 있어 인덱스 레인지 스캔으로 쿼리가 실행되지만 OR인 경우에는 둘 중 하나라도 제대로 인덱스를 사용하지 못하면 풀 테이블 스캔으로 처리됩니다.

 

Index Merge Sort Union

인덱스 머지 작업을 하는 도중 결과의 정렬이 필요하면 내부적으로 Sort union 알고리즘을 사용합니다.

내부적으로 정렬 후에 중복 제거가 필요한 경우에는 "Using sort_union"이라고 실행계획에 표시됩니다.

 

Semi Join

다른 테이블과 실제 조인을 수행하지 않고 다른 테이블에 조건이 일치하는 레코드가 있는지만 체크하는 형태의 쿼리를 세미 조인이라고 합니다.

 

 

서브쿼리가 57건만 읽으면 되지만 employee 테이블이 30만 건일 때 동작방식은 다음과 같습니다.

MySQL 서버는 employess 테이블을 풀 스캔 하면서 한 건 한 건 서브쿼리의 조건에 일치하는지 비교합니다.

57건만 읽으면 될 쿼리를 30만 건 넘게 읽어 처리합니다.

 

MySQL에서 제공하는 여러 가지 세미 조인 최적화 전략이 있습니다.

 

Table Pull Out

Table Pull Out 최적화는 서브쿼리에 사용된 테이블을 outer 쿼리로 끄집어낸 후 쿼리를 조인 쿼리로 재작성하는 형태의 최적화입니다.

실행계획에는 표시되지 않으며 정확하게 확인하는 방법은 EXPLAIN 명령을 실행해 본 직후 SHOW WARNINGS 명령으로 MySQL 옵티마이저가 재작성한 쿼리를 살펴볼 수 있습니다.

 

MySQL에서는 "최대한 서브쿼리를 조인으로 사용해라"라는 튜닝 가이드가 많은데 이 가이드를 MySQL에서 제공해 주는 것입니다.

 

First Match

해당 전략은 IN(subquery) 형태의 세미 조인을 EXISTS 형태로 튜닝한 것과 비슷한 방법으로 실행됩니다.

실행계획에는 "FirstMatch(e)"라는 문구가 출력됩니다.

여기서 e는 테이블 이름입니다.

 

해당 문구는 테이블의 레코드에 대해 titles에 일치하는 레코드 1건만 찾으면 더 이상 검색하지 않는다는 것을 의미합니다.

즉, EXISTS와 동일하게 처리됩니다.

 

먼저 employess 테이블에서 first_name 칼럼의 값이 'Matt'인 사원의 정보를 ix_firstname 인덱스를 이용하여 레인지 스캔으로 읽습니다.

 

이후에 사원번호를 기준으로 titles 테이블과 조인하여 from_date가 조건에 만족하는 레코드를 찾습니다.

이때 일치하는 레코드가 없다면 사용자에게 반환되는 결과는 없고, 일치하는 첫 번째 레코드를 찾으면 더 이상 titles 테이블을 검색하지 않고 즉시 결과를 반환합니다.

 

Loose Scan

dept_emp 테이블에 존재하는 모든 부서 번호에 대한 정보를 읽어오기 위한 쿼리입니다.

departments 테이블의 레코드 건수는 9개이지만 dept_emp 테이블의 레코드는 33만 건입니다.

dept_no 테이블에는 (dept_no + emp_no) 칼럼의 조합으로 PK가 만들어져 있습니다.

 

dept_no 테이블에서 유니크한 dept_no만 읽어오면 아주 효율적으로 서브쿼리를 실행할 수 있습니다.

 

"Using index; LosseScan"이라는 실행계획이 표시됩니다.

 

 

Materialization

세미 조인에 사용된 서브쿼리를 통째로 구체화해서 쿼리를 최적화합니다.

Materialization(구체화)는 내부 임시 테이블을 생성한다는 의미입니다.

 

실행계획에는 "MATERIALIZED"라고 표시되며 사용하는 테이블은 2개인데 실행 계획은 3개 라인이 출력된 것으로 어디선가 임시 테이블이 생성됐다는 것을 짐작할 수 있습니다.

 

GROUP BY절이 있어도 이 최적화 전략이 사용 가능합니다.

 

 

Duplicated Weed Out

세미 조인 서브쿼리를 일반적인 INNER JOIN 쿼리로 바꾸고 마지막에 중복된 레코드를 제거하는 방식입니다.

"Start temorary" 문구 그리고 "End temporary" 문구가 표시되면 Duplicate Weedout 최적화가 적용되었다고 이해할 수 있습니다.

 

Condition Fanout Filter

join을 수행할 때는 A 테이블과 B 테이블 중 어떤 것이 드라이빙 테이블인지에 따라 성능이 달라집니다.

MySQL 옵티마이저는 여러 테이블이 조인되는 경우 가능하다면 일치하는 레코드가 적은 순서대로 조인을 실행합니다.

 

Derived Merge

예전 버전의 MySQL 서버는 서브쿼리를 먼저 실행하여 그 결과를 임시 테이블로 만든 다음 외부 쿼리를 처리했습니다.

FROM 절에 사용된 서브쿼리를 파생 테이블(Derived Table)이라고 부릅니다.

 

이렇게 생성된 내부 테이블은 처음엔 메모리에 생성되지만 크기가 커지면 디스크로 기록됩니다.

 

MySQL 5.7 버전부터는 파생 테이블을 만드는 쿼리를 제거하는 최적화가 도입되었으며 실행 계획에 "DERIVED" 라인이 사라지게 됩니다.

 

 

Use Invisible Indexes

MySQL 8.0부터 인덱스를 사용할지 말지 제어하는 기능이 추가되었습니다.

인덱스를 삭제하지 않고도 해당 인덱스를 사용하지 못하게 할 수 있습니다.

 

 

Skip Scan

인덱스의 핵심은 정렬이 되어있다는 것이며, 이로 인해 인덱스를 구성하는 칼럼의 순서가 매우 중요합니다.

하지만 인덱스 스킵 스캔을 활용하면 제한적이지만 인덱스의 구성이 (A, B)로 되어 있더라도 A를 선행 조건으로 활용하지 않고 B만 활용하더라도 인덱스를 이용한 쿼리 성능 개선이 가능해졌습니다.

 

Hash Join

MySQL 8.0.18 버전부터 추가되었습니다.

대부분 해시 조인 기능을 사용하는 이유는 Nested Loop Join보다 빠르다고 생각하기 때문입니다.

 

하지만 해시 조인은 첫 번째 레코드를 찾는데 시간이 많이 걸리지만 최종 레코드를 찾는 데까지는 시간이 많이 걸리지 않습니다.

따라서 해시조인쿼리는 최소 스루풋 전략에 적합하여, 네스티드 루프 조인은 최고 응답 속도 전략에 적합합니다.

 

따라서 항상 해시 조인만이 정답은 아니며 적절한 전략을 활용하는 것이 좋습니다.

실행 계획을 보면 "hash join"이라는 키워드가 확인 가능합니다.

 

 

개인적인 생각

MySQL이 최적화 해주긴 하지만 세미 조인을 최대한 활용하지 말자