MySQL 利用自我複製快速讓資料表資料成倍增長

前言

在效能測試、分頁功能驗證或大量資料模擬時，逐筆 INSERT 效率極低。MySQL 提供一種巧妙的「表自我複製」技巧，能以指數級速度讓資料量快速膨脹，幾條指令即可從幾筆測試資料產生百萬級測試集。

核心語法

INSERT INTO target_table (col1, col2, ...)
SELECT col1, col2, ... FROM target_table;

操作步驟與原理

1. 準備種子資料：先手動或腳本插入少量基礎資料（如 10 筆）。
2. 執行自我複製：每執行一次上述語句，資料量翻倍（10→20→40→80…）。
3. 主鍵處理：若主鍵為 AUTO_INCREMENT，必須明確列出欄位名稱，排除主鍵欄位，否則會因主鍵衝突報錯。

   -- 假設 id 為自增主鍵
   INSERT INTO t1 (col1, col2) SELECT col1, col2 FROM t1;
   

4. 重複執行：視需求重複執行，N 次執行後資料量 = 初始量 × 2^N。

實戰範例

建立測試表

CREATE TABLE `user_log` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `action` VARCHAR(32) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入 5 筆種子資料

INSERT INTO `user_log` (`user_id`, `action`) VALUES
(1, 'login'), (2, 'view'), (3, 'purchase'), (4, 'logout'), (5, 'login');

指數增長演示

幾十秒內即可產生千萬級測試資料，滿足大多數效能測試需求。

進階變體

1. 搭配函數產生變化資料

INSERT INTO `user_log` (`user_id`, `action`, `created_at`)
SELECT 
  `user_id` + 1000, 
  CONCAT(`action`, '_copy'), 
  DATE_ADD(`created_at`, INTERVAL 1 DAY)
FROM `user_log`;

避免資料完全重複，更接近真實場景。

2. 限制複製範圍

INSERT INTO `t1` (`a`, `b`)
SELECT `a`, `b` FROM `t1` WHERE `id` < 10000;

控制每輪增長量，避免單次交易過大導致鎖爭用或複製日誌暴增。

注意事項與最佳實踐

• 交易日誌膨脹：大量複製會產生大量 redo/undo log，建議分批次執行，或臨時調大 innodb_log_file_size。
• 主從複寫延遲：主庫大量寫入會導致從庫延遲，測試環境可暫停複寫或使用 SET sql_log_bin=0 跳過二進制日誌（僅限測試庫）。
• 磁碟空間：預估最終資料大小，確保磁碟剩餘空間充足（建議預留 3 倍以上）。
• 索引維護成本：每次複製都會觸發二級索引更新，若只需純資料量測試，可先 DROP INDEX 複製完再 CREATE INDEX，速度提升數倍。
• 非 InnoDB 引擎：MyISAM 也支援此語法，但無交易保護，中斷風險較高。

替代方案比較

總結

MySQL 的 INSERT INTO ... SELECT ... FROM 同一張表 是快速製造大量測試資料的「秘密武器」。掌握主鍵排除、分批執行、索引暫停等技巧，能在幾分鐘內從零建立千萬級測試資料集，大幅提升測試準備效率。記得：僅限測試/開發環境使用，生產環境嚴禁直接操作！