책너두 (Real MySQL 8.0 1권) 36일차 (~411p)

요약

• MySQL 의 실행 계획을 설정하는 방법을 이해하게 됨.
  • 통계 정보 활용
    • 테이블의 데이터나 인덱스를 이용하여 통계 정보를 사용함.
    • 히스토그램을 통해 데이터 분포도를 알게 되기에 특정 범위의 데이터가 많고 적음을 알 수 있음.
      • 따라서, 히스토그램을 통해 쿼리의 최적의 실행 계획을 만들어 낼 수 있다.
• 코스트 모델에 대해 이해하게 됨.
  • MySQL 엔진이 쿼리를 처리할 때 여러 작업을 필요로 하는데, 코스트 모델에 대한 단위 작업들을 변경에 따른 비용 혹은 실행 계획의 변화를 알 수 있음.

메모

실행 계획

• 대부분의 RDBMS는 많은 데이터를 안전하게 저장 및 관리하고 사용자가 원하는 데이터를 빠르게 조회할 수 있게 해주는 것이 주목적임.
  • 이 목적을 달성하려면 옵티마이저가 사용자의 쿼리를 최적으로 처리될 수 있게 하는 쿼리의 실행 계획을 수립할 수 있어야 함.
  • 하지만, 옵티마이저나 관리자나 사용자의 개입 없이 항상 좋은 실행 계획을 만들어 낼 수 있는 것은 아님.
  • DBMS 서버는 이러한 문제점을 관리자나 사용자가 보완할 수 있도록 EXPLAIN 명령으로 옵티마이저가 수립한 실행 계획을 확인할 수 있게 해줌.
  • 하지만, MySQL 서버에서 보여주는 실행 계획을 읽고 이해하려면 MySQL 서버가 데이터를 처리하는 로직을 이해할 필요가 있음.
    • 처음 MySQL 서버를 접하는 사용자에게는 쉬운 일이 아님.
    • 여기서는 MySQL 서버의 실행 계획에 가장 큰 영향을 미치는 통계 정보에 대해 간략히 살펴본다.
    • MySQL 서버가 보여주는 실행 계획을 읽는 순서와 실행 계획에 출력되는 키워드, 그리고 알고리즘에 대해 살펴보자.

통계 정보

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

테이블 및 인덱스 통계 정보

• 비용 기반 최적화에서 가장 중요한 것은 통계 정보임.
  • 통계 정보가 정확하지 않다면 전혀 엉뚱한 방향으로 쿼리를 실행할 수 있음.
• MySQL 또한 다른 DBMS와 같이 비용 기반의 최적화를 사용함.
  • 그러나 다른 DBMS보다 통계 정보의 정확도가 높지 않고 통계 정보의 휘발성이 강했음.
  • 이러한 이유로 MySQL 5.6 버전부터 통계 정보의 정확성을 높일 수 있는 방법이 제공되기 시작함.
    • 그러나 아직도 많은 사용자가 기존 방식을 그대로 사용함.
  • 여기서는 MySQL 8.0 버전에서 통계 정보 관리가 어떻게 개선됐는지 함께 살펴본다.

MySQL 서버의 통계 정보

• MySQL 5.5 버전까지는 각 테이블의 통계 정보가 메모리에만 관리되고, SHOW INDEX 명령으로만 테이블의 인덱스 칼럼의 분포도를 볼 수 있었음.
  • 이처럼 통계 정보가 메모리에 관리될 경우, MySQL 서버가 재시작되면 지금까지 수집된 통계 정보가 모두 사라짐.
• MySQl 5.6 버전부터 InnoDB 스토리지 엔진을 사용하는 테이블에 대해 통계 정보를 영구적으로 (Persistent) 관리할 수 있게 개선됨.
  • 각 테이블의 통계 정보를 mysql 데이터베이스의 innodb_index_stats 테이블과 innodb_table_stats 테이블로 관리할 수 있게 개선됨.
  • 이렇게 통계 정보를 관리함으로써 MySQL 서버가 재시작돼도 기존의 통계 정보를 유지할 수 있게 됨.
