mysql: Query Optimierung

R3dNeXX

Hallo Leute,

habe ein Problem, meine Query zu optimieren, stehe auf dem Schlauch.

Folgender Sachverhalt:
Habe eine Tabelle, aus der ich Daten holen will. Darin sind ca. 2,2 Mio. Datensätze.
Auf diese Daten gibt es nur eine kleine Einschränkung, sonst will ich alle Daten, aber in einer bestimmten, gruppierten Form (komme so runter auf ca. 700.000 Datensätze).
Ein Wert kommt noch dazu, der über einen Join (oder vllt auch auf eine andere Weise ?) aus einer 2. Tabelle geholt wird (nur ca. 300 Datensätze).

Hier das Statement:

SELECT
	data1,
	date,
	MyTableData.data3,
	MyTableJoin.data2,
	data4,
	SUM(data5) AS d5,
	SUM(data6) AS d6

FROM MyTableData
LEFT OUTER JOIN MyTableJoin ON MyTableJoin.data3 = MyTableData.data3

WHERE date BETWEEN '2015-01-01' AND '2015-01-31'

GROUP BY 
	data1, 
	date,
	MyTableData.data3,
	MyTableJoin.data2,
	data4;

(Muss hier aus Sicherheitsgründen die Namen der Spalten/Tabellen ändern, hoffe das stört nicht.)

Mit dieser Query dauert es einige Minuten (durch meine falsche Verwendung wird eine Temp.-Table erzeugt laut EXPLAIN).
Ich denke, da geht noch was, finde aber keine ordentliche Lösung.

id	select_type	table		type	possible_keys	key		key_len		ref	rows	Extra
1	SIMPLE		MyTableData	ALL												2171442	Using where; Using temporary; Using filesort
1	SIMPLE		MyTableJoin	index					data3		404			237		Using index

Die Spalten im Join sind jeweils Indizes, kann gerne auch weitere Indizes... hinzufügen wenn nötig.
Die Spalte date im WHERE ist ebenfalls ein Index.
Sicher wird die Gruppierung das Problem sein, kann aber kein gemeinsamen Index auf alle Spalten im GROUP BY machen, da das die max. Größe des Index übersteigt (MyISAM, 1000 Bytes).

Kann mir jemand da bitte helfen ?
Komme da einfach nicht weiter.
Wenn ihr noch was braucht, sagt Bescheid.

Danke schon mal von mir !
lg

welches dbms?
was ist pk?
wie lange braucht:

SELECT
    data1,
    date,
    MyTableData.data3,
    data4,
    SUM(data5) AS d5,
    SUM(data6) AS d6
FROM MyTableData       
WHERE date BETWEEN '2015-01-01' AND '2015-01-31'

?
Brauchst du tatsächlich diese fünf Sachen, damit dein group by funktioniert?
data1,
date,
MyTableData.data3,
MyTableJoin.data2,
data4,

Könntest du da nicht was weglassen und später zusammen joinen?

"... kann aber kein gemeinsamen Index auf alle Spalten im GROUP BY machen, da das die max. Größe des Index übersteigt (MyISAM, 1000 Bytes). " -> Das willst du auch nicht, weil es nichts bringt.

R3dNeXX

Danke für die Antwort, habe es jetzt erst gesehen.

Also: DBMS ist mysql, PK ist eine Auto Increment ID, wobei ich das eigentlich ändern könnte auf eine der benötigten Spalten.

Sry, habe echt ein paar Infos vergessen: das Statement ohne den Join braucht ca 56 sec. für den gesamten Monat (date BETWEEN '2015-01-01' AND '2015-01-31').

Nein, brauche die Daten wirklich in dem Format.
Hm, weißt nicht wie du das meinst, aber da ich die Daten so brauche, könnte ich evtl. auslesen ohne GROUP BY und auf eine eigene Temp-Table die Gruppierung machen ? Bin mir nicht sicher, ob das was bringt.

Guter Ansatz: das Statement

SELECT
    data1,
    date,
    MyTableData.data3,
    data4,
    SUM(data5) AS d5,
    SUM(data6) AS d6
FROM MyTableData       
WHERE date BETWEEN '2015-01-01' AND '2015-01-31'

braucht so nur knapp 3 sec. !!

tntnet

Es ist nicht klar, welche Spalten in welcher Tabelle zu finden sind. Aus Deiner Erläuterung sieht es so aus, als hättest Du auf jede Spalte einen separaten Index. Das wäre nicht wirklich effizient.

Vieles hängt vom Mengengerüst ab, aber ein Index auf MyTableData(data3, data1, date, data4) würde wahrscheinlich helfen, je nachdem, welche Spalten in welcher Tabelle zu finden sind.

