ANALYZE TABLE Statement

ANALYZE TABLE

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name
    UPDATE HISTOGRAM ON col_name [, col_name] ...
        [WITH N BUCKETS]

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name
    DROP HISTOGRAM ON col_name [, col_name] ...

- 테이블 통계 생성

- HISOGRAM 절이 없는 ANALYZE TABLE 은 키 분포 분석을 수행하고 명명된 테이블에 대한 분포를 저장한다. 

- UPDATE HISTOGRAM 절이 있는 ANALYZE TABLE은 명명된 테이블 컬럼에 대한 HISTOGRAM 통계를 생성하고

  이를 Data Dictionary 에 저장한다. 

- DROP HISTOGRAM 절이 있는 ANALYZE TABLE은 Data Dictionary에서 명명된 테이블 컬럼에 대한 HISTOGRAM 통계를 제거한다. 

- ANALYZE TABLE 을 실행하기위해 테이블에 대한 SELECT, INSERT 권한이 필요하다. 

- ANALYZE TABLE 은 InnoDB, NDB, MyISAM 테이블에서 작동함, 뷰에서는 작동안함 

- innodb_read_only 시스템 변수가 설정되어 있는 경우 ANALYZE TABLE 시 Data Dictionary, 사용 통계 테이블을 업데이트 할수 없기 때문에 실패할 수 있다. 

- 업데이트된 분산 통계를 검색 하려면 information_schema_stats_expiry=0 를 설정한다.

- ANALYZE TABLE 은 파티션 테이블을 지원하며 , ALTER TABLE ... ANALYZE PARTITION을 사용하여 하나 이상의 파티션을 분석할 수 있다. 

- 분석중 InnoDB, MySAM 테이블은 읽기 잠금이 발생한다. 

- ANALYZE TABLE 은 플러시 잠금이 필요한 경우 테이블 정의 캐시에서 테이블을 제거한다. 

- 장시간 실행되는 쿼리, 트랜잭션이 아직 테이블을 사용하는 경우 후속 쿼리, 트랜잭션은 플러시 잠금이 해제되기전에 해당 작업이 완료될때까지 기다려야 한다. 

- ANALYZE TABLE 은 일반적으로 빠르게 완료되기 때문에 지연된 트랜잭션이나 동일한 테이블을 포함하는 명령문이 플러시 잠금으로 인한것인지 알 수 없다. 

- ANALYZE TABLE은 복제본에도 복제되게 바이너리 로그에 기록된다. (기본설정)

 

키 분포 분석

- 이전 키 분산 분석 이후 테이블이 변경되지 않은 경우, 테이블은 다시 분석되지 않는다.

- 저장된 키 배포 카디널리티를 확인하면 SWHO INDEX, INFORMATION_SCHEMA_STATISTICS 테이블 확인

- InnoDB 테이블의 경우 ANALYZE TABLE은 각 인덱스 트리에서 랜덤 검색을 수행하고 이에 따라 인덱스 카디널리티 추정치를 업데이트한다. 이는 추정치로 반복적으로 ANALYZE TABLE을 수행하면 다른 수치의 카디널리티가 생성될 수 있다. 이는 InnoDB 테이블에서 ANALYZE TABLE을 빠르게 실행하기 위해서 모든 행을 고려하지 않는것으로 인해 카디널리티가 100% 정확할 수 없다. 

- innodb_stats_persistent 가 활성화된 경우 통계를 주기적으로 수집하지 않기 때문에 서버 재부팅 또는 인덱스 컬럼에 대한 큰 변경후에 ANALYZE TABLE을 수행하는것이 좋다. 

- innodb_stats_persistent이 활성화된 경우 innodb_stats_persistent_sample_pages 시스템 변수를 변경하여 ANALYZE TABLE시 랜덤 검색수를 변경할 수 있다. innodb_stats_persistent가 비활성화된 경우 innodb_stats_transient_sample_pages 시스템 변수를 사용한다. 

 

