4. [gelöst]Abfrage über 3 Tabellen: Performance optimieren

[gelöst]Abfrage über 3 Tabellen: Performance optimieren


  • Hi zusammen,

    bin bezüglich der Performance einer SQL Abfrage ziemlich skeptisch, ich kann mir nicht vorstellen, dass es nicht wesentlich schneller ginge.
    Mein DBMS benutze ich mySQL 5.051b, das Schema der betroffenen Tabellen sieht so aus:

    Tabelle files:
id         int(10) PRIMARY KEY,
file_type  smallint( 5 ) NOT NULL,
file_name  TEXT NOT NULL,
start_time DATETIME NOT NULL,
stop_time  DATETIME NOT NULL

Tabelle feature_names:
id           int(10) PRIMARY KEY,
feature_type smallint(5) NOT NULL,
alias_id     int(10) NOT NULL,  // Fremdschlüssel für aliases.id
feature_name TEXT

Tabelle aliases:
id          int(10) PRIMARY KEY,
alias_name  TEXT NOT NULL

Tabelle counter:
id          int(10) PRIMARY KEY,
file_id     int(10) NOT NULL,  // Fremdschlüssel für files.id
feature1_id int(10) NOT NULL,  // Fremdschlüssel für feature_names.id
feature2_id int(10) NOT NULL,  // Fremdschlüssel für feature_names.id
counter     int(10) NOT NULL

Alle Spalten, die als Fremdschlüssel dienen sind zusätzlich indiziert.

    Bedeutung der einzelnen Tabellen:
    files enthältoffensichtlich Datensätze mit Dateiinformationen, file_type sei mal egal für dieses Beispiel.

    feature_names enthält Namen einzelner Attribute und eine ID für einen Alias. Wenn die alias_id ungleich 0 ist soll statt des feature_names der Name aus der Tabelle feature_aliases benutzt werden.

    feature_aliases enthält Alias Namen für einzelne Features, damit ist es möglich, unterschiedliche Features zu einem Namen zusammenzufassen

    counter enthält Zählerstände je Datei/Merkmalkombination. Jeder Zählerstand besteht aus zwei Feature IDs, die jeweils ein Merkmal referenzieren. Jede Datei hat bis zu 160 Zählerstände.

    Anzahl der Einträge je Tabelle:
    files: 400 Einträge
    feature_names: 13 Einträge
    feature_aliases: 0 Einträge
    counter: 11640 Einträge

    Folgende SQL Abfrage braucht 70(!) Sekunden, sie soll die Summe der Zählerstände je Merkmalkombination/Datei zurückgeben:

    select 
   F.file_name as filename,         // Dateiname
   feat1.name as defect_name1,      // Name Merkmal 1, Ergebnis eines sub select 
   feat2.name as defect_name2,      // Name Merkmal 2, Ergebnis eines sub select
   sum( D.defect_count) as defects  // Summe Zählerstand
from
  files F,
  defects D,
(
  select                            // Union bilden aus Merkmalnamen ohne Alias 
      N.id as id,                   // und Merkmalnamen mit Alias, je nach id
      N.name as name                // entweder den Merkmalnamen oder den
   from                             // Aliasnamen benutzen
      feature_names N
  where
      N.alias_id = 0 AND
      N.feature_type = 0            // Feature1 betrachten
  union select
      N.id as id,
      A.name as name
   from
      feature_names N,
      feature_aliases A
   where
      N.alias_id = A.id
) feat1,
(
  select                            // analog zu oben, nur dieses Mal für
      N.id as id,                   // Merkmal 2
      N.name as name
   from
      feature_names N
  where
      N.alias_id = 0 AND
      N.feature_type = 1            // id für Merkmal 2
  union select
      N.id as id,
      A.name as name
   from
      feature_names N,
      feature_aliases A
   where
      N.alias_id = A.id
) feat2
where
   D.feature1_id = feat1.id AND
   D.feature2_id = feat2.id
group by
   F.id,
   D.feature1_id,
   D.feature2_id

    70 Sekunden sind auf keinen Fall zumutbar, ich denke, dass die Anweisung eventuell schlecht formuliert ist.
    Bin für jede Hilfe dankbar.

    0

  • Mit der GROUP BY kann das IMO gar nicht funktionieren, denn du selektierst Spalten die nicht in GROUP BY auftauchen, und auch ohne Aggregatfunktion verwendet werden -> darf normal nicht gehen.

    Und das ganze GROUP BY ist IMO schmarrn.

    0
  • Y

    In mysql geht das durchaus. Aber das Ergebnis einer solchen Gruppierung ist in den allermeisten Fällen nicht das, was gewünscht ist. Vor dem folgenden Code-Quote eine Frage: Was genau möchtest du mit deiner 70-Sekundenabfrage erreichen, und wie sieht dein 70-Sekundenergebnis (auszugsweise) aus?

    mysql> CREATE DATABASE test2;
