MySQL에서 MyISAM 테이블을 복구할 때 인덱스가 크면 'repair with keycache' 방식이 사용되어 복구 속도가 매우 느려질 수 있습니다. 이를 피하려면 아래와 같은 문법을 따르는 것이 좋습니다.
기본 문법
create table yourNewTableName as (select * from yourOldTableName);
alter table yourNewTableName add index(yourColumnName);
핵심은 기존 테이블의 데이터를 새 테이블로 복사한 후, 인덱스를 나중에 추가하는 것입니다. 이렇게 하면 데이터 로딩 시점에 인덱스 유지 관리 오버헤드가 발생하지 않아 성능이 크게 향상됩니다.
실습 예제
1단계: 테이블 생성
먼저 예제용 테이블을 생성해 보겠습니다.
mysql> create table DemoTable
(
StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
StudentFirstName varchar(20),
StudentLastName varchar(20)
);
Query OK, 0 rows affected (0.24 sec)
2단계: 데이터 삽입
insert 명령어를 사용하여 테이블에 레코드를 삽입합니다.
mysql> insert into DemoTable(StudentFirstName,StudentLastName) values('John','Doe');
Query OK, 1 row affected (0.05 sec)
mysql> insert into DemoTable(StudentFirstName,StudentLastName) values('David','Miller');
Query OK, 1 row affected (0.05 sec)3단계: 데이터 확인
select 문으로 테이블의 모든 레코드를 조회합니다.
mysql> select * from DemoTable;
위 쿼리는 다음과 같은 결과를 출력합니다.
+-----------+------------------+-----------------+
| StudentId | StudentFirstName | StudentLastName |
+-----------+------------------+-----------------+
| 1 | John | Doe |
| 2 | David | Miller |
+-----------+------------------+-----------------+
2 rows in set (0.00 sec)
'Repair with keycache'를 피하는 핵심 절차
Step 1: CTAS(Create Table As Select)로 테이블 재생성
다음은 'repair with keycache'를 피하기 위한 첫 번째 쿼리입니다. 기존 테이블을 기반으로 새 테이블을 생성합니다.
mysql> create table DemoTable as (select * from DemoTable);
Query OK, 2 rows affected (0.34 sec)
Records: 2 Duplicates: 0 Warnings: 0
Step 2: ALTER TABLE로 인덱스 추가
이후 alter table 문을 사용하여 필요한 인덱스를 추가합니다.
mysql> alter table DemoTable add index(StudentFirstName);
Query OK, 2 rows affected (0.57 sec)
Records: 2 Duplicates: 0 Warnings: 0
추가 팁
- myisam_sort_buffer_size 증가: 복구 시 정렬 버퍼를 늘리면 'repair by sorting' 방식이 사용되어 속도가 빨라집니다.
- myisam_max_sort_file_size 확인: 임시 파일 크기 제한이 작으면 keycache 방식으로 전환되므로 충분히 큰 값으로 설정하세요.
- 대량 데이터 적재 시: 인덱스 없이 데이터를 먼저 적재한 후 인덱스를 생성하는 것이 일반적으로 훨씬 빠릅니다.
이처럼 데이터 복사 후 인덱스를 추가하는 순서로 작업하면, 대용량 테이블에서도 느린 keycache 복구 방식을 효과적으로 피할 수 있습니다.