MySQL 分表優化實戰：千萬級數據按用戶分表、去除冗余列、聚合查詢加速對比

場景與分表邏輯

表 t_group 1038 萬行，按 user_name（8 個用戶）水平拆分為 t_group_{user} 8 張子表。單列 user_name 索引基數極低（Cardinality=8），屬「爛索引」。

存儲過程自動化分表

CREATE PROCEDURE sp_split_table()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_user VARCHAR(20);
    DECLARE v_tbl VARCHAR(64);
    DECLARE cur CURSOR FOR SELECT user_name FROM t_group GROUP BY user_name;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    WHILE done <> 1 DO
        FETCH cur INTO v_user;
        IF NOT done THEN
            SET v_tbl = CONCAT('t_group_', v_user);
            SET @sql = CONCAT('CREATE TABLE ', v_tbl, ' LIKE t_group');
            PREPARE s1 FROM @sql; EXECUTE s1; DEALLOCATE PREPARE s1;

            SET @sql = CONCAT('INSERT INTO ', v_tbl, ' SELECT * FROM t_group WHERE user_name = "', v_user, '"');
            PREPARE s1 FROM @sql; EXECUTE s1; DEALLOCATE PREPARE s1;
        END IF;
    END WHILE;
    CLOSE cur;
    SET @sql = NULL;
END;

查詢性能對比（結果震撼）

關鍵優化點

1. 分表消除「不等於」全表掃描：user_name<>'david' 改為 總數 - 子表數，從 9 秒降為毫秒級。
2. 大膽刪除子表冗余列：user_name 在子表中恆定，刪除後 MIN/MAX 從全表掃描變為索引掃描，SUM/AVG 掃描行數減少，性能顯著提升。
3. 索引策略：大表保留組合索引 (user_name, money) 支持 GROUP BY 鬆散索引掃描；子表無需 user_name 索引。

遷移應用層改造

• 路由層根據 user_name 導向對應子表。
• 跨用戶聚合查詢改為 UNION ALL 子表或應用層合併。
• 異構查詢（如按 money 篩選）需評估是否引入全局索引表或 ES。