Der Queryoptimizer könnte über den Index die Daten aus MyTableData bereits sortiert holen und auf den filesort verzichten.

R3dNeXX

Nein, auf alle Spalten hab ich keinen Index, nur einen Index aufgeteilt auf 2 Spalten. Aber das geht aus meiner Erklärung wirklich nicht hervor.

Ich geb das lieber mal genau an. Die Table MyTableData sieht im Entwurf so aus:

id        int            pk
data1     varchar(200)
date      date           index 1 part 1
data3     varchar(200)   index 1 part 2
data4     varchar(200)
data5     int
data6     int

(Es gibt noch ein paar andere Spalten, die aber nicht ausgelesen werden, auf die auch nicht eingeschränkt wird und die nicht Teil eines Index sind, deshalb hab ich die weggelassen.)

Der Aufbau der Table MyTableJoin:

data2     varchar(200)    pk
data3     varchar(200)    pk part 2

tntnet schrieb:

Vieles hängt vom Mengengerüst ab, aber ein Index auf MyTableData(data3, data1, date, data4) würde wahrscheinlich helfen, je nachdem, welche Spalten in welcher Tabelle zu finden sind.

Das ist mein Problem. Die Größe des Index wird zu groß, wenn ich auf diese Spalten einen erstelle (aufgrund varchar(200) der Spalten).

tntnet

R3dNeXX schrieb:

Das ist mein Problem. Die Größe des Index wird zu groß, wenn ich auf diese Spalten einen erstelle (aufgrund varchar(200) der Spalten).

Ist das wirklich so? Dann kann man nichts machen. Aber ich würde es trotzdem mal in Erwägung ziehen.

Um einen Index zu nutzen brauchst Du auf jeden Fall mal einen auf MyTableData(date), da darüber gefiltert wird. Es könnte sich dann entscheiden, die Gruppierung für MyTableData zu machen. Dafür braucht er einen Index auf data1, data3 und data4. Das ergibt dann MyTableData(data, data1, data3, data4). Eine andere Möglichkeit sehe ich nicht.

R3dNeXX

Jo, laut mysql bei MyISAM (was ich hier auch verwende) max. 1000 Bytes für einen Index, bei InnoDB noch weniger.

Ok, zunächst mal danke !!

Den Index auf die Spalten data1, data3 und data4 wird wegen der Größenbeschränkung leider nix.
Verdammt...

Shade Of Mine

Du könntest sehen ob du mit char uU besser fährst als varchar.

ansonsten kannst du auch zB automatisch per trigger eine spalte data1_index anlegen die eben aus den ersten 3 buchstaben von data1 besteht und darüber indexen. Das erspart dir zwar nicht alles aber zumindest full table scans lassen sich so reduzieren. Und gerade wenn du eh den index über 4 spalten legen willst ist er uU sowieso "unique genug".

PS: Stichwort ist partial index, aber mysql kann das afaik nicht, deshalb muss man es händisch machen.

hustbaer

Mehrere Minuten kommt mir viel zu viel vor.

Wie viele Rows kommen denn bei dem über Datum eingeschränkten SELECT raus? (Die Abfrage die 3 Sekunden dauert)

Und wie viele verschiedene Zeilen werden über den JOIN aus MyTableJoin geladen?

Und wie lange dauert es wenn du nur den JOIN machst, ohne GROUP BY ?
(Also genau die Abfrage so wie du sie hast, nur halt ohne GROUP BY und natürlich dann x statt SUM(x) )

ps: Falls der JOIN das Problem ist, könntest du es mit einem "covering Index" probieren. Also einem Index auf MyTableJoin.data3 der zusätzlich noch MyTableJoin.data2 enthält.

Und nochwas: ist MyTableJoin.data3 unique, oder kann es mehrere MyTableJoin Zeilen mit dem selben data3 Wert geben? Falls MyTableJoin.data3 nämlich unique ist, dann ist MyTableJoin.data2 in der GROUP BY Anweisung überflüssig, und sollte entfernt werden. Dann kannst du nämlich ERST gruppieren und dann erst joinen.

tntnet

R3dNeXX schrieb:

Jo, laut mysql bei MyISAM (was ich hier auch verwende) max. 1000 Bytes für einen Index, bei InnoDB noch weniger.

Ok, zunächst mal danke !!

Den Index auf die Spalten data1, data3 und data4 wird wegen der Größenbeschränkung leider nix.
Verdammt...

Hmm mal sehen: 200 Bytes mal 3 ergibt 600 Bytes. Plus ein wenig Overhead. Das ist doch deutlich weniger als 1000 Bytes. Aber das wird schon stimmen, was Du sagst.

