MySQL 中 where id in (1,2,3,4,...) 的效率问题讨论

最新推荐文章于 2022-09-20 10:46:55 发布
最新推荐文章于 2022-09-20 10:46:55 发布
阅读量4.9k 收藏
点赞数 1
文章标签： 数据库 php java

MySQL

ACMAIN_CHM ACMAIN_CHM 06-26 16:36
等级 T12 84次回复

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

庆祝本月大版得分过万，兼把在这段论坛中经常被问到的一个问题拿出来大家讨论一下。

命题假设：

测试表如下

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);
 
wangxuantest wangxuantest 06-26 16:43
等级 T12 1楼

支持楼主  呵呵  有才人啊

0 0
liangCK liangCK 06-26 16:55
等级 T12 2楼

先接个分..再看.

0 0
apple_8180 apple_8180 06-26 16:56
等级 T12 3楼

将100已知ID写入一个临时表，然后用联结试试

0 0
Conry Conry 06-26 16:56
等级 T12 4楼

 

引用 2 楼 liangCK 的回复:
先接个分..再看.

up
0 0
netxuning netxuning 06-26 16:59
等级 T12 5楼

支持

0 0
liangCK liangCK 06-26 16:59
等级 T12 6楼

我认为分两步.
先将提供的ID转成行集.
ID
-----
38068
238833
308799
..

然后与表做JOIN操作.

0 0
apple_8180 apple_8180 06-26 17:05
等级 T12 7楼

 

引用 6 楼 liangCK 的回复:
我认为分两步. 
先将提供的ID转成行集. 
ID 
----- 
38068 
238833 
308799 
.. 

然后与表做JOIN操作.


我3楼就是这个意思。

实际还要对比 Join 和 In 在本例中那个效率高
0 0
WWWWA WWWWA 06-26 17:11
等级 T12 8楼

1、
直接用IN

select * from  t_06 a inner join lsbtest b on a.id=b.id1;



2、用LSBTEST

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



0 0
WWWWA WWWWA 06-26 17:21
等级 T12 9楼

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秒

0 0
lastyang lastyang 06-26 18:10
等级 T12 10楼

帮顶

0 0
nettman nettman 06-26 18:10
等级 T12 11楼

友情UP!

0 0
LongYongkai LongYongkai 06-26 21:28
等级 T12 12楼

UP

