mysql - 优化 mysql 查询 - 避免创建临时表？

Question

这是我在表上使用的查询:products , reviews , replies , review_images .

询问 :

SELECT products.id, reviews.*,
GROUP_CONCAT(DISTINCT CONCAT_WS('~',replies.reply, replies.time)) AS Replies,
GROUP_CONCAT(DISTINCT CONCAT_WS('~',review_images.image_title, review_images.image_location)) AS ReviewImages
FROM products
LEFT JOIN reviews on products.id = reviews.product_id
LEFT JOIN replies on reviews.id = replies.review_id
LEFT JOIN review_images on reviews.id = review_images.review_id
WHERE products.id = 1
GROUP BY products.id, reviews.id;

架构 :

产品:

id  |  name  |  product_details....

评论:

id  |  product_id  |  username  |  review  |  time  | ...

回复:

id  |  review_id   |  username  |  reply  |  time  | ...

评论图片:

id  |  review_id  |  image_title  |  image_location  | ...

索引 :

产品:

主键 - id

评论:

主键 - id

FOREIGN KEY - product_id (id IN products table)

FOREIGN KEY - 用户名(用户表中的用户名)

回复:

主键 - id

FOREIGN KEY - review_id(评论表中的id)

FOREIGN KEY - 用户名(用户表中的用户名)

评论图片:

主键 - id

FOREIGN KEY - review_id(评论表中的id)

解释查询:

身份证 | 选择类型 | 表 | 类型 | 可能的 key | 行 | 额外

1 |简单 |产品 |索引 |空| 1 |使用索引；使用临时；使用文件排序

1 |简单 |评论 |所有 | product_id | 4 |使用哪里；使用连接缓冲区(块嵌套循环)

1 |简单 |回复 |引用 | review_id | 1 |空值

1 |简单 | review_images |所有 | review_id | 5 |使用哪里；使用连接缓冲区(块嵌套循环)

我不知道这里有什么问题，它需要使用文件排序并创建一个临时表？

以下是一些分析结果:

打开表格 140 µs

初始化 139 µs

系统锁定 34 µs

优化 21 µs

统计 106 µs

准备 146 µs

创建 Tmp 表 13.6 ms

排序结果 27 µs

执行 11 µs

发送数据 11.6 ms

创建排序索引 1.4 毫秒

结束 89 µs

删除 Tmp 表 8.9 毫秒

结束 34 µs

查询结束 25 µs

结束表格 66 µs

释放项目 41 µs

删除 Tmp 表 1.4 ms

释放项目 46 µs

删除 Tmp 表 1.2 ms

释放项目 203 µs

清理 55 µs

从解释和分析结果来看，很明显创建临时表是为了产生结果。如何优化此查询以获得相似的结果和更好的性能并避免创建临时表？

帮助将不胜感激。提前致谢。

编辑

创建表

CREATE TABLE `products` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) NOT NULL,
 `description` varchar(100) NOT NULL,
 `items` int(11) NOT NULL,
 `price` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB

CREATE TABLE `reviews` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(30) NOT NULL,
 `product_id` int(11) NOT NULL,
 `review` text NOT NULL,
 `time` datetime NOT NULL,
 `ratings` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `product_id` (`product_id`),
 KEY `username` (`username`)
) ENGINE=InnoDB

CREATE TABLE `replies` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `review_id` int(11) NOT NULL,
 `username` varchar(30) NOT NULL,
 `reply` text NOT NULL,
 `time` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `review_id` (`review_id`)
) ENGINE=InnoDB

CREATE TABLE `review_images` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `review_id` int(11) NOT NULL,
 `image_title` text NOT NULL,
 `image_location` text NOT NULL,
 PRIMARY KEY (`id`),
 KEY `review_id` (`review_id`)
) ENGINE=InnoDB

编辑 :

我简化了上面的查询，现在它不会创建临时表。 @Bill Karwin 提到的唯一原因是我正在使用 GROUP BY在连接中的第二个表上。

简化查询:

SELECT reviews. * ,
GROUP_CONCAT( DISTINCT CONCAT_WS( '~', replies.reply, replies.time ) ) AS Replies,
GROUP_CONCAT( DISTINCT CONCAT_WS( '~', review_images.image_title, review_images.image_location ) ) AS ReviewImages
FROM reviews
LEFT JOIN replies ON reviews.id = replies.review_id
LEFT JOIN review_images ON reviews.id = review_images.review_id
WHERE reviews.product_id = 1
GROUP BY reviews.id

现在 问题 我面临的是:

因为我使用的是 GROUP_CONCAT，所以它可以保存的数据有一个限制，它在变量 GROUP_CONCAT_MAX_LEN 中。 ，所以当我连接用户给出的回复时，它可能会持续很长时间并且可能超过定义的内存。我知道我可以改变 GROUP_CONCAT_MAX_LEN 的值对于当前 session ，但仍然存在一个限制，即在某个时间点，查询可能会失败或无法获取完整的结果。

如何修改我的查询以便不使用 GROUP_CONCAT并且仍然得到预期的结果。

可能的解决方案:

简单地使用LEFT JOINS，它为最后一列中的每个新结果创建重复的行，这使得在 php 中难以遍历。 ?有什么建议？

我看到这个问题没有得到 SO 成员的足够回应。但从上周到上周，我一直在寻找解决方案和概念。仍然没有运气。希望你们中的一些专业人士可以帮助我。提前致谢。

Answer 1

当您的 GROUP BY 子句引用来自两个不同表的列时，您无法避免创建临时表。

避免此查询中出现临时表的唯一方法是将数据的非规范化版本存储在一个表中，并为您分组的两列建立索引。

另一种可以简化并以更易于在 PHP 中使用的格式获取结果的方法是执行多个查询，而不使用 GROUP BY。

首先得到评论。示例在 PHP 和 PDO 中，但该原则适用于任何语言。

$review_stmt = $pdo->query("
    SELECT reviews.*,
    FROM reviews
    WHERE reviews.product_id = 1");

将它们排列在以 review_id 为键的关联数组中。

$reviews = array();
while ($row => $review_stmt->fetch(PDO::FETCH_ASSOC)) {
    $reviews[$row['d']] = $row;
}

然后获取回复并使用键“回复”将它们附加到数组中。使用 INNER JOIN 而不是 LEFT JOIN，因为如果没有回复也没关系。

$reply_stmt = $pdo->query("
    SELECT replies.*
    FROM reviews
    INNER JOIN replies ON reviews.id = replies.review_id
    WHERE reviews.product_id = 1");
while ($row = $reply_stmt->fetch(PDO::FETCH_ASSOC)) {
    $reviews[$row['review_id']]['replies'][] = $row; 
}

对 review_images 做同样的事情。

$reply_stmt = $pdo->query("
    SELECT review_images.*
    FROM reviews
    INNER JOIN review_images ON reviews.id = review_images.review_id
    WHERE reviews.product_id = 1");
while ($row = $reply_stmt->fetch(PDO::FETCH_ASSOC)) {
    $reviews[$row['review_id']]['review_images'][] = $row; 
}

最终结果是一个评论数组，其中包含的元素分别是相关回复和图像的嵌套数组。

运行更简单的查询的效率可以弥补运行三个查询的额外工作。另外，您不必为 explode() 编写代码组连接的字符串。