MySQL MyISAM 4GB限制解除

MySQL MyISAM 有 4GB 的限制, 如果使用量超出 4GB, 有何方法能解決.

因: MySQL 用 32bits pointer 來記錄資料位置, 所以只能定址到4GB的空間, 再多就會出現此錯誤.

解法: 將原本檔案大小限制 轉換成 rows 數目限制即可. 但是特別注意, 要做此轉換前, 一定要做備份, 並對 table 做 OPTIMIZE TABLE... 千萬千萬要做 OPTIMIZE TABLE.

實作如下:

1. mysqldump -u root DB_NAME > DB_NAME.sql 備份
2. mysql> optimize table TABLE_NAME; (註)
3. mysql> alter table TABLE_NAME max_rows=210000000 avg_row_length = 1024;

若是要建立或還原時就設定要轉換, 可於 CREATE TABLE 語法中, 最後指定如下:

• TYPE=MyISAM max_rows=4294967295 avg_row_length=50 ;

上述語法中 的 max_rows 和 avg_row_length 代表:

• max_rows: 總筆數最大多少筆
• avg_row_length: 平均每筆 rows 的大小

註: 若沒有下 OPTIMIZE TABLE 而直接做 ALTER TABLE 可能導致資料流失的慘劇, 所以務必要做, 不過 OPTIMIZE TABLE 依照 MyISAM 的做法是把資料整個撈出來, 再寫到另一個新的 data file 去, 所以如果資料量大, 需要一點時間.

做過 OPTIMIZE 後, 速度會有明顯的變快,
因為平常資料難免會有 UPDATE 或 DELETE 動作, 造成資料的不連續性, 此 OPTIMIZE 也就是將此問題解決, 也可以想成就是做磁碟重組吧.

MySQL Optimize 詳可參考 MySQL OPTIMIZE TABLE Syntax