MySQL "Table is full" 錯誤根因分析與解決方案

錯誤現象

2023-01-15 14:38:23 Aborted connection 854 to db: 'users' user: 'josh'

四大核心根因

1. 磁碟空間耗盡 (最常見)

• 數據目錄分區無可用空間：df -h /var/lib/mysql
• 臨時目錄分區滿：df -h /tmp (複雜查詢產生臨時表)
• 二進制日志佔滿：ls -lh /var/lib/mysql/mysql-bin.*

解決：清理舊日志、擴容磁碟、遷移大表、調整 expire_logs_days。

2. MyISAM 表達到文件大小限制

• 32 位系統：單表最大 4GB (實際 2GB)。
• 64 位系統：受作業系統文件大小限制 (通常 16TB+)，但 MyISAM 內部指針默認 6 字節 (256TB)。
• 檢查：myisamchk -dv /path/to/table.MYI 查看 Max data length。

解決：遷移至 InnoDB、或 ALTER TABLE tbl MAX_ROWS=1000000000 AVG_ROW_LENGTH=200 增大指針 (需重建表)。

3. InnoDB 表空間滿

• 共享表空間 (ibdata1) 自動擴展受磁碟限制。
• 獨立表空間 (innodb_file_per_table=ON) 受單文件限制。
• 檢查：SHOW TABLE STATUS LIKE 'tbl'\G 觀察 Data_free。

解決：啟用 innodb_file_per_table、添加新數據文件 innodb_data_file_path=ibdata1:12M:autoextend:max:10G:ibdata2:12M:autoextend、清理歷史數據。

4. 內存臨時表超過限制

• tmp_table_size / max_heap_table_size 過小，複雜查詢強制轉磁碟臨時表 (MyISAM)，再受磁碟限制。
• 狀態變量：Created_tmp_disk_tables 快速增長。

解決：優化 SQL 減少臨時表、適當增大兩參數 (如 64MB)、確保 tmpdir 指向大分區/內存盤。

排查步驟清單

1. df -h 檢查所有相關分區 (datadir、tmpdir、log_bin 目錄)。
2. df -i 檢查 inode 是否耗盡 (大量小文件)。
3. SHOW VARIABLES LIKE 'tmp_table_size'; SHOW VARIABLES LIKE 'max_heap_table_size';
4. SHOW TABLE STATUS LIKE 'tbl'\G 確認引擎、行數、數據長度、索引長度、碎片。
5. 檢查錯誤日志確認具錯誤碼 (1114 vs 其他)。

預防最佳實踐

• 監控磁碟使用率 (告警閾值 80%)、inode 使用率。
• 所有新表默認 InnoDB，逐步遷移歷史 MyISAM 表。
• 啟用 innodb_file_per_table=ON，便於單表管理與回收空間。
• 定期 OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB 整理碎片。
• 大批量導入前預估空間，預留 2 倍餘量。
• 配置 tmpdir 指向獨立大分區或 tmpfs (內存盤，注意容量)。