히스토그램

예전 통계 정보의 부족함

• MySQL 5.7 버전까지의 통계 정보는 단순히 인덱스된 칼럼의 유니크한 값의 개수 정도만 가지고 있었다.
  • 옵티마이저가 최적의 실행 계획을 수립하기에는 많이 부족했다.
• 옵티마이저는 이러한 부족함을 메우기 위해 실행 계획을 수립할 때 실제 인덱스의 일부 페이지를 랜덤으로 가져와 참조하는 방식을 사용했다.
• MySQL 8.0 버전으로 업그레이드되면서, MySQL 서버도 칼럼의 데이터 분포도를 참조할 수 있는 히스토그램(Histogram) 정보를 활용할 수 있게 됐다.

히스토그램 정보 수집 및 삭제

히스토그램 정보 수집

• MySQL 8.0 버전에서 히스토그램 정보는 칼럼 단위로 관리된다.
• ANALYZE TABLE ... UPDATE HISTOGRAM 명령을 실행해 수동으로 수집 및 관리된다.
  • 자동으로 수집되지 않는다.
  • 수집된 히스토그램 정보는 시스템 딕셔너리에 함께 저장된다.
• MySQL 서버가 시작될 때 딕셔너리의 히스토그램 정보를 information_schema 데이터베이스의 column_statistics 테이블로 로드한다.
  • 실제 히스토그램 정보는 column_statistics 테이블을 SELECT해서 참조할 수 있다.
• 히스토그램 정보 수집 및 조회 예제

  -- 히스토그램 정보 수집
  ANALYZE TABLE employees.employees
  UPDATE HISTOGRAM ON gender, hire_date;
  
  -- 수집된 정보 조회
  SELECT *
  FROM COLUMN_STATISTICS
  WHERE SCHEMA_NAME='employees'
  	AND TABLE_NAME='employees' \G

히스토그램 타입

💡 히스토그램은 버킷(Bucket) 단위로 구분되어 레코드 건수나 칼럼값의 범위가 관리된다.

MySQL 8.0에서 지원하는 히스토그램 타입은 다음 두 가지이다.

• Singleton (싱글톤 히스토그램)
  • 칼럼값 개별로 레코드 건수를 관리하는 히스토그램
  • Value-Based 히스토그램 또는 도수 분포라고도 불린다.
  • 칼럼이 가지는 값별로 버킷이 할당된다.
  • 각 버킷이 칼럼의 값과 발생 빈도의 비율의 2개 값을 가진다.
  • 주로 코드 값과 같이 유니크한 값의 개수가 상대적으로 적은(히스토그램의 버킷 수보다 적은) 경우 사용된다.
  • 히스토그램의 모든 레코드 건수 비율은 누적으로 표시된다.
    • 예를 들어, 값이 F와 M를 갖는 gender 칼럼의 히스토그램을 그래프로 조회했을 때 값이 M인 레코드의 비율이 0.5고 F인 레코드의 비율이 1로 표시된다면, F인 레코드의 비율은 1-0.5가 된다.
• Equi-Height (높이 균형 히스토그램)
  • 칼럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램
  • Height-Balanced 히스토그램이라고도 불린다.
  • 개수가 균등한 칼럼값의 범위별로 하나의 버킷이 할당된다.
  • 각 버킷이 범위 시작 값과 마지막 값, 그리고 발생 빈도율과 각 버킷에 포함된 유니크한 값의 개수 등 4개의 값을 가진다.
  • 칼럼값의 각 범위에 대해 레코드 건수 비율이 누적으로 표시된다.
    • 히스토그램의 버킷 범위가 뒤로 갈수록 비율이 높아지는 것으로 보인다.
    • 그래프의 기울기가 일정하다면 각 범위가 비슷한 값(레코드의 건수)을 가진다는 것을 알 수 있다.

HISTOGRAM 칼럼

information_schema.column_statistics 테이블의 HISTOGRAM 칼럼이 가진 나머지 필드들은 다음과 같은 의미를 가지고 있다.

• sampling-rate
  • 히스토그램 정보를 수집하기 위해 스캔한 페이지의 비율을 저장한다.
  • 샘플링 비율이 0.35라면 전체 데이터 페이지의 35%를 스캔해서 이 정보가 수집됐다는 것을 의미한다.
  • 샘플링 비율이 높아질수록 더 정확한 히스토그램이 되지만, 테이블을 전부 스캔하는 것은 부하가 높으며 시스템의 자원을 많이 소모한다.
  • MySLQ 서버는 histogram_generation_max_mem_size 시스템 변수에 설정된 메모리 크기에 맞게 적절히 샘플링한다.
  • histogram_generation_max_mem_size 시스템 변수의 메모리 크기는 20MB로 초기화돼 있다.
• histogram-type
  • 히스토그램의 종류를 저장한다. (히스토그램 타입)
• number-of-buckets-specified
  • 히스토그램을 생성할 때 설정했던 버킷의 개수를 저장한다.
  • 히스토그램을 생성할 때 별도로 버킷의 개수를 지정하지 않았다면 기본으로 100개의 버킷이 사용된다.
  • 버킷은 최대 1024개를 설정할 수 있지만, 일반적으로 100개의 버킷이면 충분하다.

💡 MySQL 8.0.19 미만 버전일 경우

