Chapter 10.01 실행 계획

 

• 대이터베이스의 주 목적: 많은 데이터를 안전하게 보관하고 빠르게 조회한다. 
• 이러한 목적을 위해 옵티마이저가 사용자 쿼리가 최적으로 처리되도록 실행 계획을 수립할 수 있어야한다. 
• EXPLAIN 명령으로 옵티마이저가 수립한 실행 계획을 확인할 수 있다. 

 

 

10.1 통계 정보

• MySQL 5.7 버전까지는 테이블과 인덱스에 대한 개괄적인 정보를 가지고 실행 계획을 수립했다. 
• 그런데 이는 테이블 칼럼의 값이 어떻게 분포되는지 정보가 없으모로 실행 계획의 정확도가 떨어지는 경우가 많았다. 
• 그래서 8.0 버전부터는 인덱스되지 않은 칼럼들에 대해서도 데이터 분포도를 수진해서 저장하는 히스토그램(Histogram) 정보가 도입됐다. 

 

 

10.1.1 테이블 및 인덱스 통계 정보

• 비용 기반 최적화에서 가장 중요한 것은 통계 정보이다. 
• MySQL 도 다른DBMS처럼 비용 기반 최적화를 사용하지만 다른 DBMS 보다 통계 정보 정확도가 높지 않고 통계 정보의 휘발성이 강하다. 
• 그래서 MySQL 서버에서는 쿼리의 실행 계획을 수립할 때 실제 테이블의 데이터를 일부 분석해서 통계정보를 보완했다. 
• MySQL 8.0 버전 부터 통계 정보 관리가 어떻게 개선됐는지 알아본다.

 

참고) 350P (9.3.1.17) 옵티마이저가 실행 계획 수립할 때 테이블이나 인덱스의 통계 정보만 사용하는 게 아니라 순서대로 사용 가능한 방식을 선택한다. 

 

 

 

10.1.1.1 MySQL 서버의 통계 정보

• 5.6  부터는 InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로 관리 가능하도록 한다. 
• 5,5 까지는 각 테이블의 통계 정보가 메모리에서만 관리되고 SHOW INDEX 명령으로만 테이블의 인덱스 칼럼의 분포도를 볼 수 있었다.  => '휘발성' 단점
• 5.6부터는 각 테이블에의 통계 정보를 mysql  데이터베이스의 innodb_index_stats 테이블과 innodb_table_stats 테이블로 관리할 수 있도록 개선됐다. 이런 식으로 관리해서 휘발성 문제를 해결했다.
• MySQL 5.6에서 테이블을 생성할 때는 STATS_PERSISTENT 옵션으로 테이블 단위로 영구적인 통계 정보를 보관할지 말지를 결정 가능하다. 
• 테이블 설정 시 옵션 설정
  • STATS_PERSISTENT = 0 :  테이블의 통계 정보를 MySQL 5.5 버전 이전의 방식대로 관리하고 mysql 데이터베이스의  innodb_index_stats 테이블과 innodb_table_stats 테이블에 저장하지 않는다. (단기적)
  • STATS_PERSISTENT = 1 : 테이블의 통계 정보를 mysql 데이터베이스의 innodb_index_stats 테이블과 innodb_table_stats 테이블에 저장한다. (영구적)
  • STATS_PERSISTENT = DEFAULT:  테이블을 생성할 때 별도로 STATS_PERSISTENT 옵션을 설정하지 않은 것과 동일하며 테이블의 통계를 영구적으로 관리할지 말지를  innodb_stats_persistent 시스템 변수의 값으로 결정한다. 
    • innodb_stats_persistent 는 기본적으로 ON(1)로 설정돼있고 STATS_PERSISTENT 옵션 없이 테이블을 생성하면 영구적인 통계 정보를 사용하면  innodb_index_stats 테이블과 innodb_table_stats 테이블에 통계 정보를 저장한다.  
    • 테이블의 통계 정보를 변경(영구적 또는 단기적으로)하려면 ALTER TABLE 명령으로 실행 가능하다. 

 

 

 

통계 정보 자동 수집 여부 설정

