Tránh "repair with keycache" khi tạo index lớn trong MySQL

Thông báo Repair with keycache xuất hiện khi MySQL không đủ bộ nhớ (myisam_sort_buffer_size) để sắp xếp index trong bộ nhớ, buộc phải dùng file tạm trên đĩa (rất chậm). Để tránh, hãy dùng kỹ thuật: Tạo bảng mới từ bảng cũ (CREATE TABLE ... AS SELECT) rồi thêm index. Cách này cho phép MySQL dùng thuật toán sắp xếp hiệu quả hơn (Fast Index Creation).

Tạo bảng gốc

mysql> CREATE TABLE DemoTable (
    StudentId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    StudentFirstName VARCHAR(20),
    StudentLastName VARCHAR(20)
);

mysql> INSERT INTO DemoTable (StudentFirstName, StudentLastName) VALUES
('John', 'Doe'), ('David', 'Miller');

Quy trình tránh "repair with keycache"

Bước 1: Tạo bảng mới từ dữ liệu bảng cũ (không copy index)

mysql> CREATE TABLE DemoTable_New AS SELECT * FROM DemoTable;
Query OK, 2 rows affected (0.34 sec)

Bước 2: Thêm index vào bảng mới

mysql> ALTER TABLE DemoTable_New ADD INDEX (StudentFirstName);
Query OK, 2 rows affected (0.57 sec)

Bước 3: Đổi tên bảng (atomic rename)

mysql> RENAME TABLE DemoTable TO DemoTable_Old, DemoTable_New TO DemoTable;

Tại sao cách này hiệu quả?

• CREATE TABLE ... AS SELECT tạo bảng mới chỉ với dữ liệu, không có index.
• ALTER TABLE ... ADD INDEX trên bảng mới rỗng index cho phép MySQL dùng thuật toán "Sort Index" (sắp xếp dữ liệu rồi xây index B-Tree) thay vì "Insert Index" (chèn từng key). Nhanh hơn nhiều và tránh keycache.
• RENAME TABLE là atomic, không downtime cho ứng dụng.

Cấu hình buffer liên quan (MyISAM)

• myisam_sort_buffer_size: Buffer cho sắp xếp index khi REPAIR/ALTER. Tăng lên (ví dụ 256M, 1G) nếu có RAM dư.
• key_buffer_size: Cache index block. Không liên quan trực tiếp đến "repair with keycache" khi tạo index.

Lưu ý: Kỹ thuật này chủ yếu cho engine MyISAM. Với InnoDB (mặc định từ 5.5), ALGORITHM=INPLACE (Online DDL) thường xử lý tốt mà không cần thủ công. Nhưng cách tạo bảng mới + rename vẫn an toàn cho cả hai engine.