我必须通过一个或多个类别ID检索帖子列表。我不希望在结果中有重复的帖子。
我只对直接相关的回复感兴趣，或者可以与MySQL 8建立联系
有两个问题我正在考虑，我决定哪一个更好。或者，如果有一个更好的“第三个问题”，请建议。
考虑一个简单的两表结构：
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的SUBQUERY）：
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）才得到结果
- 没有临时表。只使用索引。
第二次查询（INNER JOIN with 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条答案
按热度按时间cyej8jka1#
几件事：
1.您为这两个查询都设置了适当的索引。
1.执行计划通常会随着表的增长而变化。在小表上保存几百微秒的工作对于较大的表不一定有用。随着表的增长，您可能需要重新访问执行计划。
1.“使用临时”并不意味着你的查询使用了一个完整的磁盘临时表。它只是意味着软件在对结果集进行重复数据删除之前，将其累积到一个临时数据结构中。（只有当临时数据结构对于RAM来说太大时，软件才会使用磁盘上的结构。您的结构当然适合RAM。）不要被不准确但历史悠久的执行计划的
extra列中的语言。“使用临时”可以。value IN (set of values)predicate 会自动删除重复的值。因此，您的第一个查询可以在没有GROUP BY的情况下重写。这是我会使用的查询，因为（在我看来）它最清楚地表达了你的意图。而且，我怀疑它可以更好地扩展到大型表，因为它只对
posting_id值集进行重复数据删除工作，而不是整行。1.第二个查询误用了MySQL的notorious nonstandard extension to GROUP BY。使用
SET sql_mode = CONCAT_WS(',',@@sql_mode, 'ONLY_FULL_GROUP_BY')禁用该扩展，然后重试查询。您将需要在GROUP BY子句中使用更多术语。更好的方法是，去掉GROUP BY并使用DISTINCT，如下所示。但这必须对整行进行重复数据消除。