Effiziente Datenbankoptimierung & Reduzierung von Datenbankabfragen

In den vergangenen Monaten haben wir uns relativ viel mit effizienter Datenbankoptimierung und der Reduzierung von Datenbankabfragen beschäftigt. Der Hintergrund war, dass durch die Einführung der Mehrsprachigkeit bei TutKit.com auf aktuell 23 Sprachen die Datenbanken völlig neue Dimensionen angenommen haben und die Anforderungen an Leistung und Skalierbarkeit gestiegen sind. So haben wir einen Sprint zur Datenbankoptimierung und Reduzierung von Datenbankabfragen vorgenommen mit ganz erstaunlichen Ergebnissen.

Dabei sind wir durch die verschiedenen Seitentypen (Kategorieseiten, Produktseiten, Blogseiten, …) gegangen und haben systematisch die Datenbankabfragen optimiert und reduziert. Um nur einmal zwei Beispiele zu nennen: 

• Kategorieseiten konnten wir von 916 auf 16 Queries (Abfragen) reduzieren und die Server Running Time von 177ms auf 15ms. 
• Produktseiten konnten wir von 2064 Datenbankabfragen auf 157 reduzieren und die Server Running Time von 412ms auf 68ms.

Damit haben wir die Datenbankabfragen um 92 bis 98 % reduzieren können bei gleichzeitig enorm verbesserter Geschwindigkeit, die ebenso nur noch 8 bis 17 % der vorherigen Zeit benötigt. Was für ein Ergebnis!

Hier einmal ein Vorher-Nachher-Vergleich bei eingeschalteter Debugbar unseres Laravel-Projekts vom gleichen Seitentypen. Es war eine Produktseite, die sehr viele Inhalte enthalten hat:

Über 2.000 Abfragen waren schon brutal viel, zugegeben. Warum wir solche hohen Werte hatten, werde ich in einen anderen Blogbeitrag beleuchten, wenn es um die Arbeit mit anderen Agenturen, mittelmäßigem Know-how und Technical Debt geht. Unser Backend-Entwickler hat sich diese Seite vorgenommen und konnte bereits nach 1,5 Tagen der Optimierung folgendes Ergebnis erreichen:

Da es sich dabei um den Seitentypen der Produkte handelte, hatte es einen sehr großen Einfluss auf sehr viele Seiten bei uns. Gerade diese Seiten sollen durch eine hohe Performance sowohl in der User Experience überzeugen als auch für Google attraktiv sein, damit diese Seiten höher ranken. Wir sind mit dem Ergebnis außerordentlich zufrieden.

Gute Gründe für Datenbankoptimierung und Reduzierung von Abfragen

Datenbankoptimierung ist ein wesentlicher Bestandteil der Softwareentwicklung und des Refaktoring, der eine Vielzahl von Vorteilen bietet. Entwickler und Unternehmen, die große Projekte wie Online-Shops oder Portale betreuen, sollten diese Praxis ernst nehmen aus folgenden Gründen:

• Verbesserte Leistung und Geschwindigkeit: Optimierte Datenbanken ermöglichen schnellere Abfragen und kürzere Ladezeiten. Dies ist besonders wichtig für Anwendungen mit hohem Datenvolumen oder hoher Nutzeraktivität, da schnelle Reaktionszeiten die Benutzererfahrung erheblich verbessern.
• Skalierbarkeit:  Eine gut optimierte Datenbank kann besser mit zunehmenden Datenmengen und einer wachsenden Anzahl gleichzeitiger Nutzer umgehen. Dies stellt sicher, dass die Anwendung auch bei steigendem Datenvolumen effizient bleibt.
• Ressourceneffizienz: Durch die Reduzierung unnötiger Abfragen und die Verbesserung der Datenbankstruktur werden weniger Rechenressourcen benötigt. Dies führt zu geringeren Betriebskosten und einer effizienteren Nutzung der vorhandenen Hardware.
• Zuverlässigkeit und Stabilität: Optimierungen tragen dazu bei, Datenbankengpässe und -ausfälle zu verhindern. Dies ist besonders wichtig für geschäftskritische Anwendungen, bei denen Ausfallzeiten zu erheblichen finanziellen Verlusten führen können.
• Bessere Benutzererfahrung: Nutzer erwarten schnelle und reibungslose Interaktionen mit Anwendungen. Optimierte Datenbanken tragen dazu bei, dass Nutzer nicht durch langsame Ladezeiten oder verzögerte Antworten frustriert werden, was die Kundenzufriedenheit und -bindung erhöht.
• Wettbewerbsvorteil: In einem wettbewerbsintensiven Markt kann die Leistung einer Anwendung ein entscheidender Faktor sein. Unternehmen, die in Datenbankoptimierung investieren, können sich durch eine überlegene Anwendungsleistung von der Konkurrenz abheben, auch weil Google performante Websites einfach liebt.
• Fehler- und Problemreduktion: Durch das Identifizieren und Beheben ineffizienter Abfragen und Strukturen können potenzielle Fehlerquellen eliminiert werden. Dies führt zu einer stabileren Anwendung und reduziert den Wartungsaufwand.
• Kosteneinsparungen: Optimierungen können den Bedarf an zusätzlicher Hardware und Ressourcen reduzieren, was langfristig zu erheblichen Kosteneinsparungen führen kann. Effizientere Datenbankoperationen bedeuten auch weniger Ausgaben für Cloud-Dienste und Hosting.

