業務背景

單表 t_group 1038 萬筆，按 user_name 分 8 組，單表查詢 user_name <> 'david' 耗時 9 秒，不可接受。

分表策略：按 user_name 物理分表

t_group_david, t_group_leo, t_group_livia, ... (共 8 表)

存儲過程自動分表

DELIMITER $$
CREATE PROCEDURE sp_split_table()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_user_name VARCHAR(20);
    DECLARE v_table_name VARCHAR(64);
    DECLARE cur1 CURSOR FOR SELECT user_name FROM t_group GROUP BY user_name;
    DECLARE CONTINUE HANDLER FOR 1329 SET done = 1;

    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);
            SET @stmt = CONCAT('CREATE TABLE ', v_table_name, ' LIKE t_group');
            PREPARE s1 FROM @stmt; EXECUTE s1; DEALLOCATE PREPARE s1;

            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 ;

效能對比（關鍵指標）

查詢	原大表	分表後	備註
COUNT(*) WHERE user_name='david'	1.71s	0.00s	覆蓋索引秒出
COUNT(*) WHERE user_name<>'david'	9.26s	0.00s	改用 總數 - david數
SUM/AVG money WHERE user_name='david'	2.15s	0.94s	分表後仍需掃描，但數據量少 8 倍

關鍵優化：刪除分表後冗餘欄位

ALTER TABLE t_group_david DROP COLUMN user_name;

效果：

• MIN/MAX 從 1.5s 降為 0.00s（索引生效）
• SUM/AVG 從 1.68s 降為 0.94s

分表後查詢重寫模式

-- 原：跨 user_name 聚合
SELECT user_name, COUNT(*) FROM t_group GROUP BY user_name;

-- 新：UNION ALL 分表結果
SELECT 'david' AS user_name, COUNT(*) FROM t_group_david
UNION ALL SELECT 'leo', COUNT(*) FROM t_group_leo
...;

-- 原：排除某 user
SELECT COUNT(*) FROM t_group WHERE user_name <> 'david';

-- 新：總數 - 該分表數
SELECT (SELECT COUNT(*) FROM t_group) - (SELECT COUNT(*) FROM t_group_david) AS total;

避坑與最佳實踐

1. 分表鍵選擇：基數適中、查詢頻繁、業務隔離明確
2. 冗餘欄位清理：分表鍵在子表中無查詢價值 → 刪除
3. 應用層路由：中間件或代碼層根據分表鍵導向正確表
4. 跨分表事務：避免；必須時用分散式事務或最終一致性
5. DDL 變更：需同步執行於所有分表，建議自動化工具

總結

物理分表將大表拆小，配合刪除冗餘欄位、查詢改寫（排除查詢用總數相減），將 9 秒查詢壓至毫秒級。核心是「以空間換時間、以冗餘換查詢簡單度」。