Last Updated on 4월 13, 2022 by Jade(정현호)
안녕하세요
이번 포스팅에서는 MySQL 8.0 버전에서 추가 및 개선된 히스토그램(Histogram) 정보에 대해서 확인 해 보도록 하겠습니다.
해당 포스팅은 Real MySQL 8.0 책의 내용 정리와 MySQL Document 를 참조한 글 입니다.
히스토그램
MySQL 5.7 버전 까지의 통계 정보는 단순히 인덱스된 컬럼의 유니크한 값의 개수 정도 정보만 가지고 있었으며, 이는 옵티마이저가 최적의 실행 계획을 수립 하기에는 많이 부족했습니다.
그래서 옵티마이저는 이러한 부족함을 메우기 위해서 실행 계획을 수립할 때 실제 인덱스의 일부 페이지를 랜덤으로 가져와 참조하는 방식을 사용하였습니다.
MySQL 8.0 버전으로 업그레이드 되면서 MySQL 서버도 드디어 컬럼의 데이터 분포도를 참조할 수 있는 히스토그램(Histogram) 기능이 추가 되어 정보를 활용 할 수 있게 되었습니다.
히스토그램 정보 수집과 삭제
MySQL 8.0 버전에서 히스토그램 정보는 컬럼 단위로 수집되고 관리 되는데, 이는 자동으로 수집 되지는 않고 아래와 같은 명령어를 통해서 수집되게 됩니다.
mysql> ANALYZE TABLE ... UPDATE HISTOGRAM
수집된 히스토그램 정보는 시스템 딕셔너리에 함께 저장되고, MySQL 서버가 시작될 때 딕셔너리의 히스토그램 정보를 information_schema 데이터베이스의 column_statistics 테이블에 로드 하게 됩니다.
그래서 실제 히스토그램 정보를 조회 하려면 column_statistics 딕셔너리 테이블을 조회하여 정보를 확인 할 수 있습니다.
샘플 테이블(테스트 데이터)
히스토그램의 테스트를 위해서 사용한 예제 테이블은 MySQL Document의 employees-installation 에서 가이드 되고 있는 github 에서 테이블 DDL 구문과 데이터를 다운로드 받을 수 있습니다.
서버에서 github 에 있는 데이터 dump 파일을 직접 다운로드 받을 때는 아래와 같이 wget 으로 직접 받을 수 있습니다.
wget https://github.com/datacharmer/test_db/raw/master/load_employees.dump
히스토그램 수집 및 조회
-- 테이블 생성
mysql> CREATE TABLE employees (
emp_no INT NOT NULL,
birth_date DATE NOT NULL,
first_name VARCHAR(14) NOT NULL,
last_name VARCHAR(16) NOT NULL,
gender ENUM ('M','F') NOT NULL,
hire_date DATE NOT NULL,
PRIMARY KEY (emp_no),
KEY idx_first_name (first_name))
;
-- 데이터 로드
mysql> source load_employees.dump;
-- 히스토그램 수집
mysql> ANALYZE TABLE employees
UPDATE HISTOGRAM ON gender,hire_date;
+-------------------+-----------+----------+------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------+-----------+----------+------------------------------------------------------+
| test_db.employees | histogram | status | Histogram statistics created for column 'gender'. |
| test_db.employees | histogram | status | Histogram statistics created for column 'hire_date'. |
+-------------------+-----------+----------+------------------------------------------------------+
-- COLUMN_STATISTICS 조회
mysql> select *
from information_schema.COLUMN_STATISTICS
where table_name='employees'\G
*************************** 1. row ***************************
SCHEMA_NAME: test_db
TABLE_NAME: employees
COLUMN_NAME: gender
HISTOGRAM: {"buckets": [[1, 0.5998776829658968]
, [2, 1.0]], "data-type": "enum", "null-values": 0.0, "collation-id": 45,
"last-updated": "2021-11-21 12:06:45.393325",
"sampling-rate": 0.34783905681630983, "histogram-type": "singleton",
"number-of-buckets-specified": 100}
*************************** 2. row ***************************
SCHEMA_NAME: test_db
TABLE_NAME: employees
COLUMN_NAME: hire_date
HISTOGRAM: {"buckets": [["1985-01-01", "1985-02-27", 0.010096009164069856, 29]
, ["1985-02-28", "1985-03-25", 0.01992020269679937, 26]
, ["1985-03-26", "1985-04-23", 0.02990942714855695, 29]
, ["1985-04-24", "1985-05-20", 0.03984040539359874, 27]
, ["1985-05-21", "1985-06-16", 0.04983933754647562, 27]
, ["1985-06-17", "1985-07-15", 0.06011008533069284, 29]
, ["1985-07-16", "1985-08-10", 0.06989544805894517, 26]
, ["1985-08-11", "1985-09-07", 0.07998174952189573, 28]
, ["1985-09-08", "1985-10-05", 0.08999038937589189, 28]
, ["1985-10-06", "1985-11-03", 0.09993107532205299, 29]
, ["1985-11-04", "1985-12-02", 0.10988146896933337, 29]
, ["1985-12-03", "1986-01-01", 0.12004543204123831, 30]
, ["1986-01-02", "1986-01-30", 0.13013173350418888, 29]
, ["1986-01-31", "1986-03-01", 0.14002388094475346, 30]
, ["1986-03-02", "1986-04-01", 0.15010047470658475, 31]
, ["1986-04-02", "1986-05-01", 0.16002174525050722, 30]
, ["1986-05-02", "1986-05-31", 0.16993330809331042, 30]
, ["1986-06-01", "1986-07-01", 0.18005844036073818, 31]
, ["1986-07-02", "1986-08-01", 0.19014474182368873, 31]
, ["1986-08-02", "1986-09-01", 0.19995922765529894, 31]
, ["1986-09-02", "1986-10-02", 0.2100843599227267, 31]
, ["1986-10-03", "1986-11-02", 0.219869722650979, 31]
, ["1986-11-03", "1986-12-03", 0.23006280882624186, 31]
, ["1986-12-04", "1987-01-02", 0.24001320247352226, 30]
, ["1987-01-03", "1987-02-04", 0.2500315500286377, 33]
, ["1987-02-05", "1987-03-07", 0.2600207744803953, 31]
, ["1987-03-08", "1987-04-07", 0.2698546757142441, 31]
, ["1987-04-08", "1987-05-09", 0.2799215617749561, 32]
, ["1987-05-10", "1987-06-11", 0.2899690324334294, 33]
, ["1987-06-12", "1987-07-14", 0.3000067953907835, 33]
, ["1987-07-15", "1987-08-17", 0.3100154352447797, 34]
, ["1987-08-18", "1987-09-18", 0.3198784595819864, 32]
, ["1987-09-19", "1987-10-22", 0.32986768403374395, 34]
, ["1987-10-23", "1987-11-23", 0.3400898933123647, 32]
, ["1987-11-24", "1987-12-26", 0.35013736397083806, 33]
, ["1987-12-27", "1988-01-28", 0.36002951141140266, 33]
, ["1988-01-29", "1988-03-01", 0.36995078195532516, 33]
, ["1988-03-02", "1988-04-06", 0.3800856219238722, 36]
, ["1988-04-07", "1988-05-10", 0.3900748463756298, 34]
, ["1988-05-11", "1988-06-14", 0.40013202473522247, 35]
, ["1988-06-15", "1988-07-20", 0.4099659259690713, 36]
, ["1988-07-21", "1988-08-23", 0.4198483657085166, 34]
, ["1988-08-24", "1988-09-29", 0.4300026210793022, 37]
, ["1988-09-30", "1988-11-04", 0.4400792148411335, 36]
, ["1988-11-05", "1988-12-09", 0.4498645775693858, 35]
, ["1988-12-10", "1989-01-14", 0.460135325353603, 36]
, ["1989-01-15", "1989-02-20", 0.470008057391929, 37]
, ["1989-02-21", "1989-03-29", 0.4799584510392094, 37]
, ["1989-03-30", "1989-05-08", 0.49006416790439855, 40]
, ["1989-05-09", "1989-06-15", 0.5000339769539175, 38]
, ["1989-06-16", "1989-07-22", 0.509887293590005, 37]
, ["1989-07-23", "1989-08-29", 0.5199638873518362, 38]
, ["1989-08-30", "1989-10-09", 0.5300404811136675, 41]
, ["1989-10-10", "1989-11-16", 0.5399326285542321, 38]
, ["1989-11-17", "1989-12-27", 0.5500771762238984, 41]
, ["1989-12-28", "1990-02-05", 0.5600761083767754, 40]
, ["1990-02-06", "1990-03-19", 0.5700167943229364, 42]
, ["1990-03-20", "1990-04-30", 0.5800933880847676, 42]
, ["1990-05-01", "1990-06-13", 0.5899661201230937, 44]
, ["1990-06-14", "1990-07-26", 0.6000912523905214, 43]
, ["1990-07-27", "1990-09-07", 0.6100610614400404, 43]
, ["1990-09-08", "1990-10-21", 0.6200017473862015, 44]
, ["1990-10-22", "1990-12-03", 0.629923017930124, 43]
, ["1990-12-04", "1991-01-16", 0.6399316577841201, 44]
, ["1991-01-17", "1991-03-03", 0.6500859131549058, 46]
, ["1991-03-04", "1991-04-18", 0.6600848453077827, 46]
, ["1991-04-19", "1991-06-05", 0.670035238955063, 48]
, ["1991-06-06", "1991-07-23", 0.6799759249012242, 48]
, ["1991-07-24", "1991-09-07", 0.6899360262496238, 46]
, ["1991-09-08", "1991-10-30", 0.7000029123103357, 53]
, ["1991-10-31", "1991-12-19", 0.7100503829688092, 50]
, ["1991-12-20", "1992-02-08", 0.7200298997194474, 51]
, ["1992-02-09", "1992-03-30", 0.7299608779644893, 51]
, ["1992-03-31", "1992-05-22", 0.7399209793128889, 53]
, ["1992-05-23", "1992-07-16", 0.7499004960635272, 55]
, ["1992-07-17", "1992-09-10", 0.7599382590208813, 56]
, ["1992-09-11", "1992-11-03", 0.7699274834726388, 54]
, ["1992-11-04", "1993-01-02", 0.7799264156255157, 60]
, ["1993-01-03", "1993-02-26", 0.7900612555940628, 55]
, ["1993-02-27", "1993-04-26", 0.8000116492413432, 59]
, ["1993-04-27", "1993-06-29", 0.81001058139422, 64]
, ["1993-06-30", "1993-09-02", 0.819980390443739, 65]
, ["1993-09-03", "1993-11-09", 0.8299793225966159, 68]
, ["1993-11-10", "1994-01-15", 0.8399491316461348, 67]
, ["1994-01-16", "1994-03-30", 0.8498995252934153, 74]
, ["1994-03-31", "1994-06-11", 0.8599955344574851, 73]
, ["1994-06-12", "1994-08-28", 0.8700041743114812, 78]
, ["1994-08-29", "1994-11-15", 0.8799351525565231, 79]
, ["1994-11-16", "1995-02-05", 0.8900117463183543, 82]
, ["1995-02-06", "1995-05-04", 0.9000495092757084, 88]
, ["1995-05-05", "1995-07-29", 0.9099610721185116, 86]
, ["1995-07-30", "1995-11-03", 0.9200279581792236, 97]
, ["1995-11-04", "1996-02-08", 0.9300365980332198, 97]
, ["1996-02-09", "1996-05-31", 0.9400064070827387, 113]
, ["1996-06-01", "1996-09-21", 0.9500053392356156, 113]
, ["1996-09-22", "1997-01-27", 0.9600042713884925, 128]
, ["1997-01-28", "1997-06-24", 0.9700129112424887, 148]
, ["1997-06-25", "1997-12-13", 0.9799924279931269, 171]
, ["1997-12-14", "1998-07-27", 0.9899816524448846, 224]
, ["1998-07-28", "2000-01-28", 1.0, 450]],
"data-type": "date", "null-values": 0.0,
"collation-id": 8, "last-updated": "2021-11-21 12:06:45.393773",
"sampling-rate": 0.34783905681630983, "histogram-type": "equi-height",
"number-of-buckets-specified": 100}
MySQL 8.0 버전 에서는 아래 2종류의 히스토그램 타입이 지원이 됩니다.
• Singleton(싱글톤 히스토그램)
컬럼값 개별로 레코드 건수를 관리하는 히스토그램으로, Value-Based 히스토그램 또는 도수 분표 라고 불립니다.
• Equi-Height(높이 균형 히스토그램)
컬럼값의 범위를 균등한 개수로 구분하여 관리하는 히스토그램으로, Height-Balanced 히스토그램이라고 불립니다.
히스토그램은 버킷(Bucket) 단위로 구분되며, 레코드 건수나 컬럼값의 범위가 관리되는데, 싱글톤 히스토그램은 컬럼이 가지는 값별로 버킷이 할당되며, 높이 균형 히스토그램은 개수가 균등한 컬럼 값의 범위별로 하나의 버킷이 할당 됩니다.
싱글톤 히스토그램은 각 버킷이 컬럼의 값과 발생 빈도의 비율의 2개 값을 가집니다.
높이 균등 히스토그램은 각 버킷의 범위 시작 값과 마지막 값, 그리고 발생 빈도율과 각 버킷에 포함된 유니크한 값의 개수등 4개의 값을 가지게 됩니다.
위에서 COLUMN_STATISTICS 를 조회한 내용을 다시 보면 gender 컬럼은 histogram-type 이 "singleton" 으로 되어 있는 것을 확인할 수 있습니다.
싱글톤은 gender 컬럼에서 가질수 있는 2개의 값('M','F')에 대해서 누적된 레코드 건수의 비율 정보를 담고 있습니다.
이처럼 싱글톤 히스토그램은 주로 코드값과 같은 유니크한 값의 개수가 상대적으로 적은(히스토그램의 버킷 수 보다 적은) 경우에 사용 됩니다.
gender 가진 분포는 위에서 확인 할 수 있듯이 M 레코드의 비율은 0.5998 이며, F 레코드의 비율은 1로 확인 됩니다.
"buckets": [[1, 0.5998776829658968], [2, 1.0]]
히스토그램의 모든 레코드 건수 비율은 누적으로 표시되며, 그래서 gender 컬럼의 값 F의 비율은 1-0.5998 이 되게 됩니다.
그 다음 높이 균형 히스토그램은 컬럼의 각 범위에 대해 레코드 건수 비뉼이 누적으로 표시가 됩니다.
범위 별로 비율이 같은 수준의 hire_date 컬럼의 범위가 선택되게 됩니다.
INFORMATION_SCHEMA.COLUMN_STATISTICS 테이블의 HISTOGRAM 컬럼에서 포함된 정보는 위의 내용 이회 다음과 같은 의미를 가지고 있습니다.
Sampling-rate
히스토그램 정보를 수집하기 위해 스캔한 페이지의 비율을 저장 합니다. 샘플링 비율이 0.35 라면 전체 데이터 페이지의 35%를 스캔해서 이 정보를 수집 하였다는 의미가 됩니다.
위에서 조회한 결과에서는 샘플링 비율이 0.34 였습니다
(sampling-rate": 0.34783905681630983)
물론 샘플링 비율이 높아질수록 더 정확한 히스토그램이 되겠지만, 테이블을 전부 스캔을 하는 것은 부하가 높으며 시스템 자원을 많이 소모하게 됩니다.
그래서 MySQL 서버는 histogram_generation_max_max_size 시스템 변수에 설정된 메모리 크기에 맞춰서 적절한 샘플링을 하게 됩니다
해당 시스템 변수의 기본값은 19MB(20000000 byte) 입니다.
histogram-type
히스토그램의 종류를 저장 합니다.
number-of-buckets-specified
히스토그램을 생성 할 때 설정 했던 버킷의 개수를 저장 합니다.
히스토그램을 생성 할 때 별도의 버킷의 개수를 지정하지 않았다면 기본으로 100개의 버킷이 사용 되게 됩니다.
버킷은 최대 1024개를 설정할 수 있으며, 보통의 경우 100개의 버킷 이면 충분한 것으로 알려져 있습니다.
MySQL 8.0.19 미만 버전의 히스토그램
MySQL 8.0.19 미만의 버전까지는 히스토그램 생성 시 샘플링 비율(Sampling-rate) 과 histogram_generation_max_mem_size 시스템 변수의 크기와 관계 없이 MySQL 서버는 풀 스캔을 통해 데이터 페이지를 샘플링 해서 히스토그램을 생성하였습니다.
MySQL 8.0.19 버전 부터는 InnoDB 스토리지 엔진 자체적으로 샘플링 알고리즘을 구현 하였으며, 더이상 히스토그램 수집 시 풀 테이블 스캔이 필요치 않게 되었습니다.
만약 사용중인 버전이 8.0.19 미만의 버전이라면 히스토그램 수집 시 주의가 필요 할 것으로 생각 됩니다.
히스토그램 삭제 및 미사용
생성된 히스토그램은 아래와 같이 삭제를 할 수 있습니다 다만 히스토그램 삭제 시 쿼리의 플랜 변경에 의해서 처리 성능에 영향을 받을 수 있는 점은 고려를 해야 합니다.(물론 실행계획이 변경이 안될수도 있기도 합니다)
히스토그램 삭제
mysql> ANALYZE TABLE employees DROP HISTOGRAM ON gener, hire_date;
수집된 히스토그램의 삭제 대신에 MySQL 옵티마이저가 히스토그램을 사용하지 못하게 하는 방법도 있으며 아래와 같이 optimizer_switch 값을 변경을 하면 됩니다.
mysql> SET GLOBAL optimizer_switch='condition_fanout_filter=off';
위와 같이 optimizer_switch 를 변경하면 MySQL 전역적으로 영향을 받기 때문에 모든 쿼리가 히스토그램을 사용하지 못하게 되며, condition_fanout_filter=OFF 따른 다른 영향을(플랜의 변경) 받을 수도 있기 때문에 optimizer_switch 값을 변경 하는것은 많은 고민이 필요해 보입니다.
현재 접속된 세션 또는 특정 쿼리에서만 히스토그램을 사용하지 않고자 한다면 아래와 같이 사용할 수 있습니다.
-- 접속한 커넥션(세션) 에서만 히스토그램을 사용하지 않음 mysql> SET SESSION optimizer_switch='codition_fanout_filter=off'; -- 수행하는 쿼리에서만 히스토그램을 사용하지 않음 mysql> select /*+ SET_VAR(optimizer_switch='condition_fanout_filter=off') */ * FROM .....
참고로 MySQL 8.0 기준으로 optimizer_switch 종류는 아래와 같습니다.
mysql> SELECT @@optimizer_switch\G *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on, index_merge_sort_union=on,index_merge_intersection=on, engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on, mrr_cost_based=on,block_nested_loop=on,batched_key_access=off, materialization=on,semijoin=on,loosescan=on,firstmatch=on, duplicateweedout=on,subquery_materialization_cost_based=on, use_index_extensions=on,condition_fanout_filter=on,derived_merge=on, use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off, prefer_ordering_index=on,hypergraph_optimizer=off, derived_condition_pushdown=on
히스토그램의 사용
MySQL 서버에 히스토그램이 도입되기 전에도 테이블과 인덱스에 대한 통계정보는 존재 하였습니다.
하지만 기존 MySQL 서버가 가지고 있던 통계 정보는 테이블의 전체 레코드 건수와 인덱스 된 컬럼이 가지고 있는 유니크한 값의 개수 정도 였습니다.
예를 들어, 테이블의 전체 건수가 1000건 이고 어떠한 컬럼의 유니크한 값의 개수가 100개 라면 MySQL 서버는 이 컬럼에 대해서 동등 비교 검색(WHERE COL=값) 하게 되면 대략 10개의 레코드가 일치할 것이라고 예측을 하게 됩니다.
하지만 실제 데이터는 항상 이렇게 균등한 분포도를 가지고 있지는 않습니다 하지만 일반적인 통계정보에서는 이런 부분이 고려 되지 못하고 있습니다.
그래서 이런 부분을 보완하기 위해서 히스토그램은 특정 컬럼이 가지는 모든 값에 대한 분포도 정보는 아니지만 각 범위(버킷)별로 레코드의 건수와 유니크한 값의 개수 정보를 가지고 있기 때문에 훨씬 더 정확한 예측을 할수가 있게 됩니다.
히스토그램 유무의 차이 확인
employee 테이블의 birth_date 컬럼에 대해서 히스토그램의 유무 차이를 확인 해보겠습니다.
먼저 히스토그램이 없을때의 실행계획 입니다.
mysql> explain select * from employees where first_name='Zita' and birth_date between '1950-01-01' and '1960-01-01'; +----+-------------+-----------+------+----------------+----------------+---------+-------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------+----------------+----------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | employees | ref | idx_first_name | idx_first_name | 58 | const | 224 | 11.11 | Using where | +----+-------------+-----------+------+----------------+----------------+---------+-------+------+----------+-------------+
위의 플랜을 보면 first_name='Zita' 조건을 만족하는 건수는 224건 이며, 그 중에서 11.11%(filtered 항목) 인 24.88명 정도가 1950년 출생(birth_date)일 것으로 예측이 되고 있습니다.
히스토그램 수집 후 다시 플랜을 확인 해보겠습니다.
mysql> ANALYZE TABLE employees UPDATE HISTOGRAM ON first_name, birth_date ; +-------------------+-----------+----------+-------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------------------+-----------+----------+-------------------------------------------------------+ | test_db.employees | histogram | status | Histogram statistics created for column 'birth_date'. | | test_db.employees | histogram | status | Histogram statistics created for column 'first_name'. | +-------------------+-----------+----------+-------------------------------------------------------+ mysql> explain select * from employees where first_name='Zita' and birth_date between '1950-01-01' and '1960-01-01'; +----+-------------+-----------+------+----------------+----------------+---------+-------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------+----------------+----------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | employees | ref | idx_first_name | idx_first_name | 58 | const | 224 | 60.95 | Using where | +----+-------------+-----------+------+----------------+----------------+---------+-------+------+----------+-------------+
히스토그램을 수집 후 플랜에서는 60.95%(filtered 항목) 인 136.5명이 1950년대 출생(birth_date) 인 것을 알 수 있습니다.
통계정보만 과 히스토그램을 수집하여 이용한 경우가 차이가 크다고 확인할 수 있는 부분 입니다.
아래와 같이 실제로 카운트 해보면 first_name='Zita' 이면서 1950년대 출생인 사람의 비율은 63.83 으로 위의 플랜에서 filtered 수치(60.95%) 가 실제 카운트 결과와 거의 비슷한 것을 확인 할 수 있습니다.
mysql> select SUM(CASE WHEN birth_date between '1950-01-01' and '1960-01-01' THEN 1 ELSE 0 END) / COUNT(*)*100 as ratio from employees where first_name='Zita'; +---------+ | ratio | +---------+ | 63.8393 | +---------+
이와 같이 히스토그램 정보가 없다면 옵티마이저는 데이터가 균등하게 분포되어 있다고 예측을 하는 하게 되며, 히스토그램 정보가 있다면 특정 범위의 데이터가 많고 적음을 식별 할 수 있습니다.
참고로 샘플링 된 비중은 아래와 같이 조회 할 수 있으며 이전에 확인 해본 birth_date 와 first_name 컬럼은 샘플링이 약 16% 정도 수행된 것으로 확인 할 수 있습니다.
mysql> SELECT COLUMN_NAME,HISTOGRAM->>'$."sampling-rate"'* 100
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE TABLE_NAME = "employees";
+-------------+--------------------------------------+
| COLUMN_NAME | HISTOGRAM->>'$."sampling-rate"'* 100 |
+-------------+--------------------------------------+
| gender | 34.78390568163098 |
| hire_date | 34.78390568163098 |
| birth_date | 16.368896791355755 |
| first_name | 16.368896791355755 |
+-------------+--------------------------------------+
히스토그램과 인덱스
히스토그램과 인덱스는 완전히 다른 객체이기 때문에 사실 서로 비교할 대상은 아니지만, MySQL 서버에서 인덱스는 부족한 통계 정보를 수집하기 위해 사용된다는 측면에서는 어느정도 공통된 점이 있다고 할 수 도 있습니다.
실행계획을 수립 할때 사용 가능한 인덱스로 부터 조건절에 일치하는 레코드 건수를 예측하기 위해 옵티마이저는 실제 인덱스의 B-Tree를 샘플링해서 살펴봅니다. 이 작업을 인덱스 다이브(Index Dive) 라고 표현하고 있습니다
검색 조건에 많이 사용 되는 컬럼에 대해서는 보통 인덱스를 생성하게 되고 이런 인덱스 컬럼에 대해서도 히스토그램을 수집해야 할지를 고민스러울 수도 있습니다.
아래 쿼리를 수행하였다고 가정할 때
mysql> select * from employees where first_name='Tonny' and birth_date BETWEEN '1954-01-01' and '1955-01-01';
first_name 컬럼에 히스토그램이 수집되어 있다면 MySQL 옵티마이저는 그 히스토그램을 이용하게 될까요?
MySQL 8.0 서버에서는 인덱스된 컬럼을 검색 조건으로 사용하는 경우 그 컬럼의 히스토그램을 사용하지 않고 실제 인덱스 다이브를 통해서 직접 수집한 정보를 활용하게 됩니다.
이는 실제 검색 조건의 대상값에 대한 샘플링을 실행하는 것으로 항상 히스토그램보다는 정확한 결과를 기대 할 수 있기 때문 입니다.
그래서 MySQL 8.0 에서는 히스토그램은 주로 인덱스가 되지 않은 컬럼에 대한 데이터 분포도를 참조하는 용도로 사용 하게 됩니다.
하지만 인덱스 다이브 작업은 어느정도 비용이 소요되는 작업으로, 때로는 (IN 절에 값이 많이 명시된 경우) 실행 계획 수립 만으로도 상당한 많은 인덱스 다이브를 실행하게 되어 비용도 커지게 됩니다.
조건절에 변수를 사용시
오라클의 경우 쿼리에 대한 파싱 부하를 줄이기 위해서(하드 파싱을 줄이기 위해) SQL 작성 중 여러가지 해야하는 것 있으며,그 중 우선적으로 SQL 구문을 동일하게 수행해야 하는 보통의 규칙이 있습니다.
그래서 동일하게 SQL 을 수행하기 위해서 오라클에서는 조건절의 값을 변수화 해서 수행 하여 같은 SQL구문으로 처리를 하고 있습니다(소프트 파싱을 위해서)
다만 이럴 경우 변수에 어떠한 값이 들어 올지 알수 없기 때문에 SQL 실행계획 작성시 히스토그램을 참조를 할수 없게 됩니다.
MySQL 에서는 이러한 바인드 변수를 사실 많이 사용하지 않거나 거의 사용하지 않지만 오라클 처럼 조건절에 변수를 사용한다면 어떻게 될까요?
먼저 이전 단계에서 히스토그램을 수집하고 테스트 해본 쿼리로 다시 실행계획을 확인해 보겠습니다.
mysql> explain select * from employees where first_name='Zita' and birth_date between '1950-01-01' and '1960-01-01'; +----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ref | idx_first_name | idx_first_name | 58 | const | 224 | 60.95 | Using where | +----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
이번에는 변수를 사용하여 실행을 해보도록 하겠습니다.
-- 변수 설정 mysql> SET @A1 := '1950-01-01'; mysql> SET @B1 := '1960-01-01'; -- 데이터 조회 가능 확인 mysql> select count(*) from employees where first_name='Zita' and birth_date between @A1 and @B1; +----------+ | count(*) | +----------+ | 143 | +----------+ -- 실행 계획 확인 mysql> explain select * from employees where first_name='Zita' and birth_date between @A1 and @B1; +----+-------------+-----------+------+----------------+----------------+---------+-------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------+----------------+----------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | employees | ref | idx_first_name | idx_first_name | 58 | const | 224 | 11.11 | Using where | +----+-------------+-----------+------+----------------+----------------+---------+-------+------+----------+-------------+
정상적으로 쿼리는 수행되는 것은 확인이 되며, 실행계획을 보면 이전 단계에서 히스토그램을 수집하기 전과 동일하게 filtered 항목이 11.11% 인 것을 확인 할 수 있습니다.
즉 히스토그램이 있지만 사용을 할수 없는 것 입니다. 오라클과 마찬가지로 변수를 사용하였기 때문에 어떤 값이 들어 올지 모르는 상태이기에 옵티마이저는 히스토그램을 사용할수 가 없게 됩니다.
Reference
Reference Book
• Real MySQL 8.0
Reference Link
• mysql.com/analyze-table
• mysql.com/range-optimization
관련된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io