您的位置:68399皇家赌场 > 虚拟主机 > 化解MySQL中IN子查询会招致力不从心利用索引难点

化解MySQL中IN子查询会招致力不从心利用索引难点

发布时间:2019-05-10 06:01编辑:虚拟主机浏览(158)

    建3个囤积进程插入测试数据,测试数据的特点是pay_id可重新,这里在仓库储存进程处理成,循环插入300W条数据的长河中,每隔十0条数据插入一条重复的pay_id,时间字段在早晚限制内私下

    化解MySQL中IN子查询会招致力不从心运用索引难题,mysql索引

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

    一齐始认为有些满腹狐疑(要是是换做在SQL Server中,这种情景是纯属不可能的,前面会做二个简练的测试。)

    紧接着动手遵照她说的做了一个表来测试申明,开采MySQL的IN子查询做的不佳,确实会促成不恐怕采用索引的情状(IN子查询无法使用所以,场景是MySQL,停止的版本是伍.七.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条数据的进程中,每隔拾0条数据插入一条重复的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大于壹的数目,于是就出现二种写法。

    率先种写法如下:IN子查询中是某段时光内作业总括大于一的事情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
    );
    

    推行结果:二.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自己检查的实施安插,外层(tpp一外号的询问)是用到pay_id上的目录的。

    图片 6

      前边想对第2种查询艺术选用强制索引,即便是不报错的,可是开掘根本无效

    图片 7

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

    图片 8

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

      别的:加1个行使一时表的意况,固然比大多join格局查询的,但是也比直接利用IN子查询功效要高,这种气象下,也是足以接纳到目录的,可是这种简易的情景,是未曾供给运用临时表的。

    图片 9

      上边是相仿案例在sqlserver 201四中的测试,几万一心平等的测试表结构和数量,可知这种气象下,两种写法,在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(@i00=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.七.18版本,对IN子查询,仍要慎用

    明日见到一篇关于MySQL的IN子查询优化的案例, 一方始以为有一些半疑半信(倘诺是换...

    MySQL中IN子查询会促成不能运用索引,mysql索引

     

    后天看到三个腾讯网的壹篇关于MySQL的IN子查询优化的案例,
    一齐始以为微微疑信参半(如若是换做在SQL Server中,这种情况是纯属不恐怕的,前边会做一个简易的测试。)
    随着入手依据她说的做了贰个表来测试注明,开采MySQL的IN子查询做的不好,确实会促成不能运用索引的气象(IN子查询不能接纳所以,场景是MySQL,结束的版本是5.7.1八)

    MySQL的测试情形

    图片 11

    测试表如下

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

    建三个仓库储存进度插入测试数据,测试数据的表征是pay_id可重复,这里在存款和储蓄进度管理成,循环插入300W条数据的历程中,每隔拾0条数据插入一条重复的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行数据

    图片 12

     

     

    三种子查询的写法

    查询大致的情致是查询某些时刻段之内的事体Id大于一的多寡,于是就应际而生三种写法。

     

    率先种写法如下:IN子查询中是某段日子内专门的职业总括行数大于一的事情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
    );
    

    执行结果:二.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子查询的施行陈设,发掘外层查询是1个全表扫描的法子,未有运用pay_id上的目录

      图片 15

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

       图片 16

       

     

      后面想对第3种查询格局选拔强制索引,固然是不报错的,不过开掘一向不行

       图片 17

       要是实查询是平素的值,则是足以健康使用索引的。

      图片 18

      

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

     

      其它:加叁个应用有时表的景况,纵然比大多join格局查询的,可是也比一向动用IN子查询功用要高,这种气象下,也是足以采用到目录的,但是这种简易的事态,是没有须求运用不时表的。

      图片 19

     

     

      

     

      上边是近乎案例在sqlserver 20第11肆中学的测试,几万一心平等的测试表结交涉数据,可知这种景观下,两种写法,在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(@i00=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数据中,截至伍.柒.18本子,对IN子查询,仍要慎用

    前些天收看三个乐乎的一篇有关MySQL的IN子查询优化的案例, 一开首以为有一点点疑信参半(如若...

       join自查的实行布置,外层(tpp1别称的查询)是用到pay_id上的目录的。

    总括:在MySQL数据中,停止伍.柒.1八本子,对IN子查询,仍要慎用

    测试表如下

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

    图片 21

     

    图片 22

     

      推行结果:0.48秒

      上面是看似案例在sqlserver 20第11四中学的测试,大约完全同样的测试表结商谈数目,可见这种气象下,三种写法,在SQL Server中得以以为是一点1滴等同的(推行布署 效用),那点SQL Server要比MySQL强不少

    图片 23

    试行结果:贰.23秒

    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(@i00=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对IN子查询的协理,做的实在不怎样。

      其余:加三个应用不常表的图景,即便比大多join情势查询的,但是也比直接使用IN子查询功用要高,这种场合下,也是足以行使到目录的,但是这种总结的景况,是绝非供给选拔临时表的。

       图片 24

    图片 25

     

    施行结果:二.23秒

    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 
    
    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
    

    后天收看二个博客园的1篇关于MySQL的IN子查询优化的案例,
    一齐始感觉微微半疑半信(假诺是换做在SQL Server中,这种情况是纯属不容许的,前面会做八个简便的测试。)
    随着动手依照她说的做了2个表来测试申明,开采MySQL的IN子查询做的倒霉,确实会导致力不从心利用索引的意况(IN子查询不能运用所以,场景是MySQL,截至的版本是伍.七.1八)

    查询大致的乐趣是查询有个别时刻段之内的政工Id大于一的数据,于是就涌出二种写法。

     

       要是实查询是平昔的值,则是足以不奇怪使用索引的。

    本文由68399皇家赌场发布于虚拟主机,转载请注明出处:化解MySQL中IN子查询会招致力不从心利用索引难点

    关键词: 68399皇家赌场