將MySQL去重操作優(yōu)化到極致之三彈連發(fā)（一）：巧用索引與變量
http://blog.csdn.net/wzy0623/article/details/54377986
實(shí)驗(yàn)準(zhǔn)備:
MySQL 5.6.14
-
create table t_source
-
(
-
item_id int,
-
created_time datetime,
-
modified_time datetime,
-
item_name varchar(20),
-
other varchar(20)
-
);
-
-
create table t_target like t_source;
-
-
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();
-
-
insert into t_source
-
select * from t_source where item_id=1;
-
commit;
-
-
select count(*),count(distinct created_time,item_name) from t_source;
1.使用表連接查重
-
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 t3 group by created_time,item_name) t2
-
where t1.item_id = t2.item_id;
-
commit;
由于機(jī)器性能的差異,使用表連接方式,我的環(huán)境耗時(shí)14s
執(zhí)行計(jì)劃如下:
可以看到MySQL 給 t1表的item_id自動(dòng)創(chuàng)建了一個(gè)索引.
2.使用MySQL特性
-
truncate t_target;
-
insert into t_target
-
select min(item_id),created_time,modified_time,item_name,other
-
from t_source
-
group by created_time,item_name;
-
commit;
耗時(shí)10s左右.
效率尚可,省時(shí)省力.
3.使用自定義變量
-
set @a:='0000-00-00 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;
-
commit;
耗時(shí)18s
執(zhí)行計(jì)劃如下:
以上都是沒有添加任何索引的情況.
添加索引如下:
create index idx_sort on t_source(created_time,item_name,item_id);
analyze table t_source;
創(chuàng)建索引之后,
使用表連接查詢方式耗時(shí)11s,小幅提升.
使用MySQL特性的方式,耗時(shí)11-12s,反而更慢.
使用MySQL自定義變量的方式,耗時(shí)還是18s.
很顯然,MySQL自定義變量的方式,其實(shí)沒有利用索引.
最終改進(jìn)SQL
-
set @a:='0000-00-00 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;
-
commit;
耗時(shí)11s.
該語(yǔ)句具有以下特點(diǎn)。
（1）消除了嵌套子查詢，只需要對(duì)t_source表進(jìn)行一次全索引掃描，查詢計(jì)劃已達(dá)最優(yōu)。
（2）無(wú)需distinct二次查重。
（3）變量判斷與賦值只出現(xiàn)在where子句中。
（4）利用索引消除了filesort。
強(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)橄葤呙璞聿盘幚砼判颍虼瞬荒鼙ＷC變量賦值的順序，也就不能確保查詢結(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。
“insert into t_target select * from t_source group by created_time,item_name;”的寫法，它受“sql_mode='ONLY_FULL_GROUP_BY'”的限制。
運(yùn)行耗時(shí)和原文有出入,可能是因?yàn)槲业沫h(huán)境是SSD的緣故.
另外,避免回表的開銷,可以增加索引的字段
drop index idx_sort on t_source;
create index idx_sort on t_source(created_time,item_name,item_id,modified_time,other);
analyze table t_source;
使用上述索引,終極改進(jìn)的SQL 耗時(shí)可以降到 9.5s
參考:
http://blog.csdn.net/wzy0623/article/details/54378367
http://blog.csdn.net/wzy0623/article/details/54378575
當(dāng)前標(biāo)題：將MySQL去重操作優(yōu)化到極致之三彈連發(fā)
新聞來(lái)源：
http://weahome.cn/article/jpippj.html
重慶分公司
重慶分公司