Dann reduziere den Index auf MyTableData(date, data1, data3). Ist immer noch besser als gar nichts.

Ach ja, mir fällt gerade auf, dass Du über einen Index auf data1, data3 und data4 sprichst. Das bringt gar nichts, da zuerst über date gefiltert wird. Da muss auf jeden Fall das date am Anfang stehen.

hustbaer

Ist mir gerade erst aufgefallen...

R3dNeXX schrieb:

Der Aufbau der Table MyTableJoin:
data2     varchar(200)    pk
data3     varchar(200)    pk part 2

Du joinst auf data3.
Also muss dein Index auch mit data3 anfangen!
Wenn du keine anderen Stellen hast, wo ein Index der mit data2 anfängt benötigt wird, dann würde ich empfehlen einfach die Reihenfolge der Felder im Primary Key umzudrehen. Also data3, data2 statt data2, data3 .

Ansonsten mach einen zusätzlichen data3, data2 Index.

Auf Felder zu joinen die keinen Index haben ist halt meistens keine gute Idee.

R3dNeXX schrieb:

Nein, auf alle Spalten hab ich keinen Index, nur einen Index aufgeteilt auf 2 Spalten. Aber das geht aus meiner Erklärung wirklich nicht hervor.

Ich geb das lieber mal genau an. Die Table MyTableData sieht im Entwurf so aus:
id        int            pk
data1     varchar(200)
date      date           index 1 part 1
data3     varchar(200)   index 1 part 2
data4     varchar(200)
data5     int
data6     int
(Es gibt noch ein paar andere Spalten, die aber nicht ausgelesen werden, auf die auch nicht eingeschränkt wird und die nicht Teil eines Index sind, deshalb hab ich die weggelassen.)

Der Aufbau der Table MyTableJoin:
data2     varchar(200)    pk
data3     varchar(200)    pk part 2
tntnet schrieb:

Vieles hängt vom Mengengerüst ab, aber ein Index auf MyTableData(data3, data1, date, data4) würde wahrscheinlich helfen, je nachdem, welche Spalten in welcher Tabelle zu finden sind.

Das ist mein Problem. Die Größe des Index wird zu groß, wenn ich auf diese Spalten einen erstelle (aufgrund varchar(200) der Spalten).

Deine MyTableJoin Tabelle ist nicht gut entworfen. Einen Join mit einem varchar willst du in deinem Fall nicht machen.

Auch der Fakt, dass du viele varchars in deinem group by hast ist schlecht.

Was passiert: viele eher ineffiziente Vergleiche (varchars). Viel Speicher verschwendet, der die CPU Cachelines vollstopft -> viel unnötiger IO (der Zeit verschwendet)

Was du machen kannst, hängt davon ab, ob du die Tabellenstruktur ändern kannst.

hustbaer

@Duplikateneliminator
Mit 2,2 Mio. Datensätzen darf die Abfrage trotzdem nicht mehrere Minuten brauchen.
Irgendwas läuft da falsch.
Entweder erstellt MySQL einen krass sub-optimalen Execution-Plan, oder es fehlt irgendwo ein essenzieller Index.
Wie z.B. der von mir erwähnte für den JOIN notwendige Index.

R3dNeXX

Alter hier gehts ab, komme nicht hinterher.
Zunächst mal danke an euch für eure Zeit.

hustbaer schrieb:

Wie viele Rows kommen denn bei dem über Datum eingeschränkten SELECT raus? (Die Abfrage die 3 Sekunden dauert)

Mit der Query genau 1 Zeile (wegen dem SUM). Ohne SUM kommen ganz genau alle Datensätze. Und beides innerhalb von 1 Sekunde !
Das sieht mal gut aus.

hustbaer schrieb:

Und wie viele verschiedene Zeilen werden über den JOIN aus MyTableJoin geladen?

Bin mir nicht sicher, ob ich dich richtig verstehe, aber da habe ich das komplette Statement verwendet, nur ohne GROUP BY.
In der JOIN-Table sind insgesamt auch nur ca. 300 Rows.
Komme so auf mehrere Minuten.

hustbaer schrieb:

Und wie lange dauert es wenn du nur den JOIN machst, ohne GROUP BY ?
(Also genau die Abfrage so wie du sie hast, nur halt ohne GROUP BY und natürlich dann x statt SUM(x) )

Ebenfalls mehrere Minuten.
Der JOIN ist wohl das Problem !

hustbaer schrieb:

