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

~ 5.7 버전까지 : 테이블과 인덱스에 대한 정보를 가지고 실행계획을 수립.

=> 실제 값들의 분포에 대한 정보가 없어서 정확도가 떨어졌음.

=> 8.0 부터는 인덱스되지 않은 컬럼들에해대서도 데이터 분포도를 수집해 저장하는 히스토그램(Histogram) 정보가 도입

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

비용 기반 최적화에서 가장 중요한 것은 통계 정보임.

=> 통계정보가 정확하지 않으면 엉뚱한 방향으로 쿼리를 실행

MySql은 비용 기반 최적화를 사용하짐만 다른 DBMS 보다 통계 정보의 정확도가 높지 않고 통계 정보의 휘발성이 강했음.

=> 쿼리 실행 계획을 수입할 때 실제 테이블의 데이터를 일부 분석해서 통계 정보를 보완해서 사용했음.

=> 5.6 버전부터는 통계 정보의 정확성을 높일 수 있는 방법이 제공되기 시작했음

10.1.1.1 MySql 서버의 통계 정보

5.6 버전부터는 InnoDB 엔진을 사용하는 테이블에 대한 통계정보를 영구적으로 관리할 수 있게 개선.(전에는 메모리에만 관리)

innodb_index_stats 테이블과 innodb_table_stats 테이블로 관리할수 있게 개선.

show tables like '%_stats';

5.6 에서 테이블을 생성할 때는 STATS_PERSISTENT 옵션을 설정할 수 있는데

이 설정값에 따라 테이블 단위로 영구적인 통계 정보를 보관할지 말지를 결정할 수 있다.

CREATE TABLE TAB_TEST(
    FD1 INT,
    FD2 VARCHAR(20),
    primary key(FD1)
)engine=InnoDB
stats_persistent={DEFAULT | 0 | 1}

• 0 : 통계정보를 메모리로만 관리
• 1 : 통계정보를 영속적으로 관리(테이블)
• DEFAULT : innodb_stats_persistent 시스템 변수 값으로 결정

innodb_stats_persistent 변수값은 기본적으로 ON으로 설정되어 있음.

실제로 stats_persistent 설정 값에 따라 innodb_table_stats 테이블에 관리되는지 확인 가능

alter 명령어로 stats_persistent 를 변경 가능

** employees 테이블 예시 **

• innodb_index_stats.stat_name='n_diff_pfx%' : 인덱스가 가진 유니크한 값의 개수
• innodb_index_stats.stat_name='n_leaf_pages' : 인덱스의 리프노드 페이지 개수
• innodb_index_stats.stat_name='size' : 인덱스 트리의 전체 페이지 개수
• innodb_table_stats.n_rows : 테이블 전체 레코드 수
• innodb_table_stats.clustered_index_size : 프라이머리 키의 크기(InnoDB 페이지 개수)
• innodb_table_stats.sum_of_other_index_sizes : 프라이머리 키를 제외한 인덱스의 크기(InnoDB 페이지 개수)

innodb_table_stats.sum_of_other_index_sizes 값은 테이블의 STATS_AUTO_RECALC 옵션에 따라 0으로 보일수도 있음

그럴 경우 ANALYZE TABLE employees 명령을 통해 통계값을 저장할 수 있음

5.5 버전 까지는는 테이블 통계가 메모리에서만 관리될 뿐만 아니라 사용자가 알지 못하는 순간에 아래와 같은 이벤트가 발생하면 자동으로 통계 정보가 갱신 됐음.

• 테이블이 새로 오픈되는 경우
• 테이블의 레코드가 대량으로 변경되는 경우(1/6 정도)
• ANALYZE TABLE 명령이 실행되는 경우
• SHOW TABLE STATUS 명령이나 SHOW INDEX FROM 명령이 실행되는 경우
• InnoDB 모니터가 활성화되는 경우
• innodb_status_on_metadata 시스템 설정이 ON인 상태에서 SHOW TABLE STATUS 명령이 실행되는 경우

