[求证&散分]MySQL 中 where id in (1,2,3,4,...) 的效率问题讨论

Question

庆祝本月大版得分过万，兼把在这段论坛中经常被问到的一个问题拿出来大家讨论一下。命题假设：测试表如下
create table t_06 (
id int not null primary key,
c1 varchar(30),
i2 int
) engine = myisam; delimiter //CREATE PROCEDURE prepareData_t_06 ()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i < 500000 DO
insert into t_06 values (i,concat('AA',i),i);
SET i = i + 1;
END WHILE;
END;
//delimiter ;CALL prepareData_t_06();select count(*) from t_06;
show index from t_06;
show table status like 't_06';现在如果由程序在数组中提供 100 个 ID 如下
38068,238833,308799,274344,299910,309823,337486,290812,56393,64413,492511,260426,58626,450987,499220,187731,365665,212799,227788,255724,384636,465766,417310,313148,483987,328761,402876,237274,249183,174185,28187,189155,259940,67800,60065,340172,311667,354861,182,305523,115981,365082,213915,47894,131301,198754,358852,112496,404423,486725,233123,322936,325337,125932,299260,128791,295663,469897,120580,347766,34859,364204,37597,268974,351155,256955,214013,309192,412394,216800,30315,411242,16678,233247,359013,401666,30792,452394,408649,14159,5519,91705,227648,120966,319599,351170,68129,368701,233566,144256,156172,41972,499687,390955,6549,298079,498230,196397,239493,242037如何实现查询效率比较高，请给出你的意思，测试方案和对比结果者加分。select * from t_06 where id in (38068,238833,308799,274344,299910,309823,337486,290812,56393,64413,492511,260426,58626,450987,499220,187731,365665,212799,227788,255724,384636,465766,417310,313148,483987,328761,402876,237274,249183,174185,28187,189155,259940,67800,60065,340172,311667,354861,182,305523,115981,365082,213915,47894,131301,198754,358852,112496,404423,486725,233123,322936,325337,125932,299260,128791,295663,469897,120580,347766,34859,364204,37597,268974,351155,256955,214013,309192,412394,216800,30315,411242,16678,233247,359013,401666,30792,452394,408649,14159,5519,91705,227648,120966,319599,351170,68129,368701,233566,144256,156172,41972,499687,390955,6549,298079,498230,196397,239493,242037);

Answer 1

我认为分两步.
先将提供的ID转成行集.
ID
-----
38068
238833
308799
..然后与表做JOIN操作.

Answer 2

我3楼就是这个意思。实际还要对比 Join 和 In 在本例中那个效率高

Answer 3

