mysql中groupby会⽤到索引吗_解决MySQL中IN⼦查询会导致
⽆法使⽤索引问题
今天看到⼀篇关于MySQL的IN⼦查询优化的案例，
⼀开始感觉有点半信半疑(如果是换做在SQL Server中，这种情况是绝对不可能的，后⾯会做⼀个简单的测试。)
随后动⼿按照他说的做了⼀个表来测试验证，发现MySQL的IN⼦查询做的不好，确实会导致⽆法使⽤索引的情况(IN⼦查询⽆法使⽤所以，场景是MySQL，截⽌的版本是5.7.18)
MySQL的测试环境
测试表如下
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⾏数据
两种⼦查询的写法
查询⼤概的意思是查询某个时间段之内的业务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秒
第⼆种写法，与⼦查询进⾏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秒
In⼦查询的执⾏计划，发现外层查询是⼀个全表扫描的⽅式，没有⽤到pay_id上的索引
join⾃查的执⾏计划，外层(tpp1别名的查询)是⽤到pay_id上的索引的。
后⾯想对第⼀种查询⽅式使⽤强制索引，虽然是不报错的，但是发现根本没⽤
如果⼦查询是直接的值，则是可以正常使⽤索引的。
可见MySQL对IN⼦查询的⽀持，做的确实不怎么样。join和in哪个查询更快
另外：加⼀个使⽤临时表的情况，虽然⽐不少join⽅式查询的，但是也⽐直接使⽤IN⼦查询效率要⾼，这种情况下，也是可以使⽤到索引的，不过这种简单的情况，是没有必要使⽤临时表的。
下⾯是类似案例在sqlserver 2014中的测试，⼏万完全⼀样的测试表结构和数量，可见这种情况下，两种写法，在SQL Server中可以认为是完全⼀样的(执⾏计划+效率)，这⼀点SQL Server要⽐MySQL强不少
下⾯是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⼦查询，仍要慎⽤
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论