将MySQL去重操作优化到极致之三弹连发(二):多线程并行执行

        上一篇已经将单条查重语句调整到最优,但该语句是以单线程方式执行。能否利用多处理器,让去重操作多线程并行执行,从而进一步提高速度呢?比如我的实验环境是4处理器,如果使用4个线程同时执行查重sql,理论上应该接近4倍的性能提升。

一、数据分片

        我们生成测试数据时,created_time采用每条记录加一秒的方式,也就是最大和在最小的时间差为50万秒,而且数据均匀分布。因此先把数据平均分成4份。

1. 查询出4份数据的created_time边界值

select date_add('2017-01-01',interval 125000 second) dt1,
       date_add('2017-01-01',interval 2*125000 second) dt2,
       date_add('2017-01-01',interval 3*125000 second) dt3,
       max(created_time) dt4
  from t_source;

        查询结果如图一所示。

图一

2. 查看每份数据的记录数,确认数据平均分布

select case when created_time >= '2017-01-01' 
             and created_time < '2017-01-02 10:43:20'
            then '2017-01-01'
            when created_time >= '2017-01-02 10:43:20'
             and created_time < '2017-01-03 21:26:40'
            then '2017-01-02 10:43:20'
            when created_time >= '2017-01-03 21:26:40' 
             and created_time < '2017-01-05 08:10:00'
            then '2017-01-03 21:26:40' 
            else '2017-01-05 08:10:00'
        end min_dt,
       case when created_time >= '2017-01-01' 
             and created_time < '2017-01-02 10:43:20'
            then '2017-01-02 10:43:20'
            when created_time >= '2017-01-02 10:43:20'
             and created_time < '2017-01-03 21:26:40'
            then '2017-01-03 21:26:40'
            when created_time >= '2017-01-03 21:26:40' 
             and created_time < '2017-01-05 08:10:00'
            then '2017-01-05 08:10:00'
            else '2017-01-06 18:53:20'
        end max_dt,
       count(*)
  from t_source
 group by case when created_time >= '2017-01-01' 
             and created_time < '2017-01-02 10:43:20'
            then '2017-01-01'
            when created_time >= '2017-01-02 10:43:20'
             and created_time < '2017-01-03 21:26:40'
            then '2017-01-02 10:43:20'
            when created_time >= '2017-01-03 21:26:40' 
             and created_time < '2017-01-05 08:10:00'
            then '2017-01-03 21:26:40' 
            else '2017-01-05 08:10:00'
        end,
       case when created_time >= '2017-01-01' 
             and created_time < '2017-01-02 10:43:20'
            then '2017-01-02 10:43:20'
            when created_time >= '2017-01-02 10:43:20'
             and created_time < '2017-01-03 21:26:40'
            then '2017-01-03 21:26:40'
            when created_time >= '2017-01-03 21:26:40' 
             and created_time < '2017-01-05 08:10:00'
            then '2017-01-05 08:10:00'
            else '2017-01-06 18:53:20'
        end;

        查询结果如图二所示。

图二

        4份数据的并集应该覆盖整个源数据集,并且数据之间是不重复的。也就是说4份数据的created_time要连续且互斥,连续保证处理全部数据,互斥确保了不需要二次查重。实际上这和时间范围分区的概念类似,或许用分区表更好些,只是这里省略了重建表的步骤。

3. 建立查重的存储过程

        有了以上信息我们就可以写出4条语句处理全部数据。为了调用接口尽量简单,建立下面的存储过程。

delimiter //
create procedure sp_unique(i smallint)    
begin     
    set @a:='0000-00-00 00:00:00';  
    set @b:=' ';  
	if (i<4) then
        insert into t_target  
        select * from t_source force index (idx_sort)  
         where created_time >= date_add('2017-01-01',interval (i-1)*125000 second) 
           and created_time < date_add('2017-01-01',interval i*125000 second) 
           and (@a!=created_time or @b!=item_name) 
           and (@a:=created_time) is not null 
           and (@b:=item_name) is not null  
         order by created_time,item_name;  
        commit;
    else 
	insert into t_target  
        select * from t_source force index (idx_sort)  
         where created_time >= date_add('2017-01-01',interval (i-1)*125000 second) 
           and created_time <= date_add('2017-01-01',interval i*125000 second) 
           and (@a!=created_time or @b!=item_name) 
           and (@a:=created_time) is not null 
           and (@b:=item_name) is not null  
         order by created_time,item_name;  
        commit;
    end if;	
end     
// 

delimiter ; 

        查询的执行计划都如图三所示。

图三

        mysql优化器进行索引范围扫描,并且使用索引条件下推(ICP)优化查询。

