MySQL 서버에서 보여주는 실행 계획을 읽고 이해하려면 MySQL 서버가 데이터를 처리하는 로직을 이해할 필요가 있다.

MySQL 서버의 실행 계획에서 통계 정보는 실행 계획에 가장 큰 영향을 미친다.

MySQL 5.7 버전까지는 테이블과 인덱스에 대한 개괄적인 정보를 가지고 실행 계획을 수립했으나, 이는 테이블 컬럼의 값들이 실제 어떻게 분포돼 있는지에 대한 정보가 없기 때문에 실행 계획의 정확도가 떨어지는 경우가 많았다.

이에 따라 MySQL 8.0 버전부터는 인덱스되지 않은 컬럼들에 대해서도 데이터 분포도를 수집하여 저장하는 히스토그램(Histogram) 정보가 도입되었다.

테이블 및 인덱스 통계 정보

히스토그램이 도입됐다고 해서 기존 테이블이나 인덱스의 통계 정보가 필요하지 않은 것은 아니다.

비용 기반 최적화에서 가장 중요한 것은 통계 정보로 통계 정보가 정확하지 않다면 엉뚱한 방향으로 쿼리를 실행할 수 있다.

MySQL 또한 다른 DBMS와 같이 비용 기반 최적화를 사용하지만, 다른 DBMS보다 통계 정보의 정확도가 높지 않고 통계 정보의 휘발성이 강했다. 이에 따라 쿼리의 실행 계획을 수립할 때 실제 테이블의 데이터를 일부 분석해서 통계 정보를 보완해서 사용했다.

MySQL 서버의 통계 정보

MySQL 5.5 버전 까지는 각 테이블의 통계 정보가 메모리에만 관리되고, SHOW IDNEX 명령으로만 테이블의 인덱스 컬럼의 분포도를 볼 수 있어 서버가 재시작되면 수집된 통계 정보가 모두 휘발되었다.

이에 따라 MySQL 5.6 버전부터는 각 테이블의 통계 정보를 mysql 데이터베이스의 innodb_index_stats 테이블과 innodb_table_stats 테이블을 통해 InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로(Persisdtent) 관리할 수 있게 개선되었다.

1

SHOW TABLES LIKE '%_stats';  

MySQL 5.6에서 테이블을 생성할 때 STATS_PERSISTENT 옵션을 설정할 수 있는데, 이 설정값에 따라 테이블 단위로 영구적인 통계 정보를 보관할지를 결정할 수 있다.

1
2
3

CREATE TABLE tab_test (fd1 INT, fd2 VARCHAR(20) PRIMARY KEY(fd1))
ENIGNE=InnoDB
STATS_PERSISTENT={ DEFAULT | 0 | 1 }

• 0: 테이블 통계 정보를 MySQL 5.5 이전 방식대로 관리
• 1: 통계 정보를 mysql 데이터베이스의 innodb_index_stats, innodb_table_stats 테이블에 저장함
• DEFAULT: innodb_stats_persistent 시스템 변수 값으로 결정. 기본적으로는 1

통계 정보의 각 컬럼은 다음과 같은 값을 저장하고 있다.

sum_of_other_index_sizes 컬럼은 테이블의 STATS_AUTO_RECALC 옵션에 따라 0으로 보일 수도 있는데, 그 경우 다음과 같이 테이블에 대해 ANALYZE TABLE 명령을 실행하면 통곗값이 저장된다.

MySQL 5.5 버전 까지는 테이블의 통계 정보가 메모리에만 저장되었기 때문에 서버가 재시작되면 통계 정보가 초기화되어 다시 수집돼야 하고, 추가적으로 사용자나 관리자가 알지 못하는 순간에 다음과 같은 이벤트가 발생하면 자동으로 통계 정보가 갱신되었다.

• 테이블이 새로 오픈되는 경우
• 테이블의 레코드가 대량으로 변경되는 경우
  • 테이블의 전체 레코드 중에서 1/16 정도의 UPDATE, INSERT, DELETE가 실행되는 경우
• ANALYZE TABLE 명령이 실행되는 경우
• SHOW TABLE STATUS, SHOW INDEX FROM 명령이 실행되는 경우
• InnoDB 모니터가 활성화되는 경우
• innodb_stats_on_metadata 시스템 설정이 ON인 상태에서 SHOW TABLE STATUS 명령이 실행되는 경우

