将MySQL去重操作优化到极致之三弹连发（一）：巧用索引与变量

http://blog.csdn.net/wzy0623/article/details/54377986

实验准备:
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; 由于机器性能的差异,使用表连接方式,我的环境耗时14s
执行计划如下:

可以看到MySQL 给 t1表的item_id自动创建了一个索引.

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; 耗时10s左右.
效率尚可,省时省力.


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; 耗时18s
执行计划如下:



以上都是没有添加任何索引的情况.

添加索引如下:
create index idx_sort on t_source(created_time,item_name,item_id);
analyze table t_source;

创建索引之后,
使用表连接查询方式耗时11s,小幅提升.
使用MySQL特性的方式,耗时11-12s,反而更慢.
使用MySQL自定义变量的方式,耗时还是18s.

很显然,MySQL自定义变量的方式,其实没有利用索引.


最终改进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;

耗时11s.
该语句具有以下特点。
（1）消除了嵌套子查询，只需要对t_source表进行一次全索引扫描，查询计划已达最优。
（2）无需distinct二次查重。
（3）变量判断与赋值只出现在where子句中。
（4）利用索引消除了filesort。

强制通过索引idx_sort查找数据行 -> 应用where筛选器 -> 处理select列表 -> 应用order by子句。

为了使变量能够按照created_time和item_name的排序顺序进行赋值和比较，必须按照索引顺序查找数据行。这里的force index (idx_sort)提示就起到了这个作用，必须这样写才能使整条查重语句成立。否则，因为先扫描表才处理排序，因此不能保证变量赋值的顺序，也就不能确保查询结果的正确性。order by子句同样不可忽略，否则即使有force index提示，MySQL也会使用全表扫描而不是全索引扫描，从而使结果错误。
索引同时保证了created_time,item_name的顺序，避免了文件排序。force index (idx_sort)提示和order by子句缺一不可，索引idx_sort在这里可谓恰到好处、一举两得。
查询语句开始前，先给变量初始化为数据中不可能出现的值，然后进入where子句从左向右判断。先比较变量和字段的值，再将本行created_time和item_name的值赋给变量，按created_time,item_name的顺序逐行处理。item_name是字符串类型，(@b:=item_name)不是有效的布尔表达式，因此要写成(@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'”的限制。

运行耗时和原文有出入,可能是因为我的环境是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;

使用上述索引,终极改进的SQL 耗时可以降到 9.5s

参考:
http://blog.csdn.net/wzy0623/article/details/54378367
http://blog.csdn.net/wzy0623/article/details/54378575