请教个 sql 查询问题

[brader]

需求大概是这样：根据搜索条件 查询商品列表，每个商品有分类属性，需要根据分类来分组显示，分类下没有商品的分类不展示，因为商品数据量非常庞大，所以一次搜索出来的商品无法展示完全，我采用了分页，根据搜索条件以及每个分类 ID 来分页查商品，因为产品需要只展示有商品的分类，所以在这之前，我的查出来有商品的分类列表给前端。
因搜索条件灵活动态多变，所以这个分类基本无法固定下来，缓存意义也不大。

开始我采用了方案一： sql 查询语句 where 过滤搜索条件，GROUP BY 分类 id ，该加的索引也加上了。 该方案大部分都是正常的，后面有测试给我反馈有些很慢，我排查到是某些店铺商品数量非常多，然后搜索条件又需要筛选辅表关联表，主表:辅表 是 1:n 关系，所以数据量非常庞大（有多大呢，举个例子，有些一个商品绑了 5 万多个车型，为什么允许绑这么多，是否合理，我也不想纠结，以前的技术肯定也是无奈），GROUP BY 分类 id 有点久。

后续我尝试了方案二： 拿着该店铺绑定的所有分类 id ，循环遍历 sql 查询语句 where 过滤搜索条件+分类 id ，判断有无商品存在，该语句执行状况非常好，每句在 10-20ms ，查 20 个分类都不到 500ms 。大部分店铺也是 20 个分类以下的。

我统计了分类，少部分店铺存在 50-100 个分类，这些店铺采用方案二我非常担心，毕竟是循环，分类多了，累积时间就久。

想请教下各位，这种需求有更好的方案吗？我能想的办法都想了，不知道是产品设计复杂还是技术太菜了。。。

[morty0]

放到 es 查

[notwaste]

参考京东的话，首次查询应该分类与商品不需要耦合在一起吧

[brader]

@yogogo 不行，还是一样慢，估计还是里面数据太多了

[brader]

@morty0 这是一个很好的方法，但是时间以及服务器资源没有条件给我发挥，而且搜索方面的话，我们项目也有用阿里的开放搜索，这个比 es 更强大，就是为了这个需求再买一个开放搜索实例，领导不会允许的。。。

[brader]

@notwaste 反复和产品沟通过，也说明了他这个需求这样子展示分类的利弊，但是产品不肯妥协，只能自己想办法了

[brader]

@notwaste 其实不止京东，最具参考的应该是美团外卖的 APP ，他们的分类展示设计逻辑都是非常简洁高效的，比我这个需求都简洁很多，美团就是直接读取当前商家创建展示的分类，是不会管你这个分类有没有商品的，我清楚这块是因为我在美团 APP 开过店

[wxf666]

@brader 能大致放一下当前的表结构 和 查询 SQL 吗？

[jinweijie]

用 CTE 会不会好一点？

[yogogo]

product_bsm_extract.brand_id 这个字段设计有问题，这样连接取这个值效率很慢，尤其是 ON 加了两个条件

[akira]

@brader
product.`cat3_id`= apply_users_category.`category_id`
and product.shop_id= 367

product.shop_id // 商店 id 有了
product.`cat3_id` // 分类 id 有了，

这 2 个东西 product 表都有，你外面还套一层干嘛。

[xuanbg]

先查店铺绑定的分类 ID ，然后查商品 join 这个子查询。

[xuanbg]

OP 你的问题是查询条件没有索引，所以直接按条件查速度慢。然而商品是有分类的，店铺也绑定了有限的分类，因为分类 ID 有索引，所以你用分类 ID 作为查询条件之一，就能很有效率。
你方案 2 是先查出店铺的分类 ID ，然后循环用分类 ID 查询数据。这两步其实可以写成 1 个 SQL 语句，就是把方案 2 的查询店铺分类 id 语句作为子查询，join 到方案 1 的查询语句里面就行。

[brader]

@wxf666 表结构有点敏感，怕被同事认出，哈哈

[brader]

@yogogo 这个表示是不得已的，因为主表是 1 ，对到这个表是 n ，而且这个表的辅助筛选字段不仅仅是 brand_id ，还有很多其他字段

[brader]

@xuanbg 我看过查询计划，是有用上索引的，而且这个用上的索引，当时是我尝试性根据 where 条件针对性加的联合索引