MariaDB中NOT EXISTS多行列与状态过滤查询问题及优化方案咨询

让我们逐个拆解你的问题，再给出更高效的实现方案：

1. 为什么第一个查询无法过滤状态不为'A'的产品？ #

你的第一个查询的子查询逻辑出了核心问题：

SELECT *
FROM product_bundle_parts AS parts
LEFT JOIN products AS products ON parts.sku = products.sku
WHERE parts.bundle_id = bundles.bundle_id
AND products.status = 'A'
AND products.product_id IS NULL

这里的WHERE条件同时要求products.status = 'A'和products.product_id IS NULL——但products.product_id IS NULL意味着这个sku在products表里完全没有匹配项，此时products.status也是NULL，根本不可能等于'A'。

对于那些sku存在但状态不是'A'的情况（比如示例里的sku55），LEFT JOIN后会得到products.status = 'D'，这时候products.status = 'A'的条件会把这些行直接过滤掉。所以子查询永远找不到符合条件的行，NOT EXISTS就会一直为真，自然无法排除任何有无效产品的捆绑包。

2. 为什么第二个查询无法完成执行？ #

第二个查询把products.status = 'A'移到了JOIN条件里，逻辑上是对的——它会找出捆绑包中要么sku不存在，要么状态不是'A'的部件。但执行卡住通常是因为执行计划效率太低：

• 当product_bundle_parts数据量很大时，MySQL可能会对每个捆绑包都做全表扫描来匹配部件，再和products做左连接，没有充分利用索引。
• 如果没有合适的组合索引，比如product_bundle_parts(bundle_id, sku)或者products(sku, status)，数据库需要做大量的关联计算，导致查询超时甚至无法完成。

3. 更高效的替代方案 #

这里有两种更可靠且性能更好的实现方式：

方案一：GROUP BY + HAVING 匹配计数 #

通过对比捆绑包的总部件数和有效部件数，确保所有部件都符合要求：

SELECT SQL_CALC_FOUND_ROWS bundles.*
FROM product_bundles AS bundles
JOIN product_bundle_parts AS parts ON bundles.bundle_id = parts.bundle_id
LEFT JOIN products AS p 
  ON parts.sku = p.sku 
  AND p.status = 'A' -- 只匹配状态为A的有效产品
GROUP BY bundles.bundle_id
HAVING COUNT(parts.part_id) = COUNT(p.product_id) -- 总部件数 = 有效部件数
-- 其他动态条件（排序等）
LIMIT 0, 24

这个逻辑很直观：COUNT(parts.part_id)是捆绑包的总部件数，COUNT(p.product_id)是成功匹配到有效产品的部件数，只有两者相等时，才说明所有部件都符合要求。

方案二：修正NOT EXISTS逻辑 #

重新调整子查询的判断条件，直接找出存在无效部件的捆绑包并排除：

SELECT SQL_CALC_FOUND_ROWS bundles.*
FROM product_bundles AS bundles
WHERE NOT EXISTS (
    SELECT 1 -- 用SELECT 1比SELECT *更高效
    FROM product_bundle_parts AS parts
    LEFT JOIN products AS p ON parts.sku = p.sku
    WHERE parts.bundle_id = bundles.bundle_id
    AND (p.product_id IS NULL OR p.status != 'A') -- 只要有一个部件无效就排除
)
-- 其他动态条件（排序等）
LIMIT 0, 24

这个写法逻辑更清晰：只要捆绑包里有任何一个部件不存在于products表，或者状态不是'A'，就会被NOT EXISTS排除。

性能优化建议 #

为了让查询更快，建议添加以下组合索引：

• 给product_bundle_parts加索引：CREATE INDEX idx_bundle_sku ON product_bundle_parts(bundle_id, sku);
• 给products加索引：CREATE INDEX idx_sku_status ON products(sku, status);

这些索引可以让数据库快速定位到对应的部件和产品，避免全表扫描。

内容的提问来源于stack exchange，提问作者Harm Smits