Query verbessern da Index nicht genutzt

A

AntonWert

Benutzer
Beiträge
7
Hallo,
Anton mein Name.

ich habe eine einfache Tabelle in MariaDB

Zu einer bestimmten Zeit werden Werte gemessen, und mit der Zeit abgelegt:

CREATE TABLE `tabelle1` (
`nummer` int(11) NOT NULL AUTO_INCREMENT,
`zeit` datetime NOT NULL,
`wert` int(11) NOT NULL,
PRIMARY KEY (`nummer`),
KEY `zeit` (`zeit`)
) ENGINE=InnoDB;


Ich möchte in einer Abfrage alle Werte eines Tages summiert darstellen:

SELECT
AVG(UNIX_TIMESTAMP(zeit)) AS "time",
SUM(wert) AS summe
FROM tabelle1
WHERE
zeit BETWEEN '2021-01-01' AND '2023-01-01'
GROUP BY DATE(zeit)
ORDER BY zeit

Leider wird der Index zeit nicht genutzt:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tabelle1 ALL zeit NULL NULL NULL 12345 Using where; Using temporary; Using filesort

Wie muss ich meine Abfrage umstellen?

Oder kann ich (gelingt mir aber nicht den Index zu nutzen) mit einem Sub-Querry arbeiten?

EXPLAIN SELECT
AVG(UNIX_TIMESTAMP(t1.zeit)) AS "time",
SUM(t1.wert) AS summe
FROM
(SELECT
zeit, wert
FROM tabelle1
WHERE zeit BETWEEN '2021-01-01' AND '2023-01-01' ORDER BY zeit) AS t1
GROUP BY DATE(t1.zeit)

Ich fürchte wenn die Daten größer werden wird's langsam...

Weiß jemand Rat?
 
Werbung:
Code: 
edb=# create table tabelle1 (nummer int generated always as identity primary key, zeit timestamp, wert int);
CREATE TABLE
edb=*# create index idx_zeit on tabelle1(zeit);
CREATE INDEX
edb=*# explain analyse select avg(extract(epoch from zeit)), sum(wert) from (select zeit, wert from tabelle1 where zeit between '2021-01-01' and '2023-01-01') t1 group by extract(date from zeit) ;
                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=14.92..15.06 rows=9 width=24) (actual time=0.038..0.040 rows=0 loops=1)
   Group Key: date_part('date'::text, tabelle1.zeit)
   Batches: 1  Memory Usage: 24kB
   ->  Bitmap Heap Scan on tabelle1  (cost=4.24..14.83 rows=9 width=20) (actual time=0.034..0.035 rows=0 loops=1)
         Recheck Cond: ((zeit >= '01-JAN-21 00:00:00'::timestamp without time zone) AND (zeit <= '01-JAN-23 00:00:00'::timestamp without time zone))
         ->  Bitmap Index Scan on idx_zeit  (cost=0.00..4.24 rows=9 width=0) (actual time=0.031..0.031 rows=0 loops=1)
               Index Cond: ((zeit >= '01-JAN-21 00:00:00'::timestamp without time zone) AND (zeit <= '01-JAN-23 00:00:00'::timestamp without time zone))
 Planning Time: 0.210 ms
 Execution Time: 0.134 ms
(9 rows)

Erstaunlicherweise nutzt er sogar bei einer leeren Tabelle den Index - ich hätte gedacht, ich muß enable_seqscan ausschalten oder erst einmal ein paar tausend Rows erzeugen...

Je nachdem, wie die Daten erfaßt werden(jeden Tag neu hinzukommend, Zeit damit stetig wachsend und es werden nie Datensätze gelöscht oder geändert) würde ich statt BTree-Index eher BRIN verwenden, aber das ist ein anderes Thema.

PS.: Du sagtest: "alle Werte eines Tages summiert", Dein Where geht aber über 2 Jahre.
 
btw, die erste Abfrage, ohne Subselect, läuft analog:

