10장 | Notion

10.1 통계 정보

MySQL 8.0 부터는 히스토그램 도입
MySQL 5.7 버전에서 지원했던 테이블과 인덱스에 대한 개괄적인 정보(전체 레코드 건수, 유니크 값 개수 정도) + 데이터 칼럼의 분포값 정보 -> 더 정확한 실행계획 선택 가능

10.1.1 테이블 및 인덱스 통계 정보

비용 기반 최적화에서 가장 중요한 것은 통계 정보
통계 정보가 정확하지 않다면 전혀 엉뚱한 방향으로 쿼리를 실행할 수 있기 때문

MySQL 서버의 통계 정보

MySQL 5.6 이전	MySQL 5.6 이후
휘발성	영구성
메모리에만 관리	InnoDB 스토리지 엔진 사용하는 테이블에 대한 통계 정보를 영구적으로 관리

innodb_index_stats 테이블
innodb_table_stats 테이블 | | 서버 재시작 시 휘발 | 서버 재시작해도 기존 통계 정보 유지 | | SHOW INDEX 명령으로만 테이블의 인덱스 칼럼 분포도를 확인 | STATS_PERSISTENT 옵션 : 영구적으로 통계 정보를 보관할지 결정 |
통계 정보의 각 칼럼은 다음과 같은 값을 저장
- innodb_index_stats
  - stat_name='d_diff_pfx%' : 인덱스가 가진 유니크한 값의 개수
  - stat_name='n_leaf_pages' : 인덱스의 리프 노트 페이지 개수
  - stat_name='size' : 인덱스 트리의 전체 페이지 개수
- innodb_table_status
  - n_rows : 테이블의 전체 레코드 건수
  - clustered_index_size : 프라이머리 키의 크기 (InnoDB 페이지 개수)
  - sum_of_other_index_sizes : 프라이머리 키를 제외한 인덱스의 크기 (InnoDB 페이지 개수)

ALTER TABLE testdb.employees STATS_PERSISTENT=1;

SELECT * 
FROM mysql.innodb_index_stats
WHERE database_name = 'testdb' AND table_name = 'employees';

+---------------+------------+---------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name          | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+---------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| testdb        | employees  | PRIMARY             | 2022-10-14 21:17:31 | n_diff_pfx01 |     299335 |          20 | emp_no                            |
| testdb        | employees  | PRIMARY             | 2022-10-14 21:17:31 | n_leaf_pages |        886 |        NULL | Number of leaf pages in the index |
| testdb        | employees  | PRIMARY             | 2022-10-14 21:17:31 | size         |        929 |        NULL | Number of pages in the index      |
| testdb        | employees  | ix_firstname        | 2022-10-14 21:17:31 | n_diff_pfx01 |       1304 |          20 | first_name                        |
| testdb        | employees  | ix_firstname        | 2022-10-14 21:17:31 | n_diff_pfx02 |     329443 |          20 | first_name,emp_no                 |
| testdb        | employees  | ix_firstname        | 2022-10-14 21:17:31 | n_leaf_pages |        496 |        NULL | Number of leaf pages in the index |
| testdb        | employees  | ix_firstname        | 2022-10-14 21:17:31 | size         |        609 |        NULL | Number of pages in the index      |
| testdb        | employees  | ix_gender_birthdate | 2022-10-14 21:17:31 | n_diff_pfx01 |          1 |           3 | gender                            |
| testdb        | employees  | ix_gender_birthdate | 2022-10-14 21:17:31 | n_diff_pfx02 |       9061 |          20 | gender,birth_date                 |
| testdb        | employees  | ix_gender_birthdate | 2022-10-14 21:17:31 | n_diff_pfx03 |     309467 |          20 | gender,birth_date,emp_no          |
| testdb        | employees  | ix_gender_birthdate | 2022-10-14 21:17:31 | n_leaf_pages |        361 |        NULL | Number of leaf pages in the index |
| testdb        | employees  | ix_gender_birthdate | 2022-10-14 21:17:31 | size         |        417 |        NULL | Number of pages in the index      |
| testdb        | employees  | ix_hiredate         | 2022-10-14 21:17:31 | n_diff_pfx01 |       4468 |          20 | hire_date                         |
| testdb        | employees  | ix_hiredate         | 2022-10-14 21:17:31 | n_diff_pfx02 |     296778 |          20 | hire_date,emp_no                  |
| testdb        | employees  | ix_hiredate         | 2022-10-14 21:17:31 | n_leaf_pages |        294 |        NULL | Number of leaf pages in the index |
| testdb        | employees  | ix_hiredate         | 2022-10-14 21:17:31 | size         |        353 |        NULL | Number of pages in the index      |
+---------------+------------+---------------------+---------------------+--------------+------------+-------------+-----------------------------------+

SELECT *
FROM mysql.innodb_table_stats
WHERE database_name = 'testdb' AND table_name = 'employees';

+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| testdb        | employees  | 2022-10-14 21:17:31 | 299335 |                  929 |                     1379 |
+---------------+------------+---------------------+--------+----------------------+---------------------

