Performance-Problem bei einer View

[tschroeer]

Hallo an alle

ich habe ein Problem mit einer MySQL-DB und einer View. Die Datenbank ist ein Archivsystem für eine Messwerterfassung (bzw. soll es werden). Ich habe die Daten des aktuellen Jahrs (von 01.01. bis 02.12.) importiert, das mach etwas über 23 Millionen Datensätze. In meiner View für eine Liveansicht möchte ich nur die Daten der letzten Stunde sehen. Leider benötigt die View weit über eine Minute, um die Daten zu zeigen.

Die View sieht folgernermaßen aus:

CREATE
    ALGORITHM = UNDEFINED
    DEFINER = `root`@`%`
    SQL SECURITY DEFINER
VIEW `vw_mp_all_60m` AS
    SELECT
        `tbl_measurementdata`.`ID` AS `ID`,
        `tbl_measurementdata`.`QueryTime` AS `QueryTime`,
        `tbl_measurementdata`.`MeasurinPoint` AS `MeasurinPoint`,
        `tbl_measurementdata`.`MeasuredValue` AS `MeasuredValue`,
        `tbl_measurementdata`.`LimitValueMin` AS `LimitValueMin`,
        `tbl_measurementdata`.`LimitValueMax` AS `LimitValueMax`,
        `tbl_measurementdata`.`SensorUnit` AS `SensorUnit`,
        `tbl_measurementdata`.`Remark` AS `Remark`,
        `tbl_measurementdata`.`SensorRemark` AS `SensorRemark`,
        CONCAT(`tbl_measurementdata`.`MeasurinPoint`,
                ' - ',
                IFNULL(`tbl_measurementdata`.`Remark`, '')) AS `MPDisplay`,
        `tbl_measurementdata`.`Info` AS `Info`,
        `tbl_info`.`Remark` AS `InfoText`,
        `tbl_measurementdata`.`MPAlarm` AS `MPAlarm`,
        `tbl_measurementdata`.`GlobalAlarm` AS `GlobalAlarm`,
        CONCAT('MIN: ',
                CONVERT( IFNULL(`tbl_measurementdata`.`LimitValueMin`,
                        'n/a') USING UTF8),
                IFNULL(`tbl_measurementdata`.`SensorUnit`, ''),
                ' | MAX: ',
                CONVERT( IFNULL(`tbl_measurementdata`.`LimitValueMax`,
                        'n/a') USING UTF8),
                IFNULL(`tbl_measurementdata`.`SensorUnit`, '')) AS `MetricLabel`,
        `tbl_department_mps`.`DepartmentID` AS `DepartmentID`
    FROM
        ((`tbl_measurementdata`
        LEFT JOIN `tbl_info` ON ((`tbl_measurementdata`.`Info` = `tbl_info`.`Vector`)))
        JOIN `tbl_department_mps` ON ((`tbl_department_mps`.`MeasurinPoint` = `tbl_measurementdata`.`MeasurinPoint`)))
    WHERE
        ((TIMESTAMPDIFF(MINUTE,
            `tbl_measurementdata`.`QueryTime`,
            NOW()) <= 60)
            AND `tbl_measurementdata`.`MeasurinPoint` IN (SELECT
                `tbl_mp_free`.`MeasurinPoint`
            FROM
                `tbl_mp_free`)
            IS FALSE)

Ist da vielleicht ein Fehler drin, ist das normal, kann ich da etwas verbessern in Sachen Performance? Ich muss dazu sagen, dass ich eigendlich nur mit dem MSSQL Server zu tun habe. Auch so große Datenmangen hatte ich noch nie.

Für eure Hilfe wäre ich sehr dankbar

 

[Walter]

Was sagt denn die Datenbank mit einem EXPLAIN dein_select ?

 

[BerndB]

Hallo,

da sind erst mal 2 Probleme zu sehen.

1) Die View benutzt keinen INDEX (siehe unten)
2) Prüfen ob überhaupt ein INDEX vorhanden ist.


Das Größte Problem ist das **WHERE** bei dir. E ist immer eine schlechte Idee eine Funktion auf ein Datenbankfeld anzuwenden. Besser ist es die im Konstanten Teil zu machen. Z.B

SELECT *  FROM mytable WHERE TIMESTAMPDIFF(myfield,.....) <= 60;

Hierbei MUSS MySQL jede Zeile lesen, die Funktion ausführen und kann dann erst den Vergleich durchführen.
Besser ist sowas wie

SELECT *  FROM mytable WHERE myfield >=  NOW() - INTERVAL  - 60 MINUTE;

Jetzt wird NOW() - ... nur einmal berechnet und MySQL kann dann über den Index suchen (Falls vorhanden).

Gruß

Bernd

 

[tschroeer]

SELECT *  FROM mytable WHERE myfield >=  NOW() - INTERVAL  - 60 MINUTE;

Jetzt wird NOW() - ... nur einmal berechnet und MySQL kann dann über den Index suchen (Falls vorhanden).

Gruß

Bernd

Hallo Bernd,

erst einmal vielen Dank für das tolle Telefonat. Ich habe die View nach obigem Schema geändert. Sie ist jetzt wirk viel schneller, sie zeigt jetzt nämlich gar nichts an [Ironie aus]. Ich denke, ich werde die Daten in zwei Tabellen pumpen, eine Archiv und eine Live und in der Live per Trigger alles löschen was älter ist wie 60 Minuten.

 

[BerndB]

Schicken sie mal bitte die neue View mit dem geänderten WHERE
MfG Bernd

 

[akretschmer]

E ist immer eine schlechte Idee eine Funktion auf ein Datenbankfeld anzuwenden.

Nein, nur bei Datenbanken, die keine funktionalen Indexe kennen. Also MySQL.