0 0
kaifaye
最低0.47元/天 解锁文章
weixin_34198797
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL 存储过程传参数实现where id in(1,2,3,...)示例
09-10
本篇文章将详细介绍如何通过存储过程传递参数来实现`WHERE ID IN (1, 2, 3, ...)`这样的筛选条件。 在标准SQL中，我们可以直接编写如下的查询来获取`field1`字段值在给定列表中的记录： ```sql SELECT * FROM ...
MySQL 存储过程传参数实现where id in(1,2,3,…)示例
12-15
正常写法： 代码如下: select * from table_name t where t.field1 in (1,2,3,4,…); 当在写存储过程in里面的列表用个传入参数代入的时候，就需要用到如下方式： 主要用到find_in_set函数 代码如下: select * from ...
mysql in id效率_MySQL 中 where id in (1,2,3,4,...) 的效率问题讨论
weixin_30289299的博客
01-20 788
等级1楼支持楼主呵呵有才人啊liangCK06-26 16:55等级2楼先接个分..再看.apple_818006-26 16:56等级3楼将100已知ID写入一个临时表，然后用联结试试Conry06-26 16:56等级4楼引用2楼liangCK的回复:先接个分..再看.upnetxuning06-26 16:59等级5楼支持liangCK06-26 16:59等级6楼我认为分两...
按照 where id in （）排序
weixin_30407099的博客
10-13 193
按照 where id in （）排序 select * from ibs6_terminal_adv_inf where id in (16,14,15) order by find_in_set(id,'16,14,15') posted on 2014-10-13 15...
mysql where id_mysql – WHERE id IN()语句中的多个相同id
weixin_39929138的博客
01-19 1408
如果(1,2,3,4,4,5)来自SELECT id …查询,那么你可以这样做：UPDATE yourTableJOIN( SELECT id, COUNT(id) AS counter....GROUP BY id) AS dataON yourTable.id = data.idSET yourTable.field = yourTable.field + data.counter;由于输入来...
mysql 按照 where id IN (42,25,26,27) 按照顺序查找id 的办法
永远是死循环
10-14 505
id IN (42,25,26,27)   order by find_in_set(id,'42,25,26,27')
mysql id in 效率_MySQL 中 where id in (1,2,3,4,...) 的效率问题讨论
weixin_39612877的博客
01-19 807
等级1楼支持楼主呵呵有才人啊liangCK06-26 16:55等级2楼先接个分..再看.apple_818006-26 16:56等级3楼将100已知ID写入一个临时表，然后用联结试试Conry06-26 16:56等级4楼引用2楼liangCK的回复:先接个分..再看.upnetxuning06-26 16:59等级5楼支持liangCK06-26 16:59等级6楼我认为分两...
MySQL-Short-Course.zip_4 3 2 1_MYSQL_course_html+mysql_php mysql
09-19
Mysql short course 1. Introduction to MySQL 2. MySQL and PHP 3. Database Queries 4. The Contacts Database – HTML, PHP and MySQL code
实验训练1 在MySQL中创建数据库和表.docx
12-03
在安装完成后，会显示如图4-2所示的对话框，在图4-2中选中Launch the MySQL Instance Configuration Wizard复选框，单击Finish按钮，进行配置。 在配置过程中，需要选择使用哪种配置方式，例如Detailed ...
Metasploit with MYSQL in BackTrack 4 r2.txt
03-18
Metasploit with MYSQL in BackTrack 4 r2.txt
mysql 存储过程 where in_MySQL 存储过程传参数问题：实现 where id in(1,2,3,…) | 学步园...
weixin_42474220的博客
01-27 174
昨天一朋友说在MYSQL存储过程里怎么输入参数如:select * from table_name t where t.field1 in (1,2,3,4,...);网上找了老半天，参数还是不会传进去。后来想到ORACLE 里面可以拼SQL然后将拼完的SQL再进行执行今天终于搞定了实现这种效果还是选贴段代码吧DROP PROCEDURE IF EXISTS photography.Proc_Te...
SQL 语句 where id in() 出现的问题
qq_1334124306
11-28 7459
自己编写sql查询 where id in() 出现的问题 mybatis 中当 DAO 传入的 List 参数时,会自动将参数封装成 Map 参数,而 map中的 key 会自动用 list , value 就是你传入的 List 参数. 修改 第一种: 将 List 参数封装为 Map 然后再传入,在 XML 配置页面写上相应 key 值. 第二种: 将 condition 的值修...
where id in 排序 按in的顺序排
touch126的博客
03-23 944
where id in 排序 按in的顺序排 Access: select * From 表 Where id in(1,5,3) order by instr(',1,5,3,',','&id&',') MSSQL: select * From 表 Where id in(1,5,3) order by charindex(','+rtrim(cast(id as varchar(10)))+',',',1,5,3,') MySQL: select * From 表 Where id
mysql 存储过程 where in,MySQL 存储过程传参数实现where id in(1,2,3,...)示例
weixin_39636707的博客
03-17 219
正常写法：select * from table_name t where t.field1 in (1,2,3,4,...);当在写存储过程in里面的列表用个传入参数代入的时候，就需要用到如下方式：主要用到find_in_set函数select * from table_name t where find_in_set(t.field1,'1,2,3,4');当然还可以比较笨实的方法，就是组装字...
MySQL where in 用法详解
热门推荐
DianLanHong的博客
08-18 1万+
IN 运算符用于 WHERE 表达式中，以列表项的形式支持多个选择,这里分两种情况来介绍 WHERE column IN (value1,value2,…) WHERE column NOT IN (value1,value2,…) in 后面是记录集，如： SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=0) 在这个 SQL 例子里，我们实现了查出所有状态为 0 的用户（可能是被禁止）的所有文章。首先通过一
mybatis中用where id in ()，当数据大于1000条时可能出错问题解决
ruiguang21的博客
09-20 1070
mybatis中用where id in ()，当数据大于1000条时可能出错问题解决
mysql select in 排序,使用SELECT ... WHERE ID IN（...），按IN（）排序结果？
weixin_32558527的博客
01-27 164
With a query such as:SELECT * FROM images WHERE id IN (12,9,15,3,1)is it possible to order the results by the contents of the IN clause?The result I'm looking for would be something like:[0] => Arr...
MySQL在InnoDB引擎下使用where in 导致锁升级
LJWWD的博客
05-31 723
一、项目场景： 在学习mysql锁相关知识，自己操作时发现了一个现象: 对于命令select id from test where id in () lock in share mode; 当in中元素超过2个时，会锁住所有记录，升级成表锁。 二、问题描述 1. 问题复现 1.1. test表中数据 1.2. performance_schema.data_locks数据 1.3. 手动开启事务，并执行以下命令。 select id from test where id in (2) lock in
python - 数据库查询条件有多个值时如何处理 where id in (, , ,)
Charonmomo的博客
01-01 849
这个问题困扰了好久, 没有在网上找到全一点的处理方法. 现在解决了, 记录一下处理的办法: #ask customer to order food print("\n which kind of pizza would you like to order? Please input the index number of them." "\n (note: Please separate them with commas, like 1,2,3. When you have finished t
mysql delete in_Mysql delete from where id in(select ...) 只能够删除一条数据
最新发布
05-31
DELETE FROM 表名 WHERE ID IN (1,2,3); ``` 也可以使用子查询来删除符合条件的多条数据，例如： ``` DELETE FROM 表名 WHERE ID IN (SELECT ID FROM 表名 WHERE 条件); ``` 这样就可以删除符合条件的多条数据了...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值