통계 정보

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

테이블 및 인덱스 통계 정보

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

MySQL 서버의 통계 정보

MySQL 5.5 버전까지

• 각 테이블의 통계 정보가 메모리에만 관리된다.
  • MySQL 서버가 재시작되면 지금까지 수집된 통계 정보가 모두 사라진다.
• SHOW INDEX 명령으로만 테이블 인덱스 칼럼의 분포도를 볼 수 있다.

MySQL 5.6 버전부터

• InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로(Persistent) 관리할 수 있게 개선됐다.
• 각 테이블의 통계 정보를 mysql 데이터베이스의 innodb_index_stats 테이블과 innodb_table_stats 테이블로 관리할 수 있게 개선됐다.
  • MySQL 서버가 재시작돼도 기존의 통계 정보를 유지할 수 있게 됐다.
• 테이블을 생성할 때는 STATS_PERSISTENT 옵션을 설정할 수 있다.
  • 이 설정값에 따라 테이블 단위로 영구적인 통계 정보를 보관할지 말지를 결정할 수 있다.

    CREATE TABLE tab_test (fd1 INT, fd2 VARCHAR(20), PRIMARY KEY(fd1))
    ENGINE=InnoDB
    STATS_PERSISTENT={ DEFAULT | 0 | 1 }

STATS_PERSISTENT 옵션

• 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 시스템 변수의 값으로 결정한다.
  • innodb_stats_persistent 시스템 설정 변수는 기본적으로 ON(1)로 설정돼 있다. 즉, 영구적으로 관리한다.

통계 정보 조회

• mysql 데이터베이스의 innodb_table_stats 테이블의 통계 정보를 조회하면, STATS_PERSISTENT=1 로 생성한 테이블의 통계 정보만 조회된다.
• 이렇게 생성된 테이블의 통계 정보를 ALTER TABLE 명령으로 영구적 또는 단기적으로 변경할 수 있다.

-- 통계 정보를 영구적으로 변경
ALTER TABLE employees.employees STATS_PERSISTENT=1;

-- 통계 정보 조회
SELECT *
FROM innodb_index_stats
WHERE database_name='employees'
	AND TABLE_NAME='employees';
	
	SELECT *
FROM innodb_table_stats 
WHERE database_name='employees'
	AND TABLE_NAME='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 페이지 개수)
  • STATS_AUTO_RECALC 옵션에 따라 0으로 보일 수도 있다. 이 때는 ANALYZE TABLE 명령을 실행하면 통곗값이 저장된다.

MySQL 5.5 버전과의 차이

잦은 통계 정보 갱신

• MySQL 5.5 버전까지는 테이블의 통계 정보가 메모리에만 저장되며, MySQL 서버가 재시작되면 통계 정보가 초기화됐다.
  • MySQL 서버가 시작되면 모든 테이블의 통계 정보는 다시 수집돼야 했다.
• 사용자나 관리자가 알지 못하는 순간에 다음과 같은 이벤트가 발생하면 자동으로 통계 정보가 갱신됐다.
  • 테이블이 새로 오픈되는 경우
  • 테이블의 레코드가 대량(전체 레코드 중 1/6 정도)으로 변경되는 경우
  • ANALYZE TABLE 명령이 실행되는 경우
  • SHOW TABLE STATUS 명령이나 SHOW INDEX FROM 명령이 실행되는 경우
  • InnoDB 모니터가 활성화되는 경우
  • innodb_stats_on_metadata 시스템 설정이 ON인 상태에서 SHOW TABLE STATUS 명령이 실행되는 경우
• 이렇게 자주 통계 정보가 갱신되면 응용 프로그램의 쿼리를 인덱스 레인지 스캔으로 잘 처리하던 MySQL 서버가 어느날 갑자기 풀 테이블 스캔으로 실행되는 상황이 발생할 수도 있다.

통계 정보 자동 갱신 설정

• 영구적인 통계 정보가 도입되면서 의도하지 않은 통계 정보 변경을 막을 수 있게 됐다.
• innodb_stats_auto_recalc 시스템 설정 변수의 값을 OFF로 설정해서 통계 정보가 자동으로 갱신되는 것을 막을 수 있다.
  • innodb_stats_auto_recalc 시스템 설정 변수의 기본값은 ON이므로, 영구적인 통계 정보를 이용하고자 한다면 OFF로 변경해야 한다.

통계 정보 자동 수집 여부 설정

• 테이블을 생성할 때 통계 정보를 자동으로 수집할지 여부도 STATS_AUTO_RECALC 옵션을 이용해 테이블 단위로 조정할 수 있다.
• 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되고, 다음 시스템 변수 2개로 분리됐다.
  • innodb_stats_transient_sample_pages
    • 기본값은 8이다.
    • 자동으로 통계 정보 수집이 실행될 때 8개 페이지만 임의로 샘플링해서 분석하고 그 결과를 통계 정보로 활용함을 의미한다.
  • innodb_stats_persistent_sample_pages
    • 기본값은 20이다.
    • ANALYZE TABLE 명령이 실행되면 임의로 20개 페이지만 샘플링해서 분석하고 그 결과를 영구적인 통계 정보 테이블에 저장하고 활용함을 의미한다.
• 영구적인 통계 정보를 사용한다면 MySQL 서버의 점검이나 사용량이 많지 않은 시간을 이용해 더 정확한 통계 정보를 수집할 수도 있다.
  • 더 정확한 통계 정보 수집에는 많은 시간이 소요되겠지만, 통계 정보의 정확성에 의해 쿼리의 성능이 결정되기 때문에 시간을 투자할 가치가 있다.
• 더 정확한 통계 정보를 수집하고자 한다면 innodb_stats_persistent_sample_pages 시스템 변수에 높은 값을 설정하면 된다.
  • 이 값을 너무 높이면 통계 정보 수집 시간이 길어지므로 주의해야 한다.

Reference

참고 서적

📔 Real MySQL 8.0