MySQL 실행 계획

MySQL 통계 정보 이용하기

 

1. 히스토그램

8.0 이상 버전 부터 사용할 수 있는 통계 정보. 쿼리의 실행 계획을 세우는데 도움을 준다.

컬럼 단위로 수집되며 ANALYZE TABLE ... UPDATE HISTOGRAM 명령을 실행해 수동으로 수집 및 관리된다.

 

예시)

ANALYZE TABLE employees.employees
UPDATE HISTOGRAM ON gender, hire_date;

SELECT * FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME='employees' AND TABLE_NAME='employees';

gender 컬럼에 생성된 히스토그램 내용

{"buckets": [[1, 0.6000515237677232], [2, 1.0]], "data-type": "enum", "null-values": 0.0, "collation-id": 255, "last-updated": "2022-06-12 05:53:23.395740", "sampling-rate": 0.38002295338638453, "histogram-type": "singleton", "number-of-buckets-specified": 100}

- sampling rate

히스토그램을 생성하기 위해 스캔한 페이지 비율

 

- histogram type 히스토그램 타입

• Singleton : 컬럼값 개별로 레코드 건수를 관리 하는 히스토그램.
• Equi-Height : 높이 균형 히스토그램, 컬럼값의 범위 별로 균등한 개수로 구분해서 관리하는 히스토그램.

- number-of-bucklets-specified

히스토그램을 생성할 때 설저했던 버킷의 개수

 

- MySQL 옵티마이저가 히스토그램을 사용하지 않게 하기 위한 설정

SET GLOBAL optimizer_switch='condition_fanout_filter=off';

 

히스토그램 용도

분포에 따른 최적화된 실행 계획을 선택

EXPLAIN SELECT * FROM employees WHERE first_name ='Zita' AND birth_date BETWEEN '1950-01-01' AND '1960-01-01';

히스토그램을 사용하지 않는다면 Zita와 일치하는 224 건수 중 birth_date 조건에 맞는 레코드 비율은 100%로 예측했다.

 

히스토그램을 사용한다면 실제 분포를 참고해 최적화된 방향으로 실행 계획을 변경할 수 있다. 실제 분포에 따라 60.88% 레코드 비율로 일치할 것으로 보고 실행 계획을 세운다.

 

실행 계획 확인

출력 포맷

- 테이블 형태

EXPLAIN SELECT * FROM employees e INNER JOIN salaries s ON s.emp_no = e.emp_no
WHERE first_name='ABC';

- 트리 형태

EXPLAIN FORMAT=TREE SELECT * FROM employees e INNER JOIN salaries s ON s.emp_no = e.emp_no
WHERE first_name='ABC';

- JSON 형태

EXPLAIN FORMAT=JSON SELECT * FROM employees e INNER JOIN salaries s ON s.emp_no = e.emp_no
WHERE first_name='ABC';

 

 

실행 시간 확인

EXPLAIN ANALYZE 
SELECT 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;

-> Table scan on <temporary>  (actual time=0.002..0.005 rows=48 loops=1)
    -> Aggregate using temporary table  (actual time=3.088..3.095 rows=48 loops=1)
        -> Nested loop inner join  (cost=618.92 rows=2239) (actual time=0.303..2.991 rows=48 loops=1)
            -> Index lookup on e using ix_first_name (first_name='Matt')  (cost=166.76 rows=233) (actual time=0.286..0.736 rows=233 loops=1)
            -> Filter: ((s.salary > 50000) and (s.from_date <= DATE'1990-01-01') and (s.to_date > DATE'1990-01-01'))  (cost=0.98 rows=10) (actual time=0.008..0.009 rows=0 loops=233)
                -> Index lookup on s using PRIMARY (emp_no=e.emp_no)  (cost=0.98 rows=10) (actual time=0.005..0.008 rows=10 loops=233)

 

실행 순서는 들여 쓰기 안쪽이 가장 먼저 실행되고 뎁스가 같은 경우 위에 것부터 실행된다.

1) Index lookup on s using PRIMARY

primary key를 이용하여 emp_no가 같은 두 테이블을 조인한다.

 

2) Index lookup on e using ix_first_name

first_name 인덱스를 이용하여 first_name='Matt' 인 것만 가져온다.

 

3) Filter : ((s.slary > 50000) and ...

inner join시 만족해야하는 조건에 대한 레코드를 가져온다.

 

4) Nested loop inner join

inner join을 수행한다.

 

5) Aggregate using temporay table

임시 테이블로 부터 집계 결과를 수행한다.

 

6) Table scan on <temporary>

테이블을 스캔해서 결과를 반환한다.

 

- 실제 실행 시간 관련

(actual time=0.005..0.008 rows=10 loops=233)

 

actual time = (1)...(2)

• (1) : 첫번재 레코드를 가져오는데 걸린 평균 시간
• (2) : 마지막 레코드를 가져오는데 걸린 평균 시간
• employees 테이블의 emp_no를 기준으로 salaries 테이블의 레코드를 가져오는데 걸리는 시간을 의미

 

rows = 10

• 한번에 가져오는 레코드 개수
• emplopyees 테이블의 emp_no 한 개당 일치하는 salary 레코드가 10개를 갖는다는 의미

loops = 233

• rows 만큼 가져오는 반복 횟수
• emp_no가 233개가 있음.

 

실행 계획 분석

EXPLAIN 
SELECT * FROM employees e INNER JOIN salaries s ON s.emp_no=e.emp_no
WHERE first_name='ABC';

 

1) id

주로 SELECT 실행 단위로 구분되어지는 고유 id

 

2) select_type

• SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 컬럼
• SIMPLE : UNION 이나 서브쿼리가 아닌 일반 SELECT를 의미
• PRIMARY : UNION이나 서브쿼리를 가진 SELECT 구문의 가장 Outer에 있는 단위 쿼리
• UNION : UNION으로 결합되는 SELECT
• DERIVED : UNION의 결과로 사용되는 임시 테이블, DEPENDENT UPON도 의미가 비슷하다.
• UNION RESULT : UNION 결과를 담아두는 테이블
• SUBQUERY : FROM 절외에 사용된 서브 쿼리, FROM 절에서 사용된 서브쿼리는 DERIVED로 표시

 

3) table

실행 계획은 id가 아니라 table 단위로 실행된다.

<derived2> 와 같은 테이블 이름은 임시 테이블을 의미하며 id=2인 테이블로부터 생성되었다는 것을 의미한다.

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;

<derived2>는 id=2번 실행으로 부터 생성된 파생 테이블을 가리킨다.

 

4) partitions

테이블이 파티션된 경우 참조한 파티션들의 이름을 나타낸다.

 

5) type

MySQL 서버가 각 테이블의 레코드르 어떤 방식을 읽었는 지를 나타낸다.

• system : 레코드가 1건만 존재하거나 존재하지 않는 테이블을 접근하였을 때
• const : 프라이머리 키 혹은 유니크 키를 이용하여 반드시 1건만 조회하는 쿼리 처리방식을 의미
• eq_ref : 조인 쿼리에서 두번째 테이블을 읽을 때 not null 유니크 혹은 프라이머리 키로 조회하였을 때 
• ref : 인덱스 종류와 상관없이 동등 조건으로 검색하였을 때 조회 방식
• fulltext : 전문 검색 인덱스를 이용하여 레코드를 읽는 접근 방식
• ref_or_null : ref 검색 방식에 NULL 비교가 추가된 것.
• unique_subquery : WHERE 조건절에서 사용될 수 있는 IN 형태의 쿼리를 위한 접근 방법. 
• - 서브 쿼리의 결과가 중복되지 않는 유니크한 값만 반환할 때
• EXPLAIN SELECT * FROM departments WHERE dept_no IN (SELECT dept_no FROM dept_emp WHERE emp_no=10001);
• index_subquery: IN 형태의 서브 쿼리에서 반환 결과를 INDEX를 이용하여 중복을 제거할 수 있을 때
• range : 인덱스 레인지 스캔
• index_merge : 둘 이상의 인덱스를 이용해 각 결과를 만들어내고 병합하는 처리
• EXPLAIN SELECT * FROM employees WHERE emp_no BETWEEN 10001 AND 11000 OR first_name='Smith';

• primary key를 이용하여 만들어낸 결과와 first_name으로 만들어낸 결과를 OR 연산자로 합친다.
• index: 인덱스를 이용하여 풀 스캔을 하였을 때의 처리
• all :  풀 테이블 스캔

 

6) possible_keys

사용할 수 있었던 인덱스의 목록

 

7) key

최종 선택된 실행 계획에서 사용하는 인덱스

PRIMARY인 경우 primary key 혹은 사용된 다른 인덱스가 표시된다.

 

8) key_len

다중 컬럼 인덱스 중 몇 인덱스가 사용되었는 지 바이트로 나타낸다.

EXPLAIN SELECT * FROM dept_emp WHERE dept_no='d005';

dept_no라는 인덱스를 사용해여 16 바이트의 인덱스 크기를 나타낸다.dept_no 는 utfmb4 문자 집합으로 1바이트~4바이트의 가변 크기의 문자열을 사용한다.

 

EXPLAIN SELECT * FROM dept_emp WHERE dept_no='d005' AND emp_no=10001;

emp_no에 대한 PRIMARY 키를 사용하면 INTEGER 타입의 4바이트를 추가로 사용하기 때문에 총 20바이트를 사용하게 된다.

 

9) ref

참조 조건 (Equal 비교 조건)으로 어떤 값이 제공되었는지 보여준다. 상숫값이면 const 혹은 다른 테이블의 칼럼값이면 그 테이블의 칼럼명이 표시된다.

 

EXPLAIN SELECT * FROM employees e, dept_emp de WHERE e.emp_no=de.emp_no;

 

10) rows

MySQL 옵티마이저가 선택한 실행 계획에 따라 얼마나 많은 레코드를 읽어야 하는 지 예측된 레코드 수 이다.

예측 값이므로 실제 읽은 레코드 수와는 차이가 있을 수 있으며 히스토그램과 같은 통계 정보를 사용할 수 있다.

EXPLAIN SELECT * FROM dept_emp WHERE from_date>='1985-01-01';

위 실행 계획은 '1985-01-01' 보다 이후 인 from_date를 조사하였으나 331143의 예측된 레코드 수를 읽어야한다고 예측했다. 이는 전체 레코드 수와 같으므로 WHERE 사용 유무에 상관 없이 풀 테이블 스캔 ALL을 이행한다.

 

11) filtered

실행 계획에서는 rows 칼럼의 값은 인덱스를 사용하는 조건에만 일치하는 레코드 건수를 예측한 것이다.

WHERE 절에 사용되는 조건에만 일치하는 레코드 건수를 예측한 것이다.

 

 

12) Extra

주로 MySQL 내부 알고리즘에 의해 처리된 내용을 더 자사하게 보여준다.

• const row not found : const 방법으로 읽었지만 결과가 없을 때
• deleting all rows : WHERE 조건절이 없는 delete 구문
• distinct : 결과에 대해 distinct 처리
• using filesort : ORDER BY가 인덱스를 사용하지 못하여 정렬 결과를 임시 저장하여 사용하게 된다는 의미.
• using index : 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 처리할 수 있을 때