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

        元旦假期收到阿里吴老师来电,被告知已将MySQL查重SQL优化到极致:100万原始数据,其中50万重复,把去重后的50万数据写入目标表只需要9秒钟。这是一个惊人的数字,要知道仅是insert 50万条记录也需要些时间的。于是来了兴趣,自己实验、思考、总结做了一遍。

一、问题提出

        源表t_source结构如下:

item_id int,

created_time datetime,

modified_time datetime,

item_name varchar(20),

other varchar(20)

1. 源表中有100万条数据,其中有50万created_time和item_name重复。

2. 要把去重后的50万数据写入到目标表。

3. 重复created_time和item_name的多条数据,可以保留任意一条,不做规则限制。

二、实验环境

        Linux虚机:CentOS release 6.4;8G内存;100G机械硬盘;双物理CPU双核,共四个处理器;MySQL 5.6.14。

三、建立测试表和数据

1. 建立源表

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语句。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

Oracle 硬解析与软解析

Oracle 硬解析与软解析是我们经常遇到的问题,什么情况会产生硬解析,什么情况产生软解析,又当如何避免硬解析?下面的描述将给出

663
来自专栏xingoo, 一个梦想做发明家的程序员

Java程序员的日常——存储过程知识普及

存储过程是保存可以接受或返回用户提供参数的SQL语句集合。在日常的使用中,经常会遇到复杂的业务逻辑和对数据库的操作,使用存储过程可以进行封装。可以在数据库中定...

1708
来自专栏MasiMaro 的技术博文

Windows内核中的内存管理

其中PAGED_CODE是一个WDK中提供的一个宏,只在debug版本中生效,用于判断当前的中断请求级别,当级别高于DISPATCH_LEVEL(包含这个级别)...

632
来自专栏GreenLeaves

SQL学习之分组数据Group by

简介:"Group By"根据字面上的意思理解,就是根据"By"后面指定的规则对数据进行分组(分组就是将一个数据集按照"By"指定的规则分成若干个子数据集),然...

1595
来自专栏HappenLee的技术杂谈

C++雾中风景10:聊聊左值,纯右值与将亡值

左值(lvalue)和右值(rvalue)是C++类型系统之中的基础概念,我们不需要了解这些基础概念,同样也能写出代码。但是如果没有弄清左右值的概念,对于许多C...

813
来自专栏Java进阶架构师

「mysql优化专题」90%程序员没听过的存储过程和存储函数教学(7)

储存过程是一个可编程的函数,它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时...

613
来自专栏杨建荣的学习笔记

通过oracle类比MySQL中的字节字符问题(r4笔记第44天)

在几个月前写过一篇博文 MySQL数据类型 http://blog.itpub.net/23718752/viewspace-1371434/ 当时写完以后有...

3157
来自专栏个人分享

SparkSQL的解析详解

  SparkSQL继承自Hive的接口,由于hive是基于MapReduce进行计算的,在计算过程中大量的中间数据要落地于磁盘,从而消耗了大量的I/O,降低了...

672
来自专栏安恒网络空间安全讲武堂

听说thinkphp又出事了?

0x01 前言 听说thinkphp又出事了,之前看过一次tp5的源码,不过只看了查询(select)的过程,这次问题出在update和insert中,但是归根...

2744
来自专栏企鹅号快讯

《数据库系统概念》12-文件的组织

一个数据库被映射到多个不同的文件,这些文件由底层的操作系统来维护。每个文件分成定长的存储单元,称为块(bolck),块是存储分配和数据传输的基本单元。数据库默认...

2519

扫码关注云+社区