时间戳

首页 > 解决方案 > MySQL查询 - 派生表与左连接在哪里

mysql - MySQL查询 - 派生表与左连接在哪里

问题描述

给定posts和posts_tags：

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    external_id varchar(10) UNIQUE,
    title varchar(255),
    body TEXT
)

CREATE TABLE posts_tags (
    id SERIAL PRIMARY KEY,
    post_external_id varchar(10) REFERENCES posts(external_id),
    tag varchar(255),
    UNIQUE KEY (post_external_id, tag)
)

如您所见，上面的表格存储了后端服务 REST API 的帖子及其标签。

有一个get-post-by-external-idAPI 将返回帖子，其标签由帖子的external_id. 鉴于这个用例，我想知道下面哪个查询执行得更快并且消耗的资源更少？

派生表

SELECT posts.*, posts_tags.tag 
FROM (SELECT * FROM posts WHERE external_id = ?) AS posts 
LEFT JOIN posts_tags ON posts.external_id = posts_tags.post_external_id

左加入哪里

SELECT posts.*, posts_tags.tag 
FROM posts 
LEFT JOIN posts_tags ON posts.external_id = posts_tags.post_external_id
WHERE posts.external_id = ?

我不确定，但我猜过滤器会在发生WHERE后过滤中间结果LETF JOIN？

标签： mysqlsql

解决方案

不需要子查询。引擎不会按照您编写的顺序评估/执行查询。优化器将生成一个尽可能最优的“执行路径”（尽管它并不完美）。但是对于这种特殊情况，它记录在WHERE 子句优化中：

对于连接中的每个表，WHERE构造一个更简单的方法来快速 WHERE评估表并尽快跳过行。

所以你的猜测：

I guess the WHERE filters the intermediate results after the LETF JOIN happens?

是错的。给定引擎上的索引posts.external_id将仅“触摸”匹配的行并posts_tags仅针对该/那些行执行连接。

根据我的经验，我还可以告诉您，第二个（微不足道的）查询在性能方面非常出色。

尽管出于另一个原因，我不会使用该查询。您会根据相关标签获得重复的帖子数据。假设您有一篇包含 10KB 文本和 10 个标签的帖子。您将获得 10 行（每个标签一个）具有相同文本并一起获取 100KB 的数据。然后，您将需要编写代码来“去重”数据。

我宁愿运行两个单独的查询：

SELECT posts.*
FROM posts 
WHERE posts.external_id = ?

和

SELECT posts_tags.tag 
FROM posts_tags
WHERE posts_tags.post_external_id = ?

并将结果“合并”到应用程序代码中

或使用 GROUP_CONCAT() 聚合标签

SELECT posts.*, GROUP_CONCAT(posts_tags.tag) as tags
FROM posts 
LEFT JOIN posts_tags ON posts.external_id = posts_tags.post_external_id
WHERE posts.external_id = ?
GROUP BY posts.external_id

或使用 JSON_ARRAYAGG()

SELECT posts.*, JSON_ARRAYAGG(posts_tags.tag) as tags
FROM posts 
LEFT JOIN posts_tags ON posts.external_id = posts_tags.post_external_id
WHERE posts.external_id = ?
GROUP BY posts.external_id

在任何应用程序语言中，解析逗号分隔列表或 JSON 数组都应该很简单。

推荐阅读