MySQL 8中带GROUP BY的子查询与内连接查询性能对比及优化

需求背景 #

根据一个或多个分类ID获取职位列表，结果中不能包含重复职位，仅针对MySQL 8.x版本提供解决方案。现有两种查询方案，需要对比性能优劣，并探讨是否存在更优的第三种方案。

表结构定义 #

CREATE TABLE `job_category_posting` (
  `category_posting_id` int UNSIGNED NOT NULL,
  `category_posting_category_id` int UNSIGNED NOT NULL,
  `category_posting_posting_id` int UNSIGNED NOT NULL,
  `category_posting_is_primary_category` tinyint UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `job_posting` (
  `posting_id` int UNSIGNED NOT NULL,
  `posting_title` varchar(250) NOT NULL,
  `posting_body` mediumtext CHARACTER SET utf8mb4 COLLATE=utf8mb4_0900_ai_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `job_category_posting`
  ADD PRIMARY KEY (`category_posting_id`),
  ADD UNIQUE KEY `category_posting_category_id` (`category_posting_category_id`,`category_posting_posting_id`),
  ADD UNIQUE KEY `category_posting_is_primary_category` (`category_posting_is_primary_category`,`category_posting_posting_id`),
  ADD KEY `category_posting_posting_id` (`category_posting_posting_id`) USING BTREE;

ALTER TABLE `job_posting`
  ADD PRIMARY KEY (`posting_id`),
  ADD UNIQUE KEY `posting_reserve_id` (`posting_reserve_id`),
  ADD KEY `posting_title` (`posting_title`);

现有两种查询方案对比 #

方案一：带GROUP BY的子查询 #

查询语句

SELECT t1.*
FROM job_posting AS t1
WHERE (t1.posting_id) IN(
   SELECT category_posting_posting_id
   FROM job_category_posting
   WHERE category_posting_category_id IN (2,13,22,23,24,25)
   GROUP BY category_posting_posting_id
)

性能测试结果

• 0.0017秒
• 0.0016秒
• 0.0011秒
• 0.0017秒

EXPLAIN分析结论

• 查询计划遍历行数较多（2356 + 1 + 1935）
• 未使用临时表，仅依赖索引完成查询

方案二：带GROUP BY的内连接 #

查询语句

SELECT job_posting.*
 FROM job_category_posting
 inner join job_posting on job_category_posting.category_posting_posting_id = job_posting.posting_id
 WHERE category_posting_category_id IN (2,13,22,23,24,25)
GROUP BY category_posting_posting_id

性能测试结果

• 0.0016秒
• 0.0011秒
• 0.0010秒
• 0.0019秒

EXPLAIN分析结论

• 查询计划仅遍历1935 + 1行
• 执行过程中使用了临时表

方案对比结论与优化建议 #

哪种方案更优？ #

从当前测试数据看，两种方案的实际执行时间差异极小，均处于毫秒级。但从长期性能和资源占用角度分析：

1. 方案一的优势是不依赖临时表，避免了磁盘IO或内存临时表的创建/销毁开销。当数据量持续增大时，临时表的资源消耗会逐步凸显，此时方案一的稳定性更优；劣势是遍历行数更多，极端大数据量场景下，索引扫描的总开销可能上升。
2. 方案二的优势是遍历行数更少，索引匹配更高效，但临时表的引入会带来额外资源消耗。若服务器内存充足，临时表可驻留内存，性能差异不大；但内存不足时，临时表写入磁盘会导致性能明显下降。

综合来看，数据量中等或较大的场景下，方案一更稳妥；若当前数据量极小且服务器资源充足，方案二也可接受。

是否存在更优的第三种方案？ #

推荐两种更高效的替代方案，适配MySQL 8特性：

方案三：使用DISTINCT替代GROUP BY（内连接版）

SELECT DISTINCT job_posting.*
FROM job_category_posting
INNER JOIN job_posting 
  ON job_category_posting.category_posting_posting_id = job_posting.posting_id
WHERE category_posting_category_id IN (2,13,22,23,24,25)

优势：DISTINCT直接对结果去重，避免GROUP BY可能带来的排序或临时表开销（取决于优化器选择）。结合job_category_posting表的(category_posting_category_id, category_posting_posting_id)唯一索引，可快速定位匹配记录，再通过主键关联job_posting表获取数据，效率更高。

方案四：使用EXISTS半连接查询

SELECT t1.*
FROM job_posting AS t1
WHERE EXISTS (
    SELECT 1
    FROM job_category_posting AS t2
    WHERE t2.category_posting_posting_id = t1.posting_id
      AND t2.category_posting_category_id IN (2,13,22,23,24,25)
)

优势：EXISTS是半连接逻辑，只要找到匹配记录就停止扫描，无需返回所有匹配行。结合t2表的唯一索引，可快速判断是否存在关联记录，彻底避免GROUP BY或DISTINCT的去重开销，性能通常优于前两种方案。

索引优化补充 #

当前表的索引设计已较为完善，需确保job_category_posting表的(category_posting_category_id, category_posting_posting_id)唯一索引被充分利用——该索引可直接覆盖WHERE条件和关联字段，是此类查询的核心索引，能最大化所有方案的执行效率。

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