• MySQL 5.6 에서 테이블 생성할 때 STATS_PERSISTENT 옵션을 설정할 수 있음.
  • 설정값에 따라 테이블 단위로 영구적인 통계 정보를 보관할지 말지를 결정할 수 있음.
• innodb_stats_persistent 시스템 설정 변수는 기본적으로 ON(1)로 설정되어 있음.
  • STATS_PERSISTENT 옵션 없이 테이블을 생성하면 영구적인 통계 정보를 사용하면서 innodb_index_stats 테이블과 innodb_table_stats 통계 정보를 저장한다.
• MySQL 5.5 버전까지는 테이블의 통계 정보가 메모리에만 저장됨.
  • MySQL 서버가 재시작되면 통계 정보가 초기화됨.
  • 그래서 MySQL 서버가 시작되면 모든 테이블의 통계 정보는 다시 수집돼야 헀음.
  • 그리고, 사용자나 관리자가 알지 못하는 순간, 다음과 같은 이벤트가 발생하면 자동으로 통계 정보가 갱신됨.
    • 테이블이 새로 오픈되는 경우
    • 테이블의 레코드가 대량으로 변경되는 경우
    • ANALYZE TABLE 명령이 실행되는 경우
    • SHOW TABLE STATUS 명령이나 SHOW INDEX FROM 명령이 실행되는 경우
    • InnoDB 모니터가 활성화되는 경우
    • innodb_stats_on_metadata 시스템 설정이 ON인 상태에서 SHOW TABLE STATUS 명령이 실행되는 경우
      • 이렇게 자주 테이블의 통계 정보가 갱신되면 응용 프로그램의 쿼리를 인덱스 레인지 스캔으로 잘 처리하던 MySQL 서버가 어느 날 갑자기 풀 테이블 스캔으로 실행되는 상황이 발생할 수 있음.
      • 그러나, 영구적인 통계 정보가 도입되면서 이렇게 의도하지 않은 통계 정보 변경을 막을 수 있게 됨.
      • 또, innodb_stats_auto_recalc 시스템 설정 변수를 OFF 로 설정하여 통계 정보가 자동으로 갱신되는 것을 막을 수 있음.
        • 기본 값이 ON 이므로 영구적인 통계 정보를 이용하고자 한다면 OFF 로 설정하자.
• MySQL 5.5 버전에서는 테이블의 통계 정보 수집할 때 몇 개의 InnoDB 테이블 블록을 샘플링할지 결정하는 옵션으로 innodb_stats_sample_pages 시스템 설정 변수가 제공됨.
  • MySQL 5.6 버전부터 이 옵션이 없어짐 (Deprecated)
  • 대신 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개 페이지만 샘플링해서 분석하고 그 결과를 영구적인 통계 정보 테이블에 저장하고 활용함.
  • p395~397에 예제가 잘 설명되어 있음.
• 영구적인 통계 정보를 사용한다면 MySQL 서버의 점검이나 사용량이 많지 않은 시간을 이용해 더 정확한 통계 정보를 수집할 수도 있음.
  • 통계 정보 수집에 많은 시간이 소요되겠지만, 이 통계 정보의 정확성에 의해 쿼리의 성능이 결정되기에 시간을 투자할 가치가 있음.

히스토그램

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

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

• 8.0 버전에서 히스토그램 정보는 칼럼 단위로 관리됨.
  • 자동으로 수집되지 않고, ANALYZE TABLE … UPDATE HISTOGRAM 명령을 실행해 수동으로 수집 및 관리됨.
  • 수집된 히스토그램 정보는 시스템 딕셔너리에 함께 저장됨.
  • MySQL 서버가 시작될 때 딕셔너리의 히스토그램 정보를 information_schema 데이터베이스의 column_statistics 테이블로 로드함.
