Skriptsprachen (PHP, Javascript, etc.) - Forum

Derzeit sind Sie als Gast in unserem Forum aktiv. Für das Schreiben registrieren Sie sich bitte. Unser Forum ist eine Austauschplattform für Webworker zum Kommunizieren, Helfen, Informieren und Hilfe finden. Auf der rechten Seiten finden Sie eine Forenübersicht über alle Bereiche des Webmaster-Forums. Unterhalb finden Sie alle aktuellen Themen.

Diskutieren Sie hier über die serverseitige Skriptsprachen PHP und Python und über dynamische Webseitenerstellung mit Javascript und Ajax.


Oliver H
Beiträge: 99

PN schreiben
Profil ansehen
User ist offline

[SQL]SELECT-Abfrage optimieren

Link zur Antwort auf "[SQL]SELECT-Abfrage optimieren" von Oliver H Oliver H, am Monday den 14.04.2008 um 09:33:45

Hallo.

Ich habe die Aufgabe erhalten, die SQL-Anweisungen auf einer stark frequentierten Internetseite zu optimieren. Mein Problem ist eine einzelne SELECT-Abfrage anzupassen. Diese funktioniert bei kleinen Datenmengen (bis 50.000 Datensätze pro involvierte Tabelle) bis auf kleine Performanceprobleme gut. Allerdings haben wir in einer Tabelle von den 5 ca. 600.000 Datensätze. Eine Übersicht über die jeweiligen Tabellen findet ihr hier:

Tabellenübersicht  
Text
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
Tabelle: forumthread (ENGINE=InnoDB DEFAULT CHARSET=latin1)
===========================================================
Datensätze: ca. 56.000

|---------------------------------------------------------------|
| Spalte      | Datentyp u. Extras                              |
|-------------+-------------------------------------------------|
| Id          | int(10) unsigned NOT NULL auto_increment        |
| Title       | text NOT NULL                                   |
| AuthorId    | int(10) unsigned NOT NULL default '0'           |
| Timestamp   | datetime NOT NULL default '2000-01-01 00:00:00' |
| TopicId     | int(10) unsigned NOT NULL default '0'           |
| ViewCount   | int(10) unsigned NOT NULL default '0'           |
| Locked      | tinyint(1) unsigned NOT NULL default '0'        |
| SolvedState | int(10) unsigned NOT NULL default '0'           |
| LngId       | int(10) unsigned default NULL                   |
|---------------------------------------------------------------|

  PRIMARY KEY  (`Id`)
  KEY `FK_forumthread_TopicId` (`TopicId`)
  KEY `FK_forumthread_AuthorId` (`AuthorId`)
  KEY `Index_Timestamp` (`Timestamp`)
  KEY `FK_forumthread_LngId` (`LngId`)
  CONSTRAINT `FK_forumthread_AuthorId` FOREIGN KEY (`AuthorId`) REFERENCES `user` (`Id`)
  CONSTRAINT `FK_forumthread_LngId` FOREIGN KEY (`LngId`) REFERENCES `lng` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
  CONSTRAINT `FK_forumthread_TopicId` FOREIGN KEY (`TopicId`) REFERENCES `forumtopic` (`Id`) ON DELETE CASCADE



Tabelle: forumthreadentry (ENGINE=InnoDB DEFAULT CHARSET=latin1)
================================================================
Datensätze: ca. 620.000

