MySQL 分表優化實戰：存儲過程自動化分表、千萬級測試、索引策略

業務場景

• 表 t_group：1000 萬行，欄位 id, money, user_name, create_time
• user_name 僅 8 個 distinct 值，單列索引選擇性極低（基數 8），查詢常按 user_name 過濾
• 目標：按 user_name 拆分為 8 張子表 t_group_{user_name}，提升查詢效能

自動化分表存儲過程

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_split_table`$$
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 '';
    
    -- 獲取所有 user_name
    DECLARE cur1 CURSOR FOR SELECT user_name FROM t_group GROUP BY user_name;
    DECLARE CONTINUE HANDLER FOR 1329 SET done = 1;  -- NOT FOUND
    
    OPEN cur1;
    read_loop: 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 = ', QUOTE(v_user_name));
            PREPARE s1 FROM @stmt; EXECUTE s1; DEALLOCATE PREPARE s1;
            
            -- 可選：驗證行數
            -- SELECT CONCAT(v_table_name, ': ', ROW_COUNT()) AS migrated;
        END IF;
    END WHILE;
    CLOSE cur1;
    SET @stmt = NULL;
END$$
DELIMITER ;

-- 執行
CALL sp_split_table();

關鍵技術點

1. QUOTE() 防注入：動態 SQL 中字符串值必須用 QUOTE() 包裹，避免單引號注入
2. LIKE 復制結構：CREATE TABLE ... LIKE src 保留索引、約束、預設值、引擎
3. 游標處理 NOT FOUND：DECLARE CONTINUE HANDLER FOR 1329 SET done=1 (MySQL 錯誤碼 1329 = No data)
4. PREPARE/EXECUTE：動態 SQL 執行標準模式

測試結果驗證

-- 分表前
mysql> SHOW INDEX FROM t_group;
-- idx_user_name 基數 8，選擇性極低

-- 分表後
mysql> SHOW TABLES LIKE 't_group_%';
+------------------------------+
| t_group_david  | t_group_leo | ... (8 張表) |

-- 單表查詢
SELECT * FROM t_group_david WHERE money > 1000;
-- 掃描行數從 1000萬 降為 ~125萬，索引選擇性大幅提升

索引策略調整

• 原表：idx_user_name（基數 8）、idx_combination1 (user_name, money)（鬆散索引掃描）
• 子表：移除 user_name 相關索引（已隱含在表名中），保留 PRIMARY KEY (id)、idx_money (money) 等業務索引
• 查詢改造：應用層根據 user_name 路由到對應子表

分表 vs 分區 vs 分庫

避坑指南

1. 事務跨表：分表後無法跨表事務，需業務層保證最終一致性或避免跨表寫入
2. 全局唯一 ID：子表 id 衝突 → 用雪花算法/UUID/分庫序列
3. DDL 變更：新增欄位需對 8 張表同步執行，建議自動化腳本
4. 熱點傾斜：若某 user_name 數據量極大，需二次拆分（按時間/哈希）
5. 統計資訊：各子表需定期 ANALYZE TABLE

總結

低基數欄位（如 user_name 8 個值）是分表理想鍵。存儲過程自動化分表可復用，**核心價值在於將「大表低選擇性索引」轉化為「小表高選擇性索引 + 應用路由」**。生產環境建議配合中間件或應用框架實現透明路由，而非硬編碼表名。