Nachdem ich mich in Teil 1 dieser Serie dem Thema „Servertweaks“ gewidmet hatte, soll es in diesem Artikel um Datenbankabfragen gehen. Die Abfragen der Daten aus einer Datenbank ist der Flaschenhals vieler Webprojekte und bietet oft viel Optimierungspotential. Ich fasse hier einfach mal die Schritte zusammen die ich Leuten in der Regel empfehle wenn Sie Probleme mit der Geschwindigkeit ihrer DB-Abfragen haben. Ich orientiere mich in diesem Artikel an einer MySQL-Datenbank, die meisten Tipps sollten jedoch auf andere relationale Datenbanksysteme übertragbar sein.
Optimierung der Queries und Nutzung von Indizes
Für die meisten Leser wird dieser Schritt trivial klingen, ich möchte ihn aber dennoch mit aufnehmen da ich es immer wieder erlebe das in Tabellen Indizes fehlen oder falsch gesetzt sind. Zunächst gilt es heraus zu finden welche Queries in einem Projekt langsam sind um diese dann optimieren zu können. In den meisten Fällen wird der Entwickler eines Projektes dies ohnehin wissen, falls nicht ist ratsam das MySQL Slow-Query-Log zu aktivieren. Sobald man die Problemfälle gefunden hat, gilt es diese zu optimieren. Als erstes sollte man mithilfe des „EXPLAIN-Statements“ die Queries analysieren um z.B. festzustellen ob Indizes (sofern vorhanden) auch benutzt werden. Neben der Nutzung von Indizes kann man natürlich auch die Queries selbst optimieren. Da dies ein sehr komplexes Thema ist und den Rahmen dieses Artikels sicherlich sprengen würde empfehle ich neben dem üblichen Try & Error Verfahren die Lektüre des MySQL Performace Blogs oder ähnlicher Seiten.
De-Normalisierung der Datenbankstruktur
Wir alle haben gelernt Datenbanken bzw. Tabellen zu normalisieren und dagegen ist in der Regel auch nichts einzuwenden. Schließlich hilft die Normalisierung z.B. dabei redundante Datenhaltung zu vermeiden. Wenn es jedoch darum geht Datenbankabfragen möglichst schnell zu machen kann eine bis ins letzte normalisierte Struktur auch hinderlich sein. Ein Beispiel: Ich habe ein Projekt mit einer Datenbank in der mehrere Millionen Datensätze gespeichert sind. Jeder dieser Datensätze ist einer von ca. 100 Kategorien zugeordnet. Es gibt nun eine Tabelle mit den Kategorien und jeder Datensatz ist einer dieser Kategorien zugeordnet (1:n). Die Kategorie Tabelle enthält ausserdem Information darüber wie oft eine Kategorie zugeordnet ist. Um nun allerdings zu vermeiden das bei jeder Abfragen auf einen Datensatz + entsprechender Kategorie ein JOIN gemacht werden muss sind die Kategorien zusätzlich in der Tabellen mit den eigentlichen Datensätzen gespeichert. Das entspricht nicht den Regeln der Normalisierung spart jedoch ein JOIN und ist somit schneller. Es ist sinnvoll darauf zu achten, dass die Daten die in einem Projekt am häufigsten benötigt werden auf möglichst wenige Tabellen gebündelt sind. (Ohne dabei zu übertreiben und schließlich alle Daten in einer Tabelle zu verwalten – es geht hier um Optimierungspotential)
Key-Value Stores verwenden
Trotz aller Optimierungen sind die Abfragen auf die Datenbank möglicherweise immer noch zu langsam oder zu viel. In diesem Fall kann man sich folgendes überlegen: Habe ich die Möglichkeit einen Teil meiner Daten „zwischenzuspeichern“. So kommt es zum Beispiel oft vor, dass eine Datenbank zwar Millionen von Datensätzen umfasst, 90% der Abfagen jedoch auf auf einen immer gleichen weitaus kleineren Teil dieser Daten zielen. Ein simples Beispiel: Wenn man Daten in einer Liste mit mehreren Seiten auf einer Webseite darstellt, so werden die ersten Seiten in der Regel weitaus häufiger aufgerufen als die Seiten weiter hinten. In solchen Fällen bietet es sich an diese häufig aufgerufenen Daten zwischenzuspeichern und somit Datenbankabfragen zu „sparen“. Hierzu bieten sich sog. Key-Value-Stores wie z.B. memcache, xcache oder auch redis.io an. (Günter schrieb dazu auch schonmal was: Killing MySQL Slow Queries with Xcache)
Verwendung von Sphinx (o.Ä.)
Trotz aller Optimierungen wird es dennoch Fälle geben die eine relationale Datenbank an ihre Grenzen bringt. Das bekannteste Beispiel sind wohl Such-Queries. Diese sind in der Regel auch nach aller Optimierung noch immer relativ langsam. Doch auch hier gibt es Mittel und Wege, z.B. Sphinx. Sphinx ist (einfach ausgedrückt) ein eigener, zusätzlicher Suchserver. Sphinx erzeugt dabei eigene Such-Indizes der Datenbank und durchsucht diese statt direkt auf die Datenbank zuzugreifen. Das durchsuchen dieser Indizes ist in der Regel um ein vielfaches schneller als das durchsuchen der MySQL-Tabellen. Somit kann man mithilfe von Sphinx auch seine SQL-Queries weiter optimieren indem man beide Systeme weiter kombiniert. So kann man z.B. eine Suchanfrage zunächst an den Sphinx-Server schicken um die Primärschlüssel der relevanten Datensätze für diese Suchanfrage zu erhalten. So kann man anschließend gezielt diese Datensätze aus der Datebank holen, ohne ein Such-Query ausführen zu müssen.
Ich habe in diesem Artikel die einzelnen Themen nur kurz angerissen, hoffe aber dennoch, dass ich ein paar Denkanstöße geben konnte. Bei Bedarf gehen ich auf einzelne Themen (z.B. die Suche mit Hilfe von Sphinx) genauer ein. Einfach die entsprechenden Fragen hier in den Kommentaren stellen.