MySQL 大表效能優化實戰：分庫分表與線上結構變更

案例背景

members 表超過 1000 萬筆記錄，查詢嚴重緩慢。團隊採用「分表 + 線上 DDL」雙管齊下策略。

一、分表策略：Hash 取模分散

將單表拆分為 100 張同結構子表 members00 ~ members99，依 mid % 100 路由。

建表腳本 (PHP 產生 SQL)

<?php
for ($i = 0; $i < 100; $i++) {
    // 建表語句 (實際執行時取消註解)
    // echo "CREATE TABLE db2.members" . sprintf("%02d", $i) . " LIKE db1.members;
";
    
    // 資料遷移語句
    echo "INSERT INTO members" . sprintf("%02d", $i) 
       . " SELECT * FROM members WHERE mid % 100 = $i;
";
}
?>

關鍵設計點

• 分表鍵選擇： mid 為主鍵，Hash 分布均勻
• 應用層路由： 讀寫前計算 $table = 'members' . sprintf('%02d', $mid % 100)
• 跨分表查詢： 需 UNION ALL 合併結果，或引入中介層 (如 ShardingSphere)

二、線上修改表結構：零停機 DDL

原表 8GB 含大量冗餘欄位，需重構結構。

操作步驟

1. 建立影子表： CREATE TABLE members_tmp LIKE members;
2. 修改影子表結構： ALTER TABLE members_tmp ... (新增/刪除欄位、調整型別、建索引)
3. 分批遷移資料： 利用主鍵範圍分批 INSERT INTO members_tmp SELECT * FROM members WHERE mid BETWEEN ? AND ?，每批 5 萬筆
4. 同步增量： 遷移期間新增資料，可用觸發器或雙寫同步至影子表
5. 原子切換： RENAME TABLE members TO members_bak, members_tmp TO members;

RENAME TABLE 原子性保證

/* 經典無鎖切換語句 */
RENAME TABLE members TO members_bak, members_tmp TO members;

• 單一語句執行，元資料鎖持有時間極短 (毫秒級)
• 實際測試：8GB 表切換僅鎖定 ~200ms
• 切換後表體積縮減至 2GB+ (移除冗餘、重建索引)

操作技巧

• 選擇業務低峰期執行 (雖然理論無鎖，實際仍有極短鎖定)
• 遷移腳本加入 SLEEP(0.01) 降低主庫壓力
• 監控 Seconds_Behind_Master 確保從庫同步無延遲

三、避坑指南：FLOAT 型別精度陷阱

phpMyAdmin 顯示 12.34，但 WHERE price = 12.34 查無結果。

-- 原因：FLOAT 存儲為近似值
SELECT * FROM products WHERE price = 12.34;  -- 失敗
SELECT * FROM products WHERE ABS(price - 12.34) < 0.001;  -- 正確

建議： 金額/精度敏感欄位務必使用 DECIMAL(10,2)。

總結對照表

現代架構推薦：分庫分表中介層 (ShardingSphere, Vitess) + 線上 DDL 工具 (gh-ost)，將複雜度下沉基礎設施層。