mysql 并行操作_将MySQL去重操作优化到极致之三弹连发（二）：多线程并行执行...

最新推荐文章于 2024-09-06 21:07:52 发布
最新推荐文章于 2024-09-06 21:07:52 发布
阅读量430 收藏
点赞数
文章标签： mysql 并行操作
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_35511854/article/details/113596548
版权
本文介绍了如何通过数据分片和并行执行来优化MySQL的去重操作。首先，通过查询将数据平均分成4份，然后创建4个独立的存储过程以并行执行去重任务，分别对应4个线程。最后，通过shell脚本和MySQL Schedule Event两种方式实现了并行执行，显著提高了处理速度。
摘要由CSDN通过智能技术生成

上一篇已经将单条查重语句调整到最优，但该语句是以单线程方式执行。能否利用多处理器，让去重操作多线程并行执行，从而进一步提高速度呢？比如我的实验环境是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;        查询结果如图一所示。

ff7b9ae2f71449da2d9f5a4aa54cc1fe.png

图一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;

查询结果如图二所示。

239986c5871bc73f3e9e0cb563551833.png

图二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 ;

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

b862fc5af9d5933fa6f05679ba4a6824.png

图三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

执行输出入图四所示。

89dab86990f9fa845e41ce70b9cf566e.png

图四

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

74793b0d43bff2b6702ffff8a257f59f.png

图五可以看到，每个过程的执行时间均不到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个事件，但不会等前一个执行完才执行下一个，而是立即向下执行。从图六的输出也可以清楚地看到这一点。因此四次过程调用是并行执行的。

ebf8f980523ba412409e32d3a32290a1.png图六

(5)查看事件执行日志

select * from t_event_history;

查询结果如图7所示。

5cedee9a00900f9d9b5b759c9c2da16c.png

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

参考：

Increasing slow query performance with the parallel query execution

Mysql Event 调度历史记录

