[DB] OPTIMIZE TABLE

회사에서 실 DB 서버에 있는 로그성 데이터가 디스크 사용량을 많이 잡아먹어 정리를 진행함. 23년 이전 데이터에 대한 삭제처리를 진행하였는데

원본데이터 : 6.6G ==> Delete 후 : 7G

오히려 용량이 늘었다. 이 이유에 대해서 공부해본 내용을 정리해 보려고 한다.

Q1. Delete 쿼리 후에 데이터의 갯수는 줄었지만 용량이 늘어난 이유

a. 테이블에 삭제된 데이터에 대한 빈 공간이나 여유 공간을 유지하기 위해 데이터베이스 시스템이 그 공간을 해제하지 않았기 때문 이를 해결하기 위해서 해당 테이블에 대해 OPTIMIZE TABLE 명령어를 실행 > 이로써 빈 공간이나 여유 공간이 해제되고 용량이 줄게 됨.

시스템이 공간을 해제하지 않는 이유는 첫번째, 트랜잭션 롤백 기능 : 데이터베이스는 트랜잭션 롤백이나 데이터 회복을 위해 삭제된 데이터를 실제로 삭제하지 않을 수 있다. 만약 트랜잭션이 롤백되면, 삭제된 데이터를 복구할 수 있어야 하기 때문이다.

두번째, 성능 및 효율성: 실제로 디스크에서 데이터를 제거하는 것은 비용이 많이 들 수 있기 때문에 빈 공간을 즉시 디스크에서 해제하지 않고, 나중에 필요한 경우 최적화 작업을 통해 해제할 수 있다.

세번째, 인덱스 유지: 데이터베이스에서는 인덱스를 통해 빠른 검색을 지원하는데 삭제된 데이터를 실제로 제거하면 인덱스를 다시 구축해야 하기 때문에 성능 저하가 발생할 수 있다. 때문에 데이터베이스는 이를 방지하기 위해 삭제된 데이터를 빈 공간으로 남겨둔다.

즉, 이러한 이유로 데이터베이스는 성능 및 트랜잭션 복구를 고려하여 삭제된 데이터를 즉시 실제로 제거하지 않는다. 대신, 주기적으로 최적화 작업을 실행하여 빈 공간을 정리하고 성능을 최적화할 수 있으며, 이러한 최적화 작업 중 하나가 OPTIMIZE TABLE 명령어이다.

Q2. OPTIMIZE TABLE과 ALTER TABLE 차이점

둘 다 테이블 정리를 수행하는 명령어이지만, 목적과 방식에서 차이가 있다.

a. OPTIMIZE TABLE: 이 명령어는 테이블을 최적화하여 여유 공간을 제거하고, 테이블의 물리적인 구조를 최적화한다. 주로 데이터를 삭제하거나 변경한 후에 사용하여 테이블의 용량을 최적화하고 성능을 향상시킨다. 또한, 테이블의 인덱스도 최적화한다.

OPTIMIZE TABLE table_name;

b. ALTER TABLE: 이 명령어는 테이블의 구조를 변경하는 데 사용 된다. 새로운 열을 추가하거나 기존 열을 삭제하는 등의 스키마 변경에 사용된다. 테이블 구조 변경이 필요한 경우에 사용하며, ALTER TABLE 명령어를 사용하면 인덱스 재구축과 같은 작업이 수행될 수 있다.

ALTER TABLE table_name [작업];

요약하면, OPTIMIZE TABLE은 주로 테이블의 물리적인 구조 최적화와 여유 공간 제거에 중점을 두고, ALTER TABLE은 테이블 구조를 변경하기 위해 사용된다. 두 명령어는 서로 다른 목적을 가지고 있으며 선택적으로 사용되어야 함.

원본데이터 : 6.6G ==> Delete 후 : 7G ==> OPTIMIZE 후 : 1.4G