MySQL 옵티마이저!
MySQL 옵티마이저
쿼리를 최적으로 실행하기 위해 실행계획을 수립하는 작업을 수행한다.
아래 2가지 종류가있는데 대부분 DBMS 옵티마이저는 CBO 방식을 사용한다.
- 비용 기반 최적화(CBO: Cost Based Optimizer)
- 규칙 기반 최적화(RBO: Rule Based Optimizer)
RBO 의 경우 테이블 사이즈나 분포도를 고려하지 않고 룰 베이스로 실행계획을 수립하기 때문에 항상 동일한 실행계획을 만들어 내지만, 통계적 방식을 같이 사용하는 CBO 보다 좋은 성능을 내기 어렵다.
EXPLAIN
EXPLAIN 키워드를 사용해 실제 쿼리는 수행하지 않으면서 실행 계획을 확인할 수 있다.
쿼리문장에서 사용한 테이블의 개수(임시테이블 포함) 만큼 출력된다.
위에서 아래로 순서대로 실행된다.
EXPLAIN
SELECT e.hire_date, avg(s.salary)
FROM employees e
INNER JOIN salaries s ON s.emp_no = e.emp_no
AND s.salary > 50000
AND s.from_date <= '1990-01-01'
AND s.to_date > '1990-01-01'
WHERE e.first_name = 'Matt'
GROUP BY e.hire_date
/*
+--+-----------+-----+----------+----+------------+-------+------------------+----+--------+---------------+
|id|select_type|table|partitions|type|key |key_len|ref |rows|filtered|Extra |
+--+-----------+-----+----------+----+------------+-------+------------------+----+--------+---------------+
|1 |SIMPLE |e |NULL |ref |ix_firstname|58 |const |233 |100 |Using temporary|
|1 |SIMPLE |s |NULL |ref |PRIMARY |4 |employees.e.emp_no|9 |5.55 |Using where |
+--+-----------+-----+----------+----+------------+-------+------------------+----+--------+---------------+
*/
id
WITH SELECT, INNER SELECT 등, SELECT 쿼리가 늘어나면 id 필드도 증가.
select_type
SELECT 쿼리가 어떤 타입의 쿼리인지 출력
EXPLAIN
SELECT *
FROM employees e
WHERE e.emp_no IN
(SELECT emp_no FROM salaries WHERE salary BETWEEN 100 AND 1000);
/*
+--+------------+-----------+----------+------+---------+-------+------------------+----+--------+------------------------+
|id|select_type |table |partitions|type |key |key_len|ref |rows|filtered|Extra |
+--+------------+-----------+----------+------+---------+-------+------------------+----+--------+------------------------+
|1 |SIMPLE |<subquery2>|NULL |ALL |NULL |NULL |NULL |NULL|100 |NULL |
|1 |SIMPLE |e |NULL |eq_ref|PRIMARY |4 |<subquery2>.emp_no|1 |100 |NULL |
|2 |MATERIALIZED|salaries |NULL |range |ix_salary|4 |NULL |1 |100 |Using where; Using index|
+--+------------+-----------+----------+------+---------+-------+------------------+----+--------+------------------------+
*/
- SIMPLE: 서브쿼리를 사용하지 않는 단순 SELECT 쿼리
- MATERIALIZED:
MySQL 5.6이후 지원, 서브쿼리의 내용을 임시테이블로 구체화(Materialization)한 테이블, 조인에 사용된다.
MySQL 5.6전에는 employees 테이블 레코드마다 salaries 테이블을 읽는 서브쿼리가 실행되는 형태로 처리됐다.
EXPLAIN
SELECT *
FROM (SELECT de.emp_no FROM dept_emp de GROUP BY de.emp_no) tb,
employees e
WHERE e.emp_no = tb.emp_no;
/*
+--+-----------+----------+------+-----------------+---------+------+--------+-----------+
|id|select_type|table |type |key |ref |rows |filtered|Extra |
+--+-----------+----------+------+-----------------+---------+------+--------+-----------+
|1 |PRIMARY |<derived2>|ALL |NULL |NULL |331143|100 |NULL |
|1 |PRIMARY |e |eq_ref|PRIMARY |tb.emp_no|1 |100 |NULL |
|2 |DERIVED |de |index |ix_empno_fromdate|NULL |331143|100 |Using index|
+--+-----------+----------+------+-----------------+---------+------+--------+-----------+
*/
-- 가장 높은 salary 2건 가져와서 출력
EXPLAIN
SELECT *
FROM employees e
LEFT JOIN LATERAL (
SELECT * FROM salaries s
WHERE s.emp_no=e.emp_no
ORDER BY s.from_date DESC LIMIT 2) AS s2
ON s2.emp_no=e.emp_no;
/*
+--+-----------------+----------+----+-----------+------------------+------+--------+--------------------------+
|id|select_type |table |type|key |ref |rows |filtered|Extra |
+--+-----------------+----------+----+-----------+------------------+------+--------+--------------------------+
|1 |PRIMARY |e |ALL |NULL |NULL |300363|100 |Rematerialize (<derived2>)|
|1 |PRIMARY |<derived2>|ref |<auto_key0>|employees.e.emp_no|2 |100 |NULL |
|2 |DEPENDENT DERIVED|s |ref |PRIMARY |employees.e.emp_no|9 |100 |Using filesort |
+--+-----------------+----------+----+-----------+------------------+------+--------+--------------------------+
*/
- PRIMARY: 서브쿼리를 가지는 SELECT 쿼리, 가장 Outer 쿼리
- DERIVED: 대부분 서브쿼리의 실행 결과로 메모리나 디스크에 생성되는 임시테이블(파생테이블)
MySQL 8.0부턴DERIVED로 만들어질법한 테이블을JOIN으로 자동최적화 되는 경우가 있다.
오래된 MySQL 모델에서는 최적화가 미숙하기에DERIVED가 생성되는 서브쿼리사용보단JOIN사용을 권장한다.
- DEPENDENT DERIVED:
MySQL 8.0부터래터럴조인(LATERAL JOIN)기능이 추가되면서 지원, 외부쿼리와 연계가능하다.
래터럴조인: 동적서브쿼리라 부르기도 함, row 마다 서브쿼리가 실행되어 성능에는 좋지 않지만 동적으로 쿼리를 실행하여 값을 가져올 수 있음.
EXPLAIN
SELECT e.first_name,
(SELECT COUNT(*)
FROM dept_emp de,
dept_manager dm
WHERE dm.dept_no = de.dept_no) AS cnt
FROM employees e
WHERE e.emp_no = 10001;
/*
+--+-----------+-----+-----+-------+-------+--------------------+-----+--------+-----------+
|id|select_type|table|type |key |key_len|ref |rows |filtered|Extra |
+--+-----------+-----+-----+-------+-------+--------------------+-----+--------+-----------+
|1 |PRIMARY |e |const|PRIMARY|4 |const |1 |100 |NULL |
|2 |SUBQUERY |dm |index|PRIMARY|20 |NULL |24 |100 |Using index|
|2 |SUBQUERY |de |ref |PRIMARY|16 |employees.dm.dept_no|41392|100 |Using index|
+--+-----------+-----+-----+-------+-------+--------------------+-----+--------+-----------+
*/
EXPLAIN
SELECT e.first_name,
(SELECT COUNT(*)
FROM dept_emp de,
dept_manager dm
WHERE dm.dept_no = de.dept_no
AND de.emp_no = e.emp_no) AS cnt
FROM employees e
WHERE e.first_name = 'Matt';
/*
+--+------------------+-----+----+-----------------+-------+--------------------+----+--------+-----------+
|id|select_type |table|type|key |key_len|ref |rows|filtered|Extra |
+--+------------------+-----+----+-----------------+-------+--------------------+----+--------+-----------+
|1 |PRIMARY |e |ref |ix_firstname |58 |const |233 |100 |Using index|
|2 |DEPENDENT SUBQUERY|de |ref |ix_empno_fromdate|4 |employees.e.emp_no |1 |100 |Using index|
|2 |DEPENDENT SUBQUERY|dm |ref |PRIMARY |16 |employees.de.dept_no|2 |100 |Using index|
+--+------------------+-----+----+-----------------+-------+--------------------+----+--------+-----------+
*/
- SUBQUERY: FROM 절 이외에서 사용되는 서브쿼리만을 의미. FROM 절에 사용된 서브쿼리는 파생테이블로
DERIVED로 표기된다. - DEPENDENT SUBQUERY:
SUBQUERY가 바깥 SELECT 쿼리 칼럼에 의존적일 경우, 외부쿼리 수행 후 서브쿼리가 실행되기에 처리속도가 느리다.
만들어진 SUBQUERY 는 조회결과가 캐시로 저장되고, DEPENDENT SUBQUERY 는 조건별로 조회결과가 캐시로 저장된다.
EXPLAIN
SELECT *
FROM ((SELECT emp_no FROM employees e1 LIMIT 10)
UNION ALL
(SELECT emp_no FROM employees e2 LIMIT 10)
UNION ALL
(SELECT emp_no FROM employees e3 LIMIT 10)) tb;
/*
+--+-----------+----------+-----+-----------+-------+----+------+--------+-----------+
|id|select_type|table |type |key |key_len|ref |rows |filtered|Extra |
+--+-----------+----------+-----+-----------+-------+----+------+--------+-----------+
|1 |PRIMARY |<derived2>|ALL |NULL |NULL |NULL|30 |100 |NULL |
|2 |DERIVED |e1 |index|ix_hiredate|3 |NULL|300363|100 |Using index|
|3 |UNION |e2 |index|ix_hiredate|3 |NULL|300363|100 |Using index|
|4 |UNION |e3 |index|ix_hiredate|3 |NULL|300363|100 |Using index|
+--+-----------+----------+-----+-----------+-------+----+------+--------+-----------+
*/
EXPLAIN
SELECT *
FROM employees e1
WHERE e1.emp_no IN (
SELECT emp_no FROM employees e2 WHERE e2.first_name = 'Matt'
UNION
SELECT emp_no FROM employees e3 WHERE e3.last_name = 'Matt');
/*
+--+------------------+----------+------+-------+-------+----+------+--------+---------------+
|id|select_type |table |type |key |key_len|ref |rows |filtered|Extra |
+--+------------------+----------+------+-------+-------+----+------+--------+---------------+
|1 |PRIMARY |e1 |ALL |NULL |NULL |NULL|300363|100 |Using where |
|2 |DEPENDENT SUBQUERY|e2 |eq_ref|PRIMARY|4 |func|1 |5 |Using where |
|3 |DEPENDENT UNION |e3 |eq_ref|PRIMARY|4 |func|1 |10 |Using where |
|4 |UNION RESULT |<union2,3>|ALL |NULL |NULL |NULL|NULL |NULL |Using temporary|
+--+------------------+----------+------+-------+-------+----+------+--------+---------------+
*/
- UNION: UNION 으로 결합하는 단위 SELECT 쿼리, 첫번째
UNION은 결과들을 모아서 저장하는 임시 테이블(DERIVED)로 처리된다. - DEPENDENT UNION: 내부의 UNION 쿼리가 외부에 영향을 받을 경우.
- UNION RESULT: UNION 결과를 담아두는 임시테이블.
위 예제 DEPENDENT UNION 의 경우 IN 서브쿼리가 먼저 수행되지 않고 외부 SELECT 쿼리가 먼저 수행 된 후 자동으로 JOIN 처리되어 내부쿼리를 수행시킨다.
e2.emp_no=e1.emp_no, e3.emp_no=e1.emp_no 조건이 자동으로 추가된다.
table
테이블 이름 혹은 alias 를 출력.
EXPLAIN
SELECT *
FROM (SELECT de.emp_no FROM dept_emp de GROUP BY de.emp_no) tb,
employees e
WHERE e.emp_no = tb.emp_no;
/*
+--+-----------+----------+------+-----------------+-------+---------+------+--------+-----------+
|id|select_type|table |type |key |key_len|ref |rows |filtered|Extra |
+--+-----------+----------+------+-----------------+-------+---------+------+--------+-----------+
|1 |PRIMARY |<derived2>|ALL |NULL |NULL |NULL |331143|100 |NULL |
|1 |PRIMARY |e |eq_ref|PRIMARY |4 |tb.emp_no|1 |100 |NULL |
|2 |DERIVED |de |index |ix_empno_fromdate|7 |NULL |331143|100 |Using index|
+--+-----------+----------+------+-----------------+-------+---------+------+--------+-----------+
*/
<derived N>, <union M> 과 같은 명칭은 임시테이블을 뜻한다. 뒤의 정수는 파생테이블의 id 값.
위의 경우 id=2 DERIVED 테이블로부터 검색하기 때문에 <derived2> 테이블명을 사용
partitions
테이블의 파티셔닝을 통해 조회할 경우 파티션 명칭을 출력.
CREATE TABLE employees_2
(
emp_no int NOT NULL,
birth_date DATE NOT NULL,
first_name VARCHAR(14) NOT NULL,
last_name VARCHAR(16) NOT NULL,
gender ENUM ('M','F') NOT NULL,
hire_date DATE NOT NULL,
PRIMARY KEY (emp_no, hire_date)
) PARTITION BY RANGE COLUMNS (hire_date) (
PARTITION p1986_1990 VALUES LESS THAN ('1990-01-01'),
PARTITION p1991_1995 VALUES LESS THAN ('1996-01-01'),
PARTITION p1996_2000 VALUES LESS THAN ('2000-01-01'),
PARTITION p2001_2005 VALUES LESS THAN ('2006-01-01'));
EXPLAIN
SELECT *
FROM employees_2
WHERE hire_date BETWEEN '1999-11-15' AND '2000-01-15';
/*
+--+-----------+-----------+---------------------+----+----+----+----+--------+-----------+
|id|select_type|table |partitions |type|key |ref |rows|filtered|Extra |
+--+-----------+-----------+---------------------+----+----+----+----+--------+-----------+
|1 |SIMPLE |employees_2|p1996_2000,p2001_2005|ALL |NULL|NULL|1 |100 |Using where|
+--+-----------+-----------+---------------------+----+----+----+----+--------+-----------+
*/
type
인덱스를 사용했는지 여부 확인. ALL 을 제외한 나머지 값은 모두 인덱스를 사용하는 접근 방법.
-- auto-generated definition
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)
) collate = utf8mb4_general_ci;
create index ix_empno_fromdate on dept_emp (emp_no, from_date);
create index ix_fromdate on dept_emp (from_date);
EXPLAIN
SELECT *
FROM dept_emp
WHERE dept_no = 'd005' AND emp_no = 10001;
/*
+--+-----------+--------+-----+-------+----+--------+-----+
|id|select_type|table |type |key |rows|filtered|Extra|
+--+-----------+--------+-----+-------+----+--------+-----+
|1 |SIMPLE |dept_emp|const|PRIMARY|1 |100 |NULL |
+--+-----------+--------+-----+-------+----+--------+-----+
*/
EXPLAIN
SELECT *
FROM dept_emp
WHERE dept_no = 'd005';
/*
+--+-----------+--------+----+-------+------+--------+-----+
|id|select_type|table |type|key |rows |filtered|Extra|
+--+-----------+--------+----+-------+------+--------+-----+
|1 |SIMPLE |dept_emp|ref |PRIMARY|165571|100 |NULL |
+--+-----------+--------+----+-------+------+--------+-----+
*/
- const:
유니크 인덱스 스캔(UNIQUE INDEX SCAN)이라고도 함. 인덱스를 통해 반드시 1건을 반환하는 쿼리의 처리 방식 - ref: 인덱스 혹은 인덱스의 일부만 사용
EXPLAIN
SELECT *
FROM dept_emp de,
employees e
WHERE e.emp_no = de.emp_no
AND de.dept_no = 'd005';
/*
+--+-----------+-----+------+-------+------+--------+-----+
|id|select_type|table|type |key |rows |filtered|Extra|
+--+-----------+-----+------+-------+------+--------+-----+
|1 |SIMPLE |de |ref |PRIMARY|165571|100 |NULL |
|1 |SIMPLE |e |eq_ref|PRIMARY|1 |100 |NULL |
+--+-----------+-----+------+-------+------+--------+-----+
*/
- eq_ref: 드라이빙 테이블의 칼럼값을 드리븐된 테이블의
프라이머리 키나유니크 키검색조건으로 사용한 접근방법.
EXPLAIN
SELECT *
FROM titles
WHERE to_date = '1985-03-01'
OR to_date IS NULL;
/*
+--+-----------+------+-----------+-----+----+--------+------------------------+
|id|select_type|table |type |ref |rows|filtered|Extra |
+--+-----------+------+-----------+-----+----+--------+------------------------+
|1 |SIMPLE |titles|ref_or_null|const|2 |100 |Using where; Using index|
+--+-----------+------+-----------+-----+----+--------+------------------------+
*/
- ref_or_null: ref 접근 방법에 null 비교가 추가된 접근방법.
EXPLAIN
SELECT *
FROM dept_emp
WHERE dept_no IN ('d001', 'd002');
/*
+--+-----------+--------+-----+-------+-----+--------+-----------+
|id|select_type|table |type |key |rows |filtered|Extra |
+--+-----------+--------+-----+-------+-----+--------+-----------+
|1 |SIMPLE |dept_emp|range|PRIMARY|72300|100 |Using where|
+--+-----------+--------+-----+-------+-----+--------+-----------+
*/
- range:
[<, >, IS NULL, BETWEEN, IN, LIKE]등의 연산자를 사용해 인덱스를 검색,
EXPLAIN
SELECT *
FROM dept_emp
ORDER BY from_date DESC
LIMIT 10;
/*
+--+-----------+--------+-----+-----------+----+----+--------+-------------------+
|id|select_type|table |type |key |ref |rows|filtered|Extra |
+--+-----------+--------+-----+-----------+----+----+--------+-------------------+
|1 |SIMPLE |dept_emp|index|ix_fromdate|NULL|10 |100 |Backward index scan|
+--+-----------+--------+-----+-----------+----+----+--------+-------------------+
*/
- index: 인덱스를 처음부터 끝까지 읽는
인덱스 풀 스캔을 의미,LIMIT조건과 효율적으로 사용 가능하다.
EXPLAIN
SELECT *
FROM employees
WHERE first_name = 'Georgi' -- ix_firstname
AND emp_no BETWEEN 10000 AND 20000; -- PRIMARY
/*
+--+-----------+---------+-----------+--------------------+-------+----+----+--------+--------------------------------------------------+
|id|select_type|table |type |key |key_len|ref |rows|filtered|Extra |
+--+-----------+---------+-----------+--------------------+-------+----+----+--------+--------------------------------------------------+
|1 |SIMPLE |employees|index_merge|ix_firstname,PRIMARY|62,4 |NULL|1 |100 |Using intersect(ix_firstname,PRIMARY); Using where|
+--+-----------+---------+-----------+--------------------+-------+----+----+--------+--------------------------------------------------+
*/
- index_merge: 인데스 여러개를 사용해 조회한 후 병합하는 방법.
key, key_len
EXPLAIN
SELECT *
FROM dept_emp
WHERE dept_no = 'd001';
/*
+--+-----------+--------+----+-------+-------+-----+------+--------+-----+
|id|select_type|table |type|key |key_len|ref |rows |filtered|Extra|
+--+-----------+--------+----+-------+-------+-----+------+--------+-----+
|1 |SIMPLE |dept_emp|ref |PRIMARY|16 |const|165571|100 |NULL |
+--+-----------+--------+----+-------+-------+-----+------+--------+-----+
*/
EXPLAIN
SELECT *
FROM dept_emp
WHERE dept_no = 'd001'
AND emp_no = '10877'
/*
+--+-----------+--------+-----+-------+-------+-----------+----+--------+-----+
|id|select_type|table |type |key |key_len|ref |rows|filtered|Extra|
+--+-----------+--------+-----+-------+-------+-----------+----+--------+-----+
|1 |SIMPLE |dept_emp|const|PRIMARY|20 |const,const|1 |100 |NULL |
+--+-----------+--------+-----+-------+-------+-----------+----+--------+-----+
*/
key 칼럼은 최종 선택된 실행 계획에서 사용하는 인덱스를 의미,
쿼리 튜닝시 key 칼럼에 의도했던 인덱스가 표시되는지 확인해야한다.
key_len 칼럼은 복합 인덱스 에서 몇 개 칼럼까지 사용했는지 알려준다.
dept_no 칼럼의 타입이 CHAR(4) utf8mb4 이기 때문에 앞쪽 16 바이트만 유효하게 사용했다는 의미
(dept_no, emp_no) 복합 인덱스 구조이기에 key_len 이 20 까지 늘어날 수 있다.
possible_keys의 경우 인덱스 사용 후보로 실행계획과는 무관하다.
ref
EXPLAIN
SELECT *
FROM employees e,
dept_emp de
WHERE e.emp_no = de.emp_no;
/*
+--+-----------+-----+------+-------+-------+--------------+------+--------+-----+
|id|select_type|table|type |key |key_len|ref |rows |filtered|Extra|
+--+-----------+-----+------+-------+-------+--------------+------+--------+-----+
|1 |SIMPLE |de |ALL |NULL |NULL |NULL |322846|100 |NULL |
|1 |SIMPLE |e |eq_ref|PRIMARY|4 |demo.de.emp_no|1 |100 |NULL |
+--+-----------+-----+------+-------+-------+--------------+------+--------+-----+
*/
EXPLAIN
SELECT *
FROM employees e,
dept_emp de
WHERE e.emp_no = de.emp_no-1;
/*
+--+-----------+-----+------+-------+-------+----+------+--------+-----------+
|id|select_type|table|type |key |key_len|ref |rows |filtered|Extra |
+--+-----------+-----+------+-------+-------+----+------+--------+-----------+
|1 |SIMPLE |de |ALL |NULL |NULL |NULL|322846|100 |NULL |
|1 |SIMPLE |e |eq_ref|PRIMARY|4 |func|1 |100 |Using where|
+--+-----------+-----+------+-------+-------+----+------+--------+-----------+
*/
ref 칼럼은 비교조건(equal)로 어떤 유형값이 사용되었는지 표시, 상수값을 지정했으면 const, 다른 테이블의 칼럼값이면 그 테이블명과 칼럼명이 표시된다.
MySQL 서버 내부적으로 값을 변환한 경우 func 으로 표시된다. JOIN 조건 타입의 형 변환에서 자주 일어난다.
rows, filtered
EXPLAIN
SELECT *
FROM employees e
LEFT JOIN salaries s on e.emp_no = s.emp_no
WHERE e.first_name = 'Matt' -- ix_firstname 인덱스 사용
AND e.hire_date BETWEEN '1990-01-01' AND '1991-01-01'
AND s.from_date BETWEEN '1990-01-01' AND '1991-01-01'
AND s.salary BETWEEN 50000 AND 60000; -- ix_salary 인덱스 사용
/*
+--+-----------+-----+----+------------+-------+-------------+----+--------+-----------+
|id|select_type|table|type|key |key_len|ref |rows|filtered|Extra |
+--+-----------+-----+----+------------+-------+-------------+----+--------+-----------+
|1 |SIMPLE |e |ref |ix_firstname|58 |const |233 |16.68 |Using where|
|1 |SIMPLE |s |ref |PRIMARY |4 |demo.e.emp_no|9 |5.14 |Using where|
+--+-----------+-----+----+------------+-------+-------------+----+--------+-----------+
*/
rows 칼럼은 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수이다.
각 처리 방식이 얼마나 많은 레코드를 읽고 비교하는지 통계정보를 기준으로 조사해서 예측하고 비용을 산정한다.
옵티마이저가 산출해 낸 예상값이라서 정확하지 않다.
filtered 칼럼은 인덱스를 사용한 결과에서 일치하는 레코드 건수이다.
옵티마이저가 employees 를 드라이빙 테이블로, ix_firstname 인덱스를 첫번째 검색조건으로 선정하였고,
해당 인덱스를 통해 검색된 레코드에 AND 조건으로 추가 필터링해야 하는데, 이때 16.68% 레코드가 필터링 되었다.
filtered 칼럼이 정확할수록 높은 효율의 JOIN 실행계획을 수립할 수 있다.
자동생성된 통계정보로는
filtered값을 예측하기 힘들어히스토그램방식이 도입되었다.
EXPLAIN ANALYZE
EXPLAIN ANALYZE 키워드를 사용하면 실제 쿼리를 실행하고 각 단계별 코스트, 실행시간을 알 수 있다.
EXPLAIN ANALYZE
SELECT e.hire_date, avg(s.salary)
FROM employees e
INNER JOIN salaries s ON s.emp_no = e.emp_no
AND s.salary > 50000
AND s.from_date <= '1990-01-01'
AND s.to_date > '1990-01-01'
WHERE e.first_name = 'Matt'
GROUP BY e.hire_date
/*
A -> Table scan on <temporary> (actual time=4.94..4.95 rows=48 loops=1)
B -> Aggregate using temporary table (actual time=4.94..4.94 rows=48 loops=1)
C -> Nested loop inner join (cost=531 rows=124) (actual time=0.486..4.84 rows=48 loops=1)
D -> Index lookup on e using ix_firstname (first_name='Matt') (cost=88.3 rows=233) (actual time=0.465..1.24 rows=233 loops=1)
E -> Filter: ((s.salary > 50000) and (s.from_date <= DATE'1990-01-01') and (s.to_date > DATE'1990-01-01')) (cost=0.945 rows=0.532) (actual time=0.0142..0.0152 rows=0.206 loops=233)
F -> Index lookup on s using PRIMARY (emp_no=e.emp_no) (cost=0.945 rows=9.57) (actual time=0.0103..0.0128 rows=9.53 loops=233)
*/
계층형태로 실행목록이 출력되는데, 실행 순서는 D, F, E, C, B, A 순이다.
- D: employees 테이블의 first_name=’Matt’ 조건 레코드를 찾기, ix_firstname 인덱스 사용
- F: salaries 테이블의 PRIMARY 키를 통해 D번 결과의 emp_no와 동일한 레코드를 찾기
- E: ((s.salary > 50000) and … (s.to_date > DATE’1990-01-01’)) 조건에 일치하는 레코드 찾기
- C: D번, E번의 결과를 조인
- B: 임시 테이블에 결과를 저장하면서 GROUP BY 집계를 실행하고
- A: 임시 테이블의 결과를 읽어서 결과를 반환한다.
실행계획
MySQL 8.0 부터는 인덱스되지 않은 칼럼들에 대해서도 데이터 분포도를 수집해서 저장하는 통계정보가 도입되고 실행계획 수립에 필수적으로 사용된다.
통계정보는 테이블로 영구관리되고 테이블 생성시 STATS_PERSISTENT 옵셥을 통해 조절할 수 있다.
CREATE TABLE tab_persistent
(
fd1 INT PRIMARY KEY,
fd2 INT
) ENGINE = InnoDBxl
-- STATS_PERSISTENT=1; 기본 활성화
-- STATS_AUTO_RECALC
통계정보를 비활성화하고 싶다면 innodb_stats_persistent 글로벌 환경변수를 변경하면 된다.
SHOW GLOBAL VARIABLES LIKE 'innodb_stats_persistent';
/*
+-----------------------+-----+
|Variable_name |Value|
+-----------------------+-----+
|innodb_stats_persistent|ON |
+-----------------------+-----+
*/
통계정보
MySQL 5.6 부터 통계정보는 innodb_index_stats, innodb_table_stats 테이블에서 영구관리된다.
이전에는 메모리에서 통계정보를 관리했다.
SHOW TABLES FROM mysql LIKE '%_stats';
/*
+-------------------------+
|Tables_in_mysql (%_stats)|
+-------------------------+
|innodb_index_stats |
|innodb_table_stats |
+-------------------------+
*/
SELECT *
FROM mysql.innodb_index_stats
WHERE database_name = 'employees'
AND table_name = 'employees_comp4k';
/*
+------------+------------+----------+-----------+---------------------------------+
|index_name |stat_name |stat_value|sample_size|stat_description |
+------------+------------+----------+-----------+---------------------------------+
|PRIMARY |n_diff_pfx01|297514 |20 |emp_no | emp_no 유니크한 값의 개수
|PRIMARY |n_leaf_pages|2577 |NULL |Number of leaf pages in the index| 인덱스의 리프 노드 페이지 개수
|PRIMARY |size |2599 |NULL |Number of pages in the index | 인덱스 트리의 전체 페이지 개수
|ix_firstname|n_diff_pfx01|1168 |20 |first_name | first_name 유니크한 값의 개수
|ix_firstname|n_diff_pfx02|307132 |20 |first_name,emp_no | first_name,empno 유니크한 값의 개수
|ix_firstname|n_leaf_pages|598 |NULL |Number of leaf pages in the index| 인덱스의 리프 노드 페이지 개수
|ix_firstname|size |741 |NULL |Number of pages in the index | 인덱스 트리의 전체 페이지 개수
|ix_hiredate |n_diff_pfx01|4600 |20 |hire_date | hire_date 유니크한 값의 개수
|ix_hiredate |n_diff_pfx02|305650 |20 |hire_date,emp_no | hire_date,emp_no 유니크한 값의 개수
|ix_hiredate |n_leaf_pages|514 |NULL |Number of leaf pages in the index| 인덱스의 리프 노드 페이지 개수
|ix_hiredate |size |611 |NULL |Number of pages in the index | 인덱스 트리의 전체 페이지 개수
+------------+------------+----------+-----------+---------------------------------+
*/
SELECT *
FROM mysql.innodb_table_stats
WHERE database_name = 'employees'
AND table_name = 'employees_comp4k';
/*
+------+--------------------+------------------------+
|n_rows|clustered_index_size|sum_of_other_index_sizes|
+------+--------------------+------------------------+
|297514|2599 |1352 |
+------+--------------------+------------------------+
n_rows
- 테이블의 전체 레코드 건수
clustered_index_size
- 프라이머리 키의 크기(InnoDB 페이지 개수)
sum_of_other_index_sizes
- 프라이머리 키를 제외한 인덱스의 크기(InnoDB 페이지 개수)
*/
통계정보는 자동 갱신되며 많은 자원을 요구한다.
갱신되는 상황은 아래와 같다.
- 테이블이 생성시
- 테이블의 레코드가 대량으로 변경되는 경우
테이블의 전체 레코드 중에서 1/16 정도의 변경 - ANALYZE TABLE, SHOW TABLE STATUS, SHOW INDEX FROM 명령 실행시
- InnoDB 모니터가 활성화되는 경우
만약 자동갱신을 막고 싶다면 아래 글로벌변수를 변경.
SHOW GLOBAL VARIABLES LIKE 'innodb_stats_auto_recalc'
/*
+------------------------+-----+
|Variable_name |Value|
+------------------------+-----+
|innodb_stats_auto_recalc|ON |
+------------------------+-----+
*/
히스토그램
MySQL 8.0 부터 실제 테이블의 일부 페이지를 랜덤으로 가져와 데이터 분포도를 참조할 수 있는 히스토그램 정보를 활용하여 실행계획을 세울 수 있다.
-- 히스토그램 생성
ANALYZE TABLE employees UPDATE HISTOGRAM ON gender, hire_date;
/*
+-------------------+---------+--------+----------------------------------------------------+
|Table |Op |Msg_type|Msg_text |
+-------------------+---------+--------+----------------------------------------------------+
|employees.employees|histogram|status |Histogram statistics created for column 'gender'. |
|employees.employees|histogram|status |Histogram statistics created for column 'hire_date'.|
+-------------------+---------+--------+----------------------------------------------------+
*/
-- 히스토그램 조회
SELECT *
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = 'employees'
AND TABLE_NAME = 'employees'
/*
+-----------+----------+-----------+---------------------------+
|SCHEMA_NAME|TABLE_NAME|COLUMN_NAME|HISTOGRAM |
+-----------+----------+-----------+---------------------------+
|employees |employees |gender |{"buckets": [[1, 0.600 ... |
|employees |employees |hire_date |{"buckets": [["1985-01}... |
+-----------+----------+-----------+---------------------------+
*/
-- 히스토그램 삭제
ANALYZE TABLE employees DROP HISTOGRAM ON gender, hire_date;
/*
+-------------------+---------+--------+----------------------------------------------------+
|Table |Op |Msg_type|Msg_text |
+-------------------+---------+--------+----------------------------------------------------+
|employees.employees|histogram|status |Histogram statistics removed for column 'gender'. |
|employees.employees|histogram|status |Histogram statistics removed for column 'hire_date'.|
+-------------------+---------+--------+----------------------------------------------------+
*/
{
"buckets": [
["1985-01-01", "1985-02-28", 0.009904057292283582, 31],
["1985-03-01", "1985-03-27", 0.019799003216312994, 27],
...
["1997-12-01", "1998-07-21", 0.9901961677585123, 239],
["1998-07-22", "2000-01-13", 1.0, 489]
],
"data-type": "date",
"null-values": 0.0,
"collation-id": 8,
"last-updated": "2023-11-24 10:20:21.323284",
"sampling-rate": 0.34680259108700695,
"histogram-type": "equi-height",
"number-of-buckets-specified": 100
}
gender, hire_date 는 인덱스 필드가 아니지만 분포도를 참조할 수 있다.
- buckets: 단위로 구분되어 칼럼 범위별 누적 비율.
- sampling-rate: 스캔한 페이지의 비율, 전체 페이지의 34.6% 스캔.
- number-of-buckets-specified: buckets 길이.
히스토그램 정보가있으면 JOIN 시 테이블 순서 최적화를 분포도를 통해 처리할 수 있고, 데이터 형식에 따라 배 이상의 성능차이가 날 수 있다.
코스트 모델
EXPLAIN FORMAT = JSON
SELECT *
FROM employees
WHERE first_name = 'Matt';
/*
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "91.61"
},
"table": {
"table_name": "employees",
"access_type": "ref",
"possible_keys": [
"ix_firstname"
],
"key": "ix_firstname",
"used_key_parts": [
"first_name"
],
"key_length": "58",
"ref": [
"const"
],
"rows_examined_per_scan": 233,
"rows_produced_per_join": 233,
"filtered": "100.00",
"cost_info": {
"read_cost": "68.31",
"eval_cost": "23.30",
"prefix_cost": "91.61",
"data_read_per_join": "30K"
},
"used_columns": [
"emp_no",
"birth_date",
"first_name",
"last_name",
"gender",
"hire_date"
]
}
}
}
*/
인덱스 힌트
개발자나 DBA가 생각한 것과 다른 실행계획을 옵티마이저가 수립할 경우, 본래 생각했던 실행계획으로 유도하는 쿼리힌트 를 사용한다.
대부분 옵티마이저는 조인쿼리 수행시 row 수가 적은 테이블을 드라이빙으로, row 가 많은 테이블을 드리븐으로 선택한다.
전통적으로 쿼리힌트로 인덱스 힌트 를 사용해 왔으며 SELECT, UPDATE 명령에서 사용할 수 있다.
EXPLAIN
SELECT *
FROM employees e,
dept_emp de,
departments d
WHERE e.emp_no = de.emp_no
AND d.dept_no = de.dept_no;
/*
+--+-----------+-----+----------+------+-------+-------------------+-----+-----+
|id|select_type|table|partitions|type |key |ref |rows |Extra|
+--+-----------+-----+----------+------+-------+-------------------+-----+-----+
|1 |SIMPLE |d |NULL |ALL |NULL |NULL |9 |NULL |
|1 |SIMPLE |de |NULL |ref |PRIMARY|employees.d.dept_no|41392|NULL |
|1 |SIMPLE |e |NULL |eq_ref|PRIMARY|employees.de.emp_no|1 |NULL |
+--+-----------+-----+----------+------+-------+-------------------+-----+-----+
*/
옵티마이저에 의해 row 수가 가장 적은 departments 테이블이 드라이빙으로 선택되고 풀스캔으로 조회되고, 나머지 테이블은 드리븐으로 선택되었다.
STRAIGHT_JOIN
인덱스 힌트중 하나, 드라이빙 테이블을 입력된 순서대로 지정한다.
EXPLAIN
SELECT STRAIGHT_JOIN *
FROM employees e,
dept_emp de,
departments d
WHERE e.emp_no = de.emp_no
AND d.dept_no = de.dept_no;
/*
+--+-----------+-----+------+-----------------+--------------------+------+-----+
|id|select_type|table|type |key |ref |rows |Extra|
+--+-----------+-----+------+-----------------+--------------------+------+-----+
|1 |SIMPLE |e |ALL |NULL |NULL |300695|NULL |
|1 |SIMPLE |de |ref |ix_empno_fromdate|employees.e.emp_no |1 |NULL |
|1 |SIMPLE |d |eq_ref|PRIMARY |employees.de.dept_no|1 |NULL |
+--+-----------+-----+------+-----------------+--------------------+------+-----+
*/
USE INDEX
칼럼 여러개를 겹처 여러개의 인덱스를 생성해놓은 경우, 개발자가 원하는 인덱스를 옵티마이저가 선택하지 않을 수 있다.
사용하려는 인덱스를 가지는 테이블 뒤에 인덱스 힌트 를 명시해 어떤 인덱스를 사용할지 유도한다.
EXPLAIN
SELECT *
FROM dept_emp USE INDEX (PRIMARY)
WHERE dept_no BETWEEN 'd003' AND 'd005'
AND emp_no = 10001;
/*
+--+-----------+--------+-----+-------+------+-----------+
|id|select_type|table |type |key |rows |Extra |
+--+-----------+--------+-----+-------+------+-----------+
|1 |SIMPLE |dept_emp|range|PRIMARY|165571|Using where|
+--+-----------+--------+-----+-------+------+-----------+
*/
EXPLAIN
SELECT *
FROM dept_emp USE INDEX (PRIMARY)
WHERE dept_no IN ('d003', 'd004', 'd005')
AND emp_no = 10001;
/*
+--+-----------+--------+-----+-------+----+-----------+
|id|select_type|table |type |key |rows|Extra |
+--+-----------+--------+-----+-------+----+-----------+
|1 |SIMPLE |dept_emp|range|PRIMARY|3 |Using where|
+--+-----------+--------+-----+-------+----+-----------+
*/
USE INDEX를 사용했다 해서 항상 옵티마이저가 해당 인덱스를 사용하는 것은 아니다.
좀더 강한 유도를 하고 싶다면FORCE INDEX를 사용한다.
USE INDEX 힌트에 용도를 지정할 수 도 있는데, 지정하지 않아도 옵티마이저가 알아서 최적으로 설정한다.
아래 3가지 용도가 선택된다.
- USE INDEX FOR JOIN
- USE INDEX FOR ORDER
- USE INDEX FOR GROUP
IGNORE INDEX
특정 인덱스를 사용하지 못하게 하는 용도로 사용, 옵티마이저가 풀 테이블 스캔을 사용하도록 유도하기 위해 사용한다.
EXPLAIN
SELECT *
FROM employees IGNORE INDEX (PRIMARY)
WHERE emp_no = 10001;
/*
+--+-----------+---------+----+----+------+-----------+
|id|select_type|table |type|key |rows |Extra |
+--+-----------+---------+----+----+------+-----------+
|1 |SIMPLE |employees|ALL |NULL|300695|Using where|
+--+-----------+---------+----+----+------+-----------+
*/
SQL_CALC_FOUND_ROWS
SQL_CALC_FOUND_ROWS 힌트 사용시 LIMIT 상관 없이 끝까지 검색을 수행한다.
LIMIT 상관없이 조건을 만족하는 레코드가 전체 몇건이었는지 알아낼 수 있다.
옵티마이저 힌트
https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html
MySQL 5.6 부터 옵티마이저 힌트 가 도입되었다, 영향 범위에 따라 다음 4 개 그룹으로 나눈다.
- Index: 특정 인덱스의 이름을 사용할 수 있는 옵티마이저 힌트
- Table: 특정 테이블의 이름을 사용할 수 있는 옵티마이저 힌트
- Query block: 특정 쿼리 블록에 사용할 수 있는 옵티마이저 힌트, 힌트가 명시된 쿼리 블록에 대해서만 영향을 미치는 옵티마이저 힌트
- Global: 전체 쿼리에 대해서 영향을 미치는 힌트
EXPLAIN
SELECT /*+ INDEX(employees ix_firstname) */ *
FROM employees
WHERE first_name = 'Matt';
/*
+--+-----------+---------+----+------------+-------+-----+---------+
|id|select_type|table |type|key |key_len|ref |rowsExtra|
+--+-----------+---------+----+------------+-------+-----+---------+
|1 |SIMPLE |employees|ref |ix_firstname|58 |const|267 NULL |
+--+-----------+---------+----+------------+-------+-----+---------+
*/
EXPLAIN
SELECT /*+ NO_INDEX(employees ix_firstname) */ *
FROM employees
WHERE first_name = 'Matt';
/*
+--+-----------+---------+----+----+------+--------+-----------+
|id|select_type|table |type|key |rows |filtered|Extra |
+--+-----------+---------+----+----+------+--------+-----------+
|1 |SIMPLE |employees|ALL |NULL|300695|10 |Using where|
+--+-----------+---------+----+----+------+--------+-----------+
*/
대부분의 인덱스 힌트 의 기능이 옵티마이저 힌트 로 구현되어 있으며, MySQL 8.0 부턴 인덱스 힌트 보다 옵티마이저 힌트 사용을 권장한다.
<!–
조인 실행계획 최적화
MySQL 5 이전에는 Exhaustive 방식(완전탐색) 으로 JOIN 쿼리에서 명시된 모든 테이블 조합에 대한 실행계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법은 아래 그림과 같다.
테이블이 4개이기 때문에 4! 조합을 가진다.
MySQL 5 부턴 Greedy 방식을 사용하며, 실행 계획에만 너무 많은 비용이 사용되는 것을 방지하기 위한 방식이다.
optimizer_search_depth 변수로 테이블 선택 조합을 줄일 수 있다.
기본값은 62인데 4~5 정도로 설정하는 것을 권장한다.
MySQL 5 부터 추가된 Heuristic 방식 은 Greedy 방식 를 통해 실행 계획과 비용을 계산하면서, 이미 계산했던 조인 순서의 비용보다 큰 경우 중간에 포기하는 방식, optimizer_prune_level 변수를 통해 ON/OFF 할 수 있다.
–>
정렬, 집계 최적화
정렬을 처리할 때 인덱스 방식을 사용하거나 Filesort 방식을 사용한다.
인덱스 방식의 경우 B Tree 알고리즘에 따라 이미 정렬되어있어 별도 작업을 할 필요가 없다.
Filesort 방식은 쿼리 실행시 정렬처리하는 방식으로 조회 레코드 수가 많지 않다면 나쁘지 않은 방법이다.
MySQL 에서 정렬시에 별도의 메모리 공간을 필요로 하는데 소트 버퍼 라 부른다.
소트 버퍼를 사용하는 방식을 크게 2가지로 나눌 수 있다.
- Single-pass: 레코드 전체를
소트 버퍼에 담아 정렬 - Two-pass: 정렬 기준 칼럼만
소트 버퍼에 담아 정렬
Single-pass 방식을 주로 사용하며, 레코드의 크기가 max_length_for_sort_data 시스템 변수보다 크거나 [BLOB, TEXT] 칼럼이 포함된 경우 Two-pass 방식을 사용한다.
불러와야할 레코드 수가 소트 버퍼 사이즈를 초과하면 정렬하는 작업과 디스크에 임시 저장하는 과정을 반복하고, 이를 합치는 Multi Merge 작업을 수행한다.
병합 연산 횟수는 아래 Sort_merge_passes STATUS 변수로 확인 가능.
> SHOW STATUS LIKE 'Sort%'
/*
+-----------------+-----+
|Variable_name |Value|
+-----------------+-----+
|Sort_merge_passes|0 |
|Sort_range |0 |
|Sort_rows |38 |
|Sort_scan |1 |
+-----------------+-----+
*/
집계요청을 처리할 때 3가지 방식으로 처리한다.
인덱스를 사용하지 않는 방식인덱스를 사용하는 방식루스 인덱스를 사용하는 방식
루스 인덱스는 인덱스를 건너 뛰면서 읽는 방식이다.
(emp_no, from_date) 복합 인덱스가 있는 테이블에서 WHERE, GROUP BY 절에 인덱스가 설정된 필드를 사용했다.
-- 대략 300만 row 테이블
create table salaries
(
emp_no int not null,
salary int not null,
from_date date not null,
to_date date not null,
primary key (emp_no, from_date)
) collate = utf8mb4_general_ci;
create index ix_salary on salaries (salary);
EXPLAIN
SELECT emp_no
FROM salaries
WHERE from_date = '1985-03-01'
GROUP BY emp_no;
-- Using where; Using index for group-by
먼저 emp_no 를 건너다니면서 수집하고 WHERE 조건절로 from_date 에 맞는 데이터를 조회한 후 집계한다.
(emp_no, from_date) 인덱스를 활용하는 WHERE emp_no=... AND from_date=... 쿼리를 여러번 호출하는 것과 비슷하다.
루스 인덱스의 이런 특성 때문에 복합 인덱스 의 prefix index 의 분포도가 낮을 수록 빠른 결과를 만들어낸다.
검색 최적화
옵티마이저가 실행 계획을 수립할 때 통계 정보와 옵티마이저 옵션을 결합해서 최적의 실행 계획을 수립한다.
검색 최적화에 대한 내용은 EXPLAIN 의 Extra 칼럼에 설명되어있다.
optimizer_switch 시스템변수를 확인하면 ON/OFF 된 최적화 옵션 목록을 확인할 수 있다.
SELECT @@optimizer_switch;
/*
index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,
mrr_cost_based=on,
block_nested_loop=on,
batched_key_access=off,
materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,
derived_merge=on,
use_invisible_indexes=off,
skip_scan=on,
hash_join=on,
subquery_to_derived=off,
prefer_ordering_index=on,
hypergraph_optimizer=off,
derived_condition_pushdown=on,
hash_set_operations=on
*/
블록 중첩 루프 조인(block_nested_loop)
MySQL 조인 쿼리는 중첩 루프 조인(Nested Loop Join) 을 사용한다.
아래와 같이 두개의 테이블을 join 했을 때 2중 for문을 실행시키는 것과 비슷하게 동작한다.
EXPLAIN SELECT *
FROM dept_emp de
INNER JOIN departments d ON de.dept_no = d.dept_no
WHERE de.from_date > '1988-04-1'
AND de.from_date < '1988-04-3'
/*
+--+-----------+-----+------+-----------+-------+--------------------+----+--------+---------------------+
|id|select_type|table|type |key |key_len|ref |rows|filtered|Extra |
+--+-----------+-----+------+-----------+-------+--------------------+----+--------+---------------------+
|1 |SIMPLE |de |range |ix_fromdate|3 |NULL |60 |100 |Using index condition|
|1 |SIMPLE |d |eq_ref|PRIMARY |16 |employees.de.dept_no|1 |100 |NULL |
+--+-----------+-----+------+-----------+-------+--------------------+----+--------+---------------------+
for(row1 IN dept_emp){
for(row2 IN departments){
if(condition_matched) return (row1, row2);
}
}
*/
하지만 카타시안곱(CROSS JOIN) 형태의 중복된 레코드를 여러번 반환하는 쿼리에선 중첩 루프 조인 이 비효율적으로 느껴질 수 있다.
동일한 조건으로 두번째 루프 블럭을 여러번 돌아야 하기 때문
블록 중첩 루프 조인 이런 상황에서 조인에 필요한 레코드들을 보관할 조인 버퍼를 사용하는 최적화 방법이다.
EXPLAIN SELECT *
FROM dept_emp de,
employees e
WHERE de.from_date > '1995-01-01'
AND e.emp_no < 109004;
/*
+--+-----------+-----+-----+-------+------+--------+------------------------------------------+
|id|select_type|table|type |key |rows |filtered|Extra |
+--+-----------+-----+-----+-------+------+--------+------------------------------------------+
|1 |SIMPLE |e |range|PRIMARY|150181|100 |Using where |
|1 |SIMPLE |de |ALL |NULL |331143|50 |Using where; Using join buffer (hash join)|
+--+-----------+-----+-----+-------+------+--------+------------------------------------------+
*/
동일한 결과를 가져오기 위해 드라이빙 테이블 접근하지 않고, 레코드가 저장될 조인 버퍼 를 사용한다.
조인버퍼를 사용하면 Extra 에 Using join buffer 가 출력된다.
일괄 키 엑섹스(batched_key_access)
BKA(Batched Key Access) 방식 또한 조인 버퍼를 사용하는 방식으로,
드라이빙 테이블과 드리븐 테이블이 인덱스 키를 통해 조인할 때 조인 버퍼를 사용해 연결할 레코드를 담아두는 방식이다.
이때 키를 일괄적으로 스토리지 엔진 에 요청하고 조인 버퍼에 담아두는 MRR(Multi-Range Read) 기능을 사용한다.
하지만 MRR에 대한 비용 추정이 너무 비관적이기에 대부분 조인 쿼리에서 BKA, MRR, 조인 버퍼 사용하는 실행계획이 채택되지 않는다.
아래와 같이 옵티마이저 환경변수를 변경.
SET optimizer_switch='mrr_cost_based=off,batched_key_access=on';
CREATE TABLE users
(
user_id INT PRIMARY KEY,
username VARCHAR(50)
);
CREATE TABLE orders
(
order_id INT PRIMARY KEY,
user_id INT,
order_date DATE,
amount DECIMAL(10, 2),
INDEX (user_id)
);
EXPLAIN
SELECT *
FROM users
JOIN orders ON users.user_id = orders.user_id
WHERE users.user_id BETWEEN 1 AND 100;
/*
+--+-----------+------+-----+-------+----+--------+--------------------------------------+
|id|select_type|table |type |key |rows|filtered|Extra |
+--+-----------+------+-----+-------+----+--------+--------------------------------------+
|1 |SIMPLE |users |range|PRIMARY|100 |100 |Using where |
|1 |SIMPLE |orders|ref |user_id|1 |100 |Using join buffer (Batched Key Access)|
+--+-----------+------+-----+-------+----+--------+--------------------------------------+
*/
해시조인(hash_join)
MySQL 8.0.18 버전부터는 해시조인 지원,
인덱스 컨디션 푸시다운(index_condition_pushdown)
-- auto-generated definition
create table employees
(
emp_no int not null
primary key,
birth_date date not null,
first_name varchar(14) not null,
last_name varchar(16) not null,
gender enum ('M', 'F') not null,
hire_date date not null
)
create index ix_firstname on employees (first_name);
create index ix_lastname_firstname on employees (last_name, first_name);
EXPLAIN
SELECT *
FROM employees
WHERE last_name = 'Acton'
AND first_name LIKE '%sal';
/*
+--+-----------+---------+----+---------------------+-------+-----+----+--------+---------------------+
|id|select_type|table |type|key |key_len|ref |rows|filtered|Extra |
+--+-----------+---------+----+---------------------+-------+-----+----+--------+---------------------+
|1 |SIMPLE |employees|ref |ix_lastname_firstname|66 |const|189 |11.11 |Using index condition|
+--+-----------+---------+----+---------------------+-------+-----+----+--------+---------------------+
*/
first_name LIKE '%sal' 은 끝나는 단어를 찾기에 range 인덱스 type 사용이 불가능하다.
여기서 인덱스 컨디션 푸시다운 사용 유무에 따라 어디서 필터링 하는지가 달라진다.
둘다 ix_lastname_firstname 인덱스를 사용해 레코드를 검색하지만, 인덱스 컨디션 푸시다운 미사용시 employees 테이블까지 들어가서 필터링한다.
인덱스 컨디션 푸시다운 사용시 ix_lastname_firstname 인덱스 자체에서 필터링한다.
당연히 인덱스만 읽어 필터링 가능한 인덱스 컨디션 푸시다운 방식 사용이 효율적이다.
위의 경우에도 last_name = 'Acton' 으로 검색 후 필터링을 거쳤다.
인덱스 컨디션 푸시다운 방식을 사용하면 Extra 에 Using index condition 가 출력된다.
해당 기능은
MySQL 5.5부터 추가되었다.
인덱스 확장(use_index_extensions)
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)
) collate = utf8mb4_general_ci;
create index ix_fromdate on dept_emp (from_date);
EXPLAIN
SELECT COUNT(*)
FROM dept_emp
WHERE from_date = '1987-07-25'
AND dept_no = 'd001';
/*
+--+-----------+--------+----+-----------+-------+-----------+----+--------+-----------+
|id|select_type|table |type|key |key_len|ref |rows|filtered|Extra |
+--+-----------+--------+----+-----------+-------+-----------+----+--------+-----------+
|1 |SIMPLE |dept_emp|ref |ix_fromdate|19 |const,const|6 |100 |Using index|
+--+-----------+--------+----+-----------+-------+-----------+----+--------+-----------+
*/
위와같이 인덱스가 구성되어 있으면 (from_date) 를 통해 PRIMARY(dept_no, emp_no) 를 찾아가는 형식으로 동작한다.
때문에 인덱스 확장 을 통해 (from_date, dept_no, emp_no) 조합으로 인덱스를 생성한 것과 흡사하게 동작한다.
그래서 위 예제에도 from_date 만 사용하고 이후의 조건이 필터링으로 동작하는것이 아닌,
인덱스 확장 을 사용해 (from_date, dept_no) key_len(19) 가 출력되었다.
인덱스 머지 교집합(index_merge_intersection)
WHERE 에 인덱스 칼럼 여러개를 조건으로 삽입해도 옵티마이저에 의해 첫번째 최적 조건만 인덱싱 되고 나머진 필터링 조건으로 사용된다.
하지만 필터링으로 처리하기엔 레코드 건수가 많을 경우 여러개의 인덱스를 사용하는 인덱스 머지 실행 계획 을 수립한다.
아래의 경우 first_name = 'Georgi', emp_no BETWEEN 10000 AND 20000 조건을 따로따로 검색하여 교집합을 반환한다.
EXPLAIN
SELECT *
FROM employees
WHERE first_name = 'Georgi' -- ix_firstname
AND emp_no BETWEEN 10000 AND 20000; -- PRIMARY
/*
+--+-----------+---------+-----------+--------------------+-------+----+----+--------+--------------------------------------------------+
|id|select_type|table |type |key |key_len|ref |rows|filtered|Extra |
+--+-----------+---------+-----------+--------------------+-------+----+----+--------+--------------------------------------------------+
|1 |SIMPLE |employees|index_merge|ix_firstname,PRIMARY|62,4 |NULL|1 |100 |Using intersect(ix_firstname,PRIMARY); Using where|
+--+-----------+---------+-----------+--------------------+-------+----+----+--------+--------------------------------------------------+
*/
인덱스 머지 교집합 을 사용하면 Extra 에 Using intersect 가 출력된다.
만약 인덱스 확장 으로 (first_name, emp_no) 으로 인덱스가 구성되니까 인덱스 컨디션 푸시다운 으로 필터링 검색하는게 더 효율적인것 같다 생각하면 index_merge_intersection 를 비활성화하면 된다.
인덱스 머지 합집합(index_merge_union)
인덱스 머지 합집합 에서 AND 조건을 사용했다면, 인덱스 머지 합집합 에선 OR 조건을 사용한다.
EXPLAIN
SELECT *
FROM employees
WHERE first_name = 'Georgi' -- ix_firstname
OR hire_date='1987-03-31';
/*
+--+-----------+---------+-----------+------------------------+-------+----+----+--------+--------------------------------------------------+
|id|select_type|table |type |key |key_len|ref |rows|filtered|Extra |
+--+-----------+---------+-----------+------------------------+-------+----+----+--------+--------------------------------------------------+
|1 |SIMPLE |employees|index_merge|ix_firstname,ix_hiredate|58,3 |NULL|364 |100 |Using union(ix_firstname,ix_hiredate); Using where|
+--+-----------+---------+-----------+------------------------+-------+----+----+--------+--------------------------------------------------+
*/
인덱스 머지 합집합 을 사용하면 Extra 에 Using union 이 출력된다.
인덱스 머지 기능을 효율적으로 사용하려면 두 조건으로 검색된 레코드가 동일한 칼럼 기준으로 정렬되어 있어야 한다.
하지만 아래와 같이 인덱스를 range type 으로 검색하면 hire_date 기준으로 정렬되어 출력된다.
이때 옵티마이저는 인덱스 머지 정렬 합집합 방법을 사용한다.
EXPLAIN
SELECT *
FROM employees
WHERE first_name = 'Matt'
OR hire_date BETWEEN '1987-03-01' AND '1987-03-31'
/*
+--+-----------+---------+-----------+------------------------+-------+----+--------+-------------------------------------------------------+
|id|select_type|table |type |key |key_len|rows|filtered|Extra |
+--+-----------+---------+-----------+------------------------+-------+----+--------+-------------------------------------------------------+
|1 |SIMPLE |employees|index_merge|ix_firstname,ix_hiredate|58,3 |3197|100 |Using sort_union(ix_firstname,ix_hiredate); Using where|
+--+-----------+---------+-----------+------------------------+-------+----+--------+-------------------------------------------------------+
*/
Equals 조건으로 검색한 ref type 은 PRIMARY 를 기준으로 정렬되어 출력됨으로 range type 으로 검색된 결과를 PRIMARY 를 기준 재정렬해야한다.
인덱스 머지 정렬 합집합 을 사용하면 Extra 에 Using sort_union 이 출력된다.
테이블 풀아웃(semijoin)
테이블 풀아웃 은 IN 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 조인쿼리로 재작성하는 형태의 최적화.
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 |key_len|ref |rows |filtered|Extra |
+--+-----------+-----+------+-------+-------+-------------------+-----+--------+-----------+
|1 |SIMPLE |de |ref |PRIMARY|16 |const |46012|100 |Using index|
|1 |SIMPLE |e |eq_ref|PRIMARY|4 |employees.de.emp_no|1 |100 |NULL |
+--+-----------+-----+------+-------+-------+-------------------+-----+--------+-----------+
*/
id 값이 모두 1인 것으로 보아 두 테이블이 조인방식으로 처리되었다.
애초부터
서브쿼리로 작성하는 것 보다조인쿼리사용을 권장한다.
퍼스트 매치(firstmatch)
퍼스트 매치 는 IN 서브쿼리 를 EXISTS 서브쿼리 형태로 튜닝한 것과 비슷한 형태의 최적화.
EXISTS 서브쿼리는 는 조건이 맞는 지에 대한 TRUE/FALSE 만 확인하기 때문에 만족하는 결과가 최소 하나가 나오면 바로 TRUE로 판단한다.
조인쿼리나 IN 서브쿼리 에 비해 물리적으로 읽어야할 레코드수가 적기때문에 효율적이다.
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|select_type|table|type|key |key_len|ref |rows|filtered|Extra |
+--+-----------+-----+----+------------+-------+------------------+----+--------+---------------------------------------+
|1 |SIMPLE |e |ref |ix_firstname|58 |const |233 |100 |NULL |
|1 |SIMPLE |t |ref |PRIMARY |4 |employees.e.emp_no|1 |11.11 |Using where; Using index; FirstMatch(e)|
+--+-----------+-----+----+------------+-------+------------------+----+--------+---------------------------------------+
*/
또한 id 값이 모두 1인 것으로 보아 두 테이블이 조인방식으로 처리되었다.
퍼스트 매치 를 사용하면 Extra 에 FirstMatch 가 출력된다.
아래와 같은 IN-to-EXISTS 형태로 튜닝되었다 볼 수 있다.
EXPLAIN
SELECT *
FROM employees e
WHERE e.first_name = 'Matt'
AND EXISTS(
SELECT t.emp_no
FROM titles t
WHERE e.emp_no = t.emp_no
AND t.from_date BETWEEN '1995-01-01' AND '1995-01-30'
);
IN-to-EXISTS형태보다First Match를 사용해 조인처리된 내용이 더 좋다.
조인 테이블이 늘어날 경우 최적화할 조건으로First Match가 들어갈 수 있기 때문.그래서 위와같은
EXISTS쿼리도First Match로 최적화 된다.
중복추리기(duplicateweedout)
중복추리기는 IN 서브쿼리 를 일반적인 INNER JOIN 쿼리로 바꿔 실행 후 중복 레코드를 제거하는 방법이다.
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 |ref |rows|filtered|Extra |
+--+-----------+-----+------+---------+------------------+----+--------+-----------------------------------------+
|1 |SIMPLE |s |range |ix_salary|NULL |36 |100 |Using where; Using index; Start temporary|
|1 |SIMPLE |e |eq_ref|PRIMARY |employees.s.emp_no|1 |100 |End temporary |
+--+-----------+-----+------+---------+------------------+----+--------+-----------------------------------------+
*/
salaries 테이블을 조회하는 과정에서 수많은 중복 emp_no 들이 발생할 것이고 INNER JOIN 으로 실행하기에 employees 와 결합되어 중복레코드들이 발생할 것이다.
이런 과정들이 임시테이블에 저장되었다 중복추리기 에 의해 중복레코드가 제거된 결과를 전달한다.
중복추리기 를 사용하면 Extra 에서 Start temporary - End temporary 가 출력된다.
- Using index:
- Using temporary:
- Distinct:
departments를 통해dept_emp의PRIMARY인덱스를 읽어가며 이미 알고 있는dept_no레코드는 읽지않고 넘어간다.
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|select_type|table|type|key |rows|filtered|Extra |
+--+-----------+-----+----+------------+----+--------+---------------------------------------+
|1 |SIMPLE |e |ref |ix_firstname|233 |100 |NULL |
|1 |SIMPLE |t |ref |PRIMARY |1 |11.11 |Using where; Using index; FirstMatch(e)|
+--+-----------+-----+----+------------+----+--------+---------------------------------------+
*/
파생테이블 머지(derived_merge)
아래 두 SELECT 쿼리는 동일한 실행계획을 가진다.
EXPLAIN
SELECT *
FROM (
SELECT * FROM employees WHERE first_name = 'Matt'
) derived_table
WHERE derived_table.hire_date = '1986-04-03';
EXPLAIN
SELECT *
FROM employees
WHERE first_name = 'Matt'
AND hire_date = '1986-04-03';
/*
+--+-----------+---------+-----------+------------------------+-------+----+--------+------------------------------------------------------+
|id|select_type|table |type |key |key_len|rows|filtered|Extra |
+--+-----------+---------+-----------+------------------------+-------+----+--------+------------------------------------------------------+
|1 |SIMPLE |employees|index_merge|ix_hiredate,ix_firstname|3,58 |1 |100 |Using intersect(ix_hiredate,ix_firstname); Using where|
+--+-----------+---------+-----------+------------------------+-------+----+--------+------------------------------------------------------+
*/
FROM 절 에 사용한 서브쿼리를 파생테이블(Derived Table) 이라 하는데, MySQL 5.7 이전에는 파생테이블을 임시테이블에 복사하고, 임시테이블로부터 필터링을 수행했다.
이후 버전부턴 파생테이블 머지 기능을 사용하여 서브쿼리를 외부쿼리와 병합하는 최적화가 만들어졌다.
인덱스 스킵 스캔(skip_scan)
create index ix_gender_birthdate
on employees (gender, birth_date);
위와같은 복합 인덱스를 사용하려면 조회 조건에 항상 gender 와 birth_date 가 같이 들어가야 한다.
birth_date 만 조건에 사용할 경우 위 복합 인덱스를 사용할 수 없다.
하지만 MySQL 8.0 에 추가된 인덱스 스킵 스캔 최적화 기능을 사용하면 아래와 같은 SELECT 쿼리에서도 해당 인덱스를 사용할 수 있다.
EXPLAIN
SELECT COUNT(*)
FROM employees
WHERE birth_date >= '1965-02-01';
/*
+--+-----------+---------+-----+-------------------+----+--------+--------------------------------------+
|id|select_type|table |type |key |rows|filtered|Extra |
+--+-----------+---------+-----+-------------------+----+--------+--------------------------------------+
|1 |SIMPLE |employees|range|ix_gender_birthdate|300 |100 |Using where; Using index for skip scan|
+--+-----------+---------+-----+-------------------+----+--------+--------------------------------------+
*/
인덱스 스킵 스캔은 위 아래 두 조건으로 각각 검색하고 조합하는 방식으로 동작한다.
gender='M' AND birth_date>='1965-02-01'
gender='F' AND birth_date>='1965-02-01'
그렇다 보니 복합 인덱스의 prefix index의 분포도가 낮을수록 높은 성능을 가진다.
루스 인덱스와 비슷한 단락
또한 인덱스 스킵 스캔은 인덱스를 가지고 동작하기 때문에 인덱스에 있는 칼럼만 조회하거나 위와 같이 COUNT 기능의 쿼리에서만 동작한다.
컨디션 팬아웃(condition_fanout_filter)
MySQL 에선 컨디션 팬아웃 방법으로 테이블의 통계정보를 활용해 테이블의 조인순서를 정한다.
EXPLAIN
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';
/*
+--+-----------+-----+----+------------+----+--------+-----------+
|id|select_type|table|type|key |rows|filtered|Extra |
+--+-----------+-----+----+------------+----+--------+-----------+
|1 |SIMPLE |e |ref |ix_firstname|233 |26.07 |Using where|
|1 |SIMPLE |s |ref |PRIMARY |9 |100 |NULL |
+--+-----------+-----+----+------------+----+--------+-----------+
*/
hire_date 는 인덱스로 저장되어있다 보니 분포도에 해당하는 통계정보를 알 수 있고,
Matt 에 해당하는 레코드중 26.07% 가 1985-11-21 ~ 1986-11-21 에 해당된다.
옵티마이저는 해당 정보를 사용해 효율적인 실행 계획을 수립하지만 컨디션 팬아웃 을 사용함으로 발생하는 오버헤드도 꽤 큰편이니 활성화 여부를 성능 테스트를 통해 진행하면 좋다.
잘못된 최적화
nullable 칼럼에 IN (SELECT ...) 과 같은 조건으로 쿼리검색을 할 경우 서브쿼리 테이블에 대해 풀 테이블 스캔이 발생한다.
칼럼에 null 이 지정되어 있을 경우 아무것도 검색되지 않아야 조건에 부합하다고 생각하기 때문.
Extra 에 Full scan on NULL key 문자열이 출력되면 아래와 같이 col1 NOT NULL 조건에서만 검색하도록 명시해줘야한다.
SELECT *
FROM tb_test1
WHERE col1 IS NOT NULL
AND col1 IN (SELECT col2 FROM tb_test2);