|---------------------------------------------------------------|
| Spalte      | Datentyp u. Extras                              |
|-------------+-------------------------------------------------|
| Id          | int(10) unsigned NOT NULL auto_increment        |
| Text        | text NOT NULL                                   |
| AuthorId    | int(10) unsigned NOT NULL default '0'           |
| Timestamp   | datetime NOT NULL default '0000-00-00 00:00:00' |
| ThreadId    | int(10) unsigned NOT NULL default '0'           |
| ImageId     | int(10) unsigned default NULL                   |
| AllowHtml   | tinyint(1) unsigned NOT NULL default '0'        |
|---------------------------------------------------------------|

  PRIMARY KEY  (`Id`)
  KEY `FK_forumthreadentry_AuthorId` (`AuthorId`)
  KEY `FK_forumthreadentry_ThreadId` (`ThreadId`),
  KEY `Index_TimestampThreadId` (`Timestamp`,`ThreadId`)
  KEY `FK_forumthreadentry_ImageId` (`ImageId`)
  CONSTRAINT `FK_forumthreadentry_AuthorId` FOREIGN KEY (`AuthorId`) REFERENCES `user` (`Id`)
  CONSTRAINT `FK_forumthreadentry_ImageId` FOREIGN KEY (`ImageId`) REFERENCES `document` (`Id`)
  CONSTRAINT `FK_forumthreadentry_ThreadId` FOREIGN KEY (`ThreadId`) REFERENCES `forumthread` (`Id`) ON DELETE CASCADE



Tabelle: user (ENGINE=InnoDB DEFAULT CHARSET=utf8)
==================================================
Datensätze: ca. 17.000

|------------------------------------------------------------------|
| Spalte         | Datentyp u. Extras                              |
|----------------+-------------------------------------------------|
| Id             | int(10) unsigned NOT NULL auto_increment        |
| LoginName      | text NOT NULL                                   |
| PassHash       | int(10) unsigned NOT NULL default '0'           |
| Active         | datetime NOT NULL default '0000-00-00 00:00:00' |
| ImageId        | int(10) unsigned NOT NULL default '0'           |
| Signature      | int(10) unsigned default NULL                   |
| HomepageUrl    | int(10) unsigned default NULL                   |
| AIMAdress      | int(10) unsigned default NULL                   |
| EMailAdress    | int(10) unsigned default NULL                   |
| SiteId         | int(10) unsigned default NULL                   |
| Responsibility | int(10) unsigned default NULL                   |
| Prio           | int(10) unsigned default NULL                   |
| CreateDate     | int(10) unsigned default NULL                   |
| ApproveKey     | tinyint(1) unsigned NOT NULL default '0'        |
|------------------------------------------------------------------|

  PRIMARY KEY  (`Id`)
  UNIQUE KEY `Index_Name` USING BTREE (`LoginName`,`SiteId`)
  KEY `FK_user_ImageId` (`ImageId`)
  KEY `FK_user_SiteId` (`SiteId`)
  KEY `Index_Prio` (`Prio`)
  CONSTRAINT `FK_user_ImageId` FOREIGN KEY (`ImageId`) REFERENCES `document` (`Id`) ON DELETE SET NULL ON UPDATE CASCADE
  CONSTRAINT `FK_user_SiteId` FOREIGN KEY (`SiteId`) REFERENCES `site` (`Id`)


Tabelle: document (ENGINE=InnoDB DEFAULT CHARSET=utf8)
======================================================
Datensätze: ca. 97.000

Tabelleninhalt nicht relevant


Tabelle: lng (ENGINE=InnoDB DEFAULT CHARSET=utf8)
=================================================
Datensätze: 3

Tabelleninhalt nicht relevant

Die beiden Tabellen "document" und "lng" sind nicht für das Problem verantwortlich und wurden daher von mir nur der Vollständigkeit wegen (ohne Details) angegeben.

Die alte Abfrage versagte. Durch verschiedene Testreihen (entfernen einzelner Bestandteile aus der Abfrage) konnte ich feststellen dass das Problem folgendes ist:

Und zwar muss aus der Tabelle "user" der Benutzername (LoginName) mit Hilfe der in der Tabelle "forumthreadentry" eingetragenen Benutzerid (AuthorId) ausgelsen werden. Dies dauert einfach viel zu lange (bis zu 3 Minuten).

Noch zur weiteren Erklärung: Diese Abfrage zeigt die Übersicht der in einem Topic verfassten Threads an. Diese Threads werden nach der letzten geschriebenen Antwort chronologisch absteigend sortiert. Pro Seite werden 30 Threads angezeigt (dieser Umstand wirkt sich aber nicht auf die Zeit aus. Die Zeit bleibt gleich ob mit oder ohne LIMIT).