통계 정보 갱신
- MySQL 5.6 이전 : 자동 갱신
  - 통계 정보가 갱신되면 쿼리 실행 계획 갑자기 달라질 수 있음
  - 통계 정보를 바탕으로 실행 계획을 수립하기 때문에 MySQL 서버가 갑자기 실행 계획을 다르게 설정(예측 불허)
- MySQL 5.6 이후 : 수동 갱신 가능 + 자동 선택 가능
  - STATS_AUTO_RECALC 옵션 : 통계 정보 자동 수집 여부 결정 가능
  - 통계 정보 수집 시, 몇 개의 페이지 샘플링 할 것인지도 설정 가능(시스템 변수에)
    - innodb_stats_transient_sample_pages : Default 8 / 페이지 수만큼만 임시로 샘플링 분석 후 통계 정보로 활용
    - innodb_stats_persistent_sample_pages : Default 20 / ANALYZE TABLE 명령 실행 시 페이지 수만큼 샘플링 분석 후 통계 정보 테이블에 저장 + 활용
    - 갯수 늘리기 -> (+) 더 정확한 통계 정보 수집 가능 -> (-) 수집 시간 길어짐

10.1.2 히스토그램

MySQL 5.7 버전까지는
- 인덱스된 컬럼의 유니크한 값 개수 + 실제 인덱스의 일부 페이지 랜덤 get
MySQL 8.0 버전부터는 히스토그램 정보 활용 가능

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

수집
- 히스토그램 정보는 컬럼 단위로 관리
- ANALYZE TABLE {table_name} UPDATE HISTOGRAM 명령으로 수동 수집
- 수집 후 시스템 딕셔너리에 저장 →(MySQL 서버 실행시) information_schema 데이터베이스, column_statistics 테이블로 로드해서 사용
- 암호화된 테이블에서는 히스토그램을 수집할 수 없다? (Cannot create histogram statistics for an encrypted table)

-- // 히스토그램 정보 수집
ANALYZE TABLE testdb.employees
UPDATE HISTOGRAM ON gender, hire_date;

+------------------+-----------+----------+------------------------------------------------------+
| Table            | Op        | Msg_type | Msg_text                                             |
+------------------+-----------+----------+------------------------------------------------------+
| testdb.employees | histogram | status   | Histogram statistics created for column 'gender'.    |
| testdb.employees | histogram | status   | Histogram statistics created for column 'hire_date'. |
+------------------+-----------+----------+------------------------------------------------------+

-- // 히스토그램 정보 조회
SELECT *
FROM information_schema.column_statistics
WHERE schema_name = 'testdb' AND table_name = 'employees' \\G

*************************** 1. row ***************************
SCHEMA_NAME: testdb
 TABLE_NAME: employees
COLUMN_NAME: gender
  HISTOGRAM: {"buckets": [[1, 0.6022134473264744], [2, 1.0]],
						 "data-type": "enum",
						 "null-values": 0.0,
						 "collation-id": 45,
						 "last-updated": "2022-10-18 14:48:18.023937",
						 "sampling-rate": 0.3464196832892688,
						 "histogram-type": "singleton",
						 "number-of-buckets-specified": 100
						}
*************************** 2. row ***************************
SCHEMA_NAME: testdb
 TABLE_NAME: employees
COLUMN_NAME: hire_date
  HISTOGRAM: {"buckets": [
                            ["1985-02-01", "1985-02-28", 0.009898927691638892, 28], 
                            ["1985-03-01", "1985-03-28", 0.020069555136936677, 28], 
                            ["1985-03-29", "1985-04-25", 0.0301405593392262, 28], 
                            ["1985-04-26", "1985-05-24", 0.04006665700623098, 29], 
                            ["1985-05-25", "1985-06-20", 0.05003803796551225, 27], 
                            ...
                            ["1998-08-07", "2000-01-28", 1.0, 467]
                         ], 
						"data-type": "date", 
						"null-values": 0.0, 
						"collation-id": 8, 
						"last-updated": "2022-10-18 14:48:18.033156", 
						"sampling-rate": 0.3464196832892688, 
						"histogram-type": "equi-height", 
						"number-of-buckets-specified": 100
						}

sampling-rate
- 히스토그램 정보를 수집하기 위해 스캔한 페이지의 비율
- 샘플링 비율 0.35 = 전체 데이터 페이지의 35%
- histogram_generation_max_mem_size 시스템 변수로 히스토그램 생성 시 샘플링 비율을 설정 가능
- MySQL 8.0.19 미만의 버전까지는 설정과 무관하게 풀 스캔
- MySQL 8.0.19 버전부터는 InnoDB 스토리지 엔진이 자체적으로 샘플링 알고리즘을 구현하여, 히스토그램 수집 시 풀 테이블 스캔이 발생하지 않는다.
histogram-type
- 히스토그램의 종류
number-of-buchkets-specified
- 히스토그램을 생성할 때 설정했던 버킷의 개수
- 별도로 설정하지 않으면 기본 100개의 버킷이 사용되며, 최대 1024개