실행 계획
•
옵티마이저는 항상 최고의 실행 계획을 만들지는 못합니다.
◦
그래서 DBMS 서버에서는 EXPLAIN 명령을 통해 옵티마이저의 실행 계획을 확인할 수 있습니다.
•
MySQL 서버의 실행 계획에 큰 영향을 주는 통계 정보를 살펴봅시다. 실행 계획을 이해하는 순서와 중요한 단어들도 함께 알아봅시다.
10.1 통계 정보
•
MySQL 5.7까지는 테이블과 인덱스 정보를 사용해 실행 계획을 만들었습니다.
•
MySQL 8.0부터는 인덱스되지 않은 칼럼들에 대한 데이터 분포도도 수집하게 되었습니다.
10.1.1 테이블과 인덱스 통계 정보
•
비용 기반 최적화에서 가장 중요한 것은 통계 정보입니다.
•
MySQL 서버에서는 테이블 데이터 일부를 분석해 통계 정보를 보완합니다.
10.1.1.1 MySQL 서버의 통계 정보
•
MySQL 5.6부터 InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계 정보를 지속적으로 관리할 수 있게 되었습니다.
•
통계 정보는 mysql 데이터베이스의 innodb_index_stats 테이블과 innodb_table_stats 테이블에서 관리됩니다.
◦
STATS_PERSISTENT 옵션을 통해 통계 정보를 영구적으로 관리할지 선택할 수 있습니다. (기본값은 영구 관리)
▪
특정 테이블의 통계 정보를 영구적으로 관리하고 싶지 않을 경우 테이블 생성 시 STATS_PERSISTENT를 0으로 설정
▪
STATS_PERSISTENT를 설정하지 않으면 innodb_stats_persistent 시스템 변수의 값에 따라 결정
•
(ON이면 영구 저장, OFF면 영구 저장 X)
•
innodb_index_stats 테이블의 통계 정보 칼럼
◦
stat_name=’n_diff_pfx%’ : 인덱스가 가진 독특한 값의 개수
◦
stat_name = n_leaf_pages : 인덱스의 리프 노드 페이지 수
◦
stat_name = size : 인덱스 트리의 전체 페이지 수
◦
stat value : stat_name에 지정된 통계의 실제 값을 저장
▪
n_diff_pfx01과 같은 통계 이름이 있다면, stat_value는 해당 인덱스의 첫 번째 칼럼에 대해 데이터 집합 내에서 유일한 값의 수를 나타냄.
▪
이 값은 쿼리 옵티마이저가 인덱스를 얼마나 효율적으로 사용할 수 있을지를 추정하는 데 사용
▪
쿼리 플래너(query planner)가 쿼리 실행 계획을 수립할 때 인덱스 선택의 근거로 사용
◦
sample_size : 통계를 수집할 때 사용된 데이터 샘플의 크기
▪
데이터베이스가 인덱스 통계를 계산하기 위해 얼마나 많은 데이터를 검토했는지 표시
▪
샘플 크기가 크면 큰 만큼 해당 통계 값이 더 정확할 가능성이 높다.
▪
반면, 작은 샘플 크기는 통계의 정확도가 낮을 수 있음을 의미할 수 있음.
SELECT *
FROM mysql.innodb_index_stats // rds
WHERE database_name = 'xxxxx' AND table_name = 'xxxx';
SQL
복사
•
innodb_table_stats
◦
n_rows : 테이블의 전체 레코드 수
◦
clustered_index_size : 주 키의 크기(InnoDB 페이지 수)
◦
sum_of_other_index_sizes : 주 키를 제외한 인덱스의 크기(InnoDB 페이지 수)
SELECT *
FROM mysql.innodb_table_stats // rds
WHERE database_name = 'xxxxx' AND table_name = 'xxxx';
SQL
복사
통계 정보는 아래와 같은 이벤트들이 발생하면 갱신된다.
•
테이블이 새로 오픈되는 경우
•
테이블의 레코드가 대량으로 변경되는 경우
•
ANALYZE TABLE 명령이 실행되는 경우
•
SHOW TABLE STATUS 명령이나 SHOW INDEX FROM 명령이 실행되는 경우
◦
갑자기 통계 정보가 변경되면 의도치 않게 실행 계획이 변경되는 문제가 발생할 수 있는데, innodb_stats_auto_recalc 시스템 변수의 값을 OFF로 설정하면 이를 막을 수 있다
◦
또한, 통계 정보를 자동으로 수집할지 여부도 옵션을 통해 테이블 단위로 조정할 수 있다.
통계 정보를 수집할 때 몇 개의 InnoDB 테이블 블록을 샘플링할지 설정하는 시스템 변수
1.
innodb_stats_transient_sample_pages
a.
기본값은 8
b.
자동으로 통계가 수집될 때 8개의 페이지만 분석하여 통계 정보로 활용함을 의미.
2.
innodb_stats_persistent_sample_pages
•
기본 값은 20
◦
ANALYZE TABLE 명령을 실행하면 임의로 20개의 페이지를 분석하고 그 결과를 통계 정보 테이블에 저장.
•
더 정확한 통계 정보를 수집하려면 이 시스템 변수의 값을 높게 설정.
10.1.2 히스토그램
•
MySQL 8.0 버전부터 칼럼의 데이터 분포도를 참조할 수 있는 히스토그램 정보를 활용할 수 있게 되었다.
•
해당 정보를 활용하여, 쿼리 실행 계획 최적화함
10.1.2.1 히스토그램 정보 수집 및 삭제
•
MySQL 8.0에서는 칼럼 단위로 히스토그램 정보가 관리.
◦
이를 수집하기 위해서는 ANALYZE TABLE ... UPDATE HISTOGRAM 명령을 실행.
◦
ANALYZE TABLE (테이블명) 으로 가능
•
information_schema 데이터베이스의 column_statistics 테이블을 select 해보면 히스토그램 정보를 확인할 수 있다.
◦
자매품 mariadb에는 존재하지 않음..!
▪
생성
•
ANALYZE TABLE 테이블명 PERSISTENT FOR COLUMNS (열명 나열) INDEXES();
▪
조회
•
SELECT * FROM mysql.column_stats;
◦
histogram-type은 2가지 존재
▪
singleton(싱글톤 히스토그램)
•
칼럼값 개별로 레코드 건수를 관리하는 히스토그램, Value-Based 히스토그램 또는 도수 분포라고도 불린다.
▪
Equi-height(높이 균형 히스토그램)
•
칼럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램, Height-Balanced 히스토그램이라고도 불린다.
◦
생성된 히스토그램은 아래 쿼리를 통해 삭제할 수 있다
▪
다만 삭제되면, 쿼리의 실행 계획이 달라질 수 있으므로 주의
10.1.2.2 히스토그램의 용도
•
히스토그램이 도입되기 전의 통계정보는 테이블의 전체 레코드 건수와 인덱스된 칼럼이 가지는 유니크한 값의 개수 정도였다.
•
히스토그램은 특정 칼럼이가지는 모든 값에 대한 분포도 정보를 가지지는 않지만 각 범위(버킷)별로 레코드의 건수와 유니크한 값의 개수 정보를 가지기 때문에 훨씬 정확한 예측을 할 수 있다.
◦
히스토그램 정보가 없으면 옵티마이저는 데이터가 균등하게 분포돼 있을 것으로 예측한다. 하지만 히스토그램이 있으면 특정 범위의 데이터가 많고 적음을 식별할 수 있다.
◦
각 칼럼에 대해 히스토그램 정보가 있으면 어느 테이블을 먼저 읽어야 조인의 횟수를 줄일 수 있을지 옵티마이저가 더 정확히 판단할 수 있다.
10.1.2.3 히스토그램과 인덱스
•
MySQL 서버에서는 쿼리의 실행 계획을 수립할 때 사용 가능한 인덱스들로부터 조건절에 일치하는 레코드 건수를 대략 파악하고 최종적으로 가장 나은 실행 계획을 선택한다.
◦
레코드 건수를 예측하기 위해 옵티마이저는 실제 인덱스의 B-Tree를 샘플링해서 살펴본다. (인덱스 다이브)
◦
인덱스된 칼럼을 검색 조건으로 사용하는 경우 그 칼럼의 히스토그램은 사용하지 않고 실제 인덱스 다이브를 통해 직접 수집한 정보를 활용한다.
◦
실제 검색 조건의 대상 값에 대한 샘플링을 실행하는 것이므로 항상 히스토그램보다 정확한 결과를 기대할 수 있기 때문이다.
◦
히스토그램은 주로 인덱스되지 않은 칼럼에 대한 데이터 분포도를 참조하는 용도로 사용된다.
10.1.3 코스트 모델(Cost Model)
Cost Model: 전체 쿼리의 비용을 계산하는 데 필요한 단위 작업들의 비용
MySQL의 코스트 모델은 다음 2개 테이블에 저장되어 있는 설정값을 사용한다.
•
server_cost: 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
•
engine_cost: 레코드를 가진 데이터 페이지를 가져오는데 필요한 비용 관리
두 테이블은 아래 5개의 컬럼을 공통으로 가지고있다.
◦
cost_name: 코스트 모델의 각 단위 작업
◦
default_value: 각 단위 작업의 비용(기본값, MySQL 서버 소스 코드에 설정된 값)
◦
cost_value: DBMS 관리자가 설정한 값(NULL이면 default_value 값 사용)
◦
last_updated: 단위 작업의 비용이 변경된 시점
◦
comment: 비용에 대한 추가 설명
•
단위 작업의 종류
◦
디스크로부터 데이터 페이지 읽기
◦
메모리(InnoDB 버퍼 풀)로부터 데이터 페이지 읽기
◦
인덱스 키 비교
◦
레코드 평가
◦
메모리 임시 테이블 작업
◦
디스크 임시 테이블 작업
상세
•
각 단위 작업에 설정되는 비용이 커지면 어떤 실행 계획의 비용이 변하는지 파악하는 것이 중요
•
위의 각 단위 작업의 비용에 대한 값을 변경할 수 있지만 일반적으로 기본 값을 유지하자
10.2 실행 계획 확인
•
MySQL 서버의 실행 계획은 DESC 또는 EXXPLAIN 명령으로 확인할 수 있다.
•
FORMAT 옵션을 사용해 실행 계획의 표시 방법을 JSON이나 TREE, 단순 테이블 형태로 선택할 수 있다.
◦
EXPLAIN [FORMAT=TREE or JSON] (테이블이 기본값)
10.2.2 쿼리의 실행 시간 확인
•
EXPLAIN ANALYZE 기능을 통해 쿼리의 실행 계획과 단계별 소요 시간을 확인할 수 있다.
◦
실제 쿼리를 실행하여 사용될 실행 계획과 소요된 시간을 보여준다
◦
따라서 시간이 많이 걸리는 쿼리는 EXPLAIN 만을 이용해서 분석할 것