Datenbank-Abfrage langsam (Datenbank optimieren)

[CountDracula]

Hi!

Ich suche nach ein bisschen Datenbanknachhilfe. Derzeit speichere ich einige Sensorenwerte in einer MySQL-Datenbank und durch die vielen Einträge (>3 Mio.) wird sie mit der Zeit langsamer. Lässt sich da noch etwas an meiner (schlampigen) Speicherung optimieren?

Aufbau:

• Mehrere Messstationen
• Messwerte werden jede Minute gespeichert
• Messstationen verfügen über unterschiedliche Sensoren (Zum Beispiel kann nicht jede Station den Luftdruck messen)

Tabellen-Aufbau:

Code: Alles auswählen

MariaDB [wohnung]> DESCRIBE klima;
+------------------+---------------------+------+-----+-------------------+----------------+
| Field            | Type                | Null | Key | Default           | Extra          |
+------------------+---------------------+------+-----+-------------------+----------------+
| Eintrag_ID       | bigint(20) unsigned | NO   | PRI | NULL              | auto_increment |
| Zeitpunkt        | datetime            | NO   |     | CURRENT_TIMESTAMP |                |
| Sensor           | varchar(50)         | YES  |     | NULL              |                |
| Temperatur       | decimal(7,4)        | YES  |     | NULL              |                |
| Luftfeuchtigkeit | decimal(5,2)        | YES  |     | NULL              |                |
| Status           | varchar(50)         | YES  |     | NULL              |                |
| WLAN_Signal      | decimal(6,2)        | YES  |     | NULL              |                |
| Luftdruck        | decimal(7,3)        | YES  |     | NULL              |                |
| Taupunkt         | decimal(4,2)        | YES  |     | NULL              |                |
+------------------+---------------------+------+-----+-------------------+----------------+
9 rows in set (0.00 sec)

Beispiel-Inhalt:

Code: Alles auswählen

MariaDB [wohnung]> SELECT * FROM klima ORDER BY Eintrag_ID DESC LIMIT 5;
+------------+---------------------+----------------------+------------+------------------+--------+-------------+-----------+----------+
| Eintrag_ID | Zeitpunkt           | Sensor               | Temperatur | Luftfeuchtigkeit | Status | WLAN_Signal | Luftdruck | Taupunkt |
+------------+---------------------+----------------------+------------+------------------+--------+-------------+-----------+----------+
|    3399161 | 2017-11-07 14:31:12 | Wohnzimmer_DHT22     |    18.7000 |            25.30 | ok     |      -48.00 |      NULL |     NULL |
|    3399160 | 2017-11-07 14:31:12 | Wohnzimmer_BME280    |    19.2000 |            48.93 | ok     |      -48.00 |  1023.746 |     8.22 |
|    3399159 | 2017-11-07 14:30:47 | Küche                |    16.9000 |            58.50 | ok     |      -65.00 |      NULL |     NULL |
|    3399158 | 2017-11-07 14:30:42 | dachboden            |    14.5000 |            61.70 | ok     |        NULL |      NULL |     NULL |
|    3399157 | 2017-11-07 14:30:21 | schlafzimmer_ds18b20 |    16.3125 |             NULL | ok     |        NULL |      NULL |     NULL |
+------------+---------------------+----------------------+------------+------------------+--------+-------------+-----------+----------+
5 rows in set (0.00 sec)

Beispiel-Abfrage:

Code: Alles auswählen

MariaDB [wohnung]> SELECT DISTINCT MIN(Temperatur) FROM klima WHERE Sensor="Wohnzimmer_BME280" AND Status="ok";
+-----------------+
| MIN(Temperatur) |
+-----------------+
|         13.9300 |
+-----------------+
1 row in set (1.43 sec)

Es sind zwar nur 1,43 Sekunden bis zur Antwort, aber wenn man mehrere Sensoren-Minimumwerte abfragen möchte dauert es dann doch schon bisschen lang. Bei allen Sensoren sind es dann schon 24 Sekunden.

[rne]

Brauchst du wirklich für die Verarbeitung jedes Mal alle drei Millionen Datensätze?
Falls nicht, könnte dir bereits ein einfaches LIMIT aushelfen.
Mittel-, Maximum- und Minimumwerte über vergangene Zeiträume kannst du, falls benötigt, ja in einer separaten Tabelle cachen und in etwaige Berechnungen mit einbeziehen.
Daher sehe / vermute ich das Performance Problem bei der Datenverarbeitung, nicht bei der Speicherung.

[Lord_Carlos]

Vielleicht bei sensor und status ein index hinzufuegen?

[CountDracula]

Danke euch beiden!

Ich konnte mein Problem lösen. Ein passender Index und eine Anpassung in meinem Skript konnte meine Ausführungszeit von 60 Sekunden auf 0 Sekunden beschleunigen.

Beispiel MAX-Value mit INDEX (Verbesserung von 1.53 sec auf 1.11 sec. | Reduzierung: 2989925 rows auf 411584 rows):

Code: Alles auswählen

