MariaDB 히스토그램 (옵티마이저가 실행계획 생성시 참고하는 데이터 분포 정보)

MariaDB 히스토그램 (옵티마이저가 실행계획 생성시 참고하는 데이터 분포 정보)

 

·       Version : MariaDB 10.0

 

히스토그램은 테이블의 컬럼값의 분포를 분석할 수 있는 통계 정보이다. 히스토그램 정보가 없는 경우에는 하나의 컬럼에서 유니크한 값의 개수에 기반해서 대략적인 분포도를 예측하는 형태로 실행 계획의 비용이 계산되었다.  이 또한 인덱스가 생성된 컬럼에 대해서만 유니크한 개수가 관리되었다. 이처럼 히스토그램이 관리되지 않으면 컬럼에 대한 데이터 분포를 잘 못 예측하여 옵티마이저가 잘못된 실행 계획을 생성할 수도 있다.

 

히스토그램  기반 통계는 옵티마이저가 선택한 쿼리 계획을 개선하기 위한 메커니즘으로 MariaDB 10.0 버전에서 도입되었다.  이전까지는 실행계획을 생성할 때 인덱싱 되지 않은 컬럼의 조건은 모두 무시되었다. 이번에 도입된 히스토그램은 메커니즘은 인덱스로 만들어진 컬럼 뿐만 아니라 인덱싱되지 않은 컬럼에 대해서 모두 히스토그램 정보를 저장할 수 있도록 개선되었다. 모든 테이블의 모드 컬럼에 대해서 최대값, 최소값, NULL 값을 가진 비율을 계산하여 mysql.column_stats 라는 테이블에 저장되어 관리된다.

 

MariaDB 10.0은 Height-Balanced Histogram 알고리즘을 사용한다. 이 알고리즘은 컬럼의 모든 값을 정렬해서 동일한 레코드 건수가 되도록 그룹을 몇 개로 나눈다. 그리고 각 그룹의 마지막 값 (정렬된 상태에서 가장 큰값)을 히스토그램에 저장한다. MariaDB에서는 각 그룹의 개수를 histogram_size 라는 시스템 설정 변수로 제어할 수 있다. histogram_size는 0 ~ 255 값을 가지며, 크게 설정하면 히스토그램의 정확도는 높아지겠지만 그만큼 저장 공간 및 분석 시간이 필요하다.

 

 

MariaDB에서는 히스토그램 값을 각 그룹별로 한바이트씩 할당해서 VARBINARY(255) 타입의 histogram 컬럼에 저장한다. 실제 저장되는 값은 단순 그룹의 최대값이 아닌 아래와 같은 계산을 거쳐서 그 결과를 histogram 컬럼의 각 바이트에 순서대로 저장한다.  a값이 클수록 히스토그램의 정확도는 높아지지만 저장공간을 많이 사용한다.

그룹 최대값 / (컬럼 최대값 – 칼럼 최소값) * a

 

MariaDB에서 히스토그램 생성은 ANALYZE TABLE 명령으로 다른 통계 정보와 함께 생성된다. histogram_size의 기본값은 0으로 히스토그램을 사용하지 않는다는 것을 뜻한다.  히스토그램을 사용하려면 반드시 histogram_size를 0보다 큰 값으로 설정한다. 또한 정확도를 위해서 histogram_type을 SINGLE_PREC_HB 또는 DOUBLE_PREC_HB로 설정 할 수 있다.

 

optimizer_user_condition_selectivity 옵션은 옵티마이저가 최상의 실행 계획을 사용할 수 있도록 통계를 제어한다.

·       optimizer_user_condition_selectivity = 1 : MariaDB 5.5에 사용되었던 선택도 방식을 그대로 유지

·       optimizer_user_condition_selectivity = 2 : 인덱스가 생성된 컬럼에 대해서만 선택도 사용

·       optimizer_user_condition_selectivity = 3 : 모든 컬럼에 대해서 선택도 사용 (히스토그램 사용 안함)

·       optimizer_user_condition_selectivity = 4 : 모든 컬럼에 대해서 선택도 사용 (히스토그램 사용)

·       optimizer_user_condition_selectivity = 5 : 모든 컬럼에 대해서 선택도 사용하면 레인지 스캔이 불가한 컬럼에 대해서 샘플링해서 선택도 사용

 

 

[참고자료]

·       Histogram-Based Statistics : https://mariadb.com/kb/en/library/histogram-based-statistics/

·       http://www.aioug.org/sangam14/images/Sangam14/Presentations/201446_garg_ppt.ppt.pdf

 

 

 

2019-09-17 / 강성욱 / http://sungwookkang.com

 

MariaDB, MariaDB 10.0, 히스토그램, histogram, optimizer_user_condition_selectivity, ANALYZE TABLE, mysql.column_stats, histogram_size, histogram_type