Insgesamt trägt die Datenbankoptimierung wesentlich dazu bei, die Leistung, Zuverlässigkeit und Skalierbarkeit von Anwendungen und Projekten zu verbessern, was letztendlich zu einer besseren Benutzererfahrung, höheren Suchmaschinenrankings und niedrigeren Betriebskosten führt.

Wie sind wir nun genau vorgegangen? Hier gibt es eine Zusammenfassung!

Debugging- und Profiling-Tools in Programmiersprachen

Die Arbeit findet vor allem mit Debugging- und Profling-Tools statt. Debugging- und Profiling-Tools sind für Entwickler unerlässlich, um Probleme zu identifizieren und zu beheben sowie die Leistung von Anwendungen zu optimieren. Diese Tools bieten Einblicke in verschiedene Aspekte der Codeausführung und helfen Entwicklern sicherzustellen, dass ihre Anwendungen effizient und korrekt laufen. Eines der Tools, das wir in unserem Laravel-Projekt TutKit.com verwenden, ist Debugbar.

Wichtige Funktionen von Debugging- und Profiling-Tools

• Abfrageüberwachung: Tools wie Debugbar in Laravel protokollieren alle während einer Anfrage ausgeführten Datenbankabfragen und zeigen Details, Bindungen und Ausführungszeiten der Abfragen an. Dies hilft, langsame oder redundante Abfragen zu identifizieren, die optimiert werden können.
• Leistungsprofilierung: Diese Tools bieten eine Aufschlüsselung der Ausführungszeit einer Anfrage, einschließlich der Zeit für Datenbankoperationen, das Rendern von Ansichten und andere Prozesse. Dies hilft, Leistungsengpässe zu erkennen.
• Fehler- und Ausnahmeprotokollierung: Sie erfassen und protokollieren Fehler und Ausnahmen, die während der Codeausführung auftreten, und bieten detaillierte Stack-Traces und Fehlermeldungen, um Entwicklern zu helfen, Probleme schnell zu identifizieren und zu beheben.
• Routeninformationen: Für Webanwendungen zeigen diese Tools oft Informationen zur aktuellen Route an, einschließlich Routenparametern und Middleware, was nützlich für die Fehlerbehebung bei Routing-Problemen ist.
• Ansichts-Rendering: Sie zeigen an, welche Ansichten gerendert wurden und wie lange dies gedauert hat, was zur Optimierung der Ansichtsausführung beiträgt.

Allgemeiner Anwendungsfall

In jeder Programmiersprache umfasst der Workflow für die Verwendung eines Debugging- und Profiling-Tools typischerweise die folgenden Schritte

1. Installation und Konfiguration: Einrichtung des Tools in Ihrer Entwicklungsumgebung.
2. Ausführungsüberwachung: Ausführung Ihrer Anwendung und Verwendung des Tools zur Überwachung verschiedener Aspekte ihrer Ausführung.
3. Datenanalyse: Überprüfung der protokollierten Daten zur Identifizierung langsamer Abfragen, Leistungsengpässe und Fehler.
4. Optimierung und Debugging: Durchführung der notwendigen Änderungen zur Leistungsoptimierung und Behebung identifizierter Probleme.
5. Funktionstests und Bugfixing: Nach der Optimierung muss überprüft werden, ob alle notwendigen Inhalte und Funktionen erhalten geblieben sind

Beispiel: Laravel Debugbar

In unserem Laravel-Projekt verwenden wir die Debugbar, um unsere Anwendung zu überwachen und zu optimieren:

