Post

Real MySQL 8.0 - 10장 실행 계획

Posted
By hkh7670 12 min read

10. 실행 계획

10.1 통계 정보

  • MySQL 5.7 까지는 테이블과 인덱스에 대한 정보를 가지고 실행 계획을 수립했다.
  • MySQL 8.0 부터는 인덱스되지 않은 칼럼들에 대해서도 데이터 분포도를 수집해서 저장하는 히스토그램 정보가 도입됐다.

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

  • 비용 기반 최적화에서 가장 중요한 지표는 통계 정보다.
    • 통계정보가 정확하지 않다면 전혀 엉뚱한 방향으로 쿼리를 실행할 수 있기 때문
    • ex) 1억 건의 레코드가 저장된 테이블의 통계 정보가 갱신되지 않아서 레코드가 10건 미만인 것처럼 되어있다면 옵티마이저는 실제 쿼리를 실행할 때 인덱스 레인지 스캔이 아닌 풀 스캔으로 실행해 버릴 수도 있다.
  • MySQL 서버에서는 실제 테이블 데이터의 일부를 분석해서 통계 정보를 보완하여 사용한다.

10.1.1.1 MySQL 서버의 통계 정보

  • 통계정보가 메모리에 관려될 경우 MySQL 서버가 재시작되면 지금까지 수집된 통계 정보가 모두 사라진다.
  • MySQL 5.6 버전부터 InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로 관리할 수 있게 개선되었다.

  • 각 테이블의 통계 정보를 mysql 데이터베이스의 innodb_index_stats 테이블과 innodb_table_stats 테이블로 관리할 수 있게 개선됐다.

    • STATS_PERSISTENT 옵션에 따라 통계정보를 영구 관리할지 말지를 결정할 수 있다. (defualt 는 영구 관리)

      1
2
3

      mysql> CREATE TABLE tab_test (fd1 INT, fd2 VARCHAR(20), PRIMARY KEY(fd1))
       ENGINE=InnoDB
       STATS_PERSISTENT={ DEFAULT | 0 | 1 }
      • 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 시스템 변수의 값으로 결정
          • 시스템 설정 변수는 기본적으로 ON(1)로 설정되어있음
  • innodb_index_stats 테이블의 통계 정보 칼럼
    • stat_name = ‘n_diff_pfx%’ : 인덱스가 가진 유니크한 값의 개수
    • stat_name = ‘n_leaf_pages’ : 인덱스의 리프 노드 페이지 개수
    • stat_name = ‘size’ : 인덱스 트리의 전체 페이지 개수
    • n_rows : 테이블의 전체 레코드 건수
    • clustered_index_size : 프라이머리 키의 크기(InnoDB 페이지 개수)
    • sum_of_other_index_sizes : 프라이머리 키를 제외한 인덱스의 크기(InnoDB 페이지 개수)
  • innodb_stats_transient_sample_pages 시스템 변수
  • innodb_stats_persistent_sample_pages 시스템 변수
    • 기본 값은 20이며, ANALYZE TABLE 명령이 실행되면 임의로 20개의 페이지를 샘플링하여 분석하고 그 결과를 통계 정보 테이블에 저장하고 활용한다.
    • 더 정확한 통계 정보를 수집하고자 하면 이 시스템 변수의 값을 높게 설정하면 된다.
    • 하지만 이 값을 너무 높이면 통계 정보 수집 시간이 길어지므로 주의해야 한다.

10.1.2 히스토그램

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

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

  • MySQL 8.0에서는 칼럼 단위로 히스토그램 정보가 관리된다.
  • 이는 자동으로 수집되지 않고 ANALYZE TABLE … UPDATE HISTOGRAM 명령을 실행해야 한다.
  • information_schema 데이터베이스의 column_statistics 테이블을 select 해보면 히스토그램 정보를 확인할 수 있다.
  • MySQL 8.0에서 지원하는 히스토그램 타입
    • singleton(싱글톤 히스토그램) : 칼럼값 개별로 레코드 건수를 관리하는 히스토그램, Value-Based 히스토그램 또는 도수 분포라고도 불린다.
    • Equi-Height(높이 균형 히스토그램) : 칼럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램, Height-Balanced 히스토그램이라고도 불린다.

10.1.2.2 히스토그램의 용도

  • 히스토그램이 도입되기 전의 통계정보는 테이블의 전체 레코드 건수와 인덱스된 칼럼이 가지는 유니크한 값의 개수 정도였다.
  • 히스토그램은 특정 칼럼이 가지는 모든 값에 대한 분포도 정보를 가지지는 않지만 각 범위(버킷)별로 레코드의 건수와 유니크한 값의 개수 정보를 가지기 때문에 훨씬 정확한 예측을 할 수 있다.
    • 히스토그램 정보가 없으면 옵티마이저는 데이터가 균등하게 분포돼 있을 것으로 예측한다. 하지만 히스토그램이 있으면 특정 범위의 데이터가 많고 적음을 식별할 수 있다.
    • 각 칼럼에 대해 히스토그램 정보가 있으면 어느 테이블을 먼저 읽어야 조인의 횟수를 줄일 수 있을지 옵티마이저가 더 정확히 판단할 수 있다.

10.1.2.3 히스토그램과 인덱스

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

10.1.3 코스트 모델(Cost Model)

  • MySQL 서버가 쿼리를 처리하려면 다음과 같은 다양한 작업을 필요로 한다.
    • 디스크로부터 페이지 읽기
    • 메모리(InnoDB 버퍼 풀)로부터 데이터 페이지 읽기
    • 인덱스 키 비교
    • 레코드 평가
    • 메모리 임시 테이블 작업
    • 디스크 임시 테이블 작업
  • MySQL 서버는 사용자의 쿼리에 대해 이러한 다양한 작업이 얼마나 필요한지 예측하고 전체 작업 비용을 계산한 결과를 바탕으로 최적의 실행계획을 찾는다.
  • 이렇게 전체 쿼리의 비용을 계산하는 데 필요한 단위 작업들의 비용을 코스트 모델(Cost Model) 이라고 한다.
  • MySQL 8.0 에서부터 컬럼의 데이터 분포를 위한 히스토그램과 각 인덱스별 메모리에 적재된 페이지의 비율이 관리되고 옵티마이저의 실행 계획 수립에 사용되기 시작함
  • MySQL 8.0서버의 코스트 모델은 다음 2개의 테이블에 저장된 설정값을 사용
    • server_cost
      • 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
    • engine_cost
      • 레코드를 가진 데이터 페이지를 가져오는 데 필요한 비용 관리
  • server_code 테이블과 engine_cost 테이블은 공통으로 다음 5개의 컬럼을 가지고 있다.
    • cost_name
      • 코스트 모델의 각 단위 작업
    • default_value
      • 각 단위 작업의 비용(기본값이며, 이 값은 MySQL 서버 소스 코드에 설정된 값)
    • cost_value
      • DBMS 관리자가 설정한 값(이 값이 NULL이면 MySQL 서버는 default_value 컬럼의 비용 사용)
    • last_updated
      • 단위 작업의 비용이 변경된 시점
    • comment
      • 비용에 대한 추가 설명
  • engine_cost 테이블은 위 5개 컬럼에 추가로 다음 2개 컬럼이 더 존재한다.
    • engine_name
      • 비용이 적용된 스토리지 엔진
    • device_type
      • 디스크 타입

10.2 실행 계획 확인

10.2.1 실행 계획 출력 포맷

Real MySQL 8.0
DB SQL
This post is licensed under CC BY 4.0 by the author.