핵심 포인트 💡:

• 인덱스 통계를 업데이트하여 옵티마이저가 최적의 실행 계획을 선택하도록 지원
• 읽기 잠금(read lock) 발생, 큰 테이블에서 성능 영향 가능
• MySQL 8.0.24+, MariaDB 10.5.4+에서 프로덕션 환경에서도 안전하게 사용 가능
• SELECT, INSERT 권한 필요

기본 사용법

• 구문

  -- MySQL/MariaDB 공통
  ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ...
  MySQL 8.4 Reference Manual (opens in a new tab)

  위 출처에서 "ANALYZE TABLE generates table statistics" 라고 명시되어 있음.

• 옵션
  • NO_WRITE_TO_BINLOG 또는 LOCAL: 바이너리 로그에 기록하지 않음 (복제 서버로 전파되지 않음)
• 반환값 (4개 컬럼)

  컬럼	설명
  Table	테이블 이름
  Op	analyze 또는 histogram
  Msg_type	status, error, info, note, warning
  Msg_text	정보성 메시지

MySQL 히스토그램 기능

• ✅ 기본 인덱스 통계 업데이트

  ANALYZE TABLE employees;
  MySQL 8.4 Reference Manual (opens in a new tab)
• ✅ 히스토그램 생성 (MySQL 8.0+)

  -- 특정 컬럼에 10개 버킷으로 히스토그램 생성
  ANALYZE TABLE employees 
  UPDATE HISTOGRAM ON birth_date, hire_date 
  WITH 10 BUCKETS;

  위 출처에서 "UPDATE HISTOGRAM ON col_name [, col_name] ... [WITH N BUCKETS]" 구문이 명시됨.

• ✅ 히스토그램 자동 업데이트

  ANALYZE TABLE employees 
  UPDATE HISTOGRAM ON salary 
  WITH 100 BUCKETS AUTO UPDATE;
• ✅ 히스토그램 삭제

  ANALYZE TABLE employees 
  DROP HISTOGRAM ON birth_date;
• 히스토그램 제약사항
  • ❌ 암호화된 테이블
  • ❌ 임시 테이블 (TEMPORARY TABLE)
  • ❌ 기하학적 타입 (공간 데이터)
  • ❌ JSON 데이터 타입
  • ✅ 저장된 컬럼과 가상 생성 컬럼은 가능

MariaDB PERSISTENT FOR 옵션

• ✅ 모든 열과 인덱스 통계 수집

  ANALYZE TABLE orders PERSISTENT FOR ALL;
  MariaDB Documentation (opens in a new tab)

  위 출처에서 "PERSISTENT FOR ALL" 옵션으로 "update all engine-independent statistics for all columns and indexes" 한다고 명시됨.

• ✅ 특정 열과 인덱스만 선택적 수집

  ANALYZE TABLE orders PERSISTENT FOR 
    COLUMNS (customer_id, order_date) 
    INDEXES (idx_customer, idx_date);
• 빈 목록도 허용

  ANALYZE TABLE orders PERSISTENT FOR 
    COLUMNS () INDEXES ();

주의사항

• 버전별 안전성

  • ✅ MySQL 8.0.24+: 프로덕션 환경에서 안전
  • ✅ MariaDB 10.5.4+: 락킹 문제 해결됨
  • ❌ MariaDB 10.5.3 이하: 락킹 문제 발생 가능

  Percona Blog - Is ANALYZE TABLE Safe (opens in a new tab)

  위 출처에서 "Running ANALYZE TABLE should be absolutely safe and not cause any unexpected stalls as long as your database runs on the most recent version of MySQL or MariaDB variant" 라고 명시됨.

• 권한 요구사항

  • SELECT 권한 필요
  • INSERT 권한 필요 (통계 테이블 업데이트)

• 성능 영향

  • 읽기 잠금(read lock) 발생 (InnoDB, MyISAM)
  • 큰 테이블에서 상당한 성능 영향 가능
  • 통계는 시간에 따라 크게 변하지 않으므로 자주 실행할 필요 없음

  MariaDB Documentation (opens in a new tab)

  위 출처에서 "큰 테이블의 경우 상당한 성능 영향과 디스크 사용량 증가 가능" 하다고 명시됨.

• 적용 가능 스토리지 엔진

  • ✅ InnoDB, NDB, MyISAM, Aria
  • ❌ Views (뷰는 불가)

• 메모리 제어 (MySQL)

  -- 히스토그램 생성 시 메모리 제한 (기본값: 20MB)
  SET histogram_generation_max_mem_size = 2000000;
  • 초과 시 샘플링 방식으로 처리

• innodb_read_only 모드 주의

  • 읽기 전용 모드에서 통계 테이블 업데이트 실패 가능
  • 해결: SET information_schema_stats_expiry=0;

사용 시나리오

• ✅ JOIN 쿼리 최적화가 필요할 때
• ✅ ORDER BY...LIMIT 절이 있는 쿼리 성능 개선
• ✅ 데이터가 자주 변경되는 테이블
• ✅ 인덱스 카디널리티가 부정확할 때
• ❌ 통계가 최근에 업데이트된 경우 (불필요)