해당 글은 제가 직접 DB 최적화 작업을 진행하면서 직접 경험한 내용을 바탕으로 공부하고 알게된 지식을 간단하게 정리하는 글입니다.
MySQL 옵티마이저에 대한 내용은 이 글을 확인해주세요.
통계 정보
통계 정보는 MySQL 옵티마이저가 데이터 접근 비용을 정확하게 평가하고 최적의 실행 계획을 선택하는 데 도움을 주는 역할을 합니다.
테이블 통계 정보
mysql.innodb_table_stats
InnoDB 테이블에 대해 테이블 레벨의 통계 정보를 가지고 있습니다.
MySQL 옵티마이저는 Row 수, 테이블의 크기 등을 참고하여 실행 계획을 세울 수 있습니다.
테이블 구조
| Column name | Description |
|---|---|
| database_name | Database name |
| table_name | Table name, partition name, or subpartition name |
| last_update | A timestamp indicating the last time that InnoDB updated this row |
| n_rows | The number of rows in the table |
| clustered_index_size | The size of the primary index, in pages |
| sum_of_other_index_sizes | The total size of other (non-primary) indexes, in pages |
mysql.innodb_index_stats
InnoDB 테이블에 대해 인덱스 레벨의 통계 정보를 가지고 있습니다.
MySQL 옵티마이저는 인덱스의 크기, 리프 페이지 수 등을 참고하여 실행 계획을 세울 수 있습니다.
테이블 구조
| Column name | Description |
|---|---|
| database_name | Database name |
| table_name | Table name, partition name, or subpartition name |
| index_name | Index name |
| last_update | A timestamp indicating the last time the row was updated |
| stat_name | The name of the statistic, whose value is reported in the stat_value column |
| stat_value | The value of the statistic that is named in stat_name column |
| sample_size | The number of pages sampled for the estimate provided in the stat_value column |
| stat_description | Description of the statistic that is named in the stat_name column |
통계 정보 갱신
통계 정보는 ANALYZE TABLE Statement를 통해 갱신할 수 있으며, 관련 옵션에 따라 자동 갱신되는 방식이 달라집니다.
| 변수 | 설명 | 기본 값 |
|---|---|---|
| innodb_stats_persistent | 통계 정보를 디스크에 유지할지 여부를 지정합니다. OFF인 경우 통계 데이터가 메모리에만 상주합니다. | ON |
| innodb_stats_auto_recalc | innodb_stats_persistent 옵션이 활성화된 경우, 통계 정보를 자동으로 갱신할지 여부를 지정합니다. | ON |
innodb_stats_persistent이 ON 일 때
- 테이블에 컬럼이 추가되거나 삭제된 경우
- 테이블에 인덱스가 추가되거나 삭제된 경우
- 테이블의 레코드가 대량으로 변경(추가, 수정, 삭제)되는 경우
일반적으로 전체 레코드의 10% 정도가 수정되었을 때 갱신되며,innodb_stats_auto_recalc옵션이 활성화 되어있을 때만 해당됩니다.
참고!
SQL 실행 계획이 변경되는 것을 방지하기 위해innodb_stats_auto_recalc옵션을OFF하고, DB 점검하는 시간에 수동으로Analyze Table명령으로 관리하는 방법으로 관리하는 곳도 많습니다.
innodb_stats_persistent이 OFF 일 때
SHOW TABLE STATUS,SHOW INDEX명령문을 실행했을 경우- 테이블이 처음 Open 된 경우 (참고 페이지)
- 마지막 통계 업데이트 이후 1/16 정도가 수정됐음이 감지되었을 경우
히스토그램
히스토그램은 MySQL 8.0에서 추가된 기능으로, 특정 테이블 컬럼에 대해서 데이터 분포 정보를 정밀하게 관리를 합니다.
MySQL 옵티마이저는 히스토그램을 참조하여 보다 더 정확한 실행 계획을 세울 수 있습니다.
참고!
MySQL 옵티마이저는 Index Dives를 통해 Index로 지정된 컬럼의 데이터 분포를 대략적으로 예측할 수 있습니다.Index가 걸려있지 않은 컬럼에 히스토그램을 생성하면 정확한 데이터 분포를 알 수 있게 되므로, MySQL 옵티마이저는 효율적으로 실행 계획을 세울 수 있습니다.
히스토그램 확인
생성된 히스토그램은 아래의 SQL 쿼리로 확인 가능합니다.
SELECT * FROM information_schema.COLUMN_STATISTICS;히스토그램의 단점
히스토그램은 데이터 분포 파악을 위해 테이블의 모든 행을 분석하기 때문에 Full Scan이 발생할 수 밖에 없습니다.
분석 과정에서 CPU 사용량 증가, 디스크 I/O 부하, 쿼리 성능 저하 등이 발생할 수 있기 때문에 사용자가 적은 시간에 수동으로 관리를 해줘야 합니다.
참고 문서
