[스터디] 19. 실행 계획

목차

10. 실행 계획

10.1 통계 정보

• MySQL 서버는 5.7 버전까지 테이블과 인덱스에 대한 개괄적인 정보를 가지고 실행 계획을 수립함.
• 하지만 테이블 칼럼의 값들이 어떻게 분포되어 있는지에 대한 정보가 없기 때문에, 실행 계획의 정확도가 떨어지는 경우가 많았음.
• MySQL 8.0 버전부터는 인덱스되지 않은 칼럼들에 대해서도 데이터 분포도를 수집해서 저장하는 히스토그램(Histogram) 정보가 도입됨. 히스토그램이 도입됐다고 해서 기존의 테이블이나 인덱스의 통계 정보가 필요치 않은 것은 아님.

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

• 비용 기반 최적화에서 가장 중요한 것은 통계 정보임.
• 통계 정보가 정확하지 않다면 엉뚱한 방향으로 쿼리를 실행할 수 있기 때문임. 부정확한 통계 정보 탓에 0.1초에 끝날 쿼리에 1시간이 소요될 수 있음.
• MySQL 또한 다른 DBMS와 같이 비용 기반의 최적화를 사용하지만, 다른 DBMS보다 통계 정보의 정확도가 높지 않고 통계 정보의 휘발성이 강했음.
• MySQL 서버에서는 쿼리의 실행 계획을 수립할 때, 실제 테이블의 데이터를 일부 분석해서 통계 정보를 보완해서 사용함.
• MySQL 5.6 버전부터는 통계 정보의 정확성을 높일 수 있는 방법이 제공되기 시작했지만, 아직도 많은 사용자가 기존 방식을 그대로 사용함.

10.1.1.1 MySQL 서버의 통계 정보

• MySQL 5.6 버전부터는 InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로(Persistent) 관리할 수 있게 개선되었음.
• MySQL 5.5 버전까지는 각 테이블의 통계 정보가 메모리에만 관리되고, SHOW INDEX 명령으로만 테이블의 인덱스 칼럼의 분포도를 볼 수 있었음. 하지만 통계 정보가 메모리에 관리될 경우, MySQL 서버가 재시작되면 지금까지 수집된 통계 정보가 모두 사라짐.
• MySQL 5.6 버전부터는 각 테이블의 통계 정보를 mysql 데이터베이스의 innodb_index_stats 테이블과 innodb_table_stats 테이블로 관리할 수 있게 개선되었음. 통계 정보를 테이블로 관리함으로써 MySQL 서버가 재시작되어도 기존의 통계 정보를 유지할 수 있음.
• 또한, 사용자나 관리자가 알지 못하는 순간에 다음과 같은 이벤트가 발생하면 자동으로 통계 정보가 갱신됨.
  • 테이블이 새로 오픈되는 경우
  • 테이블의 레코드가 대량으로 변경되는 경우(테이블의 전체 레코드 중에서 1/16 정도의 UPDATE 또는 INSERT나 DELETE가 실행되는 경우)
  • ANALYZE TABLE 명령이 실행되는 경우
  • SHOW TABLE STATUS 명령이나 SHOW INDEX FROM 명령이 실행되는 경우
  • InnoDB 모니터가 활성화되는 경우
  • innodb_stats_on_metadata 시스템 설정이 ON인 상태에서 SHOW TABLE STATUS 명령이 실행되는 경우
