mysql增加测试数据

背景

在进行查询操作的性能测试或者sql优化时，我们经常需要在线下环境构建大量的基础数据供我们测试，模拟线上的真实环境。

数据量计算

以项目运营一段时间后，比较理想的运营计划来估计。这里以一个多商户商城运营半年后，可能达到的数据量来计算：半年后发展店铺5000家，每个商家平均有50个商品，10个分类，50用户，平均每天30个订单。数据最多的是订单表每天30万条订单记录。

• 店铺表记录: 5000
• 用户表记录：25万
• 商品记录：25万
• 分类记录：5万
• 订单记录：1000万（5000家商铺，每家每天30个订单，一个月就是500万条，考虑到发展到峰值需要时间，就以1000万表计算）

方案选择

构建数据可能有以下方案：

• 编写代码，通过代码批量插库（编些费时，运行缓慢）
• 编写存储过程和函数执行
• 临时数据表方式（简单高效）
  下面以临时数据表方式来实现

实现步骤

创建临时数据表

CREATE TABLE tmp_table (
    id INT,
    PRIMARY KEY (id)
);

创建数据导入临时表

用 python或者bash 生成 1000w 记录的数据文件（python一会就会生成完）大约79M

python -c "for i in range(1, 1+10000000): print(i)" > base.txt

mysql> load data infile '/var/lib/mysql-files/base.txt' replace into table tmp_table;
Query OK, 10000000 rows affected (2 min 34.08 sec)
Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0

我这运行了2分34秒，1000万条数据加入了临时表了。
运行load data infile可能会报错：The MySQL server is running with the --secure-file-pri option so it cannot execute this.
那就执行sql

show variables like '%secure%';

把base.txt放到secure_file_priv允许执行的路径就可以了

创建测试数据

用一个联表sql来创建测试数据。where条件规定记录数。select查询tmp_table的id,使用CONCAT，Rand,unix_timestamp(NOW())等mysql函数及字符串来作为测试表的插入数据。

insert into ys_store
select
    null,
    Rand() * 100000,
    1,
    CONCAT('store_name', id),
    CONCAT('137', id),
    md5('123456'),
    '联系人',
    Rand() * 100000,
    null,
    '湖南',
    null,
    '长沙',
       null,
    '雨花区',
    '湖南省长沙市宁乡市金洲大道',
    '/uploads/15823704704382.JPG',
    1,
    1000,
    800,
    700,
    '16:00',
    unix_timestamp(NOW()) ,
       50,
    "112.99681627227",
    "28.184017469921",
       "智慧树幼儿园(桂阳县龙潭西路筷子厂)",
       0,
       "8:00",
       "21:00",
       "13575099354",
       10.20,
       10.20,
       5
from
    tmp_table
where id <= 5000;

总结

查入1000W的订单表花了55分钟。比起编写代码数度要快很多了。实现起了也比较简单。

相关资料

https://www.jb51.net/article/161712.htm