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

Question

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

3

以下是我在表格中使用的查询语句：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

外键 - product_id (products表中的id)

外键 - username (users表中的username)

回复 :

主键 - id

外键 - review_id (reviews表中的id)

外键 - username (users表中的username)

评论图片 :

主键 - id

外键 - review_id (reviews表中的id)

查询解释 :

1 | SIMPLE | reviews | ALL | product_id | 4 | 使用where; 使用连接缓存 (块嵌套循环)

1 | SIMPLE | replies | ref | review_id | 1 | 空

1 | SIMPLE | review_images | ALL | review_id | 5 | 使用where; 使用连接缓存 (块嵌套循环)

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

以下是一些性能分析结果:

打开表格 140 微秒

初始化 139 微秒

系统锁定 34 微秒

优化 21 微秒

统计 106 微秒

准备 146 微秒

创建临时表 13.6 毫秒

排序结果 27 微秒

执行 11 微秒

发送数据 11.6 毫秒

创建排序索引 1.4 毫秒

结束 89 微秒

删除临时表 8.9 毫秒

结束 34 微秒

查询结束 25 微秒

关闭表格 66 微秒

释放项目 41 微秒

删除临时表 1.4 毫秒

释放项目 46 微秒

删除临时表 1.2 毫秒

释放项目 203 微秒

清理中 55 微秒

从解释和分析结果可以看出，临时表被创建来生成结果。如何优化此查询以获得类似的结果和更好的性能，并避免创建临时表?

希望能得到帮助。先感谢您。

编辑

创建表格

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的值，但仍然存在限制，在某个时间点上，查询可能会失败或无法获取完整的结果。

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

可能的解决方案：

简单地使用LEFT JOINs，为最后一列的每个新结果创建重复行，并使其在php中遍历变得困难？有什么建议吗？

我看到这个问题没有得到足够的SO成员响应。但是我已经寻找解决方案并搜索概念已经持续了两周。仍旧没有运气。希望你们中的一些PRO能够帮助我。先谢谢了。

- Dangling Cruze

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Bill Karwin · Accepted Answer

如果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;
}

然后获取回复并使用键“replies”将它们附加到数组中。使用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() 组合字符串。