MySQL 테이블 단편화 해결 방법

테이블 스페이스

: InnoDB 스토리지 엔진 기반의 테이블의 경우 .ibd 파일과 .frm 파일을 통해 테이블의 구조와 데이터를 저장

: .ibd 파일과 .frm 파일을 테이블 스페이스 라고 하는데, 테이블의 row가 많아질 수록 테이블 스페이스가 증가

: 한 번 증가된 테이블 스페이스는 row를 모두 삭제한다고 해도 시스템에 다시 반환되지 않음

: 확장된 스페이스는 row가 다시 늘어날때 재사용

 

- .ibd

: 테이블의 데이터가 저장되는 파일

 

- .frm

: 테이블의 인덱스 및 스키마가 저장되는 파일

: mysql 서버에서 테이블이 깨졌을 때 해당 파일을 통해 복구 할 수 있음 (dbsake 활용)

[mySql] mariaDb, Mysql .frm, .ibd파일을 이용한 데이터 복구 ( docker 이용방법 포함 )

 

 

 

 

테이블 단편화

: 한 번 확장된 테이블 스페이스를 시스템에 환원하기 위해서는 .ibd 파일과 .frm 파일을 다시 생성해줘야함

: 위 과정을 위해서는 테이블을 복사하고 원본 테이블을 삭제한 뒤 복사한 테이블의 이름을 원본 테이블의 이름으로

  수정하면 같은 테이블이지만 테이블 스페이스가 다시 생성되는 효과를 가지게 됨

: 위 과정을 통해 DB 서버의 디스크 용량을 확보할 수 있음

 

- OPTIMIZE TABLE <테이블>

: InooDB 기반의 테이블에 실행할 경우 테이블 복사 > 원본 테이블 삭제 > 복사한 테이블의 이름 변경 과정을 통해

  테이블의 단편화를 해결

: MyISAM 기반의 테이블에 실행할 경우 조각모음을 통해 테이블의 단편화를 해결

: ALTER TABLE <테이블> ENGINE INNODB와 동일한 쿼리

 

-  ANAYZE TABLE <테이블>

: OPTIMIZER가 사용하는 통계정보의 갱신 쿼리

: 인덱스를 재생성하여 성능을 최적화하고 키를 재분배

: InnoDB 기반의 테이블의 경우 자동으로 통계 정보가 갱신되기 때문에 수동으로 할 필요는 없음

 > 인덱스 통계 정보 갱신 후 전체 행수의 1/16이 갱신된 경우나 

    20억행 이상이 갱신된 경우에 통계정보가 갱신됨

 

 

 

 

 

OPTIMIZE TABLE을 사용하기 위한 설정

- innodb_file_per_table 설정 여부 확인

: show global variables like 'innodb_file_per_table';

: 0으로 되어 있는 경우 설정 필요

 

- innodb_file_per_table 설정

: SET GLOBAL innodb_file_per_table = 1;

 

 

 

 

 

 

Refference

MySQL optimize/analyze table 정리

[MySQL - 테이블 단편화 optimize/analyze table 정리]

Mysql에서 innodb_file_per_table을 사용하는 이유