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导入功能

3.4 从已有的表中拷贝

sql
INSERT INTO user_has_index SELECT * FROM users

4、耗时对比

4.1 文件导入

所有字段均无索引
gender字段有索引

4.2 从已有的表中拷贝

4.3 储存过程

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

4.4 python批量插入

python批量插入（没计算时间，等了也有几分钟，很慢）

5、总结

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

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