mysql - MySQL 查看聚合查询的性能问题

问题描述

我正在使用 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`;

在使用大约 20K 行进行测试时，运行SELECT query与SELECT * FROM VIEW. 选择查询显示了一个优化的执行计划，只有 1 次全表扫描，而对于视图，有 2 次全表扫描。

查询统计（由 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 版测试，同样的问题发生

标签： mysqlquery-optimizationsql-viewmysql-5.6

在这种情况下，MySQL 必须对视图使用 TEMPTABLE 算法（聚合函数）。这可能是造成差异的原因。

您可以参考https://dev.mysql.com/doc/refman/5.6/en/view-algorithms.html了解更多详情。

如果不能使用 MERGE 算法，则必须使用临时表。如果视图包含以下任何构造，则不能使用 MERGE：

聚合函数（SUM()、MIN()、MAX()、COUNT() 等）

清楚的

通过...分组

拥有

限制

UNION 或 UNION ALL

选择列表中的子查询

分配给用户变量

仅指文字值（在这种情况下，没有基础表）

mysql - MySQL 查看聚合查询的性能问题

问题描述

解决方案

推荐阅读