MySQL 分表優化實測：按用戶名分表將 1000 萬行查詢從 10 秒降至毫秒級

實驗背景

• 表 t_group：1038 萬行，欄位 id, money, user_name, create_time。
• user_name 僅 8 個 distinct 值，單列索引基數極低（8），屬「爛索引」。
• 業務頻繁查詢 WHERE user_name = 'xxx' 或 <> 'xxx'。

分表存儲過程

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 ;

分表後結構

生成 8 張子表：t_group_david、t_group_leo... 每表約 130 萬行，保留原表結構（含 user_name 冗余列）。

效能對比測試

關鍵優化點：大膽刪除冗余列

子表中 user_name 已成常量，刪除該列後：

• MIN/MAX 從全表掃描變為全索引掃描（秒級 → 毫秒級）。
• SUM/AVG 亦顯著加速（1.68s → 0.94s）。

結論

1. 分表將「大表掃描」轉化為「小表精準查詢」，等值查詢提升 100 倍+。
2. 不等值查詢改用「總數 - 等值數」繞過全表掃描。
3. 分表後務必清理冗余列，釋放索引優勢，讓優化器選擇最優執行計劃。