ps: Falls der JOIN das Problem ist, könntest du es mit einem "covering Index" probieren. Also einem Index auf MyTableJoin.data3 der zusätzlich noch MyTableJoin.data2 enthält.

Der Vorschlag ist gut, habe die obigen Abfragen aber mit so einem Index ausgeführt, so wirklich ändert sich da nix.

hustbaer schrieb:

Und nochwas: ist MyTableJoin.data3 unique, oder kann es mehrere MyTableJoin Zeilen mit dem selben data3 Wert geben? Falls MyTableJoin.data3 nämlich unique ist, dann ist MyTableJoin.data2 in der GROUP BY Anweisung überflüssig, und sollte entfernt werden. Dann kannst du nämlich ERST gruppieren und dann erst joinen.

Guter Punkt. Eigentlich ist data3 unique.
Habe es genauso umgesetzt, dann läuft das in ca. 30 sec.
Geile Sache, das sieht gut aus.

Aus Fairness zu den anderen Kommentaren.

Shade Of Mine schrieb:

Du könntest sehen ob du mit char uU besser fährst als varchar.

ansonsten kannst du auch zB automatisch per trigger eine spalte data1_index anlegen die eben aus den ersten 3 buchstaben von data1 besteht und darüber indexen. Das erspart dir zwar nicht alles aber zumindest full table scans lassen sich so reduzieren. Und gerade wenn du eh den index über 4 spalten legen willst ist er uU sowieso "unique genug".

PS: Stichwort ist partial index, aber mysql kann das afaik nicht, deshalb muss man es händisch machen.

Das ist bei mir noch nicht unique genug, habe es mir angeschaut, wird leider nicht funzen.

tntnet schrieb:

Hmm mal sehen: 200 Bytes mal 3 ergibt 600 Bytes. Plus ein wenig Overhead. Das ist doch deutlich weniger als 1000 Bytes. Aber das wird schon stimmen, was Du sagst.

Dann reduziere den Index auf MyTableData(date, data1, data3). Ist immer noch besser als gar nichts.

Ach ja, mir fällt gerade auf, dass Du über einen Index auf data1, data3 und data4 sprichst. Das bringt gar nichts, da zuerst über date gefiltert wird. Da muss auf jeden Fall das date am Anfang stehen.

Habe beides umgesetzt, ich denke schon, dass das auch etwas bringt.
Es ist dann zumindest mal korrekt.

hustbaer schrieb:

Ist mir gerade erst aufgefallen...
Du joinst auf data3.
Also muss dein Index auch mit data3 anfangen!
Wenn du keine anderen Stellen hast, wo ein Index der mit data2 anfängt benötigt wird, dann würde ich empfehlen einfach die Reihenfolge der Felder im Primary Key umzudrehen. Also data3, data2 statt data2, data3 .

Ansonsten mach einen zusätzlichen data3, data2 Index.

Auf Felder zu joinen die keinen Index haben ist halt meistens keine gute Idee.

Habe ich ebenfalls jetzt drin.

Duplikateneliminator schrieb:

Deine MyTableJoin Tabelle ist nicht gut entworfen. Einen Join mit einem varchar willst du in deinem Fall nicht machen.

Auch der Fakt, dass du viele varchars in deinem group by hast ist schlecht.

Was passiert: viele eher ineffiziente Vergleiche (varchars). Viel Speicher verschwendet, der die CPU Cachelines vollstopft -> viel unnötiger IO (der Zeit verschwendet)

Was du machen kannst, hängt davon ab, ob du die Tabellenstruktur ändern kannst.

Du hast recht, es ist nur hier schlecht änderbar, da das alles so benötigt wird.
Das gibt mir aber zu denken bei den nächsten Designs.

Möglicherweise kann ich am Design doch noch was ändern.
Aber ich meine, ca. 30 sec. ist ok.
Ziel erreicht.

Also danke Leute für die Hilfe, hat mir sehr geholfen.

hustbaer

R3dNeXX schrieb:

Guter Punkt. Eigentlich ist data3 unique.

Wenn das für die ganze MyTableJoin Tabelle gilt, und nicht nur für das in der Abfrage verwendete Subset, dann wäre es wohl angebracht den Primary-Key auf einfach nur data3 zu ändern.
Wenn du zusätzlich Lookups über data2 brauchst, dann mach für data2 nen eigenen Index.

R3dNeXX

So ist es.
Habe es vorhin schon so geändert.

Ein Index auf data2 bringt derzeit nix, wird in Zukunft aber vllt mal nötig.

Danke !

nman

Btw, schau evtl. mal hier rein, um ein paar der Grundlagen besser zu verstehen:
http://use-the-index-luke.com
http://sql-performance-explained.com