MySQL常用操作之存过实现批量造数

MySQL常用操作之存过实现批量造数

• 简介
• 实现过程
• • 创建表
  • 创建存过
  • • 方式一
    • 方式二
    • 方式三(推荐)
• 总结
• 参考链接

简介

• 背景

  在进行SQL性能优化时，测试环境往往需要构造大量数据以模拟生产情况，下面主要介绍MySQL创建存过批量造数，并分析不同实现方式的执行效率。

实现过程

创建表

• 建表语句

  分页时标准列分主键列、索引列、普通列3种场景，所以，测试表需要包含这3种场景

  drop table if exists `test`.`t_model`;Create table `test`.`t_model`( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',`uid` bigint COMMENT '业务主键',`modelid` varchar(50) COMMENT '字符主键',`modelname` varchar(50) COMMENT '名称',`desc` varchar(50) COMMENT '描述',primary key (`id`),UNIQUE index `uid_unique` (`uid`),key `modelid_index` (`modelid`) USING BTREE) ENGINE=InnoDB charset=utf8mb4 ROW_FORMAT=DYNAMIC;

创建存过

方式一

• 实现思路

  直接循环单条插入1W条数据

• 存过脚本

  -- 创建存过drop procedure if exists my_procedure; delimiter //create procedure my_procedure()beginDECLARE n int DEFAULT 1;WHILE n < 10001 DOinsert into t_model (uid,modelid,modelname,`desc`) value (n,CONCAT('id20170831',n),CONCAT('name',n),'desc'); set n = n + 1;END WHILE;end//　　　　　　　　　　　　　　　delimiter ;-- 调用存过call my_procedure();

• 结果分析

  插入1W条数据，执行时间大概在6m7s，按照这个速度，要插入1000W级数据，估计要跑几天。

方式二

• 实现思路

  添加事务提交，测试每1000条就commit一下

• 存过脚本

  -- 创建存过delimiter //create procedure u_head_and_low_pro()beginDECLARE n int DEFAULT 1;WHILE n < 10001 DOinsert into t_model (uid,modelid,modelname,`desc`) value (n,CONCAT('id20170831',n),CONCAT('name',n),'desc'); set n = n + 1;if n % 1000 = 0 thencommit;end if;END WHILE;end//delimiter ;-- 调用存过call u_head_and_low_pro();

• 结果分析

  执行时间 6 min 16 sec，与不加commit执行差别不大，看来，这种方式做批量插入，性能是很低的。

方式三(推荐)

• 实现思路

  使用存储过程生成批量插入语句执行批量插入

• 存过脚本

  -- 创建存过drop procedure IF EXISTS u_head_and_low_pro;delimiter $$create procedure u_head_and_low_pro()beginDECLARE n int DEFAULT 1;set @exesql = 'insert into t_model (uid,modelid,modelname,`desc`) values ';set @exedata = '';WHILE n < 10001 DOset @exedata = concat(@exedata,"(",n,",","'id20170831",n,"','","name",n,"','","desc'",")");if n % 1000 = 0 thenset @exesql = concat(@exesql,@exedata,";");prepare stmt from @exesql;execute stmt;DEALLOCATE prepare stmt;commit; set @exesql = 'insert into t_model (uid,modelid,modelname,`desc`) values ';set @exedata = "";elseset @exedata = concat(@exedata,',');end if;set n = n + 1;END WHILE;end;$$ delimiter ;-- 调用存过call u_head_and_low_pro();

• 结果分析

  执行时间 3.308s。

总结

• 效率分析

  批量插入时，使用insert的values批量方式插入，执行速度大大提升，推荐使用。

参考链接

• mysql 循环批量插入的实例代码详解

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

• mysql 快速造数据sql

  https://blog.csdn.net/fzy629442466/article/details/103391688

• MySQL造数据，批量插入数据脚本

  https://www.cnblogs.com/gaohongyu/p/14119691.html

• 利用MYSQL存储过程批量造数据

  https://blog.csdn.net/weixin_39444878/article/details/86498260

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！