TIL

10.1 통계 정보

비용 기반 최적화에서 통계 정보가 정확하지 않다면 비효율적인 쿼리를 실행할 수도 있다.
- 레코드가 1억 건 이상 존재하는데 10건 미만인 것처럼 통계가 갱신되지 않았다면 1억 건을 풀 테이블 스캔해 버릴 수도 있다.
MySQL 8.0 버전에서 통계 정보 관리가 어떻게 개선되었는지 살펴 보자.

MySQL 5.5 버전까지는 테이블의 통계 정보가 메모리에만 관리되었다.
- 메모리에서 관리하면 MySQL 서버를 재시작할 경우 통계 정보가 모두 사라진다.
MySQL 5.6 버전부터 InnoDB 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로 관리할 수 있게 개선되었다.
- 통계 정보는 mysql 데이터베이스의 innodb_index_stats 테이블과 innodb_table_stats 테이블로 관리한다.

테이블을 생성할 때 STATS_PERSISTENT 옵션 설정을 통해 테이블 단위로 영구적인 통계 정보 보관 여부를 결정할 수 있다.
- STATS_PERSISTENT=0: 5.5 이전의 방식과 똑같이 통계 정보를 메모리에만 관리
- STATS_PERSISTENT=1: 통계 정보를 mysql 데이터베이스의 innodb_index_stats와 innodb_table_stats 테이블에 영구 저장
- STATS_PERSISTENT=DEFAULT: 테이블 생성 시 별도로 STATS_PERSISTENT 옵션을 지정하지 않은 것과 동일하며 innodb_stats_persistent 시스템 변수 값으로 영구 저장 여부를 결정한다.
  - innodb_stats_persistent 변수는 기본적으로 ON이기 때문에 테이블 통계 정보를 기본적으로 영구 저장한다.
ALTER TABLE 명령으로 영구 저장 옵션을 변경할 수 있다.
- ALTER TABLE employees.employees STATS_PERSISTENT=1;

innodb_index_stats.stat_name=’n_diff_pfx%’: 인덱스가 가진 유니크 값 개수
innodb_index_stats.stat_name=’n_leaf_pages’: 인덱스 리프 노드 페이지 개수
innodb_index_stats.stat_name=’size’: 인덱스 트리 전체 페이지 개수
innodb_table_stats.n_rows: 테이블 전체 레코드 건수
innodb_table_stats.clustered_index_size: 프라이머리 키 크기(InnoDB 페이지 개수)
innodb_table_stats.sum_of_other_index_sizes: 프라이머리 키를 제외한 인덱스 크기(InnoDB 페이지 개수)

히스토그램 정보는 칼럼 단위로 관리된다.
자동으로 수집되지 않고 ANALYZE TABLE … UPDATE HISTOGRAM 명령을 통해 수동으로 수집 및 관리된다.
- ANALYZE TABE employees.employees UPDATE HISTOGRAM ON gender, hire_date;
MySQL 서버가 시작될 때 시스템 딕셔너리에 저장된 히스토그램 정보를 information_schema 데이터베이스의 column_statistics 테이블로 로드한다.
8.0 버전에서는 다음의 2종류 히스토그램 타입이 지원된다.
- Singleton(싱글톤 히스토그램) 칼럼값 개별로 레코드 건수를 관리하는 히스토그램으로, Value-based 히스토그램 또는 도수 분포라고도 불린다.
- Equi_Height(높이 균형 히스토그램): 칼럼값 범위를 균등한 개수로 구분해서 관리하는 히스토그램으로 Height-Balanced 히스토그램이라고도 불린다.
히스토그램은 버킷(Bucket) 단위로 구분되어 레코드 건수나 칼럼값 범위가 관리된다.
- 싱글톤 히스토그램은 칼럼이 가지는 값별로 버킷이 할당되며 높이 균형 히스토그램에서는 개수가 균등한 칼럼값 범위별로 하나의 버킷이 할당된다.
싱글톤 히스토그램은 주로 유니크한 값 개수가 상대적으로 적은(히스토그램 버킷 수보다 적은) 경우 사용된다.

