利用存储过程+事务，快速模拟数据，10分钟300万

本文链接：https://blog.csdn.net/yrsg666/article/details/110821278

drop procedure if exists myProcedure;
Delimiter $
create procedure myProcedure()
begin
   -- 定义一个变量i来控制循环次数
declare i int default 1;
       declare l int default 1;
       DECLARE luuid varchar(255) DEFAULT '';
       DECLARE devicelocation varchar(255) DEFAULT '北京';
       SET autocommit = 0;
while i <= 500000 do
                       set luuid = lower(uuid());
                       set luuid = (select replace(luuid,'-',''));
Insert Into alarm_info_action(guid,alarm_type,snapshot_images,sub_type,score,video_guid,video_name,video_location,video_ip,person_num,alarm_time,event_type,handle_status,alarm_name,device_binging_algorithm,monitoring_period,reviewers)
VALUES (luuid,(rand()*5),'http//testurl',(rand()*4),(rand()*100),luuid,'设备名称',devicelocation,'10.15.226.11',(rand()*100),1234567891234567,(RAND()*20),(RAND()*4),'告警名称',(rand()*15),'1234567891234567,1234567891234567','审核人员');
-- concat函数用于拼接数字和字符串
set i = i+1;

                       set l = RAND()*5;
                       case l
                           when 1 then
                           set devicelocation = '北京';
                           when 2 then
                           set devicelocation = '南京';
                           when 3 then
                           set devicelocation = '上海';
                           when 4 then
                           set devicelocation = '天津';
                           else
                           set devicelocation = '武汉';
                       end case;
end while ;
       commit;
end $
DELIMITER ;
-- 调用存储过程
call myProcedure();

其实还可以再优化就是，同时执行多个存储过程，直接多线程调用mysql，可以观察到CUP超过100%，证明开了多线程，同时，这样开几个会提升几倍，一般来说模拟数据的瓶颈不是IO单表的话。并且这种关闭存储过程的是只在commit的时候占用IO，时间很短。

数据插入性能
40万 4270s 关闭数据库事务前
50万 213s 关闭数据库事务，继续观察发现，插入50~100万的时候是320s,100~150s的时候是360s左右。待确认是由于并发还是数量级上去自然就慢所导致：？去掉并发150~200是312s，结果是数量级上来确实会增长，但是也跟并发有一定关系，暂不确定是什么原因，但猜测并不是IO,因为IO基本空闲99%以上。最后记录下200~250两个同时插入时候是617s，250~300万是630s.

服务器是：物理CPU 1，物理核数16，逻辑核数32 ，内存64G

begin
-- 定义一个变量i来控制循环次数
declare i int default 0;
DECLARE luuid varchar(36) DEFAULT '';

               declare personnameid int default 1;
DECLARE personnamevalue varchar(255) DEFAULT '张三';
               declare subtypeid int default 1;
               DECLARE subtypevalue varchar(36) DEFAULT '黑名单';
               declare videoGuidid int default 1;
               DECLARE videoGuidguid varchar(36) DEFAULT '2aasdfadsfbf5048f192fed33af4d23741';
               DECLARE videoNameValue varchar(100) DEFAULT '相机1';
               DECLARE videoLocationValue varchar(100) DEFAULT '位置1';
               declare facelibraryid int default 1;
               DECLARE facelibraryguid varchar(36) DEFAULT '2aasdfadsfbf5048f192fed33af4d23111';
               DECLARE facelibrarynamevalue varchar(100) DEFAULT '人脸库1';
               declare taskid int default 1;
               DECLARE taskguid varchar(36) DEFAULT '111sdfadsfbf5048f192fed33af4d23111';
               DECLARE tasknamevalue varchar(100) DEFAULT '任务1';
               declare handleid int default 1;
               DECLARE handlestatusvalue varchar(100) DEFAULT '未处理';

SET autocommit = 0;
while i <= 100 do
set luuid = lower(uuid());
set luuid = (select replace(luuid,'-',''));
Insert Into alarm_info_face_xx(guid,alarm_type,sub_type,pid,person_name,person_credential_type,person_credential_num,snapshot_image,lib_image,similar_score,video_guid,video_name,video_location,video_ip,alarm_time,face_library_guid,face_library_name,task_guid,task_name,handle_status,reviewers)
VALUES (luuid,'人脸比对',subtypevalue,luuid,personnamevalue,(rand()*7),'13092719991111111x','http://10.15.123.215:8087/test//snapshot.jpg','http://10.15.123.215:8087/test//libImage.jpg',floor(rand()*100),videoGuidguid,videoNameValue,videoLocationValue,'127.0.0.1',i,facelibraryguid,facelibrarynamevalue,taskguid,tasknamevalue,handlestatusvalue,personnamevalue);
-- concat函数用于拼接数字和字符串
set i = i+1;

