这是一个创建于 724 天前的主题，其中的信息可能已经有所发展或是发生改变。
需求大概是这样：根据搜索条件 查询商品列表，每个商品有分类属性，需要根据分类来分组显示，分类下没有商品的分类不展示，因为商品数据量非常庞大，所以一次搜索出来的商品无法展示完全，我采用了分页，根据搜索条件以及每个分类 ID 来分页查商品，因为产品需要只展示有商品的分类，所以在这之前，我的查出来有商品的分类列表给前端。
因搜索条件灵活动态多变，所以这个分类基本无法固定下来，缓存意义也不大。
开始我采用了方案一： sql 查询语句 where 过滤搜索条件，GROUP BY 分类 id ，该加的索引也加上了。 该方案大部分都是正常的，后面有测试给我反馈有些很慢，我排查到是某些店铺商品数量非常多，然后搜索条件又需要筛选辅表关联表，主表:辅表 是 1:n 关系，所以数据量非常庞大（有多大呢，举个例子，有些一个商品绑了 5 万多个车型，为什么允许绑这么多，是否合理，我也不想纠结，以前的技术肯定也是无奈），GROUP BY 分类 id 有点久。
后续我尝试了方案二： 拿着该店铺绑定的所有分类 id ，循环遍历 sql 查询语句 where 过滤搜索条件+分类 id ，判断有无商品存在，该语句执行状况非常好，每句在 10-20ms ，查 20 个分类都不到 500ms 。大部分店铺也是 20 个分类以下的。
我统计了分类，少部分店铺存在 50-100 个分类，这些店铺采用方案二我非常担心，毕竟是循环，分类多了，累积时间就久。
想请教下各位，这种需求有更好的方案吗？我能想的办法都想了，不知道是产品设计复杂还是技术太菜了。。。
 |
1
nekolr 2022-11-15 11:42:03 +08:00
产品设计上可以考虑分类多了不用都展示
|
 |
2
zlhsvc 2022-11-15 11:46:46 +08:00
分类不分页吗，50-100 个分类一页也展示不全吧。
|
 |
3
brader OP @nekolr 其实分类也不多，举个详细点的例子，某个店铺授权了 100 个分类，但当前搜索条件下，存在商品的分类，筛选完只有 15 个，你说多吗？不多，但是这个筛选，就是我上面说的，筛不动。。。
|
|
4
brader OP @zlhsvc 没到分页的程度，是我筛都筛不动，比如这个店铺 100 个分类，这个搜索条件下只有 5 个分类存在商品，你说 5 个分类还有必要分页吗？但是我要从几千万数据中根据搜索条件筛选出这 5 个分类，这一步就太难了，很慢
|
 |
5
dqzcwxb 2022-11-15 13:49:10 +08:00
循环查询+缓存,这样第二次查询就不走数据库只过内存
当然还要把双重循环匹配改成 hashjoin,这个你应该知道吧? |
|
6
brader OP @dqzcwxb 目前就是采用循环查询的，也加了 5 分钟缓存，不过缓存作用不是特别大，因为用户输入、筛选构建的搜索查询条件时时在变化。hashjoin 没听过，哈哈。
|
 |
7
Dganzh 2022-11-15 14:14:37 +08:00
方案 2 ，分类多时并发查可行不？
|
 |
9
gy123 2022-11-15 14:27:53 +08:00
分类筛选的数据是面对所有用户的,感觉还是得缓存,缓存粒度小点;
比如现在需要通过分类以及其他条件筛选,那么先将数据根据分类类型进行逐个缓存,然后在细分,在应用层进行一个合并数据; |
|
10
brader OP @Dganzh 没有尝试过，因为目前二三十个分类循环，响应时间还是挺快的，项目是 PHP 写的，也不怎么方便开多线程并发
|
|
11
brader OP @dqzcwxb 可能我描述的不是很到位，代码是刚写的，我很确认代码逻辑上是没有双层循环的，只循环查询了分类列表，这个分类列表也很有限，100 个以内，压力主要在数据库
|
|
13
gy123 2022-11-15 14:40:05 +08:00
@brader 看看具体数据量,拿出来全放内存看看能占多少,纯走内存查询那肯定很快..或者就是看看有啥规律,将数据进行处理冗余到另一个表,大宽表操作,减少查询等
|
 |