테이블의 통계 정보가 자주 갱신되면 응용 프로그램의 쿼리의 실행 계획을 정확히 처리하지 못할 수 있었으나 영구적인 통계 정보가 도입되면서 의도하지 않은 통계 정보 변경을 막을 수 있게 되었다. 또한 innodb_stats_auto_recalc 시스템 변수의 값을 OFF로 설정하여 통계 정보가 자동으로 갱신되는 것을 막을 수 있다.

MySQL 5.5 버전에서는 테이블의 통계 정보를 수집할 때 몇 개의 InnoDB 테이블 블록을 샘플링할지 결정하는 옵션으로 innodb_stats_sample_pages 시스템 변수가 제공되었는데, 5.6 버전부터 없어지고 innodb_stats_transient_sample_pages, innodb_stats_persistent_sample_pages 시스템 변수로 분리되었다.

• innodb_stats_transient_sample_pages
  • 기본값: 8
  • 자동으로 통계 정보 수집이 실행될 때 설정된 페이지 개수만 임의로 샘플링해서 분석하고 그 결과를 통계 정보로 활용
• innodb_stats_persistent_sample_pages
  • 기본값: 20
  • ANALYZE TABLE 명령이 실행되면 임의로 설정값 개수 만큼의 페이지만 샘플링해서 분석하고 그 결과를 영구적인 통계 정보 테이블에 저장하고 활용

영구적인 통계 정보를 사용한다면 MySQL 서버의 점검이나 사용량이 많지 않은 시간을 이용해 더 정확한 통계 정보를 수집할 수도 있다. 더 정확한 통계 정보를 수집하고자 한다면 innodb_stats_persistent_sample_pages 시스템 변수에 높을 값을 설정하면 되지만 이 값을 너무 높이면 통계 정보 수집 시간이 길어지므로 주의가 필요하다.

히스토그램

MySQL 5.7 버전까지 활용되던 통계 정보는 인덱스된 컬럼의 유니크한 값의 개수 정도로 단순하여 옵티마이저가 최적의 실행 계획을 수립하기에는 많이 부족하여 옵티마이저는 실행 계획을 수립할 때 실제 인덱스의 일부 페이지를 랜덤으로 가져와 참조여 부족한 부분을 메웠다.

8.0 버전으로 업그레이드 되면서 MySQL 서버도 컬름의 데이터 분포도를 참조할 수 있는 히스토그램 정보를 활용할 수 있게 되었다.

히스토그램 정보 수집

MySQL 8.0 버전에서 히스토그램 정보는 컬럼 단위로 관리되는데, 이는 자동으로 수집되지 않고 ANALYZE TABLE ... UPDATE HISTOGRAM 명령을 통해 수동으로 수집 및 관리된다.

수집된 히스토그램 정보는 시스템 딕셔너리에 함께 저장되고, MySQL 서버가 시작될 때 딕셔너리의 히스토그램 정보를 infomation_schema 데이터베이스의 column_statistics 테이블로 로드한다. 따라서 실제 히스토그램 정보를 조회하려면 column_statistics 테이블을 SELECT 해서 참조할 수 있다.

MySQL 8.0 버전에서는 2종류의 히스토그램 타입이 지원된다.

• Singleton(싱글톤 히스토그램)
  • 컬럼값 개별로 레코드 건수를 관리하는 히스토그램
  • Value-Based 히스토그램 또는 도수 분포라고도 불린다.
  • 컬럼 가진 값 별로 버킷이 할당된다.
  • 각 버킷이 컬럼의 값과 발생 빈도의 비율 2개 값을 가진다.
• Equi-Height(높이 균형 히스토그램)
  • 컬럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램
  • Height-Balances 히스토그램이라고도 불린다.
  • 개수가 균등한 컬럼값의 범위별로 하나의 버킷이 할당된다
  • 각 버킷이 범위 시작값 및 마지막 값, 발생 빈도율, 각 버킷에 포함된 유니크한 값의 개수등 4개 값을 가진다.

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

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

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

히스토그램 삭제

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

1
2

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