• 8.0 버전에서는 다음과 같이 2종류의 히스토그램 타입이 지원됨.
  • Singleton(싱글톤 히스토그램) : 칼럼값 개별로 레코드 건수를 관리하는 히스토그램, Value-Based 히스토그램, 또는 도수 분포라고도 불림.
  • Equi-Height(높이 균형 히스토그램) : 칼럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램, Height-Balanced 히스토그램이라고도 불림.
• 히스토그램은 버킷(Bucket) 단위로 구분되어 레코드 건수나 칼럼값의 범위가 관리됨.
  • 싱글톤 히스토그램은 칼럼이 가지는 값별로 버킷이 할당됨.
    • 각 버킷이 칼럼의 값과 발생 빈도의 비율, 2개 값을 가짐
  • 높이 균형 히스토그램에서는 개수가 균등한 칼럼값의 범위별로 하나의 버킷이 할당됨.
    • 각 버킷이 범위 시작 값과 마지막 값, 그리고 발생 빈도율과 각 버킷에 포함된 유니크한 값의 개수 등 4개의 값을 가짐.
  • p401~402에 대한 예외가 잘 설명되어 있음.
• 생성된 히스토그램을 삭제할 수 있음.
  • 히스토그램 삭제 작업은 테이블의 데이터를 참조하는 것이 아니라, 딕셔너리의 내용만 삭제하기 때문에 다른 쿼리 처리의 성능에 영향을 주지 않고 즉시 완료됨.
  • 하지만 히스토그램이 사라지면 쿼리의 실행 계획이 달라질 수 있으니 주의해야 함.
• 히스토그램을 삭제하지 않고 MySQL 옵티마이저가 히스토그램을 사용하지 않게 optimizer_switch 시스템 변수 값을 변경해도 됨.

히스토그램의 용도

• MySQL 서버에 히스토그램이 도입되기 이전에도 테이블과 인덱스에 대한 통계 정보는 존재했음.
  • 하지만 기존 MySQL 서버가 가지고 있던 통계 정보는 테이블의 전체 레코드 건수와 인덱스된 칼럼이 가지는 유니크한 값의 개수 정도였음.
• 실제 응용 프로그램의 데이터는 항상 균등한 분포도를 가지지 않음.
  • MySQL 서버의 기존 통계 정보는 이런 부분을 고려하지 못함.
  • 이 단점을 보완하기 위해 히스토그램이 도입됨.
  • 히스토그램은 특정 칼럼이 가지는 모든 값에 대한 분포도 정보를 가지지는 않지만, 각 범위(버킷)별로 레코드의 건수와 유니크한 값의 개수 정보를 가지기 때문에 훨씬 정확한 예측을 할 수 있음.
  • p404~405에 예시가 잘 설명되어 있음.
• 히스토그램 정보가 없으면 옵티마이저는 데이터가 균등하게 분포돼 있을 것으로 예측함.
  • 하지만, 히스토그램이 있으면 특정 범위의 데이터가 많고 적음을 식별할 수 있음.

히스토그램과 인덱스

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

코스트 모델(Cost Model)

• MySQL 서버가 쿼리 처리할 때, 다음의 다양한 작업을 필요로 함.
  • 디스크로부터 데이터 페이지 읽기
  • 메모리(InnoDB 버퍼풀)로부터 데이터 페이지 읽기
  • 인덱스 키 비교
  • 레코드 평가
  • 메모리 임시 테이블 작업
  • 디스크 임시 테이블 작업
