集計クエリでのMySQLViewのパフォーマンスの問題

debugcn 投稿 Dev

ラジェッシュグプタ

私は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 queryVsはSELECT * FROM VIEW。selectクエリは、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でテストしましたが、同じ問題が発生します

インディアンシャー

この場合、MySQLはビューにTEMPTABLEアルゴリズムを使用している必要があります（集計関数）。これが違いの理由かもしれません。

詳細については、https：//dev.mysql.com/doc/refman/5.6/en/view-algorithms.htmlを参照してください。

MERGEアルゴリズムを使用できない場合は、代わりに一時テーブルを使用する必要があります。ビューに次の構成のいずれかが含まれている場合、MERGEは使用できません。

集計関数（SUM（）、MIN（）、MAX（）、COUNT（）など）

DISTINCT

GROUP BY

持っている

制限

UNIONまたはUNIONALL

選択リストのサブクエリ

ユーザー変数への割り当て

リテラル値のみを参照します（この場合、基になるテーブルはありません）