MySQL中如何为IN数组内每个元素匹配独立筛选条件实现高效查询

适用场景：待查询的book数量较少（100个以内）时的最优选择，写法简单性能高。

SELECT book_number, COUNT(*) as book_orders
FROM book_sales
WHERE 
    (book_number = 1 AND purchase_date > 1412)
    OR (book_number = 2 AND purchase_date > 1621)
    OR (book_number = 3 AND purchase_date > 1014)
    OR (book_number = 4 AND purchase_date > 1978)
GROUP BY book_number;

注意：只要提前给book_sales表创建(book_number, purchase_date)联合索引，所有条件都会走索引过滤，不会触发全表扫描，性能远高于多子查询写法。如果某本书没有符合条件的记录，该book_number不会出现在返回结果中。

适用场景：待查询的book数量较多，拼接OR语句过长的场景，同时支持返回无匹配记录的book的统计值（结果为0）。

SELECT t.book_number, COUNT(bs.id) as book_orders
FROM (
    -- 此处批量填入所有book_number和对应的阈值
    SELECT 1 as book_number, 1412 as min_purchase_date UNION ALL
    SELECT 2 as book_number, 1621 as min_purchase_date UNION ALL
    SELECT 3 as book_number, 1014 as min_purchase_date UNION ALL
    SELECT 4 as book_number, 1978 as min_purchase_date
) t
LEFT JOIN book_sales bs 
    ON bs.book_number = t.book_number 
    AND bs.purchase_date > t.min_purchase_date
GROUP BY t.book_number;

优势：结构化存储阈值配对，SQL结构更清晰，不会因为条件过多导致语句难以维护，同样可以命中上述联合索引。

• 上述两种方案依赖的(book_number, purchase_date)联合索引属于覆盖索引，统计COUNT(*)时不需要回表查询数据行，千万级数据量下也可以保持毫秒级响应。
• 不要使用多子查询笛卡尔积写法，该方案会产生大量临时中间表，性能随查询的book数量增长指数级下降。

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