MySQL 千萬級測試數據秒級生成：存儲過程 + MyISAM 引擎切換實戰

MySQL 千萬級測試數據秒級生成：存儲過程 + MyISAM 引擎切換實戰

壓測、基準測試、新功能驗證常需千萬級測試數據。本文揭示「存儲過程批量提交 + 臨時切換 MyISAM 引擎」組合拳，將 1500 萬條插入從小時級壓縮至分鐘級，並給出演練腳本、引擎切換風險控制、數據驗證清單。

核心加速原理

完整存儲過程腳本

-- 1. 目標表（預建結構，無索引/僅主鍵）
CREATE TABLE `test` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(20) DEFAULT 'sdfasdfas',
  `name` VARCHAR(20) DEFAULT 'aaa',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;  -- 初始用 InnoDB 便於後續業務驗證

-- 2. 生成存儲過程
DROP PROCEDURE IF EXISTS gen_test_data;
DELIMITER $$
CREATE PROCEDURE gen_test_data(IN p_total INT, IN p_batch INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  -- 關鍵：關閉自動提交，手動控制事務邊界
  SET autocommit = 0;
  
  WHILE i < p_total DO
    INSERT INTO `test` (`code`, `name`) VALUES ('sdfasdfas', 'aaa');
    SET i = i + 1;
    
    -- 每 p_batch 條提交一次
    IF i % p_batch = 0 THEN
      COMMIT;
      -- 可選：進度日誌
      -- INSERT INTO gen_log (cnt, ts) VALUES (i, NOW());
    END IF;
  END WHILE;
  
  -- 餘數提交
  COMMIT;
  SET autocommit = 1;
END$$
DELIMITER ;

-- 3. 臨時切換 MyISAM（生成階段）
ALTER TABLE `test` ENGINE = MyISAM;

-- 4. 執行生成（1500萬條，每1000條提交）
CALL gen_test_data(15000000, 1000);

-- 5. 切回 InnoDB（業務驗證階段）
ALTER TABLE `test` ENGINE = InnoDB;

-- 6. 重建索引（如有二級索引）
-- ALTER TABLE `test` ADD INDEX idx_code (`code`);

關鍵風險控制

1. 數據持久性： MyISAM 無 REDO 日誌，生成過程中崩潰會導致部分數據丟失。僅用於可重跑的測試數據生成，嚴禁生產業務表。
2. 表鎖阻塞： MyISAM 表級鎖，生成期間該表不可讀寫。安排在維護窗口或獨立測試實例執行。
3. 引擎切換耗時： ALTER TABLE ... ENGINE=InnoDB 會全表重建，1500 萬行約需數分鐘，需納入時間預算。
4. 自增值連續性： 存儲過程中斷重跑時，自增值不會回退，需手動 ALTER TABLE test AUTO_INCREMENT=1 重置。

進階優化：禁用二級索引、批量載入

-- 生成前刪除所有二級索引
SHOW INDEX FROM `test` WHERE Key_name != 'PRIMARY';
-- 逐個 DROP INDEX ...

-- 生成後一次性添加
ALTER TABLE `test` 
  ADD INDEX idx_code (`code`),
  ADD INDEX idx_name (`name`);

-- 或使用 LOAD DATA INFILE（更快，需文件準備）
-- 1. 存儲過程改為寫入 CSV 文件（SELECT ... INTO OUTFILE）
-- 2. LOAD DATA INFILE '/path/data.csv' INTO TABLE `test` ...

驗證清單

• 行數核對：SELECT COUNT(*) FROM test; = 15000000
• 自增值：SHOW TABLE STATUS LIKE 'test'\G → Auto_increment = 15000001
• 數據分布：SELECT code, COUNT(*) FROM test GROUP BY code; 全為 15000000
• 引擎確認：ENGINE=InnoDB（切回後）
• 索引狀態：ANALYZE TABLE test; 更新統計信息

替代方案對比

「存儲過程 + MyISAM + 批量提交」是零依賴、純 SQL、秒級生成千萬測試數據的最佳平衡點。掌握引擎切換風險邊界，讓測試數據準備不再是壓測啟動的瓶頸。