189 8069 5689

将MySQL去重操作优化到极致之三弹连发

 

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



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

实验准备:
MySQL 5.6.14

  1. create table t_source
  2. (
  3.     item_id int,
  4.     created_time datetime,
  5.     modified_time datetime,
  6.     item_name varchar(20),
  7.     other varchar(20)
  8. );

  10. create table t_target like t_source;

  12. delimiter //
  13. create procedure sp_generate_data()
  14. begin
  15.     set @i := 1;
  16.       
  17.     while @i<=500000 do
  18.         set @created_time := date_add('2017-01-01',interval @i second);
  19.         set @modified_time := @created_time;
  20.         set @item_name := concat('a',@i);
  21.         insert into t_source
  22.         values (@i,@created_time,@modified_time,@item_name,'other');
  23.         set @i:=@i+1;
  24.     end while;
  25.     commit;
  26.       
  27.     set @last_insert_id := 500000;
  28.     insert into t_source
  29.     select item_id + @last_insert_id,
  30.            created_time,
  31.            date_add(modified_time,interval @last_insert_id second),
  32.            item_name,
  33.            'other'
  34.       from t_source;
  35.     commit;
  36. end
  37. //
  38. delimiter ;
  39.       
  40. call sp_generate_data();

  42. insert into t_source
  43. select * from t_source where item_id=1;
  44. commit;

  46. select count(*),count(distinct created_time,item_name) from t_source;
1.使用表连接查重
  1. truncate t_target;
  2. insert into t_target
  3. select distinct t1.* from t_source t1,
  4. (select min(item_id) item_id,created_time,item_name from t_source t3 group by created_time,item_name) t2
  5. where t1.item_id = t2.item_id;
  6. commit;
由于机器性能的差异,使用表连接方式,我的环境耗时14s
执行计划如下:
将MySQL去重操作优化到极致之三弹连发
可以看到MySQL 给 t1表的item_id自动创建了一个索引.

2.使用MySQL特性

  1. truncate t_target;
  2. insert into t_target
  3. select min(item_id),created_time,modified_time,item_name,other
  4. from t_source
  5. group by created_time,item_name;
  6. commit;
耗时10s左右.
效率尚可,省时省力.


3.使用自定义变量

  1. set @a:='0000-00-00 00:00:00';
  2. set @b:=' ';
  3. set @f:=0;
  4. truncate t_target;
  5. insert into t_target
  6. select
  7.     item_id, created_time, modified_time, item_name, other
  8. from
  9.     (
  10.         select
  11.             t0 . *,
  12.                 if(@a = created_time and @b = item_name, @f:=0, @f:=1) f,
  13.                 @a:=created_time,
  14.                 @b:=item_name
  15.         from
  16.             (
  17.                 select
  18.                     *
  19.                 from
  20.                     t_source
  21.                 order by created_time , item_name
  22.             ) t0
  23.     ) t1
  24. where
  25.     f = 1;
  26. commit;
耗时18s
执行计划如下:
将MySQL去重操作优化到极致之三弹连发


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

添加索引如下:
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

  1. set @a:='0000-00-00 00:00:00';
  2. set @b:=' ';
  3. truncate t_target;
  4. insert into t_target
  5. select * from t_source force index (idx_sort)
  6.  where (@a!=created_time or @b!=item_name) and (@a:=created_time) is not null and (@b:=item_name) is not null
  7.  order by created_time,item_name;
  8. commit;
将MySQL去重操作优化到极致之三弹连发

耗时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

新闻名称：将MySQL去重操作优化到极致之三弹连发
文章分享：http://cdxtjz.cn/article/jpippj.html

其他资讯