com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value ‘Öйú±ê×解决方案
weixin_43178406的博客
01-08 2万+
本文主要介绍了com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value ‘Öйú±ê׼ʱ¼ä’ is unrecognized or represents more than one time zone解决方案，希望能对使用mysql的同学们有所帮助。 文章目录 1. 问题描述 2. 解决方案
解决Cause: java.sql.SQLException: sql injection violation, dbType mysql ... token IDENTIFIER deleted错误
念兮为美
03-21 9885
Cause: java.sql.SQLException: sql injection violation, dbType mysql, , druid-version 1.2.11, syntax error: not supported.pos 86, line 1, column 79, token IDENTIFIER deleted : xxx详细的解决方法以及建表的命名规范。
mysql-parallel:一组脚本和工具，通过并行运行来加速 MySQL 转储和恢复
06-13
MySQL 的并行脚本 这些脚本用于并行运行 MySQL 转储和恢复，每个表一个作业。 这在大多数转储和恢复中带来了显着的性能提升，尤其是在使用 SSD 时。 默认情况下，将为系统可以运行的每个并发线程生成作业。 同样，将使用 lbzip2，与 bzip2 一样，任何包含大部分数据的少量表的转储或恢复都将被 CPU 内核上的单个 bzip2 进程阻止。 虽然这看起来有点极端，但实际上 OS X 调度程序（可能还有 Linux 调度程序）可以很好地处理大量进程和线程。 Ubuntu 用户警告 默认情况下，Ubuntu 在/etc/parallel/config设置--tollef标志。 这以有趣和可怕的方式破坏了这些脚本。 如果要使用这些脚本，请从全局配置中删除该行。 有关详细信息，请参阅。 例子 # Will create a timestamped my_database-TIMEST
oracle并行parallel update两张表_MySQL8.0 InnoDB并行执行的详解
weixin_39900582的博客
12-01 301
php中文网最新课程每日17点准时技术干货分享概述MySQL经过多年的发展已然成为最流行的数据库，广泛用于互联网行业，并逐步向各个传统行业渗透。之所以流行，一方面是其优秀的高并发事务处理的能力，另一方面也得益于MySQL丰富的生态。MySQL在处理OLTP场景下的短查询效果很好，但对于复杂大查询则能力有限。最直接一点就是，对于一个SQL语句，MySQL最多只能使用一个CPU核来处理，在这...
mysql多线程优化并行复制
最新发布
qq_2382495516的博客
09-06 693
在从库中将relay_log_recovery设置为on，假如果碰到上面的情形，从库会自动放弃所有未执行的relay log，重新生成一个relay log，并将从库的io线程的position重新指向新的relay log。并将sql线程的position退回到跟io线程的position保持一致，重新开始同步，这样在从库中事务不会丢失。在从库中将relay_log_recovery不设置或者设置为off，如果碰到上面的情形，从库会丢失那些没有应用的日志，主从会不一致。
mysql-5.7 innodb 的并行任务调度详解
weixin_33968104的博客
09-12 144
一、innodb并行任务调度是什么： 　　这里要“考古”一下了，不然问题说不清楚。上大学的时候老师和我们说最初的计算机只有一个核心，并且一次也只能做一件事， 　　如果你有两件事要用到计算机，在第一件事没有做完之前，后面那件事只能等着。后来呀，计算机就进化了一把，计算机不再 　　是把一件是做完之后，再去做第二件事，而是一件事只做一段非常短的时间，并不关心这件事有没有做完，它就会去做第二件 　　事，同...
mysql存储过程 多线程_解决多线程调用sql存储过程问题
weixin_42502255的博客
01-20 908
场景：我们程序现在改成多线程了，我现在需要把临时表中的数据给插入到TABLE_M中，但这时候可能其他的线程也在插入，我就不能用之前我们的方案了(select max(oid) from Tuning.TABLE_M。。。，之后去维护主子表关系，改掉TABLE_M的OID的自增属性，插入，之后再添加上自增属性。。。。。。)我现在是想这样操作：1,)先插入把临时数据插入到TABLE_M(OID是自增的...
mysql 多线程存储过程_mysql多线程调用存储过程的问题
weixin_34109083的博客
02-27 1092
引用通过传入参数来改变存储过程内操作的表没理解你想实现什么功能，能否举例说明？在数据库dbtest中，有一个存储过程createproceduresp_protest(intablenamevarchar(16))begin对传入表名的表进行插入与更新的操作end;多线程调用这个存储过程，会不会有什么问题会有问题你不妨把这个sp_protest写完整，这样你会用到一个SESSION级变...
解决java.lang.ClassNotFoundException: com.mysql.jdbc.Driver问题（真实有效）
热门推荐
向小凯同学学习
06-29 2万+
数据库驱动找不到 这个一场特别奇葩，既然这个异常存在，竟然又能编译通过，项目也不报错，只是在启动的时候，抛出异常！ 一、看看这个异常 Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled. 2018-06-29 19:...
MySQL多线程并发调优
学习，思考，记录，分享。
06-16 6705
前言 MySQL并发模型 架构 Innodb并发参数 建议值 Demo Reference 前言 学习MySQL数据库技术，一个非常重要的技能就是性能调优。通常情况下，都是自下而上的调优方法，主要包括运行环境、配置参数、SQL性能和系统架构设计调优等。 本文从多线程的角度，简单描述MySQL并发参数及其调优。 MySQL并发模型 架构 Innodb用自己的...
mysql存储过程并发_使用存储过程并发访问MySQL数据库
weixin_39564036的博客
01-19 218
I have a stored procedure that will read and then increment a value in the database. This particular procedure is used by many programs at the same time. I am concerned about the concurrency issues, i...
mysql 动态执行存储过程语句
09-11
MSSQL中动态执行sql语句可以使用EXEC()函数。MSSQL中也有类似的函数EXECUTE()，不过不同的是MYSQL中动态执行存储过程语句与MSSQL还是有区别的。
mysql 并行查询_mysql并行查询问题
weixin_39556853的博客
01-18 1158
业务表数据量过大分表设计4张，然后需求是将4张表全量数据导出。测试机器4核，做法是开启4个线程同时查询，最终结果汇总。如果一个线程查询一张表耗时 2s，那么要实现的是 4个线程跑完也是耗时 2s(利用cpu多核真正并行)，但现实是耗时 2 * 4 约等于 8s多。真的困扰多时的一个问题，还望大佬相助，跪谢！附上mysql参数：+-----------------------------------...
Flink自定义并行读取Mysql数据
dinghua_xuexi的专栏
08-03 2678
自定义Source概述 自定义Source是Flink学习中非常重要的一环，一般实现一个Source，可以继承下面几个类之一 SourceFunction RichSourceFunction ParallelSourceFunction RichParallelSourceFunction 前两个是单并行度，后2个是多并行度，无论是否多并行度，我们一般选择带Rich的SourceFunction, 也就是RichSourceFunction和RichParallelSourceFunction，这
mysql支持并行运行机制_mysql 5.6并行复制事件分发机制
weixin_28040125的博客
01-26 171
并行复制相关线程在MySQL 5.6并行复制中，当设置set global slave_parallel_workers=2时，共有4个复制相关的线程，如下：+----+-------------+------+-------+---------+------+------------------------------------------------------------------+--...
C# MySQL 数据库操作类 (包含MySql.Data.Dll文件) 常用方法二三十多个
12-26
本资源提供了一个C#编写的MySQL数据库操作类，包含了MySql.Data.Dll库，这使得开发者可以方便地进行数据库查询、插入、更新和删除等操作。这个类库集成了大约二三十个常用的方法，覆盖了数据库操作的基础功能。 ...
MySQL多线程环境下的操作要点
保持敏锐，保持进化。
01-02 1187
MySQL多线程环境下的操作要点：多线程逻辑下的初始化要领
mysql定时任务(event事件)实现详解
09-09
主要介绍了mysql定时任务(event事件)实现详解,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
用mysql source命令可以导入比较大的文件
明晓默的博客
04-28 434
  今天碰到今天碰到个问题要用phpmyadmin导入1G的数据，但是在怎么都导入不了，用命令行就可以轻松搞定了。用mysql source命令可以导入比较大的文件。 mysql&gt;use dbtest; mysql&gt;set names utf8; mysql&gt;source D:/www/sql/back.sql; 通过source命令导入多个文件，可以新建一个sou.s...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值