1. Abfragen anzeigen: Debugbar zeigt alle ausgeführten Abfragen, deren Ausführungszeit und Parameter an.
2. Langsame Abfragen identifizieren: Wir suchen nach Abfragen mit langen Ausführungszeiten und optimieren diese.
3. Abfrageanzahl überprüfen: Wir stellen sicher, dass keine unnötigen wiederholten Abfragen vorliegen, die auf ein N+1-Problem hinweisen könnten.
4. Leistungsprofilierung: Die Timeline-Funktion hilft uns zu sehen, wo die Ausführungszeit aufgewendet wird, sodass wir entsprechend optimieren können.

Detaillierte Analyse von Datenbankabfragen und -schemata

Im nächsten Schritt analysierten wir ähnliche Datenbankabfragen, identifizierten langsame und zeitaufwändige Abfragen, überprüften Datenbanktabellen ohne Indexe und überprüften unnötige Standardspalten. Diese umfassende Analyse ermöglichte es uns, die Datenbankleistung erheblich zu optimieren.

Tiefgehendes Debugging und Leistungsoptimierung bei Datenbanken

Beim Debuggen bietet unser Tool (wie Debugbar) Einblicke, aus welchen Dateien und Zeilen jede Abfrage stammt. Bei über 40 Dateien, einschließlich Vendor-Dateien, kann die Identifizierung der Ursache jedoch herausfordernd sein. So gehen wir vor:

1. Gründliches Debugging: Wir nutzen unsere Erfahrung und detailliertes Debugging, um das Problem schnell zu lokalisieren, selbst wenn es nicht sofort offensichtlich ist. Wir analysieren, warum das Problem auftrat, und überprüfen sowohl den Code als auch Framework-Ineffizienzen.
2. Code- und Framework-Analyse: Manchmal kann gut geschriebener Code Leistungsprobleme verursachen, wenn das Framework nicht ausreichend optimiert ist. Umgekehrt kann ein gutes Framework unter schlecht geschriebenem Code oder der Nichteinhaltung von Programmierstandards leiden.

Häufige Probleme in Datenbanken

Die gängigen Probleme in den den meisten Datenbanken sind:

• Wiederholte Anfragen: Diese können aufgrund übersehener Optimierungen, ineffizienter Programmierpraktiken oder unbeabsichtigter Schleifen in der Anwendungslogik auftreten.
• Langsame Abfragen: Oft das Ergebnis fehlender Indexe, ineffizienter Abfragestrukturen oder komplexer Joins, die vereinfacht werden können.
• Schema-Design: Während der Projektplanungsphase könnten wesentliche Elemente wie Indexe übersehen werden, was später zu Leistungsengpässen führt.
• N+1 Abfrageproblem: Dies tritt auf, wenn die Anwendung zusätzliche Abfragen für verwandte Daten ausführt, was durch eager loading vermieden werden könnte.
• Speicherlecks: Ineffiziente Speichernutzung, wie das Nichtfreigeben von Ressourcen oder übermäßiger Einsatz globaler Variablen, kann die Leistung im Laufe der Zeit verschlechtern.
• Konkurrenzprobleme: Unsachgemäße Handhabung paralleler Prozesse kann zu Race Conditions, Deadlocks oder anderen Synchronisationsproblemen führen, die die Leistung beeinträchtigen.
• Netzwerklatenz:  Übermäßige oder schlecht verwaltete Netzwerk-Anfragen können die Anwendung verlangsamen, insbesondere in verteilten Systemen.

Wir sind zuversichtlich, dass wir diese Probleme aufgrund unserer tiefen Kompetenzen und der langjährigen Erfahrung effizient lösen können. Dieses Fachwissen ermöglicht es uns, Probleme schnell zu identifizieren und zu beheben und so eine optimale Leistung für unsere und gern auch deine Projekte sicherzustellen.

Reduzierung von Datenbankabfragen zur Verbesserung der Leistung

Die wiederholte Abfrage der Datenbank für dieselben Daten kann zu unnötiger Belastung und langsamerer Leistung führen. Jeder Aufruf der Datenbank verbraucht Ressourcen und Zeit. Hier ist eine beispielhafte Lösung:

Beispiel für Leistungsverbesserung durch Reduzierung redundanter Abfragen

