mySQL 连续INSERT怎么这么慢?

[hyrongg]

DELIMITER $$

DROP PROCEDURE IF EXISTS 'doloop`$$
CREATE DEFINER=`mysql`@`%` PROCEDURE doloop`()
BEGIN
DECLARE v INT default 10000;
WHILE v > 0 DO
insert into t values(v);
SET v = v - 1;
END WHILE;
END $$

DELIMITER ;

上面是一个简单的PROCEDURE,连续INSERT 10000 条记录到一个简单
表T(IDX INTEGER)

CALL DOLOOP;
要等一分多钟,MS-SQL只要9秒,怎么会事?

当家帮忙看看,谢谢!

[懒得去死]

批量快速插入：
http://blog.chinaunix.net/u/29134/showart_400939.html

[hyrongg]

关键问题,我是要比较MS-SERVER和MYSQL的插入速度,因为都说MYSQL速度比较快,
所以打算用MYSQL,但同一机器上,都是一条一条的INSERT,MS-SQL比MYSQL快的太多了,
简直不能相信,是不是我的MYSQL SCRIPT写的有问题?

谢谢大家.

[yifuzhiming]

你用程序插入试试撒,不会这么慢的

[懒得去死]

给你的例子为什么你就不看呢？
修改一下就可以测试了嘛！！！

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`sp_insert_batch`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert_batch`(IN number int(11))
BEGIN
  declare i int(11);
  set i = 1;
  -- such as 1-2000,2000-4000,....
  WHILE i <= number DO
    if mod(i,2000)=1 then
       set @sqltext =concat('(',i,')');
    elseif mod(i,2000)=0 then
       set @sqltext=concat(@sqltext,',(',i,')');
       set @sqltext=concat('insert into t values',@sqltext);
       prepare stmt from @sqltext;
       execute stmt;
       DEALLOCATE PREPARE stmt;
       set @sqltext='';
    else 
       set @sqltext=concat(@sqltext,',(',i,')');
    end if;
    set i = i + 1;
  END WHILE;
  -- process when number is not be moded by 2000
  -- such as 2001,4002,15200,...
  if @sqltext<>'' then
     set @sqltext=concat('insert into t values',@sqltext);
     prepare stmt from @sqltext;
     execute stmt;
     DEALLOCATE PREPARE stmt;
     set @sqltext='';
  end if;
END$$

DELIMITER ;

表结构：

Table   Create Table                          
------  --------------------------------------
t       CREATE TABLE `t` (                    
          `id` int(11) NOT NULL               
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
测试结果 ：

mysql> call sp_insert_batch(10000);
Query OK, 0 rows affected (0.61 sec)
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

[xbt746]

改用事务
begin
commit试哈

[faisun]

mysql> delimiter $;
mysql> create procedure doloop()
-> begin declare v int default 10000;
-> while v>0 do
-> insert into t values(v);
-> set v=v-1;
-> end while;
-> end $;

mysql> delimiter ;

mysql> call doloop;
Query OK, 1 row affected (0.31 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.00 sec)

------------------------------------
不是吧, 我才 0.31 秒,你什么机器啊....

[faisun]

mysql> delimiter $;
mysql> drop procedure doloop;

mysql> create procedure doloop(in v int(11))
    -> BEGIN
    -> truncate table t;
    -> while v>0 do
    -> insert into t values (v);
    -> set v = v-1;
    -> end while;
    -> end $;

mysql> delimiter ;

mysql> call doloop(100000);
Query OK, 1 row affected (2.42 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.00 sec)

[懒得去死]

还有，如果你想要你自己的存储过程来实现批量插入，建议改变表引擎为MYISAM，而且屏蔽掉索引信息。这样插入速度简直就是天和地的区别！

[懒得去死]

看我的测试。MYSQL 一秒都不到。

mysql> create table t (num int not null) engine myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE PROCEDURE  `doloop`()
    -> BEGIN
    ->   DECLARE v INT default 10000;
    ->   WHILE v >  0 DO
    ->     insert into t values(v);
    ->     SET v = v - 1;
    ->   END WHILE;
    -> END$$
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> DELIMITER ; 
mysql> 
mysql> call doloop();
Query OK, 1 row affected (0.42 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|    10000 | 
+----------+
1 row in set (0.00 sec)

mysql>