MySQL（3）如何快速插入大量数据

Posted on Edited on In Word count in article: 1.2k Reading time ≈ 4 mins.

在学习和使用MySQL的过程中，可能会遇到需要学习或者解决MySQL慢查询的情况。

为了模拟出线上数据库大数据量的情况，我们可以在我们自己的电脑上面批量插入几百甚至上千万条数据。

但是不同的插入方法，插入效率可能不同，快的可能需要几十秒，慢的话几个小时可能都打不住。

造数据

创建用户表结构如下。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create database testdb;
use testdb;
create user 'wr_user'@'%' identified by 'Wr_user_pw123!';
grant all privileges  on testdb.* to 'wr_user'@'%';
CREATE TABLE `user` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
    `name` varchar(100) NOT NULL COMMENT '名称',
    `phone` varchar(20) NOT NULL COMMENT '电话',
    `gender` int NOT NULL COMMENT '分类',
    `age` int NOT NULL COMMENT '年龄',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户信息';

使用的MySQL版本是8.0.30，系统是CentOS Linux release 7.6.1810 (Core)，1核2G内存。

生成数据的python3脚本generate.py，需要先安装python的faker依赖。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
from faker import Faker
import sys
# 简体中文：zh_CN
faker = Faker(locale="zh_CN")
# 指定随机种子，确保每次生成的数据都是一致的
Faker.seed(4321)

def get_user():
    return {
        'name': faker.name(),
        'phone': faker.phone_number(),
        'age': faker.random_int(18, 35),
        'gender': faker.random_int(0,3)
    }


def format_user(user):
    return ('insert into user(`name`, `phone`, `gender`, `age`) value(\'%s\',\'%s\',\'%s\',\'%s\');' %
            (user['name'], user['phone'], user['age'], user['gender']))

def main():
    size = 10
    if len(sys.argv) >= 2:
        size = int(sys.argv[1])
    for _ in range(size):
        print(format_user(get_user()))


if __name__ == '__main__':
    main()

运行脚本生成10万条数据。

1
$ python3 generate.py 100000  | tee user.sql > /dev/null

数据样式如下，均为单独的insert语句。

1
2
3
insert into user(`name`, `phone`, `gender`, `age`) value('王鑫','13951029074','31','1');
insert into user(`name`, `phone`, `gender`, `age`) value('潘淑兰','18727018273','26','1');
...

将数据插入到数据库。

1
$ mysql -uwr_user -pWr_user_pw123! testdb < user.sql

使用这个方法插入10万条数据，花了大概超过10分钟！

如果想要插入1000万条数据，估计要花掉十几个小时。

优化（一条语句插入多条数据）

修改python的生成代码逻辑。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
from faker import Faker
import sys
# 简体中文：zh_CN
faker = Faker(locale="zh_CN")
# 指定随机种子，确保每次生成的数据都是一致的
Faker.seed(4321)

def get_user():
    return {
        'name': faker.name(),
        'phone': faker.phone_number(),
        'age': faker.random_int(18, 35),
        'gender': faker.random_int(0,3)
    }

def format_user(user, last):
    return '(\'%s\',\'%s\',\'%s\',\'%s\')%s' % (user['name'], user['phone'], user['age'], user['gender'], last)


def main():
    size = 10
    if len(sys.argv) >= 2:
        size = int(sys.argv[1])
    print('insert into user(`name`, `phone`, `gender`, `age`) values')
    for _ in range(size-1):
        print(format_user(get_user(), ','))
    print(format_user(get_user(), ';'))


if __name__ == '__main__':
    main()

生成数据。

1
$ python3 generate2.py 100000 |tee user2.sql > /dev/null

生成数据如下。

1
2
3
4
insert into user(`name`, `phone`, `gender`, `age`) values
('王鑫','13951029074','31','1'),
('潘淑兰','18727018273','26','1'),
...

导入数据库中。

1
$ mysql -uwr_user -pWr_user_pw123! testdb < user2.sql

整体执行时间只有大概2秒钟，效率提升了两个数量级。

如果尝试生成100万个数据，看看时间消耗。

1
$ python3 generate2.py 1000000 |tee user3.sql > /dev/null

再次导入数据库。

1
2
$ mysql -uwr_user -pWr_user_pw123! testdb < user3.sql
ERROR 1041 (HY000) at line 1: Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

此时100万条数据直接插入，会导致MySQL内存耗尽，无法直接插入。

可以把这100万条数据分成10个每条10万个数据的insert语句，再进行插入。

再优化（load data）

MySQL load data 语句能快速将一个文本文件的内容导入到对应的数据库表中（一般文本的一行对应表的一条记录）。

使用load data，效率比insert要高更多。

先修改一下python脚本。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
from faker import Faker
import sys
# 简体中文：zh_CN
faker = Faker(locale="zh_CN")
# 指定随机种子，确保每次生成的数据都是一致的
Faker.seed(4321)

def get_user(id):
    d = faker.date_this_month()
    return {
        'id' : id,
        'name': faker.name(),
        'phone': faker.phone_number(),
        'age': faker.random_int(18, 35),
        'gender': faker.random_int(0, 3),
        'create_time': d,
        'update_time': d
    }

def format_user(user):
    return '%s,%s,%s,%s,%s,%s,%s' % \
           (user['id'], user['name'], user['phone'], user['age'], user['gender'], user['create_time'], user['update_time'])


def main():
    size = 10
    if len(sys.argv) >= 2:
        size = int(sys.argv[1])
    for i in range(size):
        print(format_user(get_user(i+1)))


if __name__ == '__main__':
    main()

生成如下的数据。

1
2
3
4
1,赵鑫,13951029074,31,1,2023-06-07,2023-06-07
2,徐兵,18701827349,24,2,2023-06-10,2023-06-10
3,毛淑华,14743566115,26,1,2023-06-11,2023-06-11
...

最后使用sql的导入语句导入mysql数据。

1
2
3
4
5
6
7
8
9
10
load data 
    local infile '/home/shuyi/work/blog-learn/mysql/user4.txt'
    into table user
    fields 
        terminated by ','
        optionally enclosed by ''
        escaped by '\\'
    lines 
        terminated by '\n'
    (id,name,phone,age,gender,create_time,update_time);

如果load失败，出现下面错误，则说明没有导入权限，需要开启这个功能。

1
2
mysql> load data ...
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

需要先在MySQL控制台设置参数。

1
2
mysql> SET GLOBAL local_infile=1;
Query OK, 0 rows affected (0.00 sec)

添加参数重新进入控制台。

1
$ mysql -uroot -p --local-infile=1

然后再次导入就能成功。

1
2
3
mysql> load data ...
Query OK, 1000000 rows affected (10.61 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

插入100万条数据，耗时仅仅10秒！