元旦假期收到阿里吴老师来电,被告知已将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的多条数据,可以保留任意一条,不做规则限制。
二、建立测试表和数据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;
end
//
delimiter ;
call sp_generate_data();
源表没有主键或唯一性约束,有可能存在两条完全一样的数据,所以再插入一条记录模拟这种情况。
insert into t_source
select * from t_source where item_id=1;
commit;
查询总记录数和去重后的记录数图一所示
图一
可以看到,源表中有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;
这种方法用时38秒,查询计划如图三所示。
图三
(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;
这种方法用时20秒,查询计划如图四所示。
图四
(1)最内层的查询扫描t_source表的100万行,并使用文件排序,生成导出表derived3。
(2)第二层查询要扫描derived3的100万行,生成导出表derived2,完成变量的比较和赋值,并自动创建一个导出列f上的索引auto_key0。
(3)最外层使用auto_key0索引扫描derived2得到去重的结果行。
与方法2比较,变量方法消除了表关联,提高了查询速度。
至此,我们还没有在源表上创建任何索引。无论使用哪种写法,要查重都需要对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;
这种方法用时25秒,查询计划如图五所示。
图五
(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;
这种方法用时35秒,查询计划如图六所示。
图六
和没有索引相比,子查询虽然从全表扫描变为了全索引扫描,但还是需要扫描100万行记录。因此查询性能并没有明显提升。
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;
这种方法用时20秒,查询计划如图七所示,与没有索引时的相同。
图七
可见索引对这种写法没有作用。能不能消除嵌套,只用一层查询出结果呢?
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;
这种方法用时13秒,查询计划如图八所示。
图八
该语句具有以下特点。
(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语句。
分享到:
相关推荐
从Oracle的SQL优化到MySQL的SQL优化
1. SQL优化 1 1.1. 优化实战 1 1.1.1. 策略1.尽量全值匹配 1 1.1.2. 策略2.最佳左前缀法则 2 1.1.3. 策略3.不在索引列上做任何操作 2 1.1.4. 策略4.范围条件放最后 3 1.1.5. 策略5.覆盖索引尽量用 3 1.1.6. 策略6.不...
2023最新mysql的sql语句优化方法技巧面试题总结.docx2023最新mysql的sql语句优化方法技巧面试题总结.docx2023最新mysql的sql语句优化方法技巧面试题总结.docx2023最新mysql的sql语句优化方法技巧面试题总结.docx2023...
MySQL性能优化 SQL优化方法技巧
MySQL SQL优化的小册子。 对优化这块重点阐述了相关原理与技术手段。
本书希望能够通过一步步详细介绍SQL优化的方法,帮助读者分 析和调优有问题的SQL语句。 主要内容 ● 找出收集和诊断问题必备的分析命令 ● 创建MySQL索引来改进查询性能 ● 掌握MySQL的查询执行计划 ● 找...
基于Mysql数据库的SQL优化 亲测
数据治理__从Oracle的SQL优化到MySQL的SQL优化.pdf
mysql-sql优化-小米开源-soar 。。。。。
《Effective MySQL之SQL语句最优化》希望能够通过一步步详细介绍SQL优化的方法,帮助读者分析和调优有问题的SQL语句。 内容简介 《Effective MySQL之SQL语句最优化》主要内容: ●找出收集和诊断问题必备的分析...
2016年 的视频,适合有一定数据库经验的开发者,高级开发者没必要看了
MySQL数据库优化SQL篇.ppt,适用于企业级项目开发
MySQL教程SQL优化
sql查询优化 提高MySQL数据库查询效率的几个技巧
mysql优化从以下几个方面介绍 mysql的架构 索引优化分析 查询截取分析 mysql锁机制 主从复制
将mysql数据库转换为sql server的数据库,或者将sql server数据库转换为mysql的数据库,在nt环境下很多时候都会用到。使用mysql odbc后就比较好办,可以使用sql server的管理工具,也可以使用mysql的管理工具,更可以...
MySQL架构执行与SQL性能优化-MySQL高并发详解课程,课程的目标简单明确,核心就是MySQL的性能优化与高并发。课程内容进行了精华的浓缩,有四大内容主旨,MySQL架构与执行流程,MySQL索引原理详解,MySQL事务原理与...
2. SQL优化 2.1优化SQL的一般步骤 2.2 索引问题. 2.3两个常用的优化技巧 2.4常用SQL优化 2.5常用SQL技巧 3.优化数据库对象 3.1优化表的数据类型逆规范化 3.2提高查询速度 4.锁问题 4.1MyISQM表锁 4.2...
mysql的优化 数据库(表)设计合理 我们的表设计要符合3NF 3范式(规范的模式) , 有时我们需要适当的逆范式 sql语句的优化(索引,常用小技巧.) 数据的配置(缓存设大) 适当硬件配置和操作系统 (读写分离.)
《Effective MySQL之SQL语句最优化》数据库SQL