일부 행을 삭제한 후 MySQL 테이블을 최적화하는 방법

MySQL에서 DELETE 문으로 행을 삭제하면 해당 공간이 즉시 운영체제로 반환되지 않고 테이블 내부에 조각화(fragmentation)된 상태로 남게 됩니다. 이렇게 되면 디스크 공간이 낭비되고 쿼리 성능도 저하될 수 있습니다. 이때 OPTIMIZE TABLE 명령어를 사용하면 테이블을 재구성하여 불필요한 공간을 회수하고 성능을 개선할 수 있습니다.

OPTIMIZE TABLE 기본 문법

optimize table yourTableName;

실습 예제로 알아보기

1단계: 테이블 생성

먼저 테스트에 사용할 테이블을 생성합니다.

mysql> create table DemoTable(
    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name varchar(100)
);
Query OK, 0 rows affected (1.38 sec)

2단계: 데이터 삽입

INSERT 명령어를 사용해 몇 개의 레코드를 추가합니다.

mysql> insert into DemoTable(Name) values('Chris');
Query OK, 1 row affected (0.27 sec)
mysql> insert into DemoTable(Name) values('Robert');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable(Name) values('Bob');
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable(Name) values('David');
Query OK, 1 row affected (0.38 sec)

3단계: 전체 데이터 조회

SELECT 문으로 테이블의 모든 레코드를 확인합니다.

mysql> select *from DemoTable;

실행 결과는 다음과 같습니다.

+----+--------+
| Id | Name   |
+----+--------+
|  1 | Chris  |
|  2 | Robert |
|  3 | Bob    |
|  4 | David  |
+----+--------+
4 rows in set (0.00 sec)

4단계: 행 삭제

이제 WHERE 절과 IN 연산자를 활용해 특정 행들을 삭제합니다.

mysql> delete from DemoTable where Id IN(1,3);
Query OK, 2 rows affected (0.29 sec)
mysql> select *from DemoTable;
+----+--------+
| Id | Name   |
+----+--------+
|  2 | Robert |
|  4 | David  |
+----+--------+
2 rows in set (0.00 sec)

2개의 행이 삭제되었지만, 삭제된 공간은 아직 실제로 회수되지 않은 상태입니다.

5단계: 테이블 최적화 실행

행 삭제 후 아래와 같이 OPTIMIZE TABLE 명령어를 실행하여 테이블을 최적화합니다.

mysql> optimize table DemoTable;

실행 결과는 다음과 같습니다.

+------------------+----------+----------+-------------------------------------------------------------------+
| Table            | Op       | Msg_type | Msg_text                                                          |
+------------------+----------+----------+-------------------------------------------------------------------+
| web.DemoTable    | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| web.DemoTable    | optimize | status   | OK                                                                |
+------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (4.32 sec)

출력 결과 해석하기

InnoDB 스토리지 엔진은 OPTIMIZE TABLE을 직접 지원하지 않기 때문에 "Table does not support optimize, doing recreate + analyze instead"라는 안내(note) 메시지가 출력됩니다. 이 경우 MySQL은 자동으로 테이블을 재생성(recreate)하고 분석(analyze)하는 방식으로 대체 처리합니다. 최종적으로 status가 OK로 표시되면 최적화가 정상적으로 완료된 것입니다.

참고 사항

• OPTIMIZE TABLE 실행 중에는 테이블에 쓰기 작업이 제한될 수 있으므로 트래픽이 적은 시간대에 실행하는 것이 좋습니다.
• 대량의 데이터를 삭제한 직후에 실행하면 디스크 공간 회수 효과가 큽니다.
• MyISAM 엔진에서는 조각화 제거와 인덱스 정렬이 함께 수행됩니다.