이렇게 자주 테이블 통계정보가 갱신되면 인덱스 레인지 스캔으로 잘 처리되던 것이 어느날 풀 테이블 스캔으로 실행되는 상황이 발생할 수도 있음.

=> 영구적인 통계 정보가 도입되면서 의도치 않은 통계 정보 변경을 막을 수 있게 됨.

또한 innodb_status_auto_recalc 시스템 설정 변수의 값을 OFF로 설정해서 통계 정보가 자동으로 갱신 되는 것을 막을 수 있음.

통계 정보를 자동으로 수집할 지 여부도 테이블 생성 시 STATS_AUTO_RECALC 옵션을 통해 조정할 수 있음.

• STATS_AUTO_RECALC=1 : 5.5 이전 방식대로 자동 수집
• STATS_AUTO_RECALC=0 : ANALYZE TABLE 명령을 실행할 때만 수집
• STATS_AUTO_RECALC=DEFAULT : innodb_stats_auto_recalc 시스템 변수의 값으로 결정

5.5 버전에서는 테이블 통계 정보를 수집할 때 몇 개의 InnoDB 테이블 블록을 샘플링 할 지 결정하는 옵션으로 innodb_stats_sampl_pages 시스템 설정 변수가 제공.

5.6 부터는 이 옵션이 없어지고 innodb_stats_transient_sample_pages 와 innodb_stats_persistent_sample_pages 시스템 변수 2개로 분리됨.

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

정확한 통계 정보를 수집하고 싶으면 innodb_stats_persistent_sample_pages 를 높은 값으로 설정하면 됨

하지만 값이 높을 수록 정보 수집시간이 길어지므로 주의해야함.

10.1.2 히스토그램

5.7 버전 까지는 통계정보를 인ㄴ덱스된 컬럼의 유니크한 값의 개수 정도만 가지고 있었음

=> 옵티마이저가 최적의 실행계획을 수립하기에 부족

=> 부족함을 메우기 위해 실행 계획을 수립할 때 실제 인덱스의 일부 페이지를 랜덤으로 가져와 참조하는 방식 사용.

8.0 버전부터는 컬럼의 데이터 분포도를 참조할 수 있는 히스토그램(Histogram) 정보를 활용할 수 있게됨.

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

히스토그램 수집

8.0 버전에서 히스토그램 정보는 컬럼단위로 관리됨.

ANALYZE TABLE ... UPDATE HISTOGRAM 명령을 실행해 수동으로 수집 및 관리됨.

히스토그램 정보는 시스템 딕셔너리에 저장되고, MySql 서버가 시작될 때 딕셔너리의 히스토그램 정보를

information_schema 데이터베이스의 column_statistics 테이블로 로드함.

=> 실제 히스토그램 정보를 조회하려면 column_statistics 테이블을 select 해서 참조할 수 있음.

8.0 버전에서 지원하는 히스토그램 종류

• 싱글톤 히스토그램(Singleton)
  
  컬럼값 개별로 레코드 건수를 관리하는 히스토그램.
  
  Value-Based 히스토그램 또는 도수 분포라고도 부름
• 높이 균형 히스토그램(Equi-Height)
  
  칼럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램
  
  Height-Balanced 히스토그램 이라고도 부름

히스토그램은 버킷(Bucket) 단위로 구분되어 관리됨.

버킷 구분 단위

싱글톤 히스토그램 : 컬럼 값

높이균형 히스토그램 : 개수가 균등한 컬럼값의 범위

버킷의 구성

싱글톤 히스토그램 : 컬럼 값, 발생빈도의 비율

높이균형 히스토그램 : 범위 시작 값, 마지막 값, 발생 빈도율, 유니크 한 값의 개수

information_schema.column_statistics 테이블의 HITOGRAM 칼럼이 가진 나머지 필드

• sampling-rate
  
  히스토그램 정보를 수집하기 위해 스캔한 페이지의 비율
  
  샘플링 비율이 높아질 수록 더 정확한 히스토그램이 되지만 높을수록 부하가 높고 시스템 자원을 많이 소모
  
  histogram_generation_max_mem_size 시스템 변수에 설정된 메모리 크기(default 20Mb )에 맞게 샘플링함.
