插入100万条记录花费的时间太长MYSQL

Question

我有一个简单的表格测试，包含三列

id -> primary key (auto increment)
name -> varchar
age -> int

我正在使用一个简单的存储过程，将100万条数据填充到表中。

drop PROCEDURE if  EXISTS big_data;
CREATE PROCEDURE big_data()
BEGIN
    DECLARE i int DEFAULT 1;
    WHILE i <= 10000000 DO
        INSERT INTO test(id, name, age) VALUES (i, 'name', 34);
        SET i = i + 1;
    END WHILE;
END;

CALL big_data(); 

我面临的问题是使用这个简单的架构插入100万条记录需要近6到7个小时。我想知道如何快速插入数据？

我不想使用LOAD DATA INFILE查询，也没有更改my.ini文件中的任何设置。

只是想知道为什么插入速度太慢？

系统规格

    Mysql version 5.5.25a
    innodb_version =1.1.8
    System = 16GB RAM / 8 cores @ 2.70 GHz

- Ali

---

考虑到这是一种非常低效的插入数据方式，你的数据库实际上每秒能够插入几百行数据。这并不算太糟糕。考虑到机器上大多数核心对于这种串行插入没有任何好处（它是单线程的）...简而言之，它很慢是因为你使用了错误的数据库方法。 - Assaf Lavie

我没有明白你使用了错误的数据库@AssafLavie，还能否建议我使用更高效的方式插入数据？ - Ali

不是使用错误的数据库，而是以错误的方式使用数据库。进行批量插入或使用LOAD DATA命令。这就是它的作用。 - Assaf Lavie

如果id是自动递增的，为什么需要插入它？ - Strawberry

Answer 1

一次性插入100行比插入100个1行的 INSERTs 快10倍:

INSERT INTO foo (a, b, c) VALUES (1,2,3), (4,5,6), ...;

LOAD DATA 可能会更快。

- Rick James

Answer 2

尝试将您的查询作为单个字符串并一次性执行它

drop PROCEDURE if  EXISTS big_data;
CREATE PROCEDURE big_data()
BEGIN
    DECLARE total_count TEXT DEFAULT '';
    DECLARE i int DEFAULT 1;
    WHILE i <= 10000000 DO
        SET total_count = total_count + '(' + i+ ', name, 34),';
        SET i = i + 1;
    END WHILE;
    SET total_count = TRIM(TRAILING ',' FROM total_count);
    INSERT INTO test(id, name, age) VALUES ' + total_count;
END;

- Double H

---

这个目前被踩到了-1，我想知道为什么。有没有特别的原因说明这是一个不好的想法？我和提问者有类似的问题，正在考虑尝试类似的方法。 - Mark Verrey

@MarkVerrey，无法追踪是谁给它点了踩，但如果它对你有帮助，你总可以给它一个+1。 - dewd