mysql In长度的问题?

xiasf
  • 1.9k

业务逻辑中常有这样的情况:

得到某一个无限分类下面的全部文章。

统计某一账号下面店铺的全部订单。

经常会用到article: in (cid,……),
order: in (shop_id,……)

如果某个分类下面有很多很多子分类,总之很多很多,可能无限,某一个账号下面也有n个店铺,那么in ()不撑死了啊,sql长度不能很大吧

不知道对于这种情况有没有什么好的方法,项目中很多地方都是这样用in (),从来没有考虑过这个问题,今天突然想到了,意识到了这个问题,不知道有没有好的优化方式。

在线等大神,谢谢了!

回复
阅读 7.1k
7 个回答

不记得有代替in的语句,可能也是因为我不太关注sql导致水平较低哈。
这种问题通常是由于无限分类表本身的设计问题导致的,例如只有id, parent_id两个字段,所以只要in了,如果无限分类表包含 id, parent_id, level(树高), path这个字段的时候就可以避免使用in了。

我个人使用的一个无限分级表包括以下的字段
id 唯一索引
parent_id 父级的id
number 数轴投影序号
leve 树高
left_number 已当前节点为树根时,其下的最左端的子节点的数轴投影序号
一个简单的例子演变:

  [id:1, level:1 number: 1, left_number:1]

插入一个子节点

   [id:1, level:1 number: 2, left_number:1]

[id:2, level:2 number: 1, left_number:1]
于level2再插入一个子节点

    [id:1, level:1 number: 3, left_number:1]

[id:2, level:2 number: 1, left_number:1] [id:2, level:2 number: 2, left_number:2]

这种树形的数据维护很麻烦,但是搜索要快的多,例如任意节点下的全部子节点就是left_number到number-1。用这个做过一个Chuan啊销系统的人员结构树,效果很不错。

核心思路就是把一颗树的分支转换为数轴上的点/线段,从而在数轴上得到完整的树的投影。

忘了一个top_id,表示节点对应的根节点,否则表里面有多颗树的话就出问题了。

采用Memory引擎表,在拼接in里面的字段时,把这些值全部写到这个表中,然后使用这个表关联查询;in里面最好是索引字段,这样的话,可以避免in长度限制。

不想用 in 的话,可以考虑冗余一个字段。比如所有的商铺订单加上帐号 id。冗余字段带来新问题就是关系变动的维护:比如商铺转移给其他帐号(但历史订单应该还是属于原来的帐号,冗余应该问题不大)
分类的话可能就不适合冗余了,随时可能查询不同级别下的所有分类,还是用 in 比较好,毕竟不是真的无限分类。

mysql in 本身 没有长度限制,而整个SQL 是有长度限制的,明知道 in 的法子有一定的缺陷或者 存在隐患,不妨 换一种思路,一个SQL 很难搞定,实在不济, 动态拼凑其多个union all ,也可以搞定的

IN没有限制,不过整个SQL长度在my.cnf可以配置限制的max_allowed_packet=2M。SQL太长肯定会影响传输和查询分析器分析的效率,越精简越好。
IN的这种做法,本质上底层IN(1,2) 和... where id = 1 union all ..where id = 2差不多的。

从业务上要归避这种垃圾SQL。
1、业务上如果可以,把IN(id1,id2) 把这些id放到一个表中管理。然后JOIN这个表。
2、通常你的id1,id2是由某个查询产生的,可以用子查询来做。
3、产品上妥协,改善

  1. mysql中的In操作,只要字段上建立好有效的索引,效率完全不用担心。

  2. 不过id In('1','2', ....,'1000' )这种语句,最好数量控制在一千以内,再多的话,sql效率就会下降。如果有需求超过一千以上,说明楼主需要冗余字段了,比如冗余你业务场景中的用户账号ID。

  3. 至于楼上建议join联表的,我觉得如果表数量级在10万级别还可以;一旦达到百万级,联表就是灾难。

in 实在显示不了,试试left join on 连接一下吧

宣传栏