에 대한 내용의 실행 계획과 어떤 알고리즘(ex: 합집합 → 우선순위 큐)을 사용했는지 알게 됨.
세미 조인에 대한 내용을 알게 됨.
다른 테이블에 조건이 일치하는지만 판단함 (실제 조인은 X)
세미 조인, 안티 세미 조인에 따라 최적화 형태가 다름
8.0 버전부터 세미 조인 쿼리 최적화를 위한 전략들이 있음.
메모
인덱스 머지(index_merge)
인덱스를 이용하여 쿼리를 실행할 때, 대부분의 옵티마이저는 테이블별로 하나의 인덱스만 사용하도록 실행 계획을 수립함.
하지만, 인덱스 머지 실행 계획을 사용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용하여 쿼리를 처리함.
일반적으로, 쿼리에서 한 테이블에 대한 WHERE 조건이 여러 개 있더라도 하나의 인덱스에 포함된 칼럼에 대한 조건만으로 인덱스를 검색하고, 나머지 조건은 읽어온 레코드에 대해서 체크하는 형태로만 사용됨.
이처럼 하나의 인덱스만 사용해서 작업 범위를 충분히 줄일 수 있는 경우라면 테이블별로 하나의 인덱스만 활용하는 것이 효율적임.
하지만, 쿼리에 사용된 각 조건이 서로 다른 인덱스를 사용할 수 있고, 그 조건을 만족하는 레코드 건수가 많을 것으로 예상될 때는 MySQL 서버는 인덱스 머지 실행 계획을 선택한다.
인덱스 머지 실행 계획은 아래와 같이 3개의 세부 실행 계획으로 나눠 볼 수 있음.
index_merge_intersection
index_merge_sort_union
index_merge_union
index_merge 옵티마이저 옵션은 위 3개의 최적화 옵션을 한 번에 모두 제어할 수 있는 옵션임.
인덱스 머지 - 교집합(index_merge_intersection)
mysql> EXPLAIN SELECT *
FROM employees
WHERE first_name='Georgi' AND emp_no BETWEEN 10000 AND 20000;
+-------------+----------------------+----------------------------------------------------+
|type | key | Extra |
+-------------+----------------------+----------------------------------------------------+
| index_merge | ix_firstname,PRIMARY | Using intersect(ix_firstname,PRIMARY); Using where |
+-------------+----------------------+----------------------------------------------------+
위 쿼리는 WHERE 조건을 2개 가지고 있음.
first_name칼럼과 emp_no 칼럼 모두 각각의 인덱스(ix_firstname, PRIMARY)를 가지고 있음.
즉, 2개 중, 어떤 조건을 사용하더라도 인덱스를 사용할 수 있음.
위 쿼리에서, 옵티마이저는 ix_firstname과 PRIMARY 키를 모두 사용해서 쿼리를 처리하기로 결정함.
실행계획에서 Extra 칼럼에 “Using inertsect”라고 표시된 것은 이 쿼리가 여러 개의 인덱스를 각각 검색해서 그 결과의 교집합만 반환했다는 것을 의미함.
first_name 칼럼의 조건과 emp_no 칼럼이ㅡ 조건 중 하나라도 충분히 효율적으로 쿼리를 처리할 수 있었다면, 옵티마이저는 2개의 인덱스를 모두 사용하는 실행 계획을 사용하지 않았을 것임.
mysql> SELECT COUNT(*) FROM employees WHERE first_name='Georgi';
+----------+
| COUNT(*) |
+----------+
| 253 |
+----------+
mysql> SELECT COUNT(*) FROM employees WHERE emp_no BETWEEN 10000 AND 20000;
+----------+
| COUNT(*) |
+----------+
| 10000 |
+----------+
인덱스 머지 실행 계획이 아니었다면 다음 2가지 방식으로 처리해야 했을 것임.
“first_name=’Georgi’” 조건만 인덱스를 사용했다면, 일치하는 레코드 253건을 검색한 다음, 데이터 페이지에서 레코드를 찾고 emp_no 칼럼의 조건에 일치하는 레코드들만 반환하는 형태로 처리돼야 함.
“emp_no BETWEEN 10000 AND 20000” 조건만 인덱스를 사용했다면 프라이머리 키를 이용해 10,000건을 읽어와서 “first_name=’Georgi’” 조건에 일치하는 레코드만 반환하는 형태로 처리돼야함.
위 두 방식 모두 나쁘지 않은 실행 계획인듯 하지만, MySQL 의 옵티마이저는 두 인덱스의 교집합만 가져오는 실행 계획을 세웠음.
mysql> SELECT COUNT(*) FROM employees
WHERE first_name='Georgi' AND emp_no BETWEEN 10000 AND 20000;
+----------+
| COUNT(*) |
+----------+
| 14 |
+----------+
위 쿼리 결과를 보면, 옵티마이저가 현명한 선택을 했다는 것을 알 수 있음.
실제로 두 조건을 모두 만족하는 레코드 건수는 14건밖에 안됨.
ix_firstname 인덱스만 사용했다면 253번의 데이터 페이지 읽기 중, 14번만 의미 있는 작업이었을 것임.
그래서, first_name=’Matt’ 인 조건과 hire_date=’1987-03-31’ 조건이 각각 인덱스를 사용할 수 있음.
이 쿼리의 실행 계획은 “Using union” 최적화를 사용함.
Using union(ix_firstname, ix_hiredate) 라고 표시된 것은, 인덱스 머지 최적화가 ix_firstname 인덱스의 검색 결과와 ix_hiredate 인덱스 검색 결과를 Union 알고리즘으로 병합(두 집합의 합집합)했음을 의미함.
이 Union 알고리즘에 숨은 비밀이 하나 있음.
위 예제에서, first_name=’Matt’ 이면서 hire_date=’1987-03-31’인 사원에 대한 조건을 걸었는데, 그 사원의 정보는 ix_firstname 인덱스를 검색한 결과에도 포함돼 있을 것이고, ix_hiredate 인덱스를 검색한 결과도 포함돼 있을 것임.
하지만, 이 쿼리의 결과에서 사원의 정보가 두번 출력 되지는 않음.
MySQL 서버에서 두 결과 집합을 정렬해서 중복 레코드를 제거했을 것이라고 볼 수 있는데, 정렬 했다는 내용이 실행 계획에는 없음.
위 그림은 인덱스 머지 최적화의 ‘Union’ 알고리즘의 작동 방식을 그림으로 표현한 것임.
위 그림에서, first_name 칼럼의 검색 결과와 hire_date 칼럼의 검색 결과에서 사우너 번호가 “13163”인 사원은 양쪽 집합에 모두 포함돼 있어서 반드시 제거해야 함.
MySQL 서버는 first_name 조건 검색 결과와 hire_date 검색 결과가 프라이머리 키로 이미 정렬되어 있다는 걸 알고 있음.
MySQL 서버는 first_name 조건으로 얻은 집합과, hire_date 조건으로 얻은 집합을 하나씩 가져와서 서로 비교하면서 프라이머리 키인 emp_no 칼럼의 값이 중복된 레코드들을 정렬 없이 걸러낼 수 있음.
이렇게 정렬된 두 집합의 결과를 하나씩 가져와서 중복 제거를 수행할 때 사용된 알고리즘을 우선순위 큐(Priority Queue) 라고 함.
인덱스 머지 - 정렬 후 합집합(index_merge_sort_union)
인덱스 머지 최적화의 ‘Union’ 알고리즘은 두 결과 집합의 중복을 제거하기 위해 정렬된 결과를 필요로 하는데도 MySQL 서버는 별도의 정렬을 수행하지 않음. (위, 합집합 인덱스 머지의 예제에서는 프라이머리 키가 정렬을 수행해서 가능했음)
모든 경우가 정렬이 필요하지 않은 건 아님.
만약, 인덱스 머지 작업 하는 도중, 결과의 정렬이 필요한 경우, MySQL 서버는 인덱스 머지 최적화의 ‘Sort union’ 알고리즘을 사용함.
mysql> EXPLAIN
SELECT * FROM employees
WHERE first_name='Matt'
OR hire_date BETWEEN '1987-03-01' AND '1987-03-31'
위 쿼리를 ‘Union’ 알고리즘에서 설명한 것처럼, 2개의 쿼리로 분리해서 생각해 보자.
mysql> SELECT * FROM employees WHERE first_name='Matt';
mysql> SELECT * FROM employees WHERE hire_date BETWEEN '1987-03-01' AND '1987-03-31'';
첫 번째 쿼리 결과는 emp_no 로 정렬되어 출력됨.
두 번째 쿼리 결과는 emp_no 칼럼으로 정렬되 있지 않은 것을 알 수 있음. (범위 이므로 프라이머리로 정렬되어있지 않을 수 있음.)
따라서, 중복 제거하기 위한 우선순위 큐를 사용하는 것이 불가능함.
그래서, MySQL 서버는 두 집합의 결과에서 중복을 제거하기 위해, 각 집합을 emp_no 칼럼으로 정렬한 다음, 중복 제거를 수행함.
위 쿼리는 인덱스 머지 최적화에서 중복 제거를 위해 강제로 정렬을 수행하므로, Extra 칼럼에 “Using sort_union” 문구가 표시됨.
세미 조인(semijoin)
다른 테이블과 실제 조인을 수행하지 않고, 단지 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리를 세미 조인(Semi-Join) 이라고 함.
MySQL 5.7 서버는 전통적으로 세미 조인 형태의 쿼리 최적화 부분이 취약했음.
mysql> SELECT *
FROM employees e
WHERE e.emp_no IN
(SELECT de.demp_no FROM dept_emp de WHERE de.from_date='1995-01-01');
MySQL 서버에서 세미 조인 최적화 기능이 없었을 떄, 위의 세미 조인 쿼리의 실행 결과는 아래와 같았음.
+----+-------------+-------+------+-------------+---------+
| id | select_type | table | type | key | rows |
+----+-------------+-------+------+-------------+---------+
| 1 | PRIMARY | e | ALL | NULL | 300363 |
| 1 | SUBQUERY | de | ref | ix_fromdate | 57 |
+----+-------------+-------+------+-------------+---------+
MySQL 서버는 employees 테이블을 풀 스캔하면서 한 건 한 건 서브쿼리의 조건에 일치하는지 비교했음.
위 실행 계획만 봐도 57건만 읽으면 될 쿼리를 30만 건 넘게 읽어서 처리된다는 것을 알 수 있음.
세미 조인(Semi-join) 형태의 쿼리와 안티 세미 조인(Anti Semi-join) 형태의 쿼리는 최적화 방법이 조금 차이가 있음.
“= (subquery)” 형태와 “IN (subquery)” 형태의 세미 조인 쿼리에 대해 다음과 같은 3가지 최적화 방법을 적용할 수 있음.
세미 조인 최적화
IN-to-EXISTS 최적화
MATERIALIZATION 최적화
“<> (subquery)” 형태와 “NOT IN (subquery)” 형태의 안티 세미 조인 쿼리에 대해서는 다음 2가지 최적화 방법이 있음.
IN-to-EXISTS 최적화
MATERIALIZATION 최적화
이 책에서는 서브쿼리 최적화 중, 최근 도입된 세미 조인 최적화에 대해서만 살펴본다.
MySQL 서버 8.0 버전부터는 세미 조인 쿼리의 성능을 개선하기 위해, 다음과 같은 최적화 전략이 있음.
MySQL 서버 매뉴얼에서는 아래 최적화 전략들을 모아서 세미 조인 최적화라 부름.
Table Pull-out
Duplicate Weed-out
First Match
Loose Scan
Materialization
쿼리에 사용되는 테이블과 조인 조건의 특성에 따라 MySQL 옵티마이저는 사용 가능한 전략들을 선별적으로 사용함.
Table pull-out 최적화 전략은 사용 가능하면 항상 세미 조인보다는 좋은 성능을 내기 때문에 별도로 제어하는 옵티마이저 옵션을 제공하지 않음.
그리고, First Match, Loose Scan 최적화 전략은 각각 firstmatch와 loosescan 옵티마이저 옵션으로 사용 여부를 결정할 수 있음.
Duplicate Weed-out과 Materialization 최적화 전략은 materialization 옵티마이저 스위치로 사용 여부를 선택할 수 있음.
optimizer_switch 시스템 변수의 semijoin 옵티마이저 옵션은 firstmatch와 loosescan, materialization 옵티마이저 옵션을 한 번에 활성화하거나 비활성화할 때 사용함.