Code: 
edb=*# explain analyse select avg(extract(epoch from zeit)), sum(wert) from tabelle1 where zeit between '2021-01-01' and '2023-01-01' group by extract(date from zeit) ;
                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=14.92..15.06 rows=9 width=24) (actual time=0.014..0.016 rows=0 loops=1)
   Group Key: date_part('date'::text, zeit)
   Batches: 1  Memory Usage: 24kB
   ->  Bitmap Heap Scan on tabelle1  (cost=4.24..14.83 rows=9 width=20) (actual time=0.011..0.012 rows=0 loops=1)
         Recheck Cond: ((zeit >= '01-JAN-21 00:00:00'::timestamp without time zone) AND (zeit <= '01-JAN-23 00:00:00'::timestamp without time zone))
         ->  Bitmap Index Scan on idx_zeit  (cost=0.00..4.24 rows=9 width=0) (actual time=0.008..0.009 rows=0 loops=1)
               Index Cond: ((zeit >= '01-JAN-21 00:00:00'::timestamp without time zone) AND (zeit <= '01-JAN-23 00:00:00'::timestamp without time zone))
 Planning Time: 0.187 ms
 Execution Time: 0.106 ms
(9 rows)
 
Ja ich möchte die Daten über 2 Jahre abfragen, aber dabei die summierten Werte der einzelnen Tage Gruppieren, daher GROUP BY DATE(zeit)

Und ja, es kommen eigentlich immer nur Werte hinzu, ich fürchte aber, MariaDB ist hier begrenzter als PostgreeSQL, und es steht nur BTREE und Hash zur Auswahl.

Kann es doch daran liegen, dass ich derzeit "nur" 2500 Einträge habe
 
ahhh, okay. So macht die Abfrage mehr Sinn.

Und ja, MySQL & Derivate sind begrenzter als PostgreSQL, und meines Wissens nach haben die keinen kostenbasierten Planner/Optimizer. Von daher dürfte der Plan (vermutlich) bei größeren Datenmengen auch nicht Richtung Indexscan kippen.
 
Wahrscheinlich hast du nur sehr wenig Einträge in deiner Tabelle. Der Optimizer bestimmt anhand von statistischen Werten ob es sich lohnt einen INDEX zu nutzen.

Füll doch einfach testweise mal deine Tabelle. Mit MariaDB geht das da ganz einfach mit der sequel engine.

Damit kannst du Werte für deine Felder erzeugen:
SQL: 
SELECT NULL, now() - INTERVAL seq HOUR, ROUND(RAND()*1000,0) FROM seq_1_to_100000;

Und so kannste die dann befüllen
SQL: 
INSERT into YourTable SELECT NULL, now() - INTERVAL seq HOUR FROM seq_1_to_100000;

Hier ein Beispiel mit meiner Tabelle. Da kannst du sehen das der Index nicht genutzt wird wenn du viele daten der Tabelle verarbeiten musst, denn dann lohnt es sich nicht

SQL: 
MariaDB [test]> select COUNT(*) FROM mytab;
+----------+
| COUNT(*) |
+----------+
|  1090010 |
+----------+
1 row in set (0.387 sec)

MariaDB [test]> EXPLAIN
    -> SELECT * FROM mytab
    -> WHERE ts BETWEEN '2021-12-01 00:00:00' AND '2022-01-01 00:00:00';
+------+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                 |
+------+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------+
|    1 | SIMPLE      | mytab | range | ts            | ts   | 6       | NULL | 180466 | Using index condition |
+------+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------+
1 row in set (0.003 sec)

MariaDB [test]> EXPLAIN
    -> SELECT * FROM mytab
    -> WHERE ts BETWEEN '2019-01-01 00:00:00' AND '2022-01-01 00:00:00';
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | mytab | ALL  | ts            | NULL | NULL    | NULL | 1088110 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.001 sec)

MariaDB [test]>
 
Habe nun mal eine Spalte "datum" hinzugefügt und mit "Update tabelle1 set datum=date(zeit)" gefüllt und natürlich einen Index darüber angelegt. Selbst jetzt bekomme ich die Abfrage nicht so hin, dass er auf den Index gehen will
 