1、
直接用INselect * from  t_06 a inner join lsbtest b on a.id=b.id1;2、用LSBTESTDROP TABLE IF EXISTS `lsbtest`;
CREATE TABLE `lsbtest` (
  `id1` bigint(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `lsbtest` */LOCK TABLES `lsbtest` WRITE;insert  into `lsbtest`(`id1`) values (1),(2),(38068),(238833),(308799),(274344),(299910),(309823),(337486),(290812),(56393),(64413),(492511),(260426),(58626),(450987),(499220),(187731),(365665),(212799),(227788),(255724),(384636),(465766),(417310),(313148),(483987),(328761),(402876),(237274),(249183),(174185),(28187),(189155),(259940),(67800),(60065),(340172),(311667),(354861),(182),(305523),(115981),(365082),(213915),(47894),(131301),(198754),(358852),(112496),(404423),(486725),(233123),(322936),(325337),(125932),(299260),(128791),(295663),(469897),(120580),(347766),(34859),(364204),(37597),(268974),(351155),(256955),(214013),(309192),(412394),(216800),(30315),(411242),(16678),(233247),(359013),(401666),(30792),(452394),(408649),(14159),(5519),(91705),(227648),(120966),(319599),(351170),(68129),(368701),(233566),(144256),(156172),(41972),(499687),(390955),(6549),(298079),(498230),(196397),(239493),(242037);UNLOCK TABLES;select * from  t_06 a inner join lsbtest b on a.id=b.id1;

Answer 4

1、
select * from t_06 where id in (38068,238833,308799,274344,299910,309823,337486,290812,56393,64413,492511,260426,58626,450987,499220,187731,365665,212799,227788,255724,384636,465766,417310,313148,483987,328761,402876,237274,249183,174185,28187,189155,259940,67800,60065,340172,311667,354861,182,305523,115981,365082,213915,47894,131301,198754,358852,112496,404423,486725,233123,322936,325337,125932,299260,128791,295663,469897,120580,347766,34859,364204,37597,268974,351155,256955,214013,309192,412394,216800,30315,411242,16678,233247,359013,401666,30792,452394,408649,14159,5519,91705,227648,120966,319599,351170,68129,368701,233566,144256,156172,41972,499687,390955,6549,298079,498230,196397,239493,242037);
(100 row(s) returned)
Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000
2、
select * from  t_06 a inner join lsbtest b on a.id=b.id1;
lsbtest：没有索引(102 row(s) returned)
Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000
如果直接运行1、2则两者在速度上基本相近但2如果加上建表、插入数据，则
DROP TABLE IF EXISTS `lsbtest`; 
CREATE TABLE `lsbtest` ( 
  `id1` bigint(10) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `lsbtest` */ insert  into `lsbtest`(`id1`) values (1),(2),(38068),(238833),(308799),(274344),(299910),(309823),(337486),(290812),(56393),(64413),(492511),(260426),(58626),(450987),(499220),(187731),(365665),(212799),(227788),(255724),(384636),(465766),(417310),(313148),(483987),(328761),(402876),(237274),(249183),(174185),(28187),(189155),(259940),(67800),(60065),(340172),(311667),(354861),(182),(305523),(115981),(365082),(213915),(47894),(131301),(198754),(358852),(112496),(404423),(486725),(233123),(322936),(325337),(125932),(299260),(128791),(295663),(469897),(120580),(347766),(34859),(364204),(37597),(268974),(351155),(256955),(214013),(309192),(412394),(216800),(30315),(411242),(16678),(233247),(359013),(401666),(30792),(452394),(408649),(14159),(5519),(91705),(227648),(120966),(319599),(351170),(68129),(368701),(233566),(144256),(156172),(41972),(499687),(390955),(6549),(298079),(498230),(196397),(239493),(242037); select * from  t_06 a inner join lsbtest b on a.id=b.id1;Total Time     : 00:00:01:094
Total Time     : 00:00:00:250
Total Time     : 00:00:00:015大约1.359秒

Answer 5

把这个放到一个INDEX中，可以吗？
比如：
create index index_id on yourtable (id)

Answer 6

用临时表的话，应该把创建临时表和删除临时表的时间算上
试试这个不创建表的方法（没有环境测试，不知道性能如何）：
select 
  a.* 
from 
  t_06 as a
inner join 
  (select 38068 as id
   union all select 238833   union all select 308799 
   union all select 274344 
   union all select 299910 
   union all select 309823 
   union all select 337486 
   union all select 290812 
   union all select 56393 
   union all select 64413 
   union all select 492511 
   union all select 260426 
   union all select 58626 
   union all select 450987 
   union all select 499220 
   union all select 187731 
   union all select 365665 
   union all select 212799 
   union all select 227788 
   union all select 255724 
   union all select 384636 
   union all select 465766 
   union all select 417310 
   union all select 313148 
   union all select 483987 
   union all select 328761 
   union all select 402876 
   union all select 237274 
   union all select 249183 
   union all select 174185 
   union all select 28187 
   union all select 189155 
   union all select 259940 
   union all select 67800 
   union all select 60065 
   union all select 340172 
   union all select 311667 
   union all select 354861 
   union all select 182 
   union all select 305523 
   union all select 115981 
   union all select 365082 
   union all select 213915 
   union all select 47894 
   union all select 131301 
   union all select 198754 
   union all select 358852 
   union all select 112496 
   union all select 404423 
   union all select 486725 
   union all select 233123 
   union all select 322936 
   union all select 325337 
   union all select 125932 
   union all select 299260 
   union all select 128791 
   union all select 295663 
   union all select 469897 
   union all select 120580 
   union all select 347766 
   union all select 34859 
   union all select 364204 
   union all select 37597 
   union all select 268974 
   union all select 351155 
   union all select 256955 
   union all select 214013 
   union all select 309192 
   union all select 412394 
   union all select 216800 
   union all select 30315 
   union all select 411242 
   union all select 16678 
   union all select 233247 
   union all select 359013 
   union all select 401666 
   union all select 30792 
   union all select 452394 
   union all select 408649 
   union all select 14159 
   union all select 5519 
   union all select 91705 
   union all select 227648 
   union all select 120966 
   union all select 319599 
   union all select 351170 
   union all select 68129 
   union all select 368701 
   union all select 233566 
   union all select 144256 
   union all select 156172 
   union all select 41972 
   union all select 499687 
   union all select 390955 
   union all select 6549 
   union all select 298079 
   union all select 498230 
   union all select 196397 
   union all select 239493 
   union all select 242037
  ) as b
on
  a.id=b.id

Answer 7

临时表：
DELIMITER $$DROP PROCEDURE IF EXISTS `zz`.`cxzfnewa`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `cxzfnewa`(i varchar(5000))
BEGIN
DECLARE ai INT DEFAULT 1;
DROP TABLE IF EXISTS `t_06`; 
create table t_06 (
    id    int not null primary key,
    c1    varchar(30),
    i2    int
) engine = myisam; 
    WHILE ai < 500000 DO
        insert into t_06 values (ai,concat('AA',ai),ai);
        SET ai = ai + 1;
    END WHILE;
DROP TABLE IF EXISTS `lsb2`; 
CREATE TABLE `lsb2` ( 
  `id` bigint(10) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
set ai=1;
while ai<=1000 do
insert into lsb2 values (ai);
set ai=ai+1;
end while;
DROP TABLE IF EXISTS `lsbtest`; 
CREATE TABLE `lsbtest` ( 
  `id1` bigint(10) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
insert into lsbtest
select 
mid(mid(i,id,length(i)),2,LOCATE(',',mid(i,id,length(i)),2)-2)
from lsb2 where length(i)>=id and mid(i,id,1)=',' 
and length(mid(i,id,length(i)))>=2;
select * from  t_06 a inner join lsbtest b on a.id=b.id1;
    END$$DELIMITER ;Total Time     : 00:00:47:719

Answer 8

上一个调用：
call cfzfnew(',38068,238833,308799,274344,299910,309823,337486,290812,56393,64413,492511,260426,58626,450987,499220,187731,365665,212799,227788,255724,384636,465766,417310,313148,483987,328761,402876,237274,249183,174185,28187,189155,259940,67800,60065,340172,311667,354861,182,305523,115981,365082,213915,47894,131301,198754,358852,112496,404423,486725,233123,322936,325337,125932,299260,128791,295663,469897,120580,347766,34859,364204,37597,268974,351155,256955,214013,309192,412394,216800,30315,411242,16678,233247,359013,401666,30792,452394,408649,14159,5519,91705,227648,120966,319599,351170,68129,368701,233566,144256,156172,41972,499687,390955,6549,298079,498230,196397,239493,242037,');
show warnings;IN方法：
call cfzxin('38068,238833,308799,274344,299910,309823,337486,290812,56393,64413,492511,260426,58626,450987,499220,187731,365665,212799,227788,255724,384636,465766,417310,313148,483987,328761,402876,237274,249183,174185,28187,189155,259940,67800,60065,340172,311667,354861,182,305523,115981,365082,213915,47894,131301,198754,358852,112496,404423,486725,233123,322936,325337,125932,299260,128791,295663,469897,120580,347766,34859,364204,37597,268974,351155,256955,214013,309192,412394,216800,30315,411242,16678,233247,359013,401666,30792,452394,408649,14159,5519,91705,227648,120966,319599,351170,68129,368701,233566,144256,156172,41972,499687,390955,6549,298079,498230,196397,239493,242037');
show warnings;
Total Time     : 00:00:18:234DELIMITER $$DROP PROCEDURE IF EXISTS `zz`.`cfzxin`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `cfzxin`(i varchar(8000))
BEGIN
DECLARE ai INT DEFAULT 1; 
DROP TABLE IF EXISTS `t_06`; 
create table t_06 ( 
    id    int not null primary key, 
    c1    varchar(30), 
    i2    int 
) engine = myisam; 
    WHILE ai < 500000 DO 
        insert into t_06 values (ai,concat('AA',ai),ai); 
        SET ai = ai + 1; 
    END WHILE;
set @qq=concat('select * from t_06 where id in(',i,')');
select @qq;
prepare dd from @qq;
execute dd;
    END$$DELIMITER ;

Answer 9

用临时表方法，去掉生成LSB2（即生成1－1000数字的代码），加入主键Total Time     : 00:00:18:438DELIMITER $$DROP PROCEDURE IF EXISTS `zz`.`cfzfnewa`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `cfzfnewa`(i varchar(5000))
BEGIN
DECLARE ai INT DEFAULT 1; 
DROP TABLE IF EXISTS `t_06`; 
create table t_06 ( 
    id    int not null primary key, 
    c1    varchar(30), 
    i2    int 
) engine = myisam; 
    WHILE ai < 500000 DO 
        insert into t_06 values (ai,concat('AA',ai),ai); 
        SET ai = ai + 1; 
    END WHILE; DROP TABLE IF EXISTS `lsbtest`; 
CREATE TABLE `lsbtest` ( 
  `id1` bigint(10) primary key 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into lsbtest 
select 
mid(mid(i,id,length(i)),2,LOCATE(',',mid(i,id,length(i)),2)-2) 
from lsb2 where length(i)>=id and mid(i,id,1)=',' 
and length(mid(i,id,length(i)))>=2; 
select a.* from  t_06 a inner join lsbtest b on a.id=b.id1;
    END$$DELIMITER ;与IN方法时间相差不多。

Answer 10

妙！我的看法是：如果前面要查询的源表的数据量很大时，则把in这部分放进临时表并建立索引来关联的速度是最快的（当然源查询表对应字段也有索引），如果要把查询动态化的话，我的看法跟WWWWA 一样，就是用存储过程的方式，把in那部分的值清单作为参数传进去，若in的清单比较多，则在存储过程内部的最后对临时表进行人工处理（先truncate，再drop）。其实这种需求，关键还要看源查询表的数据量及in的值清单的数据量。

Answer 11

我习惯是这样,放哈稀表里,然后 INNER JOIN .

Answer 12

其实在特大的高压下，你会发现IN的效率很高！最近在帮客户解决了一个问题，就是把自己内部的临时表直接转成IN了。能够承受的压力更大！

Answer 13

如果你数据量比较少，那可能in的效率比临时表还要快了
其实想下就知道，少了索引扫描、临时表的IO部分、磁头的移动臂减少等等方面。

Answer 14

这也有可能. IN ( ...... ) 经MYSQL优化后可能变成有序的内存表或数组了.

Answer 15

从8楼，9楼的试验结果上，似乎还看不出 mysql 用临时表做 join 会比 直接 IN 有效率上的提高。
各位可以在其它数据库上试试

Answer 16

个人总结一下以上的方法有:1。 直接使用 IN (38068,238833,308799 ... )
2。 将 (38068,238833,308799 ... ) 放入临时表，然后用 JOIN
3。 直接在程序中 执行多个 select * where id = 38068; select * from where id=238833; ...
4。 使用 inner join (select 38068 union all select 238833 union all ... )

Answer 17

关注此帖子,也遇到这个问题
目前是直接用的in的,源数据表有20万数据,in后面的数字有一百多个,感觉有些吃不清,建临时表并join没有试过

Answer 18

1 觉得楼主要这种测试比较意义不太大，不同数据分布查询速度会有一点点不同。 2 在MySQL 中，应该只有 in 和 join 表之间比较。3 join 表比较多的时候会比 in 慢，因为 in 是在 buffer 中寻找对应内容；
  直接使用 【fill_record】函数就完成；join 是需要创建临时表的额外消耗。

Answer 19

我也想知道，目前也遇到了同样的问题，如果我的in里有5W个Id或更多，结果会怎样呢。

Answer 20

如果我的in里有5W个Id或更多，结果会怎样呢?用 join

Answer 21

好帖！csdn这种贴多一些的话会感觉很好。

Answer 22

我想知道in里多于多少个id了，需要换成join希望下次搜到的时候，有人回答了。

Answer 23

到20W个ID，in就吃不消了，5W以内没问题，10W的话，in会比left join慢

Answer 24

测试结果：近30W条数据，in(小于100个的主键)，响应时间在毫秒级。

Answer 25

那要是同时查询的个数多呢 像这样
。in(1,2,3,4,5,6,7,8,9,10);
。in(12,22,32,42,52,62,72,82,92,102);
。in(11,21,31,41,51,61,7,81,9,10);
。in(13,2,3,4,53,6,7,8,9,10);
。in(1,2,32,4,5,6,74,84,9,10);
。in(15,25,3,4,5,6,7,8,69,10);
我这儿使用后的结果，效率很慢，慢慢的jsp页面就卡死了
这个问题怎么解决 用什么可以替换in