将MySQL去重操作优化到极致的操作方法
•问题提出
源表t_source结构如下：
item_idint, created_timedatetime, modified_timedatetime, item_namevarchar(20), othervarchar(20)
要求：
1.源表中有100万条数据，其中有50万created_time和item_name重复。
2.要把去重后的50万数据写入到目标表。
3.重复created_time和item_name的多条数据，可以保留任意一条，不做规则限制。
•实验环境
Linux虚机：CentOSrelease6.4；8G物理内存（MySQL配置4G）；100G机械硬盘；双物理CPU双核，共四个处理器；MySQL8.0.16。
•建立测试表和数据
--建立源表
createtablet_source
(item_idint,
created_timedatetime,
modified_timedatetime,
item_namevarchar(20),
othervarchar(20)
);
--建立目标表
createtablet_targetliket_source;
--生成100万测试数据，其中有50万created_time和item_name重复
delimiter//
createproceduresp_generate_data()
begin
set@i:=1;
while@i<=500000do
set@created_time:=date_add('2017-01-01',interval@isecond);
set@modified_time:=@created_time;
set@item_name:=concat('a',@i);
insertintot_source
values(@i,@created_time,@modified_time,@item_name,'other');
set@i:=@i+1;
endwhile;
commit;
set@last_insert_id:=500000;
insertintot_source
selectitem_id+@last_insert_id,
created_time,
date_add(modified_time,interval@last_insert_idsecond),
item_name,
'other'
fromt_source;
commit;
end
//
delimiter;
callsp_generate_data();
--源表没有主键或唯一性约束，有可能存在两条完全一样的数据，所以再插入一条记录模拟这种情况。
insertintot_sourceselect*fromt_sourcewhereitem_id=1;
源表中有1000001条记录，去重后的目标表应该有500000条记录。
mysql>selectcount(*),count(distinctcreated_time,item_name)fromt_source;
+----------+----------------------------------------+
|count(*)|count(distinctcreated_time,item_name)|
+----------+----------------------------------------+
|1000001|500000|
+----------+----------------------------------------+
1rowinset(1.92sec)
一、巧用索引与变量
1.无索引对比测试
（1）使用相关子查询
truncatet_target; insertintot_target selectdistinctt1.*fromt_sourcet1whereitem_idin (selectmin(item_id)fromt_sourcet2wheret1.created_time=t2.created_timeandt1.item_name=t2.item_name);
这个语句很长时间都出不来结果，只看一下执行计划吧。
mysql>explainselectdistinctt1.*fromt_sourcet1whereitem_idin ->(selectmin(item_id)fromt_sourcet2wheret1.created_time=t2.created_timeandt1.item_name=t2.item_name); +----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| +----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+ |1|PRIMARY|t1|NULL|ALL|NULL|NULL|NULL|NULL|997282|100.00|Usingwhere;Usingtemporary| |2|DEPENDENTSUBQUERY|t2|NULL|ALL|NULL|NULL|NULL|NULL|997282|1.00|Usingwhere| +----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+ 2rowsinset,3warnings(0.00sec)
主查询和相关子查询都是全表扫描，一共要扫描100万*100万数据行，难怪出不来结果。
（2）使用表连接
truncatet_target; insertintot_target selectdistinctt1.*fromt_sourcet1, (selectmin(item_id)item_id,created_time,item_namefromt_sourcegroupbycreated_time,item_name)t2 wheret1.item_id=t2.item_id;
这种方法用时14秒，查询计划如下：
mysql>explainselectdistinctt1.*fromt_sourcet1,(selectmin(item_id)item_id,created_time,item_namefromt_sourcegroupbycreated_time,item_name)t2wheret1.item_id=t2.item_id; +----+-------------+------------+------------+------+---------------+-------------+---------+-----------------+--------+----------+------------------------------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| +----+-------------+------------+------------+------+---------------+-------------+---------+-----------------+--------+----------+------------------------------+ |1|PRIMARY|t1|NULL|ALL|NULL|NULL|NULL|NULL|997282|100.00|Usingwhere;Usingtemporary| |1|PRIMARY||NULL|ref| | |5|test.t1.item_id|10|100.00|Distinct| |2|DERIVED|t_source|NULL|ALL|NULL|NULL|NULL|NULL|997282|100.00|Usingtemporary| +----+-------------+------------+------------+------+---------------+-------------+---------+-----------------+--------+----------+------------------------------+ 3rowsinset,1warning(0.00sec)
•内层查询扫描t_source表的100万行，建立临时表，找出去重后的最小item_id，生成导出表derived2，此导出表有50万行。
•MySQL会在导出表derived2上自动创建一个item_id字段的索引auto_key0。
•外层查询也要扫描t_source表的100万行数据，在与导出表做链接时，对t_source表每行的item_id，使用auto_key0索引查找导出表中匹配的行，并在此时优化distinct操作，在找到第一个匹配的行后即停止查找同样值的动作。
（3）使用变量
set@a:='1000-01-0100:00:00'; set@b:=''; set@f:=0; truncatet_target; insertintot_target selectitem_id,created_time,modified_time,item_name,other from (selectt0.*,if(@a=created_timeand@b=item_name,@f:=0,@f:=1)f,@a:=created_time,@b:=item_name from (select*fromt_sourceorderbycreated_time,item_name)t0)t1wheref=1;
这种方法用时13秒，查询计划如下：
mysql>explainselectitem_id,created_time,modified_time,item_name,other ->from ->(selectt0.*,if(@a=created_timeand@b=item_name,@f:=0,@f:=1)f,@a:=created_time,@b:=item_name ->from ->(select*fromt_sourceorderbycreated_time,item_name)t0)t1wheref=1; +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+ |1|PRIMARY||NULL|ref| | |4|const|10|100.00|NULL| |2|DERIVED| |NULL|ALL|NULL|NULL|NULL|NULL|997282|100.00|NULL| |3|DERIVED|t_source|NULL|ALL|NULL|NULL|NULL|NULL|997282|100.00|Usingfilesort| +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+ 3rowsinset,5warnings(0.00sec)
•最内层的查询扫描t_source表的100万行，并使用文件排序，生成导出表derived3。
•第二层查询要扫描derived3的100万行，生成导出表derived2，完成变量的比较和赋值，并自动创建一个导出列f上的索引auto_key0。
•最外层使用auto_key0索引扫描derived2得到去重的结果行。
与上面方法2比较，总的扫描行数不变，都是200万行。只存在一点微小的差别，这次自动生成的索引是在常量列f上，而表关联自动生成的索引是在item_id列上，所以查询时间几乎相同。
至此，我们还没有在源表上创建任何索引。无论使用哪种写法，要查重都需要对created_time和item_name字段进行排序，因此很自然地想到，如果在这两个字段上建立联合索引，利用索引本身有序的特性消除额外排序，从而提高查询性能。
2.建立created_time和item_name上的联合索引对比测试
--建立created_time和item_name字段的联合索引 createindexidx_sortont_source(created_time,item_name,item_id); analyzetablet_source;
（1）使用相关子查询
truncatet_target; insertintot_target selectdistinctt1.*fromt_sourcet1whereitem_idin (selectmin(item_id)fromt_sourcet2wheret1.created_time=t2.created_timeandt1.item_name=t2.item_name);
本次用时19秒，查询计划如下：
mysql>explainselectdistinctt1.*fromt_sourcet1whereitem_idin ->(selectmin(item_id)fromt_sourcet2wheret1.created_time=t2.created_timeandt1.item_name=t2.item_name); +----+--------------------+-------+------------+------+---------------+----------+---------+----------------------------------------+--------+----------+------------------------------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| +----+--------------------+-------+------------+------+---------------+----------+---------+----------------------------------------+--------+----------+------------------------------+ |1|PRIMARY|t1|NULL|ALL|NULL|NULL|NULL|NULL|997281|100.00|Usingwhere;Usingtemporary| |2|DEPENDENTSUBQUERY|t2|NULL|ref|idx_sort|idx_sort|89|test.t1.created_time,test.t1.item_name|2|100.00|Usingindex| +----+--------------------+-------+------------+------+---------------+----------+---------+----------------------------------------+--------+----------+------------------------------+ 2rowsinset,3warnings(0.00sec)
•外层查询的t_source表是驱动表，需要扫描100万行。
•对于驱动表每行的item_id，通过idx_sort索引查询出两行数据。
（2）使用表连接
truncatet_target; insertintot_target selectdistinctt1.*fromt_sourcet1, (selectmin(item_id)item_id,created_time,item_namefromt_sourcegroupbycreated_time,item_name)t2 wheret1.item_id=t2.item_id;
本次用时13秒，查询计划如下：
mysql>explainselectdistinctt1.*fromt_sourcet1, ->(selectmin(item_id)item_id,created_time,item_namefromt_sourcegroupbycreated_time,item_name)t2 ->wheret1.item_id=t2.item_id; +----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------+--------+----------+------------------------------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| +----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------+--------+----------+------------------------------+ |1|PRIMARY|t1|NULL|ALL|NULL|NULL|NULL|NULL|997281|100.00|Usingwhere;Usingtemporary| |1|PRIMARY||NULL|ref| | |5|test.t1.item_id|10|100.00|Distinct| |2|DERIVED|t_source|NULL|index|idx_sort|idx_sort|94|NULL|997281|100.00|Usingindex| +----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------+--------+----------+------------------------------+ 3rowsinset,1warning(0.00sec)
和没有索引相比，子查询虽然从全表扫描变为了全索引扫描，但还是需要扫描100万行记录。因此查询性能提升并不是明显。
（3）使用变量
set@a:='1000-01-0100:00:00'; set@b:=''; set@f:=0; truncatet_target; insertintot_target selectitem_id,created_time,modified_time,item_name,other from (selectt0.*,if(@a=created_timeand@b=item_name,@f:=0,@f:=1)f,@a:=created_time,@b:=item_name from (select*fromt_sourceorderbycreated_time,item_name)t0)t1wheref=1;
本次用时13秒，查询计划与没有索引时的完全相同。可见索引对这种写法没有作用。能不能消除嵌套，只用一层查询出结果呢？
（4）使用变量，并且消除嵌套查询
set@a:='1000-01-0100:00:00'; set@b:=''; truncatet_target; insertintot_target select*fromt_sourceforceindex(idx_sort) where(@a!=created_timeor@b!=item_name)and(@a:=created_time)isnotnulland(@b:=item_name)isnotnull orderbycreated_time,item_name;
本次用时12秒，查询计划如下：
mysql>explainselect*fromt_sourceforceindex(idx_sort) ->where(@a!=created_timeor@b!=item_name)and(@a:=created_time)isnotnulland(@b:=item_name)isnotnull ->orderbycreated_time,item_name; +----+-------------+----------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| +----+-------------+----------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+ |1|SIMPLE|t_source|NULL|index|NULL|idx_sort|94|NULL|997281|99.00|Usingwhere| +----+-------------+----------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+ 1rowinset,3warnings(0.00sec)
该语句具有以下特点：
•消除了嵌套子查询，只需要对t_source表进行一次全索引扫描，查询计划已达最优。
•无需distinct二次查重。
•变量判断与赋值只出现在where子句中。
•利用索引消除了filesort。
在MySQL8之前，该语句是单线程去重的最佳解决方案。仔细分析这条语句，发现它巧妙地利用了SQL语句的逻辑查询处理步骤和索引特性。一条SQL查询的逻辑步骤为：
1.执行笛卡尔乘积（交叉连接）
2.应用ON筛选器（连接条件）
3.添加外部行（outerjoin）
4.应用where筛选器
5.分组
6.应用cube或rollup
7.应用having筛选器
8.处理select列表
9.应用distinct子句
10.应用orderby子句
11.应用limit子句
每条查询语句的逻辑执行步骤都是这11步的子集。拿这条查询语句来说，其执行顺序为：强制通过索引idx_sort查找数据行->应用where筛选器->处理select列表->应用orderby子句。
为了使变量能够按照created_time和item_name的排序顺序进行赋值和比较，必须按照索引顺序查找数据行。这里的forceindex(idx_sort)提示就起到了这个作用，必须这样写才能使整条查重语句成立。否则，因为先扫描表才处理排序，因此不能保证变量赋值的顺序，也就不能确保查询结果的正确性。orderby子句同样不可忽略，否则即使有forceindex提示，MySQL也会使用全表扫描而不是全索引扫描，从而使结果错误。索引同时保证了created_time,item_name的顺序，避免了文件排序。forceindex(idx_sort)提示和orderby子句缺一不可，索引idx_sort在这里可谓恰到好处、一举两得。
查询语句开始前，先给变量初始化为数据中不可能出现的值，然后进入where子句从左向右判断。先比较变量和字段的值，再将本行created_time和item_name的值赋给变量，按created_time、item_name的顺序逐行处理。item_name是字符串类型，(@b:=item_name)不是有效的布尔表达式，因此要写成(@b:=item_name)isnotnull。
最后补充一句，这里忽略了“insertintot_targetselect*fromt_sourcegroupbycreated_time,item_name;”的写法，因为它受“sql_mode='ONLY_FULL_GROUP_BY'”的限制。
二、利用窗口函数
MySQL8中新增的窗口函数使得原来麻烦的去重操作变得很简单。
truncatet_target; insertintot_target selectitem_id,created_time,modified_time,item_name,other from(select*,row_number()over(partitionbycreated_time,item_name)asrn fromt_source)twherern=1;
这个语句执行只需要12秒，而且写法清晰易懂，其查询计划如下：
mysql>explainselectitem_id,created_time,modified_time,item_name,other ->from(select*,row_number()over(partitionbycreated_time,item_name)asrn ->fromt_source)twherern=1; +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+ |1|PRIMARY||NULL|ref| | |8|const|10|100.00|NULL| |2|DERIVED|t_source|NULL|ALL|NULL|NULL|NULL|NULL|997281|100.00|Usingfilesort| +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+ 2rowsinset,2warnings(0.00sec)
该查询对t_source表进行了一次全表扫描，同时用filesort对表按分区字段created_time、item_name进行了排序。外层查询从每个分区中保留一条数据。因为重复created_time和item_name的多条数据中可以保留任意一条，所以oevr中不需要使用orderby子句。
从执行计划看，窗口函数去重语句似乎没有消除嵌套查询的变量去重好，但此方法实际执行是最快的。
MySQL窗口函数说明参见“https://dev.mysql.com/doc/refman/8.0/en/window-functions.html”。
三、多线程并行执行
前面已经将单条查重语句调整到最优，但还是以单线程方式执行。能否利用多处理器，让去重操作多线程并行执行，从而进一步提高速度呢？比如我的实验环境是4处理器，如果使用4个线程同时执行查重SQL，理论上应该接近4倍的性能提升。
1.数据分片
在生成测试数据时，created_time采用每条记录加一秒的方式，也就是最大和在最小的时间差为50万秒，而且数据均匀分布，因此先把数据平均分成4份。
（1）查询出4份数据的created_time边界值
mysql>selectdate_add('2017-01-01',interval125000second)dt1,
->date_add('2017-01-01',interval2*125000second)dt2,
->date_add('2017-01-01',interval3*125000second)dt3,
->max(created_time)dt4
->fromt_source;
+---------------------+---------------------+---------------------+---------------------+
|dt1|dt2|dt3|dt4|
+---------------------+---------------------+---------------------+---------------------+
|2017-01-0210:43:20|2017-01-0321:26:40|2017-01-0508:10:00|2017-01-0618:53:20|
+---------------------+---------------------+---------------------+---------------------+
1rowinset(0.00sec)
（2）查看每份数据的记录数，确认数据平均分布
mysql>selectcasewhencreated_time>='2017-01-01' ->andcreated_time<'2017-01-0210:43:20' ->then'2017-01-01' ->whencreated_time>='2017-01-0210:43:20' ->andcreated_time<'2017-01-0321:26:40' ->then'2017-01-0210:43:20' ->whencreated_time>='2017-01-0321:26:40' ->andcreated_time<'2017-01-0508:10:00' ->then'2017-01-0321:26:40' ->else'2017-01-0508:10:00' ->endmin_dt, ->casewhencreated_time>='2017-01-01' ->andcreated_time<'2017-01-0210:43:20' ->then'2017-01-0210:43:20' ->whencreated_time>='2017-01-0210:43:20' ->andcreated_time<'2017-01-0321:26:40' ->then'2017-01-0321:26:40' ->whencreated_time>='2017-01-0321:26:40' ->andcreated_time<'2017-01-0508:10:00' ->then'2017-01-0508:10:00' ->else'2017-01-0618:53:20' ->endmax_dt, ->count(*) ->fromt_source ->groupbycasewhencreated_time>='2017-01-01' ->andcreated_time<'2017-01-0210:43:20' ->then'2017-01-01' ->whencreated_time>='2017-01-0210:43:20' ->andcreated_time<'2017-01-0321:26:40' ->then'2017-01-0210:43:20' ->whencreated_time>='2017-01-0321:26:40' ->andcreated_time<'2017-01-0508:10:00' ->then'2017-01-0321:26:40' ->else'2017-01-0508:10:00' ->end, ->casewhencreated_time>='2017-01-01' ->andcreated_time<'2017-01-0210:43:20' ->then'2017-01-0210:43:20' ->whencreated_time>='2017-01-0210:43:20' ->andcreated_time<'2017-01-0321:26:40' ->then'2017-01-0321:26:40' ->whencreated_time>='2017-01-0321:26:40' ->andcreated_time<'2017-01-0508:10:00' ->then'2017-01-0508:10:00' ->else'2017-01-0618:53:20' ->end; +---------------------+---------------------+----------+ |min_dt|max_dt|count(*)| +---------------------+---------------------+----------+ |2017-01-01|2017-01-0210:43:20|249999| |2017-01-0210:43:20|2017-01-0321:26:40|250000| |2017-01-0321:26:40|2017-01-0508:10:00|250000| |2017-01-0508:10:00|2017-01-0618:53:20|250002| +---------------------+---------------------+----------+ 4rowsinset(4.86sec)
4份数据的并集应该覆盖整个源数据集，并且数据之间是不重复的。也就是说4份数据的created_time要连续且互斥，连续保证处理全部数据，互斥确保了不需要二次查重。实际上这和时间范围分区的概念类似，或许用分区表更好些，只是这里省略了重建表的步骤。
2.建立查重的存储过程
有了以上信息我们就可以写出4条语句处理全部数据。为了调用接口尽量简单，建立下面的存储过程。
delimiter//
createproceduresp_unique(ismallint)
begin
set@a:='1000-01-0100:00:00';
set@b:='';
if(i<4)then
insertintot_target
select*fromt_sourceforceindex(idx_sort)
wherecreated_time>=date_add('2017-01-01',interval(i-1)*125000second)
andcreated_time=date_add('2017-01-01',interval(i-1)*125000second)
andcreated_time<=date_add('2017-01-01',intervali*125000second)
and(@a!=created_timeor@b!=item_name)
and(@a:=created_time)isnotnull
and(@b:=item_name)isnotnull
orderbycreated_time,item_name;
endif;
end
//
查询语句的执行计划如下：
mysql>explainselect*fromt_sourceforceindex(idx_sort)
->wherecreated_time>=date_add('2017-01-01',interval(1-1)*125000second)
->andcreated_timeand(@a!=created_timeor@b!=item_name)
->and(@a:=created_time)isnotnull
->and(@b:=item_name)isnotnull
->orderbycreated_time,item_name;
+----+-------------+----------+------------+-------+---------------+----------+---------+------+--------+----------+-----------------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+----------+------------+-------+---------------+----------+---------+------+--------+----------+-----------------------+
|1|SIMPLE|t_source|NULL|range|idx_sort|idx_sort|6|NULL|498640|100.00|Usingindexcondition|
+----+-------------+----------+------------+-------+---------------+----------+---------+------+--------+----------+-----------------------+
1rowinset,3warnings(0.00sec)
MySQL优化器进行索引范围扫描，并且使用索引条件下推（ICP）优化查询。
3.并行执行
下面分别使用shell后台进程和MySQLScheduleEvent实现并行。
（1）shell后台进程
•建立duplicate_removal.sh文件，内容如下：
#!/bin/bash
mysql-vvv-uroot-p123456test-e"truncatet_target"&>/dev/null
date'+%H:%M:%S'
foryin{1..4}
do
sql="callsp_unique($y)"
mysql-vvv-uroot-p123456test-e"$sql"&>par_sql1_$y.log&
done
wait
date'+%H:%M:%S'
•执行脚本文件
./duplicate_removal.sh
执行输出如下：
[mysql@hdp2~]$./duplicate_removal.sh 14:27:30 14:27:35
这种方法用时5秒，并行执行的4个过程调用分别用时为4.87秒、4.88秒、4.91秒、4.73秒：
[mysql@hdp2~]$catpar_sql1_1.log|sed'/^$/d' mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure. -------------- callsp_unique(1) -------------- QueryOK,124999rowsaffected(4.87sec) Bye [mysql@hdp2~]$catpar_sql1_2.log|sed'/^$/d' mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure. -------------- callsp_unique(2) -------------- QueryOK,125000rowsaffected(4.88sec) Bye [mysql@hdp2~]$catpar_sql1_3.log|sed'/^$/d' mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure. -------------- callsp_unique(3) -------------- QueryOK,125000rowsaffected(4.91sec) Bye [mysql@hdp2~]$catpar_sql1_4.log|sed'/^$/d' mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure. -------------- callsp_unique(4) -------------- QueryOK,125001rowsaffected(4.73sec) Bye [mysql@hdp2~]$
可以看到，每个过程的执行时间均4.85，因为是并行执行，总的过程执行时间为最慢的4.91秒，比单线程速度提高了2.5倍。
（2）MySQLScheduleEvent
•建立事件历史日志表
--用于查看事件执行时间等信息 createtablet_event_history( dbnamevarchar(128)notnulldefault'', eventnamevarchar(128)notnulldefault'', starttimedatetime(3)notnulldefault'1000-01-0100:00:00', endtimedatetime(3)defaultnull, issuccessint(11)defaultnull, durationint(11)defaultnull, errormessagevarchar(512)defaultnull, randnoint(11)defaultnull );
•为每个并发线程创建一个事件
delimiter//
createeventev1onscheduleatcurrent_timestamp+interval1houroncompletionpreservedisabledo
begin
declarer_codechar(5)default'00000';
declarer_msgtext;
declarev_errorinteger;
declarev_starttimedatetimedefaultnow(3);
declarev_randnointegerdefaultfloor(rand()*100001);
insertintot_event_history(dbname,eventname,starttime,randno)
#作业名
values(database(),'ev1',v_starttime,v_randno);
begin
#异常处理段
declarecontinuehandlerforsqlexception
begin
setv_error=1;
getdiagnosticscondition1r_code=returned_sqlstate,r_msg=message_text;
end;
#此处为实际调用的用户程序过程
callsp_unique(1);
end;
updatet_event_historysetendtime=now(3),issuccess=isnull(v_error),duration=timestampdiff(microsecond,starttime,now(3)),errormessage=concat('error=',r_code,',message=',r_msg),randno=nullwherestarttime=v_starttimeandrandno=v_randno;
end
//
createeventev2onscheduleatcurrent_timestamp+interval1houroncompletionpreservedisabledo
begin
declarer_codechar(5)default'00000';
declarer_msgtext;
declarev_errorinteger;
declarev_starttimedatetimedefaultnow(3);
declarev_randnointegerdefaultfloor(rand()*100001);
insertintot_event_history(dbname,eventname,starttime,randno)
#作业名
values(database(),'ev2',v_starttime,v_randno);
begin
#异常处理段
declarecontinuehandlerforsqlexception
begin
setv_error=1;
getdiagnosticscondition1r_code=returned_sqlstate,r_msg=message_text;
end;
#此处为实际调用的用户程序过程
callsp_unique(2);
end;
updatet_event_historysetendtime=now(3),issuccess=isnull(v_error),duration=timestampdiff(microsecond,starttime,now(3)),errormessage=concat('error=',r_code,',message=',r_msg),randno=nullwherestarttime=v_starttimeandrandno=v_randno;
end
//
createeventev3onscheduleatcurrent_timestamp+interval1houroncompletionpreservedisabledo
begin
declarer_codechar(5)default'00000';
declarer_msgtext;
declarev_errorinteger;
declarev_starttimedatetimedefaultnow(3);
declarev_randnointegerdefaultfloor(rand()*100001);
insertintot_event_history(dbname,eventname,starttime,randno)
#作业名
values(database(),'ev3',v_starttime,v_randno);
begin
#异常处理段
declarecontinuehandlerforsqlexception
begin
setv_error=1;
getdiagnosticscondition1r_code=returned_sqlstate,r_msg=message_text;
end;
#此处为实际调用的用户程序过程
callsp_unique(3);
end;
updatet_event_historysetendtime=now(3),issuccess=isnull(v_error),duration=timestampdiff(microsecond,starttime,now(3)),errormessage=concat('error=',r_code,',message=',r_msg),randno=nullwherestarttime=v_starttimeandrandno=v_randno;
end
//
createeventev4onscheduleatcurrent_timestamp+interval1houroncompletionpreservedisabledo
begin
declarer_codechar(5)default'00000';
declarer_msgtext;
declarev_errorinteger;
declarev_starttimedatetimedefaultnow(3);
declarev_randnointegerdefaultfloor(rand()*100001);
insertintot_event_history(dbname,eventname,starttime,randno)
#作业名
values(database(),'ev4',v_starttime,v_randno);
begin
#异常处理段
declarecontinuehandlerforsqlexception
begin
setv_error=1;
getdiagnosticscondition1r_code=returned_sqlstate,r_msg=message_text;
end;
#此处为实际调用的用户程序过程
callsp_unique(4);
end;
updatet_event_historysetendtime=now(3),issuccess=isnull(v_error),duration=timestampdiff(microsecond,starttime,now(3)),errormessage=concat('error=',r_code,',message=',r_msg),randno=nullwherestarttime=v_starttimeandrandno=v_randno;
end
//
为了记录每个事件执行的时间，在事件定义中增加了操作日志表的逻辑，因为每个事件中只多执行了一条insert，一条update，4个事件总共多执行8条很简单的语句，对测试的影响可以忽略不计。执行时间精确到毫秒。
•触发事件执行
mysql-vvv-uroot-p123456test-e"truncatet_target;altereventev1onscheduleatcurrent_timestampenable;altereventev2onscheduleatcurrent_timestampenable;altereventev3onscheduleatcurrent_timestampenable;altereventev4onscheduleatcurrent_timestampenable;"
该命令行顺序触发了4个事件，但不会等前一个执行完才执行下一个，而是立即向下执行。这可从命令的输出可以清除看到：
[mysql@hdp2~]$mysql-vvv-uroot-p123456test-e"truncatet_target;altereventev1onscheduleatcurrent_timestampenable;altereventev2onscheduleatcurrent_timestampenable;altereventev3onscheduleatcurrent_timestampenable;altereventev4onscheduleatcurrent_timestampenable;" mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure. -------------- truncatet_target -------------- QueryOK,0rowsaffected(0.06sec) -------------- altereventev1onscheduleatcurrent_timestampenable -------------- QueryOK,0rowsaffected(0.02sec) -------------- altereventev2onscheduleatcurrent_timestampenable -------------- QueryOK,0rowsaffected(0.00sec) -------------- altereventev3onscheduleatcurrent_timestampenable -------------- QueryOK,0rowsaffected(0.02sec) -------------- altereventev4onscheduleatcurrent_timestampenable -------------- QueryOK,0rowsaffected(0.00sec) Bye [mysql@hdp2~]$
•查看事件执行日志
mysql>select*fromtest.t_event_history; +--------+-----------+-------------------------+-------------------------+-----------+----------+--------------+--------+ |dbname|eventname|starttime|endtime|issuccess|duration|errormessage|randno| +--------+-----------+-------------------------+-------------------------+-----------+----------+--------------+--------+ |test|ev1|2019-07-3114:38:04.000|2019-07-3114:38:09.389|1|5389000|NULL|NULL| |test|ev2|2019-07-3114:38:04.000|2019-07-3114:38:09.344|1|5344000|NULL|NULL| |test|ev3|2019-07-3114:38:05.000|2019-07-3114:38:09.230|1|4230000|NULL|NULL| |test|ev4|2019-07-3114:38:05.000|2019-07-3114:38:09.344|1|4344000|NULL|NULL| +--------+-----------+-------------------------+-------------------------+-----------+----------+--------------+--------+ 4rowsinset(0.00sec)
可以看到，每个过程的执行均为4.83秒，又因为是并行执行的，因此总的执行之间为最慢的5.3秒，优化效果和shell后台进程方式几乎相同。
总结
以上所述是小编给大家介绍的将MySQL去重操作优化到极致的操作方法,希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！