티스토리 뷰
이전 글: https://gojs.tistory.com/40
MySQL에서 옵티마이저 동작하는 원리
쿼리 실행 절차SQL parsing → parse tree → query plan → physical work옵티마이저는 위의 쿼리 실행 절차 중 parse tree를 기반으로 쿼리 실행 계획을 세우는 역할을 수행한다. 옵티마이저의 자체적인 동작
gojs.tistory.com
옵티마이저 스위치 옵션
옵티마이저 스위치 옵션은 optimizer_switch 시스템 변수를 이용해서 제어한다. 이 변수는 여러 옵션을 같이 설정하게 되는데 아래와 같은 최적화 옵션을 가진다.
| 옵티마이저 스위치 이름 | 기본값 | 설명 |
| batched_key_access | off | BKA 조인 알고리즘을 사용할지 여부 설정 |
| block_nested_loop | on | Block Nested Loop 조인 알고리즘을 사용할지 여부 설정 |
| engine_condition_pushdown | on | Engine Condition Pushdown 기능을 사용할지 여부 설정 |
| index_condition_pushdown | on | Index Condition Pushdown 기능을 사용할지 여부 설정 |
| use_index_extensions | on | Index Extension 최적화를 사용할지 여부 설정 |
| index_merge | on | Index Merge 최적화를 사용할지 여부 설정 |
| index_merge_intersection | on | Index Merge Intersection 최적화를 사용할지 여부 설정 |
| index_merge_sort_union | on | Index Merge Sort Union 최적화를 사용할지 여부 설정 |
| index_merge_union | on | Index Merge Union 최적화를 사용할지 여부 설정 |
| mrr | on | MRR 최적화를 사용할지 여부 설정 |
| mrr_cost_based | on | 비용 기반의 MMR 최적화를 사용할지 여부 설정 |
| semijoin | on | 세미 조인 최적화를 사용할지 여부 설정 |
| firstmatch | on | FirstMatch 세미 조인 최적화를 사용할지 여부 설정 |
| loosescan | on | LooseScan 세미 조인 최적화를 사용할지 여부 설정 |
| materialization | on | Materialization 최적화를 사용할지 여부 설정 |
| subquery_materialization_cost_based | on | 비용 기반의 Materialization 최적화를 사용할지 여부 설정 |
각 스위치 옵션은 default, on, off 중 하나를 설정할 수 있으며 글로벌/세션 모두 별도로 설정할 수 있다.
mysql> SET GLOBAL optimizer_switch='index_merge=on, index_merge_union=on';
mysql> SET SESSION optimizer_switch='index_merge=on, index_merge_union=on';
또한 아래와 같이 SET_VAR 힌트를 사용해 현재 쿼리에만 설정할 수도 있다.
mysql> SELECT /*+ SET_VAR(optimizer_switch='index_merge=on, index_merge_union=on') */
...
MRR과 배치 키 액세스
- 네스티드 루프 조인 (Nested Loop Join)
- MySQL에서 지금까지 지원하던 조인 방식
- 드라이빙 테이블의 레코드 1건 조회 → 드리븐 테이블의 일치하는 레코드 조회 → 조인
- 조인 처리 : MySQL 엔진이 처리
- 레코드 조회 : 스토리지 엔진이 처리 ⇒ 최적화 수행 불가
- MySQL 엔진
- 드라이빙 테이블 레코드 조회 → 조회 대상을 버퍼링
- 버퍼가 조회 대상 레코드가 가득차면 한번에 스토리지 엔진에 요청
- 스토리지 엔진
- 읽어야 할 레코드를 정렬된 순서로 접근
- 버퍼 풀 접근 최소화MRR (Multi-Range Read)
⇒ MMR을 응용해서 실행되는 조인 방식 = BKA(Batched Key Access) 조인
⇒ 부가적인 정렬 작업이 필요해져 종종 성능 저하 케이스가 발생한다
⇒ batched_key_access → default : off
블록 네스티드 루프 조인 (block_nested_loop)
mysql> EXPLAIN SELECT *
FROM employees e
INNER JOIN salaries s
ON s.emp_no = e.emp_no
AND s.from_date <= NOW()
AND s.to_date >= NOW()
WHERE e.first_name='Amor';
+----+-------------+-------+------+-----------------+------+-------------+
| id | select_type | table | type | key | rows | Extra |
+----+-------------+-------+------+-----------------+------+-------------+
| 1 | SIMPLE | e | ref | ix_firstname | 1 | NULL |
| 2 | SIMPLE | s | ref | PRIMARY | 10 | Using where |
+----+-------------+-------+------+-----------------+------+-------------+위와 같은 조인은 중첩된 반복문 처럼 동작해서 네스티드 루프 조인(Nested Loop Join)이라고 한다.
for (row1 in employees) {
for (row2 in salaries) {
if (condition_matched) return (row1, row2);
}
}위의 의사 코드처럼 드라이빙 레코드를 읽어서 그대로 드리븐 테이블의 레코드를 찾는다.
블록 네스티드 루프 조인과 네스티드 루프 조인의 가장 큰 차이점은 조인 버퍼를 사용하는지 여부와 조인에서 드라이빙 테이블과 드리븐 테이블이 조회되는 순서이다.
(Extra 컬럼에 Using Join buffer가 명시된다)
- 드라이빙 테이블에서 읽은 데이터를 메모리에 캐시 (조인 버퍼)
- 드리븐 테이블과 메모리 캐시를 조인
mysql> SELECT *
FROM dept_emp de, employees e
WHERE de.from_date > '1995-01-01' AND e.emp_no < 109004;위의 쿼리는 두 테이블에 대한 조인 조건이 없다. 따라서 각 where 조건에 따라 필터링된 데이터가 N x M 개의 레코드로 카테시안 조인을 일으킨다.
+----+-------------+-------+-------+-----------------+---------------------------------------+
| id | select_type | table | type | key | Extra |
+----+-------------+-------+-------+-----------------+---------------------------------------+
| 1 | SIMPLE | de | range | ix_fromdate | Using index condition |
| 2 | SIMPLE | e | range | PRIMARY | Using join buffer (block nested loop) |
+----+-------------+-------+-------+-----------------+---------------------------------------+
위의 실행 계획을 보면 드라이빙 테이블은 dept_emp 테이블이다.
그리고 employees 테이블을 읽을 때에는 조인 버퍼를 사용하여 블록 네스티드 루프 조인을 한다. (Extra 컬럼)
- dept_emp 테이블의 ix_fromdate 인덱스를 이용해 조건절에 알맞는 레코드 조회
- 조인에 필요한 나머지 컬럼을 모두 dept_emp 테이블로부터 읽어서 조인 버퍼에 저장
- employees 테이블의 PK를 이용해 조건절에 알맞는 레코드 조회
- 3번의 결과와 2번의 캐시 데이터를 결합해서 반환
위 연산의 과정에서 드라이빙 테이블은 dept_emp이지만, 최종 결과는 3번 단계의 employees 테이블의 레코드를 기반으로 생성된다.
따라서 최종 결과의 정렬 순서는 employees의 PK 순일 수 있다.
인덱스 컨디션 푸시다운 (index_condition_pushdown)
mysql> ALTER TABLE employees ADD INDEX ix_lastname_firstname (last_name, first_name);
mysql> SET optimizer_switch='index_condition_pushdown=off';
mysql> SELECT *
FROM employees
WHERE last_name='Action'
AND first_name like '%sal';
+----+-------------+-----------+------+-----------------------+---------+-------------+
| id | select_type | table | type | key | key_len | Extra |
+----+-------------+-----------+------+-----------------------+---------+-------------+
| 1 | SIMPLE | employees | ref | ix_lastname_firstname | 66 | Using where |
+----+-------------+-----------+------+-----------------------+---------+-------------+last_name, first_name으로 구성된 인덱스를 하나 생성하고 인덱스 컨디션 푸시다운 옵션을 off로 바꾸었다.
그리고 작성된 쿼리 실행 계획의 Extra 컬럼에 Using where가 표시된다.
인덱스를 사용해 스토리지 엔진이 반환해준 데이터를 where 조건에 일치하는지 검사하는 과정을 의미한다. (first_name like ‘%sal’)
이 때 원하는 데이터를 얻기 위한 과정은 아래와 같다.
- 스토리지 엔진이 인덱스를 사용해 조건절의 last_name에 해당하는 데이터를 조회
- MySQL 엔진이 조회된 데이터를 가지고 조건절의 first_name에 해당하는 데이터를 조회
이처럼 데이터를 조회하기 때문에 first_name에 대한 조회를 두 번씩 처리하게되어 성능이 저하된다.
하지만 인덱스 컨디션 푸시다운을 사용하게 되면
- 스토리지 엔진이 인덱스를 사용해 조건절의 last_name에 해당하는 데이터를 필터링
- 스토리지 엔진이 first_name에 대한 조건절에 해당하는 데이터를 필터링하고 최종 조회
이 경우 스토리지 엔진에서 데이터를 한 번에 조회하기 때문에 성능이 증가한다.
+----+-------------+-----------+------+-----------------------+---------+-----------------------+
| id | select_type | table | type | key | key_len | Extra |
+----+-------------+-----------+------+-----------------------+---------+-----------------------+
| 1 | SIMPLE | employees | ref | ix_lastname_firstname | 66 | Using index condition |
+----+-------------+-----------+------+-----------------------+---------+-----------------------+
인덱스 확장 (use_index_extensions)
InnoDB를 사용하는 테이블에서 세컨더리 인덱스에 자동으로 추가된 PK를 사용할지 말지 결정하는 것이다.
mysql> CREATE TABLE dept_emp (
emp_no INT NOT NULL,
dept_no CHAR(4) NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
PRIMARY KEY (dept_no, emp_no),
KEY ix_fromdate (from_date)
) ENGINE=InnoDB;위의 ix_fromdate 인덱스는 from_date 컬럼을 인덱스 컬럼으로 사용한다.
그러나 실제 레코드를 찾아가는 과정에서는 PK를 타고 가기 때문에 (from_date, dept_no, emp_no)처럼 동작한다.
따라서 ix_fromdate를 범위 조회에 활용할 수 있지만, PK 컬럼을 사용해서 필요한 하나의 레코드만 조회도 가능하다.
mysql> EXPLAIN SELECT COUNT(*)
FROM dept_emp
WHERE from_date = '1987-07-25'
AND dept_no = 'd0001';
+----+-------------+-----------+------+-------------+---------+--------------+
| id | select_type | table | type | key | key_len | ref |
+----+-------------+-----------+------+-------------+---------+--------------+
| 1 | SIMPLE | dept_emp | ref | ix_fromdate | 19 | const, const |
+----+-------------+-----------+------+-------------+---------+--------------+위 실행 계획에서 key_len 컬럼은 인덱스를 구성하는 컬럼 중 어느 컬럼까지 사용했는지를 보여준다.
(from_date, dept_no [3byte + 16byte])
mysql> EXPLAIN SELECT COUNT(*)
FROM dept_emp
WHERE from_date = '1987-07-25';
+----+-------------+-----------+------+-------------+---------+-------+
| id | select_type | table | type | key | key_len | ref |
+----+-------------+-----------+------+-------------+---------+-------+
| 1 | SIMPLE | dept_emp | ref | ix_fromdate | 3 | const |
+----+-------------+-----------+------+-------------+---------+-------+dept_no 컬럼을 조건절에서 제거하니 key_len 값도 3바이트만 표시된 것을 확인할 수 있다.
인덱스 머지 (index_merge)
인덱스를 이용해 쿼리를 실행하는 경우 대부분 옵티마이저는 하나의 인덱스를 사용한다.
그러나 인덱스 머지를 사용한다면 하나의 테이블에 두 개 이상의 인덱스를 이용하여 쿼리하게 된다.
교집합 (index_merge_intersection)
mysql> EXPLAIN SELECT *
FROM employees
WHERE first_name = 'Georgi'
AND emp_no BETWEEN 10000 AND 20000;
+-------------+-----------------------+---------+-----------------------------------------------------+
| type | key | key_len | Extra |
+-------------+-----------------------+---------+-----------------------------------------------------+
| index_merge | ix_firstname, PRIMARY | 62, 4 | Using intersect(ix_firstname, PRIMARY); Using where |
+-------------+-----------------------+---------+-----------------------------------------------------+위 쿼리의 조건절의 first_name과 emp_no 모두 각각의 인덱스를 사용할 수 있다.
따라서 Using intersect라고 표시된 것은 각각의 인덱스를 검색해서 그 교집합의 결과만 반환했음을 뜻한다.
두 인덱스 중 하나의 비용이 작았더라면 해당 인덱스만 사용했을 것이지만 각 반환결과가 많은 레코드를 포함하고 있기 때문에 index_merge_intersection을 사용한 것이다.
ix_firstname만 사용하는 경우
1. 인덱스에서 WHERE first_name = 'Georgi'에 해당하는 253건의 데이터 조회
2. 해당 데이터에 대한 레코드를 읽어 AND emp_no BETWEEN 10000 AND 20000; 처리
이 경우 ix_firstname 인덱스는 확장되어 PK인 emp_no를 포함하기 때문에, ix_firstname 인덱스만 사용하는 것이 성능이 더 좋을 수도 있다.
mysql> EXPLAIN SELECT /*+ SET_VAR(optimizer_switch='index_merge_intersection=off') */ *
FROM employees
WHERE first_name = 'Georgi'
AND emp_no BETWEEN 10000 AND 20000;
+----+-------------+-----------+------+--------------+---------+-----------------------+
| id | select_type | table | type | key | key_len | Extra |
+----+-------------+-----------+------+--------------+---------+-----------------------+
| 1 | SIMPLE | employees | range| ix_firstname | 62 | Using index condition |
+----+-------------+-----------+------+--------------+---------+-----------------------+합집합 (index_merge_union)
인덱스 머지의 Using union은 OR 연산에 대한 최적화이다.
mysql> SELECT *
FROM employees
WHERE first_name = 'Matt'
OR hire_date = '1987-03-31';
+-------------+---------------------------+---------+-----------------------------------------+
| type | key | key_len | Extra |
+-------------+---------------------------+---------+-----------------------------------------+
| index_merge | ix_firstname, ix_hiredate | 58, 3 | Using union(ix_firstname, ix_hiredate); |
+-------------+---------------------------+---------+-----------------------------------------+실행 계획의 Extra 컬럼의 Using union이라고 표시된 것은 ix_firstname과 ix_hiredate의 검색 결과를 병합하였다는 뜻이다.
위의 그림과 같이 두 개의 인덱스 검색 결과를 하나로 합치는 과정에서 우선순위 큐를 사용한다.
이미 PK로 정렬되어 있는 양쪽의 데이터를 비교하며 중복 제거를 수행한다.
정렬 후 합집합 (index_merge_sort_union)
위의 index_merge_union의 경우 이미 정렬이 되어 있기에 추가적인 정렬작업이 필요하지 않다.
하지만 추가적으로 정렬이 필요한 경우 Sort union 알고리즘을 사용한다.
mysql> EXPLAIN SELECT *
FROM employees
WHERE first_name = 'Matt'
OR hire_date BETWEEN '1987-03-01' AND '1987-03-31';위의 쿼리를 아래 두 개의 쿼리로 분리하여 생각해보자.
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로 정렬되어 있지 않기 때문에, 우선순위 큐를 사용하는 것이 불가능하다.
따라서 병합작업 전 정렬작업을 먼저 수행하게된다.
+-------------+---------------------------+---------+----------------------------------------------+
| type | key | key_len | Extra |
+-------------+---------------------------+---------+----------------------------------------------+
| index_merge | ix_firstname, ix_hiredate | 58, 3 | Using sort_union(ix_firstname, ix_hiredate); |
+-------------+---------------------------+---------+----------------------------------------------+
세미 조인
다른 테이블과 실제 조인을 수행하지 않고 조건에 일치하는지만 체크하는 쿼리를 세미 조인이라고 한다.
mysql> SELECT *
FROM employees e
WHERE e.emp_no in (SELECT de.emp_no
FROM dept_emp de
WHERE de.from_date='1995-01-01');
+----+-------------+-------+-------+--------------+--------+
| id | select_type | table | type | key | rows |
+----+-------------+-------+-------+--------------+--------+
| 1 | PRIMARY | e | ALL | NULL | 300363 |
| 2 | SUBQUERY | de | ref | ix_from_date | 57 |
+----+-------------+-------+-------+--------------+--------+이전에 세미 조인 최적화 기능이 없을 때는 위와 같은 실행 계획이 생성되었다.
employees 테이블을 풀 스캔하고 서브쿼리의 조건에 일치하는지 한 건씩 비교했다.
(57건 정도 읽는 쿼리를 30만 건 이상 조회)
따라서 이를 해결하기 위한 아래와 같은 최적화 전략이 등장했다.
- Table Pull-out
- Duplicate Weed-out
- First Match
- Loose Scan
- Materialization
테이블 풀-아웃
Table pull-out 최적화는 세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끌어내 쿼리를 재작성하는 형태로 동작한다.
mysql> EXPLAIN SELECT *
FROM employees e
WHERE e.emp_no in (SELECT de.emp_no
FROM dept_emp de
WHERE de.dept_no = 'd009');
+----+-------------+-------+--------+--------------+-------+-------------+
| id | select_type | table | type | key | rows | Extra |
+----+-------------+-------+--------+--------------+-------+-------------+
| 1 | SIMPLE | de | ref | PRIMARY | 46012 | Using index |
| 2 | SIMPLE | e | eq_ref | PRIMARY | 1 | NULL |
+----+-------------+-------+--------+--------------+-------+-------------+위의 실행 계획을 확인해보면 옵티마이저가 table pull-out 최적화를 사용했는지 확인할 수 있는 방법은 없다.
다만 mysql의 show warnings 명령어로 옵티마이저가 바꿔서 실행한 쿼리를 확인할 수는 있다.
mysql> SHOW WARNINGS \\G
****************************** 1. row ******************************
Level: Note
Code: 1003
Message: /* select#1 */ SELECT employees.e.emp_no AS emp_no,
employees.e.birth_date AS birth_date,
employees.e.first_name AS first_name,
employees.e.last_name AS last_name,
employees.e.gender AS gender,
employees.e.hire_date AS hire_date
FROM employees.dept_emp de
JOIN employees.employees e
WHERE ((employees.e.emp_no = employees.de.emp_no)
AND (employees.de.dept_no = 'd009'));위의 쿼리를 확인해보면 in 연산을 사라지고 join 연산을 사용하는 쿼리가 발생한 것을 확인할 수 있다.
Table pull-out 최적화는 모든 서브 쿼리에서 사용되는 것은 아니고 아래와 같은 경우에만 사용된다.
- 세미 조인 서브쿼리
- 서브쿼리 부분이 UNIQUE 인덱스나 프라이머리 키 룩업으로 결과가 1건인 경우
MySQL에서는 최대한 서브쿼리를 풀어서 조인으로 사용해라는 튜닝가이드를 제공하는데 이에 대한 내용을 수행하는 것이 Table pull-out 최적화이다.
서브쿼리보다 조인을 수행해야하는 이유
1. 연산비용이 추가 : 서브쿼리는 실제 저장된 데이터가 아니기 때문에, 서브쿼리에 접근할 때마다 쿼리를 실행한다.
2. 데이터 I/O 증가 : 연산결과를 어딘가에 써두어야 하기 때문에 이에 따른 데이터 I/O가 증가한다.
3. 최적화 불가 : 서브쿼리로 만들어지는 데이터는 메타데이터가 없기 때문에 최적화가 불가능하다.
퍼스트 매치
First Match 최적화 전략은 in 연산을 exist 형태로 튜닝한 것과 비슷한 방법으로 실행된다.
mysql> EXPLAIN SELECT *
FROM employees e
WHERE e.first_name = 'Matt'
AND e.emp_no in (SELECT t.emp_no
FROM titles t
WHERE t.from_date BETWEEN '1995-01-01' AND '1995-01-30');
+----+-------+--------+--------------+-------+-----------------------------------------+
| id | table | type | key | rows | Extra |
+----+-------+--------+--------------+-------+-----------------------------------------+
| 1 | e | ref | ix_firstname | 233 | NULL |
| 1 | t | ref | PRIMARY | 1 | Using where; Using index; FirstMatch(e) |
+----+-------+--------+--------------+-------+-----------------------------------------+실행 계획의 id 컬럼이 모두 1로 표시되었다. (titles 테이블을 조인 형태로 사용)
실행 계획의 Extra 컬럼에 FirstMatch(e) 라는 문구가 출력되었다. (titles 테이블 일치 레코드 1건만 찾고 추가 검색하지 않는다)
emp_no가 243075인 직원은 두 번 직급이 변경되었지만 titles 테이블에 접근할 때 두 번째 변경분은 체크하지 않는다.
왜냐하면 이미 첫 번째 변경 분에서 쿼리의 조건에 맞는 레코드라는 것을 확인하였기 때문이다.
FirstMatch 최적화는 아래와 같은 특징을 가진다.
- 단축 실행 경로이므로 서브쿼리가 참조하는 모든 아우터 테이블이 먼저 조회된 이후 실행
- 상관 서브쿼리(바깥쪽 테이블의 컬럼을 조건에 활용)에서도 사용
- GROUP BY, 집합함수 사용하는 경우 사용 불가
- semijoin, firstmatch 옵션이 모두 ON인 경우에만 사용 가능
루스 스캔
LooseScan 최적화는 인덱스를 사용하는 GROUP BY 최적화 중 루스 인덱스 스캔과 비슷한 방식을 사용한다.
mysql> EXPLAIN SELECT *
FROM departments d
WHERE d.dept_no in (SELECT de.dept_no
FROM dept_emp de);
+----+-------+--------+--------------+--------+------------------------+
| id | table | type | key | rows | Extra |
+----+-------+--------+--------------+--------+------------------------+
| 1 | de | index | PRIMARY | 331143 | Using index, LooseScan |
| 1 | d | eq_ref | PRIMARY | 1 | NULL |
+----+-------+--------+--------------+--------+------------------------+dept_emp 테이블의 경우에는 (dept_no, emp_no)으로 구성된 기본 키를 가진다.
그리고 테이블 구조상 dept 테이블은 적은 레코드를 가지며 dept_emp 테이블은 많은 레코드를 가진다.
따라서 dept_emp 테이블의 기본 키의 유니크한 dept_no만 읽음으로써 효율적으로 서브쿼리를 실행할 수 있다.
따라서 dept_emp 테이블이 드라이빙 테이블로 실행되며, 실행 계획의 id가 모두 1인 것으로 보아 내부적으로 조인처럼 처리된 것을 알 수 있다.
구체화
Materialization 최적화는 세미 조인에서 사용된 서브쿼리를 내부 임시 테이블을 생성하여 최적화한다.
mysql> EXPLAIN SELECT *
FROM employees e
WHERE e.emp_no in (SELECT de.emp_no
FROM dept_emp de
WHERE de.from_date = '1995-01-01');
+----+--------------+-------------+--------+-------------+--------------------+
| id | select_type | table | type | key | ref |
+----+--------------+-------------+--------+-------------+--------------------+
| 1 | SIMPLE | <subquery2> | ALL | NULL | NULL |
| 1 | SIMPLE | e | eq_ref | PRIMARY | <subquery2>.emp_no |
| 2 | MATERIALIZED | de | ref | ix_fromdate | const |
+----+--------------+-------------+--------+-------------+--------------------+위 쿼리에서는 employees 테이블에 대한 조건이 없기 때문에 FirstMatch 최적화를 사용하면 employees 테이블을 풀 스캔해야하기 때문에 위와 같은 실행 계획을 바탕으로 Materialization 최적화를 사용한다.
실행 계획의 마지막 라인의 select_type 컬럼에 MATERIALIZED라고 표시되었다. 위의 쿼리의 테이블은 2개인데 실행 계획에 3개의 라인이 출력된 것을 가지고도 어디엔가 임시테이블이 생성되었음을 알 수 있다.
이와 같은 경우에는 dept_emp 테이블을 읽어 서브쿼리의 결과를 임시테이블(<subquery2>)에 저장한다.
그리고 임시테이블과 employees 테이블을 조인하여 결과를 반환한다.
- 상관 서브쿼리는 사용 불가
- GROUP BY나 집합함수 사용 가능
중복 제거
Duplicate Weedout 최적화는 서브쿼리를 inner join으로 바꿔서 실행하고 중복을 제거하는 방식으로 동작하는 최적화 전략이다.
mysql> EXPLAIN SELECT *
FROM employees e
WHERE e.emp_no in (SELECT s.emp_no
FROM salaries s
WHERE s.salary > 150000);
+----+-------------+-------+--------+-----------+-------------------------------------------+
| id | select_type | table | type | key | Extra |
+----+-------------+-------+--------+-----------+-------------------------------------------+
| 1 | SIMPLE | s | range | ix_salary | Using where; Using index; Start temporary |
| 1 | SIMPLE | e | eq_ref | PRIMARY | End temporary |
+----+-------------+-------+--------+-----------+-------------------------------------------+위의 쿼리를 실행하면 아래의 쿼리와 같은 작업으로 쿼리를 처리한다.
mysql> SELECT e.*
FROM employees e, salaries s
WHERE e.emp_no = s.emp_no
AND s.salary > 1500000
GROUP BY e.emp_no;그리고 그 과정을 아래의 그림으로 표현할 수 있다.
- salaries 테이블의 ix_salary 인덱스를 스캔하여 salary > 150000인 사원을 검색하고 employees 테이블과 조인
- 조인된 결과를 임시테이블에 저장
- 임시 테이블에 저장된 결과에서 emp_no 기준 중복 제거
- 결과 반환
이렇게 수행되는 쿼리의 실행 계획을 확인해보면 Start temporary와 End temporary 문구가 표기된 것을 확인할 수 있다.
조인을 수행하고 임시 테이블에 저장하는 작업을 시작하는 테이블과 끝나는 테이블에 해당 문구들이 표시된다.
Duplicate Weedout 최적화는 아래와 같은 특징이 있다.
- 상관 서브쿼리도 사용 가능
- GROUP BY나 집합 함수는 사용 불가
컨디션 팬아웃
조인 실행 시 테이블 순서는 성능에 큰 영향을 미친다.
만약 A 테이블 일치 레코드 : 1만 건, B 테이블 일치 레코드 : 10 건이라면 A 테이블이 드라이빙 테이블이면 B 테이블을 1만 번 읽어야 한다
이 경우에는 가능한 적은 양의 레코드를 가진 테이블부터 읽어서 성능 최적화를 수행한다.
mysql> SELECT *
FROM employees e
INNER JOIN salaries s
ON s.emp_no = e.emp_no
WHERE e.first_name = 'Matt'
AND e.hire_date BETWEEN '1985-11-21' AND '1986-11-21';
mysql> SET optimizer_switch='condition_fanout_filter=off';
+----+-------+------+--------------+------+-----------+-------------+
| id | table | type | key | rows | filtered | Extra |
+----+-------+------+--------------+------+-----------+-------------+
| 1 | e | ref | ix_firstname | 233 | 100.00 | Using where |
| 1 | s | ref | PRIMARY | 10 | 100.00 | NULL |
+----+-------+------+--------------+------+-----------+-------------+
mysql> SET optimizer_switch='condition_fanout_filter=on';
+----+-------+------+--------------+------+-----------+-------------+
| id | table | type | key | rows | filtered | Extra |
+----+-------+------+--------------+------+-----------+-------------+
| 1 | e | ref | ix_firstname | 233 | 23.20 | Using where |
| 1 | s | ref | PRIMARY | 10 | 100.00 | NULL |
+----+-------+------+--------------+------+-----------+-------------+우선 condition_fanout_filter 옵션을 off하고 실행 계획을 조회해보았더니 아래와 같은 순서로 처리되는 것을 알 수 있다.
- employees 테이블에서 ix_firstname 인덱스를 이용해 Matt인 233건 레코드를 검색
- 233건의 레코드 중 hire_date가 1985-11-21 ~ 1986-11-21 인 레코드를 필터링
- salaries 테이블의 PK를 이용해 salaries 테이블의 레코드 검색
- employees 테이블 레코드 1건 당 salaries 테이블 레코드 10건 예상
그리고 condition_fanout_filter 옵션을 on하고 실행 계획을 조회해보았다.
실행 계획의 다른 내용들은 거의 일치하지만 filtered 컬럼의 값이 100.00 → 23.20로 변경되었다.
condition_fanout_filter 최적화가 활성화되며 인덱스를 사용할 수 있는 first_name 칼럼 조건 외의 나머지 조건에서도 얼마나 충족할지가 고려되었다.
233건 중 e.hire_date BETWEEN '1985-11-21' AND '1986-11-21' 를 만족하는 데이터 → 54건 예측 (233 x 0.2320)
23.20을 예측하는 방법
- 사용된 컬럼의 인덱스가 있는 경우
- hire_date 컬럼의 인덱스로 레코드 비율이 23.2% 정도 임을 확인
- ix_firstname 인덱스로 233건을 먼저 확인
- 사용된 컬럼의 히스토그램이 존재하는 경우
- hire_date 컬럼의 분포도를 살펴보고 비율 예측
- ix_firstname 인덱스로 233건을 먼저 확인
condition_fanout_filter 최적화 기능을 사용함으로써 얻는 이점
- 옵티마이저가 더 정교한 계산을 거처 실행 계획 수립
- (오히려 실행 계획 계산에 더 많은 비용을 사용)
- 더 올바른 비용 계산으로 더 좋은 계획을 선정하여 작업 수행
파생 테이블 머지
이전의 MySQL 서버는 FROM 절에 사용된 서브쿼리는 먼저 실행해서 그 결과를 임시 테이블로 만든 다음 외부 쿼리 부분을 처리했다.
mysql> EXPLAIN SELECT *
FROM (SELECT *
FROM employees
WHERE first_name = 'Matt') derived_table
WHERE derived_table.hire_date = '1986-04-03';
+----+--------------+-------------+--------+--------------+
| id | select_type | table | type | key |
+----+--------------+-------------+--------+--------------+
| 1 | PRIMARY | <derived2> | ref | <auto_key0> |
| 2 | DERIVED | employees | ref | ix_firstname |
+----+--------------+-------------+--------+--------------+위 실행 계획을 보면 employees 테이블의 select_type 컬럼이 DERIVED로 표시되었다. 그 의미는 employees 테이블의 first_name이 ‘Matt’인 레코드만 먼저 조회하여 임시 테이블을 만든다는 의미이다.
이렇게 FROM절에 사용된 서브 쿼리에서 만들어내는 임시 테이블을 파생 테이블이라고 한다.
우선 파생 테이블은 메모리 영역에 생성되어 사용되지만 그 데이터의 양이 많아지게 된다면 디스크 영역에 생성되기 때문에 파생 테이블의 크기에 따라 성능의 차이가 크게된다.
derived_merge 최적화 옵션은 서브 쿼리와 외부 쿼리를 병합하여 최적화하는 옵션이다.
+----+--------------+-------------+-------------+---------------------------+
| id | select_type | table | type | key |
+----+--------------+-------------+-------------+---------------------------+
| 1 | PRIMARY | employees | index_merge | ix_hiredate, ix_firstname |
+----+--------------+-------------+-------------+---------------------------+
위의 실행 계획에서는 select_type 컬럼의 DERIVED 값이 없어지고, 단순 employees 테이블 쿼리의 실행 계획으로 바뀌었다.
mysql> SHOW WARNINGS \\G
****************************** 1. row ******************************
Level: Note
Code: 1003
Message: /* select#1 */ SELECT employees.e.emp_no AS emp_no,
employees.e.birth_date AS birth_date,
employees.e.first_name AS first_name,
employees.e.last_name AS last_name,
employees.e.gender AS gender,
employees.e.hire_date AS hire_date
FROM employees.employees
WHERE ((employees.employees.hire_date = DATE'1986-04-03')
AND (employees.employees.first_name = 'Matt'));항상 옵티마이저가 위와 같은 쿼리로 병합하는 작업을 처리하는 것은 아니다.
아래와 같은 경우에는 파생 테이블 머지 작업을 수행할 수 없다.
- SUM(), MIN(), MAX() 같은 집계 함수가 사용된 서브쿼리
- DISTINCT가 사용된 서브쿼리
- GROUP BY가 사용된 서브쿼리
- LIMIT이 사용된 서브쿼리
- UNION, UNION ALL을 포함한 서브쿼리
- SELECT 절에 사용된 서브쿼리
- 값이 변경되는 사용자 변수가 사용된 서브쿼리
인비저블 인덱스
MySQL 8.0 버전부터는 인덱스의 가용 상태를 제어할 수 있는 기능이 추가되었다.
mysql> ALTER TABLE employees ALTER INDEX ix_hiredate INVISIBLE;
mysql> ALTER TABLE employees ALTER INDEX ix_hiredate VISIBLE;
위와 같이 index 오브젝트의 가용 상태를 제어할 수 있다.
mysql> SET optimizer_switch='use_invisiblae_indexes=on';
위와 같이 옵티마이저 옵션을 설정함으로써, invisible 상태의 인덱스를 사용할지 말지를 결정할 수 있다.
스킵 스캔
인덱스는 순서에 따른 제약이 존재한다.
예를 들어 (A, B, C)라는 인덱스가 있을 때 A가 없으면 B, C를 이용해서 인덱스를 활용할 수 없다.
그러나 Skip Scan 최적화 전략은 이러한 인덱스의 제약을 극복하는 전략이다.
mysql> ALTER TABLE employees ADD INDEX ix_gender_birthdate (gender, birth_date);
-- 인덱스 사용 불가
mysql> SELECT *
FROM employees
WHERE birth_date >= '1965-02-01';
-- 인덱스 사용 가능
mysql> SELECT *
FROM employees
WHERE gender = 'M'
AND birth_date >= '1965-02-01';이 때 Skip Scan 최적화 전략은 사용할 수 있는 모든 gender 값을 가져와 두 번째 쿼리와 같이 쿼리를 최적화 하여 인덱스를 사용할 수 있는 쿼리로 변경하는 것이다.
-- SKIP SCAN 최적화 전략 사용
mysql> SELECT /*+ SKIP_SCAN(employees) */ count(*)
FROM employees
WHERE birth_date >= '1965-02-01';
-- 인덱스 명시
mysql> SELECT /*+ SKIP_SCAN(employees ix_gender_birthdate) */ count(*)
FROM employees
WHERE birth_date >= '1965-02-01';
-- SKIP SCAN 최적화 전략 사용 안함
mysql> SELECT /*+ NO_SKIP_SCAN(employees) */ count(*)
FROM employees
WHERE birth_date >= '1965-02-01';
해시 조인
mysql> EXPLAIN SELECT *
FROM employees e IGNORE INDEX(PRIMARY, ix_hiredate)
INNER JOIN dept_emp de IGNORE INDEX(ix_empno_fromdate, ix_fromdate)
ON de.emp_no = e.emp_no
AND de.from_date = e.hire_date;
+----+-------------+-------+--------+--------------------------------------------+
| id | select_type | table | type | Extra |
+----+-------------+-------+--------+--------------------------------------------+
| 1 | SIMPLE | de | ALL | NULL |
| 1 | SIMPLE | e | ALL | Using where; Using join buffer (hash join) |
+----+-------------+-------+--------+--------------------------------------------+위 쿼리는 인덱스를 사용못하게 막았기 때문에 옵티마이저가 해시 조인을 사용했다.
해시 조인은 빌드 단계와 프로브 단계로 나뉘어서 처리된다.
- 빌드 단계 : 조인 대상 테이블 중 레코드 건수가 적은 테이블을 선정하여 메모리에 해시 테이블을 생성
- 프로브 단계 : 나머지 테이블의 레코드를 읽어 해시 테이블의 일치 레코드를 찾는 과정
위의 경우에는 dept_emp 테이블이 빌드 테이블로 선정되었다. 따라서 employees 테이블을 스캔하며 메모리에 생성된 해시 테이블의 레코드를 찾아서 결과를 반환했다.
해시 조인을 사용할 때 join_buffer_size 로 설정된 값을 크기로 가지는 버퍼를 사용하게 된다. 그러나 실제 메모리의 크기가 이 버퍼의 크기를 넘는 경우가 발생할 수도 있다.
이 경우 빌드 테이블과 프로브 테이블을 청크 사이즈만큼 나누어서 해시 조인을 처리한다.
인덱스 정렬 선호
MySQL 옵티마이저는 ORDER BY 또는 GROUP BY를 인덱스를 사용해 처리가능한 경우 인덱스의 가중치를 높이 설정해서 실행된다.
mysql> EXPLAIN SELECT *
FROM employees
WHERE hire_date BETWEEN '1985-01-01' AND '1985-02-01'
ORDER BY emp_no;
+----+-----------+--------+--------------+--------+-------------+
| id | table | type | key | rows | Extra |
+----+-----------+--------+--------------+--------+-------------+
| 1 | employees | index | PRIMARY | 300252 | Using where |
+----+-----------+--------+--------------+--------+-------------+위의 쿼리는 아래의 두 가지 방법의 실행계획을 세울 수 있다.
- ix_hiredate 인덱스를 이용해 WHERE hire_date BETWEEN '1985-01-01' AND '1985-02-01' 조건에 맞는 레코드를 찾은 다음, emp_no로 정렬해서 결과 반환
- employees 테이블의 PK가 emp_no이므로 테이블을 정순으로 읽으며 조건에 일치하는지 비교 후 반환
hire_date 조건에 부합하는 레코드 수가 적으면 정렬에 대한 비용이 적기 때문에 1번 방법이 더 효율적일 수 있다.
그런데 위의 실행 계획에서는 2번 방법을 활용하여 쿼리를 실행하였다.
상황에 따라 더 효율적인 방법이 있겠지만 위의 경우에는 옵티마이저가 실수한 케이스이다.
(1번 방법 (ix_hiredate 레인지 스캔 + 정렬) vs 2번 방법 (pk index 풀 스캔))
이처럼 옵티마이저가 정렬에 대한 가중치를 너무 높게 잡는 경우에는 아래와 같은 방법을 활용할 수 있다.
mysql> SET SESSION optimizer_switch='prefer_ordering_index=OFF';
mysql> SELECT /*+ SET_VAR(optimizer_switch='prefer_ordering_index=OFF') */ *
FROM ...
조인 최적화 알고리즘
Exhaustive 검색 알고리즘
Exhaustive 검색 알고리즘은 FROM절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법이다.
위의 그림과 같이 동작하는 Exhaustive 검색 알고리즘은 테이블이 20개라면 20!(3,628,800개)의 조합을 계산하게된다
Greedy 검색 알고리즘
Greedy 검색 알고리즘은 Exhaustive 검색 알고리즘의 단점을 해결하기 위해 도입된 최적화 기법이다.
위의 그림은 아래와 같은 순서로 동작한다.
- N개의 테이블 중 optimizer_search_depth 시스템변수에 정의된 수의 테이블로 가능한 조인조합 생성
- 생성된 조인 조합 중 최소 비용의 실행 계획 선정
- 선정된 실행 계획의 첫 번째 테이블을 부분 실행 계획 의 첫 번째 테이블로 선정
- N-1개의 테이블 중 optimizer_search_depth 시스템변수에 정의된 수의 테이블로 가능한 조인조합 생성
- 생성된 조인 조합들을 하나씩 부분 실행 계획 에 대입해 비용 계산
- 비용 계산 결과 중 최적의 실행 계획의 두 번째 테이블을 부분 실행 계획 의 두 번째 테이블로 선정
- 4 ~ 6번 반복
Greedy 검색 알고리즘은 optimizer_search_depth 시스템 변수의 값에 따라 비용이 상당히 차이가 날 수 있다. (기본 값 : 62)
다음 글: https://gojs.tistory.com/42
MySQL에서 쿼리 힌트 사용하기
옵티마이저는 비즈니스를 100% 이해하지 못하기 때문에 개발자나 DBA가 옵티마이저에게 더 좋은 계획을 알려줘야하는 경우가 있다.이 때 다양한 힌트를 제공함으로써 보다 나은 계획을 세울 수
gojs.tistory.com
'공부 > MySQL' 카테고리의 다른 글
| MySQL 쿼리 힌트 사용하기 (0) | 2024.07.19 |
|---|---|
| MySQL 옵티마이저 동작 원리 알아보기 (0) | 2024.07.17 |
| MySQL 트랜잭션 및 잠금 동작 원리 (0) | 2024.07.16 |
| MySQL InnoDB 엔진 알아보기 (0) | 2024.07.15 |
| MySQL 서버 구조 및 MySQL Engine 알아보기 (0) | 2024.07.15 |