Computer >> Hướng Dẫn Máy Tính >  >> Lập Trình >> MySQL

Tối ưu hóa bảng MySQL sau khi xóa hàng với OPTIMIZE TABLE

Giới thiệu

Sau khi xóa nhiều hàng (DELETE), bảng InnoDB có thể bị phân mảnh (fragmentation), lãng phí dung lượng và làm chậm truy vấn. OPTIMIZE TABLE tái cấu trúc bảng, giải phóng không gian và cập nhật thống kê.

Ví dụ thực hành

1. Tạo bảng DemoTable

mysql> CREATE TABLE DemoTable (
    Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100)
);

2. Chèn 4 hàng

mysql> INSERT INTO DemoTable(Name) VALUES ('Chris'),('Robert'),('Bob'),('David');

3. Xóa 2 hàng (Id 1, 3)

mysql> DELETE FROM DemoTable WHERE Id IN (1,3);

4. Chạy OPTIMIZE TABLE

mysql> OPTIMIZE TABLE DemoTable;

5. Kết quả

+------------------+----------+----------+-------------------------------------------------------------------+
| Table            | Op       | Msg_type | Msg_text                                                          |
+------------------+----------+----------+-------------------------------------------------------------------+
| web.DemoTable    | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| web.DemoTable    | optimize | status   | OK                                                                |
+------------------+----------+----------+-------------------------------------------------------------------+

Giải thích thông báo

  • Table does not support optimize, doing recreate + analyze instead: InnoDB không hỗ trợ OPTIMIZE trực tiếp (như MyISAM). MySQL thực hiện: CREATE TABLE new LIKE old; INSERT INTO new SELECT * FROM old; RENAME TABLE old TO tmp, new TO old; DROP TABLE tmp; ANALYZE TABLE old; — tái tạo bảng.
  • status: OK: Thành công.

Khi nào nên OPTIMIZE TABLE

  • Sau DELETE lượng lớn ( > 10-20% hàng).
  • Sau cập nhật nhiều cột biến độ dài (VARCHAR, TEXT, BLOB) làm phân mảnh.
  • Bảng có nhiều UPDATE/DELETE thường xuyên.
  • Muốn cập nhật thống kê cho optimizer (ANALYZE TABLE cũng làm được).

Lưu ý quan trọng

  • Khóa bảng (lock) trong quá trình tái tạo → ứng dụng không ghi được. Chạy ở giờ thấp điểm.
  • Cần dung lượng disk trống ≥ kích thước bảng hiện tại.
  • InnoDB: OPTIMIZE TABLE = ALTER TABLE ... ENGINE=InnoDB (rebuild).
  • MyISAM: OPTIMIZE TABLE thực sự defragment file dữ liệu.
  • Thay thế nhẹ nhàng hơn: ANALYZE TABLE (chỉ cập nhật thống kê, không rebuild, không lock lâu).