将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