Query OK, 1 row affected (0.00 sec)

mysql> use test2;
Database changed
mysql> CREATE TABLE toast (
    -> id INT UNSIGNED NOT NULL PRIMARY KEY auto_increment COMMENT 'Primaerschluessel',
    -> wertA INT DEFAULT 0 COMMENT 'Erste Zahl',
    -> wertB INT DEFAULT 0 COMMENT 'Zweite Zahl',
    -> INDEX `zahlen`(`wertA`,`wertB`)
    -> ) ENGINE InnoDB COMMENT 'Nur ein toastertest';
Query OK, 0 rows affected (0.26 sec)

mysql> INSERT INTO toast (wertA,wertB) VALUES (1,1),(1,2),(1,3),(2,2),(2,7),(2,1);
Query OK, 6 rows affected (0.08 sec)
Datensätze: 6  Duplikate: 0  Warnungen: 0

mysql> SELECT * FROM toast;
+----+-------+-------+
| id | wertA | wertB |
+----+-------+-------+
|  1 |     1 |     1 |
|  2 |     1 |     2 |
|  3 |     1 |     3 |
|  6 |     2 |     1 |
|  4 |     2 |     2 |
|  5 |     2 |     7 |
+----+-------+-------+
6 rows in set (0.00 sec)

mysql> SELECT wertA,wertB FROM toast GROUP BY wertA;
+-------+-------+
| wertA | wertB |
+-------+-------+
|     1 |     1 |
|     2 |     1 |
+-------+-------+
2 rows in set (0.00 sec)

mysql> SELECT wertA,wertB FROM toast GROUP BY wertB;
+-------+-------+
| wertA | wertB |
+-------+-------+
|     1 |     1 |
|     1 |     2 |
|     1 |     3 |
|     2 |     7 |
+-------+-------+
4 rows in set (0.00 sec)

mysql> SELECT id FROM toast GROUP BY wertB;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  5 |
+----+
4 rows in set (0.00 sec)

    Wie gesagt, solche "GROUP-BY-ohne Aggregatsfunktion"-Geschichten liefern eher nicht das, was man haben möchte.

    Aber noch zwei Andere Fragen:

    • Welche Datenbank-Engine benutzt du?
    • Würdest du mir die Ausgabe von SHOW CREATE TABLE <tabellenname>; (ohne Header) posten?

    70 Sekunden sind wirklich nicht annehmbar bei der Datenmenge.

    0
  • Z

    Was bitte ist das Ziel?

    0

  • *facepalm*

    Was das Vergessen einer where-Klausel an Performanceeinbruch nach sich ziehen kann ist schon erstaunlich.

    Da Hustbär aber sagte, dass das ganze Statement nicht SQL konform sei beschreib´ ich das Problem mal ausführlicher.
    Ein Messgerät erzeugt Messdaten und schreibt sie in eine Datei, wobei jedes Messdatum aus zwei Elementen aus jeweils einer Menge besteht. Nennen wir Eigenschaft 1 mal F und Eigenschaft 2 G. F kann einen der Werte {f1,f2,..., f10} annehmen, G kann einen der Werte {g1, g2,..., g16} annehmen. Während der Messung können Messwerte der gleichen Merkmalskombination mehrfach auftreten, also wird noch ein Zählerstand benötigt. In der Praxis werden es einige tausend Dateien werden.
    Das db Schema besteht aus einer Tabelle für die Dateidaten (id, name, start, ende), Merkmalnamen (id, alias_id, name), Aliasnamen für Merkmale (id, name) und Zählerstände (id, file_id, feature1_id, feature2_id, counter).
    Die Anforderung ist, die Summe aller Merkmalkombinationen je Datei zu bilden, allerdings kommt hinzu, dass statt des tatsächlichen Merkmalnamens ein Name aus einer Aliastabelle benutzt werden soll. Die Bestimmung des effektiven Merkmalnamens erfolgt in den beiden sub-selects feat1 und feat2.

    Hier die gewünschten CREATE TABLE Anweisungen:

    CREATE TABLE `files` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `file_type` int(10) unsigned NOT NULL,
  `file_name` text NOT NULL,
  `start_time` datetime NOT NULL,
  `stop_time` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

