将MySQL去重操作优化到极致之三弹连发（一）：巧用索引与变量
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了将MySQL去重操作优化到极致之三弹连发（一）：巧用索引与变量，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7620字，纯文字阅读大概需要11分钟。
内容图文
create table t_source
(
item_id int,
created_time datetime,
modified_time datetime,
item_name varchar(20),
other varchar(20)
); 2. 建立目标表
create table t_target like t_source; 3. 生成100万测试数据，其中有50万created_time和item_name重复
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;
图一可以看到，源表中有1000001条记录，去重后的目标表应该有500000条记录。
三、无索引对比测试
1. 使用相关子查询
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);
commit; 这个语句很长时间都出不来结果，只看一下执行计划吧。如图二所示，要进行100万*100万次表扫描，难怪出不来结果。
图二
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;
commit; 这种方法用时35秒，查询计划如图三所示。
图三
（1）内层查询扫描t_source表的100万行，建立临时表，并使用文件排序找出去重后的最小item_id，生成导出表derived2，此导出表有50万行。（2）MySQL会在临时表derived2上自动创建一个item_id字段的索引auto_key0。
（3）外层查询也要扫描t_source表的100万行数据，在与临时表做链接时，对t_source表每行的item_id，使用auto_key0索引查找临时表中匹配的行，并在此时优化distinct操作，在找到第一个匹配的行后即停止查找同样值的动作。
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; 这种方法用时14秒，查询计划如图四所示。
图四
（1）最内层的查询扫描t_source表的100万行，并使用文件排序，生成导出表derived3。（2）第二层查询要扫描derived3的100万行，生成导出表derived2，完成变量的比较和赋值，并自动创建一个导出列f上的索引auto_key0。
（3）最外层使用auto_key0索引扫描derived2得到去重的结果行。
与方法2比较，变量方法消除了表关联，查询速度提高了2.7倍。
至此，我们还没有在源表上创建任何索引。无论使用哪种写法，要查重都需要对created_time和item_name字段进行排序，因此很自然地想到，如果在这两个字段上建立联合索引，可以用于消除filesort，从而提高查询性能。四、建立created_time和item_name上的联合索引对比测试
1. 建立created_time和item_name字段的联合索引。
create index idx_sort on t_source(created_time,item_name,item_id);
analyze table t_source; 2. 使用相关子查询
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);
commit; 这种方法用时20秒，查询计划如图五所示。
图五
（1）外层查询的t_source表是驱动表，需要扫描100万行。（2）对于驱动表每行的item_id，通过idx_sort索引查询出一行数据。
3. 使用表连接查重
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;
commit; 这种方法用时25秒，查询计划如图六所示。
图六
和没有索引相比，子查询虽然从全表扫描变为了全索引扫描，但还是需要扫描100万行记录。因此查询性能提升36%，并不是很多。4. 使用变量
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;这种方法用时14秒，查询计划与没有索引时的相同，如图四所示。可见索引对这种写法没有作用。能不能消除嵌套，只用一层查询出结果呢？
5. 使用变量，并且消除嵌套查询
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; 这种方法用时8秒，查询计划如图七所示。
图七
该语句具有以下特点。（1）消除了嵌套子查询，只需要对t_source表进行一次全索引扫描，查询计划已达最优。
（2）无需distinct二次查重。
（3）变量判断与赋值只出现在where子句中。
（4）利用索引消除了filesort。
该语句就是吴老师的单线程解决方案。仔细分析这条语句，发现它巧妙地利用了SQL语句的逻辑查询处理步骤和索引特性。
一条SQL查询的逻辑步骤为：
步骤1：执行笛卡尔乘积（交叉连接）
步骤2：应用ON筛选器（连接条件）
步骤3：添加外部行（outer join）
步骤4：应用where筛选器
步骤5：分组
步骤6：应用cube或rollup
步骤7：应用having筛选器
步骤8：处理select列表
步骤9：应用distinct子句
步骤10：应用order by子句
步骤11：应用limit子句
每条查询语句的逻辑执行步骤都是这11步的子集。拿这条查询语句来说，其执行顺序为：
强制通过索引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‘”的限制。
五、总结
看似一个简单的部分查重语句，要想完美优化，也必须清晰理解很多知识点。如：查询语句的逻辑执行顺序、使用索引优化排序、强制按索引顺序扫描表、索引覆盖、半连接查询优化、布尔表达式等。基础要扎实，应用要灵活，方能书写出高效的SQL语句。
将MySQL去重操作优化到极致之三弹连发（一）：巧用索引与变量
标签：pre strong 提示 count 可谓 分组 全表扫描 index 5.6
本文系统来源：http://blog.csdn.net/wzy0623/article/details/54377986
内容总结
以上是互联网集市为您收集整理的将MySQL去重操作优化到极致之三弹连发（一）：巧用索引与变量全部内容，希望文章能够帮你解决将MySQL去重操作优化到极致之三弹连发（一）：巧用索引与变量所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。