• MySQL 서버는 사용자의 쿼리에 대해 다양한 작업이 얼마나 필요한지 예측하고 전체 작업 비용을 계산한 결과를 바탕으로 최적의 실행 계획을 찾는다.
  • 이렇게 쿼리 전체 비용을 계산하는데 필요한 단위 작업들의 비용을 코스트 모델(Cost Model) 이라고 함.
  • MySQL 5.7 이전 버전까지는 이런 작업들의 비용을 MySQL 서버 소스 코드에 상수화해서 사용함.
    • 이 작업의 비용은 MySQL 서버가 사용하는 하드웨어에 따라 달라질 수 있기에 고정된 비용을 일률적으로 적용하는 것은 최적의 실행 계획 수립이 힘들어짐.
  • MySQL 5.7 버전부터 상수화된 단위 작업 비용을 DBMS 관리자가 조정할 수 있게 개선됨.
    • 하지만 MySQL 5.7 버전에서는 인덱스 되지 않은 칼럼의 데이터 분포(히스토그램)나 메모리에 상주 중인 페이지의 비율 등, 비용 계산과 연관된 부분의 정보가 부족한 상태였음.
  • MySQL 8.0 버전에서야 비로소 칼럼의 데이터 분포를 위한 히스토그램과 각 인덱스별 메모리에 적재된 페이지 비율이 관리되고 옵티마이저의 실행 계획 수립에 사용되기 시작함.
• MySQL 8.0 서버의 코스트 모델은 다음 2개 테이블에 저장돼 있는 설정 값을 사용함.
• 두 테이블 모두 mysql DB에 존재함.
  • server_cost : 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
  • engine_cost : 레코드를 가진 데이터 페이지를 가져오는 데 필요한 비용 관리
  • 두 테이블은 공통으로 다음 5개 칼럼을 가지고 있음.
    • cost_name : 코스트 모델의 각 단위 작업
    • default_value : 각 단위 작업 비용(기본 값임. 이 값은 MySQL 서버 소스 코드에 설정된 값)
    • cost_value : DBMS 관리자가 설정한 값(이 값이 NULL 이면 MySQL 서버는 default_value 칼럼의 비용을 사용)
    • last_updated : 단위 작업의 비용이 변경된 시점
    • comment : 비용에 대한 추가 설명
  • engine_cost 테이블에는 아래 2개 칼럼을 더 가짐
    • engine_name : 비용이 적용된 스토리지 엔진
      • engine_name 칼럼은 스토리지 엔진별로 각 단위 작업의 비용 설정할 수 있음.
    • device_type : 디스크 타입
      • 8.0 에서는 아직 이 칼럼 값을 활용하지 않음.
• cost_name = row_evaluate_cost 는 스토리지 엔진이 반환한 레코드가 쿼리의 조건에 일치하는지를 평가하는 단위 작업을 의미함.
  • 이 값이 증가할 수록 풀 테이블 스캔과 같이 많은 레코드를 처리하는 쿼리 비용이 높아짐.
  • 반대로, 레인지 스캔과 같이 상대적으로 적은 수의 레코들르 처리하는 쿼리의 비용이 낮아짐.
• cost_name = key_compare_cost 는 키 값의 비교 작업에 필요한 비용을 의미함.
  • 값이 증가할수록 레코드 정렬과 같이 키 값 비교 처리가 많은 경우, 쿼리 비용이 높아짐.
• 코스트 모델에서 중요한 것은 각 단위 작업에 설정되는 비용 값이 커지면 어떤 실행 계획들이 고비용으로 바뀌고 어떤 실행 계획들이 저비용으로 바뀌는지를 파악하는 것임.
  • p411에 예시가 잘 설명되어 있음. (단위 작업의 비용 조절 연습의 기준이 될 수 있음.)

각 단위 작업의 비용을 사용자가 변경할 수 있더라도 코스트 모델에 대한 전문 적인 지식을 가지고 있지 않다면 서비스에 사용되는 MySQL 서버의 engine_cost 테이블과 server_cost 테이블의 기본 값은 함부로 변경하지 않는게 좋음. → 코스트 모델은 MySQL 서버가 사용하는 하드웨어와 MySQL 서버 내부적인 처리 방식에 대한 깊이 있는 지식이 필요하기 때문임.