mysql循环插入时卡死_批量创建百万条数据，会让程序卡死，数据库崩溃嘛，为什么我们的技术说创建一万条，写for循环都会卡死？...

我现在坐在厦门的某个图书馆里，操作我2015年租用的位于杭州的阿里云上一台4G内存，2核CPU的服务器(运行着四个网站，磁盘空间所剩无几)，给你看看Mysql插入100万条(和1000万条)的时间：

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 134332

Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(*) from information_schema.columns;

+----------+

| count(*) |

+----------+

| 36472 |

+----------+

1 row in set (0.72 sec)

mysql> create table tt select * from information_schema.columns;

Query OK, 36493 rows affected (5.44 sec)

Records: 36493 Duplicates: 0 Warnings: 0

mysql> insert into tt select * from tt;

Query OK, 36493 rows affected (0.73 sec)

Records: 36493 Duplicates: 0 Warnings: 0

mysql> insert into tt select * from tt;

Query OK, 72986 rows affected (1.30 sec)

Records: 72986 Duplicates: 0 Warnings: 0

mysql> insert into tt select * from tt;

Query OK, 145972 rows affected (2.56 sec)

Records: 145972 Duplicates: 0 Warnings: 0

mysql> insert into tt select * from tt;

Query OK, 291944 rows affected (5.76 sec)

Records: 291944 Duplicates: 0 Warnings: 0

mysql> insert into tt select * from tt;

Query OK, 583888 rows affected (12.84 sec)

Records: 583888 Duplicates: 0 Warnings: 0

mysql> select count(*) from tt;

+----------+

| count(*) |

+----------+

| 1167776 |

+----------+

1 row in set (1.76 sec)

mysql> insert into tt select * from tt;

Query OK, 1167776 rows affected (29.35 sec)

Records: 1167776 Duplicates: 0 Warnings: 0

mysql> insert into tt select * from tt;

Query OK, 2335552 rows affected (58.10 sec)

Records: 2335552 Duplicates: 0 Warnings: 0

mysql> insert into tt select * from tt;

Query OK, 4671104 rows affected (2 min 11.81 sec)

Records: 4671104 Duplicates: 0 Warnings: 0

mysql> select count(*) from tt;

+----------+

| count(*) |

+----------+

| 9342208 |

+----------+

1 row in set (16.31 sec)

mysql>

生成100万条记录，大约20多秒(把那几句的执行时间加起来)。

生成1000万条记录，大约4分钟。

建议：

1、如果是巨量原始数据且在外部，那先用LOAD DATA INFILE导入数据库。如果仅仅是要造些伪数据，那随便拿数据库里现有表生成一些。

2、能用SQL的，一定不要用存储过程。

3、能用存储过程的，一定不要用外部程序for loop。

方法很重要。你的技术人员，恐怕是拿编程的思路来操作数据库了。