백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.
1. 테이블 및 인덱스 통계 정보
MySQL 서버의 통계 정보
- InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로 관리할 수 있음
- 서버가 재시작되어도 재사용이 가능함
통계 테이블
- mysql.innodb_table_stats
- mysql.innodb_index_stats
통계 테이블 컬럼
select * from mysql.innodb_index_stats where table_name ='employees';
- stat_name='d_diff_pfx01': 인덱스가 가진 유니크한 값의 개수
- stat_name='n_leaf_pages': 인덱스의 리프 노드 페이지 개수
- stat_name='size': 인덱스 트리의 전체 페이지 개수
- stat_name='n_rows': 테이블의 전체 레코드 수
- stat_name='clustered_index_size': 프라이머리 키 크기
- stat_name='sum_of_other_index_sizes': 프라이머리 키를 제외한 인덱스 크기
테이블 옵션
create table tab (
fd1 INT
)
ENGINE=InnoDB
STATS_PERSISTENT=1
- STATS_PERSISTENT: 테이블 단위로 영구적인 통계 정보를 보관할지 결정
시스템 변수
- innodb_stats_persistent: 테이블 생성 시, 통계정보를 영구적으로 보관
- innodb_stats_auto_recalc: 통계 정보 자동 수집 여부
- 1: 자동 수집
- 0: ANALYZE TABLE 명령 수행 때만 수집
- innodb_stats_transient_sample_pages: 자동으로 통계가 수집되면, n개 페이지만 샘플링해서 통계 자료로 활용
- innodb_stats_persistent_sample_pages: "ANALYZE TABLE" 명령이 실행되면, n개 페이지만 샘플링해서 통계 자료로 활용
2. 히스토그램
- 칼럼의 데이터 분포도를 분석하여 통계를 저장하는 자료구조
- 훨씬 더 정확한 실행 계획을 수립하는데 정보를 줌
정보 수집 및 삭제
수동 수집
analyze table employees.employees update histogram on gender, hire_date;
select * from information_schema.column_statistics where table_name = 'employees';
- ANALYZE TABLE ... UPDATE HISTOGRAM
- 수집된 히스토그램 정보는 시스템 딕셔너리에 저장됨
로드
- 서버가 시작될 때 수행됨
- 딕셔너리의 히스토그램 정보를 information_schema 데이터베이스의 column_statistics 테이블로 로드함
삭제
analyze table employees.employees drop histogram on gender, hire_date;
HISTOGRAM 컬럼 필드 (column_statistics)
{
"buckets": [
[
1,
0.5994075206651058
],
[
2,
1
]
],
"data-type": "enum",
"null-values": 0,
"collation-id": 45,
"last-updated": "2025-03-11 23:04:27.360590",
"sampling-rate": 0.3480459309254124,
"histogram-type": "singleton",
"number-of-buckets-specified": 100
}
- sampling-rage: 히스토그램 정보를 수집하기 위해 스캔한 페이지의 비율
- histogram-type: 히스토그램 종류
- number-of-buckets-specified: 히스토그램을 생성할 때 설정했던 버킷 갯수
타입
Singleton (Value-Based)
- 각 컬럼 값별로 레코드 건수를 관리하는 히스토그램
- 유니크한 값의 개수가 상대적으로 적은 경우 사용됨
- 모든 레코드 건수 비율은 누적으로 표시됨
Equi-Height (Height-Balanced)
- 컬럼 값의 범위를 일정한 개수로 나누어 관리하는 히스토그램
- 모든 레코드 건수 비율은 누적으로 표시됨
인덱스 다이브
- 조건절에 인덱스 컬럼이 있을 경우, 옵티마이저는 레코드 건수를 파악하기 위해 B-Tree를 샘플링해서 레코드 건수를 파악
- 검색 조건에 인덱스 컬럼이 있다면, 히스토그램은 사용하지 않고 실제 인덱스 다이브를 통해 직접 수집한 정보를 활용함
- 히스토그램은 샘플링이므로 항상 인덱스보다 정확한 결과의 수집이 가능함
- 어느 정도의 비용이 필요하며, 때로는 비용이 큼
상태 변수
- condition_fanout_filter: 히스토그램 사용 여부
시스템 변수
- historgram_generation_max_mem_size: 히스토그램 샘플링 크기
3. 코스트 모델
- 전체 쿼리의 비용을 계산하는 데 필요한 단위 작업들의 비용
- MySQL 서버는 사용자의 쿼리에 대해 이러한 다양한 작업이 얼마나 필요한지 예측하고 전체 작업 비용을 계산
- 계산된 결과를 바탕으로 최적의 실행 계획을 찾음
테이블
server_cost
- 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
- 컬럼
- cost_name: 코스트 모델의 각 단위 작업
- default_value: 각 단위작업의 비용
- cost_value: DBMS 관리자가 설정한 값
- last_updated
- comment
- cost_name
- disk_temptable_create_cost: 디스크 임시 테이블 생성
- disk_temptable_row_cost: 디스크 임시 테이블의 레코드 읽기
- memory_temptable_create_cost: 메모리 임시 테이블 생성
- memory_temptable_row_cost: 메모리 임시 테이블의 레코드 읽기
- key_compare_cost
- 키 값의 비교 작업이 필요한 비용
- 정렬 작업과 같이 키 값 비교 처리가 많은 경우 높아짐
- row_evaluate_cost
- 스토리지 엔진이 반환한 레코드가 쿼리의 조건에 일치하는지를 평가하는 단위 작업
- 인덱스를 잘 사용하면 작아지고, 풀 테이블 스캔과 같은 작업시 값이 높아짐
engine_cost
- 레코드를 가진 데이터 페이지를 가져오는 데 필요한 비용 관리
- 컬럼
- cost_name, default_value, cost_value, last_updated, comment
- engine_name: 비용이 적용된 스토리지 엔진
- device_type: 디스크 타입
- cost_name
- io_block_read_cost: 디스크 데이터 페이지 읽기
- memory_block_read_cost: 메모리 데이터 페이지 읽기
확인하기
explain format=json select * from employees where first_name='Matt';
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "81.55"
} /* cost_info */,
"table": {
"table_name": "employees",
"access_type": "ref",
"possible_keys": [
"ix_firstname"
] /* possible_keys */,
"key": "ix_firstname",
"used_key_parts": [
"first_name"
] /* used_key_parts */,
"key_length": "58",
"ref": [
"const"
] /* ref */,
"rows_examined_per_scan": 233,
"rows_produced_per_join": 233,
"filtered": "100.00",
"cost_info": {
"read_cost": "58.25",
"eval_cost": "23.30",
"prefix_cost": "81.55",
"data_read_per_join": "30K"
} /* cost_info */,
"used_columns": [
"emp_no",
"birth_date",
"first_name",
"last_name",
"gender",
"hire_date"
] /* used_columns */
} /* table */
} /* query_block */
}