HISTOGRAM 통계 분석 

- ANALYZE TABLE 문에서 HISTOGRAM 절을 사용하면 테이블 컬럼에 대한 HISTOGRAM 통계를 관리할 수 있다.

- ANALYZE TABLE 문에서 UPDATE HISTOGRAM 절을 사용하면 지정된 테이블 컬럼의 HISTOGRAM 통계가 생성된 Data Dictionary에 저장된다. 

- ANALYZE TABLE 문에서 DROP HISTOGRAM 절을 사용하면 지정된 테이블 컬럼의 HISTOGRAM 통계가 Data Dictionary 에서 삭제된다. 

- 저장된 히스토그램 관리문은 명명된 컬럼에만 영향을 준다. 

ANALYZE TABLE t UPDATE HISTOGRAM ON c1, c2, c3 WITH 10 BUCKETS;
ANALYZE TABLE t UPDATE HISTOGRAM ON c1, c3 WITH 10 BUCKETS;
ANALYZE TABLE t DROP HISTOGRAM ON c2;

- 암호화된 테이블, TEMPORARY 테이블은 HISTOGRAM 생성이 지원되지 않는다. 

- HISTOGRAM 생성은 지오메트리 유형(공간 데이터), JSON을 제외한 모든 데이터 유형의 컬럼에 적용된다. 

- HISTOGRAM은 저장된 컬럼 및 가상 컬럼에 대해 생성할 수 있다.

- 단일 컬럼 유니크 인덱스 컬럼에 대해서는 HISTOGRAM을 생성할 수 없다.

- HISTOGRAM 관리문은 요청된 작업을 가능한 많이 수행하고 나머지에 대한 진단 메시지를 보고한다. 

  예를 들어 UPDATE HISTOGRAM 명령문에 여러 컬럼을 지정했지만 일부 컬럼이 없거나 지원되지 않는 데이터 유형이 

  있는 경우 다른 컬럼에 대해 HISTOGRAM 이 생성되고 유효하지 않은 열에 대해 메시지가 생성된다.

- HISTOGRAM은 다음 DDL문에 영향을 받는다.

  - DROP TABLE 에서 삭제된 테이블 컬럼의 HISTOGRAM을 제거한다.

  - DROP DATABASE 문이 데이터베이스의 모든 테이블을 삭제하기 때문에 삭제된 데이터베이스의 모든 테이블에

    대한 HISTOGRAM을 제거한다. 

  - RENAME TABLE 은 HISTOGRAM을 제거하지 않는다. 

    새 테이블 이름과 연결되도록 이름이 바뀐 테이블의 HISTOGRAM 이름을 바꾼다. 

  - ALTER TABLE 문에서 컬럼을 삭제, 수정할경우 해당 컬럼에 대한 HISTOGRAM을 제거한다. 

  - ALTER TABLE ... CONVERT TO CHARACTER SET 은 문자 집합 변경의 영향을 받기 때문에

    문자열에 대한 HISTOGRAM을 제거한다. 비 문저열에 대한 HISTOGRAM은 영향을 받지 않는다. 

- histogram_generation_max_mem_size 시스템 변수는 HISTOGRAM 생성에 사용할 수 있는 최대 메모리 크기를 제어한다. 

- HISTOGRAM 생성을 위해 메모리로 읽을 예상 데이터량이 histogram_generation_max_mem_size 에서

  정의한 제한을 초과하면 MySQL은 모든 데이터를 메모리로 읽는 대신 샘플링을 사용한다. 

  샘플링은 전체 테이블에 균등하게 분산된다. MySQL은 페이지 수준 샘플링 메서드인 SYSTEM 샘플링을 사용합니다.

- INFORMATION_SCHEMA.COLUMN_STATISTICS 테이블의 sampling-rate 컬럼을 조회하여 HISTOGRAM을 생성하는 샘플링된 데이터의 비율값을 확인할 수 있다.

 

