相同查詢的不同執行

在遷移計劃中，我將使用只有 MyISAM 表的 mysql 5.5 數據庫，並將其轉換為帶有 InnoDB 表的 mysql 5.7。

創建表語句在這裡：http ://sqlfiddle.com/#!9/bb815d 。

除了引擎之外，它們完全相同：

CREATE TABLE `pageview` (
 `Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `VisitId` int(11) DEFAULT '0',
 `PageId` int(11) DEFAULT '0',
 `ViewDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`Id`),
 KEY `VisitId` (`VisitId`),
 KEY `PageId` (`PageId`),
 KEY `All` (`PageId`,`ViewDate`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3245799556 DEFAULT CHARSET=utf8;

CREATE TABLE `page` (
 `Id` int(11) NOT NULL AUTO_INCREMENT,
 `SiteId` int(11) DEFAULT '0',
 `Url` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 `Name` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 `Rating` tinyint(1) NOT NULL DEFAULT '0',
 `RawUrl` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 PRIMARY KEY (`Id`),
 KEY `Url` (`Url`,`SiteId`),
 KEY `SiteId` (`SiteId`)
) ENGINE=InnoDB AUTO_INCREMENT=105689724 DEFAULT CHARSET=utf8;

執行此查詢時，結果非常不同，使用 InnoDB 的 5.7 中的性能比使用 MyISAM 的 5.5 中的性能要差得多：

SELECT VisitId,SUM(page.Rating) AS Rating
FROM pageview
INNER JOIN page ON page.Id = pageview.PageId
GROUP BY VisitId
ORDER BY VisitId DESC
LIMIT 500000;

5.5 MyISAM 的解釋​​是這樣的：

5.7 InnoDB 的解釋是這樣的：

我不知道我可以做些什麼來提高 5.7 中的性能，或者為什麼性能下降這麼多。

我注意到的第一件事是表格的順序是向後的，但對於page表格來說，它也沒有像 5.5 中那樣使用PRIMARY索引，我認為這是關鍵的區別。

你能建議改善這種情況的方法嗎？

更新

在遵循mendosi 的建議後，我仍然沒有得到任何改善，這裡是更新的SHOW CREATE TABLE：

CREATE TABLE `pageview` (
 `Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `VisitId` int(11) DEFAULT '0',
 `PageId` int(11) DEFAULT '0',
 `ViewDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`Id`),
 KEY `PageId` (`PageId`),
 KEY `All` (`PageId`,`ViewDate`) USING BTREE,
 KEY `VisitId` (`VisitId`,`PageId`)
) ENGINE=InnoDB AUTO_INCREMENT=3268418928 DEFAULT CHARSET=utf8

並解釋：

很難說為什麼 MySQL Optimizer 為 5.7 選擇了不同的連接順序，而沒有看到查詢的優化器跟踪。但是，您可以通過使用 STRAIGHT_JOIN 而不是 INNER JOIN 來強制使用與 5.5 相同的連接順序。這將告訴 MySQL 按列出的順序處理表。

值得嘗試使索引VisitId覆蓋此查詢。如果您像這樣更改索引定義：

KEY VisitId (VisitId, PageId)

那麼pageview桌子上就會有一個更好的候選索引。

引用自：https://dba.stackexchange.com/questions/153590