• STATS_AUTO_RECALC = 1:  테이블의 통계 정보를 MySQL 5.5 이전의 방식대로 자동 수집한다. 
• STATS_AUTO_RECALC = 0:  테이블의 통계 정보를 ANALYZE TABLE  명령을 실행할 때만 수집된다.
• STATS_AUTO_RECALC = DEFAULT: 테이블을 생성할 때 별도로 STATS_AUTO_RECALS 옵션을 설정하지 않은 것과 동일하며 테이블의 통계 수집을 innodb_stats_auto_recalc 시스템 설정 변수의 값으로 결정한다. 

 

• innodb_stats_transient_sample_pages: 시스템 변수의 기본값은 8인데 자동으로 통계 정보 수집이 실행될 때, 8개 페이지만 임의로 샘플링해서 분석하고 그 결과를 통계 정보로 활용한다. 
• innodb_stats_persistent_sample_pages: 기본값은 20이고 ANALYZE TABLE 명령이 실행되면 임의로 20개 페이지만 샘플링해서 분석하고 그 결과를 영구적인 통계 정보 테이블에 저장하고 활용한다. 
  • 통계 정보의 정확성에 따라 쿼리 성능이 결정된다. 
  • 정확한 통계 정보를  수집하려면 innodb_stats_persistent_sample_pages 변수에 높은 값을 설정하면 된다. 
  • 그런데 너무 높게 잡으면 통계 정보 수집 시간이 길어지므로 주의한다. 

 

 

10.1.2 히스토그램(Histogram)

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

 

 

 

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

• 히스토그램 정보는 칼럼 단위로 관리된다. 
• 자동으로 수집되는 게 아니라 ANALYZE TABLE .. UPDATE HISTOGRAM 명령을 실행해서 수동으로 수집하고 관리된다. 
• 수집된 히스토그램 정보는 시스템 딕셔너리에 저장되고 MySQL 서버가 시작될 때 딕셔너리의 히스토그램 정보를 information_schema 데이터베이스의 column_statistics 테이블로 로드한다. 
• 그래서 실제 히스토그램 정보를 조회하려면 column_statistics 테이블을 SELECT 해서 참조한다. 

 

 

 

MySQL 8.0 버전에서 지원하는 히스토그램 타입

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

 

 

• sampling-rate: 히스토그램 정보를 수집하기 위해 스캔한 페이지의 비율을 저장한다. 
• histogram-type: 히스토그램의 종류를 저장한다. 
• number-of-buckers-specified: 히스토그램을 생성할 때 설정했던 버킷의 개수를 저장한다. 

 

 

 

 

 

  Ex) 히스토그램 삭제하기

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

• 히스토그램 삭제 작업은 테이블의 데이터를 참조하는 게 아니라 딕셔너리의 내용만 삭제하기 때문에 다른 쿼리 처리의 성능에 영향 주지 않고 완료된다. 

 

 

 

10.1.2.2 히스토그램 용도

• 히스토그램이 도입되기 전에도 테이블과 인덱스에 대한 통계 정보는 존재했다.
• 하지만 기존 MySQL 서버가 가지고 있던 통계 정보는 테이블의 전체 레코드 건수와 인덱스된 칼럼이 가지는 유니크한 값의 개수 정도이다. 
• 실제 응용 프로그램의 데이터는 항상 균등한 분포도를 가지지 않는다. 
• 예를 들어, 어떤 사용자는 주문 레코드를 많이 가지고 있고 또 다른 사용자는 주문 정보가 하나도 없을 수도 있다. 
• MySQL  서버의 기존 통계 정보는 이런 정보를 고려하지 못했다. 
• 이런 단점을 보완하기위해 히스토그램이 도입됐다. 
• 히스토그램은 특정 칼럼이 가지는 모든 값에 대한 분포도 정보를 가지지는 않지만 각 범위(버킷)별로 레코드 건수와 유니크한 값의 개수 정보를 가지기 때문에 훨씬 정확하게 예측 가능하다.

 

 

 

  Ex 1) 히스토그램이 없이 단순 통계 정보만 이용하는 경우

SELECT *
FROM employees
WHERE first_name = 'Zita'
	AND birth_date BETWEEN '1950-01-01' AND '1960-01-01';

