如何快速检索/统计满足多状态条件的Items表记录？

数据表结构 #

商品表（Items） #

商品状态表（States） #

（注：并非所有商品都包含全部state_var_name）

需求 #

最快的检索/统计符合多状态条件的商品的方法是什么？
例如：检索所有满足PAID = true 且 CANCELLED = (false 或不存在该状态)的商品。

现有尝试（性能不佳） #

Items表约80万行，States表约1300万行，以下两种查询均耗时数十秒：

第一种尝试 #

SELECT COUNT(*) FROM items
WHERE 
    EXISTS (
        SELECT item_id FROM states 
        WHERE item_id = items.id AND state_var_name = 'PAID' AND state_value = 1
    )
    AND NOT EXISTS (
        SELECT item_id FROM states 
        WHERE item_id = items.id AND state_var_name = 'CANCELLED' AND state_value = 1
    )

第二种尝试（略快但仍慢） #

SELECT COUNT(*) FROM items
    LEFT JOIN states AS s1
    ON s1.item_id = items.id AND s1.state_var_name = 'PAID' AND s1.state_value = 1
    LEFT JOIN states AS s2
    ON s2.item_id = items.id AND s2.state_var_name = 'CANCELLED' AND s2.state_value = 1
WHERE s1.state_value IS NOT NULL AND s2.state_value IS NULL

（编辑：States表已有item_id + state_var_name + state_value的联合索引）

优化方案 #

方案1：从States表聚合筛选后关联Items #

先在States表中筛选出符合条件的item_id，再关联Items表统计，避免全表扫描Items：

SELECT COUNT(DISTINCT s.item_id)
FROM states s
WHERE 
    s.state_var_name = 'PAID' AND s.state_value = 1
    AND NOT EXISTS (
        SELECT 1 
        FROM states sc
        WHERE sc.item_id = s.item_id 
          AND sc.state_var_name = 'CANCELLED' 
          AND sc.state_value = 1
    )

如果需要获取商品详情，再关联Items表：

SELECT i.*
FROM items i
JOIN (
    SELECT DISTINCT item_id
    FROM states s
    WHERE s.state_var_name = 'PAID' AND s.state_value = 1
      AND NOT EXISTS (
          SELECT 1 
          FROM states sc
          WHERE sc.item_id = s.item_id 
            AND sc.state_var_name = 'CANCELLED' 
            AND sc.state_value = 1
      )
) filtered ON i.id = filtered.item_id

方案2：使用条件聚合筛选 #

通过聚合函数将同一商品的状态整合，再筛选符合条件的记录：

SELECT COUNT(item_id)
FROM (
    SELECT 
        item_id,
        MAX(CASE WHEN state_var_name = 'PAID' THEN state_value ELSE 0 END) AS is_paid,
        MAX(CASE WHEN state_var_name = 'CANCELLED' THEN state_value ELSE 0 END) AS is_cancelled
    FROM states
    WHERE state_var_name IN ('PAID', 'CANCELLED')
    GROUP BY item_id
) state_summary
WHERE is_paid = 1 AND is_cancelled = 0

这个方案利用索引快速筛选出仅涉及目标状态的记录，聚合后再判断条件，避免了大量无关数据的处理。

索引优化建议 #

虽然已有联合索引(item_id, state_var_name, state_value)，可以考虑调整索引顺序为(state_var_name, state_value, item_id)，这样在筛选特定状态时能更快定位到符合条件的item_id，减少索引扫描范围。

内容的提问来源于stack exchange，提问作者Charles.C