• 售前

  • 售后

热门帖子
入门百科

解决MySQL中IN子查询会导致无法利用索引题目

[复制链接]
123456881 显示全部楼层 发表于 2021-10-25 20:01:14 |阅读模式 打印 上一主题 下一主题
本日看到一篇关于MySQL的IN子查询优化的案例,
一开始感觉有点将信将疑(假如是换做在SQL Server中,这种环境是绝对不大概的,背面会做一个简朴的测试。)
随后动手按照他说的做了一个表来测试验证,发现MySQL的IN子查询做的不好,确实会导致无法使用索引的环境(IN子查询无法使用以是,场景是MySQL,停止的版本是5.7.18)
MySQL的测试环境

测试表如下
  1. create table test_table2
  2. (
  3.   id int auto_increment primary key,
  4.   pay_id int,
  5.   pay_time datetime,
  6.   other_col varchar(100)
  7. )
复制代码
建一个存储过程插入测试数据,测试数据的特点是pay_id可重复,这里在存储过程处置惩罚成,循环插入300W条数据的过程中,每隔100条数据插入一条重复的pay_id,时间字段在一定范围内随机
  1. CREATE DEFINER=`root`@`%` PROCEDURE `test_insert`(IN `loopcount` INT)
  2.   LANGUAGE SQL
  3.   NOT DETERMINISTIC
  4.   CONTAINS SQL
  5.   SQL SECURITY DEFINER
  6.   COMMENT ''
  7. BEGIN
  8.   declare cnt int;
  9.   set cnt = 0;
  10.   while cnt< loopcount do
  11.     insert into test_table2 (pay_id,pay_time,other_col) values (cnt,date_add(now(), interval floor(300*rand()) day),uuid());
  12.     if (cnt mod 100 = 0) then
  13.       insert into test_table2 (pay_id,pay_time,other_col) values (cnt,date_add(now(), interval floor(300*rand()) day),uuid());
  14.     end if;
  15.     set cnt = cnt + 1;  
  16.   end while;
  17. END
复制代码
  执行 call test_insert(3000000); 插入303000行数据

两种子查询的写法
查询大概的意思是查询某个时间段之内的业务Id大于1的数据,于是就出现两种写法。
第一种写法如下:IN子查询中是某段时间内业务统计大于1的业务Id,外层按照IN子查询的结果进行查询,业务Id的列pay_id上有索引,逻辑也比力简朴,这种写法,在数据量大的时间确实服从比力低,用不到索引
  1. select * from test_table2 force index(idx_pay_id)
  2. where pay_id in (
  3.   select pay_id from test_table2
  4.   where pay_time>="2016-06-01 00:00:00"
  5.     AND pay_time<="2017-07-03 12:59:59"
  6.   group by pay_id
  7.   having count(pay_id) > 1
  8. );
复制代码
执行结果:2.23秒

第二种写法,与子查询进行join关联,这种写法相当于上面的IN子查询写法,下面测试发现,服从确实有不少的提高
  1. select tpp1.* from test_table2 tpp1,
  2. (
  3.    select pay_id
  4.    from test_table2
  5.    WHERE pay_time>="2016-07-01 00:00:00"
  6.    AND pay_time<="2017-07-03 12:59:59"
  7.    group by pay_id
  8.    having count(pay_id) > 1
  9. ) tpp2
  10. where tpp1.pay_id=tpp2.pay_id
复制代码
  执行结果:0.48秒

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

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

  背面想对第一种查询方式使用逼迫索引,固然是不报错的,但是发现根本没用

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

  可见MySQL对IN子查询的支持,做的确实不怎么样。
  别的:加一个使用暂时表的环境,固然比不少join方式查询的,但是也比直接使用IN子查询服从要高,这种环境下,也是可以使用到索引的,不外这种简朴的环境,是没有须要使用暂时表的。

  下面是雷同案例在sqlserver 2014中的测试,几万完全一样的测试表结构和数量,可见这种环境下,两种写法,在SQL Server中可以以为是完全一样的(执行筹划+服从),这一点SQL Server要比MySQL强不少

  下面是sqlserver中的测试环境脚本。
  1. create table test_table2
  2. (
  3.   id int identity(1,1) primary key,
  4.   pay_id int,
  5.   pay_time datetime,
  6.   other_col varchar(100)
  7. )
  8. begin tran
  9. declare @i int = 0
  10. while @i<300000
  11. begin
  12.   insert into test_table2 values (@i,getdate()-rand()*300,newid());
  13.   if(@i%1000=0)
  14.   begin
  15.     insert into test_table2 values (@i,getdate()-rand()*300,newid());
  16.   end
  17.   set @i = @i + 1
  18. end
  19. COMMIT
  20. GO
  21. create index idx_pay_id on test_table2(pay_id);
  22. create index idx_time on test_table2(pay_time);
  23. GO
  24. select * from test_table2
  25. where pay_id in (
  26.           select pay_id from test_table2
  27.           where pay_time>='2017-01-21 00:00:00'
  28.           AND pay_time<='2017-07-03 12:59:59'
  29.           group by pay_id
  30.           having count(pay_id) > 1
  31.         );
  32. select tpp1.* from test_table2 tpp1,
  33. (
  34.    select pay_id
  35.    from test_table2
  36.    WHERE pay_time>='2017-01-21 00:00:00'
  37.    AND pay_time<='2017-07-30 12:59:59'
  38.    group by pay_id having
  39.    count(pay_id) > 1
  40. ) tpp2
  41. where tpp1.pay_id=tpp2.pay_id
复制代码
总结:在MySQL数据中,停止5.7.18版本,对IN子查询,仍要慎用

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作