AND嵌套多OR条件的SQL返回空原因及INTERSECT查询性能优化

第一类查询返回空结果的根因 #

你使用的custom_attribute_values是典型EAV（实体-属性-值）结构表，每一行仅存储单个会员的单条自定义属性键值对：任意一行的custom_attribute_id只会对应一个固定属性取值，要么是代表性别的12，要么是代表婚姻状况的17，不可能同一行同时存两个属性的值。
你编写的第一个查询的WHERE逻辑，要求同一行数据同时满足「属性12匹配指定性别值」和「属性17匹配指定婚姻状况值」两个完全互斥的条件，这类条件在单条EAV记录中永远不可能成立，因此查询必然返回空结果集。

基于INTERSECT的查询的耗时优化方案 #

当前使用的INTERSECT写法，本质是两次独立扫描符合条件的会员集合，再对两个集合做排序、去重、求交集操作，百万级数据下排序去重的开销会随数据量线性增长，可通过以下方式降低耗时：

• 搭建适配查询场景的覆盖联合索引
  针对查询的筛选条件和返回字段，创建联合索引(custom_attribute_id, value_string, attributable_id)。索引建成后，查询可直接从索引树中获取全部需要的字段值，不需要回表扫描原始数据页，通常能把单轮查询的IO开销降低一个数量级。
• 改写查询逻辑为单次扫描+分组聚合，替代两次扫描的INTERSECT流程
  把原来需要两次独立扫表、再求交集的逻辑，改成一次扫描筛出所有相关行，按会员ID分组后判断是否同时满足两类属性要求，彻底避免两次扫描、两次排序去重的额外开销，参考改写后的SQL：

  SELECT attributable_id AS Member_id
  FROM custom_attribute_values
  WHERE 
    (custom_attribute_id = '12' AND value_string IN ('Female', 'Male'))
    OR (custom_attribute_id = '17' AND value_string IN ('Widowed','Divorced','Never married','Married','Separated'))
  GROUP BY attributable_id
  HAVING 
    MAX(CASE WHEN custom_attribute_id = '12' THEN 1 ELSE 0 END) = 1
    AND MAX(CASE WHEN custom_attribute_id = '17' THEN 1 ELSE 0 END) = 1
  

• 缩小无效数据的扫描范围
  在WHERE条件最外层提前限定本次查询涉及的属性ID范围，避免扫描全表中其他自定义属性的无关记录；如果表本身按属性ID或会员ID做了分区，配合分区裁剪可进一步减少磁盘扫描的IO开销。

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