MySQL 分表優化實測：千萬級數據下的存儲過程拆分與查詢效能對比

理論皆灰綠，唯實測吐真金。本文記錄一個真實的千萬級數據分表實驗：從存儲過程自動拆分、索引策略調整、冗余列清理，到最終查詢效能的量化對比，還原分表優化的完整決策鏈路。

一、 實驗環境與基線數據

• 源表： t_group，1038 萬行，字段：id (PK)、money、user_name、create_time。
• 索引： PK(id)、idx_user_name(user_name)、idx_combination1(user_name, money)。
• 分片鍵： user_name (8 個 distinct 值：david, leo, livia, lucy, sarah, simon, sony, sunny)。
• 切分邏輯： 按 user_name 拆分為 8 張子表 t_group_{user_name}。

二、 存儲過程自動化拆分

DELIMITER $$
CREATE PROCEDURE `sp_split_table`()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_user_name VARCHAR(20) DEFAULT '';
    DECLARE v_table_name VARCHAR(64) DEFAULT '';
    DECLARE cur1 CURSOR FOR SELECT user_name FROM t_group GROUP BY user_name;
    DECLARE CONTINUE HANDLER FOR 1329 SET done = 1; -- No data
    
    OPEN cur1;
    WHILE done <> 1 DO
        FETCH cur1 INTO v_user_name;
        IF NOT done THEN
            SET v_table_name = CONCAT('t_group_', v_user_name);
            -- 1. 克隆結構
            SET @stmt = CONCAT('CREATE TABLE ', v_table_name, ' LIKE t_group');
            PREPARE s1 FROM @stmt; EXECUTE s1; DEALLOCATE PREPARE s1;
            -- 2. 導入數據
            SET @stmt = CONCAT('INSERT INTO ', v_table_name, 
                               ' SELECT * FROM t_group WHERE user_name = ''', v_user_name, '''');
            PREPARE s1 FROM @stmt; EXECUTE s1; DEALLOCATE PREPARE s1;
        END IF;
    END WHILE;
    CLOSE cur1;
    SET @stmt = NULL;
END$$
DELIMITER ;

三、 關鍵優化：刪除冗余分片鍵列

子表中 user_name 已無區分度（全表同值），佔用空間且干擾聚合掃描。執行：

ALTER TABLE t_group_david DROP COLUMN user_name;

耗時 7.58 秒 (129 萬行)，後續聚合查詢顯著加速。

四、 效能對比數據 (秒)

五、 核心發現與經驗總結

1. 分表後單表行數驟降 → 索引樹高度降低 → 等值查詢極速。
2. 冗余分片鍵列害大於利： 佔空間、導致聚合退化為 Full Table Scan，必須刪除。
3. 範圍/不等值查詢： 原表需掃描 900 萬行 (9.26s)；分表策略改為「總數 - 目標分片數」秒級完成。
4. 存儲過程動態建表 + 預處理語句： 避免 SQL 注入、提升批量 DDL 效率，生產可直接復用。
5. 索引策略重審： 分表後單表可只保留業務真正需要的索引，減少寫入開銷。

六、 生產落地建議

• 分片鍵選型：基數適中、查詢高頻、業務天然隔離（如 user_id、shop_id、date）。
• 自動化運維：封裝拆分/合併/遷移腳本，納入發布流程。
• 中間件層屏蔽：使用 ShardingSphere、MyCat 或自研 DDAL 路由，業務代碼無感知。
• 監控告警：單表行數、索引大小、慢查詢、分片分佈均勻度。

七、 總結

千萬級實測證明：分表 + 清理冗余列 + 索引重構 可將核心查詢從秒級降至毫秒級。分表非銀彈，但配合精細化優化，是突破單表效能天花板的必經之路。