Die Leistungsverbesserung im aktualisierten Code ergibt sich aus der Reduzierung redundanter Datenbankabfragen. Durch das Speichern des Ergebnisses der translationQuality-Beziehungsabfrage in einer Variablen und dessen Wiederverwendung vermeidet der Code mehrere Datenbankaufrufe für dieselben Daten. Diese Optimierung verringert die Anzahl der Datenbankinteraktionen, was die Antwortzeit und Effizienz der edit-Methode verbessert.

Manchmal kann das direkte Abfragen der Datenbank mit Rohabfragen zu weniger und effizienteren Abfragen führen als die Verwendung von ORM-Methoden. Dieser Ansatz ermöglicht eine präzisere Steuerung der Abfrage und Optimierungen wie selektive Joins und Filterung. Durch das manuelle Erstellen der Abfrage können wir unnötigen Overhead eliminieren und sicherstellen, dass nur die benötigten Daten in einer einzigen, optimierten Abfrage abgerufen werden, was zu besserer Leistung und schnelleren Ausführungszeiten führt.

Die Leistungsaktualisierung verbessert die Effizienz erheblich, indem mehrere Datenbankabfragen in eine einzige, optimierte Abfrage umgewandelt werden. Anstatt alle Pakete abzurufen und dann im Speicher zu filtern, nutzt der neue Ansatz einen einzigen, gut gestalteten Datenbank-Join und eine bedingte Abfrage, um nur die erforderlichen Datensätze abzurufen. Dies minimiert nicht nur die übertragene Datenmenge, sondern reduziert auch den Verarbeitungsaufwand, was zu einer schnelleren Ausführung und einem optimierten Datenabruf führt.

Implementierung eines Datenbankindex zur Leistungssteigerung

Die Implementierung von Datenbankindezes ist eine leistungsstarke Technik zur Verbesserung der Seitengeschwindigkeit. Ein Index reduziert die Zeit, die zum Abrufen von Daten benötigt wird, erheblich, indem der Index der Datenbank ermöglicht, Datensätze effizienter zu lokalisieren und zuzugreifen. Diese Optimierung ist besonders wichtig für Tabellen mit großem Datenvolumen und häufigen Leseoperationen. Durch das Indizieren wichtiger Spalten, wie diejenigen, die in WHERE-Klauseln oder JOIN-Operationen verwendet werden, können wir die Abfrageausführungszeit drastisch verringern, was zu schnelleren Seitenladezeiten und einer reaktionsschnelleren Benutzererfahrung führt. Gut gestaltete Indizes stellen sicher, dass unsere Anwendung effektiv skalieren kann und dabei eine optimale Leistung beibehält, was letztendlich die Gesamtseitengeschwindigkeit verbessert.

Effiziente und schnelle Aufgabenbewältigung

Wir legen Wert auf Effizienz und Geschwindigkeit in unserem Workflow, was es uns ermöglicht, Aufgaben rechtzeitig zu erledigen. Durch den Einsatz optimierter Abfragen, das Minimieren redundanter Datenverarbeitung und die Anwendung unserer umfangreichen Erfahrung straffen wir unseren Entwicklungsprozess. Dieser Fokus auf Leistung stellt sicher, dass wir hochwertige Ergebnisse innerhalb kurzer Zeit liefern, Projekttermine einhalten und die Erwartungen unserer Kunden übertreffen.

Unsere Expertise beschränkt sich nicht auf eine bestimmte Programmiersprache. Wir wenden diese Prinzipien in verschiedenen Technologien und Frameworks an und passen unseren Ansatz an

die Anforderungen jedes Projekts an. Egal, ob es sich um PHP, MySQL, Python, JavaScript oder eine andere Sprache handelt, unser Engagement für Effizienz und Geschwindigkeit bleibt konstant und ermöglicht es uns, außergewöhnliche Leistung und schnelle Aufgabenbewältigung in jeder Entwicklungsumgebung zu liefern.

Berechnung der Auswirkungen der Reduzierung von Datenbankabfragen in Sprints

Die Reduzierung der Anzahl der Datenbankabfragen während eines Sprints kann die Gesamtleistung und Effizienz einer Anwendung erheblich verbessern. Um die Auswirkungen zu berechnen, identifizieren wir zunächst alle Datenbankinteraktionen im Code, wobei wir uns besonders auf häufige Abfragen und solche in kritischen Pfaden konzentrieren.

Zuerst messen wir die Ausführungszeit und Ressourcennutzung der vorhandenen Abfragen vor der Optimierung. Dazu verwenden wir Profiling-Tools, um die Latenz und Belastung jeder Datenbankinteraktion zu erfassen. Durch die Aggregation dieser Metriken können wir eine Leistungsbasislinie erstellen.

