MySQL - 优化由 ORDER BY 引起的 "Using temporary"

Question

这里是查询

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 倍多与没有 order by 的版本相比更慢)。

据我了解，using temporary 是由于 order by 列不在第一个表中造成的。

有没有办法优化这个查询，或者我应该将时间戳复制到 ProductReviews 表中吗？

更新表格:

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

Answer 1

试试这个:

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 的注释。