• 자주 테이블의 통계 정보가 갱신되면, 응용 프로그램의 쿼리를 인덱스 레인지 스캔으로 잘 처리하던 MySQL 서버가 갑자기 풀 테이블 스캔으로 실행되는 상황이 발생할 수도 있음. 하지만 영구적인 통계 정보가 도입되면서 의도하지 않은 통계 정보 변경을 막을 수 있음. 또한, innodb_stats_auto_recalc 시스템 설정 변수의 값을 OFF로 설정해서 통계 정보가 자동으로 갱신되는 것을 막을 수 있음.
  • STATS_AUTO_RECALC=1: 테이블의 통계 정보를 MySQL 5.5 이전의 방식대로 자동 수집함.
  • STATS_AUTO_RECALC=0: 테이블의 통계 정보는 ANALYZE TABLE 명령을 실행할 때만 수집됨.
  • STATS_AUTO_RECALC=DEFAULT: 테이블을 생성할 때 별도로 STATS_AUTO_RECALC 옵션을 설정하지 않은 것과 동일 하며, 테이블의 통계 정보 수집을 innodb_stats_auto_recalc 시스템 설정 변수의 값으로 결정함.
• MySQL 5.5 버전에서는 테이블의 통계 정보를 수집할 때 몇 개의 InnoDB 테이블 블록을 샘플링할지 결정하는 옵션으로 innodb_stats_sample_pages 시스템 설정 변수가 제공되는데, MySQL 5.6 버전부터 이 옵션이 없어짐(Deprecated). 이 시스템 변수는 innodb_stats_transient_sample_pages와 innodb_stats_persistent_sample_pages 시스템 변수로 분리되었음.
  1. innodb_stats_transient_sample_pages: 기본값은 8인데, 자동으로 통계 정보 수집이 실행될 때 8개 페이지만 임의로 샘플링해서 분석하고 그 결과를 통계 정보로 활용함을 의미함.
  2. innodb_stats_persistent_sample_pages: 기본값은 20이며, ANALYZE TABLE 명령이 실행되면 임의로 20개 페이지만 샘플링해서 분석하고 그 결과를 영구적인 통계 정보 테이블에 저장하고 활용함을 의미함.

10.1.2 히스토그램

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

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

• MySQL 8.0 버전에서 히스토그램 정보는 칼럼 단위로 관리되는데, 이는 자동으로 수집되지 않고 ANALYZE TABLE ... UPDATE HISTOGRAM 명령을 실행해 수동으로 수집 및 관리됨.
• 수집된 히스토그램 정보는 시스템 딕셔너리에 함께 저장되고, MySQL 서버가 시작될 때 딕셔너리의 히스토그램 정보를 information_schema 데이터베이스의 column_statistics 테이블로 로드함. 실제 히스토그램 정보를 조회하려면, column_statistics 테이블을 SELECT해서 참조할 수 있음.
• MySQL 8.0 버전에서는 다음과 같이 2종류의 히스토그램 타입이 지원됨.
  1. Singleton(싱글톤 히스토그램): 칼럼값 개별로 레코드 건수를 관리하는 히스토그램. Value-Based 히스토그램 또는 도수 분포라고도 불림.
  2. Equi-Height(높이 균형 히스토그램): 칼럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램. HeightBalanced 히스토그램이라고도 불림.
• 히스토그램은 버킷(Bucket) 단위로 구분되어 레코드 건수나 칼럼값의 범위가 관리됨.
• 싱글톤 히스토그램은 칼럼이 가지는 값별로 버킷이 할당되며 높이 균형 히스토그램에서는 개수가 균등한 칼럼값의 범위별로 하나의 버킷이 할당됨. 그리고 각 버킷이 칼럼의 값과 발생 빈도의 비율의 2개 값을 가짐.
• 높이 균형 히스토그램은 각 버킷이 범위 시작 값과 마지막 값, 그리고 발생 빈도율과 각 버킷에 포함된 유니크한 값의 개수 등 4개의 값을 가짐.

10.1.2.2 히스토그램의 용도

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

10.1.2.3 히스토그램과 인덱스

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

10.1.3 코스트 모델(Cost Model)

• MySQL 서버가 쿼리를 처리하려면 아래와 같은 작업을 필요로 함.
  • 디스크로부터 데이터 페이지 읽기
  • 메모리(InnoDB 버퍼 풀)로부터 데이터 페이지 읽기
  • 인덱스 키 비교
  • 레코드 평가
  • 메모리 임시 테이블 작업
  • 디스크 임시 테이블 작업