• EXPLAIN 키워드를 이용해서 결과를 살펴본다. 
• first_name 조건에 해당하는 레코드 224건이 있고 그 중에서 11.11%인 24.8 명 정도의 birth_date가 1950년대 출생일 것으로 예측했다. 
• 히스토그램이 없으면 옵티마이저는 데이터가 균등하게 분포돼있을 거라고 예측한다.

 

 

 

  Ex 2) 히스토그램이 있을 때

ANALYZE TABLE employees
	UPDATE histogram ON first_name, birth_date;

SELECT *
FROM employees
WHERE first_name = 'Zita'
	AND birth_date BETWEEN '1950-01-01' AND '1960-01-01';

• 히스토그램을 사용한 실행 계획에서는 60.82% 인 136.2명이 1950년대 출생일 것으로 예측했다. 
• 실제 데이터를 조회해보면 대략 63.84%인 143명이 1950년대 출생인 것을 알 수 있다. 
• 히스토그램이 유무에 따라서 차이가 매우 크다.
• 히스토그램이 있으면 특정 범위의 데이터가 많고 적음을 식별 가능하다.
• 히스토그램의 유무는  쿼리의 성능에 영향을 준다. 
• 각 칼럼에 대해 히스토그램 정보가 있으면 어느 테이블을 먼저 읽어야 조인의 횟수를 줄일 수 있을지 옵티마이저가 정확히 판단 가능하다. 

 

 

 

10.1.2.3 히스토그램과 인덱스

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

 

 

 

10.1.3 코스트 모델 (Cost Model)

• MySQL 서버가 쿼리를 처리하려면 다음 작업을 필요로 한다. 
  • 디스크로부터 데이터 페이지 읽기
  • 메모리(InnoDB 버퍼 풀)로부터 데이터 페이지 읽기
  • 인덱스 키 비교
  • 레코드 평가
  • 메모리 임시 테이블 작업
  • 디스크 임시 테이블 작업
• 서버는 사용자 쿼리에  대해 이런 작업이 얼마나 필요한지 예측하고 작업 비용을 계산한 결과를 바탕으로 최적의 실행 계획을 찾는다. 
• Def) 코스트 모델(Cost Model): 전체 쿼리의 비용을 계산하는데  필요한 단위 작업들의 비용을 말한다. 
  • 5.7 버전까지는 이런 작업들의 비용을 MySQL 서버 소스 코드에 상수화해서 사용했다. 
  • 그런데 이 작업들의 비용은 하드웨어에 따라 달라질 수 있기 때문에 예전 버전처럼 일률적으로 적용하는 것은 최적의 실행 계획 수립에 방해 요소였다.
• MySQL 5.7 버전부터는 MySQL 서버의 소스 코드에 상수화돼 있던 각 단위의 작업 비용을 DBMS 관리자가 조정할 수 있게 개선됐다. 
• MySQL 8.0 부터는 칼럼의 데이터 분포를 위한 히스토그램과 각 인덱스별 메모리에 적재된 페이지의 비율이 관리되고 옵티마이저의
• 실행 계획 수립에 사용되기 시작했다. 

 

 

서버의 코스트 모델은 다음 두 테이블에 저장된 설정값을 사용

• 다음 두 테이블 모두 mysql DB에 존재한다.
  • server_cost: 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
  • engine_cost: 레코드를 가진 데이터 페이지를 가져오는 데 필요한 비용을 관리하다. 
• 두 테이블의 공통 칼럼
  • cost_name: 코스트 모델의 각 단위 작업
  • default_value: 각 단위 작업의 비용(기본값이다. MySQL 서버 소스 코드에 설정된 값)
  • cost_value:  DBMS 관리자가 설정한 값(NULL 이면 MySQL 서버는 default_value 칼럼의 비용 사용)
  • last_update: 단위 작어브이 비용이 변경된 시점
  • comment: 비용에 대한 추가 설명
• engine_cost 테이블은 두 개의 칼럼이 더 있다. 
  • engine_name: 비용이 적용된 스토리지 엔진, 스토리지 엔진별로 각 단위 작업의 비용을 설정할 수 있다. 
  • device_type: 디스크 타입 

 

 

 

 

 

출처 - 「Real MySQL 8.0 - 백은빈, 이성욱」