히스토그램을 삭제하지 않고 MySQL 옵티마이저가 히스토그램을 사용하지 않게 하려면 optimizer_switch 시스템 변수 값을 변경하면 된다. 시스템 변수의 값을 글로벌로 변경하면 MySQL 서버의 모든 쿼리가 히스토그램을 사용하지 않으며, condition_fanout_filter 옵션에 의해 영향받는 다른 최적화 기능들의 사용되지 않을 수 있으므로 주의가 필요하다.

1

SET GLOBAL optimizer_switch='condition_fanout_filter=off';

히스토그램 정보가 없으면 옵티마이저는 데이터가 균등하게 분포돼 있을 것으로 예측하고, 히스토그램이 있으면 특정 범위의 데이터가 많고 적음을 식별하므로 실행 계획 수립이 달라지므로 쿼리 성능에 상당항 영향을 미칠 수 있다.

히스토그램과 인덱스

히스토그램과 인덱스는 완전히 다른 객체이므로 서로 비교할 대상은 아니지만, MySQL 서버에서 인덱스는 부족한 통계 정보를 수집하기 위해 사용된다는 측면에서 어느 정도 공통점을 가진다.

MySQL 서버에서는 쿼리의 실행 계획을 수립할 때 사용 가능한 인덱스들로부터 조건절에 일치하는 레코드 건수를 대략 파악하고 최종적으로 가장 나은 실행 계획을 선택한다.

인덱스 다이브(Index Dive)
조건절에 일치하는 레코드 건수를 예측하기 위해 실제 인덱스의 B-Tree를 샘플링해서 확인하는 작업

MySQL 8.0 서버에서는 인덱스된 컬럼을 검색 조건으로 사용하는 경우 그 컬럼의 히스토그램을 사용하지 않고 실제 인덱스 다이브를 통해 직접 수집한 정보를 활용한다. 그래서 히스토그램은 주로 인덱스되지 않은 컬럼에 대한 데이터 분포도를 참조하는 용도로 사용된다.

하지만 인덱스 다이브 작업은 어느 정도의 비용이 필요하며, 때로는 (IN 절에 값이 많이 명시된 경우) 실행 계획 수립만으로도 상당한 인덱스 다이브를 실행하고 비용도 커진다.

조만간 실제 인덱스 다이브를 실행하기 보다 히스토그램을 활용하는 최적화 기능도 추가될 것으로 예상된다.

코스트 모델(Cost Model)

MySQL 서버가 쿼리를 처리하려면 다음과 같은 다양한 작업이 필요하다.

• 디스크로부터 데이터 페이지 읽기
• 메모리(InnoDB 버퍼풀)로부터 페이지 읽기
• 인덱스 키 비교
• 레코드 평가
• 메모리 임시 테이블 작업
• 디스크 임시 테이블 작업

MySQL 서버는 사용자의 쿼리에 대해 이러한 다양한 작업이 얼마나 필요한지 예측하고 전체 작업비용을 계산한 결과를 바탕으로 최적 실행 계획을 찾는데, 이렇게 전체 쿼리의 비용을 계산하는데 필요한 단위 작업들의 비용을 코스트 모델이라고 한다.

MySQL 5.7 이전 버전까지는 이런 코스트 모델을 소스 코드에 상수화 해서 새용했으나, 이러한 작업 비용은 서버가 사용하는 하드웨어에 따라 달라질 수 있기 때문에 최적 실행 계획 수립에 있어 항상 긍정적이지 않았다.

이러한 부분을 보완하기 위해 MySQL 5.7 버전부터 소스 코드에 상수화 되어있던 각 단위 작업 비용을 DBMS 관리자가 조정할 수 있게 개선되었지만, 인덱스되지 않은 컬럼의 데이터 분포(히스토그램)나 메모리에 상주중인 페이지의 비율 등 비용 계산과 연관된 부분의 정보가 부족했다.

MySQL 8.0 버전으로 업그레이드되면서 컬럼의 데이터 분포를 위한 히스토그램과 각 인덱스별 메모리에 적재된 페이지의 비율이 관리되고 옵티마이저의 실행 계획 수립에 사용되기 시작했다.

