MySQL多对多关系场景下动态过滤查询的构造方法

推荐解决方案 #

方案一：基于分组计数的高效查询（最适合动态构造） #

这个方案直接利用多对多关联的匹配次数做判断，完全避免了LIKE模糊匹配，所有条件都可以走索引，性能远高于GROUP_CONCAT方案，同时支持OR/AND逻辑的动态构造：

SELECT s.*
FROM states s
JOIN states_descriptions sd ON s.id = sd.state_id
JOIN descriptions d ON sd.description_id = d.id
-- 先把所有涉及的标签筛出来，走d.description的索引过滤掉无关数据
WHERE d.description IN ('hot', 'humid')
GROUP BY s.id
-- 动态调整HAVING条件即可实现OR/AND逻辑
HAVING COUNT(DISTINCT d.description) = <匹配数量>

• 如果是AND逻辑（需要同时满足所有标签）：<匹配数量>填你要匹配的标签总数，比如同时要hot和humid就填2，自动筛选出同时关联了两个标签的州
• 如果是OR逻辑（满足任意一个标签即可）：把HAVING条件改成COUNT(DISTINCT d.description) >=1就行，或者直接删掉HAVING也能得到一样的结果

如果有更复杂的混合逻辑（比如(hot AND humid) OR dry），可以拆分逻辑组分别统计，动态拼接HAVING条件即可，用查询构造器的fluent接口非常好实现。

方案二：基于EXISTS的关联查询（数据量极大时性能更优） #

如果单表数据量千万级以上，不想做GROUP BY，可以针对每个AND条件加独立的EXISTS子查询，OR条件则合并到同一个子查询的WHERE里：

• 示例1：查询同时有hot和humid标签的州（AND逻辑）

SELECT * FROM states s
WHERE 
EXISTS (
  SELECT 1 FROM states_descriptions sd 
  JOIN descriptions d ON sd.description_id = d.id 
  WHERE sd.state_id = s.id AND d.description = 'hot'
)
AND EXISTS (
  SELECT 1 FROM states_descriptions sd 
  JOIN descriptions d ON sd.description_id = d.id 
  WHERE sd.state_id = s.id AND d.description = 'humid'
)

• 示例2：查询有hot或者humid标签的州（OR逻辑）

SELECT * FROM states s
WHERE 
EXISTS (
  SELECT 1 FROM states_descriptions sd 
  JOIN descriptions d ON sd.description_id = d.id 
  WHERE sd.state_id = s.id AND d.description IN ('hot', 'humid')
)

这个方案所有子查询都可以走states_descriptions.state_id、descriptions.description的索引，不需要全表扫描，也不需要临时表分组，性能极高，而且逻辑拆分清晰，用查询构造器动态追加条件非常方便，不需要写多套逻辑，只需要根据解析出来的表达式逻辑，动态拼接EXISTS条件的AND/OR关系即可。

性能优化建议 #

提前给descriptions.description字段加唯一索引，给states_descriptions的state_id和description_id加联合索引，上面两个方案的查询速度都会接近主键查询的效率，完全满足大数据量场景的性能要求。

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