DBMS의 목적은 많은 데이터를 안전하게 저장/관리하고, 사용자가 원하는 데이터를 빠르게 조회할 수 있게 해주는 것
이 목적을 달성하기 위해 옵티마이저는 사용자의 쿼리를 최적으로 처리하기 위한 쿼리 실행 계획을 수립
하지만 옵티마이저가 항상 최적의 실행 계획을 수립할 수 있는 것 아니기 때문에 이를 보완할 필요가 있음
사용자는 이러한 문제점을 보완하기 위해 EXPLAIN 명령으로 옵티마이저가 수립한 실행 계획을 확인할 수 있음
실행 계획과 관련하여 이 장에서는 아래의 내용을 다룸
MySQL 서버의 실행 계획에 영향을 미치는 통계 정보
MySQL 서버가 보여주는 실행 계획을 읽는 순서와 출력되는 키워드와 알고리즘
통계 정보
8.0 버전부터는 인덱스 되지 않은 컬럼들에 대해 데이터 분포도(커디널리티)를 수집해서 저장하는 히스토그램 정보 도입
5.7 버전까지는 테이블과 인덱스에 대한 개괄적인 정보를 가지고 실행 계획을 수립
실제로 데이터가 분포되어 있는 정보가 없기 때문에 실행 계획의 정확도가 떨어지는 경우 존재
물론 히스토그램이 도입됐다고 기존 테이블이나 인덱스의 통계 정보가 필요하지 않은 것은 아님
테이블 및 인덱스 통계 정보
비용 기반 최적화(Cost based optimizer)에서 가장 중요한 것은 통계 정보
5.6 버전부터는 InnoDB 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로 관리할 수 있게 개선
5.5 버전까지는 각 테이블의 통계 정보가 메모리에 관리되고, SHOW INDEX 명령을 통해 인덱스 분포를 확인
서버 재시작 시 통계 정보 초기화
5.6 버전부터는 innodb_index_stats 테이블과 innodb_table_stats 테이블로 관리할 수 있게 개선
SHOW TABLES LIKE '%_stats
5.6 버전부터는 STATS_PERSISTENT 옵션을 통해 테아블 단위로 영구적인 통계 정보를 보관할지 설정 가능
ALTER TABLE tbl_name STATS_PERSISTENT = 0: 5.5 이전 버전처럼 관리, stats 테이블에 저장하지 않음
ALTER TABLE tbl_name STATS_PERSISTENT = 1: 통계 정보 저장
ALTER TABLE tbl_name STATS_PERSISTENT = DEFAULT: 기본값(1, ON)으로 설정
히스토그램
8.0 버전부터는 컬럼의 데이터 분포로들 참조할 수 있는 히스토그램 정보 활용
5.7 버전까지는 통계 정보는 단순히 인덱스된 컬럼의 유니크한 개수 정도만 저장
COLUMN_STATISTICS 타입 조회를 통해 확인 가능
히스토그램 타입
8.0 버전에서는 아래 두 가지 종류의 히스토그램 타입 지원
Singleton
Equi-Height
Singleton(싱글톤 히스토그램)
컬럼값 개별로 레코드 건수를 관리하는 히스토그램 (Value-Based histogram)
컬럼이 가지는 값별로 버킷 할당
각 버킷이 컬럼의 값과 발생 빈도의 비율 두 개의 값을 포함
주로 코드값과 같은 유니크한 값의 개수가 상대적으로 적 경우에 사용(히스토그램 버킷 수보다 적은 경우)
Equi-Height(높이 균형 히스토그램)
컬럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램(Height-Balanced histogram)
개수가 균등한 컬럼값의 범위별로 하나의 버킷이 할당
각 버킷이 범위 시작 값과 마지막 값, 그리고 발생 빈도율과 각 버킷에 포함된 유니크한 값의 개수 등 네 개의 값을 포함
기울기가 일정한 것은 각 범위가 비슷한 값(레코드의 건수)을 가진다 것을 알 수 있음
샘플링 비율(Sampling-rate)
히스토그램 정보를 수집하기 위해 스캔한 페이지의 비율을 저장
샘플링 비율이 0.35 라면 전체 데이터 페이지의 35%를 스캔해서 이 정보를 수집했음을 의미
샘플링 비율이 높아질수록 더 정확한 히스토그램이 되지만, 테이블을 전부 스캔을 하는 것은 부하가 높으며 시스템 자원을 많이 소모
histogram_generation_max_max_size 시스템 변수에 설정된 메모리 크기에 맞춰서 적절한 샘플링
메모리 크기의 기본값은 20MB로 초기화
히스토그램 삭제 및 미사용
히스토그램 삭제 작업은 테이블의 데이터를 참조하지 않고, 딕셔너리 내용만 삭제하기 때문에 다른 쿼리의 성능에 영향을 주지 않음
삭제 명령어
mysql> ANALYZE TABLE employees DROP HISTOGRAM ON gener, hire_date;
히스토그램을 삭제하지 않고, 옵티마이저가 히스토그램을 사용하지 않게 설정하는 방법도 존재
optimizer_switch 설정
mysql> SET GLOBAL optimizer_switch='condition_fanout_filter=off';
다만 이 경우 전역적으로 영향을 받기 때문에 모든 쿼리가 히스토그램을 사용하지 못함
히스토그램 용도
히스토그램이 도입되기 전에 사용되던 통계 정보는 테이블의 전체 레코드 건수와 인덱스된 컬럼이 가지는 유니크한 값의 개수 정도
하지만 실제 응용 프로그램의 데이터는 균등한 분포도를 가지지 않음
반면 히스토그램은 특정 컬럼이 가지는 모든 값에 대한 분포도 정보를 가지진 못하지만, 각 버킷(범위)별로 레코드의 건수와 유니크한 값의 개수 정보를 갖기 때문에 훨씬 정확한 예측을 할 수 있음
특정 범위의 데이터가 많고 적음을 식별 가능
이러한 정보를 바탕으로 조인의 드라이빙 테이블을 결정하는 등 쿼리 성능에 큰 영향을 줌
히스토그램과 인덱스
히스토그램과 인덱스는 다른 객체이기 때문에 서로 비교할 대상은 아니지만, 부족한 통계 정보를 수집하기 위해 사용된다는 측면에서는 어느 정도 공통점 존재
MySQL 서버에서는 쿼리의 실행 계획을 수립할 때 사용 가능한 인덱스들로부터 조건절에 일치하는 레코드 건수를 대략 파악하고 최종적으로 최적의 실행 계획 수립
이때 조건절에 일치하는 레코드 건수를 예측하기 위해 옵티마이저는 실제 인덱스의 B-Tree를 샘플링해서 살펴봄
이 작업을 인덱스 다이브(Index Dive)라고 표현
인덱스된 컬럼을 검색 조건으로 사용하는 경우 그 컬럼의 히스토그램은 사용하지 않고 인덱스 다이브를 통해 직접 수집한 정보를 활용
실제 검색 조건의 대상 값에 대한 샘플링을 실행하는 것이기 때문에 항상 히스토그램보다 정확한 결과를 기대할 수 있음
히스토그램은 주로 인덱스되지 않은 칼럼에 대한 데이터 분포도를 참조하는 용도로 사용
다만 인덱스 다이브 작업은 어느 정도 비용이 소모
IN 절에 값이 많이 명시된 경우 실행 계획 수립 과정에서 많은 인덱스 다이브를 유발하고 비용도 증가
코스트 모델
MySQL 서버가 쿼리를 처리하려면 아래의 다양한 작업을 필요로 함
디스크로부터 데이터 페이지 읽기
메모리(InnoDB 버퍼 풀)로 데이터 페이지 읽기
인덱스 키 비교
레코드 평가
메모리 임시 테이블 작업
디스크 임시 테이블 작업
전체 쿼리의 비용을 계산하는 데 필요한 단위 작업들의 비용
이 값들을 계산하여 전체 작업 비용 계획을 계산 및 최적의 실행 계획 수립
각 단위 작업의 비용을 사용자가 변경할 수 있는 기능을 제공하지만, 무조건 이 비용들을 바꿔서 사용해야 하는 것은 아님
일반적으로는 기본 값을 변경하지 않는 게 좋음
실행 계획 확인
실행 계획 출력 포맷
키워드 : EXPLAIN / EXPLAIN FORMAT = JSON / EXPLAIN = TREE
EXPLAIN [SELECT|UPDATE|DELETE] ... FROM tbl
EXPLAIN TREE = JSON [SELECT|UPDATE|DELETE] ... FROM tbl
EXPLAIN FORMAT = JSON [SELECT|UPDATE|DELETE] ... FROM tbl
SELECT, UPDATE, DELETE 모두 EXPLAIN 을 통해 실행계획 확인 가능
쿼리의 실행계획을 미리 확인해 봄으로써 풀 스캔 및 적절한 인덱스 사용 여부 등을 확인하여 장애 예방 가능
실행 순서는 보통 위에서 아래로 순서대로 표시
다만, UNION, 서브 쿼리와 같은 경우 순서대로 표시되지 않을 수 있음
쿼리의 실행 시간 확인
키워드 : EXPLAIN ANALYZE
EXPLAIN ANALYZE [SELECT|UPDATE|DELETE] ... FROM tbl
EXPLAIN ANALYZE 기능을 통해 쿼리의 실행 계획과 단계별 소요 시간을 확인 가능
실행 계획 분석
EXPLAIN 명령을 통해 실행 계획 확인 가능
컬럼명
의미
특징
id
단위 SELECT 쿼리별로 부여되는 식별자 값
하나의 SELECT 문장에서 여러 개의 테이블이 조인되는 경우는 id 값이 증가하지 않고 같은 id 값이 부여 실행 계획의 id 칼럼이 테이블의 접근 순서를 의미하지 않음
select_type
SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼
SIMPLE: UNION이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리 PRIMARY: UNION, 서브쿼리를 사용하는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽에 있는 단위 쿼리 UNION: UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리 DEPENDENT UNION: UNION 또는 UNION ALL 로 결합하는 쿼리가 외부 쿼리에 의해 영향을 받을 때 내부 쿼리에 표시(IN 절에 UNION이 들어있는 경우) UNION RESULT: UNION 결과를 담아두는 임시 테이블 SUBQUERY: FROM절 이외에서 사용되는 서브쿼리만 표(FROM절에 사용된 경우 DERIVED 표시) DEPENDENT SUBQUERY: 서브쿼리가 바깥쪽 SELECT 쿼리에서 정의된 칼럼을 사용하는 경우(안쪽 서브쿼리 결과가 바깥쪽에 의존적) DERIVED: FROM절에서 서브쿼리가 사용되면서 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 경우(쿼리를 튜닝하기 위해 실행 계획의 DERIVED가 있는지 확인, 서브 쿼리를 조인으로 튜닝) UNCACHEABLE SUBQUERY: 하나의 쿼리 문장에 조건이 똑같은 서브쿼리가 실행될 때는 이전의 실행 결과를 그대로 캐시 공간에 담아두어 재사용 UNCACHEABLE UNION: UNCACHEABLE + UNION MATERIALIZED: 서브쿼리의 내용을 임시 테이블로 구체화한 후 임시 테이블을 조인하는 형태로 최적화 하는 경우(서브쿼리 부분이 먼저 처리되어 임시 테이블로 구체화한다는 것을 알 수 있음
table
MySQL 서버의 실행계획은 단위 SELECT 기준이 아니라 테이블 기준으로 표시
테이블 이름에 별칭이 부여된 경우 별칭이 표시
partitions
파티션 관련 실행 계획 확인 가능
type
MySQL 서버가 레코드를 어떤 방식으로 읽었는지를 의미
const, eq_ref, ref, range (후술 예정)
possible_keys
옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 인덱스의 목록
무시해도 괜찮음
key
key 칼럼에 표시되는 인덱스는 최종 선택된 실행 계획에서 사용하는 인덱스
쿼리를 튜닝 시 이 칼럼에 의도했던 인덱스가 나오는지 확인하는 것이 중요 (후술 예정)
key_len
다중 칼럼으로 만들어진 인덱스에서 쿼리를 처리하기 위해 몇 개의 컬럼이 사용됐는지를 판단할 때 사용되는 컬럼
인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려줌
ref
접근 방법이 ref(type에서 확인)인 경우 참조 조건으로 어떤 값이 제공됐는지 출력
무시해도 괜찮음
rows
효율성 판단을 위해 예측했던 레코드 건수 출력
옵티마이저는 해당 쿼리가 얼마나 많은 레코드를 읽고 비교해야 하는지 예측해서 비용을 산정 (후술 예정)
filtered
필터링되고 남은 레코드의 비율 출력
조인을 사용할 때 일치하는 레코드 수가 적은 테이블이 드라이빙 테이블로 선정해야 함 rows 칼럼 * filtered 칼럼 의 결과로 비교 최종적으로 일치하는 레코드의 건수가 적은 테이블이 드라이빙 테이블로 선정
extra
내부에서 처리되는 알고리즘에 대해 보여줌
요약하면 가장 중요함 (후술 예정)
그 중 중요하게 봐야 하는 부분은 type, key, rows,extra 컬럼
type
MySQL 서버가 레코드를 어떤 방식으로 읽었는지를 나타냄
const
테이블의 레코드 건수와 관계없이 쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 WHERE 조건을 가지고 반드시 1건 만을 반환하는 쿼리의 처리 방식
select * from departments where dept_no = 'd009';
+---------+------------------+
| dept_no | dept_name |
+---------+------------------+
| d009 | Customer Service |
+---------+------------------+
1 row in set
explain select * from departments where dept_no = 'd009';
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| 1 | SIMPLE | departments | <null> | const | PRIMARY | PRIMARY | 16 | const | 1 | 100.0 | <null> |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
type은 const, rows는 1
eq_ref
여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시
조인에서 처음 읽은 테이블의 칼럼 값을 그 다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 컬럼의 검색 조건에 사용할 때 eq_ref 출력
주로 <,>,IS NULL, BETWEEN, IN, LIKE 등의 연산자를 이용해 인덱스를 검색할 때 사용
일반적으로 애플리케이션의 쿼리가 가장 많이 사용하는 접근 방법
얼마나 많은 레코드를 검색하느냐에 따라 성능 차이가 발생하는 접근 방법
MySQL에서는 보통 인덱스를 통해 읽어야할 레코드가 전체 테이블의 20~25 %가 넘어갈 경우, 풀스캔이 발생할 수 있음
따라서 조회 쿼리 작성 시 검색 레코드를 최대 20% 미만으로 설계하는 것을 권장
explain select * from employees where emp_no between 10000 and 11000;
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+------+----------+-------------+
| 1 | SIMPLE | employees | <null> | range | PRIMARY | PRIMARY | 4 | <null> | 1000 | 100.0 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+------+----------+-------------+
index
일반적으로 작업 범위를 제한하는 조건이 아니므로 빠른 응답을 사용자에게 보내 줘야하는 서비스에서 적합하지 않은 실행 계획
인덱스 풀스캔, 흔히 효율적으로 인덱스를 타고 있다고 생각할 수 있지만, 처음부터 끝까지 해당 인덱스를 풀스캔 하는 실행 계획
그래도 일반적으로 인덱스는 데이터 파일 전체보다 크기가 작으므로 풀 테이블 스캔보다는 빠르게 처리
range 나 const, ref 같은 접근 방법으로 인덱스를 사용하지 못하는 경우
다중 컬럼 인덱스에서 (첫번째+두번째, 세번째 X) or (첫번째 + 세번째) 조건을 충족하는 경우
인덱스에 포함된 컬럼만으로 처리할 수 있는 쿼리인 경우 (즉, 데이터 파일을 읽지 않아도 되는 경우)
인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우 (즉, 별도의 정렬 작업을 피할 수 있는 경우)
all
일반적으로 작업 범위를 제한하는 조건이 아니므로 빠른 응답을 사용자에게 보내 줘야하는 서비스에서 적합하지 않은 실행 계획
테이블 풀스캔을 의미
모든 접근 방법으로는 처리할 수 없을때 가장 마지막에 선택하는 가장 비효율적인 방법
대부분 인덱스가 없는 쿼리에서 많이 발생
인덱스의 순서를 지키지 않았을 때
IN() 조건이 많은 경우 발생할 수 있음
key
현재 쿼리에서 사용된 인덱스를 의미
따라서 쿼리를 튜닝할 때는 key 컬럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요
rows
현재 쿼리를 처리하기 위해 얼마나 많은 레코드를 읽고 체크해야 하는지를 의미
extra
컬럼의 이름과는 달리, 쿼리 실행 계획에서 성능에 관련된 중요한 내용이 표시되는 컬럼
내부 처리 알고리즘에 대한 설명
Using where
주로 MySQL 엔진 레이어에서 별도의 가공을 통해 필터링 작업을 처리한 경우에 출력
explain select * from employees where emp_no between 10001 and 10100 and first_name like 'G%';
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+------+----------+-------------+
| 1 | SIMPLE | employees | <null> | range | PRIMARY | PRIMARY | 4 | <null> | 100 | 11.11 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+------+----------+-------------+
Using filesort
Order by 작업 시 인덱스를 사용하지 못할 때 출력
Using index
커버링 인덱스라고 하며, 데이터 파일을 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 출력
# first_name을 이용해서 일치하는 레코드를 찾은 후, birth_date을 다시 조회해야 되기 때문에 옵티마이저는 인덱스를 사용하는 것보다 풀테이블 스캔이 효율적이라고 판단
explain select first_name, birth_date from employees where first_name between 'Babette' and 'Gad';
+----+-------------+-----------+------+------+---------+--------+-----------------------------+
| id | select_type | table | type | key | key_len | rows | Extra |
+----+-------------+-----------+------+------+---------+--------+-----------------------------+
| 1 | SIMPLE | employees | ALL | NULL | NULL | 300473 | Using where; Using filesort |
+----+-------------+-----------+------+------+---------+--------+-----------------------------+
# birth_date를 제거하면 풀테이블 스캔이 아닌 인덱스 레인지 스캔을 처리
explain select first_name from employees where first_name between 'Babette' and 'Gad';
+----+-------------+-----------+-------+--------------+---------+-----------------------------+
| id | select_type | table | type | key | key_len | Extra |
+----+-------------+-----------+-------+--------------+---------+-----------------------------+
| 1 | SIMPLE | employees | range | ix_firstname | 58 | Using where; Using index |
+----+-------------+-----------+-------+--------------+---------+-----------------------------+
# 풀 스캔 쿼리 (57965 rows in set (0.35 sec)) - first_name에 index가 설정되지 않은 경우
explain select first_name, birth_date from employees where first_name between 'Aamer' and 'Ebbe' order by first_name;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299101 | 11.11 | Using where; Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
# 위와 같은 풀스캔을 방지하기 위해 where 조건과 order by 절을 만족 시킬 수 있는 first_name 인덱스를 생성
alter table employees add index ix_first_name (first_name);
explain select first_name, birth_date from employees where first_name between 'Aamer' and 'Ebbe' order by first_name;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| 1 | SIMPLE | employees | NULL | ALL | ix_first_name | NULL | NULL | NULL | 299101 | 38.39 | Using where; Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
# 해결 방안
# force index 를 이용하여 실행계획을 잡아주는 방법 1 (57965 rows in set (0.17 sec))
explain select first_name, birth_date from employees force index (ix_first_name) where first_name between 'Aamer' and 'Ebbe' order by first_name;
+----+-------------+-----------+------------+-------+---------------+---------------+---------+--------+--------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+--------+--------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | range | ix_first_name | ix_first_name | 58 | NULL | 114824 | 100.0 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+--------+--------+----------+-----------------------+
# 인덱스 추가를 통한 커버링 인덱스를 유도하는 방법 2 (57965 rows in set (0.04 sec))
alter table employees add index ix_first_name_birth_date (first_name, birth_date);
explain select first_name, birth_date from employees where first_name between 'Aamer' and 'Ebbe' order by first_name;
+----+-------------+-----------+------------+-------+----------------------------------------+--------------------------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+----------------------------------------+--------------------------+---------+------+--------+----------+--------------------------+
| 1 | SIMPLE | employees | NULL | range | ix_first_name_birth_date,ix_first_name | ix_first_name_birth_date | 58 | NULL | 123068 | 100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+----------------------------------------+--------------------------+---------+------+--------+----------+--------------------------+
Using index condition
index 조건에 대하여 스토리지 엔진에서 모두 처리하는 방법
인덱스 컨디션 푸시 다운 최적화가 사용된 경우
explain select * from employees where first_name = 'Aamer' and last_name like '%sal';
+----+-------------+-----------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | ref | ix_first_name_last_name | ix_first_name_last_name | 58 | const | 228 | 11.11 | Using index condition |
+----+-------------+-----------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
ix(first_name, last_name) 이라는 인덱스가 있을때, last_name like ‘%sal’ 조건은 인덱스를 사용할 수 없는 조건
MySQL 5.5 에서는 MySQL 엔진 → Innodb 스토리지 엔진으로 last_name 은 사용할 수 없는 조건이기에 전달조차 해주지 않음
MySQL 5.6 이후 부터는 인덱스에 있는 컬럼은 모두 Innodb 스토리지 엔진으로 전달해줌으로써, 스토리지 엔진단에서 최대한 필터링까지 완료해서 MySQL 엔진으로 전달하게 되는데 이러한 처리 과정을 Using index condition 이라고 합니다.
Using Temporary
임시테이블 사용 시 발생
MySQL 서버에서는 쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시테이블을 사용하는데, 임시 테이블은 메모리상에 생성될 수 있고 디스크상에 생성될 수도 있음
Order by 와 Group by 에 명시된 칼럼이 다른 쿼리
Order by 나 Group by 에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
Distinct 와 Order by 가 동시에 쿼리에 존재하는 경우 또는 Distinct 가 인덱스로 처리되지 못하는 쿼리
Union 이나 Union distinct 가 사용된 쿼리 (select tye 칼럼이 Union result 인 경우)
im-gnar
commented
1 year ago
실행 계획
EXPLAIN명령으로 옵티마이저가 수립한 실행 계획을 확인할 수 있음통계 정보
8.0 버전부터는 인덱스 되지 않은 컬럼들에 대해 데이터 분포도(커디널리티)를 수집해서 저장하는 히스토그램 정보 도입
물론 히스토그램이 도입됐다고 기존 테이블이나 인덱스의 통계 정보가 필요하지 않은 것은 아님
테이블 및 인덱스 통계 정보
비용 기반 최적화(Cost based optimizer)에서 가장 중요한 것은 통계 정보
5.6 버전부터는 InnoDB 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로 관리할 수 있게 개선
SHOW INDEX명령을 통해 인덱스 분포를 확인SHOW TABLES LIKE '%_statsSTATS_PERSISTENT옵션을 통해 테아블 단위로 영구적인 통계 정보를 보관할지 설정 가능ALTER TABLE tbl_name STATS_PERSISTENT = 0: 5.5 이전 버전처럼 관리, stats 테이블에 저장하지 않음ALTER TABLE tbl_name STATS_PERSISTENT = 1: 통계 정보 저장ALTER TABLE tbl_name STATS_PERSISTENT = DEFAULT: 기본값(1, ON)으로 설정히스토그램
8.0 버전부터는 컬럼의 데이터 분포로들 참조할 수 있는 히스토그램 정보 활용
COLUMN_STATISTICS타입 조회를 통해 확인 가능히스토그램 타입
8.0 버전에서는 아래 두 가지 종류의 히스토그램 타입 지원
Singleton(싱글톤 히스토그램)
컬럼값 개별로 레코드 건수를 관리하는 히스토그램 (Value-Based histogram)
Equi-Height(높이 균형 히스토그램)
컬럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램(Height-Balanced histogram)
기울기가 일정한 것은 각 범위가 비슷한 값(레코드의 건수)을 가진다 것을 알 수 있음
히스토그램 삭제 및 미사용
히스토그램 삭제 작업은 테이블의 데이터를 참조하지 않고, 딕셔너리 내용만 삭제하기 때문에 다른 쿼리의 성능에 영향을 주지 않음
삭제 명령어
히스토그램을 삭제하지 않고, 옵티마이저가 히스토그램을 사용하지 않게 설정하는 방법도 존재
optimizer_switch설정다만 이 경우 전역적으로 영향을 받기 때문에 모든 쿼리가 히스토그램을 사용하지 못함
히스토그램 용도
히스토그램이 도입되기 전에 사용되던 통계 정보는 테이블의 전체 레코드 건수와 인덱스된 컬럼이 가지는 유니크한 값의 개수 정도
반면 히스토그램은 특정 컬럼이 가지는 모든 값에 대한 분포도 정보를 가지진 못하지만, 각 버킷(범위)별로 레코드의 건수와 유니크한 값의 개수 정보를 갖기 때문에 훨씬 정확한 예측을 할 수 있음
히스토그램과 인덱스
히스토그램과 인덱스는 다른 객체이기 때문에 서로 비교할 대상은 아니지만, 부족한 통계 정보를 수집하기 위해 사용된다는 측면에서는 어느 정도 공통점 존재
MySQL 서버에서는 쿼리의 실행 계획을 수립할 때 사용 가능한 인덱스들로부터 조건절에 일치하는 레코드 건수를 대략 파악하고 최종적으로 최적의 실행 계획 수립
인덱스된 컬럼을 검색 조건으로 사용하는 경우 그 컬럼의 히스토그램은 사용하지 않고 인덱스 다이브를 통해 직접 수집한 정보를 활용
히스토그램은 주로 인덱스되지 않은 칼럼에 대한 데이터 분포도를 참조하는 용도로 사용
코스트 모델
MySQL 서버가 쿼리를 처리하려면 아래의 다양한 작업을 필요로 함
전체 쿼리의 비용을 계산하는 데 필요한 단위 작업들의 비용
각 단위 작업의 비용을 사용자가 변경할 수 있는 기능을 제공하지만, 무조건 이 비용들을 바꿔서 사용해야 하는 것은 아님
실행 계획 확인
실행 계획 출력 포맷
키워드 : EXPLAIN / EXPLAIN FORMAT = JSON / EXPLAIN = TREE
SELECT, UPDATE, DELETE모두 EXPLAIN 을 통해 실행계획 확인 가능쿼리의 실행계획을 미리 확인해 봄으로써 풀 스캔 및 적절한 인덱스 사용 여부 등을 확인하여 장애 예방 가능
실행 순서는 보통 위에서 아래로 순서대로 표시
쿼리의 실행 시간 확인
키워드 : EXPLAIN ANALYZE
EXPLAIN ANALYZE기능을 통해 쿼리의 실행 계획과 단계별 소요 시간을 확인 가능실행 계획 분석
EXPLAIN명령을 통해 실행 계획 확인 가능실행 계획의 id 칼럼이 테이블의 접근 순서를 의미하지 않음
SIMPLE: UNION이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리PRIMARY: UNION, 서브쿼리를 사용하는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽에 있는 단위 쿼리UNION: UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리DEPENDENT UNION: UNION 또는 UNION ALL 로 결합하는 쿼리가 외부 쿼리에 의해 영향을 받을 때 내부 쿼리에 표시(IN 절에 UNION이 들어있는 경우)UNION RESULT: UNION 결과를 담아두는 임시 테이블SUBQUERY: FROM절 이외에서 사용되는 서브쿼리만 표(FROM절에 사용된 경우DERIVED표시)DEPENDENT SUBQUERY: 서브쿼리가 바깥쪽 SELECT 쿼리에서 정의된 칼럼을 사용하는 경우(안쪽 서브쿼리 결과가 바깥쪽에 의존적)DERIVED: FROM절에서 서브쿼리가 사용되면서 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 경우(쿼리를 튜닝하기 위해 실행 계획의 DERIVED가 있는지 확인, 서브 쿼리를 조인으로 튜닝)UNCACHEABLE SUBQUERY: 하나의 쿼리 문장에 조건이 똑같은 서브쿼리가 실행될 때는 이전의 실행 결과를 그대로 캐시 공간에 담아두어 재사용UNCACHEABLE UNION: UNCACHEABLE + UNIONMATERIALIZED: 서브쿼리의 내용을 임시 테이블로 구체화한 후 임시 테이블을 조인하는 형태로 최적화 하는 경우(서브쿼리 부분이 먼저 처리되어 임시 테이블로 구체화한다는 것을 알 수 있음const,eq_ref,ref,range(후술 예정)ref(type에서 확인)인 경우 참조 조건으로 어떤 값이 제공됐는지 출력rows 칼럼 * filtered 칼럼 의 결과로 비교
최종적으로 일치하는 레코드의 건수가 적은 테이블이 드라이빙 테이블로 선정
그 중 중요하게 봐야 하는 부분은
type,key,rows,extra컬럼typeMySQL 서버가 레코드를 어떤 방식으로 읽었는지를 나타냄
const
테이블의 레코드 건수와 관계없이 쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 WHERE 조건을 가지고 반드시 1건 만을 반환하는 쿼리의 처리 방식
type은 const,rows는 1eq_ref
여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시
조인에서 처음 읽은 테이블의 칼럼 값을 그 다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 컬럼의 검색 조건에 사용할 때
eq_ref출력type컬럼에eq_ref가 표시ref
eq_ref와는 달리 조인의 순서와 관계없이 사용되며, 동등 조건으로 검색할 때ref접근 방식이 사용range
인덱스를 하나의 값이 아니라 범위로 검색하는 경우를 의미
일반적으로 애플리케이션의 쿼리가 가장 많이 사용하는 접근 방법
얼마나 많은 레코드를 검색하느냐에 따라 성능 차이가 발생하는 접근 방법
MySQL에서는 보통 인덱스를 통해 읽어야할 레코드가 전체 테이블의 20~25 %가 넘어갈 경우, 풀스캔이 발생할 수 있음
index
인덱스 풀스캔, 흔히 효율적으로 인덱스를 타고 있다고 생각할 수 있지만, 처음부터 끝까지 해당 인덱스를 풀스캔 하는 실행 계획
all
테이블 풀스캔을 의미
모든 접근 방법으로는 처리할 수 없을때 가장 마지막에 선택하는 가장 비효율적인 방법
key현재 쿼리에서 사용된 인덱스를 의미
따라서 쿼리를 튜닝할 때는 key 컬럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요
rows현재 쿼리를 처리하기 위해 얼마나 많은 레코드를 읽고 체크해야 하는지를 의미
extra컬럼의 이름과는 달리, 쿼리 실행 계획에서 성능에 관련된 중요한 내용이 표시되는 컬럼
Using where
주로 MySQL 엔진 레이어에서 별도의 가공을 통해 필터링 작업을 처리한 경우에 출력
Using filesort
Order by 작업 시 인덱스를 사용하지 못할 때 출력
Using index
커버링 인덱스라고 하며, 데이터 파일을 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 출력
Using index condition
index 조건에 대하여 스토리지 엔진에서 모두 처리하는 방법
Using Temporary
임시테이블 사용 시 발생
MySQL 서버에서는 쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시테이블을 사용하는데, 임시 테이블은 메모리상에 생성될 수 있고 디스크상에 생성될 수도 있음