試圖看看是否有任何方法可以提高 MySQL 中的 INSERT 速度。我在 Windows 10 上運行。這是我在回圈中執行完全相同的 INSERT 10000 次后得到的結果：
MySQL:
CALL TestStoredProcedure(10000);
/* Affected rows: 0 Found rows: 1 Warnings: 0 Duration for 1 query: 48.906 sec. */
MSSQL:
EXEC TestStoredProcedure @iterations = 10000;
/* Affected rows: 10,000 Found rows: 0 Warnings: 0 Duration for 1 query: 0.875 sec. */
MySQL：
BEGIN
DECLARE counter INT DEFAULT 1;
WHILE counter <= iterations DO
INSERT INTO mytable (COLUMN1, COLUMN2) VALUES (counter, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
SET counter = counter 1;
END WHILE;
END
MSSQL :
BEGIN
DECLARE @counter int
SET @counter = 1
WHILE (@counter <= @iterations)
BEGIN
INSERT INTO mytable (COLUMN1, COLUMN2) VALUES (@counter, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')
SET @counter = @counter 1
END
END
謝謝大家！下面找到了解決方法，就是插入START TRANSACTION;行。
結果：
InnoDB (using START TRANSACTION;): 0.406 sec
InnoDB: 48.312 sec
MyISAM: 23.218 sec
MEMORY: 22.922 sec
BLACKHOLE: 22.890 sec
ARCHIVE: 22.860 sec
uj5u.com熱心網友回復：
嗨，您可能正在使用 InnoDB 作為資料庫引擎，InnoDB 的作業原理是它需要在每次插入后進行提交，這會使您的插入變慢，請按如下方式重寫您的函式：
BEGIN
DECLARE counter INT DEFAULT 1;
Start transaction;
WHILE counter <= iterations DO
INSERT into MyTable (Column1, Column2) VALUES (counter, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
SET counter = counter 1;
END WHILE;
commit;
END;
uj5u.com熱心網友回復：
首先，在可能的情況下避免回圈
例如，您 LOOP 需要0.839幾秒鐘，而 set 方法需要0.027幾秒鐘
INSERT INTO MyTable (COLUMN1, COLUMN2)
Select Top 10000
Column1 = row_number() over (order by (select NULL))
,Column2 = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
From master..spt_values n1, master..spt_values n2
編輯...只是為了好玩dbFiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/355011.html
標籤：mysql sql-server 表现 插入