: 8.0버전부터는 인덱스되지 않은 칼럼들에 대해서도 데이터 분포도를 수집해서 저장하는 히스토그램 정보가 도입됨, 히스토그램이 도입되었다고 기존의 테이블이나 인덱스의 통계 정보가 필요하지 않은 것은 아님
1. 테이블 및 인덱스 통계 정보
: 8.0버전부터 옵티마이저가 사용하는 비용 기반 최적화에서 가장 중요한 것은 통계 정보, 정확하지 않으면 엉뚱한 방향으로 잘못된 쿼리를 실행할 수 있기 때문
1.
MySQL 서버의 통계 정보
: MySQL 5.6버전부터는 InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로 관리할 수 있게 개선됨, 각 테이블의 통계 정보를 mysql 데이터베이스의 innodb_index_stats 테이블과
innodb_table_stats 테이블로 관리할 수 있게 개선, 이렇게 통계 정보를 테이블로 관리하여 MySQL 서버의 재시작에도 통계 정보를 유지할 수 있게 되었음
⇒ innodb_stats_persistent 시스템 설정 변수를 통해 이런 정보를 어디에 저장할 지, 결정할 수 있으며 0, 1, DEFAULT 등의 옵션이 있다.
2. 히스토그램
: 8.0으로 업그레이드되면서 칼럼의 데이터 분포도를 참조할 수 있는 히스토그램 정보를 활용할 수 있게 되었다.
1.
히스토그램 정보 수집 및 삭제
: 8.0에서 히스토그램 정보는 칼럼단위로 관리되는데, 이는 자동으로 수집되지 않고 ANALYZE TABLE … UDPATE HISTOGRAM 명령을 실행해 수동으로 수집 및 관리, 수집된 히스토그램 정보는 시스템 딕셔너리에
함께 저장되고, MySQL 서버가 시작할 때 딕셔너리의 히스토그램 정보를 information_schema 데이터베이스의 column_statistics 테이블로 로드
: MySQL 8.0에서는 2종류의 히스토그램 타입이 지원
•
Singleton: 칼럼값 개별로 레코드 건수를 관리하는 히스토그램, Value-Based 히스토그램 또는 도수 분포라고 함
•
Equi-Height: 칼럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램으로 Height-Balanced 히스토그램이라고도 불림
: 히스토그램은 버킷 단위로 구분되어 레코드 건수나 칼럼값의 범위가 관리되는데, 싱글톤은 칼럼이 가지는 값별로 버킷이 할당되며 높이 균형 히스토그램은 개수가 균등한 칼럼값의 범위별로 하나의 버킷이
할당, 싱글톤 히스토그램은 각 버킷이 칼럼의 값과 발생 빈도의 비율의 2개 값을 가지며, 높이 균형 히스토그램은 각 버킷이 범위 시작 값과 마지막 값, 그리고 발생 빈도율과 각 버킷에 포함된 유니크한
값의 개수 등 4개의 값을 가짐
2.
히스토그램의 용도
: 히스토그램을 사용했을 때, 사용하지 않았을 때 차이점
•
히스토그램을 사용하지 않은 실행 계획
: 실제값이 63.84%임에도 11.11%로 예상
•
히스토그램을 사용한 실행 계획
: 실제값이 63.84%이며 60.82%로 예상
⇒ 단순 통계 정보만 사용한 경우와 히스토그램을 이용한 경우의 차이가 매우 큰 것을 알 수 있다.
: 히스토그램 정보가 없으면 옵티마이저는 데이터가 균등하게 분포하고 있을 것으로 예측하는데, 히스토그램이 있다면 특정 범위의 데이터가 많고 적음을 식별할 수 있다.
3.
히스토그램과 인덱스
: 히스토그램과 인덱스는 완전히 다른 객체로 서로 비교할 대상은 아니지만 MySQL 서버에서 인덱스는 부족한 통계 정보를 수집하기 위해 사용되는 측면에서 어느 정도 공통점을 가진다고 볼 수 있음
: MySQL 서버에서는 쿼리의 실행 계획을 수립할 때 사용 가능한 인덱스들로부터 조건절에 일치하는 레코드 건수를 대략 파악하고 최종적으로 가장 나은 실행 계획을 선택
3. 코스트 모델
: MySQL 서버가 쿼리를 처리하려면 다음과 같은 다양한 작업을 필요로 함
•
디스크로부터 데이터 페이지 읽기
•
메모리로부터 데이터 페이지 읽기
•
인덱스 키 비교
•
레코드 평가
•
메모리 임시 테이블 작업
•
디스크 임시 테이블 작업
: 이런 다양한 작업들이 얼마나 필요한지 예측하고 전체 작업 비용을 계산한 결과를 바탕으로 최적의 실행 계획을 찾는데, 이를 코스트 모델이라고 함
: 이런 작업의 비율들을 5.7버전부터 조정할 수 있게 되었음