MariaDB [wohnung]> SHOW INDEXES FROM klima;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| klima |          0 | PRIMARY    |            1 | Eintrag_ID  | A         |     2989925 |     NULL | NULL   |      | BTREE      |         |               |
| klima |          0 | Eintrag_ID |            1 | Eintrag_ID  | A         |     2989925 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

MariaDB [wohnung]> EXPLAIN SELECT MAX(Temperatur) FROM klima WHERE Sensor="Wohnzimmer_BME280" AND Status="ok";
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | klima | ALL  | NULL          | NULL | NULL    | NULL | 2989925 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

MariaDB [wohnung]> SELECT MAX(Temperatur) FROM klima WHERE Sensor="Wohnzimmer_BME280" AND Status="ok";
+-----------------+
| MAX(Temperatur) |
+-----------------+
|         29.2000 |
+-----------------+
1 row in set (1.53 sec)

MariaDB [wohnung]> ALTER TABLE klima ADD INDEX sensor_status_index(Sensor,Status);
Query OK, 0 rows affected (10.46 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [wohnung]> SHOW INDEXES FROM klima;
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| klima |          0 | PRIMARY             |            1 | Eintrag_ID  | A         |     2989932 |     NULL | NULL   |      | BTREE      |         |               |
| klima |          0 | Eintrag_ID          |            1 | Eintrag_ID  | A         |     2989932 |     NULL | NULL   |      | BTREE      |         |               |
| klima |          1 | sensor_status_index |            1 | Sensor      | A         |        8422 |     NULL | NULL   | YES  | BTREE      |         |               |
| klima |          1 | sensor_status_index |            2 | Status      | A         |        9835 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

MariaDB [wohnung]> EXPLAIN SELECT MAX(Temperatur) FROM klima WHERE Sensor="Wohnzimmer_BME280" AND Status="ok";
+------+-------------+-------+------+---------------------+---------------------+---------+-------------+--------+-----------------------+
| id   | select_type | table | type | possible_keys       | key                 | key_len | ref         | rows   | Extra                 |
+------+-------------+-------+------+---------------------+---------------------+---------+-------------+--------+-----------------------+
|    1 | SIMPLE      | klima | ref  | sensor_status_index | sensor_status_index | 106     | const,const | 411584 | Using index condition |
+------+-------------+-------+------+---------------------+---------------------+---------+-------------+--------+-----------------------+
1 row in set (0.00 sec)

MariaDB [wohnung]> SELECT MAX(Temperatur) FROM klima WHERE Sensor="Wohnzimmer_BME280" AND Status="ok";
+-----------------+
| MAX(Temperatur) |
+-----------------+
|         29.2000 |
+-----------------+
1 row in set (1.11 sec)

MariaDB [wohnung]> 

Weiteres Beispiel: Man sollte nicht nach Zeitpunkt(datetime) bei so vielen Einträgen sortieren. Besser man sortiert bei meinem Setup nach Eintrag_ID. Oder man legt einen Index auf Zeitpunkt(datetime).

Code: Alles auswählen

MariaDB [wohnung]> EXPLAIN SELECT Temperatur FROM klima WHERE Status='ok' AND Sensor='Wohnzimmer_BME280' ORDER BY Zeitpunkt DESC LIMIT 1;
+------+-------------+-------+------+---------------------+---------------------+---------+-------------+--------+----------------------------------------------------+
| id   | select_type | table | type | possible_keys       | key                 | key_len | ref         | rows   | Extra                                              |
+------+-------------+-------+------+---------------------+---------------------+---------+-------------+--------+----------------------------------------------------+
|    1 | SIMPLE      | klima | ref  | sensor_status_index | sensor_status_index | 106     | const,const | 397224 | Using index condition; Using where; Using filesort |
+------+-------------+-------+------+---------------------+---------------------+---------+-------------+--------+----------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [wohnung]> SELECT Temperatur FROM klima WHERE Status='ok' AND Sensor='Wohnzimmer_BME280' ORDER BY Zeitpunkt DESC LIMIT 1;
+------------+
| Temperatur |
+------------+
|    20.8000 |
+------------+
1 row in set (1.43 sec)

MariaDB [wohnung]> EXPLAIN SELECT Temperatur FROM klima WHERE Status='ok' AND Sensor='Wohnzimmer_BME280' ORDER BY Eintrag_ID DESC LIMIT 1;
+------+-------------+-------+------+---------------------+---------------------+---------+-------------+--------+-------------+
| id   | select_type | table | type | possible_keys       | key                 | key_len | ref         | rows   | Extra       |
+------+-------------+-------+------+---------------------+---------------------+---------+-------------+--------+-------------+
|    1 | SIMPLE      | klima | ref  | sensor_status_index | sensor_status_index | 106     | const,const | 397224 | Using where |
+------+-------------+-------+------+---------------------+---------------------+---------+-------------+--------+-------------+
1 row in set (0.01 sec)

MariaDB [wohnung]> SELECT Temperatur FROM klima WHERE Status='ok' AND Sensor='Wohnzimmer_BME280' ORDER BY Eintrag_ID DESC LIMIT 1;
+------------+
| Temperatur |
+------------+
|    20.8000 |
+------------+
1 row in set (0.00 sec)

MariaDB [wohnung]>