@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.

```