Außerdem wäre es schön die ganzen SUBSELECTS aus der Abfrage zu verbannen (sofern möglich).

Hier die alte Abfrage und meine derzeit aktuellste:

Alte Abfrage  
sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
SELECT
  forumthread.Id
  ,forumthread.Locked
  ,forumthread.SolvedState
  ,forumthread.Timestamp
  ,forumthread.Title
  ,forumthread.ViewCount
  ,forumthreadentry.AllowHtml
  ,forumthreadentry.ImageId
  ,forumthreadentry.Text
  ,forumthreadentry.Timestamp
  ,(SELECT
      COUNT(forumthreadentryCount.Id)
    FROM
      forumthreadentry AS forumthreadentryCount
    WHERE
      forumthreadentryCount.ThreadId = forumthread.Id
    LIMIT 1) AS forumthreadentryCount
  ,(SELECT
      COUNT(forumthreadentryCount2.Id)
    FROM
      forumthreadentry AS forumthreadentryCount2
    WHERE
      forumthreadentryCount2.AuthorId = @userId
    AND
      forumthreadentryCount2.ThreadId = forumthread.Id
   ) AS forumthreadentryCount2
  ,forumthreadentryUser.Id
  ,forumthreadentryUser.LoginName
  ,forumthreadUser.Id
  ,forumthreadUser.LoginName
  ,forumthread.LngId AS LngId
  ,flagDocument.Id AS LngFlagId
  ,flagDocument.Extension AS LngFlagExtension
  ,forumthreadentrylast.Timestamp AS LastEntryTimeStamp

FROM
  forumthread

LEFT JOIN
  forumthreadentry AS forumthreadentrylast
ON
  forumthreadentrylast.Id = (SELECT
                               forumthreadentryUserSelect.Id
                             FROM
                               forumthreadentry AS forumthreadentryUserSelect
                             WHERE
                               forumthreadentryUserSelect.ThreadId = forumthread.Id
                             ORDER BY
                               forumthreadentryUserSelect.Timestamp DESC
                             LIMIT 1)

LEFT JOIN
  forumthreadentry
ON
  forumthreadentry.ThreadId = forumthread.Id

LEFT JOIN
  user AS forumthreadentryUser
ON
  forumthreadentryUser.Id = forumthreadentrylast.AuthorId

JOIN
  user AS forumthreadUser
ON
  forumthreadUser.Id = forumthread.AuthorId

LEFT JOIN
  lng AS forumthreadLng
ON
  forumthreadLng.Id = forumthread.LngId

LEFT JOIN
  document AS flagDocument
ON flagDocument.Id = forumthreadLng.SmallFlagImage

WHERE
  forumthread.TopicId = @forumTopicId

GROUP BY
  forumthread.Id

ORDER BY
  forumthreadentrylast.Timestamp DESC
  ,forumthread.Timestamp DESC

LIMIT
  0
  ,30;
Aktuellste Abfrage  
sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
SELECT
  forumthread.Id
  ,forumthread.Title
  ,forumthread.Locked
  ,forumthread.SolvedState
  ,forumthread.Timestamp AS Open_Timestamp
  ,MAX(forumthreadentry.Timestamp) AS LastPost_Timestamp
  ,forumthreadentry.AuthorId
  ,user.LoginName
  ,forumthread.ViewCount
  ,f_countthreads(forumthreadentry.ThreadId) AS counter2
  ,forumthreadentry.AllowHtml
  ,forumthreadentry.ImageId
  ,forumthread.LngId AS LngId
  ,document.Id AS LngFlagId
  ,document.Extension AS LngFlagExtension
FROM
  forumthreadentry

LEFT JOIN
  forumthread
ON
  forumthread.Id = forumthreadentry.ThreadId

LEFT JOIN
  user
ON
  user.Id = forumthreadentry.AuthorId

LEFT JOIN
  lng
ON
  lng.Id = forumthread.LngId

