MariaDB 실행 계획 분석 1

옵티마이저 -> 기본 데이터를 비교해 최적의 실행 계획을 수립하는 작업 담당

1. 쿼리 실행 절차

   

2. 옵티마이저의 종류
   1. 규칙 기반 최적화
      1. 대상 테이블의 레코드 건수나 선택도를 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립
   2. 비용 기반 최적화
      1. 실행 계획 별 비용 산출 후 최소 비용이 소요되는 처리 방식을 선택해서 실행
3. 통계정보

• MariaDB에서 기본적으로 관리되는 통계 정보는 대략의 레코드 건수와 인덱스의 유니크한 값의 개수 정도

1. MySQL 5.6의 통계 정보
   1. InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로 관리할 수 있게 개선
   2. innodb_index_stats/innodb_table_stats 테이블에서 인덱스 조회 가능
   3. 영구적으로 통계 정보 저장 여부 옵션 -> STATS_PERSISTENT| 0 : 저장하지 않음, 1 : 테이블에 저장함, DEFAULT : 시스템 설정 변수의 값을 따름|
   4. 테이블 통계 정보의 수집
      1. 테이블 생성
      2. 대량 레코드의 변경(전체 레코드의 1/16)
      3. ANALYZE TABLE 명령 실행
      4. SHOW TABLE STATUS / SHOW INDEX FROM 명령 실행
      5. InnoDB 모니터 활성화
   5. 통계 정보 자동 수집 여부 옵션 -> STATS_AUTO_RECLAC| 0 : ANALYZE TABLE 명령 실행 시 수집, 1 : 자동 수집, DEFAULT : 시스템 설정 변수의 값을 따름|
   6. 통계정보 생성 시 샘플링 할 테이블 블록 옵션
      1. innodb_stats_transient_sample_pages - 자동으로 통계 정보 수집이 실행 될 때 설정된 페이지만 임의로 샘플링, 기본값 8
      2. innodb_stats_persistent_sample_pages - ANALYZE TABLE 명령이 실행되면 설정된 페이지만 샘플링, 기본값 20
2. MariaDB 10.0의 통계 정보 – ANALYZE TABLE 명령으로만 가능
   1. 인덱스되지 않은 컬럼에 대해서도 영구적으로 통계 정보 관리 가능 -> 정확도가 높은 통계 정보를 백업/복구해서 사용 가능
   2. 통계 정보가 부정확한 경우에 관리자가 직접 통계 정보 변경 가능 -> 옵티마이저가 조금 더 정확한 정보를 기준으로 실행 계획 수립 가능
   3. 통계정보 관리 방법
      1. use_stat_tables = 'never' : 통합 통계 정보 수집 불가
      2. use_stat_tables = 'complementary' : 통계 정보 사용 우선순위 1. 스토리지 엔진 통계 정보, 2. 통합 통계 정보
      3. use_stat_tables = 'preferably' : 통계 정보 사용 우선순위 1. 통합 통계 정보, 2. 스토리지 엔진 통계 정보

1. 히스토그램 통계 정보

• 히스토그램은 컬럼 값의 분포도를 분석할 수 있는 통계 정보

1. 히스토그램이란?
   1. 인덱스 여부와 상관 없이 원하는 컬럼에 대한 히스토그램 정보 저장 가능
   2. 히스토그램 관리 방법 : Height-Balanced Histogram -> 데이터를 정렬해서 그룹으로 나눈 다음 최대 값을 구해서 값의 분포도를 구함
2. MariaDB에서 히스토그램 사용
   1. ANALYZE TABLE 명령으로 히스토그램 생성
   2. 히스토그램 사용 여부 옵션 : histogram_size| 0 : 사용 안함, 1 이상 : 사용, 기본값 0
   3. 히스토그램 정확도 옵션 – 히스토그램의 그룹 수 : DOUBLE_PREC_HB – 그룹 수 : (hist_size/2) / SINGLE_PRC_HB – 그룹 수 : hist_size
   4. 히스토그램을 사용할 때 MariaDB의 엔진 레벨과 스토리지 엔진 레벨의 통계 정보를 설정 값이 따라서 섞어서 사용 옵션 : optimizer_user_condition_selectivity
      1. MariaDB 5.5에서 사용되던 선택도 예측 방식 사용
      2. 인덱스가 생성되어 있는 컬럼의 조건에 대해서만 선택도 판단
      3. 히스토그램 사용 안 함
      4. 히스토그램 사용
      5. 히스토그램 사용 및 인덱스 레인지 스캔이 불가한 컬럼에 대해서 레코드 샘플링으로 선택도 판단

1. 조인 옵티마이저 옵션
   1. Exhaustive 검색
      1. FROM 절에 명시된 모든 테이블의 조합에 대해서 실행 계획 비용 생성 후 최적의 조합 1개를 선택
   2. Heuristic 검색(Greedy 검색)
   3. 조인 최적화 시스템 변수
      1. optimizer_prune_level : 조인 알고리즘 선택| OFF : Exhaustive, ON : Greedy, 기본값 ON
      2. optimizer_search_depth : Greedy 검색에서 사용하는 옵션으로 설정된 개수의 테이블로 가능한 조인 조합을 생성할 때 사용, 기본값 60, 작아질수록 최적화 감소