前几日遇上多少个索要对表举行去重的难题,数据量大致千万左右,第生龙活虎抉择正是按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
二零一五中的测量试验,几万截然生龙活虎致的测量试验表结商谈数码,可以知道这种情景下,三种写法,在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子查询优化的案例,
风姿浪漫起先认为有一点半信不相信(借使是换…

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

以上相关子查询的SQL在c1上存在索引时间效果与利益率不算低,可是很可惜MySQL未有这种写法,形似的代替写法在MySQL中功用也低的势如水火,如中间表等花招。

无唯有偶在上月整合治理一些shell脚本时管理过mysql导入时出错继续推行的主题材料,因而测量试验后使用了之类办法:

1.将表数据导出:

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

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

2.truncate表,然后创立独一索引

网站地图xml地图