python提高mysql数据库插入数据效率

python提高mysql数据库插入数据效率

1、insert的时候尽量多条一起插，不要单条插。这样可以减少日志量，降低日志刷盘数据量和频率，效率提高很多。
2、在事务中进行插入。也就是每次部分commit，否则每条insert commit 创建事务的消耗也是不小的。
3、 数据插入的时候保持有序。比如Innodb用的是B+树索引，对B+树的插入如果是在索引中间就会需要树节点分裂合并，这也会有一定的消耗。

修改前：

#!/usr/bin/python3
# -*- coding: utf-8 -*-
# @Time    : 2021/4/2 17:23
# @Author  : LQ
# @FileName: test.py
# @Software: PyCharm
import datetime
import random
import time
import pymysql
# 打开数据库连接
db = pymysql.connect("localhost", "root", "root", "test")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute()  方法执行 SQL 查询
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
print("Database version : %s " % data)
# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS SQLTEST1")
# 使用预处理语句创建表
sql = """CREATE TABLE sqltest1 (
         ID int not null auto_increment,
         NAME  CHAR(20) NOT NULL,
         TIME CHAR(255),
         PRIMARY KEY(ID) )"""

cursor.execute(sql)
# 记录执行前时间
start_time = datetime.datetime.now()
print("开始时间：", start_time)
print("插入数据")
i = 0
while i < 10000:
    alphabet = 'abcdefghijklmnopqrstuvwxyz'
    name = ''.join(random.choice(alphabet) for i in range(random.randint(5, 10)))
    time1 = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    sql = "INSERT INTO sqltest1(NAME, \
       TIME) \
       VALUES ('%s','%s')" % \
          (name, time1)
    i += 1
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 执行sql语句
        db.commit()
    except:
        # 发生错误时回滚
        db.rollback()
        print('插入失败')
# 记录执行完成时间
end_time = datetime.datetime.now()
print("结束时间：", end_time)
# 计算时间差
time_d = end_time - start_time
print(time_d)
# 关闭数据库连接
db.close()

插入10000条数据耗时0:05:41.597354

修改后

#!/usr/bin/python3
# -*- coding: utf-8 -*-
# @Time    : 2021/4/2 17:23
# @Author  : LQ
# @FileName: test.py
# @Software: PyCharm
import datetime
import random
import time
import pymysql
# 打开数据库连接
db = pymysql.connect("localhost", "root", "root", "test")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute()  方法执行 SQL 查询
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
print("Database version : %s " % data)
# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS SQLTEST1")
# 使用预处理语句创建表
sql = """CREATE TABLE sqltest1 (
         ID int not null auto_increment,
         NAME  CHAR(20) NOT NULL,
         TIME CHAR(255),
         PRIMARY KEY(ID) )"""

cursor.execute(sql)
# 批量创建数据
userValues = []
i = 0
while i < 10000:
    alphabet = 'abcdefghijklmnopqrstuvwxyz'
    name = ''.join(random.choice(alphabet) for i in range(random.randint(5, 10)))
    time1 = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    userValues.append((name, time1))
    i += 1
# 记录执行前时间
start_time = datetime.datetime.now()
print("开始时间：", start_time)
print("插入数据")
try:
    sql = "INSERT INTO sqltest1(NAME, TIME) VALUE (%s,%s)"
    # 执行sql语句
    cursor.executemany(sql, userValues)
    # 执行sql语句
    db.commit()
except:
    # 发生错误时回滚
    db.rollback()
    print('插入失败')
# 记录执行完成时间
end_time = datetime.datetime.now()
print("结束时间：", end_time)
# 计算时间差
time_d = end_time - start_time
print(time_d)
# 关闭数据库连接
db.close()

插入10000条数据耗时0:00:01.357660