• histogram-type
  
  히스토그램 종류
• number-of-buckets-specified
  
  히스토그램 생성 시 설정했던 버킷의 개수
  
  기본 100, 최대 1024, 일반적으로 100 개의 버킷이면 충분한 것으로 알려져있음.

8.0.19 전까지는 히스토그램 생성 시 무조건 풀 스캔을 했음.

8.0.19 부터 InnoDB 엔진 자체적으로 샘플링 알고리즘을 구현해서 더이상 풀스캔이 필요하지 않게 됨.

8.0.19 버전 보다 낮으면 히스토그램 수집 시 주의.

히스토그램 삭제

히스토그램 삭제는 딕셔너리의 내용만 삭제하기 때문에 다른 쿼리 처리의 성능에 영향을 주지 않고 즉시 완료됨.

하지만 히스토그램이 사라지면 쿼리의 실행계획이 달라질 수 있음.

analyze table employees
drop histogram on gender, hire_date;

히스토그램을 삭제하지 않고 옵티마이저가 히스토그램을 사용하지 않게 하려면

optimizer_switch 시스템 변수의 값을 변경하면됨.

글로벌로 변경 시 모든 쿼리가 히스토그램을 사용하지 않는다.

또한 condition_fanout_filter 옵션에 의해 영향 받는 다른 최적화 기능들이 사용되지 않을 수 있음.

-- global
set global optimizer_switch='condition_fanout_filter=off';

-- session
set session optimizer_switch='condition_fanout_filter=off';

-- 현재 쿼리에서만
select /*+ set var(optimizer_switch='condition_fanout_filter=off')*/ *
from employees;

10.1.2.2 히스토그램의 용도

히스토그램이 도입되기 전에는 전체 레코드 개수와 인덱스된 컬럼이 가지는 유니크한 값의 개수만 가지고 있었음.

=> 데이터가 동등한 분포도를 가지고 있다는 전제하에 실행계획을 만듬

=> 최적화 되지 않은 실행계획을 수립하는 일이 생김

=> 히스토그램을 도입 후 훨씬 정확한 예측을 함

히스토그램을 만들기 전에는 1950년대생이 약 11% 있을거라고 예측

히스토그램을 사용한 실행 계획은 대략 61% 가 1950 년대생으로 예측

실제로는 약 63% 로 히스토그램을 사용하는것이 훨씬 정확함.

히스토그램이 있으면 특정 범위의 데이터가 많고 적음을 식별할 수 있음.

=> 이는 쿼리 성능에 상당한 영향을 미칠 수 있음

select /*+ join_order(e, s)*/ *
from salaries s
join employees e
    on e.emp_no = s.emp_no
    and e.birth_date between '1950-01-01' and '1950-02-01'
where s.salary between 40000 and 70000;

select /*+ join_order(s, e)*/  *
from salaries s
join employees e
    on e.emp_no = s.emp_no
    and e.birth_date between '1950-01-01' and '1950-02-01'
where s.salary between 40000 and 70000;

birth_date 와 salary 컬럼 은 인덱스 되지 않은 컬럼이어서 히스토그램이 없다면 옵티마이저는 데이터 분포를 알지 못하고 실행계획을 수립하게됨.

때문에 힌트를 제거하면 테이블의 전체 레코드 건수나 크기 같은 단순한 정보만으로 조인의 드라이빙 테이블을 결정하게 됨.

10.1.2.3 히스토그램과 인덱스

히스토그램은 인덱스와 전혀 다른 객체지만 부족한 통계정보 수집을 위해 사용된다는 측면에서는 비슷하다.

실행 계획 수립 시 사용가능한 인덱스들로부터 조건절에 일치하는 레코드 건수를 파악하고 최종적으로 나은 실행계획을 선택함

조건에 일치하는 레코드 수를 파악하기 위해 옵티마이저는 실제 인덱스의 B-Tree를 샘플링 해서 살펴보는데 이를 인덱스 다이브(Index-Dive) 라고 함

