您的位置:永利集团登录网址 > 计算机入门 > MySQL去除重复数据

MySQL去除重复数据

2019-10-05 07:57

今天遇到一个需要对表进行去重的问题,数据量大概千万左右,第一选择就是按Oracle的思路上:

解决MySQL中IN子查询会导致无法使用索引问题,mysql索引

今天看到一篇关于MySQL的IN子查询优化的案例,

一开始感觉有点半信半疑(如果是换做在SQL Server中,这种情况是绝对不可能的,后面会做一个简单的测试。)

随后动手按照他说的做了一个表来测试验证,发现MySQL的IN子查询做的不好,确实会导致无法使用索引的情况(IN子查询无法使用所以,场景是MySQL,截止的版本是5.7.18)

MySQL的测试环境

图片 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行数据

图片 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秒

图片 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秒

图片 4

  In子查询的执行计划,发现外层查询是一个全表扫描的方式,没有用到pay_id上的索引

图片 5

   join自查的执行计划,外层(tpp1别名的查询)是用到pay_id上的索引的。

图片 6

  后面想对第一种查询方式使用强制索引,虽然是不报错的,但是发现根本没用

图片 7

   如果子查询是直接的值,则是可以正常使用索引的。

图片 8

  可见MySQL对IN子查询的支持,做的确实不怎么样。

  另外:加一个使用临时表的情况,虽然比不少join方式查询的,但是也比直接使用IN子查询效率要高,这种情况下,也是可以使用到索引的,不过这种简单的情况,是没有必要使用临时表的。

图片 9

  下面是类似案例在sqlserver 2014中的测试,几万完全一样的测试表结构和数量,可见这种情况下,两种写法,在SQL Server中可以认为是完全一样的(执行计划+效率),这一点SQL Server要比MySQL强不少

图片 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子查询优化的案例,

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.将表数据导出:

图片 11

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.

图片 12

这样导入时会报很多的错误,就是因为唯一约束的存在,你只需要最后检查下表的记录数时候与第一步中查到的数目一致就可以了。

两种子查询的写法

这种去重方式效率比较高,缺陷可能是出错时屏幕上一堆的‘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秒

图片 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秒

图片 14

  In子查询的执行计划,发现外层查询是一个全表扫描的方式,没有用到pay_id上的索引

图片 15

   join自查的执行计划,外层(tpp1别名的查询)是用到pay_id上的索引的。

图片 16

  后面想对第一种查询方式使用强制索引,虽然是不报错的,但是发现根本没用

图片 17

   如果子查询是直接的值,则是可以正常使用索引的。

图片 18

  可见MySQL对IN子查询的支持,做的确实不怎么样。

  另外:加一个使用临时表的情况,虽然比不少join方式查询的,但是也比直接使用IN子查询效率要高,这种情况下,也是可以使用到索引的,不过这种简单的情况,是没有必要使用临时表的。

图片 19

  下面是类似案例在sqlserver 2014中的测试,几万完全一样的测试表结构和数量,可见这种情况下,两种写法,在SQL Server中可以认为是完全一样的(执行计划+效率),这一点SQL Server要比MySQL强不少

图片 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中索引与视图的用法与区别详解
  • MySQL分区字段列有必要再单独建索引吗?
  • 详解mysql中的冗余和重复索引
  • 浅析mysql索引
  • MySQL联合索引功能与用法实例分析
  • MySql索引详细介绍及正确使用方法
  • mysql中索引与FROM_UNIXTIME的问题
  • MySQL常用的建表、添加字段、修改字段、添加索引SQL语句写法总结
  • 图文详解MySQL中两表关联的连接表如何创建索引
  • mysql索引使用技巧及注意事项
  • 浅谈mysql的索引设计原则以及常见索引的区别
  • mysql为字段添加和删除唯一性索引(unique) 的方法
  • mysql增加和删除索引的相关操作
  • MySQL索引操作命令详解
  • MySQL创建全文索引分享
  • MySQL修改表一次添加多个列(字段)和索引的方法
  • 理解MySQL——索引与优化总结
  • 详解mysql权限和索引

本文由永利集团登录网址发布于计算机入门,转载请注明出处:MySQL去除重复数据

关键词:

  • 上一篇:没有了
  • 下一篇:没有了