【997755.com澳门葡京】MySQL去除重复数据,MySQL中IN子查询会促成力不从心选取索引

明天碰到一个亟需对表进行去重的问题,数据量大致千万左右,第一拔取就是按Oracle的思绪上:

不留余地MySQL中IN子查询会促成不能运用索引问题,mysql索引

明天看看一篇有关MySQL的IN子查询优化的案例,

一开头感到微微半信半疑(如若是换做在SQL
Server中,那种气象是纯属无法的,后面会做一个粗略的测试。)

随即出手依据她说的做了一个表来测试讲明,发现MySQL的IN子查询做的不佳,确实会促成无法运用索引的图景(IN子查询不能选拔所以,场景是MySQL,停止的版本是5.7.18)

MySQL的测试环境

997755.com澳门葡京 1

测试表如下

create table test_table2
(
  id int auto_increment primary key,
  pay_id int,
  pay_time datetime,
  other_col varchar(100)
)

建一个仓储进程插入测试数据,测试数据的特征是pay_id可重复,那里在存储进程处理成,循环插入300W条数据的进度中,每隔100条数据插入一条重复的pay_id,时间字段在听天由命限制内任意

CREATE DEFINER=`root`@`%` PROCEDURE `test_insert`(IN `loopcount` INT)
  LANGUAGE SQL
  NOT DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY DEFINER
  COMMENT ''
BEGIN
  declare cnt int;
  set cnt = 0;
  while cnt< loopcount do
    insert into test_table2 (pay_id,pay_time,other_col) values (cnt,date_add(now(), interval floor(300*rand()) day),uuid());
    if (cnt mod 100 = 0) then
      insert into test_table2 (pay_id,pay_time,other_col) values (cnt,date_add(now(), interval floor(300*rand()) day),uuid());
    end if;
    set cnt = cnt + 1;  
  end while;
END

  执行 call test_insert(3000000); 插入303000行数据

997755.com澳门葡京 2

二种子查询的写法

查询大约的意味是询问某个时刻段之内的工作Id大于1的数目,于是就出现二种写法。

第一种写法如下:IN子查询中是某段时光内作业计算大于1的政工Id,外层依据IN子查询的结果举办询问,业务Id的列pay_id上有索引,逻辑也相比较简单,那种写法,在数据量大的时候确实效用相比低,用不到目录

select * from test_table2 force index(idx_pay_id)
where pay_id in (
  select pay_id from test_table2 
  where pay_time>="2016-06-01 00:00:00" 
    AND pay_time<="2017-07-03 12:59:59" 
  group by pay_id 
  having count(pay_id) > 1
);

推行结果:2.23秒

997755.com澳门葡京 3

其次种写法,与子查询进行join关联,那种写法相当于地方的IN子查询写法,下边测试发现,功用的确有不可胜道的滋长

select tpp1.* from test_table2 tpp1, 
(
   select pay_id 
   from test_table2 
   WHERE pay_time>="2016-07-01 00:00:00" 
   AND pay_time<="2017-07-03 12:59:59" 
   group by pay_id 
   having count(pay_id) > 1
) tpp2 
where tpp1.pay_id=tpp2.pay_id

  执行结果:0.48秒

997755.com澳门葡京 4

  In子查询的施行安插,发现外层查询是一个全表扫描的艺术,没有使用pay_id上的目录

997755.com澳门葡京 5

   join自查的执行陈设,外层(tpp1别名的查询)是用到pay_id上的目录的。

997755.com澳门葡京 6

  后边想对第一种查询格局选取强制索引,尽管是不报错的,不过发现平昔没用

997755.com澳门葡京 7

   倘诺实查询是间接的值,则是可以健康使用索引的。

997755.com澳门葡京 8

  可知MySQL对IN子查询的支撑,做的确实不如何。

  此外:加一个采纳临时表的情状,即使比许多join形式查询的,可是也比直接选择IN子查询功能要高,那种意况下,也是足以应用到目录的,不过这种简易的事态,是不曾要求选择临时表的。

997755.com澳门葡京 9

  上面是接近案例在sqlserver
2014中的测试,几万一心相同的测试表结构和数量,可知那种情况下,二种写法,在SQL
Server中可以认为是截然一致的(执行安排+成效),这点SQL
Server要比MySQL强不少

997755.com澳门葡京 10

   上面是sqlserver中的测试环境脚本。

create table test_table2
(
  id int identity(1,1) primary key,
  pay_id int,
  pay_time datetime,
  other_col varchar(100)
)
begin tran
declare @i int = 0
while @i<300000
begin
  insert into test_table2 values (@i,getdate()-rand()*300,newid());
  if(@i%1000=0)
  begin
    insert into test_table2 values (@i,getdate()-rand()*300,newid());
  end
  set @i = @i + 1
