[图片] MySQL 配置 修改配置文件:sudo vim /etc/mysql/my.cnf,  避免插入数据时出现内存表 is full 的错误。 添加如下内容: [mysqld] # 避免内存表if full, 自行设置 max_heap_table_size = 512M 重启 MySQL 服务: sudo s ..

Python Mysql 存储过程 快速生成千万级测试数据

MySQL 配置

修改配置文件:sudo vim /etc/mysql/my.cnf,  避免插入数据时出现内存表 is full 的错误。
添加如下内容:

[mysqld]
# 避免内存表if full, 自行设置
max_heap_table_size = 512M

重启 MySQL 服务: sudo service mysql restart

创建数据表

创建一个商品数据表 goods,
商品分类表和商品品牌表需要独立建的,
这里添加到商品表里面,是为了测试分表
操作对查询性能的影响,请忽视。

InnoDb 引擎表

DROP TABLE IF EXISTS goods;
CREATE TABLE goods
(
   goods_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   goods_name VARCHAR(30) NOT NULL DEFAULT '' COMMENT '商品名称',
   goods_price DECIMAL(10, 2) NOT NULL DEFAULT 0.0 COMMENT '商品价格',
   goods_cate VARCHAR(30) NOT NULL DEFAULT ''  COMMENT '商品分类名称',
   goods_brand VARCHAR(30) NOT NULL DEFAULT '' COMMENT '商品品牌名称',
   goods_order TINYINT(1) NOT NULL DEFAULT 0 COMMENT '排序字段',
   create_time INT(11) NOT NULL DEFaULT 0 COMMENT '创建时间',
   update_time INT(11) NOT NULL DEFaULT 0 COMMENT '更新时间',
   PRIMARY KEY(goods_id)
   # INDEX order_index (`goods_order`) 暂时不加索引,优化的时候再加, 测试性能差别
)ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COMMENT '商品表';

Memory 引擎表

DROP TABLE IF EXISTS goods_memory;
CREATE TABLE goods_memory
(
   goods_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   goods_name VARCHAR(30) NOT NULL DEFAULT '' COMMENT '商品名称',
   goods_price DECIMAL(10, 2) NOT NULL DEFAULT 0.0 COMMENT '商品价格',
   goods_cate VARCHAR(30) NOT NULL DEFAULT ''  COMMENT '商品分类名称',
   goods_brand VARCHAR(30) NOT NULL DEFAULT '' COMMENT '商品品牌名称',
   goods_order TINYINT(1) NOT NULL DEFAULT 0 COMMENT '排序字段',
   create_time INT(11) NOT NULL DEFaULT 0 COMMENT '创建时间',
   update_time INT(11) NOT NULL DEFaULT 0 COMMENT '更新时间',
	 PRIMARY KEY(goods_id)
	 # INDEX order_index (goods_order) 暂时不加索引,优化的时候再加, 测试性能差别
)ENGINE = MEMORY AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COMMENT '内存商品表';

创建存储过程

使用存储过程的插入速度较快,原因在与存储过程预编译并保存在数据库中,而 SQL 运行过程需要经历几个阶段。

CREATE DEFINER=`root`@`localhost` PROCEDURE `add_goods`(
    IN g_name VARCHAR (30),
    IN g_price DECIMAL(10, 2),
    IN g_cate VARCHAR(30),
    IN g_brand VARCHAR(30),
    IN g_order TINYINT(1),
    IN c_time INT(11),
    IN u_time INT(11)
    )
BEGIN  
   INSERT INTO goods_memory(goods_name, goods_price, goods_cate, goods_brand, goods_order, create_time, update_time)
    VALUES( g_name, g_price, g_cate, g_brand, g_order, c_time, u_time);
END

该除了不能生孩子的 Python 上场了

# _*_coding:utf-8_*_
"""
File: main.py
Time: 2019/8/20 21:07
Author: ClassmateLin
Email: 406728295@qq.com
Desc: 
"""
import asyncio
import aiomysql
import random
import decimal
import time

pool = ''


async def consumer(num: int):
    """
    生成数据,并调用存储过程插入数据
    :param num: 需要生成的数据条数
    :return: 
    """
    brands = ['华为', '小米', '中兴', '华硕', '戴尔', '三星', '金士顿', '耐克', '新百伦']
    categories = ['手机', '电脑', '配件', '箱包', '服饰', '鞋子', '家具']
    for i in range(num):
        async with pool.acquire() as conn:
            async with conn.cursor() as cur:
                try:
                    goods_name = '商品' + str(i)
                    goods_cate = random.choice(categories)
                    goods_brand = random.choice(brands)
                    goods_order = random.randint(0, 9)
                    goods_price = decimal.Decimal(random.randint(100, 1000000))
                    create_time = update_time = time.time()
                    # 调用存储过程插入数据到内存表
                    await cur.callproc('add_goods', (goods_name, goods_price, goods_cate,
                                                     goods_brand, goods_order, create_time, update_time))
                    print('插入第{}条数据成功'.format(str(i)))
                except aiomysql.Error as e:
                    print('mysql error {}: {}'.format(e.args[0], e.args[1]))


async def main(loop, num):
    global pool
    database = {
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'root',
        'password': 'root',
        'db': 'test',
        'loop': loop,
        'charset': 'utf8',
        'autocommit': True
    }
    pool = await aiomysql.create_pool(**database)
    asyncio.ensure_future(consumer(num))

if __name__ == '__main__':
    max_num = 10000 * 1000  # 1千万
    loop = asyncio.get_event_loop()
    asyncio.ensure_future(main(loop, max_num))
    loop.run_forever()

微信图片 20190820214351.png

内存表数据插入到普通表

INSERT INTO goods SELECT * goods_memory

  • Python

    Python 是一种面向对象、直译式电脑编程语言,具有近二十年的发展历史,成熟且稳定。它包含了一组完善而且容易理解的标准库,能够轻松完成很多常见的任务。它的语法简捷和清晰,尽量使用无异义的英语单词,与其它大多数程序设计语言使用大括号不一样,它使用缩进来定义语句块。

    335 引用 • 484 回帖 • 667 关注
  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    434 引用 • 468 回帖 • 895 关注
回帖   
请输入回帖内容...