我正在使用mysql版本5.6.47。 我有下表用于学生评分:
CREATE TABLE `studentmarks` (
`ID` int(11) NOT NULL AUTO_INCREMENT,`StudentID` int(11) NOT NULL,`subjectName` varchar(255) DEFAULT NULL,`MARKS` int(11) NOT NULL,PRIMARY KEY (`ID`),KEY `idx_studentmarks_StudentID` (`StudentID`)
);
并在桌子上创建一个视图:
CREATE OR REPLACE VIEW `vw_student_marks` AS
SELECT
`s1`.`StudentID` AS `StudentID`,`s1`.`subjectName` AS `subjectName`,`s1`.`MARKS` AS `marks`,(SELECT
SUM(`s2`.`MARKS`)
FROM
`studentmarks` `s2`
WHERE
(`s2`.`StudentID` = `s1`.`StudentID`)) AS `totalMarks`
FROM
`studentmarks` `s1`;
在测试约2万行时,运行SELECT query
与SELECT * FROM VIEW
的性能存在明显差异。 select查询显示仅执行一次全表扫描的优化执行计划,而要查看则执行两次全表扫描。
查询状态(由MySQL Workbench测量):
选择查询
Timing: 0:00:0.07677120 (as measured by the server)
Rows Examined: 108285
从查看查询中选择:
Timing: 0:00:1.6082441 (as measured by the server)
Rows Examined: 2985730
这种性能差异背后的原因是什么?
查询执行计划:https://i.stack.imgur.com/noOxI.jpg
更新:我使用MySQL 8.0.19版进行了测试,发生了同样的问题