將MySQL去重操作優(yōu)化到極致的操作方法
更新時(shí)間：2019年08月02日 09:12:53 作者：wzy0623
這篇文章主要介紹了如何將MySQL去重操作優(yōu)化到極致,本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下
•問(wèn)題提出
源表t_source結(jié)構(gòu)如下：
item_id int, created_time datetime, modified_time datetime, item_name varchar(20), other varchar(20)
要求：
1.源表中有100萬(wàn)條數(shù)據(jù)，其中有50萬(wàn)created_time和item_name重復(fù)。
2.要把去重后的50萬(wàn)數(shù)據(jù)寫入到目標(biāo)表。
3.重復(fù)created_time和item_name的多條數(shù)據(jù)，可以保留任意一條，不做規(guī)則限制。
•實(shí)驗(yàn)環(huán)境
Linux虛機(jī)：CentOS release 6.4；8G物理內(nèi)存（MySQL配置4G）；100G機(jī)械硬盤；雙物理CPU雙核，共四個(gè)處理器；MySQL 8.0.16。
•建立測(cè)試表和數(shù)據(jù)
-- 建立源表
create table t_source
( item_id int,
created_time datetime,
modified_time datetime,
item_name varchar(20),
other varchar(20)
);
-- 建立目標(biāo)表
create table t_target like t_source;
-- 生成100萬(wàn)測(cè)試數(shù)據(jù)，其中有50萬(wàn)created_time和item_name重復(fù)
delimiter //
create procedure sp_generate_data()
begin
set @i := 1;
while @i<=500000 do
set @created_time := date_add('2017-01-01',interval @i second);
set @modified_time := @created_time;
set @item_name := concat('a',@i);
insert into t_source
values (@i,@created_time,@modified_time,@item_name,'other');
set @i:=@i+1;
end while;
commit;
set @last_insert_id := 500000;
insert into t_source
select item_id + @last_insert_id,
created_time,
date_add(modified_time,interval @last_insert_id second),
item_name,
'other'
from t_source;
commit;
end
//
delimiter ;
call sp_generate_data();
-- 源表沒(méi)有主鍵或唯一性約束，有可能存在兩條完全一樣的數(shù)據(jù)，所以再插入一條記錄模擬這種情況。
insert into t_source select * from t_source where item_id=1;
源表中有1000001條記錄，去重后的目標(biāo)表應(yīng)該有500000條記錄。
mysql> select count(*),count(distinct created_time,item_name) from t_source;
+----------+----------------------------------------+
| count(*) | count(distinct created_time,item_name) |
+----------+----------------------------------------+
| 1000001 | 500000 |
+----------+----------------------------------------+
1 row in set (1.92 sec)
一、巧用索引與變量
1. 無(wú)索引對(duì)比測(cè)試
（1）使用相關(guān)子查詢
truncate t_target; insert into t_target select distinct t1.* from t_source t1 where item_id in (select min(item_id) from t_source t2 where t1.created_time=t2.created_time and t1.item_name=t2.item_name);
這個(gè)語(yǔ)句很長(zhǎng)時(shí)間都出不來(lái)結(jié)果，只看一下執(zhí)行計(jì)劃吧。
mysql> explain select distinct t1.* from t_source t1 where item_id in -> (select min(item_id) from t_source t2 where t1.created_time=t2.created_time and t1.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 | Using where; Using temporary | | 2 | DEPENDENT SUBQUERY | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 1.00 | Using where | +----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+ 2 rows in set, 3 warnings (0.00 sec)
主查詢和相關(guān)子查詢都是全表掃描，一共要掃描100萬(wàn)*100萬(wàn)數(shù)據(jù)行，難怪出不來(lái)結(jié)果。
（2）使用表連接
truncate t_target; insert into t_target select distinct t1.* from t_source t1, (select min(item_id) item_id,created_time,item_name from t_source group by created_time,item_name) t2 where t1.item_id = t2.item_id;
這種方法用時(shí)14秒，查詢計(jì)劃如下：
mysql> explain select distinct t1.* from t_source t1, (select min(item_id) item_id,created_time,item_name from t_source group by created_time,item_name) t2 where t1.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 | Using where; Using temporary | | 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 5 | test.t1.item_id | 10 | 100.00 | Distinct | | 2 | DERIVED | t_source | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 100.00 | Using temporary | +----+-------------+------------+------------+------+---------------+-------------+---------+-----------------+--------+----------+------------------------------+ 3 rows in set, 1 warning (0.00 sec)
•內(nèi)層查詢掃描t_source表的100萬(wàn)行，建立臨時(shí)表，找出去重后的最小item_id，生成導(dǎo)出表derived2，此導(dǎo)出表有50萬(wàn)行。
•MySQL會(huì)在導(dǎo)出表derived2上自動(dòng)創(chuàng)建一個(gè)item_id字段的索引auto_key0。
•外層查詢也要掃描t_source表的100萬(wàn)行數(shù)據(jù)，在與導(dǎo)出表做鏈接時(shí)，對(duì)t_source表每行的item_id，使用auto_key0索引查找導(dǎo)出表中匹配的行，并在此時(shí)優(yōu)化distinct操作，在找到第一個(gè)匹配的行后即停止查找同樣值的動(dòng)作。
（3）使用變量
set @a:='1000-01-01 00:00:00'; set @b:=' '; set @f:=0; truncate t_target; insert into t_target select item_id,created_time,modified_time,item_name,other from (select t0.*,if(@a=created_time and @b=item_name,@f:=0,@f:=1) f, @a:=created_time,@b:=item_name from (select * from t_source order by created_time,item_name) t0) t1 where f=1;
這種方法用時(shí)13秒，查詢計(jì)劃如下：
mysql> explain select item_id,created_time,modified_time,item_name,other -> from -> (select t0.*,if(@a=created_time and @b=item_name,@f:=0,@f:=1) f, @a:=created_time,@b:=item_name -> from -> (select * from t_source order by created_time,item_name) t0) t1 where f=1; +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+ | 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | const | 10 | 100.00 | NULL | | 2 | DERIVED | <derived3> | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 100.00 | NULL | | 3 | DERIVED | t_source | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 100.00 | Using filesort | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+ 3 rows in set, 5 warnings (0.00 sec)
•最內(nèi)層的查詢掃描t_source表的100萬(wàn)行，并使用文件排序，生成導(dǎo)出表derived3。
•第二層查詢要掃描derived3的100萬(wàn)行，生成導(dǎo)出表derived2，完成變量的比較和賦值，并自動(dòng)創(chuàng)建一個(gè)導(dǎo)出列f上的索引auto_key0。
•最外層使用auto_key0索引掃描derived2得到去重的結(jié)果行。
與上面方法2比較，總的掃描行數(shù)不變，都是200萬(wàn)行。只存在一點(diǎn)微小的差別，這次自動(dòng)生成的索引是在常量列 f 上，而表關(guān)聯(lián)自動(dòng)生成的索引是在item_id列上，所以查詢時(shí)間幾乎相同。
至此，我們還沒(méi)有在源表上創(chuàng)建任何索引。無(wú)論使用哪種寫法，要查重都需要對(duì)created_time和item_name字段進(jìn)行排序，因此很自然地想到，如果在這兩個(gè)字段上建立聯(lián)合索引，利用索引本身有序的特性消除額外排序，從而提高查詢性能。
2. 建立created_time和item_name上的聯(lián)合索引對(duì)比測(cè)試
-- 建立created_time和item_name字段的聯(lián)合索引 create index idx_sort on t_source(created_time,item_name,item_id); analyze table t_source;
（1）使用相關(guān)子查詢
truncate t_target; insert into t_target select distinct t1.* from t_source t1 where item_id in (select min(item_id) from t_source t2 where t1.created_time=t2.created_time and t1.item_name=t2.item_name);
本次用時(shí)19秒，查詢計(jì)劃如下：
mysql> explain select distinct t1.* from t_source t1 where item_id in -> (select min(item_id) from t_source t2 where t1.created_time=t2.created_time and t1.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 | Using where; Using temporary | | 2 | DEPENDENT SUBQUERY | t2 | NULL | ref | idx_sort | idx_sort | 89 | test.t1.created_time,test.t1.item_name | 2 | 100.00 | Using index | +----+--------------------+-------+------------+------+---------------+----------+---------+----------------------------------------+--------+----------+------------------------------+ 2 rows in set, 3 warnings (0.00 sec)
•外層查詢的t_source表是驅(qū)動(dòng)表，需要掃描100萬(wàn)行。
•對(duì)于驅(qū)動(dòng)表每行的item_id，通過(guò)idx_sort索引查詢出兩行數(shù)據(jù)。
（2）使用表連接
truncate t_target; insert into t_target select distinct t1.* from t_source t1, (select min(item_id) item_id,created_time,item_name from t_source group by created_time,item_name) t2 where t1.item_id = t2.item_id;
本次用時(shí)13秒，查詢計(jì)劃如下：
mysql> explain select distinct t1.* from t_source t1, -> (select min(item_id) item_id,created_time,item_name from t_source group by created_time,item_name) t2 -> where t1.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 | Using where; Using temporary | | 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 5 | test.t1.item_id | 10 | 100.00 | Distinct | | 2 | DERIVED | t_source | NULL | index | idx_sort | idx_sort | 94 | NULL | 997281 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------+--------+----------+------------------------------+ 3 rows in set, 1 warning (0.00 sec)
和沒(méi)有索引相比，子查詢雖然從全表掃描變?yōu)榱巳饕龗呙?，但還是需要掃描100萬(wàn)行記錄。因此查詢性能提升并不是明顯。
（3）使用變量
set @a:='1000-01-01 00:00:00'; set @b:=' '; set @f:=0; truncate t_target; insert into t_target select item_id,created_time,modified_time,item_name,other from (select t0.*,if(@a=created_time and @b=item_name,@f:=0,@f:=1) f, @a:=created_time,@b:=item_name from (select * from t_source order by created_time,item_name) t0) t1 where f=1;
本次用時(shí)13秒，查詢計(jì)劃與沒(méi)有索引時(shí)的完全相同。可見索引對(duì)這種寫法沒(méi)有作用。能不能消除嵌套，只用一層查詢出結(jié)果呢？
（4）使用變量，并且消除嵌套查詢
set @a:='1000-01-01 00:00:00'; set @b:=' '; truncate t_target; insert into t_target select * from t_source force index (idx_sort) where (@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;
本次用時(shí)12秒，查詢計(jì)劃如下：
mysql> explain select * from t_source force index (idx_sort) -> where (@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; +----+-------------+----------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+ | 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 | Using where | +----+-------------+----------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+ 1 row in set, 3 warnings (0.00 sec)
該語(yǔ)句具有以下特點(diǎn)：
•消除了嵌套子查詢，只需要對(duì)t_source表進(jìn)行一次全索引掃描，查詢計(jì)劃已達(dá)最優(yōu)。
•無(wú)需distinct二次查重。
•變量判斷與賦值只出現(xiàn)在where子句中。
•利用索引消除了filesort。
在MySQL 8之前，該語(yǔ)句是單線程去重的最佳解決方案。仔細(xì)分析這條語(yǔ)句，發(fā)現(xiàn)它巧妙地利用了SQL語(yǔ)句的邏輯查詢處理步驟和索引特性。一條SQL查詢的邏輯步驟為：
1.執(zhí)行笛卡爾乘積（交叉連接）
2.應(yīng)用ON篩選器（連接條件）
3.添加外部行（outer join）
4.應(yīng)用where篩選器
5.分組
6.應(yīng)用cube或rollup
7.應(yīng)用having篩選器
8.處理select列表
9.應(yīng)用distinct子句
10.應(yīng)用order by子句
11.應(yīng)用limit子句
每條查詢語(yǔ)句的邏輯執(zhí)行步驟都是這11步的子集。拿這條查詢語(yǔ)句來(lái)說(shuō)，其執(zhí)行順序?yàn)椋簭?qiáng)制通過(guò)索引idx_sort查找數(shù)據(jù)行 -> 應(yīng)用where篩選器 -> 處理select列表 -> 應(yīng)用order by子句。
為了使變量能夠按照created_time和item_name的排序順序進(jìn)行賦值和比較，必須按照索引順序查找數(shù)據(jù)行。這里的force index (idx_sort)提示就起到了這個(gè)作用，必須這樣寫才能使整條查重語(yǔ)句成立。否則，因?yàn)橄葤呙璞聿盘幚砼判?，因此不能保證變量賦值的順序，也就不能確保查詢結(jié)果的正確性。order by子句同樣不可忽略，否則即使有force index提示，MySQL也會(huì)使用全表掃描而不是全索引掃描，從而使結(jié)果錯(cuò)誤。索引同時(shí)保證了created_time,item_name的順序，避免了文件排序。force index (idx_sort)提示和order by子句缺一不可，索引idx_sort在這里可謂恰到好處、一舉兩得。
查詢語(yǔ)句開始前，先給變量初始化為數(shù)據(jù)中不可能出現(xiàn)的值，然后進(jìn)入where子句從左向右判斷。先比較變量和字段的值，再將本行created_time和item_name的值賦給變量，按created_time、item_name的順序逐行處理。item_name是字符串類型，(@b:=item_name)不是有效的布爾表達(dá)式，因此要寫成(@b:=item_name) is not null。
最后補(bǔ)充一句，這里忽略了“insert into t_target select * from t_source group by created_time,item_name;”的寫法，因?yàn)樗?code>“sql_mode='ONLY_FULL_GROUP_BY'”的限制。
二、利用窗口函數(shù)
MySQL 8中新增的窗口函數(shù)使得原來(lái)麻煩的去重操作變得很簡(jiǎn)單。
truncate t_target; insert into t_target select item_id, created_time, modified_time, item_name, other from (select *, row_number() over(partition by created_time,item_name) as rn from t_source) t where rn=1;
這個(gè)語(yǔ)句執(zhí)行只需要12秒，而且寫法清晰易懂，其查詢計(jì)劃如下：
mysql> explain select item_id, created_time, modified_time, item_name, other -> from (select *, row_number() over(partition by created_time,item_name) as rn -> from t_source) t where rn=1; +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+ | 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 8 | const | 10 | 100.00 | NULL | | 2 | DERIVED | t_source | NULL | ALL | NULL | NULL | NULL | NULL | 997281 | 100.00 | Using filesort | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+ 2 rows in set, 2 warnings (0.00 sec)
該查詢對(duì)t_source表進(jìn)行了一次全表掃描，同時(shí)用filesort對(duì)表按分區(qū)字段created_time、item_name進(jìn)行了排序。外層查詢從每個(gè)分區(qū)中保留一條數(shù)據(jù)。因?yàn)橹貜?fù)created_time和item_name的多條數(shù)據(jù)中可以保留任意一條，所以oevr中不需要使用order by子句。
從執(zhí)行計(jì)劃看，窗口函數(shù)去重語(yǔ)句似乎沒(méi)有消除嵌套查詢的變量去重好，但此方法實(shí)際執(zhí)行是最快的。
MySQL窗口函數(shù)說(shuō)明參見“https://dev.mysql.com/doc/refman/8.0/en/window-functions.html”。
三、多線程并行執(zhí)行
前面已經(jīng)將單條查重語(yǔ)句調(diào)整到最優(yōu)，但還是以單線程方式執(zhí)行。能否利用多處理器，讓去重操作多線程并行執(zhí)行，從而進(jìn)一步提高速度呢？比如我的實(shí)驗(yàn)環(huán)境是4處理器，如果使用4個(gè)線程同時(shí)執(zhí)行查重SQL，理論上應(yīng)該接近4倍的性能提升。
1. 數(shù)據(jù)分片
在生成測(cè)試數(shù)據(jù)時(shí)，created_time采用每條記錄加一秒的方式，也就是最大和在最小的時(shí)間差為50萬(wàn)秒，而且數(shù)據(jù)均勻分布，因此先把數(shù)據(jù)平均分成4份。
（1）查詢出4份數(shù)據(jù)的created_time邊界值
mysql> 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;
+---------------------+---------------------+---------------------+---------------------+
| dt1 | dt2 | dt3 | dt4 |
+---------------------+---------------------+---------------------+---------------------+
| 2017-01-02 10:43:20 | 2017-01-03 21:26:40 | 2017-01-05 08:10:00 | 2017-01-06 18:53:20 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
（2）查看每份數(shù)據(jù)的記錄數(shù)，確認(rèn)數(shù)據(jù)平均分布
mysql> 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; +---------------------+---------------------+----------+ | min_dt | max_dt | count(*) | +---------------------+---------------------+----------+ | 2017-01-01 | 2017-01-02 10:43:20 | 249999 | | 2017-01-02 10:43:20 | 2017-01-03 21:26:40 | 250000 | | 2017-01-03 21:26:40 | 2017-01-05 08:10:00 | 250000 | | 2017-01-05 08:10:00 | 2017-01-06 18:53:20 | 250002 | +---------------------+---------------------+----------+ 4 rows in set (4.86 sec)
4份數(shù)據(jù)的并集應(yīng)該覆蓋整個(gè)源數(shù)據(jù)集，并且數(shù)據(jù)之間是不重復(fù)的。也就是說(shuō)4份數(shù)據(jù)的created_time要連續(xù)且互斥，連續(xù)保證處理全部數(shù)據(jù)，互斥確保了不需要二次查重。實(shí)際上這和時(shí)間范圍分區(qū)的概念類似，或許用分區(qū)表更好些，只是這里省略了重建表的步驟。
2. 建立查重的存儲(chǔ)過(guò)程
有了以上信息我們就可以寫出4條語(yǔ)句處理全部數(shù)據(jù)。為了調(diào)用接口盡量簡(jiǎn)單，建立下面的存儲(chǔ)過(guò)程。
delimiter //
create procedure sp_unique(i smallint)
begin
set @a:='1000-01-01 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;
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;
end if;
end
//
查詢語(yǔ)句的執(zhí)行計(jì)劃如下：
mysql> explain select * from t_source force index (idx_sort)
-> where created_time >= date_add('2017-01-01',interval (1-1)*125000 second)
-> and created_time < date_add('2017-01-01',interval 1*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;
+----+-------------+----------+------------+-------+---------------+----------+---------+------+--------+----------+-----------------------+
| 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 | Using index condition |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+--------+----------+-----------------------+
1 row in set, 3 warnings (0.00 sec)
MySQL優(yōu)化器進(jìn)行索引范圍掃描，并且使用索引條件下推（ICP）優(yōu)化查詢。
3. 并行執(zhí)行
下面分別使用shell后臺(tái)進(jìn)程和MySQL Schedule Event實(shí)現(xiàn)并行。
（1）shell后臺(tái)進(jìn)程
•建立duplicate_removal.sh文件，內(nèi)容如下：
#!/bin/bash
mysql -vvv -u root -p123456 test -e "truncate t_target" &>/dev/null
date '+%H:%M:%S'
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:%S'
•執(zhí)行腳本文件
./duplicate_removal.sh
執(zhí)行輸出如下：
[mysql@hdp2~]$./duplicate_removal.sh 14:27:30 14:27:35
這種方法用時(shí)5秒，并行執(zhí)行的4個(gè)過(guò)程調(diào)用分別用時(shí)為4.87秒、4.88秒、4.91秒、4.73秒：
[mysql@hdp2~]$cat par_sql1_1.log | sed '/^$/d' mysql: [Warning] Using a password on the command line interface can be insecure. -------------- call sp_unique(1) -------------- Query OK, 124999 rows affected (4.87 sec) Bye [mysql@hdp2~]$cat par_sql1_2.log | sed '/^$/d' mysql: [Warning] Using a password on the command line interface can be insecure. -------------- call sp_unique(2) -------------- Query OK, 125000 rows affected (4.88 sec) Bye [mysql@hdp2~]$cat par_sql1_3.log | sed '/^$/d' mysql: [Warning] Using a password on the command line interface can be insecure. -------------- call sp_unique(3) -------------- Query OK, 125000 rows affected (4.91 sec) Bye [mysql@hdp2~]$cat par_sql1_4.log | sed '/^$/d' mysql: [Warning] Using a password on the command line interface can be insecure. -------------- call sp_unique(4) -------------- Query OK, 125001 rows affected (4.73 sec) Bye [mysql@hdp2~]$
可以看到，每個(gè)過(guò)程的執(zhí)行時(shí)間均4.85，因?yàn)槭遣⑿袌?zhí)行，總的過(guò)程執(zhí)行時(shí)間為最慢的4.91秒，比單線程速度提高了2.5倍。
（2）MySQL Schedule Event
•建立事件歷史日志表
-- 用于查看事件執(zhí)行時(shí)間等信息 create table t_event_history ( dbname varchar(128) not null default '', eventname varchar(128) not null default '', starttime datetime(3) not null default '1000-01-01 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 );
•為每個(gè)并發(fā)線程創(chuàng)建一個(gè)事件
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)
#作業(yè)名
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;
#此處為實(shí)際調(diào)用的用戶程序過(guò)程
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)
#作業(yè)名
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;
#此處為實(shí)際調(diào)用的用戶程序過(guò)程
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)
#作業(yè)名
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;
#此處為實(shí)際調(diào)用的用戶程序過(guò)程
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)
#作業(yè)名
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;
#此處為實(shí)際調(diào)用的用戶程序過(guò)程
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
//
為了記錄每個(gè)事件執(zhí)行的時(shí)間，在事件定義中增加了操作日志表的邏輯，因?yàn)槊總€(gè)事件中只多執(zhí)行了一條insert，一條update，4個(gè)事件總共多執(zhí)行8條很簡(jiǎn)單的語(yǔ)句，對(duì)測(cè)試的影響可以忽略不計(jì)。執(zhí)行時(shí)間精確到毫秒。
•觸發(fā)事件執(zhí)行
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;"
該命令行順序觸發(fā)了4個(gè)事件，但不會(huì)等前一個(gè)執(zhí)行完才執(zhí)行下一個(gè)，而是立即向下執(zhí)行。這可從命令的輸出可以清除看到：
[mysql@hdp2~]$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;" mysql: [Warning] Using a password on the command line interface can be insecure. -------------- truncate t_target -------------- Query OK, 0 rows affected (0.06 sec) -------------- alter event ev1 on schedule at current_timestamp enable -------------- Query OK, 0 rows affected (0.02 sec) -------------- alter event ev2 on schedule at current_timestamp enable -------------- Query OK, 0 rows affected (0.00 sec) -------------- alter event ev3 on schedule at current_timestamp enable -------------- Query OK, 0 rows affected (0.02 sec) -------------- alter event ev4 on schedule at current_timestamp enable -------------- Query OK, 0 rows affected (0.00 sec) Bye [mysql@hdp2~]$
•查看事件執(zhí)行日志
mysql> select * from test.t_event_history; +--------+-----------+-------------------------+-------------------------+-----------+----------+--------------+--------+ | dbname | eventname | starttime | endtime | issuccess | duration | errormessage | randno | +--------+-----------+-------------------------+-------------------------+-----------+----------+--------------+--------+ | test | ev1 | 2019-07-31 14:38:04.000 | 2019-07-31 14:38:09.389 | 1 | 5389000 | NULL | NULL | | test | ev2 | 2019-07-31 14:38:04.000 | 2019-07-31 14:38:09.344 | 1 | 5344000 | NULL | NULL | | test | ev3 | 2019-07-31 14:38:05.000 | 2019-07-31 14:38:09.230 | 1 | 4230000 | NULL | NULL | | test | ev4 | 2019-07-31 14:38:05.000 | 2019-07-31 14:38:09.344 | 1 | 4344000 | NULL | NULL | +--------+-----------+-------------------------+-------------------------+-----------+----------+--------------+--------+ 4 rows in set (0.00 sec)
可以看到，每個(gè)過(guò)程的執(zhí)行均為4.83秒，又因?yàn)槭遣⑿袌?zhí)行的，因此總的執(zhí)行之間為最慢的5.3秒，優(yōu)化效果和shell后臺(tái)進(jìn)程方式幾乎相同。
總結(jié)
以上所述是小編給大家介紹的將MySQL去重操作優(yōu)化到極致的操作方法,希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！
如果你覺(jué)得本文對(duì)你有幫助，歡迎轉(zhuǎn)載，煩請(qǐng)注明出處，謝謝！
相關(guān)文章
使用mydumper多線程備份MySQL數(shù)據(jù)庫(kù)
MySQL在備份方面包含了自身的mysqldump工具，但其只支持單線程工作，這就使得它無(wú)法迅速的備份數(shù)據(jù)。而 mydumper作為一個(gè)實(shí)用工具，能夠良好支持多線程工作，這使得它在處理速度方面十倍于傳統(tǒng)的2013-11-11
Canal實(shí)現(xiàn)MYSQL實(shí)時(shí)數(shù)據(jù)同步的示例代碼
本文詳細(xì)介紹了Canal部署的全過(guò)程,包括Canal-Admin、Canal-Server和Canal-Adapter的安裝和配置,涵蓋創(chuàng)建目錄、修改配置文件、容器部署等步驟,適用于MYSQL8.0+環(huán)境,旨在幫助用戶實(shí)現(xiàn)MYSQL實(shí)時(shí)數(shù)據(jù)同步2024-11-11
mysql千萬(wàn)級(jí)數(shù)據(jù)量根據(jù)索引優(yōu)化查詢速度的實(shí)現(xiàn)
這篇文章主要介紹了mysql千萬(wàn)級(jí)數(shù)據(jù)量根據(jù)索引優(yōu)化查詢速度的實(shí)現(xiàn)，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-03-03
這篇文章主要介紹了最全的mysql查詢語(yǔ)句整理,需要的朋友可以參考下2016-06-06
這篇文章主要為大家介紹了Mysql索引如何實(shí)現(xiàn)更加簡(jiǎn)易的查找方案，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2022-05-05
Mysql獲取id最大值、表的記錄總數(shù)等相關(guān)問(wèn)題的方法匯總
在做網(wǎng)站開發(fā)時(shí)，我們也許會(huì)想要取得mysql里id最大的一條記錄，這個(gè)其實(shí)很簡(jiǎn)單。這篇文章給大家整理了獲取一個(gè)表的記錄數(shù)、獲取一個(gè)表的最大id、獲取一個(gè)表的auto_increment值等相關(guān)問(wèn)題的答案，有需要的朋友們可以參考借鑒。2016-09-09
MySQL中一條查詢SQL語(yǔ)句的完整執(zhí)行流程
通常我們?cè)谑褂肕ySQL時(shí),我們看到的只是輸入一條語(yǔ)句,返回一個(gè)結(jié)果,卻不知道這條語(yǔ)句在MySQL內(nèi)部的執(zhí)行過(guò)程,這篇文章主要給大家介紹了關(guān)于MySQL中一條查詢SQL語(yǔ)句的完整執(zhí)行流程,需要的朋友可以參考下2024-05-05
最近看到很多sql里用到where 1=1，原來(lái)覺(jué)得這沒(méi)用嘛，但是又想到如果沒(méi)用為什么要寫呢？2011-09-09
MySQL Semisynchronous Replication介紹
這篇文章主要介紹了MySQL Semisynchronous Replication介紹,本文講解了Semisynchronous Replication 定義、,需要的朋友可以參考下2015-05-05
大概幾個(gè)月之前項(xiàng)目中用到事務(wù),需要保證數(shù)據(jù)的強(qiáng)一致性,期間也用到了mysql的鎖,所以本文打算總結(jié)一下mysql的鎖機(jī)制,這篇文章主要給大家介紹了關(guān)于mysql中鎖機(jī)制的相關(guān)資料,需要的朋友可以參考下2021-09-09