BerndB schrieb:
Wahrscheinlich hast du nur sehr wenig Einträge in deiner Tabelle. Der Optimizer bestimmt anhand von statistischen Werten ob es sich lohnt einen INDEX zu nutzen.

Füll doch einfach testweise mal deine Tabelle. Mit MariaDB geht das da ganz einfach mit der sequel engine.

Damit kannst du Werte für deine Felder erzeugen:
SQL: 
SELECT NULL, now() - INTERVAL seq HOUR, ROUND(RAND()*1000,0) FROM seq_1_to_100000;

Und so kannste die dann befüllen
SQL: 
INSERT into YourTable SELECT NULL, now() - INTERVAL seq HOUR FROM seq_1_to_100000;

Hier ein Beispiel mit meiner Tabelle. Da kannst du sehen das der Index nicht genutzt wird wenn du viele daten der Tabelle verarbeiten musst, denn dann lohnt es sich nicht

SQL: 
MariaDB [test]> select COUNT(*) FROM mytab;
+----------+
| COUNT(*) |
+----------+
|  1090010 |
+----------+
1 row in set (0.387 sec)

MariaDB [test]> EXPLAIN
    -> SELECT * FROM mytab
    -> WHERE ts BETWEEN '2021-12-01 00:00:00' AND '2022-01-01 00:00:00';
+------+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                 |
+------+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------+
|    1 | SIMPLE      | mytab | range | ts            | ts   | 6       | NULL | 180466 | Using index condition |
+------+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------+
1 row in set (0.003 sec)

MariaDB [test]> EXPLAIN
    -> SELECT * FROM mytab
    -> WHERE ts BETWEEN '2019-01-01 00:00:00' AND '2022-01-01 00:00:00';
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | mytab | ALL  | ts            | NULL | NULL    | NULL | 1088110 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.001 sec)

MariaDB [test]>
Zum Vergrößern anklicken....
JA super, dann besteht doch noch Hoffnung. Danke
 
mal kurzer test ...

Code: 
edb=*# insert into tabelle1 (zeit, wert) select '2000-01-01'::date + s*'1hour'::interval, random()*10000 from generate_series(1, 300000) s;
INSERT 0 300000
edb=*# select min(zeit), max(zeit) from tabelle1;
         min         |         max         
---------------------+---------------------
 2000-01-01 01:00:00 | 2034-03-23 00:00:00
(1 row)

edb=*# create index index_zeit_wert on tabelle1 (zeit) include (wert);
CREATE INDEX

edb=*# explain analyse select zeit::date, sum(wert) from tabelle1 where zeit between '2021-01-01'::timestamp and '2023-01-01'::timestamp group by zeit::date order by zeit;
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.42..876.65 rows=17378 width=16) (actual time=0.072..11.790 rows=17521 loops=1)
   Group Key: zeit
   ->  Index Only Scan using index_zeit_wert on tabelle1  (cost=0.42..615.98 rows=17378 width=12) (actual time=0.057..3.694 rows=17521 loops=1)
         Index Cond: ((zeit >= '2021-01-01 00:00:00'::timestamp without time zone) AND (zeit <= '2023-01-01 00:00:00'::timestamp without time zone))
         Heap Fetches: 0
 Planning Time: 0.252 ms
 Execution Time: 13.288 ms
(7 rows)

edb=*# drop index index_zeit_wert;
DROP INDEX
edb=*# explain analyse select zeit::date, sum(wert) from tabelle1 where zeit between '2021-01-01'::timestamp and '2023-01-01'::timestamp group by zeit::date order by zeit;
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.42..914.65 rows=17378 width=16) (actual time=0.027..11.130 rows=17521 loops=1)
   Group Key: zeit
   ->  Index Scan using idx_zeit on tabelle1  (cost=0.42..653.98 rows=17378 width=12) (actual time=0.020..4.549 rows=17521 loops=1)
         Index Cond: ((zeit >= '2021-01-01 00:00:00'::timestamp without time zone) AND (zeit <= '2023-01-01 00:00:00'::timestamp without time zone))
 Planning Time: 0.130 ms
 Execution Time: 12.226 ms
