MySql Beschleunigen

O

Obiast

Neuer Benutzer
Beiträge
4
Hallo zusammen

Ich habe ein Problem mit meiner Datenbank. Habe Grundkenntnisse, bin aber nicht SQL Profi.

Die Datenbank hat ein bisschen über 3000000 Datenpunkte. Anzahl steigend.
Die Daten hole ich einzeln nacheinander ab und zeige diese dann auf der Homepage an.

Nun hat meine erste SQL Query bereist 15 Sekunden um die Daten abzufragen. Diese ist noch nicht so komplex wie die anderen.

Die Werte

SELECT SUM(messwertEnergie) as MesswertHeute FROM monitoring.messung1 WHERE zaehlerId = X AND raumId = X AND (tarifvarianteId = X OR tarifvarianteId = X OR tarifvarianteId = X) AND Date(messzeitpunkt) BETWEEN (CURRENT_DATE()) - 0 AND CURRENT_DATE;

Wie ist das Vorgehen um eine Datenbank zu optimieren. Muss die Query angepasst werden oder habe ich andere Optimierungsmöglichkeiten?

Hoffe es kann mir jemand helfen.

Gruss
 
Werbung:
dein Date(messzeitpunkt) BETWEEN ... z.B. könnte von einem funktionalen Index profitieren - würde MySQL das können. Kann es nicht. Deine Abfrage dürfte daher IMMER in einem FullTable-Scan enden. Die Laufzeit nimmt also linear zu.
 
um das mit dem funktionalen Index, Explain & einer besseren DB mal sehr einfach zu zeigen:

Code: 
postgres=# create table obiast(ts timestamp);
CREATE TABLE
postgres=# create index idx_date on obiast (date_trunc('date',ts));
CREATE INDEX
postgres=# 
postgres=# 
postgres=# explain select * from obiast where date_trunc('date',ts) between current_date and current_date + 3;
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on obiast  (cost=4.28..15.11 rows=11 width=8)
   Recheck Cond: ((date_trunc('date'::text, ts) >= CURRENT_DATE) AND (date_trunc('date'::text, ts) <= (CURRENT_DATE + 3)))
   ->  Bitmap Index Scan on idx_date  (cost=0.00..4.27 rows=11 width=0)
         Index Cond: ((date_trunc('date'::text, ts) >= CURRENT_DATE) AND (date_trunc('date'::text, ts) <= (CURRENT_DATE + 3)))
(4 rows)

postgres=#
 
Was sagt "EXPLAIN sqlstatement"?
Was für ein Datentyp ist messzeitpunkt?
Welche Indexe gibt es auf dieser Tabelle?
 
Danke für deine rasche Antwort:

Abfrageplan
Ich habe folgende Meldungen erhalten:

id1
select_typesimple
tablemessungabrechId1

partitionsNull
typeindex_merge
possible_keys'fk_messungAbrechid0001_zaehler1_idx,fk_messungAbrechid0001_liegenschaft1_idx,fk_messungAbrechid0001_tarifvariante1_idx'
keyfk_messungAbrechid0001_zaehler1_idx,fk_messungAbrechid0001_liegenschaft1_idx
key_len4,4
ref
rows127103
filtred100.00
ExtraUsing intersect(fk_messungAbrechid0001_zaehler1_idx,fk_messungAbrechid0001_liegenschaft1_idx); Using where

fehlende Indexe setzten
Können diese nun über diesen Abfrageplan bestimmt werden? Oder wie definiere ich nun, welche Index ich setzten muss?

bessere DB nutzten
Aktuell nutze ich MySql Commnity Server- was empfiehlst du?

Wünsche dir einen schönen Abend.
 
um das mit dem funktionalen Index, Explain & einer besseren DB mal sehr einfach zu zeigen:

