mysql> OPTIMIZE TABLE test;
+------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------+----------+----------+-------------------------------------------------------------------+
| test1.test | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test1.test | optimize | status | OK |
+------------+----------+----------+-------------------------------------------------------------------+
提示:「Table does not support optimize, doing recreate + analyze instead」。
原因:InnoDB 不支援 OPTIMIZE TABLE,MySQL 內部轉為 ALTER TABLE ... ENGINE=InnoDB 重建表 + ANALYZE TABLE 更新統計信息。
ALTER TABLE test ENGINE=InnoDB;
等效於 OPTIMIZE TABLE 但更可控。執行過程:
過程中允許並發讀寫(Online DDL,5.6+),但會佔用額外磁盤空間(約原表大小)。
編輯 my.cnf [mysqld]:
innodb_buffer_pool_size=1G # 物理內存 50%-75%,避免磁盤 IO
innodb_file_io_threads=4 # ≤ CPU 核心數
innodb_write_io_threads=4
innodb_read_io_threads=4
innodb_log_file_size=256M # 減少 checkpoint 刷盤頻率
innodb_flush_log_at_trx_commit=2 # 非核心業務可設 2,效能大增
修改後 systemctl restart mysqld 生效。
USE information_schema;
SELECT
CONCAT(ROUND(SUM(data_length)/1024/1024,2), 'MB') AS data_length_MB,
CONCAT(ROUND(SUM(index_length)/1024/1024,2), 'MB') AS index_length_MB
FROM tables
WHERE table_schema='test1' AND table_name='test';
| 階段 | data_length_MB | index_length_MB |
|---|---|---|
| 優化前 | 20.55 MB | 27.55 MB |
| 優化後 | 20.55 MB | 16.55 MB |
索引體積縮減 40%,查詢效能顯著提升。
ANALYZE,但建議手動 ANALYZE TABLE test; 確保優化器統計最新。