end
COMMIT
GO
create index idx_pay_id on test_table2(pay_id);
create index idx_time on test_table2(pay_time);
GO
select * from test_table2 
where pay_id in (
          select pay_id from test_table2 
          where pay_time>='2017-01-21 00:00:00' 
          AND pay_time<='2017-07-03 12:59:59' 
          group by pay_id 
          having count(pay_id) > 1
        );
select tpp1.* from test_table2 tpp1, 
(
   select pay_id 
   from test_table2 
   WHERE pay_time>='2017-01-21 00:00:00'
   AND pay_time<='2017-07-30 12:59:59' 
   group by pay_id having 
   count(pay_id) > 1
) tpp2 
where tpp1.pay_id=tpp2.pay_id

小结:在MySQL数据中,停止5.7.18版本,对IN子查询,仍要慎用

明日见到一篇关于MySQL的IN子查询优化的案例,
一开首感觉有点半信半疑(借使是换…

先天见到一篇有关MySQL的IN子查询优化的案例,

MySQL中IN子查询会导致不可能使用索引,mysql索引

 

【997755.com澳门葡京】MySQL去除重复数据,MySQL中IN子查询会促成力不从心选取索引。昨天见到一个今日头条的一篇关于MySQL的IN子查询优化的案例,
一初步感到微微半信半疑(假若是换做在SQL
Server中,那种情况是纯属不可以的,前面会做一个简练的测试。)
接着出手按照他说的做了一个表来测试阐明,发现MySQL的IN子查询做的不佳,确实会招致不可能运用索引的动静(IN子查询不能采用所以,场景是MySQL,停止的版本是5.7.18)

MySQL的测试环境

997755.com澳门葡京 11

测试表如下

create table test_table2
(
    id int auto_increment primary key,
    pay_id int,
    pay_time datetime,
    other_col varchar(100)
)

建一个仓储进程插入测试数据,测试数据的表征是pay_id可重复,那里在存储进程处理成,循环插入300W条数据的进程中,每隔100条数据插入一条重复的pay_id,时间字段在一定限制内随机

CREATE DEFINER=`root`@`%` PROCEDURE `test_insert`(IN `loopcount` INT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

    declare cnt int;
    set  cnt = 0;
    while cnt< loopcount do
        insert into test_table2 (pay_id,pay_time,other_col) values  (cnt,date_add(now(), interval floor(300*rand()) day),uuid());
        if (cnt mod 100 = 0) then
            insert into test_table2 (pay_id,pay_time,other_col) values  (cnt,date_add(now(), interval floor(300*rand()) day),uuid());
        end if;
        set cnt = cnt + 1;    
    end while;
END

  执行 call test_insert(3000000); 插入303000行数据

997755.com澳门葡京 12

 

 

三种子查询的写法

查询大致的意趣是询问某个时刻段之内的政工Id大于1的数据,于是就应运而生三种写法。

 

第一种写法如下:IN子查询中是某段时光内作业总结行数大于1的事体Id,外层根据IN子查询的结果开展询问,业务Id的列pay_id上有索引,逻辑也相比简单,
那种写法,在数据量大的时候真的功能相比较低,用不到目录

select * from test_table2 force index(idx_pay_id)
where pay_id in (
  select pay_id from test_table2 
  where pay_time>="2016-06-01 00:00:00" 
    AND pay_time<="2017-07-03 12:59:59" 
  group by pay_id 
  having count(pay_id) > 1
);

推行结果:2.23秒

997755.com澳门葡京 13

 

其次种写法,与子查询进行join关联,那种写法相当于地点的IN子查询写法,上边测试发现,效用真的有众多的加强

select tpp1.* from test_table2 tpp1, 
(
     select pay_id 
     from test_table2 
      WHERE pay_time>="2016-07-01 00:00:00" 
     AND pay_time<="2017-07-03 12:59:59" 
     group by pay_id 
     having count(pay_id) > 1
) tpp2 
where tpp1.pay_id=tpp2.pay_id 

  执行结果:0.48秒

  997755.com澳门葡京 14

 

  In子查询的实践陈设,发现外层查询是一个全表扫描的主意,没有选取pay_id上的目录

  997755.com澳门葡京 15

   join自查的施行安顿,外层(tpp1别名的查询)是用到pay_id上的目录的。

   997755.com澳门葡京 16

   

 

  前面想对第一种查询方式选用强制索引,尽管是不报错的,可是发现平昔不行

   997755.com澳门葡京 17

   若是实查询是一贯的值,则是足以健康使用索引的。

  997755.com澳门葡京 18

  

  可知MySQL对IN子查询的支撑,做的真的不怎么着。

 

  其它:加一个接纳临时表的事态,固然比许多join方式查询的,不过也比一直动用IN子查询效能要高,那种境况下,也是足以采纳到目录的,可是那种简易的情景,是从未有过要求运用临时表的。

  997755.com澳门葡京 19

 

 

  

 

  上面是近似案例在sqlserver
2014中的测试,几万完全相同的测试表结构和数量,可知那种情景下,三种写法,在SQL
Server中可以认为是截然一致的(执行陈设+功用),这点SQL
Server要比MySQL强不少

997755.com澳门葡京 20

   下边是sqlserver中的测试环境脚本。

create table test_table2
(
    id int identity(1,1) primary key,
    pay_id int,
    pay_time datetime,
    other_col varchar(100)
)

begin  tran
declare @i int = 0
while @i<300000
begin
    insert into test_table2 values (@i,getdate()-rand()*300,newid());
    if(@i%1000=0)
    begin
        insert into test_table2 values (@i,getdate()-rand()*300,newid());
    end
    set @i = @i + 1
end
COMMIT
GO


create index idx_pay_id on test_table2(pay_id);
create index idx_time on test_table2(pay_time);
GO




select * from test_table2 
where pay_id in (
                    select pay_id from test_table2 
                    where pay_time>='2017-01-21 00:00:00' 
                    AND pay_time<='2017-07-03 12:59:59' 
                    group by pay_id 
                    having count(pay_id) > 1
                );

select tpp1.* from test_table2 tpp1, 
(
     select pay_id 
     from test_table2 
      WHERE pay_time>='2017-01-21 00:00:00'
     AND pay_time<='2017-07-30 12:59:59' 
     group by pay_id having 
     count(pay_id) > 1
) tpp2 
where tpp1.pay_id=tpp2.pay_id 

 

计算:在MySQL数据中,停止5.7.18本子,对IN子查询,仍要慎用

明天观看一个乐乎的一篇有关MySQL的IN子查询优化的案例,
一初始感到微微半信半疑(倘诺…

delete from table t1 where id < (select max(id) from table t2 where t1.c1=t2.c1);  --将c1值相同的记录进行去重,只留下id最大的,写成id>min(id)效果相同。

一初阶感觉有点半信半疑(若是是换做在SQL
Server中,那种景色是纯属不容许的,前边会做一个大概的测试。)

上述相关子查询的SQL在c1上设有索引时效能不算低,但是很遗憾MySQL没有那种写法,类似的替代写法在MySQL中作用也低的令人切齿,如中间表等手段。

随之出手依据她说的做了一个表来测试表明,发现MySQL的IN子查询做的不得了,确实会导致不可以使用索引的气象(IN子查询不能利用所以,场景是MySQL,停止的版本是5.7.18)

司空见惯在前些日子整治一些shell脚本时处理过mysql导入时出错继续执行的题材,由此测试后采用了之类办法:

MySQL的测试环境

1.将表数据导出:

997755.com澳门葡京 21

mysqldump -uroot -p --skip-extended-insert -t DBNAME TABLE>TABLE.sql

然后记一下去重后的记录数:
select count(*) from (select 1 from TABLE group by c1) a;

测试表如下

2.truncate表,然后成立唯一索引

create table test_table2
(
  id int auto_increment primary key,
  pay_id int,
  pay_time datetime,
  other_col varchar(100)
)
truncate table TABLE;
create unique index IX_c1 on TABLE(c1);

建一个仓储进度插入测试数据,测试数据的特点是pay_id可重复,那里在蕴藏进度处理成,循环插入300W条数据的进度中,每隔100条数据插入一条重复的pay_id,时间字段在自然限制内任意

3.说到底导入数据,需求加上-f选项。

CREATE DEFINER=`root`@`%` PROCEDURE `test_insert`(IN `loopcount` INT)
  LANGUAGE SQL
  NOT DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY DEFINER
  COMMENT ''
BEGIN
  declare cnt int;
  set cnt = 0;
  while cnt< loopcount do
    insert into test_table2 (pay_id,pay_time,other_col) values (cnt,date_add(now(), interval floor(300*rand()) day),uuid());
    if (cnt mod 100 = 0) then
      insert into test_table2 (pay_id,pay_time,other_col) values (cnt,date_add(now(), interval floor(300*rand()) day),uuid());
    end if;
    set cnt = cnt + 1;  
  end while;
END
mysql -uroot -p -f DBNAME<TABLE.sql

  执行 call test_insert(3000000); 插入303000行数据

-f的效果是:Continue
even if an SQL error occurs.

997755.com澳门葡京 22

诸如此类导入时会报很多的荒谬,就是因为唯一约束的存在,你只必要最终检查下表的记录数时候与第一步中查到的数量一致就可以了。

两种子查询的写法

那种去重情势作用比较高,缺陷或者是失误时显示器上一堆的‘Duplicate
entry’报错会淹没其余的报错。

询问差不离的情趣是询问某个时刻段之内的政工Id大于1的数量,于是就出现三种写法。

别的还足以写存储进度来删除重复数据,那种方法对数据库的震慑较小,无需导出导入数据,存储进度写法详见:

先是种写法如下:IN子查询中是某段时间内工作统计大于1的事体Id,外层按照IN子查询的结果进行查询,业务Id的列pay_id上有索引,逻辑也相比较简单,那种写法,在数据量大的时候的确功用比较低,用不到目录

select * from test_table2 force index(idx_pay_id)
where pay_id in (
  select pay_id from test_table2 
  where pay_time>="2016-06-01 00:00:00" 
    AND pay_time<="2017-07-03 12:59:59" 
  group by pay_id 
  having count(pay_id) > 1
);

实践结果:2.23秒

997755.com澳门葡京 23

其次种写法,与子查询进行join关联,那种写法相当于地点的IN子查询写法,下边测试发现,成效真的有恒河沙数的滋长

select tpp1.* from test_table2 tpp1, 
(
   select pay_id 
   from test_table2 
   WHERE pay_time>="2016-07-01 00:00:00" 
   AND pay_time<="2017-07-03 12:59:59" 
   group by pay_id 
   having count(pay_id) > 1
) tpp2 
where tpp1.pay_id=tpp2.pay_id

  执行结果:0.48秒

997755.com澳门葡京 24

997755.com澳门葡京,  In子查询的执行安排,发现外层查询是一个全表扫描的不二法门,没有动用pay_id上的目录

997755.com澳门葡京 25

   join自查的履行安排,外层(tpp1别名的询问)是用到pay_id上的目录的。

997755.com澳门葡京 26

  后边想对第一种查询艺术使用强制索引,即使是不报错的,不过发现向来不算

997755.com澳门葡京 27

   如若实查询是直接的值,则是足以健康使用索引的。

997755.com澳门葡京 28

  可知MySQL对IN子查询的辅助,做的着实不怎样。

  其余:加一个利用临时表的情形,即便比许多join格局查询的,不过也比一向利用IN子查询效用要高,那种景况下,也是可以应用到目录的,但是这种简单的景色,是不曾必要运用临时表的。

997755.com澳门葡京 29

  下边是相仿案例在sqlserver
2014中的测试,几万通通等同的测试表结构和多少,可知那种处境下,三种写法,在SQL
Server中得以认为是完全相同的(执行部署+功用),那一点SQL
Server要比MySQL强不少

997755.com澳门葡京 30

   下边是sqlserver中的测试环境脚本。

create table test_table2
(
  id int identity(1,1) primary key,
  pay_id int,
  pay_time datetime,
  other_col varchar(100)
)
begin tran
declare @i int = 0
while @i<300000
begin
  insert into test_table2 values (@i,getdate()-rand()*300,newid());
  if(@i%1000=0)
  begin
    insert into test_table2 values (@i,getdate()-rand()*300,newid());
  end
  set @i = @i + 1
end
COMMIT
GO
create index idx_pay_id on test_table2(pay_id);
create index idx_time on test_table2(pay_time);
GO
select * from test_table2 
where pay_id in (
          select pay_id from test_table2 
          where pay_time>='2017-01-21 00:00:00' 
          AND pay_time<='2017-07-03 12:59:59' 
          group by pay_id 
          having count(pay_id) > 1
        );
select tpp1.* from test_table2 tpp1, 
(
   select pay_id 
   from test_table2 
   WHERE pay_time>='2017-01-21 00:00:00'
   AND pay_time<='2017-07-30 12:59:59' 
   group by pay_id having 
   count(pay_id) > 1
) tpp2 
where tpp1.pay_id=tpp2.pay_id

统计:在MySQL数据中,截至5.7.18版本,对IN子查询,仍要慎用

你或许感兴趣的篇章:

  • MySQL中索引与视图的用法与分化详解
  • MySQL分区字段列有要求再独自建索引吗?
  • 详解mysql中的冗余和另行索引
  • 浅析mysql索引
  • MySQL联合索引功用与用法实例分析
  • MySql索引详细介绍及科学运用办法
  • mysql中索引与FROM_UNIXTIME的问题
  • MySQL常用的建表、添加字段、修改字段、添加索引SQL语句写法总括
  • 图文详解MySQL中两表关联的连接表怎样创造索引
  • mysql索引使用技术及注意事项
  • 浅谈mysql的目录设计原则以及常见索引的界别
  • mysql为字段添加和删除唯一性索引(unique)
    的方法
  • mysql增加和删除索引的连锁操作
  • MySQL索引操作命令详解
  • MySQL成立全文索引分享
  • MySQL修改表几遍添加七个列(字段)和目录的艺术
  • 略知一二MySQL——索引与优化总计
  • 详解mysql权限和目录

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website