OPTIMIZE TABLE 을 돌리면 다음이 나온다.
+---------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------------+----------+----------+-------------------------------------------------------------------+
| hue.desktop_document2 | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| hue.desktop_document2 | optimize | status | OK |
+---------------------+----------+----------+-------------------------------------------------------------------+
Msg_type 이 note 이고 뒤이어 status OK 가 나온다. 작업은 끝났고, 다만 InnoDB 가 OPTIMIZE 를 직접 구현하지 않아 테이블 재생성 + 통계 갱신으로 대신 처리했다는 안내다. 따로 할 일이 없다.
MyISAM 은 OPTIMIZE 를 직접 지원하고, InnoDB 는 이 우회 경로를 쓴다. 테이블 엔진을 확인하면 어느 쪽인지 알 수 있다.
SELECT table_name, engine, data_free
FROM information_schema.tables
WHERE table_schema = 'hue';
OPTIMIZE TABLE 은 InnoDB 에서 다음과 같이 풀린다.
ALTER TABLE hue.desktop_document2 FORCE;
ANALYZE TABLE hue.desktop_document2;
ALTER TABLE ... FORCE 는 테이블을 새로 만들어 데이터를 옮긴다. 이 과정에서 삭제·갱신으로 생긴 페이지 안의 빈 공간이 정리되고 인덱스가 다시 쌓인다. ANALYZE 는 옵티마이저가 쓰는 통계를 갱신한다.
직접 위 두 문장을 실행해도 결과는 같다. OPTIMIZE TABLE 을 쓰면 되므로 굳이 나눠 쓸 이유는 없다.
MySQL 5.6 / MariaDB 10.0 이후의 InnoDB 에서 이 재생성은 온라인 DDL 로 처리돼 작업 중에도 읽기와 쓰기가 가능하다. 다만 다음을 알고 시작한다.
명시적으로 온라인 여부를 지정하고 싶으면 ALTER 를 직접 쓴다.
ALTER TABLE hue.desktop_document2 FORCE, ALGORITHM=INPLACE, LOCK=NONE;
LOCK=NONE 으로 불가능한 상황이면 문장이 실패한다. 조용히 잠금이 걸리는 것보다 낫다.
OPTIMIZE TABLE 은 공짜가 아니다. 다음일 때만 의미가 있다.
DELETE 로 행을 크게 줄인 직후. data_free 가 크게 남아 있다.innodb_file_per_table=ON)를 써서 실제로 디스크가 회수되는 경우. 공유 테이블스페이스면 파일 크기가 줄지 않는다.정기적으로 전 테이블에 돌리는 운영은 권하지 않는다. 얻는 것보다 부하가 크다.
크기와 여유 공간을 먼저 본다.
SELECT table_name,
ROUND((data_length + index_length) / 1024 / 1024) AS size_mb,
ROUND(data_free / 1024 / 1024) AS free_mb
FROM information_schema.tables
WHERE table_schema = 'hue'
ORDER BY data_free DESC;
"새 테이블을 만들어 데이터를 붓고 이름을 바꾸면 되지 않느냐" 는 방법도 쓴다. 이때 두 테이블의 이름을 한 문장으로 바꾸는 것이 중요하다.
CREATE TABLE hue.desktop_document2_new LIKE hue.desktop_document2;
INSERT INTO hue.desktop_document2_new SELECT * FROM hue.desktop_document2;
RENAME TABLE hue.desktop_document2 TO hue.desktop_document2_old,
hue.desktop_document2_new TO hue.desktop_document2;
RENAME TABLE 하나로 두 개를 함께 바꾸면 그 사이에 테이블이 없는 순간이 생기지 않는다. 두 문장으로 나누면 그 틈에 들어온 질의가 실패한다.
이 방법의 약점은 INSERT ... SELECT 가 도는 동안 원본에 들어온 변경이 새 테이블에 반영되지 않는다는 점이다. 무중단으로 해야 하면 pt-online-schema-change 나 gh-ost 같은 도구를 쓴다. 그런 도구가 트리거나 바이너리 로그로 그 틈을 메운다.
확인이 끝나면 옛 테이블을 지운다.
DROP TABLE hue.desktop_document2_old;
innodb_file_per_table 을 포함한 설정.