编辑
2023-07-27
数据库
00
请注意,本文编写于 221 天前,最后修改于 221 天前,其中某些信息可能已经过时。

目录

1、背景
2、建表语句
3.1 存储过程
3.2 python生成insert语句批量提交
3.3 使用文件导入
3.3.1 生成csv文件
3.3.2 导入文件
3.4 从已有的表中拷贝
4、耗时对比
4.1 文件导入
4.2 从已有的表中拷贝
4.3 储存过程
4.4 python批量插入
5、总结

1、背景

在技术群里面有个讨论,枚举字段是否需要增加索引的问题
有人说必须要,有人说加了索引没什么用。
talk is cheap, show me the data. 讨论那么多,还是得先有数据来验证。
说到这里,你可以先猜猜哪种最快,是储存过程最快的吗?

2、建表语句

数据库字段使用字符串代替枚举,由程序控制字段的值

sql
create table users ( id int auto_increment primary key, name varchar(255) not null, age int not null, gender varchar(10) not null );

3、插入数据对比

3.1 存储过程

sql
CREATE PROCEDURE GenerateData() BEGIN DECLARE i INT DEFAULT 1; WHILE i <= 1000000 DO INSERT INTO users (name, age, gender) VALUES ( CONCAT('User', i), FLOOR(18 + RAND() * (100 - 18)), CASE WHEN i % 3 = 1 THEN 'male' WHEN i % 3 = 2 THEN 'female' ELSE 'unknown' END ); SET i = i + 1; END WHILE; END CALL GenerateData();

3.2 python生成insert语句批量提交

python
import pymysql import random # 连接数据库 conn = pymysql.connect(host='localhost',port=3306, user='root', password='root', db='my_database', charset='utf8mb4') cur = conn.cursor() try: # 开启事务 conn.begin() for i in range(1, 1000001): name = f'User{i}' age = random.randint(18, 100) gender = 'male' if i % 3 == 1 else 'female' if i % 3 == 2 else 'unknown' # 执行插入语句 cur.execute("INSERT INTO users (name, age, gender) VALUES (%s, %s, %s)", (name, age, gender)) # 提交事务 conn.commit() except Exception as e: print(f"An error occurred: {e}") # 发生错误时回滚 conn.rollback() finally: # 关闭游标和连接 cur.close() conn.close()

3.3 使用文件导入

3.3.1 生成csv文件

python
import csv import random # 生成包含1,000,000条用户记录的CSV文件 with open('users.csv', 'w', newline='') as file: writer = csv.writer(file) for i in range(1, 1000001): name = f'User{i}' age = random.randint(18, 100) gender = 'male' if i % 3 == 1 else 'female' if i % 3 == 2 else 'unknown' writer.writerow([name, age, gender])

3.3.2 导入文件

sql
# 数据库需要能读取到这个路径,否则会报错 # [2023-07-26 23:08:28] [HY000][13] (conn=151) Can't get stat of '/tmp/users.csv' (Errcode: 2 "No such file or directory") # 如果使用的容器,需要把宿主机上的文件拷贝到容器 # 下面的例子是把宿主机/local/users.csv 拷贝到容器的根目录/ # docker cp /local/users.csv db-docker-compose-db-1:/ LOAD DATA INFILE '/users.csv' INTO TABLE users FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (name, age, gender);

如果数据库不在本地,不方便拷贝。可以使用IDEA自带的database导入功能

image.png

3.4 从已有的表中拷贝

sql
INSERT INTO user_has_index SELECT * FROM users

4、耗时对比

4.1 文件导入

  • 所有字段均无索引 没有索引

  • gender字段有索引 有索引

4.2 从已有的表中拷贝

image.png

4.3 储存过程

太慢啦,我手动中断,此时有57w左右的数据

4.4 python批量插入

python批量插入(没计算时间,等了也有几分钟,很慢)

5、总结

  • 存储过程并不是最快的,甚至很慢
  • 文件导入是最快的
  • 使用Python直接批量insert不可取

你猜对了吗?
或者还有更快的方案,比如说分批+事务。
欢迎留言和我交流。

本文作者:花菜

本文链接:

版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!