• MySQL 서버는 사용자의 쿼리에 대해 이러한 작업이 얼마나 필요한지 예측하고, 전체 작업 비용을 계산한 결과를 바탕으로 최적의 실행 계획을 찾음. 전체 쿼리의 비용을 계산하는 데 필요한 단위 작업들의 비용을 코스트 모델(Cost Model)이라고 함.
• MySQL 5.7 이전 버전까지는 이런 작업들의 비용을 MySQL 서버 소스 코드에 상수화해서 사용함. 이 비용은 MySQL 서버가 사용하는 하드웨어에 따라 달라질 수 있기 때문에 최적의 실행 계획 수립에 있어서 방해 요소임.
• MySQL 5.7 버전부터 MySQL 서버의 소스 코드에 상수화돼 있던 각 단위 작업의 비용을 DBMS 관리자가 조정할 수 있게 개선되었음. 하지만 MySQL 5.7 버전에서는 인덱스되지 않은 칼럼의 데이터 분포(히스토그램)나 메모리에 상주 중인 페이지의 비율 등 비용 계산과 연관된 부분의 정보가 부족한 상태였음.
• MySQL 8.0 버전에서 칼럼의 데이터 분포를 위한 히스토그램과 각 인덱스별 메모리에 적재된 페이지의 비율이 관리되고 옵티마이저의 실행 계획 수립에 사용되기 시작함.
• MySQL 8.0 서버의 코스트 모델은 아래 2개 테이블에 저장돼 있는 설정값을 사용함. 두 테이블 모두 mysql DB에 존재함.
  1. server_cost: 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
  2. engine_cost: 레코드를 가진 데이터 페이지를 가져오는 데 필요한 비용 관리
• server_cost 테이블과 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: 디스크 타입
• engine_name 칼럼은 스토리지 엔진별로 각 단위 작업의 비용을 설정할 수 있는데, 기본값은 "default"임. "default"는 특정 스토리지 엔진의 비용이 설정되지 않았다면 해당 스토리지 엔진의 비용으로 이 값을 적용한다는 의미임.

10.2 실행 계획 확인

• MySQL 서버의 실행 계획은 DESC 또는 EXPLAIN 명령으로 확인할 수 있음.
• MySQL 8.0 버전부터는 EXPLAIN 명령에 사용할 수 있는 새로운 옵션이 추가되었음.

10.2.1 실행 계획 출력 포맷

• 이전 버전에서는 EXPLAIN EXTENDED 또는 EXPLAIN PARTITIONS 명령이 구분되어 있었음.
• MySQL 8.0 버전 부터는 모든 내용이 통합되어 보이도록 개선되면서 PARTITIONS나 EXTENDED 옵션은 문법에서 제거됨.
• MySQL 8.0 버전부터는 FORMAT 옵션을 사용해 실행 계획의 표시 방법을 JSON이나 TREE, 단순 테이블 형태로 선택할 수 있음.

10.2.2 쿼리의 실행 시간 확인

• MySQL 8.0.18 버전부터는 쿼리의 실행 계획과 단계별 소요된 시간 정보를 확인할 수 있는 EXPLAIN ANALYZE 기능이 추가됨.
• SHOW PROFILE 명령으로 어떤 부분에서 시간이 많이 소요되는지 확인할 수 있지만, SHOW PROFILE 명령의 결과는 실행 계획의 단계별로 소요된 시간 정보를 보여주진 않음.
• EXPLAIN ANALYZE 명령은 항상 결과를 TREE 포맷으로 보여주기 때문에, EXPLAIN 명령에 FORMAT 옵션을 사용할 수 없음.

 

이 글은 『Real MySQL 8.0 (1권)』 책을 학습한 내용을 정리한 것입니다.