MySQL的 - 优化“使用临时”引发的ORDER BY

问题描述：

SELECT * FROM ProductReviews 
INNER JOIN RatingActions USING(RatingActionID) 
LEFT JOIN ProductRatingVotes USING(RatingActionID) 
WHERE ProductReviews.ProductID="200129" AND ProductReviewStatus="1" 
ORDER BY RatingActionTimestamp DESC;

这里是执行计划

*************************** 1. row *************************** 
      id: 1 
    select_type: SIMPLE 
     table: ProductReviews 
     type: ref 
possible_keys: FK_ProductReview_ProductID,FK_ProductReviews_RatingActionID 
      key: FK_ProductReview_ProductID 
     key_len: 4 
      ref: const 
     rows: 1 
     Extra: Using where; Using temporary; Using filesort 
*************************** 2. row *************************** 
      id: 1 
    select_type: SIMPLE 
     table: ProductRatingVotes 
     type: ref 
possible_keys: FK_ProductRatingVotes_RatingActionID 
      key: FK_ProductRatingVotes_RatingActionID 
     key_len: 4 
      ref: scart.ProductReviews.RatingActionID 
     rows: 1 
     Extra: 
*************************** 3. row *************************** 
      id: 1 
    select_type: SIMPLE 
     table: RatingActions 
     type: eq_ref 
possible_keys: PRIMARY 
      key: PRIMARY 
     key_len: 4 
      ref: scart.ProductReviews.RatingActionID 
     rows: 1 
     Extra: 
3 rows in set (0.00 sec)

虽然它的扫描只有一个排，using temporary杀死它，它需要3-4秒才能完成（一个非常繁忙的服务器;在我的本地主机上0.004秒，与没有排序的版本相比，仍然是6倍慢）。

据我所知，using temporary是由于order by列不在第一个表中。

有没有一种方法来优化此查询，或者我应该将Timestamp复制到ProductReviews表中？

UPDATE 表：

CREATE TABLE `ProductReviews` (
`ProductReviewID` int(10) unsigned NOT NULL AUTO_INCREMENT, 
`ProductID` int(10) unsigned NOT NULL DEFAULT '0', 
`RatingActionID` int(10) unsigned NOT NULL DEFAULT '0', 
`ProductReviewText` text NOT NULL, 
`ProductReviewStatus` tinyint(3) unsigned NOT NULL DEFAULT '0', 
PRIMARY KEY (`ProductReviewID`), 
KEY `FK_ProductReview_ProductID` (`ProductID`), 
KEY `FK_ProductReviews_RatingActionID` (`RatingActionID`), 
CONSTRAINT `FK_ProductReviews_RatingActionID` FOREIGN KEY (`RatingActionID`) REFERENCES `ratingactions` (`RatingActionID`) ON DELETE CASCADE ON UPDATE CASCADE, 
CONSTRAINT `FK_ProductReview_ProductID` FOREIGN KEY (`ProductID`) REFERENCES `products` (`ProductID`) ON DELETE CASCADE ON UPDATE CASCADE 
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8 

CREATE TABLE `ratingactions` (
`RatingActionID` int(10) unsigned NOT NULL AUTO_INCREMENT, 
`RatingActionTimestamp` int(10) unsigned NOT NULL DEFAULT '0', 
`CustomerID` int(10) unsigned DEFAULT NULL, 
`RatingActionIPAddress` int(10) unsigned NOT NULL DEFAULT '0', 
`RatingActionInputName` varchar(255) NOT NULL DEFAULT '', 
PRIMARY KEY (`RatingActionID`), 
KEY `FK_RatingActions_CustomerID` (`CustomerID`), 
CONSTRAINT `FK_RatingActions_CustomerID` FOREIGN KEY (`CustomerID`) REFERENCES `customers` (`CustomerID`) ON DELETE SET NULL ON UPDATE CASCADE 
) ENGINE=InnoDB AUTO_INCREMENT=142 DEFAULT CHARSET=utf8 

CREATE TABLE `ProductRatingVotes` (
`ProductRatingVoteID` int(10) unsigned NOT NULL AUTO_INCREMENT, 
`ProductID` int(10) unsigned NOT NULL DEFAULT '0', 
`RatingActionID` int(10) unsigned NOT NULL DEFAULT '0', 
`ProductRatingVoteValue` tinyint(3) unsigned NOT NULL DEFAULT '0', 
`ProductRatingVoteStatus` tinyint(3) unsigned NOT NULL DEFAULT '0', 
PRIMARY KEY (`ProductRatingVoteID`), 
KEY `FK_ProductRatingVotes_ProductID` (`ProductID`), 
KEY `FK_ProductRatingVotes_RatingActionID` (`RatingActionID`), 
CONSTRAINT `FK_ProductRatingVotes_ProductID` FOREIGN KEY (`ProductID`) REFERENCES 
`products` (`ProductID`) ON DELETE CASCADE ON UPDATE CASCADE, 
CONSTRAINT `FK_ProductRatingVotes_RatingActionID` FOREIGN KEY 
(`RatingActionID`) REFERENCES `ratingactions` (`RatingActionID`) ON 
DELETE CASCADE ON UPDATE CASCADE) 
ENGINE=InnoDB AUTO_INCREMENT=142 
DEFAULT CHARSET=utf8

你确定你需要所有列作为结果？ – newtover

将'*'改为一个整数列 - 查询速度稍快，但执行计划没有改变 –

顺便说一下，根据执行计划，您的命令由IS在第一个表中执行。所以你说，'SELECT RatingActions.RatingActionID FROM ...'在生产中几乎与'SELECT * ...'同时存在，不是吗？ – newtover

答

看来，其中一个表包含文本或BLOB字段和ORDER BY被迫使用磁盘排序。一种解决方案可能是在没有子查询中的字段的情况下进行排序，并再次与其余列进行连接。

true，有一个文本列，ProductReviewText，但将*更改为RatingActionID不影响执行计划 –

答

试试这个：

SELECT /*STRAIGHT_JOIN*/ ProductReviews.ProductID, RatingActionTimestamp 
FROM ratingactions 
join ProductReviews USING(RatingActionID) 
LEFT JOIN ProductRatingVotes USING(RatingActionID) 
WHERE ProductReviews.ProductID=200129 AND ProductReviewStatus=1 
order by RatingActionTimestamp desc;

这应该消除了使用临时使用文件排序额外的步骤。如果没有，请尝试取消注释STRAIGHT_JOIN。

没有STRAIGHT_JOIN - 执行计划相同。使用STRAIGHT_JOIN - 删除临时文件，但整个RatingActions表正在被扫描（类型：ALL） –

答

与所有其他不起作用的明显的东西，我会然后提供以下...将产品评论移动到第一个位置作为一个select/from本身...然后应用连接

SELECT STRAIGHT_JOIN 
     PR.ProductID, 
     RA.RatingActionTimestamp 
    FROM 
     (select PR1.ProductID, 
       PR1.RatingActionID 
      FROM ProductReviews PR1 
      WHERE 
       PR1.ProductID = 200129 
      AND PR1.ProductReviewStatus = 1) PR 
     JOIN rating actions RA 
      on PR.RatingActionID = RA.RatingActionID 
     LEFT JOIN ProductRatingVotes PRV 
      on PR.RatingActionID = PRV.RatingActionID 
    order by 
     RA.RatingActionTimestamp desc;

这样，它应该与您的预期，加盟评级行动。产品条用户，让那些有资格的条目有问题的独特产品的单排造成的内部查询开始，然后继续参加了到评级行动的评级和评级表...

MySQL的 - 优化“使用临时”引发的ORDER BY

相关推荐