CREATE TABLE `feature_names` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `feature_type` smallint(5) NOT NULL,
  `alias_id` int(10) unsigned NOT NULL,
  `name` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `FEATURE_ALIAS_ID` (`alias_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

CREATE TABLE `feature_aliases` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC'

CREATE TABLE `defects` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `file_id` int(10) unsigned NOT NULL,
  `feature1_id` smallint(5) unsigned NOT NULL,
  `feature2_id` smallint(5) unsigned NOT NULL,
  `defect_count` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `DEFECT_FILE_ID` (`file_id`),
  KEY `DEFECT_FEATURE2_ID` (`feature2_id`),
  KEY `DEFECT_FEATURE1_ID` (`feature1_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

    Die folgende Abfrage läuft in 0.05 Sekunden und scheint die richtigen Ergebnisse zu liefern

    select
   F.id,
   F.file_name,
   F1.name,
   F2.name,
   sum( D.defect_count )
from
   files F,
   defects D,
(
   select
      N.id,
      N.name
   from
      feature_names N
   where
      N.alias_id = 0 AND
      N.feature_type = 0
   union select
      N.id,
      A.name
   from
      feature_names N,
      feature_aliases A
   where
      N.alias_id = A.id AND
      N.feature_type = 0
) F1,
(
   select
      N.id,
      N.name
   from
      feature_names N
   where
      N.alias_id = 0 AND
      N.feature_type = 1
   union select
      N.id,
      A.name
   from
      feature_names N,
      feature_aliases A
   where
      N.alias_id = N.id AND
      N.feature_type = 1
) F2
where
   [b]D.file_id = F.id AND[/b]
   D.feature1_id = F1.id AND
   D.feature2_id = F2.id
group by
   F.id,
   F.file_name,
   F1.name,
   F2.name

    Ist daran noch was auszusetzen? Weitere Erklärungen notwendig? Ich bedanke mich auf jeden Fall schon einmal für die Hinweise.

    0
  • Z

    DocShoe schrieb:

    Die Anforderung ist, die Summe aller Merkmalkombinationen je Datei zu bilden, allerdings kommt hinzu, dass statt des tatsächlichen Merkmalnamens ein Name aus einer Aliastabelle benutzt werden soll.

    Irgendwie überkommt mir das Gefühl, dass du mir nicht alles erzählt hast, schielen auf deine Abfrage - wie auch immer deine Abrage enträtseln ich nicht!

    Meinst du Summe oder Liste?

    Bei Summe versteh ich es so, dass du zu jeder Dateiname die Anzahl der Kombinationen ausgeben willst, sind also aggregierte Daten.

    Datei1 6
    Datei2 5

    Dazu ist die SQL Anfrage recht leicht.

    SELECT
	file_name AS Dateiname,
	COUNT(SELECT * FROM defects WHERE file_id = f.id ) AS AnzahlKombination
FROM files f

    Wenn du jetzt noch die Featurenamen einbauen willst, sind die Daten nicht mehr aggregierte, aber als Liste dargestellt.

    SELECT
	(SELECT id FROM files WHERE id = d.file_id) AS Id,
	(SELECT file_name FROM files WHERE id = d.file_id) As Dateiname,
	(SELECT name FROM feature_aliases WHERE id = (SELECT alias_id WHERE feature_name.id = d.feature1_id)) AS Featurename1,
	(SELECT name FROM feature_aliases WHERE id = (SELECT alias_id WHERE feature_name.id = d.feature2_id)) AS Featurename2	
FROM defects d

    Hoffe es hilft etwas!

    0

  • Habe mich in meinem letzten Posting nicht ganz klar ausgedrückt, die Sache mit den Merkmalnamen ist folgende:
    Wenn in der Tabelle feature_names.alias_id 0 ist soll feature_names.name zurückgegeben werden, ansonsten feature_aliases.name wo feature_aliases.id = feature_names.alias_id ist. Das hatte ich im ersten Posting schon erklärt und dachte, das sei ersichtlich. Hätte ich wohl besser noch dazugeschrieben.
    Der Thread läuft langsam aus dem Ruder, da es inzwischen nicht mehr um die Performance der Abfrage geht, sondern um die Abfrage selbst. Ich denke, ich beenden das hier besser und ich starte einen neuen Thread, der sich mit der Abfrage selbst beschäftigt. Das Performanceproblem habe ich ja lösen können.

    0
  • Z

    Seit wann ist das trennbar?

    0

  • Zeus schrieb:

    Seit wann ist das trennbar?

    Was ist nicht trennbar?

    0
Anmelden zum Antworten