(6 rows)


Zwischendurch noch einen Index erzeugt, der als INCLUDE noch den Wert mit hatte, damit Index-Only-Scan, war aber bei den geringen Datenmengen jetzt nicht wirklich von Vorteil. Die Zeiten schwanken durch Caching-Effekte.
 
AntonWert schrieb:
Habe nun mal eine Spalte "datum" hinzugefügt und mit "Update tabelle1 set datum=date(zeit)" gefüllt und natürlich einen Index darüber angelegt. Selbst jetzt bekomme ich die Abfrage nicht so hin, dass er auf den Index gehen will
Zum Vergrößern anklicken....

nette Idee, aber schlecht, weil das kann inkonsistent werden. Besser, wenn man solche 'berechneten' Felder möchte:

Code: 
edb=*# drop table demo;
DROP TABLE
edb=*# create table demo(zeit timestamp, datum date generated always as (zeit::date) stored);
CREATE TABLE
edb=*# insert into demo(zeit) select now();
INSERT 0 1
edb=*# select * from demo;
            zeit            |   datum    
----------------------------+------------
 2022-01-16 18:41:13.891684 | 2022-01-16
(1 row)

edb=*#
 
Du kannst das Feld automatisch berechnen lassen mit:

sieh mal dieses Beispiel:

SQL: 
ALTER TABLE mytab ADD COLUMN mydate DATE AS (DATE(ts)) PERSISTENT;
ALTER TABLE mytab ADD KEY ( mydate );

SQL: 
MariaDB [test]> SELECT * FROM mytab LIMIT 5;
+----+---------------------+------+
| id | ts                  | con  |
+----+---------------------+------+
|  1 | 2022-01-16 00:15:00 |   48 |
|  2 | 2022-02-16 00:15:00 |   33 |
|  3 | 2021-01-16 00:15:00 |   12 |
|  4 | 2020-01-16 00:15:00 |  199 |
|  5 | 2020-02-16 00:15:00 |   17 |
+----+---------------------+------+
5 rows in set (0.014 sec)

MariaDB [test]> ALTER TABLE mytab ADD COLUMN mydate DATE AS (DATE(ts)) PERSISTENT;
Query OK, 1090010 rows affected (9.760 sec)           
Records: 1090010  Duplicates: 0  Warnings: 0

MariaDB [test]> ALTER TABLE mytab ADD KEY ( mydate );
Query OK, 0 rows affected (2.216 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT * FROM mytab LIMIT 5;
+----+---------------------+------+------------+
| id | ts                  | con  | mydate     |
+----+---------------------+------+------------+
|  1 | 2022-01-16 00:15:00 |   48 | 2022-01-16 |
|  2 | 2022-02-16 00:15:00 |   33 | 2022-02-16 |
|  3 | 2021-01-16 00:15:00 |   12 | 2021-01-16 |
|  4 | 2020-01-16 00:15:00 |  199 | 2020-01-16 |
|  5 | 2020-02-16 00:15:00 |   17 | 2020-02-16 |
+----+---------------------+------+------------+
5 rows in set (0.043 sec)

MariaDB [test]>
 
Werbung:

Ähnliche Themen

H
y2k38-Problem und utc_time
Antworten
7
Aufrufe
313
Haksi
H
D
DB mit Zufallswerten füllen
Antworten
2
Aufrufe
356
dcst55
D
L
Mehrung Behälterfüllstand je Zeiteinheit
Antworten
4
Aufrufe
443
Ludwigmller
L
M
Festtext in korreliertem Update oder mit Abfrageergebnissen rechnen
Antworten
4
Aufrufe
1K
Muschelpuster
M
norbert_pdm
komplexe Abfrage
Antworten
6
Aufrufe
2K
akretschmer
akretschmer
Zurück
Oben