Anschließend implementieren wir optimierte Abfragen und refaktorisieren den Code, um redundante Anfragen zu minimieren. Dies könnte das Konsolidieren mehrerer Abfragen in eine einzelne, effizientere Abfrage, die Verwendung von Joins anstelle separater Abfragen oder die Nutzung von Caching-Strategien zur Vermeidung unnötiger Datenbankzugriffe umfassen.

Nach der Optimierung messen wir erneut die Ausführungszeit und Ressourcennutzung der refaktorierten Abfragen. Durch den Vergleich dieser Post-Optimierungsmetriken mit unserer Basislinie können wir die Verbesserungen quantifizieren. Wichtige Erfolgsindikatoren sind reduzierte Abfrageanzahl, geringere Ausführungszeit, verminderte Datenbankbelastung und verbesserte Antwortzeiten.

Darüber hinaus bewerten wir die breiteren Auswirkungen auf die Anwendungsleistung. Dies umfasst die Überwachung von Benutzererfahrungsmetriken wie Seitenladezeiten und Systemreaktionsfähigkeit. Durch die Dokumentation dieser Verbesserungen bieten wir eine klare, datengestützte Rechtfertigung für die Optimierungsbemühungen.

Insgesamt trägt dieser systematische Ansatz zur Reduzierung von Datenbankabfragen nicht nur zur Beschleunigung einzelner Sprints bei, sondern auch zur langfristigen Skalierbarkeit und Wartbarkeit der Anwendung bei.

Braucht deine Datenbank eine Optimierung?

Wenn du besondere Anforderungen an die Skalierbarkeit deines Projekts hast, weil du etwa die Mehrsprachigkeit und den internationalen Roll-out planst oder weißt, dass in Kürze Trafficspitzen (z. B. durch Fernsehwerbung) zu erwarten sind, bereite dich auf diese kommenden Lasten für deine Datenbanken vor. Gerade dann, wenn du das Gefühl hast, dass der Erstaufbau deiner Seite ohnehin schon sehr langsam geschieht, werden deine Datenbanken und die Datenbankabfragen nicht optimal sein und einen kommenden Ansturm von vielen Besuchern gleichzeitig auf der Seite nicht standhalten. Es kann zum Servercrash kommen. 

Am einfachsten ist es, du misst über ein Debug-Tool selbst, wie viele Abfragen für den Seitenaufruf nötig sind. Einen weiteren Hinweis, dass die Serverreaktionenzeiten und damit die Datenbankabfragen zu lange dauern, findest du auch in der Search Console unter Einstellungen => Crawling-Statisitken. Ein Wert von über 1.100 ms durchschnittliche Reaktionszeit ist schon fast eine Katastrophe. 

Die guten Werte liegen meiner Meinung nach unter 400 ms. John Müller von Google empfahl in einem Videocall zwischen 100 und 500 ms. 

Der beste bisher von mir gesehene Durchschnittwert für die Serverantwortzeit lag bei 180 ms in der Crawling-Statistik der Search Console für einen Online-Shop auf Shopify-Basis, wo Texte, Bilder, Produkte etc. enthalten waren. 

Agentur für Datenbankoptimierung: Kosten für die Reduzierung von Datenbankabfragen

Wir berechnen für diese sehr spezialisierte Leistung einen Stundensatz von 240 Euro netto. Dabei sind wir in der Lage mit verschiedensten Datenbanksystem zu arbeiten wie beispielsweise MariaDB, 
Mongo DB, PostgreSQL oder MySQL.

Wie lange die Optimierung von Datenbanken pro Seitentyp in deinem Projekt dauert, hängt immer auch vom Server und Hosting ab sowie auch vom eingesetzten Framework. Müssen wir alles klonen oder können wir bereits in einer vorbereiteten Testumgebung arbeiten? Haben wir SSH Zugang oder nicht? Ist bereits alles eingerichtet, sind zwei volle Arbeitstage pro Seitentyp eine gute Faustformel. Muss das Serversetup noch vorbereitet werden, kommt die Zeit entsprechend hinzu.

Nach einem Briefing sowie einer Erstanalyse können wir eine erste Schätzung über den tatsächlichen Zeitaufwand geben.

Wenn auch für dich eine Reduzierung von Datenbankabfragen und die damit verbundene serverseitige Leistungssteigerung interessant ist, melde dich gern bei uns.