[MySQL] OPTIMIZE TABLE 완벽 가이드 사용법(테이블 최적화)

MySQL OPTIMIZE TABLE은 테이블의 성능을 향상시키고 디스크 공간을 효율적으로 사용하기 위해 제공되는 강력한 명령어입니다. 이 명령어는 테이블의 구조를 재구성하거나, 데이터 파일과 인덱스를 정리하여 데이터베이스의 효율성을 극대화할 수 있습니다. 이 글에서는 OPTIMIZE TABLE 명령어의 사용법과 다양한 예제를 중심으로 상세히 설명하겠습니다.

OPTIMIZE TABLE이란?

OPTIMIZE TABLE은 MySQL에서 테이블의 성능을 최적화하기 위해 사용되는 명령어입니다. 주로 삭제 또는 업데이트 작업으로 인해 발생하는 디스크 공간 단편화를 줄이고, 인덱스를 정리하여 데이터 검색 속도를 향상시킵니다.

OPTIMIZE TABLE의 주요 기능

1. 디스크 공간 재사용: 불필요하게 차지된 공간을 회수합니다.
2. 인덱스 최적화: 인덱스를 다시 빌드하여 검색 성능을 향상시킵니다.
3. 테이블 구조 재구성: 데이터 파일을 재정렬하여 성능을 개선합니다.

기본 문법

OPTIMIZE TABLE table_name;

• table_name: 최적화할 테이블의 이름을 지정합니다.

반응형

OPTIMIZE TABLE 사용 시점

• 테이블에서 DELETE, INSERT, UPDATE 작업이 빈번하게 이루어진 경우.
• 데이터 파일 크기가 지나치게 커진 경우.
• 테이블 성능이 저하되었을 때.

OPTIMIZE TABLE 지원 스토리지 엔진

• InnoDB: 테이블이 리빌드되며 공간이 회수됩니다.
• MyISAM: 테이블을 잠근 상태에서 데이터와 인덱스를 최적화합니다.
• ARCHIVE: 삭제된 레코드 공간을 회수합니다.

OPTIMIZE TABLE 사용 예제

기본 사용

OPTIMIZE TABLE employees;

위 명령어는 employees 테이블의 성능을 최적화합니다.

여러 테이블 최적화

OPTIMIZE TABLE employees, departments;

한 번의 명령으로 여러 테이블을 동시에 최적화할 수 있습니다.

데이터베이스 내 모든 테이블 최적화

SELECT CONCAT('OPTIMIZE TABLE ', table_name, ';') AS query
FROM information_schema.tables
WHERE table_schema = 'my_database';

위 쿼리는 my_database의 모든 테이블에 대해 OPTIMIZE TABLE을 실행할 명령어를 생성합니다.

반응형

OPTIMIZE TABLE의 결과 출력

OPTIMIZE TABLE employees;

출력:

 

Table	Op	Msg_type	Msg_text
mydb.employees	optimize	status	OK

• Table: 최적화된 테이블 이름.
• Op: 수행한 작업(여기서는 optimize).
• Msg_type: 메시지 유형(에러, 경고, 상태 등).
• Msg_text: 작업 결과 메시지.

OPTIMIZE TABLE의 동작 방식

1. InnoDB 테이블: 테이블 데이터를 새롭게 정렬하며, 사용되지 않는 공간을 제거합니다. 내부적으로 ALTER TABLE ... FORCE와 동일하게 동작합니다.
2. MyISAM 테이블: 테이블을 잠그고, 데이터 파일과 인덱스를 정리합니다.

고급 사용 사례

정기적인 최적화 작업

정기적으로 최적화 명령을 실행하여 데이터베이스 성능 저하를 방지할 수 있습니다.

OPTIMIZE TABLE employees, projects;

DELETE 작업 후 최적화

대량의 데이터를 삭제한 후에는 공간 단편화를 줄이기 위해 최적화를 수행해야 합니다.

DELETE FROM employees WHERE department = 'Sales';
OPTIMIZE TABLE employees;

대규모 데이터베이스 최적화 자동화

아래 스크립트를 사용하여 데이터베이스 내 모든 테이블을 최적화할 수 있습니다.

DELIMITER //
CREATE PROCEDURE optimize_all_tables()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE tbl_name VARCHAR(255);
  DECLARE tbl_cursor CURSOR FOR 
    SELECT table_name 
    FROM information_schema.tables 
    WHERE table_schema = DATABASE();
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

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

반응형

OPTIMIZE TABLE과 다른 명령어 비교

• ANALYZE TABLE: 테이블의 통계를 업데이트하고 인덱스 분포를 분석합니다.
• CHECK TABLE: 테이블의 무결성을 검사합니다.
• REPAIR TABLE: MyISAM 테이블에서 손상된 데이터를 복구합니다.

OPTIMIZE TABLE 사용 시 주의사항

1. InnoDB의 테이블 잠금: InnoDB 테이블은 최적화 작업 중에 잠길 수 있어, 대량 데이터가 있는 경우 성능에 영향을 줄 수 있습니다.
2. 작업 시간: 테이블 크기가 클수록 최적화 작업에 시간이 더 걸릴 수 있습니다.
3. 백업 권장: 최적화 작업 전에 데이터베이스를 백업하는 것이 안전합니다.

공식 가이드문서

MySQL 공식 문서 : https://dev.mysql.com/doc/refman/8.0/en/