search

woowacourse-study / 2022-Real-MySQL

⚡️토르⚡️의 짜릿한 Real MySQL 뽀개기 🔨
9 stars 3 forks source link

히스토그램과 인덱스 #25

Open hyeonic opened 2 years ago

hyeonic commented 2 years ago

주제

히스토그램을 사용하면 각 범위 별로 레코드의 건수와 유니크한 값의 개수 정보를 가지기 때문에 옵티마이저는 훨씬 정확한 예측이 가능하다. 또한 MySQL 서버에서 쿼리의 실행 계획을 수립할 때 사용 가능한 인덱스로 부터 조건절에 일치하는 레코드 대략적으로 파악하며 가장 나은 실행 계획을 선택한다. 이러한 관점에서 히스토그램과 인덱스를 비교해보려 한다.

선정 이유

MySQL 서버에서 부족한 통계 정보 수집을 위해 사용하는 측면에서 히스토그램과 인덱스는 공통점을 가진다. 그렇다면 어떠한 부분에서 활용이 되는지 알아보기 위해 선정했다.

해당 텍스트

히스토그램

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

히스토그램은 특정 칼럼이 가지는 모든 값에 대한 분포도 정보를 가지지 않지만 각 범위별로 레코드의 건수와 유니크한 값의 개수 정보를 가지기 때문에 훨씬 정확한 예측이 가능하다.

아래는 간단한 히스토그램 정보 수집 및 조회를 진행한 것이다.

mysql> ANALYZE TABLE employees.employees UPDATE HISTGRAM ON gender, hire_date;

mysql> USE information_schema;

mysql> SELECT * FROM COLUMN_STATISTICS WHERE SCHEMA_NAME='employees' AND TABLE_NAME='employees'\G
*************************** 1. row ***************************
SCHEMA_NAME: employees
 TABLE_NAME: employees
COLUMN_NAME: gender
  HISTOGRAM: {"buckets": [
        [1, 0.598075905169612], 
        [2, 1.0]
    ], 
    "data-type": "enum", 
    "null-values": 0.0, 
    "collation-id": 45, 
    "last-updated": "2022-07-09 13:14:40.200496", 
    "sampling-rate": 0.34747935694636256, 
    "histogram-type": "singleton", "number-of-buckets-specified": 100
}
*************************** 2. row ***************************
SCHEMA_NAME: employees
 TABLE_NAME: employees
COLUMN_NAME: hire_date
  HISTOGRAM: {"buckets": [
        ["1985-02-01", "1985-03-02", 0.010010493374316306, 30], 
        ["1985-03-03", "1985-03-29", 0.020067417608439272, 27], 
        ["1985-03-30", "1985-04-26", 0.030068624810794246, 28], 
        ...
    ], 
    "data-type": "date", 
    "null-values": 0.0, 
    "collation-id": 8, 
    "last-updated": "2022-07-09 13:14:40.202811", 
    "sampling-rate": 0.34747935694636256, 
    "histogram-type": "equi-height", "number-of-buckets-specified": 100
}

히스토그램과 인덱스

MySQL 서버는 쿼리의 실행 계획 수립을 위해 사용 가능한 인덱스로 부터 조건절에 일치하는 레코드의 건수를 대략적으로 파악하고 가장 나은 실행 계획을 선택한다. 조건절에 일치하는 레코드 건수 예측을 위해 옵티마이저는 실제 인덱스의 B-Tree를 샘플링한다. 이러한 작업을 인덱스 다이브(Index Dive)라고 한다.

쿼리의 검색 조건으로 많이 사용되는 칼럼은 보통 인덱스를 생성한다. 그렇다면 이렇게 인덱스된 칼럼을 히스토그램 정보로 수집해두는 것이 좋을까? 아래는 간단한 조건을 가진 조회 예시이다.

SELECT * 
FROM employees
WHERE first_name='Tonny'
AND birth_date BETWEEN '1954-01-01' AND '1955-01-01';

옵티마이저는 우선 테이블 풀 스캔을 진행 할지 first_name 칼럼의 인덱스를 이용할지 고민한다. 만약 first_name 칼럼에 히스토그램이 수집되어 있다면 옵티마이저는 어떤 것을 선택할까?

MySQL 8.0 서버에서는 인덱스된 칼럼을 검색 조건으로 사용하는 경우 그 칼럼의 히스토그램은 사용하지 않고 실제 인덱스 다이브를 통해 수집한 정보를 활용한다. 인덱스 다이브 과정은 실제 검색 조건의 대상 값에 대한 샘플링을 진행하는 것이기 때문에 히스토그램보다 정확한 결과를 예측할 수 있다. MySQL 8.0 버전에서는 인덱스되지 않은 칼럼에 대한 데이터 분포도를 참조할 때 히스토그램을 활용한다.

인덱스 다이브 작업은 비용이 많이 필요하다고 한다. 이후 인덱스 다이브 보다 히스토그램을 활용한 최적화도 MySQL 서버에 추가되길 기대해본다.

관련 페이지

399p ~ 407p