Entwickler-Ecke
Datenbanken - SELECT DISTINCT sehr langsam
Aya - Mi 20.02.13 12:18
Titel: SELECT DISTINCT sehr langsam
Hi,
ich habe in einem Programm hier einen riesen MySQL Query der in der Ausführung 90sek dauert.. auf der Suche nach der Ursache habe ich das ganze auf ein paar Zeilen zusammen gekürzt die das Problem sehr verdeutlichen:
SQL-Anweisung
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
| SELECT DISTINCT
f.`version`,
tvs.`id_task_status`
FROM `file` f
LEFT JOIN `file_task_connection` ftc USING(`id_file`)
LEFT JOIN `task_version_status` tvs USING(`id_task`, `version`)
WHERE f.`id_project` = 56 AND f.`id_file_master` IS NOT NULL |
Diese Abfrage dauert 17sek (file und file_task_connection haben jeweils etwa 700.000 Einträge, task_version_status hat 50.000).
Auch ist auf allen Feldern die in dem Query vorkommen ein Index (BTREE).
Der Query liefert etwa 600 Ergebnisse, wenn ich das
DISTINCT wegnehme bekomme ich 200.000 Stück, allerdings dauert der Query dann auch nur 0.2 Sekunden statt 17..
Wenn ich das ganze mit
EXPLAIN mir anschaue bekomme ich:
SQL-Anweisung
1:
2:
3:
4:
5:
6:
7:
| +----+-------------+-------+------+---------------------------------------+----------------------------+---------+------------------------+--------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------------------------+----------------------------+---------+------------------------+--------+------------------------------+
| 1 | SIMPLE | f | ref | fk_file_project,fk_file_file_master | fk_file_project | 4 | const | 263232 | Using where; Using temporary |
| 1 | SIMPLE | ftc | ref | PRIMARY,fk_file_has_task_file1 | PRIMARY | 4 | vfxdb_v001.f.id_file | 1 | Using index |
| 1 | SIMPLE | tvs | ref | fk_taskVersionStatus_task1,SpeedIndex | fk_taskVersionStatus_task1 | 5 | vfxdb_v001.ftc.id_task | 11 | |
+----+-------------+-------+------+---------------------------------------+----------------------------+---------+------------------------+--------+------------------------------+ |
Ohne
DISTINCT und mit
EXPLAIN:
SQL-Anweisung
1:
2:
3:
4:
5:
6:
7:
| +----+-------------+-------+------+---------------------------------------+----------------------------+---------+------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------------------------+----------------------------+---------+------------------------+--------+-------------+
| 1 | SIMPLE | f | ref | fk_file_project,fk_file_file_master | fk_file_project | 4 | const | 263232 | Using where |
| 1 | SIMPLE | ftc | ref | PRIMARY,fk_file_has_task_file1 | PRIMARY | 4 | vfxdb_v001.f.id_file | 1 | Using index |
| 1 | SIMPLE | tvs | ref | fk_taskVersionStatus_task1,SpeedIndex | fk_taskVersionStatus_task1 | 5 | vfxdb_v001.ftc.id_task | 11 | |
+----+-------------+-------+------+---------------------------------------+----------------------------+---------+------------------------+--------+-------------+ |
Gibt es da jetzt irgendeine Möglichkeit das ganze schneller zu bekommen?
Danke~
Aya
PS: Es sind alles InnoDBs
Ralf Jansen - Mi 20.02.13 12:26
| Zitat: |
| Der Query liefert etwa 600 Ergebnisse, wenn ich das DISTINCT wegnehme bekomme ich 200.000 Stück, allerdings dauert der Query dann auch nur 0.2 Sekunden statt 17.. |
Bist du dir bei den 0.2 Sekunden sicher? Oder ist es eher so das die Query bei dir anfängt nach 0.2 Sekunden Daten zu liefern bis du alle 200000 hast aber dann doch ähnlich lang dauert wie ohne Distinct.
Laut EXPLAINPLAN ist ja der einzige Unterschied das die 200000 Datensätze durch ein Temporäre Tabelle gejagt werden um dort die Duplikate zu entsorgen. Mit meinen spärlichen MYSQL Kenntnissen würde ich das so interpretieren das beide Abfragen ähnlich lang dauern sollten du mit DISTINCT aber einfach nur später den ersten Datensatz bekommst.
Aya - Mi 20.02.13 12:31
Die 0.2sek sind nur die Zeit bis der Query ausgeführt wurde, ja. Hab es eben mal mit einer Stoppuhr getestet, die zeit von der Anfrage bis dahin wo alle Daten gelesen wurden ist bei beiden in etwa gleich..
Also gibt es da wohl nur recht wenig zu optimieren? :(
WasWeißDennIch - Mi 20.02.13 12:37
Hast Du mal versucht, das DISTINCT durch eine Gruppierung zu ersetzen?
Aya - Mi 20.02.13 12:41
Jep, dauert leider genau gleich lang :(
Aya - Mi 20.02.13 13:05
Hab noch eine Sache getestet, wenn ich den letzten
JOIN rausnehme, dann dauert es (inkl. holen der Datensätze) 1.2 Sekunden.
SQL-Anweisung
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
| SELECT DISTINCT
f.`version`
FROM `file` f
LEFT JOIN `file_task_connection` ftc USING(`id_file`)
WHERE f.`id_project` = 56 AND f.`id_file_master` IS NOT NULL |
Hier das dazugehörige
EXPLAIN:
SQL-Anweisung
1:
2:
3:
4:
5:
6:
| +----+-------------+-------+------+-------------------------------------+-----------------+---------+----------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------------------------+-----------------+---------+----------------------+--------+-------------+
| 1 | SIMPLE | f | ref | fk_file_project,fk_file_file_master | fk_file_project | 4 | const | 263232 | Using where |
| 1 | SIMPLE | ftc | ref | PRIMARY,fk_file_has_task_file1 | PRIMARY | 4 | vfxdb_v001.f.id_file | 1 | Using index |
+----+-------------+-------+------+-------------------------------------+-----------------+---------+----------------------+--------+-------------+ |
Wieso macht es der eine
JOIN so sehr viel langsamer?
Aya~
Ralf Jansen - Mi 20.02.13 13:05
Beschleunigen würde wahrscheinlich nur wenn du es schaffst die Duplikate bereits während der Join Operation loszuwerden. Alles was danach kommt, also wenn die 200000 Datensätze schon existieren, wird wohl nichts ändern. Da können wir aber wohl wenig helfen dafür muß man inneres Wissen über die Datenbankstruktur haben um eventuell geschickt die Reihenfolge der Joins festzulegen oder Subselect mit Teiljoins einzuziehen um schon einen ~Distinct~ frühzeitig zu platzieren bevor sich das Ergebnis auf entsprechend viele Datensätze multipliziert hat.
Ist der Left Join auf file_task_connection ein Überbleibsel der Kürzung? In der gekürzten Fassung sieht der Überflüssig aus.
Edit:
| Zitat: |
| Wieso macht es der eine JOIN so sehr viel langsamer? |
Es wird keine Temporäre Tabelle verwendet also scheint in dieser Form der Distinct keinen Unterschied zu machen was der SQl Optimizer gemerkt hat und dir wieder frühzeitig die erste Row liefern kann.
Aya - Mi 20.02.13 13:11
 Ralf Jansen hat folgendes geschrieben  : |
| Ist der Left Join auf file_task_connection ein Überbleibsel der Kürzung? In der gekürzten Fassung sieht der Überflüssig aus. |
Die file_task_connection ist eine Tabelle mit nur zwei Spalten: id_file und id_task. In der File Tabelle selbst gibt es kein id_task, daher wird die für das spätere JOIN mit der task_version_status tabelle benötigt :)
| Ralf Jansen hat folgendes geschrieben : |
| Es wird keine Temporäre Tabelle verwendet also scheint in dieser Form der Distinct keinen Unterschied zu machen was der SQl Optimizer gemerkt hat und dir wieder frühzeitig die erste Row liefern kann. |
In dem Falle werden aber auch knapp 200.000 Zeilen zurückgegeben, und das abholen dieser ist in den 1.2 Sek schon drin - wohingegen vorher bei 200.000 Zeilen es 17 Sek gedauert hat sie zu holen..
Ralf Jansen - Mi 20.02.13 13:20
Dann liegt es vielleicht dran das der letzte LEFT JOIN auch irgendwie überflüssig geworden ist und die Join Operation nicht mehr ausgeführt wird.
Für mich entspricht dein SQL jetzt
SQL-Anweisung
1:
2:
3:
| SELECT f.`version`
FROM `file` f
WHERE f.`id_project` = 56 AND f.`id_file_master` IS NOT NULL |
weil nix mit ftc gemacht wird bzw. alles was an Daten daraus gezogen wurde durch das Distinct wieder weg ist. Mein EXPLAIN Lesefähigkeiten geben aber leider nicht her ob der Optimizer so schlau war und das intern so umgesetzt hat.
Entwickler-Ecke.de based on phpBB
Copyright 2002 - 2011 by Tino Teuber, Copyright 2011 - 2025 by Christian Stelzmann Alle Rechte vorbehalten.
Alle Beiträge stammen von dritten Personen und dürfen geltendes Recht nicht verletzen.
Entwickler-Ecke und die zugehörigen Webseiten distanzieren sich ausdrücklich von Fremdinhalten jeglicher Art!