2022-12-08 @이영훈
루스 스캔
세미 조인 서브쿼리 최적화의 LooseScan은 인덱스를 사용하는 GROUP BY 최적화 방법에서 살펴본 “Using index for group-by”의 루스 인덱스 스캔 (Loose Index Scan)과 비슷한 읽기 방식을 사용한다.
다음 쿼리는 dept_emp 테이블에 존재하는 모든 부서 번호에 대해 부서 정보를 읽어 오기 위한 쿼리다.
EXPLAIN
SELECT * FROM departments d WHERE d.dept_no IN (
SELECT de.dept_no FROM dept_emp de
);
SQL
복사
departments 테이블의 레코드 건수는 9건 밖에 되지 않지만 dept_emp 테이블의 레코드 건수는 무려 33만건 가까이 저장돼 있다. 그런데 dept_emp 테이블에는 (dept_no + emp_no)칼럼의 조합으로 프라이머리 키 인덱스가 만들어져 있다.
이 프라이머리 키는 전체 레코드 수는 33만 건 정도 있지만 dept_no만으로 그루핑해서 보면 결국 9건 밖에 ㅇ벗다는 것을 알 수 있다.
그렇다면 dept_emp 테이블의 프라이머리 키를 루스 인덱스 스캔으로 유니크한 dept_no만 읽으면 아주 효율적으로 서브쿼리 부분을 실행할 수 있다. 중복된 레코드까지 제거해서.
서브쿼리 사용된 dept_emp 테이블이 드라이빙 테이블로 실행되며, dept_emp 테이블의 프라이머리 키를 dept_no 부분에서 유니크하게 한 건씩만 읽고 있다는 것을 보여준다.
id | table | type | key | rows | Extra |
1 | de | index | PRIMARY | 331143 | Using index; LooseScan |
1 | d | eq_ref | PRIMARY | 1 | NULL |
•
실행 계획으로 Extra 칼럼에 “LooseScan”
•
각 테이블에 할당된 id 칼럼의 값이 동일하게 1이라는 것도 MySQL 내부적으로는 조인처럼 처리
LooseScan 최적화의 특성
•
LooseScan 최적화는 루스 인덱스 스캔으로 서브쿼리 테이블을 읽고, 그 다음으로 아우터 테이블을 드리븐으로 사용해서 조인을 수행한다.
•
그래서 서브쿼리 부분이 루스 인덱스 스캔을 사용할 수 있는 조건이 갖춰져야 사용할 수 있는 최적화다.
구체화 (Materialization)
Materialization 최적화는 세미 조인에 사용된 서브쿼리를 통째로 구체화해서 쿼리를 최적화한다는 의미다.
구체화(Materialization)는 쉽게 표현하면 내부 임시 테이블을 생성한다는 것의 의미한다.
다음은 1995년 1월 1일 조직이 변경된 사원들의 목록을 조회하는 쿼리는 IN (subquery) 포맷의 세미 조인을 사용하는 예제다.
EXPLAIN
SELECT *
FROM employees e
WHERE e.emp_no IN (SELECT de.dep_no
FROM dept_emp de
WHERE de.from_date = '1995-01-01');
SQL
복사
이 쿼리는 FirstMatch 최적화를 사용하면 employees 테이블에 대한 조건이 서브쿼리 이외에는 아무것도 없기 때문에 employees 테이블을 풀 스캔해야 할 것이다.
그래서 이런 형태의 세미 조인에서는 First Match 최적화가 성능 향상에 별로 도움이 되지 않는다.
MySQL 서버 옵티마이저는 이런 형태의 쿼리를 위해 서브쿼리 구체화(Subquery Materialization)라는 최적화를 도입했다.
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 |
dept_emp 테이블을 읽는 서브쿼리가 먼저 실행되어서 그 결과로 임시 테이블 <subquery2>이 만들어졌다.
그리고 최종적으로 서브쿼리가 구체화된 임시 테이블 <subquery2>과 employees 테이블을 조인해서 결과를 반환한다.
Materialization 최적화는 다른 서브쿼리 최적화는 달리, 서브쿼리 내에 GROUP BY절이 있어도 최적화 전략을 사용할 수 있다. (임시 테이블을 만들기 때문에)
Materialization 최적화의 특성
•
IN (subquery)에서 서브쿼리는 상관 서브쿼리(Correlated subquery)가 아니어야 한다.
•
서브쿼리는 GROUP BY나 집합 함수들이 사용돼도 구체화를 사용할 수 있다.
•
구체화가 사용된 경우에는 내부 임시테이블이 사용된다.
중복 제거(Duplicated Weed-out)
Duplicate Weedout은 세미 조인 서브쿼리를 일반적인 INNER JOIN 쿼리로 바꿔서 실행하고 마지막에 중복된 레코드를 제거하는 방법으로 처리되는 최적화 알고리즘이다.
다음 쿼리는 급여가 150,000 이상인 사원들의 정보를 조회하는 쿼리다.
EXPLAIN
SELECT *
FROM employees e
WHERE e.emp_no IN (SELECT s.emp_no FROM salaries s WHERE s.salary > 150000);
SQL
복사
salaries 테이블의 프라이머리 키가 (emp_no + from_date)이므로 salary가 150000 이상인 레코드를 salaries 테이블에서 조회하면 그 결과에는 중복된 emp_no가 발생할 수 있다.
그래서 이 쿼리를 다음과 같이 재작성해서 GROUP BY 절에 넣어주면 위의 세미 조인 서브쿼리와 동일한 결과를 얻을 수 있다.
SELECT e.*
FROM employees e,
salaries s
WHERE e.emp_no = s.emp_no
AND s.salary > 150000
GROUP BY e.emp_no;
SQL
복사
1.
salaries 테이블의 ix_salary 인덱스를 스캔해서 salary가 150000보다 큰 사원을 검색해 employees 테이블 조인을 실행
2.
조인된 결과를 임시 테이블에 저장
3.
임시 테이블에 저장된 결과에서 emp_no 기준으로 중복 제거
4.
중복을 제거하고 남은 레코드를 최종적으로 반환
Duplicate Weedout 최적화를 이용한 예제 쿼리의 실행 계획은 다음과 같다. Extra 칼럼에 “Start temporary”와 “End temporary” 문구가 별도로 표기된 것을 확인할 수 있다. Start / End temporary 문구의 구간이 Duplicate Weedout 최적화 처리 과정이라고 보면 된다.
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 |
Duplicate Weedout 최적화의 특징
•
서브쿼리가 상관 서브쿼리라고 하더라도 사용할 수 있는 최적화다.
•
서브쿼리가 GROUP BY나 집합 함수가 사용된 경우에는 사용될 수 없다.
•
서브쿼리의 테이블을 조인으로 처리하기 때문에 최적화할 수 있는 방법이 많다.
컨디션 팬아웃(condition_fanout_filter)
조인을 실행할 때 테이블의 순서는 쿼리의 성능에 매우 큰 영향을 미친다.
예를 들어, A 테이블과 B 테이블을 조인할 때 A 테이블에는 조건에 일치하는 레코드가 1만 건이고, B 테이블에는 일치하는 레코드 건수가 10건이라고 가정해보자. 이때 A 테이블을 조인의 드라이빙 테이블로 결정하면 B 테이블을 1만번 읽어야 한다.
그래서 MySQL 옵티마이저는 여러 테이블이 조인되는 경우 가능하다면 일치하는 레코드 건수가 적은 순서대로 조인을 실행한다.
다음 쿼리는 employees 테이블에서 이름이 ‘Matt’이면서 입사 일자가 ‘1985-11-21’부터 ‘1986-11-21’일 사이인 사원을 검색해 해당 사원의 급여를 조회하는 쿼리다.
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';
SQL
복사
condition_fanout_filter 옵티마이저 옵션을 비활성화한 상태에서 실행 계획을 살펴보자.
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 |
실행 계획에 의하면
1.
employees 테이블에서 ix_firstname 인덱스를 이용해 ‘Matt’ 조건에 일치하는 233건 레코드를 검색한다.
2.
검색된 233건 레코드 중에서 hire_date가 ‘1985-11-21’부터 ‘1986-11-21’일 사이인 레코드만 걸러내는데, filtered 칼럼의 값이 100인 것은 옵티마이저가 233건 모두 hire_date 칼럼의 조건을 만족할 것으로 예측했다는 것을 의미한다.
3.
employees 테이블을 읽은 결과 233건에 대해 salaries 테이블의 프라이머리 키를 이용해 salaries 테이블의 레코드를 읽는다. 이때 MySQL 옵티마이저는 employees 테이블의 레코드는 한 건당 salaries 테이블의 레코드는 10건이 일치할 것으로 예상했다.
condition_fanout_filter 최적화를 활성화한 상태의 실행 계획과 비교해보자
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 |
rows 칼럼의 값은 233으로 동일하다. 하지만 filtered 칼럼의 값이 100%가 아니라 23.2%로 변경됐다.
인덱스를 사용할 수 있는 first_name 칼럼 조건 이외의 나머지 조건(hire_date 칼럼의 조건)에 대해서도 얼마나 조건을 충족할지를 고려했다는 뜻이다.
employees 테이블에서 54건 (233 * 0.2320)만 조건을 충족할 것이라고 예측했다.
condition_fanout_filter 최적화가 filtered 칼럼 값을 예측하는 방법
•
WHERE 조건절에 사용된 칼럼에 인덱스가 있는 경우
•
WHERE 조건절에 사용된 칼럼에 히스토그램이 있는 경우
쿼리가 실제 실행되는 경우 first_name = ‘Matt’ 조건을 위한 ix_firstname 인덱스만 사용한다.
하지만 실행 계획을 수립하는 경우에는 first_name 칼럼의 인덱스를 이용해 first_name = ‘Matt’ 조건에 일치하는 레코드 건수가 대략 233건 정도라는 것을 알아내고, hire_date 칼럼의 조건을 만족하는 레코드의 비율이 대략 23.2%일 것으로 예측한다.
employees 테이블의 hire_date 칼럼의 인덱스가 없었다면 MySQL 옵티마이저는 first_name 칼럼의 인덱스를 이용해 hire_date 칼럼의 분포도를 살펴보고 filtered 칼럼의 값을 예측한다.
테이블이나 인덱스의 통계 정보만 사용하는 것이 아니라 다음의 순서대로 사용 가능한 방식을 선택한다.
1. 레인지 옵티마이저(Range optimizer)를 이용한 예측
2. 히스토그램을 이용한 예측
3. 인덱스 통계를 이용한 예측
4. 추측에 기반한 예측(Guesstimates)
레인지 옵티마이저는 실제 인덱스의 데이터를 살펴보고 레코드 건수를 예측하는 방식인데, 실제 쿼리가 실행되기도 전에 실행 계획을 수립 단계에서 빠르게 소량의 데이터를 읽어보는 것이다. 레인지 옵티마이저는 인덱스를 이용해서 쿼리가 실행될 수 있을 때만 사용된다.