히스토그램 정보가 없으면 옵티마이저는 데이터가 균등하게 분포돼 있을 것으로 예측하지만 실제 데이터는 항상 균등한 분포를 가지지 않는다.
히스토그램은 특정 칼럼의 모든 값에 대한 분포도 정보를 가지지 않지만 각 범위(버킷) 별로 레코드 건수와 유니크 값 개수 정보를 가지기 때문에 훨씬 정확한 예측을 할 수 있다.
각 칼럼에 대한 히스토그램 정보가 있으면 더 효율적인 실행 계획을 옵티마이저가 수립할 수 있게 된다.

실행 계획을 수립할 때 조건절에 일치하는 레코드 건수를 파악하기 위해 실제 인덱스의 B-Tree를 샘플링해서 살펴보는데 이 작업을 인덱스 다이브라고 한다.
MySQL 8.0 서버에서 인덱스된 칼럼을 검색 조건으로 사용하는 경우 그 칼럼의 히스토그램은 사용하지 않고 실제 인덱스 다이브를 통해 직접 수집한 정보를 활용한다.
- 실제 검색 조건의 대상 값에 대한 샘플링 실행이므로 항상 히스토그램보다 정확한 결과를 기대할 수 있기 때문이다.
MySQL 8.0 버전에서 히스토그램은 주로 인덱스되지 않은 칼럼에 대한 데이터 분포도를 참조하는 용도로 사용된다.
하지만 인덱스 다이브 작업은 어느 정도 비용이 필요하다.
- 특히 IN 절에 값이 많이 명시된 경우 비용이 많이 커진다.

MySQL 서버가 쿼리를 처리하려면 다양한 작업이 필요하다.
- 디스크로부터 데이터 페이지 읽기
- 메모리(InnoDB 버퍼 풀)로부터 데이터 페이지 읽기
- 인덱스 키 비교
- 레코드 평가
- 메모리 임시 테이블 작업
- 디스크 임시 테이블 작업
전체 쿼리 비용을 계산하는 데 필요한 단위 작업들의 비용을 코스트 모델이라고 한다.
이전엔 서버 소스 코드에 상수화해서 사용하던 각 단위 작업 비용을 5.7 버전부터 DBMS 관리자가 조정할 수 있게 개선되었다.
8.0으로 업그레이드되면서 칼럼의 데이터 분포를 위한 히스토그램과 각 인덱스별 메모리에 적재된 페이지 비율이 관리되고 실행 계획 수립에 사용되기 시작했다.

8.0 버전 서버의 코스트 모델은 mysql DB에 존재하는 다음 2개 테이블에 저장된 설정값을 사용한다.
- server_cost: 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
- engine_cost: 레코드를 가진 데이터 페이지를 가져오는 데 필요한 비용 관리
server_cost와 engine_cost는 공통으로 다음 5개 칼럼을 가지고 있다.
- cost_name: 코스트 모델의 각 단위 작업
- default_value: 각 단위 작업의 비용
- cost_value: DBMS 관리자가 설정한 값
- last_updated: 단위 작업의 비용이 변경된 시점
- comment: 비용에 대한 추가 설명
engine_cost 테이블은 추가로 2 칼럼을 더 가지고 있다.
- engine_name: 비용이 적용된 스토리지 엔진
- device_type: 디스크 타입
코스트 모델에서 중요한 것은 각 단위 작업에서 설정되는 비용 값이 커지면 어떤 실행 계획들이 고비용으로 바뀌고 저비용으로 바뀌는지를 파악하는 것이다.

각 단위 작업 비용을 변경할 수 있다고 해서 꼭 바꿀 필요는 없다. 코스트 모델은 깊은 지식을 필요로 한다. 전문적인 지식을 가지고 있지 않다면 기본값으로 사용해도 아무 문제없고 20년이 넘는 시간동안 기본값으로도 수많은 애플리케이션에서 잘 사용돼 왔다.