[MySQL] ANALYZE TABLE 완벽 가이드 사용법(테이블 분석)

MySQL ANALYZE TABLE은 데이터베이스에서 테이블의 통계를 수집하거나 업데이트하는 데 사용되는 SQL 명령어입니다. 이 명령어는 데이터베이스 관리 및 성능 최적화에서 매우 중요한 역할을 합니다. ANALYZE TABLE을 이해하고 활용하면 쿼리 최적화와 효율적인 데이터 처리가 가능합니다.

ANALYZE TABLE이란?

ANALYZE TABLE은 테이블의 인덱스 키 분포를 분석하고, 그 결과를 데이터베이스 통계로 저장하는 데 사용됩니다. 이 통계 정보는 MySQL의 쿼리 옵티마이저가 최적의 실행 계획을 결정하는 데 활용됩니다.

ANALYZE TABLE의 주요 기능

1. 인덱스 키 분포 분석: 테이블의 인덱스가 효율적으로 동작하는지 확인합니다.
2. 통계 업데이트: 쿼리 실행 시 최신 데이터 기반의 최적화가 이루어지도록 돕습니다.
3. 쿼리 성능 향상: 옵티마이저가 최적의 실행 계획을 수립하도록 지원합니다.

기본 문법

ANALYZE TABLE table_name;

• table_name: 분석하려는 테이블의 이름입니다.

반응형

ANALYZE TABLE 사용 예제

기본 사용

ANALYZE TABLE employees;

위 명령어는 employees 테이블의 인덱스 키 분포를 분석하고 통계 정보를 업데이트합니다.

여러 테이블 분석

ANALYZE TABLE employees, departments;

이 명령어는 employees와 departments 테이블을 동시에 분석합니다.

결과 확인

ANALYZE TABLE employees;

출력 예:

Table	Op	Msg_type	Msg_text
mydb.employees	analyze	status	OK

Msg_type과 Msg_text는 분석 결과에 대한 상태 정보를 제공합니다.

반응형

ANALYZE TABLE의 내부 동작

1. 테이블의 인덱스를 분석합니다.
2. 키 분포 통계를 업데이트합니다.
3. 업데이트된 통계 정보를 MySQL 옵티마이저에 저장합니다.

ANALYZE TABLE의 활용 사례

쿼리 성능 저하 해결

테이블에 많은 데이터가 추가되거나 삭제될 경우, 기존 인덱스 통계가 오래된 상태가 되어 쿼리 성능이 저하될 수 있습니다. ANALYZE TABLE을 실행하면 최신 통계로 업데이트되어 성능이 개선됩니다.

-- 대량의 데이터 추가 후
INSERT INTO employees (name, department) VALUES ('John Doe', 'HR');

-- 통계 업데이트
ANALYZE TABLE employees;

주기적인 통계 업데이트

데이터베이스 관리자는 주기적으로 ANALYZE TABLE을 실행하여 성능 문제를 사전에 방지할 수 있습니다.

-- 매주 실행
ANALYZE TABLE employees;

ANALYZE TABLE의 제한 사항

1. Locking: MySQL은 ANALYZE TABLE 실행 시 테이블에 잠금을 걸어 작업을 수행합니다.
2. InnoDB 제한: InnoDB 엔진에서는 일부 통계가 자동으로 업데이트되므로 필요에 따라 실행 여부를 판단해야 합니다.
3. 성능 영향: 데이터가 많거나 큰 테이블에서 실행 시 시간이 소요될 수 있습니다.

ANALYZE TABLE과 기타 명령어 비교

CHECK TABLE

테이블의 손상을 검사하는 명령어로, ANALYZE TABLE과는 목적이 다릅니다.

CHECK TABLE employees;

OPTIMIZE TABLE

테이블을 최적화하여 디스크 공간을 재사용하도록 합니다.

OPTIMIZE TABLE employees;

반응형

고급 예제

조건부 실행

SELECT table_name, table_rows FROM information_schema.tables 
WHERE table_schema = 'mydb';

ANALYZE TABLE employees;

위 예제는 데이터베이스 mydb의 테이블 목록과 행 수를 확인한 뒤, 특정 테이블에 대해 분석을 수행합니다.

자동화 스크립트

DELIMITER //
CREATE PROCEDURE analyze_all_tables()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(64);
    DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = 'mydb';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO tbl_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @query = CONCAT('ANALYZE TABLE ', tbl_name);
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END;
//
DELIMITER ;

이 스크립트는 데이터베이스 mydb의 모든 테이블을 자동으로 분석합니다.

공식 가이드문서

https://dev.mysql.com/doc/refman/8.0/en/