Mysql 5.6 버전 이후로는 InnoDB 엔진을 사용하는 테이블의 통계 정보를 영구적으로 저장하는 게 가능하다.
통계 정보는
mysql 데이터베이스 내 아래 2가지 통계 테이블에서 관리한다.mysql> use mysql; Database changed mysql> show tables like '%_stats'; +---------------------------+ | Tables_in_mysql (%_stats) | +---------------------------+ | innodb_index_stats | | innodb_table_stats | +---------------------------+ 2 rows in set (0.02 sec)
통계 정보를 영구 저장할 지 여부 설정
통계 정보를 영구 저장할 지 여부는
innodb_stats_persistent 시스템 변수로 설정할 수 있고, 디폴트로 ON 으로 되어 있다.mysql> show variables like 'innodb_stats_per%'; +--------------------------------------+-------+ | Variable_name | Value | +--------------------------------------+-------+ | innodb_stats_persistent | ON | | innodb_stats_persistent_sample_pages | 20 | +--------------------------------------+-------+ 2 rows in set (0.02 sec)
통계 정보를 영구 저장할 지 여부는 테이블을 생성/변경할 때
STAT_PERSISTENT 절을 사용하여 테이블 단위로도 설정 할 수 있다.CREATE TABLE tab (...) STATS_PERSISTENT = {DEFAULT | 0 | 1}
DEFAULT | innodb_stats_persistent 시스템 변수 설정에 따름 |
0 | 영구 저장하지 않음 |
1 | 영구 저장함 |
통계 정보를 자동으로 재계산 할 지 여부 설정
통계 정보를 자동으로 재계산할 지 여부는
innodb_stats_auto_recalc 시스템 변수로 설정할 수 있고, 디폴트로 ON 으로 되어 있다. mysql> show variables like 'innodb_stats_auto_recalc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_stats_auto_recalc | ON | +--------------------------+-------+ 1 row in set (0.01 sec)
시스템 변수가 ON 으로 설정되어 있으면 아래와 같은 이벤트가 발생할 때 자동으로 통계 정보를 재계산한다.
CREATE TABLE또는ALTER TABLE쿼리 수행
- 테이블 내에 데이터 레코드를 대량으로 (전체 레코드 수의 10% 이상)
UPDATE또는INSERT
통계 정보를 자동으로 재계산할 지 여부는 테이블을 생성/변경할 때
STATS_AUTO_RECALC 절을 사용하여 테이블 단위로도 설정할 수 있다.CREATE TABLE tab (...) STATS_AUTO_RECALC= {DEFAULT | 0 | 1}
DEFAULT | innodb_stats_auto_recalc 시스템 변수 설정에 따름 |
0 | ANALYZE TABLE <table_name> 명령 실행 시 재계산 |
1 | 테이블 내 전체 레코드 중 10% 이상 변경이 발생하면 재계산 |
통계 정보를 수집하기 위한 샘플링에 관련된 설정
mysql> show variables like '%sample_pages'; +--------------------------------------+-------+ | Variable_name | Value | +--------------------------------------+-------+ | innodb_stats_persistent_sample_pages | 20 | | innodb_stats_transient_sample_pages | 8 | +--------------------------------------+-------+ 2 rows in set (0.01 sec)
innodb_stats_transient_sample_pages- 샘플링 대상이 되는 인덱스 페이지 갯수.
-
ANALYZE TABLE과 같은 명령을 실행하면innodb_stats_transient_sample_pages변수에 설정된 인덱스 페이지 갯수 만큼 카디널리티를 포함한 여러 통계 정보를 수집한다. - 기본값은 8이고, 값이 높을수록 실행 계획의 정확도는 높아지지만, I/O 비용도 커진다.
innodb_stats_persistent가 OFF 으로 설정되어 있어야 영향력이 있다.
innodb_stats_persistent_sample_pagesinnodb_stats_transient_sample_pages변수와 같은 역할을 한다.- 기본값은 20이고, 값이 높을수록 실행 계획의 정확도는 높아지지만, I/O 비용도 커진다.
innodb_stats_persistent가 ON 으로 설정되어 있어야 영향력이 있다.
테이블 통계 정보 조회하기
mysql> use mysql Database changed mysql> select * from innodb_table_stats where database_name = 'employees' and table_name = 'employees'; +---------------+------------+---------------------+--------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+------------+---------------------+--------+----------------------+--------------------------+ | employees | employees | 2024-03-31 14:26:11 | 299113 | 929 | 1379 | +---------------+------------+---------------------+--------+----------------------+--------------------------+ 1 row in set (0.01 sec)
인덱스 통계 정보 조회하기
각 레코드에서
stat_name 필드가 의미하는 바는 stat_description 필드를 참고하면 된다.mysql> use mysql Database changed mysql> select * from innodb_index_stats where database_name = 'employees' and table_name = 'employees'; +---------------+------------+---------------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+---------------------+---------------------+--------------+------------+-------------+-----------------------------------+ | employees | employees | PRIMARY | 2024-03-31 14:26:11 | n_diff_pfx01 | 299113 | 20 | emp_no | | employees | employees | PRIMARY | 2024-03-31 14:26:11 | n_leaf_pages | 886 | NULL | Number of leaf pages in the index | | employees | employees | PRIMARY | 2024-03-31 14:26:11 | size | 929 | NULL | Number of pages in the index | | employees | employees | ix_firstname | 2024-03-31 14:26:11 | n_diff_pfx01 | 1497 | 20 | first_name | | employees | employees | ix_firstname | 2024-03-31 14:26:11 | n_diff_pfx02 | 287332 | 20 | first_name,emp_no | | employees | employees | ix_firstname | 2024-03-31 14:26:11 | n_leaf_pages | 496 | NULL | Number of leaf pages in the index | | employees | employees | ix_firstname | 2024-03-31 14:26:11 | size | 609 | NULL | Number of pages in the index | | employees | employees | ix_gender_birthdate | 2024-03-31 14:26:11 | n_diff_pfx01 | 1 | 3 | gender | | employees | employees | ix_gender_birthdate | 2024-03-31 14:26:11 | n_diff_pfx02 | 9747 | 20 | gender,birth_date | | employees | employees | ix_gender_birthdate | 2024-03-31 14:26:11 | n_diff_pfx03 | 301561 | 20 | gender,birth_date,emp_no | | employees | employees | ix_gender_birthdate | 2024-03-31 14:26:11 | n_leaf_pages | 361 | NULL | Number of leaf pages in the index | | employees | employees | ix_gender_birthdate | 2024-03-31 14:26:11 | size | 417 | NULL | Number of pages in the index | | employees | employees | ix_hiredate | 2024-03-31 14:26:11 | n_diff_pfx01 | 4983 | 20 | hire_date | | employees | employees | ix_hiredate | 2024-03-31 14:26:11 | n_diff_pfx02 | 276080 | 20 | hire_date,emp_no | | employees | employees | ix_hiredate | 2024-03-31 14:26:11 | n_leaf_pages | 294 | NULL | Number of leaf pages in the index | | employees | employees | ix_hiredate | 2024-03-31 14:26:11 | size | 353 | NULL | Number of pages in the index | +---------------+------------+---------------------+---------------------+--------------+------------+-------------+-----------------------------------+ 16 rows in set (0.01 sec)