MySQL 8.0 서버의 코스트모델은 다음 2개 테이블에 저장되어 있는 설정값을 사용하는데, 두 테이블 모두 mysql DB에 존재한다.

• server_cost
  • 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
• engine_cost
  • 레코드를 가진 데이터 페이지를 가져오는 데 필요한 비용 관리

각 테이블은 공통으로 5개 컬럼을 가진다.

• cost_name: 코스트 모델의 각 단위 작업
• default_value: 각 단위 작업의 비용
  • 기본값으로, MySQL 서버 소스코드에 설정된 값
• cost_value: DBMS 관리자가 설정한 값
  • NULL이면 default_value 컬럼값 사용
• last_update: 작업 비용이 변경된 시점
• comment: 비용에 대한 추가 설명

engine_cost 테이블은 2개의 컬럼을 더 가진다.

• engine_name: 비용이 적용된 스토리지 엔진
  • 스토리지 엔진별로 각 단위 작업 비용을 설정할 수 있다.
  • 기본값은 default이며 특정 스토리지 엔진 비용이 설정되지 않았다면 해당 스토리지 엔진의 비용으로 값을 적용한다.
• device_type: 디스크 타입
  • MySQL 8.0에서는 아직 이 컬럼의 값을 활용하지 않는다.

MySQL 8.0 버전의 코스트 모델에서 지원하는 단위 작업은 다음과 같이 8개이다.

각 단위 작업의 비용을 이용해 MySQL 서버의 실행 계획에 표시되는 비용을 직접 계산해보고 싶을 수 있지만, 역으로 이러한 계산을 직접 해보기는 쉽지 않다.

• B-Tree 깊이와 인덱스 키 검색을 위해 읽어야하는 페이지의 개수
• 디스크와 메모리(버퍼풀)에서 일어야하는 데이터 페이지의 개수
• 레코드 정렬 작업에서 사용되는 알고르즘별 키 값 비교 작업 횟수 등

이런 정보는 모두 사용자에게 표시되지 않기 때문에 직접 계산하는 것은 상당히 어렵다.

코스트 모델에서 중요한 것은 각 단위 작업에 설정되는 비용 값이 커지면 어던 실행 계획들이 고비용으로 바뀌고 어떤 실행 계획들이 저비용으로 바뀌는지 파악하는 것이다. 대포적으로 각 단위 작업의 비용이 병견되면 예상할 수 있는 결과들은 다음과 같다.

• key_compare_cost 비용을 높이면 MySQL 서버 옵티마이저가 가능하면 정렬을 수행하지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.
• row_evaluate_cost 비용을 높이면 풀 스캔을 실행하는 쿼리들의 비용이 높아지고, MySQL 서버 옵티마이저는 가능하면 인덱스 레인지 스캔을 사용하는 실행 계획을 선택할 가능성이 높아진다.
• disk_temptable_create_cost와 dist_temptable_row_cost 비용을 높이면 MySQL 옵티마이저는 디스크에 임시 테이블을 만들지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.
• memory_temptable_create_cost와 memory_temptable_row_cost 비용을 높이면 MySQL서버 옵티마이저는 메모리 임시 테이블을 만들지 않는 방향으로 실행 계획을 선택할 가능성이 높아진다.
• io_block_read_cost 비용이 높아지면 MySQL 서버 옵티마이저는 가능하면 InnoDB 버퍼풀에 데이터 페이지가 많이 적재되어있는 인덱스를 사용하는 실행 계획을 선택할 가능성이 높아진다.
• memory_block_read_cost 비용이 높아지만 InnoDB 버퍼풀에 적재된 데이터 페이지가 상대적으로 적다고 하더라도 그 인덱스를 사용할 가능성이 높아진다.

코스트 모델은 MySQL 서버가 사용하는 하드웨어와 MySQL 서버 내부적인 처리 방식에 대한 깊이 있는 지식을 필요로 한다. MySQL 서버에 적용된 기본 값으로도 20년이 넘는 시간동안 수많은 응용 프로그램에서 잘 사용되어 왔으므로, 이런 부분에 대해 전문적인 지식을 가지고 있지 않다면 서버스에 사용되는 MySQL 서버의 engine_cost 테이블과 server_cost 테이블의 기본값을 함부로 변경하지 않는 게 좋다.