통계 정보는 MySQL의 실행 계획에 가장 큰 영향을 미친다.
통계 정보를 테이블 및 인덱스에 대한 통계 정보와 히스토그램으로 나누어 살펴보자.
mysql
데이터베이스의 innodb_index_stats
테이블과 innodb_index_stats
테이블로 관리할 수 있게되어 재시작 되어도 통계 정보가 유지된다.STATS_PERSISTENT
를 0으로 설정하면 된다.STATS_PERSISTENT
를 설정하지 않으면 innodb_stats_persistent
시스템 변수의 값에 따라 결정한다. (ON이면 영구 저장, OFF면 영구 저장 X) // employees 테이블의 인덱스 통계 정보
SELECT *
FROM innodb_index_stats
WHERE database_name='employees'
AND TABLE_NAME='employees';
employees
테이블에 있는 인덱스들의 통계 정보는 위와 같이 저장되어 있다.
stat_name=’n_diff_pfx%’
: 인덱스가 가진 유니크한 값의 개수stat_name=’n_leaf_pages’
: 인덱스의 리프 노드 페이지 개수stat_name=’size’
: 인덱스 트리의 전체 페이지 개수 // employees 테이블의 통계 정보
SELECT *
FROM innodb_table_stats
WHERE database_name='employees'
AND TABLE_NAME='employees';
employees
테이블의 통계 정보는 위와 같이 저장되어 있다.
n_rows
: 테이블의 전체 레코드 건수clustered_index_size
: 프라이머리 키의 크기(InnoDB 페이지 개수)sum_of_other_index_sizes
: 프라이머리 키를 제외한 인덱스의 크기(InnoDB 페이지 개수)통계 정보는 아래와 같은 이벤트들이 발생하면 갱신된다.
ANALYZE TABLE
명령이 실행되는 경우SHOW TABLE STATUS
명령이나 SHOW INDEX FROM
명령이 실행되는 경우 하지만 갑자기 통계 정보가 변경되면 의도치 않게 실행 계획이 변경되는 문제가 발생할 수 있는데, innodb_stats_auto_recalc
시스템 변수의 값을 OFF로 설정하면 이를 막을 수 있다.innodb_stats_transient_sample_pages
: 기본값=8, 자동으로 통계가 수집될 때 8개의 페이지만 분석하여 통계 정보로 활용함을 의미한다.innodb_stats_persistent_sample_pages
: 기본값=20, ANALYZE TABLE 명령이 실행되면 20개의 페이지만 분석하여 통계 정보로 활용함을 의미한다.→ 정확도를 높히고 싶다면 위 시스템 변수 값을 올리면 되지만, 통계 수집 시간이 길어지므로 주의해야한다.
기존 통계 정보만으로는 최적의 실행 계획을 수립하기에는 많이 부족했다.
MySQL 8.0부터는 컬럼의 데이터 분포도를 참조할 수 있는 히스토그램을 활용할 수 있다.
히스토그램 정보는 컬럼 단위로 관리되는데, 이는 자동으로 수집되지 않고 ANALYZE TABLE … UPDATE HISTOGRAM
명령을 실행해 수동으로 수집된다.
이 히스토그램 정보를 조회하려면 column_statistics
테이블을 SELECT
하면 된다.
히스토그램은 싱글톤 히스토그램, 높이 균형 히스토그램 두 가지가 지원된다.
전체 쿼리의 비용을 계산하는데 필요한 단위 작업들의 비용을 코스트 모델이라고 한다.
MySQL의 코스트 모델은 다음 2개 테이블에 저장되어 있는 설정값을 사용한다.
server_cost
: 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리engine_cost
: 레코드를 가진 데이터 페이지를 가져오는데 필요한 비용 관리두 테이블은 아래 5개의 컬럼을 공통으로 가지고있다.
cost_name
: 코스트 모델의 각 단위 작업default_value
: 각 단위 작업의 비용(기본값, MySQL 서버 소스 코드에 설정된 값)cost_value
: DBMS 관리자가 설정한 값(NULL이면 default_value
값 사용)last_updated
: 단위 작업의 비용이 변경된 시점comment
: 비용에 대한 추가 설명engine_cost
테이블은 아래 2개의 컬럼을 더 가지고 있다.
engine_name
: 비용이 적용된 스토리지 엔진device_type
: 디스크 타입단위 작업의 종류는 아래와 같이 8개가 있다.
테이블 이름 | cost_name | default_value | 설명 |
---|---|---|---|
engine_cost | io_block_read_cost | 1 | 디스크 데이터 페이지 읽기 |
engine_cost | memory_block_read_cost | 0.25 | 메모리 데이터 페이지 읽기 |
server_cost | disk_temptable_cost | 20 | 디스크 임시 테이블 생성 |
server_cost | disk_temptable_row_cost | 0.5 | 디스크 임시 테이블의 레코드 읽기 |
server_cost | key_compare_cost | 0.05 | 인덱스 키 비교 |
server_cost | memory_temptable_create_cost | 1 | 메모리 임시 테이블 생성 |
server_cost | memory_temptable_row_cost | 0.1 | 메모리 임시 테이블의 레코드 읽기 |
server_cost | row_evaluate_cost | 0.1 | 레코드 비교 |
코스트 모델은 각 단위 작업에 설정되는 비용이 커지면 어떤 실행 계획의 비용이 변하는지 파악하는 것이 중요하다.
웬만하면 위 테이블들의 default_value
를 바꾸지 말자
MySQL의 실행 계획은 DESC
또는 EXPLAIN
명령으로 확인할 수 있다.
실행 계획의 포맷은 아래와 같이 테이블, 트리, JSON 3가지 중 하나를 선택할 수 있다.
EXPLAIN [FORMAT=TREE or JSON]
(테이블이 기본값)EXPLAIN ANALYZE
명령으로 쿼리의 실행 계획과 단계별 소요된 시간 정보를 확인할 수 있다.
EXPLAIN ANALYZE
SELECT e.emp_no, 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=0.001..0.004 rows=48 loops=1)
B) -> Aggregate using temporary table (actual time=3.799. .3.808 rows=48 loops=1)
C) -> Nested loop inner join (cost=685.24 rows=135)
(actual time=0.367..3.602 rows=48 loops=1)
D) -> Index lookup on e using ix_firstname (first_name='Matt') (cost=215.08 rows=233)
(actual time 0.348..1.046 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.98 rows=1)
(actual time 0.009..0.011 rows=0 loops=233)
F) -> Index lookup on s using PRIMARY (emp_no=e.emp_no) (cost=0.98 rows=10)
(actual time=0.007..0.009 rows=10 loops=233)
위와 같은 TREE 포맷의 실행 계획에서 들여쓰기는 호출 순서를 의미하며 규칙은 아래와 같다.
위 실행 계획을 풀어서 설명하면
employees
테이블의 ix_firstname
인덱스를 통해 first_name=’Matt’
조건에 일치하는 레코드를 찾는다.salaries
테이블의 PK를 이용해 1번 결과의 emp_no
와 같은 emp_no
를 가진 레코드를 s
테이블에서 찾는다.s.salary > 50000 and s.from_date <= DATE'1990-01-01' and s.to_date > DATE'1990-01-01'
조건으로 필터링한다.GROUP BY
집계를 실행한다.F) -> Index lookup on s using PRIMARY (emp_no=e.emp_no) (cost=0.98 rows=10)
(actual time=0.007..0.009 rows=10 loops=233)
실행 계획의 F라인을 자세히 분석해보자.
actual time=0.007 ..0.009
employees
테이블에서 읽은 emp_no
값을 기준으로 salaries
테이블에서 일치하는 레코드를 검색하는 데 걸린 시간(밀리초)을 의미한다.첫 번째 숫자는 첫 번째 레코드를 가져오는 데 걸린 평균 시간, 두 번째 숫자는 마지막 레코드를 가져오는데 걸린 평균 시간을 의미한다.
rows=10
employees
테이블에서 읽은 emp_no
에 일치하는 salaries
테이블의 평균 레코드 건수를 의미한다.loops=233
employees
테이블에서 읽은 emp_no
를 이용해 salaries
테이블의 레코드를 찾는 작업이 반복된 횟수를 의미한다.employees
테이블에서 읽은 emp_no
의 개수가 233개임을 의미한다.→ salaries
테이블에서 emp_no
일치 건을 찾는 작업을 233번 반복했는데, 매번 salaries
테이블에서 첫 번째 레코드를 가져오는데 0.007 밀리초가, 10개의 레코드를 모두 가져오는 데 0.009 밀리초가 걸린 것이다.
select_type
이 SIMPLE
이다.SIMPLE
쿼리는 하나만 존재하고, 일반적으로 제일 바깥 SELECT
쿼리이다.UNION
이나 서브쿼리를 사용하는 SELECT
쿼리의 가장 바깥쪽 쿼리는 select_type
이 PRIMARY
이다.UNION
으로 결합하는 단위 SELECT
쿼리 가운데 첫 번째 이후 쿼리의 select_type
은 UNION
으로 표시된다. 첫 번째 쿼리는 DERIVED
로 표시된다. 왜냐하면 여러 쿼리의 결과를 합치기 위한 임시 테이블이 필요하기 때문이다.UNION
으로 결합하는 쿼리에서 표시되지만, 외부 쿼리의 영향을 받는 경우를 말한다.select_type
이 UNION RESULT
이다.FROM
절 이외에서 사용되는 서브쿼리만을 의미한다.FROM
절에 사용된 서브쿼리는 DERIVED
로 표시된다.DERIVED
는 단위 SELECT
쿼리의 결과로 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다.LATERAL JOIN
을 통해 FROM
절 서브쿼리에서 외부 컬럼을 참조할 수 있는데, 이때 나오는 select_type
이다.select_type
이 UNCACHEABLE SUBQUERY
이다.UNION
+ UNCACHEABLE
<derived2>
의 경우는 id가 2인 SELECT
의 결과를 의미한다. 즉, 2번 SELECT
문에서 생성된 임시테이블과 e
테이블을 조인한 것이 결과가 된다. select_type
이 MATERIALIZED
인 실행 계획에서는 <subquery N>
과 같은 값이 표시된다.ALL
을 제외한 나머지 타입은 모두 인덱스를 사용한다.WHERE
절을 가지고 있고, 1건만 반환하는 방식의 쿼리를 말한다. type이 const
인 실행 계획은 옵티마이저가 쿼리를 최적하며 먼저 실행해서 통째로 상수화한다. 예를들어 SELECT name FROM user WHERE id=1;
이런 서브쿼리가 있다면 이 서브쿼리를 통째로 ‘name1'
으로 상수화 하는 것이다.eq_ref
라고 한다. 즉, 조인에서 두 번째 이후에 읽는 테이블에 조건에 맞는 레코드가 1건만 존재한다는 보장이 있어야한다.ref
접근 방법은 인덱스의 종류와 상관없이 동등 조건으로 검색할 때 사용된다. 레코드가 반드시 1건이란 보장이 없으므로 const
나 eq_ref
보다 느리지만 매우 빠른 조회 방법이다.fulltext
접근 방법은 전문 검색 인덱스를 사용해 레코드를 읽는 방법을 의미한다.ref
방식 또는 NULL
비교 접근 방법을 의미한다. 잘 사용되진 않지만 나쁘지 않은 접근 방법이다.WHERE
절에서 사용될 수 있는 IN(subquery)
형태의 쿼리를 위한 접근 방법이다. 말 그대로 서브쿼리에서 중복되지 않은 값만 반환할 때 이 방법을 사용한다.unique_subquery
와 비슷하지만 중복된 값이 있을 수 있어서 중복 제거 작업이 필요한 경우 사용되는 접근 방법이다. MySQL 8.0 버전에서는 세미조인을 최적화 하는 많은 기능이 생겨 unique_subquery
와 index_subquery
은 잘 보이지 않는다.index_merge
접근 방법이 이용되면 Extra 컬럼에 추가적인 내용이 표시된다.index
접근 방법은 인덱스 풀 스캔을 의미한다. LIMIT
조건이 없거나 가져올 레코드 건수가 많으면 상당히 느린 처리를 수행한다.key_len
컬럼은 ****쿼리를 처리하기 위해 다중 컬럼으로 구성된 인덱스에서 몇 바이트 까지 썼는지를 의미한다.ref
면 참조 조건으로 어떤 값이 제공됐는지 보여준다. 상숫값을 지정했다면 const
, 다른 테이블의 컬럼 값이면 그 테이블이름과 컬럼이름이 표시된다. ref
컬럼의 값이 func
면 값에 연산을 거쳐서 참조했다는 것을 의미한다.rows
가 233이고, filtered
가 16.03이면 결과 레코드 건수는 233 * 0.1603 = 37이 된다.SELECT DISTINCT d.dept_no
FROM departments d, dept_emp de WHERE de.dept_no=d.dept_no;
Extra
컬럼에 Distinct
가 표시되면 위와 같이 처리된다. 만약 departments
테이블을 조회하는데, 조건에 dept_emp
테이블에 존재하는 dept_no
를 가져야 하는 경우 위와 같이 일부 레코드만 조인한다.col1 IN (SELECT col2 FROM …)
과 같은 조건을 가진 쿼리의 실행 계획에서 표시될 수 있다. Full scan on NULL key
는 위와 같은 쿼리에서 col1
이 NULL
일때 발생할 수 있고, 결과를 가지는지 확인하기 위해 서브쿼리 테이블에 대해 풀 스캔을 할 것이라는 것을 의미한다.HAVING
, WHERE
절의 조건이 무조건 FALSE
가 나오는 경우 표시된다.MIN
이나 MAX
함수의 대상이 NULL
일 때 표시된다.const
방법으로 접근할 때 일치하는 레코드가 없을 때 표시된다.UPDATE
or DELETE
명령의 실행 계획에서 해당 파티션이 없을 때 표시된다.NOT IN(subquery) 나 NOT EXISTS
연산자를 주로 사용하는데, 이러한 형태의 조인을 안티 조인이라고 한다.SELECT *
FROM dept_emp de
LEFT JOIN departments d ON de.dept_no=dept_no
WHERE d.dept_no IS NULL;
레코드 건수가 많을 때는 위와 같이 아우터 조인을 이용해서 안티 조인을 구현하는 것이 빠른데, 이렇게 아우터 조인을 이용해 안티 조인을 수행하는 쿼리의 경우 Not exists
가 표시된다. Not exists
의 의미는 옵티마이저가 조인 시 departments
테이블의 레코드가 존재여부만 판단한다는 것을 의미한다. 즉, 조건에 일치하는 레코드가 여러 건 있어도 1건만 조회해보고 처리를 완료하는 최적화를 의미한다.SELECT *
FROM employees e1, employees e2
WHERE e2.emp_no >= e1.emp_no;
위 쿼리는 WHERE절의 조인 조건에 변수만 있기 때문에 인덱스 레인지 스캔과 풀 테이블 스캔 중 어느 것이 효율적인지 판단할 수 없다. 즉, 의미 그대로 레코드 마다 인덱스 레인지 스캔을 체크한다는 뜻이다. index map: N
의 경우는 사용할 인덱스의 후보에 대한 정보를 담고있다.Rematerialining
이라고 한다. 결국 각 레코드 마다 새 내부 임시 테이블이 생성되는데 이때 Rematerialize
가 표시된다.MIN()
또는 MAX()
만 SELECT
절에 사용되거나 GROUP BY
로 MIN()
, MAX()
를 조회하는 쿼리가 인덱스를 이용해 1건만 읽는 형태의 최적화가 적용되면 표시된다.Start temporary
, 마지막 테이블에 End temporary
를 표시한다.ORDER BY
처리가 인덱스를 사용하지 못할 때 표시된다. Using filesort가 표시되는 쿼리는 많은 부하를 일으키므로 튜닝이 필요하다.GROUP BY
처리를 수행하면 별도의 정렬 작업이 필요 없어지고, 인덱스의 필요한 부분만 읽으면(루스 인덱스 스캔) 되므로 성능이 향상되는데, 이 때 Using index for group-by
메시지가 표시된다. 인덱스를 이용하여 GROUP BY
를 처리할 수 있더라도 AVG()
, SUM()
처럼 조회하려는 값이 모든 인덱스를 다 읽어야 할 경우 루스 인덱스 스캔이 불가능하다. 이 경우에는 Using index for group-by
메시지가 표시되지 않는다. 참고로, 루스 인덱스 스캔은 대량의 레코드를 GROUP BY
하는 경우엔 성능 향상효과가 있지만 레코드 건수가 적으면 루스 인덱스 스캔을 사용하지 않아도 빠르게 처리가 가능하므로 무조건 좋은 것은 아니다.index_merge
접근 방법으로 실행되는 경우 어떤 방식의 index_merge
인지 알려주기 위해 표시된다.