테이블 및 인덱스 통계 정보

Created
March 31, 2024
Created by
D
DaEun Kim
Tags
Mysql
Property

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
  • ANALYZE TABLE <table_name> 명령 실행

통계 정보를 자동으로 재계산할 지 여부는 테이블을 생성/변경할 때 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_pages
    • innodb_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)