MySQL 8.0.19 미만의 버전까지는 히스토그램 생성 시 MySQL 서버는 풀 스캔을 통해 데이터 페이지를 샘플링해서 히스토그램을 생성하므로 주의해야 한다.

MySQL 8.0.19 버전부터는 InnoDB 스토리지 엔진 자체적으로 샘플링 알고리즘을 구현했으며, 더이상 히스토그램 수집 시 풀 테이블 스캔이 필요치 않게 되었다.

히스토그램 삭제

• 히스토그램의 삭제는 ANALYZE TABLE ... DROP HISTOGRAM 을 사용한다.

  ANALYZE TABLE employees.employees
  DROP HISTOGRAM ON gender, hire_date;

• 히스토그램의 삭제 작업은 테이블의 데이터를 참조하는 것이 아니라 딕셔너리의 내용만 삭제한다.
  • 다른 쿼리 처리의 성능에 영향을 주지 않고 즉시 완료된다.
• 히스토그램이 삭제되면 쿼리의 실행 계획이 달라질 수 있으므로 주의해야 한다.

히스토그램 사용하지 않게 설정

• 히스토그램을 삭제하지 않고 MySQL 옵티마이저가 히스토그램을 사용하지 않게 하려면 optimizer_switch 시스템 변수의 값을 변경하면 된다.
• condition_fanout_filter 옵션에 의해 영향받는 다른 최적화 기능들이 사용되지 않을 수도 있으니 주의해야 한다.

-- MySQL 서버의 모든 쿼리가 히스토그램을 사용하지 않게 설정
SET GLOBAL optimizer_switch='condition_fanout_filter=off';

-- 현재 커넥션에서 실행되는 쿼리만 히스토그램을 사용하지 않게 설정
SET SESSION optimizer_switch='condition_fanout_filter=off';

-- 현재 쿼리만 히스토그램을 사용하지 않게 설정
SELECT /*+ SET_VAR(optimizer_switch='condition_fanout_filter=off') */ ...
FROM ...;

히스토그램의 용도

• MySQL 서버에 히스토그램이 도입되기 전 가지고 있던 통계 정보는 테이블의 전체 레코드 건수와 인덱스된 칼럼이 가지는 유니크한 값의 개수 정도였다.
  • 예를 들어, 테이블의 레코드가 1000건이고 어떤 칼럼의 유니크한 값 개수가 100개일 때, 해당 칼럼에 대해 동등 비교 검색을 하면 대략 10개의 레코드가 일치할 것이라고 예측한다.
• 이는 실제 응용 프로그램의 데이터는 항상 균등한 분포도를 가지지 않는다는 부분을 고려하지 못한다는 단점이 있었다.
• 이러한 단점을 보완하기 위해 히스토그램이 도입되었다.
  • 히스토그램은 특정 칼럼이 가지는 모든 값에 대한 분포도 정보를 가지지는 않지만, 각 범위(버킷)별로 레코드의 건수와 유니크한 값의 개수 정보를 가지기 때문에 훨씬 정확한 예측을 할 수 있다.
• 히스토그램 정보가 없으면 옵티마이저는 데이터가 균등하게 분포돼 있을 것으로 예측하고, 히스토그램 정보가 없으면 특정 범위의 데이터가 많고 적음을 식별할 수 있다.
  • 이는 쿼리의 성능에 상당한 영향을 미칠 수 있다.
  • 각 칼럼에 대해 히스토그램 정보가 있으면 어느 테이블을 먼저 읽어야 조인의 횟수를 줄일 수 있을지 옵티마이저가 더 정확히 판단할 수 있다.

히스토그램과 인덱스

• 히스토그램과 인덱스는 완전히 다른 객체이지만, MySQL 서버에서 인덱스는 부족한 통계 정보를 수집하기 위해 사용된다는 측면에서 어느 정도 공통점을 가진다고 볼 수 있다.

인덱스 다이브

• MySQL 서버에서는 쿼리의 실행 계획을 수립할 때 사용 가능한 인덱스들로부터 조건절에 일치하는 레코드 건수를 대략 파악하고 최종적으로 가장 나은 실행 계획을 선택한다.
  • 이때, 옵티마이저는 조건절에 일치하는 레코드 건수를 예측하기 위해 실제 인덱스의 B-Tree를 샘플링해서 살펴본다.
  • 이 작업을 “인덱스 다이브 (Index Dive)”라고 표현한다.
• MySQL 8.0 서버에서는 인덱스된 칼럼을 검색 조건으로 사용하는 경우 그 칼럼의 히스토그램은 사용하지 않고 실제 인덱스 다이브를 통해 직접 수집한 정보를 활용한다.
  • 이는 실제 검색 조건의 대상 값에 대한 샘플링을 실행하는 것이므로 항상 히스토그램보다 정확한 결과를 기대할 수 있기 때문이다.
• MySQL 8.0 버전에서 히스토그램은 주로 인덱스되지 않은 칼럼에 대한 데이터 분포도를 참조하는 용도로 사용된다.
• 인덱스 다이브 작업은 어느 정도의 비용이 필요하다.
  • 때로 IN 절에 값이 많이 명시된 경우, 실행 계획 수립만으로도 상당한 인덱스 다이브를 실행하고 비용도 그만큼 커진다.

Reference

참고 서적

📔 Real MySQL 8.0