背景

需要往数据库中导入千万级别的数据，数据存储在sql文件中，文件中都是单独的sql语句。

初版解决方法

使用python开线程，多文件同时读取，执行sql语句，测试预估5000条/分，速度还行，但是需要大量时间。

sql优化

思路

影响存储速度的主要三个因素：网络IO速度、本地读取速度、cpu读取速度，三者耗时比较：网络IO速度>本地读取速度>cpu读取速度，所以需要尽可能的减少网络请求，将所有的压力给cpu，这样可以最大程度的减少网络请求。

方法

  1. 使用线程多个文件同时读取，这样可以减少本地的时间消耗
  2. 将每个SQL文件中的所有sql语句转换为一个sql语句
  3. 耐心等待

原理

加入每个sql文件中有1w条数据，也就是1w个sql语句，当不优化时，程序每个文件会发送1w次（甚至更多）网络请求，这个耗时是很大很大的，如果我们在本地将这些sql语句转化成一个sql语句，这样我们只需要发送1次网络请求，发送之后，数据就会交给服务器端的mysql服务器进行处理，也就是交给了cpu进行处理，这样处理的速度相对于开始的方式提升了很多很多。

对比

原方法：5000条/分
优化后：30s/提交完成

预估原方法需要用时：24h+
新方法用时：30m

参考代码

import os
import pymysql
from concurrent.futures import ThreadPoolExecutor, as_completed

files = r'存放sql文件的目录'

# 读取所有的.sql文件
def get_sql_name():
    names = os.listdir(files)

    return names

# 读取每个sql文件中所有sql语句
def open_sql(sql_file_name):
    sql_list = []
    for sql in open(files+'\\'+sql_file_name,'r',encoding='utf-16'):
    	# 有单行的; 所以过滤一下
        if ';' not in sql:
        	# 去掉换行后的sql
            sql1 = sql_set(str(sql).replace('\n',''))
            if sql1:
                sql_list.append(sql1)
            else:
                print(sql)
    # 如果当前文件中有合法的sql语句
    if sql_list:
    	# sql拼接一下 这里用的切片 也可以用python的内置方法
        zsql = 'INSERT 表明 (各个字段) VALUES '+(','.join(sql_list))+';'
        mysql_select(zsql)
    # print(zsql)

# sql语句处理
def sql_set(sql):
    # print()
    # 这里用的切片 可以用正则匹配
    return str(sql).split('INSERT 表明 (各个字段) VALUES ')[-1].replace('\\','')

# sql语句提交
def mysql_select(sql):

    conn = pymysql.connect(
        host='服务器ip',
        port=端口,
        user='用户名',
        passwd='密码',
        db='库名',
    )
    try:
        # print('执行')
        cur = conn.cursor()
        cur.execute(sql)
        conn.commit()
        # print('插入成功')
    except:
        conn.rollback()
        print("插入数据失败")
        error_sql(sql)
    conn.close()

# 如果出现错误的sql语句 存一下 后期好处理
def error_sql(sql):
    with open("D:\errorsql\error.txt", "a") as f:
        f.write(sql+'\n')


if __name__ == '__main__':
    object_list = (get_sql_name())
    with ThreadPoolExecutor(max_workers=20) as t:  # max_workers 最大线程数
        obj_list = []
        for y in object_list:  # object_list 传进来的 list数组
            print(y)
            objc = t.submit(open_sql, y)  # objFunc 需要开启多线的函数 y是参数 参数可以有多个
            obj_list.append(objc)
        for future in as_completed(obj_list):
            data = future.result()
    # for i in object_list:
    #     print(i)
    #     open_sql(i)