MySQL 快速生成千萬級測試數據表：MyISAM 批量插入存儲過程實測 95 秒完成

MySQL 千萬級測試表快速構建實戰

目標

建立 1000 萬行用戶測試表，驗證分表、優化等方案。

表結構

CREATE TABLE usertb (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uname VARCHAR(20),
  ucreatetime DATETIME,
  age INT(11)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1 ROW_FORMAT=COMPACT;

選 MyISAM：批量插入速度遠快於 InnoDB（無事務、無雙寫、順序寫入）。

存儲過程：批量提交 + 循環

DELIMITER $$
SET AUTOCOMMIT = 0$$
CREATE PROCEDURE test1()
BEGIN
  DECLARE v_cnt DECIMAL(10) DEFAULT 0;
  dd: LOOP
    INSERT INTO usertb VALUES
      (NULL,'用戶1','2010-01-01 00:00:00',20),
      (NULL,'用戶2','2010-01-01 00:00:00',20),
      (NULL,'用戶3','2010-01-01 00:00:00',20),
      (NULL,'用戶4','2010-01-01 00:00:00',20),
      (NULL,'用戶5','2011-01-01 00:00:00',20),
      (NULL,'用戶6','2011-01-01 00:00:00',20),
      (NULL,'用戶7','2011-01-01 00:00:00',20),
      (NULL,'用戶8','2012-01-01 00:00:00',20),
      (NULL,'用戶9','2012-01-01 00:00:00',20),
      (NULL,'用戶0','2012-01-01 00:00:00',20);
    COMMIT;
    SET v_cnt = v_cnt + 10;
    IF v_cnt = 10000000 THEN LEAVE dd; END IF;
  END LOOP dd;
END$$
DELIMITER ;

執行與耗時

CALL test1();

• i5 筆記本，**95 秒**完成 1000 萬行插入（MyISAM）。
• 後續轉 InnoDB：ALTER TABLE usertb ENGINE=InnoDB; 耗時 **200 秒**。

關鍵優化點

1. MyISAM 引擎：無事務日誌，順序寫入極快。
2. 關閉自動提交：SET AUTOCOMMIT=0，手動 COMMIT 批量提交（每 10 行）。
3. 多值 INSERT：單語句插入 10 行，減少網絡往返與解析開銷。
4. 簡單數據類型：定長/短字段，減少行大小。

遷移到 InnoDB 生產環境

測試完成後，ALTER TABLE ... ENGINE=InnoDB 重建表，獲得事務、行鎖、崩潰恢復能力。