14
yogogo 2022-11-15 15:32:10 +08:00
子查询啊，一句 sql 就出来了
|
|
15
yogogo 2022-11-15 15:36:17 +08:00
select
分类 id, (select count(*) from 商品 where 分类 id = 分类 id limit 1) num from 分类 having num > 0 |
|
18
yogogo 2022-11-15 15:56:34 +08:00
select
分类 id, (select max(id) from 商品 where 分类 id = 分类 id ) num from 分类 having num is not null |
|
19
brader OP @yogogo 很感谢您的回答，刚才我使用您提供的思路，进行了尝试，查询计划显示是非常优良的，但是实际执行效果却不太理想。
sql 语句参考： ``` SELECT `category_id`, ( select max(product.id) from `product` INNER JOIN product_bsm_extract ON product_bsm_extract.shop_id= product.shop_id and product_bsm_extract.product_id= product.id where product.`cat3_id`= apply_users_category.`category_id` and product.shop_id= 367 AND `product_brand_id` IN('10113', '10162', '10163', '10164', '10287') AND product_bsm_extract.brand_id= 1 AND `is_del`= 0) num FROM `apply_users_category` WHERE `apply_shop_id`= 367 having num is not null ``` 查询计划： ``` id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY apply_users_category ref PRIMARY,IDX_CAT_ID PRIMARY 4 const 1 100 Using index 2 DEPENDENT SUBQUERY product ref PRIMARY,IDX_SHOP_DEL,idx_is_del,idx_shop_id,idx_shop_cat_prod idx_shop_cat_prod 8 const,warehouse.apply_users_category.category_id 3138 25 Using index condition; Using where 2 DEPENDENT SUBQUERY product_bsm_extract ref IDX_BRAND_ID,IDX_MAIN,IDX_PRODUCT_ID,IDX_SHOP_ID_PROD_ID IDX_SHOP_ID_PROD_ID 8 const,warehouse.product.id 44 10 Using where ``` 实际执行效果： ``` category_id num 199 1246851 [消息] ：执行成功，当前返回：[1]行，耗时：[30,566]ms. ``` |
 |
21
morty0 2022-11-15 17:18:43 +08:00
放到 es 查
|
 |
22
notwaste 2022-11-15 17:26:03 +08:00
参考京东的话，首次查询应该分类与商品不需要耦合在一起吧
|
|
24
brader OP @morty0 这是一个很好的方法，但是时间以及服务器资源没有条件给我发挥，而且搜索方面的话，我们项目也有用阿里的开放搜索，这个比 es 更强大，就是为了这个需求再买一个开放搜索实例，领导不会允许的。。。
|
|
26
brader OP @notwaste 其实不止京东，最具参考的应该是美团外卖的 APP ，他们的分类展示设计逻辑都是非常简洁高效的，比我这个需求都简洁很多，美团就是直接读取当前商家创建展示的分类，是不会管你这个分类有没有商品的，我清楚这块是因为我在美团 APP 开过店
|
 |
28
jinweijie 2022-11-15 22:19:03 +08:00
用 CTE 会不会好一点？
|
|
29
yogogo 2022-11-15 22:53:34 +08:00
product_bsm_extract.brand_id 这个字段设计有问题，这样连接取这个值效率很慢，尤其是 ON 加了两个条件
|
 |
30
akira 2022-11-15 23:45:08 +08:00
@brader
product.`cat3_id`= apply_users_category.`category_id` and product.shop_id= 367 product.shop_id // 商店 id 有了 product.`cat3_id` // 分类 id 有了， 这 2 个东西 product 表都有，你外面还套一层干嘛。 |
 |
31
xuanbg 2022-11-16 08:15:32 +08:00
先查店铺绑定的分类 ID ，然后查商品 join 这个子查询。
|
|
32
xuanbg 2022-11-16 08:25:50 +08:00
OP 你的问题是查询条件没有索引，所以直接按条件查速度慢。然而商品是有分类的，店铺也绑定了有限的分类，因为分类 ID 有索引，所以你用分类 ID 作为查询条件之一，就能很有效率。
你方案 2 是先查出店铺的分类 ID ，然后循环用分类 ID 查询数据。这两步其实可以写成 1 个 SQL 语句，就是把方案 2 的查询店铺分类 id 语句作为子查询，join 到方案 1 的查询语句里面就行。 |
Select Language