MySQL 存儲過程批量插入千萬級測試數據：從基礎寫法到分表擴展優化

MySQL 存儲過程批量插入千萬級測試數據：從基礎寫法到分表擴展優化

性能測試需要千萬級數據支撐。本文展示從單表存儲過程插入、到分表擴展、再到腳本化批量複製的完整演進路徑，實測單表 1000 萬行寫入從小時級降至分鐘級。

一、基礎版：雙層循環存儲過程

CREATE TABLE `fortest` (
  `ID` INT(30) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `IP` VARCHAR(32) NOT NULL,
  `OID` VARCHAR(15) DEFAULT NULL
) ENGINE=InnoDB;

DELIMITER $$
CREATE PROCEDURE `autoinsert`(IN IP_NUM INT, IN OID_NUM INT)
BEGIN
  DECLARE iIP INT DEFAULT 0;
  DECLARE iOID INT DEFAULT 0;
  WHILE iIP < IP_NUM DO
    SET iOID = 0;
    WHILE iOID < OID_NUM DO
      SET @sql = CONCAT('INSERT INTO fortest (IP, OID) VALUES(',
        'CONCAT(ROUND(RAND()*255)+1,".",ROUND(RAND()*255)+1,".",ROUND(RAND()*255)+1,".",ROUND(RAND()*255)+1),',
        'ROUND(RAND()*100)+1)');
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
      SET iOID = iOID + 1;
    END WHILE;
    SET iIP = iIP + 1;
  END WHILE;
END$$
DELIMITER ;

-- 調用: 1000 IP × 50 OID = 50,000 行
CALL autoinsert(1000, 50);

缺點：逐條 PREPARE/EXECUTE 開銷大，5 萬行約 30 秒；千萬行需數小時。

二、進階版：分表 + INSERT SELECT 批量複製

-- 1. 創建同結構空表
CREATE TABLE fortest_2 LIKE fortest;

-- 2. 先在 fortest 造 5 萬基礎數據 (上述存儲過程)

-- 3. 批量複製並隨機化 OID，單條 SQL 寫入 5 萬行
INSERT INTO fortest_2 (IP, OID) 
SELECT IP, ROUND(RAND()*100)+1 FROM fortest;
-- 耗時 < 1 秒

三、生產級腳本：Shell 循環放大至 1000 萬行

#!/bin/bash
# insert_scale.sh  目標行數作為參數 $1
MAX_INSERT_ROW_COUNT=$1
i=1
while [ $i -le $MAX_INSERT_ROW_COUNT ]; do
  TIMEFORMAT=%R
  elapsed=$( { time mysql -h192.168.137.1 -uroot -p123456 fortest \
    -e "INSERT INTO fortest_2(IP,OID) SELECT IP,ROUND(RAND()*100)+1 FROM fortest;" ; } 2>&1 )
  echo "Batch $i: ${elapsed}s"
  i=$((i+1))
  # sleep 0.05  # 可選：緩衝主從同步壓力
done

執行 ./insert_scale.sh 200 (200 × 5萬 = 1000 萬)，總耗時約 3-5 分鐘 (視磁盤 IO、緩沖池大小)。

四、關鍵優化點總結

五、生產環境注意事項

• 主從同步壓力：大批量寫入導致 Binlog 激增，Slave 延遲飆升。建議：SET SESSION sql_log_bin=0; (臨時關閉主庫 Binlog) 或分時段寫入。
• 自增 ID 耗盡：BIGINT 替代 INT，或預留步長 auto_increment_increment。
• 緩沖池污染：大量熱數據擠佔 Buffer Pool，影響線上查詢。建議：innodb_old_blocks_pct、innodb_old_blocks_time 調優，或使用專用測試實例。
• 磁盤空間：預估數據+索引大小，預留 2 倍空間應對碎片、Undo、Redo。
• 統計信息更新：導入完成執行 ANALYZE TABLE，確保優化器選擇正確執行計劃。

由「逐條寫」到「批量複製」再到「腳本放大」，掌握分層優化思維，才能在有限時間內構建千萬級甚至億級測試數據集。