예제) 데이터 량이 histogram_generation_max_mem_size 제한을 초과하도록 하기 위해 employees 테이블의 birth_date컬럼의 히스토그램 통계를 생성하기 전에 제한은 낮은 값 (2000000 바이트)로 설정한다. 

mysql> SET histogram_generation_max_mem_size = 2000000;

mysql> USE employees;

mysql> ANALYZE TABLE employees UPDATE HISTOGRAM ON birth_date WITH 16 BUCKETS\G
*************************** 1. row ***************************
   Table: employees.employees
      Op: histogram
Msg_type: status
Msg_text: Histogram statistics created for column 'birth_date'.

mysql> SELECT HISTOGRAM->>'$."sampling-rate"'
       FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
       WHERE TABLE_NAME = "employees"
       AND COLUMN_NAME = "birth_date";
+---------------------------------+
| HISTOGRAM->>'$."sampling-rate"' |
+---------------------------------+
| 0.0491431208869665              |
+---------------------------------+

sampling-rate 값이 0.0491431208869665의 경우 birth_date 컬럼에 있는 데이터의 약 4.9 %가 HISTOGRAM 통계를 생성하기 위해 메모리에 읽혀 졌음을 의미한다. 

 

- MySQL 8.0.19 부터 InnoDB스토리지 엔진은 InnoDB 테이블에 저장되어 있는 데이터에 대한 자체 샘플링 구현을 제공한다. 스토리지 엔진이 자체 샘플링을 제공하지 않는 경우 MySQL에서 사용되는 기본 샘플링 구현에서는 Full Table Scan 이 필요하며 큰 테이블의 경우 비용이 많이 발생한다. InnoDB 샘플링의 구현은 Full Table Sacn 을 방지하여 샘플링 성능이 향상된다. 

- sampled_pages_read, sampled_pages_skipped INNODB_METRICS 카운터를 사용하여 InnoDB 데이터 페이지의 샘플링을 모니터링 할 수 있다. 

 

예제) HISTOGRAM 통계를 생성하기 전에 카운터를 활성화해야하는 샘플링 카운터 사용을 보여준다. 

mysql> SET GLOBAL innodb_monitor_enable = 'sampled%';

mysql> USE employees;

mysql> ANALYZE TABLE employees UPDATE HISTOGRAM ON birth_date WITH 16 BUCKETS\G
*************************** 1. row ***************************
   Table: employees.employees
      Op: histogram
Msg_type: status
Msg_text: Histogram statistics created for column 'birth_date'.

mysql> USE INFORMATION_SCHEMA;

mysql> SELECT NAME, COUNT FROM INNODB_METRICS WHERE NAME LIKE 'sampled%'\G
*************************** 1. row ***************************
 NAME: sampled_pages_read
COUNT: 43
*************************** 2. row ***************************
 NAME: sampled_pages_skipped
COUNT: 843

 

이 공식은 샘플링 카운터 데이터 기반으로 샘플링 속도를 추정한다.

sampling rate = sampled_page_read/(sampled_pages_read + sampled_pages_skipped)

- 샘플링 카운터 데이터를 기반으로 샘플링 속도는 INFORMATION_SCHEMA.COLUMN_STATISTICS 테이블의 sampling-rate값과 거의 동일하다.

 

- HISTOGRAM 생성을 위해 수행된 메모리 할당에 대한 정보는 성능 스키마  memory/sql/histograms 계측기를 모니터링 한다.

 

기타 고려 사항

- ANALYZE TABLE은 INFORMATION_SCHEMA.INNODB_TABLESTATS 테이블에서 테이블 통계를 지우고

  STATS_INITIALIZED 컬럼을 초기화되지 않은 상태로 만든다. 통계는 다음 테이블에 액세스 할 때 다시 수집된다. 

 

참고 

https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html