二、并行执行

        下面分别使用shell后台进程和MySQL Schedule Event实现并行。

1. shell后台进程

(1)建立duplicate_removal.sh文件,内容如下。

#!/bin/bash
mysql -vvv -u root -p123456 test -e "truncate t_target" &>/dev/null 
date '+%H:%M.%N'
for y in {1..4}
do
  sql="call sp_unique($y)"
  mysql -vvv -u root -p123456 test -e "$sql" &>par_sql1_$y.log &
done
wait
date '+%H:%M.%N'

(2)执行脚本文件

chmod 755 duplicate_removal.sh
./duplicate_removal.sh

        执行输出入图四所示。

图四

这种方法用时3.4秒,并行执行的4个过程调用分别用时如图五所示。

图五

        可以看到,每个过程的执行时间均不到3.4秒,因为是并行执行,总的过程执行时间也小于3.4秒,比单线程sql速度提高了近3倍。

2. MySQL Schedule Event

        吴老师也用到了并行,但他是利用MySQL自带的Schedule Event功能实现的,代码应该和下面的类似。

(1)建立事件历史日志表

-- 用于查看事件执行时间等信息
create table t_event_history  (  
   dbname  varchar(128) not null default '',  
   eventname  varchar(128) not null default '',  
   starttime  datetime(3) not null default '0000-00-00 00:00:00',  
   endtime  datetime(3) default null,  
   issuccess  int(11) default null,  
   duration  int(11) default null,  
   errormessage  varchar(512) default null,  
   randno  int(11) default null
);  

(2)修改event_scheduler参数

set global event_scheduler = 1;

(3)为每个并发线程创建一个事件

delimiter //
create event ev1 on schedule at current_timestamp + interval 1 hour on completion preserve disable do 
begin
    declare r_code char(5) default '00000';  
    declare r_msg text;  
    declare v_error integer;  
    declare v_starttime datetime default now(3);  
    declare v_randno integer default floor(rand()*100001);  
      
    insert into t_event_history (dbname,eventname,starttime,randno) 
    #作业名	
    values(database(),'ev1', v_starttime,v_randno);    
     
    begin  
        #异常处理段  
        declare continue handler for sqlexception    
        begin  
            set v_error = 1;  
            get diagnostics condition 1 r_code = returned_sqlstate , r_msg = message_text;  
        end;  
          
        #此处为实际调用的用户程序过程  
        call sp_unique(1);  
    end;  
      
    update t_event_history set endtime=now(3),issuccess=isnull(v_error),duration=timestampdiff(microsecond,starttime,now(3)), errormessage=concat('error=',r_code,', message=',r_msg),randno=null where starttime=v_starttime and randno=v_randno;  
      
end
//     

create event ev2 on schedule at current_timestamp + interval 1 hour on completion preserve disable do 
begin
    declare r_code char(5) default '00000';  
    declare r_msg text;  
    declare v_error integer;  
    declare v_starttime datetime default now(3);  
    declare v_randno integer default floor(rand()*100001);  
      
    insert into t_event_history (dbname,eventname,starttime,randno) 
    #作业名	
    values(database(),'ev2', v_starttime,v_randno);    
     
    begin  
        #异常处理段  
        declare continue handler for sqlexception    
        begin  
            set v_error = 1;  
            get diagnostics condition 1 r_code = returned_sqlstate , r_msg = message_text;  
        end;  
          
        #此处为实际调用的用户程序过程  
        call sp_unique(2);  
    end;  
      
    update t_event_history set endtime=now(3),issuccess=isnull(v_error),duration=timestampdiff(microsecond,starttime,now(3)), errormessage=concat('error=',r_code,', message=',r_msg),randno=null where starttime=v_starttime and randno=v_randno;  
      
end
//  

create event ev3 on schedule at current_timestamp + interval 1 hour on completion preserve disable do 
begin
    declare r_code char(5) default '00000';  
    declare r_msg text;  
    declare v_error integer;  
    declare v_starttime datetime default now(3);  
    declare v_randno integer default floor(rand()*100001);  
      
    insert into t_event_history (dbname,eventname,starttime,randno) 
    #作业名	
    values(database(),'ev3', v_starttime,v_randno);    
     
    begin  
        #异常处理段  
        declare continue handler for sqlexception    
        begin  
            set v_error = 1;  
            get diagnostics condition 1 r_code = returned_sqlstate , r_msg = message_text;  
        end;  
          
        #此处为实际调用的用户程序过程  
        call sp_unique(3);  
    end;  
      
    update t_event_history set endtime=now(3),issuccess=isnull(v_error),duration=timestampdiff(microsecond,starttime,now(3)), errormessage=concat('error=',r_code,', message=',r_msg),randno=null where starttime=v_starttime and randno=v_randno;  
      
