{eval=Array;=+count(Array);}

问答专栏Q & A COLUMN

MySQL查询select * from table where id in (几百或几千个id) ,如何提高效率?

CKJOKERCKJOKER 回答0 收藏1
问题描述:电商网站,一个商品属性表,几十万条记录,80M,索引只有主键id,做这样的查询如何提高效率? select * from table where id in (几百或几千个id) 这些id没啥规律,分散的……
收藏问题

10条回答

Jonathan Shiebe

Jonathan Shiebe

回答于2022-06-28 14:43

看了下面各位的回答,有的说用exist,有的说用join,难道你们不是在把简单的事情复杂化了吗?竟然还有子表子查询一说?也有朋友说的很精准,不要用select *,这个*是个坑,实际开发过程中,关于MySQL开发规范也会明确告知大家不要select *。

首先我想问的是:查询MySQL的一张表怎么查最快?当然是根据主键查询了!

默认你的MySQL库、表引擎是Innodb引擎,然后会有一颗主键的B+树,叶子节点就是这个主键索引对应的数据,意味着一次查询即可,回表都不需要好不好?简单直接!

这就是MySQL在Innodb引擎下的聚集索引。

什么是聚集索引?

InnoDB聚集索引的叶子节点存储行记录,因此InnoDB必须要有且只有一个聚集索引。

1.如果表定义了PK(Primary Key,主键),那么PK就是聚集索引。

2.如果表没有定义PK,则第一个NOT NULL UNIQUE的列就是聚集索引。

3.否则InnoDB会另外创建一个隐藏的ROWID作为聚集索引。

这种机制使得基于PK的查询速度非常快,因为直接定位的行记录。

下图是利用普通索引做查询时候的一个回表操作,如何避免回表操作?使用覆盖索引!即select xxx,yyy from table where xxx=' and yyy=',只能查询xxx,yyy就会避免回表操作!

所以你还搞什么其他各种操作来秀呢?只不过题主说了id不是连续的,所以做不到范围查询,也就无法between查询了。

不要纯粹的依赖数据库

如果这个查询量级很大,并发很高,原则上我们是不允许直接查库的,中间必须有一层缓存,比如Redis。那至于这个数据怎么存储到redis就要看具体业务具体分析了。

如果内存足够,甚至可以把这几十万的数据直接放到redis里面去,然后通过redis 的管道查询一次给批量查询出来。

如果没必要存储这么多,或者不让存这么多,是不是可以采用redis的淘汰策略来控制缓存里的数据都是热点数据?

评论0 赞同0
  •  加载中...
jayce

jayce

回答于2022-06-28 14:43

几十万用主键查你还闲效率低?又不是上亿的表。如果上亿了那就拆表吧,也简单的。

评论0 赞同0
  •  加载中...
luoyibu

luoyibu

回答于2022-06-28 14:43

select a.* from tableA a

inner join (select id from tableB)b on a. id=b. id

创建临时表tableB并建主键索引,

或者构建inner join的只包括id字段的虚拟表b。

评论0 赞同0
  •  加载中...
anonymoussf

anonymoussf

回答于2022-06-28 14:43

mysql8.0版本中有with公共表达式,这个是最好的,通过两个表做关联,8.0以下的取决于你的where条件了,icp可以谓词下推,in在优化器会自动转换成semi-jion,在8.0.4以后exists也可以使用semi join了,在之前的时候exists就只能使用dependent subquey了,exists 要想快的话是 小表驱动大表;所以说这里最重要的是看你的where条件中是否有筛选。如果没有的话,最好建临时表 或者用with,另外不要用select *,要不然在后面优化器 需要block nestloop的时候会有压力,如果都不让用的话,你目前的叙述,in比exists好

评论0 赞同0
  •  加载中...
jiekechoo

jiekechoo

回答于2022-06-28 14:43

MySQL官网有对in的说法,如果有索引,其实都差不多。如果你查询的不是所有字段,只拿abc三个字段,可以在abc三个字段建立组合索引,in再走索引。如果我没有记错MySQL不会限制你in多少,但是会限制整个SQL总字节数

评论0 赞同0
  •  加载中...
JerryWangSAP

JerryWangSAP

回答于2022-06-28 14:43

还是从表结构上调整吧!

评论0 赞同0
  •  加载中...
Cciradih

Cciradih

回答于2022-06-28 14:43

首先为什么一次查这么多,合理吗?

没法在数据库层面直接优化,建议利用缓存解决。

评论0 赞同0
  •  加载中...
anonymoussf

anonymoussf

回答于2022-06-28 14:43

将条件中的几千个id扔到另外一个表里面,然后用exists语句来替换in就可以了

评论0 赞同0
  •  加载中...
clasnake

clasnake

回答于2022-06-28 14:43

1,去掉*, select table.name,table.age,table.asscess from table where in ( ....)

2.如果id是连续的范围,那就用between and 代替in

评论0 赞同0
  •  加载中...
glumes

glumes

回答于2022-06-28 14:43

几十万行,in还有几百上千,应该还有一层关系,按归类给id做排序索引,使得你要的是其中几段:也即,随机io转连续io,再加索引覆盖。

评论0 赞同0
  •  加载中...

最新活动

您已邀请0人回答 查看邀请

我的邀请列表

  • 擅长该话题
  • 回答过该话题
  • 我关注的人
向帮助了您的网友说句感谢的话吧!
付费偷看金额在0.1-10元之间
<