LEFT JOIN
  document
ON
  document.Id = lng.SmallFlagImage

WHERE
  forumthread.TopicId = @forumTopicId
GROUP BY
  forumthreadentry.ThreadId

ORDER BY
  LastPost_Timestamp DESC

LIMIT
  0
  ,30;

Zu dieser Abfrage gehört noch folgende Funktion:

Funktion f_countthreads  
sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
DELIMITER $$

DROP FUNCTION IF EXISTS `f_countthreads` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `f_countthreads`(ThreadId INT) RETURNS int(11)
BEGIN
  DECLARE counter INT;
  SELECT
           COUNT(forumthreadentry.ThreadId) INTO counter
         FROM
           forumthreadentry
         WHERE
           forumthreadentry.ThreadId = ThreadId
         LIMIT 1;
  RETURN counter;
END $$

DELIMITER ;

Ich hoffe das ich genügend Informationen zur Verfügung gestellt habe und das ihr mir helfen könnt.

Gruß Oli

Insgesamt 1 mal bearbeitet, zuletzt am 14.04.2008 um 09:35:08

Jan Pieper
Beiträge: 249

PN schreiben
Profil ansehen
User ist offline
Link zur Antwort auf "[SQL]SELECT-Abfrage optimieren" von Jan Pieper Jan Pieper, am Monday den 14.04.2008 um 13:43:42

Die Subselects sind sicher das Problem an dem ganzen. Auch die Auslagerung in eine Funktion bringt da sicher keine Verbesserung. Mach da lieber zwei Queries anstatt dich da mit einem Query rumzuplagen und vereine die Ergebnisse oder so. Subselects sind immer fies, da, soweit ich weiß, diese Subselects für JEDES Ergebnis ausgeführt werden. Das heisst also bei 56.000 Ergebnissen wir der Subselect bzw. die Funktion auch 56.000 mal ausgeführt.

Bei der Funktion ansich frag ich mich jedoch, wieso du ein COUNT mit nem LIMIT 1 machst. Da kannst du ja max. 0 bzw. 1 als Ergebnis bekommen. Und als Rückgabe int(11)? Du kannst nur einen positiven Wert zurückbekommen, also sollte UNSIGNED INT(10) auch reichen.

Aber ich bin sowieso in diesem Fall gegen einen Subselect bzw. einer Funktion.


Oliver H
Beiträge: 99

PN schreiben
Profil ansehen
User ist offline
Link zur Antwort auf "[SQL]SELECT-Abfrage optimieren" von Oliver H Oliver H, am Monday den 14.04.2008 um 13:48:01

Klingt alles ganz vernünftig. Allerdings soll das alles in einer Abfrage laufen und nicht in 2 Querys.

Zu den SUBSELECTS und Funktionen. Ich bin auch kein Fan von Subselects. Deshalb möche ich die auch eliminieren. Ich habe als Alternaive zur Funktion noch eine VIEW die ich über einen JOIN in die Query einbauen kann.

VIEW  
sql
1
2
3
4
DROP VIEW IF EXISTS `lastentrycount`;
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `lastentrycount`
  AS
    select `forumthreadentry`.`ThreadId` AS `ThreadId` from `forumthreadentry`;

Allerdings löst das nicht mein Problem mit der Ausführzeit...

Hier mal ein Screenshot von der Abfrage in Verbindung mit EXPLAIN.

Abfrage mit Explain

Insgesamt 5 mal bearbeitet, zuletzt am 15.04.2008 um 08:42:13

Jan Pieper
Beiträge: 249

PN schreiben
Profil ansehen
User ist offline
Link zur Antwort auf "[SQL]SELECT-Abfrage optimieren" von Jan Pieper Jan Pieper, am Monday den 14.04.2008 um 18:56:07

Das EXPLAIN sieht egentlich garnicht so übel aus, wie du es beschreibst. Da habe ich jedoch schon fiesere aber schnellere Queries gesehen. Vielleicht liegt es einfach am Server, dass der so lahm ist? Ist das vllt. ein 386er? *g*