end
//  

create event ev4 on schedule at current_timestamp + interval 1 hour on completion preserve disable do 
begin
    declare r_code char(5) default '00000';  
    declare r_msg text;  
    declare v_error integer;  
    declare v_starttime datetime default now(3);  
    declare v_randno integer default floor(rand()*100001);  
      
    insert into t_event_history (dbname,eventname,starttime,randno) 
    #作业名	
    values(database(),'ev4', v_starttime,v_randno);    
     
    begin  
        #异常处理段  
        declare continue handler for sqlexception    
        begin  
            set v_error = 1;  
            get diagnostics condition 1 r_code = returned_sqlstate , r_msg = message_text;  
        end;  
          
        #此处为实际调用的用户程序过程  
        call sp_unique(4);  
    end;  
      
    update t_event_history set endtime=now(3),issuccess=isnull(v_error),duration=timestampdiff(microsecond,starttime,now(3)), errormessage=concat('error=',r_code,', message=',r_msg),randno=null where starttime=v_starttime and randno=v_randno;  
      
end
//

delimiter ;   

        说明:为了记录每个事件执行的时间,在事件定义中增加了操作日志表的逻辑,因为每个事件中只多执行了一条insert,一条update,4个事件总共多执行8条很简单的语句,对测试的影响可以忽略不计。执行时间精确到毫秒。

(4)触发事件执行

mysql -vvv -u root -p123456 test -e "truncate t_target;alter event ev1 on schedule at current_timestamp enable;alter event ev2 on schedule at current_timestamp enable;alter event ev3 on schedule at current_timestamp enable;alter event ev4 on schedule at current_timestamp enable;"

        说明:该命令行顺序触发了4个事件,但不会等前一个执行完才执行下一个,而是立即向下执行。从图六的输出也可以清楚地看到这一点。因此四次过程调用是并行执行的。

图六

(5)查看事件执行日志

select * from t_event_history;

        查询结果如图7所示。

图七

        可以看到,每个过程的执行均为3.5秒,又因为是并行执行的,因此总的执行之间也是3.5秒,优化效果和shell后台进程方式几乎相同。

参考:

Increasing slow query performance with the parallel query execution

Mysql Event 调度历史记录

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏腾讯大数据的专栏

zookeeper 运营经验分享

Zookeeper作为TDBank系统的一个重要模块,我们运营它已经两年多。在使用过程中,我们也遇到了一些问题及走过很多弯路,本文主要对zookeeper运营经...

2199
来自专栏腾讯大讲堂的专栏

zookeeper 运营经验分享

Zookeeper作为TDBank系统的一个重要模块,我们运营它已经两年多。在使用过程中,我们也遇到了一些问题及走过很多弯路,本文主要对zookeeper运营经...

2177
来自专栏HappenLee的技术杂谈

分布式系统的烦恼------《Designing Data-Intensive Applications》读书笔记11

当我们在使用单机系统时,它通常以一种相当可预测的方式工作:要么它正常工作,要么不工作。

683
来自专栏Kubernetes

docker-monitor-injector源码分析

1.工具介绍 docker-monitor-injector是携程开源的一个修正docker容器内监控数据的工具,通过劫持open,fopen等linux库函数...

2979
来自专栏腾讯云Elasticsearch Service

Elasticsearch调优实践

本文基于ES 5.6.4,从性能和稳定性两方面,从linux参数调优、ES节点配置和ES使用方式三个角度入手,介绍ES调优的基本方案。当然,ES的调优绝不能一概...

1331
来自专栏全华班

springcloud学习手册-Ribbon(第一节)

导读 | 介绍什么是Ribbon,主要概念和内容 前几天学习了Eureka ,今天咱们再来学习springcloud 的第三部分内容Ribbon 那什么是 Ri...

3706
来自专栏无题

mongoDB运作原理与分片实现

觉得这一篇整理比较好,主要在于通俗易懂,逻辑清晰。但是网上转载的人太多了,我实在找不到原作者了。 关于MongoDB,我们能看到的资料,基本都是在指导大家如何使...

3384
来自专栏腾讯技术工程官方号的专栏

Elasticsearch调优实践

4415
来自专栏码洞

一种简单的Failover机制

在应用结构上有这样一个业务场景,机房里部署了多个物理数据库的Proxy无状态节点,业务端通过Proxy节点间接和存储DB交互。Proxy支持了分库分表的特性,管...

722
来自专栏redis集群

redis集群的高可用性实现

当主从的redis性能和容量满足不了项目的需求时,一般会采用集群方案。而原生的集群方案是一个比较好的选择。本文主要是讨论如何保证集群版高可用。高...

794

扫码关注云+社区