python实现myql表字段自适应并发插入1000万条数据

进行大数据开发时，我们常常需要造数据去进行性能验证，本文我们将介绍两种方法往mysql中插入1000万条随机数据（可以自己指定数据量），两个方法均可以达到目的，其中方法一为顺序执行，耗时较长；方法二为并发插入，耗时短，同样插入1000万条数据，在8核情况下，方法二耗时约为方法一的1/2。

我们以下面库表结构为例：

# 创建mysql数据库
CREATE DATABASE IF NOT EXISTS test_db;
 
# 创建表
USE test_db;
CREATE TABLE `alex_datagen2mysql` (
  `param_a` int not null,
  `param_b` int not null,
  `param_d` int not null,
  `param_e` int not null,
  `param_f` int not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

方法一：顺序（循环）插入

# -*- coding: utf-8 -*-
import datetime
import random
import pymysql

global db


def mysql_connect(database, table, host='localhost', user='root', password='123456', port=3306):
    # 打开数据库连接
    global db
    db = pymysql.connect(host=host,
                         user=user,
                         password=password,
                         database=database,
                         port=port)

    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    print("mysql已连接")
    # 使用 execute()  方法执行 SQL 查询
    sql = "desc %s;" % table
    cursor.execute(sql)
    # 获取表字段名
    fields = [field[0] for field in cursor.fetchall()]
    return fields


def mysql_exit():
    # 关闭数据库连接
    global db
    db.commit()
    db.close()
    print('mysql退出了')


def data2mysql(database, table, nums, host='localhost', user='root', password='123456', port=3306):
    global db
    # 获取表字段名
    fileds = mysql_connect(database, table, host, user, password, port)
    # 获取字段个数
    data_len = len(fileds)
    # 拼接要插入的数据 -- 这里以变量 %s 通配并以()封装
    res = ','.join(['%s' for _ in range(data_len)])
    metadata = '(' + res + ')'
    # 组装SQL插入语句
    sql = "insert into %s values%s" % (table, metadata)
    # 生成nums条数据，每行为data_len个字段
    data = data_gen(nums, data_len)
    cursor = db.cursor()
    for meta in data:
        # 顺序执行SQL插入语句
        cursor.execute(sql % meta)
    mysql_exit()
    return '插入完毕'


# 随机生成数据，col为要生成的数据行数，row为数据字段数
def data_gen(col, row):
    data = list()
    for i in range(col):
        data.append(tuple([str(random.randint(0, 1000)) for _ in range(row)]))
    return data


if __name__ == '__main__':
    # 开始时间
    startTime = datetime.datetime.now()
    # 顺序插入1000万条数据
    print(data2mysql("db_test", "alex_datagen2mysql", 10000000))
    # 结束时间
    endTime = datetime.datetime.now()
    # 执行耗时
    print(endTime - startTime)

方法二：并发插入（推荐）

# -*- coding: utf-8 -*-
import datetime
import random
import pymysql
from multiprocessing import cpu_count, Pool

global db


def mysql_connect(database, table, host='localhost', user='root', password='123456', port=3306):
    # 打开数据库连接
    global db
    db = pymysql.connect(host=host,
                         user=user,
                         password=password,
                         database=database,
                         port=port)

    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    print("mysql已连接")
    # 使用 execute()  方法执行 SQL 查询
    sql = "desc %s;" % table
    cursor.execute(sql)
    # 获取表字段名
    fields = [field[0] for field in cursor.fetchall()]
    return fields


def mysql_exit():
    # 关闭数据库连接
    global db
    db.commit()
    db.close()
    print('mysql退出了')


def data2mysql(database, table, nums, host='localhost', user='root', password='123456', port=3306):
    global db
    # 获取表字段名
    fileds = mysql_connect(database, table, host, user, password, port)
    # 获取字段个数
    data_len = len(fileds)
    # 拼接要插入的数据 -- 这里以变量 %s 通配并以()封装
    res = ','.join(['%s' for _ in range(data_len)])
    metadata = '(' + res + ')'
    # 组装SQL插入语句
    sql = "insert into %s values%s" % (table, metadata)
    # 生成nums条数据，每行为data_len个字段
    data = data_gen(nums, data_len)
    cursor = db.cursor()
    # 顺序执行SQL插入语句，此处可优化为拼接逻辑
    for meta in data:
        # 顺序执行SQL插入语句
        cursor.execute(sql % meta)
    mysql_exit()
    return '插入完毕'


# 随机生成数据，col为要生成的数据行数，row为数据字段数
def data_gen(col, row):
    data = list()
    for i in range(col):
        data.append(tuple([str(random.randint(0, 1000)) for _ in range(row)]))
    return data


if __name__ == '__main__':
    # 开始时间
    startTime = datetime.datetime.now()
    # 并发插入1000万条数据
    p = Pool(cpu_count())
    # 此处可优化为多个进程在数据库建立一次连接条件下执行，当前开了8个连接数，会产生一定耗时
    for _ in range(cpu_count()):
        p.apply_async(data2mysql, ("db_test", "alex_datagen2mysql", 1250000))
    # 关闭进程池
    p.close()
    p.join()
    # 结束时间
    endTime = datetime.datetime.now()
    # 执行耗时
    print(endTime - startTime)