含IN子句的MySQL查询性能低下，寻求高效优化方案

哇，这么多层嵌套的子查询确实会让MySQL优化器难以生成高效的执行计划，5000条数据就耗时80秒完全是因为重复扫描和低效的关联方式。先帮你拆解下原查询的核心需求：

你需要筛选出同时满足以下三个条件的item，然后获取这些item在FK_SnapshotId = 1时，FK_PropertyId = 12的value值：

• 该item的FK_PropertyId = 1对应的value属于(12,11)
• 该item的FK_PropertyId = 12对应的value不属于("Rejected","Closed")
• 该item的FK_PropertyId = 2对应的value属于(73,74)

方案一：使用JOIN替代嵌套子查询 #

子查询的IN操作尤其是多层嵌套时，MySQL需要多次扫描表，而JOIN可以让优化器更高效地利用索引关联数据。优化后的SQL如下：

SELECT pv_final.*
FROM remian.remian_propertyvalue pv_final
JOIN remian.remian_propertyvalue pv1 
  ON pv_final.FK_ItemId = pv1.FK_ItemId
  AND pv1.FK_PropertyId = 1 
  AND pv1.Value IN (12,11)
JOIN remian.remian_propertyvalue pv2 
  ON pv_final.FK_ItemId = pv2.FK_ItemId
  AND pv2.FK_PropertyId = 2 
  AND pv2.Value IN (73,74)
JOIN remian.remian_propertyvalue pv12 
  ON pv_final.FK_ItemId = pv12.FK_ItemId
  AND pv12.FK_PropertyId = 12 
  AND pv12.Value NOT IN ("Rejected","Closed")
WHERE pv_final.FK_PropertyId = 12 
  AND pv_final.FK_SnapshotId = 1;

方案二：使用GROUP BY + HAVING聚合筛选 #

如果同一个item可能有多个相同property的记录（比如不同snapshot），可以用聚合的方式一次性筛选出满足所有条件的item，再关联获取目标值：

SELECT pv.*
FROM remian.remian_propertyvalue pv
JOIN (
  SELECT FK_ItemId
  FROM remian.remian_propertyvalue
  WHERE 
    (FK_PropertyId = 1 AND Value IN (12,11))
    OR (FK_PropertyId = 2 AND Value IN (73,74))
    OR (FK_PropertyId = 12 AND Value NOT IN ("Rejected","Closed"))
  GROUP BY FK_ItemId
  HAVING 
    COUNT(DISTINCT CASE WHEN FK_PropertyId = 1 THEN Value END) >= 1
    AND COUNT(DISTINCT CASE WHEN FK_PropertyId = 2 THEN Value END) >= 1
    AND COUNT(DISTINCT CASE WHEN FK_PropertyId = 12 THEN Value END) >= 1
) AS valid_items ON pv.FK_ItemId = valid_items.FK_ItemId
WHERE pv.FK_PropertyId = 12 
  AND pv.FK_SnapshotId = 1;

关键优化：添加合适的索引 #

上面两种方案的性能提升都依赖于合理的索引，建议在remian_propertyvalue表上创建以下复合索引：

• idx_property_value_item (FK_PropertyId, Value, FK_ItemId)：让筛选property和value的条件能快速定位到对应的item
• idx_item_property_snapshot (FK_ItemId, FK_PropertyId, FK_SnapshotId)：让最后获取目标值的查询能快速找到对应记录

创建索引的SQL：

CREATE INDEX idx_property_value_item ON remian.remian_propertyvalue(FK_PropertyId, Value, FK_ItemId);
CREATE INDEX idx_item_property_snapshot ON remian.remian_propertyvalue(FK_ItemId, FK_PropertyId, FK_SnapshotId);

验证建议 #

在执行优化后的查询前，可以用EXPLAIN命令查看执行计划，确认索引是否被正确使用，比如：

EXPLAIN SELECT pv_final.* FROM ...; -- 替换成优化后的SQL

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