set personnameid = RAND()*5;
case personnameid
when 1 then
set personnamevalue = '张三';
when 2 then
set personnamevalue = '李四';
when 3 then
set personnamevalue = '王五';
when 4 then
set personnamevalue = '赵六';
else
set personnamevalue = '小明';
end case;
                                               set subtypeid = RAND()*1;
case subtypeid
when 0 then
set subtypevalue = '黑名单';
else
set subtypevalue = '白名单';
end case;
                                               set videoGuidid = RAND()*6;
case videoGuidid
when 1 then
set videoGuidguid = '2aasdfadsfbf5048f192fed33af4d23741';
                                                       set videoNameValue = '相机1';
                                                       set videoLocationValue = '位置1';
when 2 then
set videoGuidguid = '2aasdfadsfbf5048f192fed33af4d23742';
                                                       set videoNameValue = '相机2';
                                                       set videoLocationValue = '位置2';
when 3 then
set videoGuidguid = '2aasdfadsfbf5048f192fed33af4d23743';
                                                       set videoNameValue = '相机3';
                                                       set videoLocationValue = '位置3';
when 4 then
set videoGuidguid = '2aasdfadsfbf5048f192fed33af4d23744';
                                                       set videoNameValue = '相机4';
                                                       set videoLocationValue = '位置4';
when 5 then
set videoGuidguid = '2aasdfadsfbf5048f192fed33af4d23745';
                                                       set videoNameValue = '相机5';
                                                       set videoLocationValue = '位置5';
else
set videoGuidguid = '2aasdfadsfbf5048f192fed33af4d23746';
                                                       set videoNameValue = '相机6';
                                                       set videoLocationValue = '位置6';
end case;
                                               set facelibraryid = RAND()*7;
case facelibraryid
when 1 then
set facelibraryguid = '2aasdfadsfbf5048f192fed33af4d23111';
                                                       set facelibrarynamevalue = '人脸库1';
when 2 then
set facelibraryguid = '2aasdfadsfbf5048f192fed33af4d23112';
                                                       set facelibrarynamevalue = '人脸库2';
when 3 then
set facelibraryguid = '2aasdfadsfbf5048f192fed33af4d23113';
                                                       set facelibrarynamevalue = '人脸库3';
when 4 then
set facelibraryguid = '2aasdfadsfbf5048f192fed33af4d23114';
                                                       set facelibrarynamevalue = '人脸库4';
when 5 then
set facelibraryguid = '2aasdfadsfbf5048f192fed33af4d23115';
                                                       set facelibrarynamevalue = '人脸库5';
when 6 then
set facelibraryguid = '2aasdfadsfbf5048f192fed33af4d23116';
                                                       set facelibrarynamevalue = '人脸库6';
else
set facelibraryguid = '2aasdfadsfbf5048f192fed33af4d23117';
                                                       set facelibrarynamevalue = '人脸库7';
end case;
                                               set taskid = RAND()*5;
case taskid
when 1 then
set taskguid = '111sdfadsfbf5048f192fed33af4d23111';
                                                       set tasknamevalue = '任务1';
when 2 then
set taskguid = '211sdfadsfbf5048f192fed33af4d23111';
                                                       set tasknamevalue = '任务2';
when 3 then
set taskguid = '311sdfadsfbf5048f192fed33af4d23111';
                                                       set tasknamevalue = '任务3';
when 4 then
set taskguid = '411sdfadsfbf5048f192fed33af4d23111';
                                                       set tasknamevalue = '任务4';
else
set taskguid = '511sdfadsfbf5048f192fed33af4d23111';
                                                       set tasknamevalue = '任务5';
end case;
                                               set handleid = RAND()*3;
case handleid
when 1 then
set handlestatusvalue = '未处理';
when 2 then
set handlestatusvalue = '可疑';
else
set handlestatusvalue = '放行';
end case;
end while ;
commit;
end