Code: 
postgres=# create table obiast(ts timestamp);
CREATE TABLE
postgres=# create index idx_date on obiast (date_trunc('date',ts));
CREATE INDEX
postgres=#
postgres=#
postgres=# explain select * from obiast where date_trunc('date',ts) between current_date and current_date + 3;
                                                          QUERY PLAN                                                          
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on obiast  (cost=4.28..15.11 rows=11 width=8)
   Recheck Cond: ((date_trunc('date'::text, ts) >= CURRENT_DATE) AND (date_trunc('date'::text, ts) <= (CURRENT_DATE + 3)))
   ->  Bitmap Index Scan on idx_date  (cost=0.00..4.27 rows=11 width=0)
         Index Cond: ((date_trunc('date'::text, ts) >= CURRENT_DATE) AND (date_trunc('date'::text, ts) <= (CURRENT_DATE + 3)))
(4 rows)

postgres=#
 
Obiast schrieb:
fehlende Indexe setzten
Können diese nun über diesen Abfrageplan bestimmt werden? Oder wie definiere ich nun, welche Index ich setzten muss?

bessere DB nutzten
Aktuell nutze ich MySql Commnity Server- was empfiehlst du?
Zum Vergrößern anklicken....

ich nutze kein MySQL, daher ist es für mich schwer, dieses gruslige Explain zu interpretieren. Was ich nutze steht in meinem gezeigten Code.
 
messungid = BIGINT (PK, AI)
messwertEnergie = Decimal(11,2)
zaehlerId = Int
liegenschaftId = int
tariefvarianteId = int
messzeitpunkt = Datetime
 
Obiast schrieb:
Date(messzeitpunkt) BETWEEN (CURRENT_DATE()) - 0 AND CURRENT_DATE;
Zum Vergrößern anklicken....
das kannst Du optimieren, wenn
Obiast schrieb:
messzeitpunkt = Datetime
Zum Vergrößern anklicken....
das stimmt.

Dann brauchst du statt date(messzeitpunkt) nur messzeitpunkt zu verwenden.
Die Auswirkung ergibt sich dann nur, falls auf messzeitpunkt ein Index liegt.
Was
Obiast schrieb:
(CURRENT_DATE()) - 0 AND CURRENT_DATE
Zum Vergrößern anklicken....
ergeben soll, ist mir allerdings grad nicht klar.
Tagesdatum- 0 and Tagesdatum
Wenn Du einen echten Offset von einem (nicht 0) Tag passend zu Deinem Bedarf einsetzt, ergibt es vielleicht mehr Sinn, gerade dann, wenn der Messzeitpunkt ein Datetime ist und die Werte über einen Tag streuen. Vielleicht hast Du das halbrichtig aus dem Netz kopiert oder so.
 
Das mit dem Datum checke ich auch nicht, was macht den CURRENT_DATE() - 0 und gibt es Datenpunkte > CURRENT_DATE? Ansonsten wäre = CURRENT_DATE die schlichtere Variante.

Genauso könnte man
tarifvarianteId = X OR tarifvarianteId = X OR tarifvarianteId = X
zusammen fassen in
tarifvarianteId IN (x,y,z)
(ich denke mal X ist nicht immer gleich, was Schwachsinn wäre).

An der Performance wird das nichts ändern. Index muss rein logisch vermutlich auch zaehlerID,raumID,tarifvarianteID,messzeitpunkt DESC aber guck dir an wie sich dein EXPLAIN dabei verändert.

Wenn Messpunkte so linear auswertest könntest du auch das Aggregat für jeden Tag in einer eigenen Tabelle cachen, das ist durchaus üblich.
 
Obiast schrieb:
(CURRENT_DATE()) - 0
Zum Vergrößern anklicken....
Nur als Hinweis: das Ergebnis dieses Ausdrucks ist im übrigen ein Integer Wert, kein DATE Wert mehr. Vielleicht kann MySQL deswegen auch einen vorhandenen Index nicht mehr verwenden. (Ich vermute mal, dass überflüssige - 0 hat irgendwas mit dem Generieren der Abfrage zu tun, ansonsten wüsste ich nicht welchen Sinn das hätte)

Wenn man nur die Einträge von "heute" haben will, und messzeitpunkt ein Timestamp (Datum und Zeit) ist, dann wäre eine Bereichsabfrage ohne die Umwandlung von einem DATE in ein Integer vielleicht die effizientere Methode:

Code: 
 where messzeitpunkt >= current_date 
   and messzeitpunkt < date_add(current_date, INTERVAL 1 DAY)
 
Werbung:
Zurück
Oben