MySql 8.0 에서는 인덱스된 컬럼을 조건절에 사용하는 경우 그 컬럼의 히스토그램을 사용하지 않고 실제 인덱스 다이브를 통해 직접 수집한 정보를 활용함.

실제 검색 조건 대상값에 대한 샘플링을 실행한 것이므로 항상 히스토그램보다 정확한 결과를 기대할 수 있기 때문임.

그래서 히스토그램은 주로 인덱스 되지 않은 칼럼에 대한 데이터 분포도를 참조하는 용도로 사용됨.

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

10.1.3 코스트 모델(Cost Model)

MySql 서버가 쿼리를 처리하려면 다음 같은 작업들을 필요로함

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

사용자의 쿼리에 대해 위와 같은 작업들이 얼마나 필요한지 예측하고 계산한 비용을 바탕으로 실행계획을 찾음.

이렇게 쿼리의 비용을 계산하는데 필요한 단위 작업들의 비용을 코스트 모델이라고 함.

5.7 전 까지는 이 비용을 서버 소스 코드에 상수화하여 사용했음

=> 하드웨어에 따라 비용이 달라질 수 있음

=> 5.7 부터 비용을 DBMS 관리자가 조정할 수 있게 개선

=> 인덱스 되지 않은 컬럼의 데이터 분포나 메모리에 상주 중인 페이지의 비율 등 비용계산과 관련된 정보가 부족

=> 8.0 부터 히스토그램과 인덱스별 메모리에 적재된 페이지의 비율이 관리

MySql 8.0 서버의 코스트 모델은 다음 두 테이블에 저장돼 있는 설정 값을 사용함(모두 mysql DB 에 존재)

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

공통컬럼

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

engine_cost 에 더 있는 컬럼

• engin_name : 비용이 적용된 스토리지 엔진
• device_type : 디스크 타입

MySql 8.0 버전의 코스트 모델에서 지원하난 단위 작업

	cost_name	default_value	설명
engine_cost	io_block_read_cost	1.00	디스크 데이터 페이지 읽기
	memory_block_read_cost	0.25	메모리 데이터 페이지 읽기
server_cost	disk_temtable_create_cost	20.00	디스크 임시 테이블 생성
	disk_temptable_row_cost	0.50	디스크 임시 테이블의 레코드 읽기
	key_compare_cost	0.05	인덱스 키 비교
	memory_temptable_create_cost	1.00	메모리 임시 테이블 생성
	memory_temptable_row_cost	0.10	메모리 임시 테이블의 레코드 읽기
	row_evaluate_cost	0.10	레코드 비교

row_evaluate_cost 가 높으면 풀 테이블 스캔과 같은 많은 레코드를 처리하는 쿼리의 비용이 높아지고

반대로 레인지 스캔과 같이 상대적으로 적은 수의 레코드를 처리하는 쿼리의 비용이 낮아짐.

key_compare_cost 가 증가할수록 레코드 정렬과 같인 키 값 비교 처리가 많은 경우 쿼리의 비용이 높아짐.

-- cost 확인
explain format=tree
select *
from employees
where first_name = 'Matt';

/*
-> Index lookup on employees using ix_firstname (first_name='Matt')  (cost=256.30 rows=233)
 */

explain format=json
select *
from employees
where first_name = 'Matt';

/*
{
   "query_block": {
     "select_id": 1,
     "cost_info": {
       "query_cost": "256.30"
     },
     "table": {
       "table_name": "employees",
       "access_type": "ref",
       "possible_keys": [
         "ix_firstname"
       ],
       "key": "ix_firstnam...
*/

각 단위 작업의 비용을 이용해 실행계획에 표시되는 비용을 계산해 보고 싶을 수 있지만

사용자에게 표시되지 않는 정보가 많이 때문에 직접 계산 하는것은 어렵다.

중요한 점은 단위 작업에 설정되는 비용 값이 커지면 어떤 실행 계획들이 고비용으로 또는 저비용으로 바뀌는지 파악하는 것이다.

비용 변경에 따른 예상 예사

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