Ich finde jedoch, dass es keinen Unterschied macht, ob du einen Subquery hast, oder ob du eine Funktion aufrufst in der ein Query gemacht wird. Kommt für mich aufs gleiche raus.

Aber hast du eigen bestimmten Grund, wieso du es mit einem Query lösen musst?


Oliver H
Beiträge: 99

PN schreiben
Profil ansehen
User ist offline
Link zur Antwort auf "[SQL]SELECT-Abfrage optimieren" von Oliver H Oliver H, am Tuesday den 15.04.2008 um 09:11:33

Also ich würde behaupten dass es nicht am Server liegt. Ist ein eigener Server auf dem ausschliesslich unsere Internetseite läuft. Dieser hat folgende Konfiguration:

CPU
2 x Opteron 2216
CPU-Details
2 x 2 x 2,4 GHz
Arbeitsspeicher
8 GB
RAID1
Hardware
Festplatte
2 x 500 GB

Ich möchte dass in einem Query und ohne Subselects haben weil es schöner und komfortabler ist.

Insgesamt 1 mal bearbeitet, zuletzt am 15.04.2008 um 09:12:59

Jan Pieper
Beiträge: 249

PN schreiben
Profil ansehen
User ist offline
Link zur Antwort auf "[SQL]SELECT-Abfrage optimieren" von Jan Pieper Jan Pieper, am Tuesday den 15.04.2008 um 09:37:29

Baust du auch Häuser die kein Erdgeschoss haben, weil sie dann schöner und vllt. auch für Einbrüche sicherer sind? Nein.

Hier sollte nicht die Frage sein, ob der Query schön ist oder sonstwas. Du hast das Problem, dass dieser zu langsam ist und somit brauchst du eine Lösung, ob der Query nun schön ist oder nicht.

Bau dir das vllt. doch alles erstmal in zwei Queries. Wenn das schnell ist, dann gehst du hin und versuchst es mit einem Query zu toppen.


Oliver H
Beiträge: 99

PN schreiben
Profil ansehen
User ist offline
Link zur Antwort auf "[SQL]SELECT-Abfrage optimieren" von Oliver H Oliver H, am Tuesday den 15.04.2008 um 09:43:08

Ja hast ja schon recht :-P *fg*

Na ich werd das mal mit den 2 Querys probieren. Bin aber weiterhin für neue Ideen offen...


Jan Pieper
Beiträge: 249

PN schreiben
Profil ansehen
User ist offline
Link zur Antwort auf "[SQL]SELECT-Abfrage optimieren" von Jan Pieper Jan Pieper, am Tuesday den 15.04.2008 um 18:19:20

Da du bisher noch nicht gemeckert hast, gehe ich mal davon aus, dass sich zwei Queries bewert haben? :)


Oliver H
Beiträge: 99

PN schreiben
Profil ansehen
User ist offline
Link zur Antwort auf "[SQL]SELECT-Abfrage optimieren" von Oliver H Oliver H, am Wednesday den 16.04.2008 um 08:47:20

Noch hatte ich keine möglichkeit dies zu testen da ich die datenbank und asp.net neu installieren musste... werde das aber vermutlich heute schaffen...


Oliver H
Beiträge: 99

PN schreiben
Profil ansehen
User ist offline
Link zur Antwort auf "[SQL]SELECT-Abfrage optimieren" von Oliver H Oliver H, am Thursday den 17.04.2008 um 20:02:08

So Leute hab selber eine Lösung gefunden. Muß dazu das Schema der Datenbank ändern.


Jan Pieper
Beiträge: 249

PN schreiben
Profil ansehen
User ist offline
Link zur Antwort auf "[SQL]SELECT-Abfrage optimieren" von Jan Pieper Jan Pieper, am Friday den 18.04.2008 um 10:07:53

In wiefern musstest du denn nun das Schema anpassen? Sind denn zwei Queries so langsam? Hast du es denn nun mit einem einzelnen Query geschafft?