MySQL多对多关联表AND/OR条件搜索查询的优化方案咨询

结合你的数据规模（tbl_post_mention数百万行、tbl_mention不足100行），以及当前查询存在的性能问题，我从索引优化、查询改写、执行计划调优三个方面给出针对性方案：

一、AND条件（包含所有指定mention的帖子）优化 #

1. 查询改写：用JOIN替代分组计数 #

你当前的分组计数方案需要对匹配的记录做分组统计，当匹配数据量较大时，分组计算的开销会很高。可以改用多表JOIN的方式，直接匹配同时包含所有指定mention的帖子：

-- 示例：查找同时包含mention_idx=1和95的post_idx
SELECT DISTINCT pm1.post_idx
FROM tbl_post_mention pm1
JOIN tbl_post_mention pm2 
  ON pm1.post_idx = pm2.post_idx
WHERE pm1.mention_idx = 1 
  AND pm2.mention_idx = 95;

如果需要匹配3个及以上mention，继续追加JOIN即可。这种方式利用索引直接定位匹配的post_idx，避免了分组计数的计算开销。

2. 针对性索引优化 #

确保tbl_post_mention上创建复合索引(mention_idx, post_idx)：

CREATE INDEX idx_pm_mention_post ON tbl_post_mention(mention_idx, post_idx);

这个索引的顺序非常关键：先按mention_idx过滤（精准匹配），再直接获取对应的post_idx，JOIN时可以快速匹配关联的post_idx，完全避免全表扫描。

3. 数据库特性利用（部分数据库支持） #

如果你的数据库支持INTERSECT语法（如PostgreSQL、SQL Server），可以用交集查询替代JOIN，代码更简洁：

SELECT post_idx FROM tbl_post_mention WHERE mention_idx=1
INTERSECT
SELECT post_idx FROM tbl_post_mention WHERE mention_idx=95;

INTERSECT会自动去重，且每个子查询都能用到上述复合索引，性能表现优异。

二、OR条件（包含任意指定mention的帖子）优化 #

1. 查询改写：用UNION替代IN+DISTINCT #

你当前的IN+DISTINCT方案，IN会触发索引范围扫描，后续的DISTINCT还需要去重排序。改用UNION拆分多个精准查询，利用索引的精准匹配能力：

SELECT post_idx FROM tbl_post_mention WHERE mention_idx=1
UNION
SELECT post_idx FROM tbl_post_mention WHERE mention_idx=95;

UNION会自动去重，且每个子查询都能命中(mention_idx, post_idx)索引，比IN的范围扫描效率更高。如果业务允许（你的场景应该不需要），可以用UNION ALL进一步提升性能，但记得后续关联时处理重复数据。

2. 关联查询提前合并 #

避免先查询post_idx再关联tbl_post和tbl_mention，直接将OR条件和关联逻辑合并，减少中间结果集的传递：

SELECT DISTINCT p.*, m.*
FROM tbl_post p
JOIN tbl_post_mention pm ON p.post_idx = pm.post_idx
JOIN tbl_mention m ON pm.mention_idx = m.mention_idx
WHERE pm.mention_idx IN (1, 95)
ORDER BY p.post_created DESC
LIMIT n;

结合(mention_idx, post_idx)索引和tbl_post上的(post_created, post_idx)复合索引，可以让排序和关联过程更高效。

三、后续关联与排序的通用优化 #

1. 预排序索引优化 #

如果你的排序字段是post_created，在tbl_post上创建复合索引(post_created DESC, post_idx)：

CREATE INDEX idx_post_created ON tbl_post(post_created DESC, post_idx);

这样在关联后排序时，数据库可以直接利用索引的有序性，避免额外的filesort操作，大幅提升排序性能。

2. 临时表/CTE优化（针对大数据量结果） #

如果AND/OR查询返回的post_idx数量较多，可以将结果存入临时表并创建索引，再关联其他表：

-- 示例：AND条件下的临时表方案
CREATE TEMPORARY TABLE temp_posts AS
SELECT DISTINCT pm1.post_idx
FROM tbl_post_mention pm1
JOIN tbl_post_mention pm2 ON pm1.post_idx = pm2.post_idx
WHERE pm1.mention_idx=1 AND pm2.mention_idx=95;

CREATE INDEX idx_temp_post_idx ON temp_posts(post_idx);

SELECT p.*, m.*
FROM temp_posts tp
JOIN tbl_post p ON tp.post_idx = p.post_idx
JOIN tbl_post_mention pm ON tp.post_idx = pm.post_idx
JOIN tbl_mention m ON pm.mention_idx = m.mention_idx
ORDER BY p.post_created DESC
LIMIT n;

临时表的索引可以让后续关联操作更快，尤其适合结果集较大的场景。

四、分区方案（可选） #

由于tbl_mention不足100行，可以考虑对tbl_post_mention按mention_idx做LIST分区（如PostgreSQL）或RANGE分区（如MySQL）：

-- PostgreSQL示例：按mention_idx列表分区
CREATE TABLE tbl_post_mention (
  post_idx INT,
  mention_idx INT
) PARTITION BY LIST (mention_idx);

-- 为每个常用的mention_idx创建分区（或批量创建）
CREATE TABLE tbl_post_mention_1 PARTITION OF tbl_post_mention FOR VALUES IN (1);
CREATE TABLE tbl_post_mention_95 PARTITION OF tbl_post_mention FOR VALUES IN (95);

分区后，查询指定mention_idx时，数据库只会扫描对应的分区，减少数据扫描范围，提升查询速度。

五、容易遗漏的基础概念 #

1. 复合索引的顺序：索引字段顺序要匹配查询的过滤顺序，先过滤的字段放在前面（如mention_idx在前，post_idx在后），才能让索引被高效利用。
2. 执行计划分析：用EXPLAIN查看查询的执行计划，确认是否命中预期索引，是否存在全表扫描、临时表、filesort等开销较大的操作。
3. 外键约束的影响：外键约束会带来额外的维护开销，但你的场景中外键是必要的，确保外键关联的字段都有索引即可。

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