MySQL 5.6 Indexierung über DateTime

[ChrisLeh]

Hallo Community,
ich hab ein Index Problem und habe eigentlich immer gedacht ich verstehe es, aber jetzt geht es irgendwie nicht weiter.
Datenbank: MySQL 5.6.27 64bit Version
Betriebssystem: Windows Server 2012 R2
Problem:
- Anzeige der Temperatur für einzelne Geräte in einem bestimmten Zeitverlauf für eine bestimmte Probe
- In einem bestimmten Zeitraum

Lösungsansatz:
DB Tables:
- Table: tracking_history -> Speichern der einzelnen Temperatur sensoren einträgen
- Table movement_history -> Speichern der einzelnen Bewegungsdaten

Umsetzung

CREATE TABLE IF NOT EXISTS `movement_history` (
  `movement_id` INT(11) NOT NULL AUTO_INCREMENT     COMMENT 'Primärschlüssel, fortlaufende Nummer' ,
  `id_sample` INT(11) NULL DEFAULT NULL                 COMMENT 'zugeordnente Probe',
  `id_device_move` INT(11) NULL DEFAULT NULL                 COMMENT 'zugeordnentes Device',
  `movement_in` DATETIME NOT NULL                             COMMENT 'Messzeitpunk',
  `movement_out` DATETIME NULL DEFAULT NULL                    COMMENT 'Messzeitpunk'
  PRIMARY KEY (`sample_movement_id`) ,
  KEY `tracking` (`id_device_move`,`movement_in`,`movement_out`),
  KEY `sample` (`id_sample`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = 'Verlaufsdaten der Proben';

CREATE TABLE IF NOT EXISTS `tracking_history` (
  `tracking_history_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel, fortlaufende Nummer' ,
  `id_device` INT(11) NULL DEFAULT NULL COMMENT 'zugeordnentes Device',
  `id_sample` INT(11) NULL DEFAULT NULL                 COMMENT 'zugeordnente Probe',
  `measure_time` DATETIME NOT NULL COMMENT 'Messzeitpunk' ,
  `temp` DECIMAL(10,4) NOT NULL COMMENT 'Wert der Messung' ,
  `report_typ` VARCHAR(3) NOT NULL COMMENT 'Typ des Trackings',
  `sensor` VARCHAR(3) NOT NULL COMMENT 'Welcher Sensor gibt die Meldung',
  `direction` VARCHAR(3) Default NULL COMMENT 'Richtung der Meldung',
  `sensorstate` TINYINT(1) UNSIGNED DEFAULT 0 COMMENT 'Wie verhält sich der Sensor',
  PRIMARY KEY (`tracking_history_id`) ,
  UNIQUE KEY `record` (`id_device`,`id_sample`,`measure_time`),
  UNIQUE KEY `record_tmp` (`id_device`,`measure_time`,`sensor`,`report_typ`,`id_sample`),
  KEY `temp` (`id_device`,`measure_time`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = 'Verlaufsdaten der Temperatur';

So wie oben gezeigt die zwei Tabellen und hier mein Select

SELECT
  *
FROM
    tracking_history th
    inner join sample_movement_history smh on
    smh.id_device_move = th.id_device
       AND th.measure_time BETWEEN smh.movement_in AND IFNULL(smh.movement_out, NOW())  
WHERE
    smh.id_sample = 48341
and th.measure_time between now() - interval 1 year and now()

Explain

{
   "query_block" : {
       "nested_loop" : [
           {
               "table" : {
                   "access_type" : "ref",
                   "filtered" : 100,
                   "index_condition" : "(`clctl_labor`.`smh`.`id_device_move` is not null)",
                   "key" : "sample",
                   "key_length" : "5",
                   "possible_keys" : [
                       "tracking",
                       "sample"
                   ],
                   "ref" : [
                       "const"
                   ],
                   "rows" : 13,
                   "table_name" : "smh",
                   "used_key_parts" : [
                       "id_sample"
                   ]
               }
           },
           {
               "table" : {
                   "access_type" : "ref",
                   "filtered" : 100,
                   "index_condition" : "(`clctl_labor`.`th`.`measure_time` between `clctl_labor`.`smh`.`movement_in` and ifnull(`clctl_labor`.`smh`.`movement_out`,now()))",
                   "key" : "temp",
                   "key_length" : "5",
                   "possible_keys" : [
                       "record",
                       "record_tmp",
                       "fk_temperatur_history_device1",
                       "temp"
                   ],
                   "ref" : [
                       "clctl_labor.smh.id_device_move"
                   ],
                   "rows" : 10154,
                   "table_name" : "th",
                   "used_key_parts" : [
                       "id_device"
                   ]
               }
           }
       ],
       "select_id" : 1
   }
}

So und jetzt endich mein Problem. Das ganze soll natürlich nach Datum (measure_time) sortiert werden und die Ausgabe soll kein Limit haben.
Das order by wollte ich mir durch den Index ersparen, hab aber festgestellt das es hier auf unterschiedlichen Installation nicht funktioniert.
Und ich hab das gefühl das die Abfrage einfach zu lange brauche und es sicherlich an dem Schlüssel liegt.
Jeder Tip hilft mir weiter, also schreibt einfach was ihr dazu denkt.

MFG
Chris

 

[Fragenfrager]

Ein paar grundsätzliche Dinge von mir:
1) Das "Order by" wird bei SQL als letztes abgearbeitet, arbeitet also mit den schon abgerufenen Daten. Hier helfen RAM und CPU.

2) Man sollte bei "SELECT" grundsätzlich nur die Spalten abfragen, die man benötigt. "SELECT *" ist also nicht so gut, zumal Du in Deinen Resultsets unter Umständen Probleme bekommst, wenn Du gezwungen bist, Spalten hinzuzufügen oder zu entfernen. Je weniger Du abfragst, desto besser die Perfomance.

3) Wie stehen die Chancen, dass Deine IDs einen negativen Wert haben? Ehr schlecht, oder? Dann sollte auch nicht INT verwendet werden, sondern unsigned Integer. Ansonsten verschenkst Du die Hälfte (nämlich den negativen Teil) des Integerbereichs. Gerade bei großen Datenbanken ein beliebtest Problem.
Zudem ist INT(11) relativ witzlos. Du veränderst damit nur die Stellen die angezeigt werden, nicht den reservierten Speicherplatz. Da liegt der Unterschied zu varchar(11), das tatsächlich nur 11 Zeichen speichert.
TINYINT = 1 byte (8 bit)
SMALLINT = 2 bytes (16 bit)
MEDIUMINT = 3 bytes (24 bit)
INT = 4 bytes (32 bit)
BIGINT = 8 bytes (64 bit)

4) Ich weiss nicht genau, was Du abfragen willst, aber ggf. kannst Du das "IFNULL(smh.movement_out, NOW()) " in die WHERE-Klausel einarbeiten. Der Vorteil ist, dass WHERE vor SELECT abgearbeitet wird und Du somit weniger Datensätze zum verarbeiten hast.

 

[ChrisLeh]

Hallo Fragenfrager,
danke für deine Hinweise.
zu 1.) Ich wollte mir das Order by auch eigentlich sparen und habe gehofft er greift auf einen schon sortiertet Index Schlüssel zurück und gibt mir das Result sortiert zurück. Aber hier ist das Problem, das der Index sich immer anhand des Inhaltes der Table ändert. <7 MIO Index Temp => Ausgabe wird sortiert >7 MIO Index record_tmp => keine Sortierung und ich brauch ein Order By was zusätzlich Zeit kostet
zu 3.) Okay das kann ich ja noch anpassen
zu 4.) Ich habe die Abfrage jetzt so umgebaut und das Result ist schon mal schneller geworden

          Select
               `th`.`measure_time` AS `measure_time`,
               `th`.`temp` AS `temp`
           from
               sample_movement_history smh
               left join tracking_history th on smh.id_device_move = th.id_device
           where
               smh.id_sample = 48341
               and th.measure_time between smh.movement_in and ifnull(smh.movement_out,now())
               and th.measure_time between now() - interval 1 year and now()
           order by th.measure_time asc

 

[Fragenfrager]

Als Nachtrag zu 4):
SQL-Statements werden in folgender Reihenfolge abgearbeitet:
1) FROM
2) WHERE
3) GROUP BY
4) HAVING
5) SELECT
6) ORDER BY

Je mehr Daten Du also "oben" rausfiltern kannst, desto weniger haben die nachfolgenden zu tun.