{„title“: „Postgres, MySQL und SQLite: Vergleich der SQL-Leistung zwischen Datenbank-Engines“, „content“: „
Einleitung
\n
Bei der Entwicklung einer Anwendung kann die Wahl der richtigen SQL-Datenbank-Engine einen erheblichen Einfluss auf die Leistung haben.
\n
Drei gängige Optionen sind PostgreSQL, MySQL und SQLite. Jede dieser Engines weist spezifische Stärken und Optimierungsstrategien auf, die sie für unterschiedliche Szenarien geeignet machen.
\n
PostgreSQL zeichnet sich in der Regel durch die Verarbeitung komplexer analytischer Abfragen aus, während MySQL ebenfalls eine robuste allgemeine Leistung bietet. Im Gegensatz dazu bietet SQLite eine leichtgewichtige Lösung für eingebettete Anwendungen.
\n
In diesem Artikel werden wir diese drei Engines anhand von vier analytischen Interviewfragen benchmarken: zwei mit mittlerem Schwierigkeitsgrad und zwei mit hohem Schwierigkeitsgrad.
\n
Das Ziel besteht darin, zu untersuchen, wie jede Engine mit Joins, Fensterfunktionen, Datumsarithmetik und komplexen Aggregationen umgeht. Dies wird die plattformspezifischen Optimierungsstrategien hervorheben und nützliche Einblicke in die Leistung und Spezifikationen jeder Engine bieten.
\n
Die drei SQL-Engines verstehen
\n
Bevor wir in die Benchmarks eintauchen, wollen wir die Unterschiede zwischen diesen drei Datenbanksystemen verstehen.
\n
PostgreSQL ist eine funktionsreiche, Open-Source-Relationale Datenbank, die für ihre fortschrittliche SQL-Konformität und ausgeklügelte Abfrageoptimierung bekannt ist. Sie kann komplexe analytische Abfragen effektiv verarbeiten und bietet umfassende Unterstützung für Fensterfunktionen, CTEs und verschiedene Indexierungsstrategien.
\n
MySQL ist die am weitesten verbreitete Open-Source-Datenbank, die aufgrund ihrer Geschwindigkeit und Genauigkeit in Webanwendungen geschätzt wird. Trotz ihres historischen Schwerpunkts auf transaktionalen Workloads beinhalten moderne Versionen dieser Engine umfassende analytische Fähigkeiten mit Fensterfunktionen und verbesserter Abfrageoptimierung.
\n
SQLite ist eine leichtgewichtige Engine, die direkt in Anwendungen eingebettet ist. Im Gegensatz zu den beiden vorherigen Engines, die als separate Serverprozesse laufen, agiert SQLite als Bibliothek, was sie ideal für mobile Anwendungen, Desktop-Programme und Entwicklungsumgebungen macht.
\n
Diese Einfachheit bringt jedoch einige Einschränkungen mit sich, beispielsweise bei gleichzeitigen Schreiboperationen und bestimmten SQL-Funktionen.
\n
Die Benchmarks in diesem Artikel verwenden vier Interviewfragen, die verschiedene SQL-Fähigkeiten testen.
\n
Für jedes Problem werden wir die Abfragestrukturen über alle drei Engines hinweg analysieren und dabei die Syntaxvariationen, Leistungsüberlegungen und Optimierungsmöglichkeiten hervorheben.
\n
Wir werden die Leistung hinsichtlich der Ausführungszeit testen. PostgreSQL und MySQL wurden auf der Plattform von StrataScratch (serverbasiert) benchmarked, während SQLite lokal im Speicher getestet wurde.
\n
Mittelschwere Fragen lösen
\n
Interviewfrage #1: Risikobehaftete Projekte
\n
Diese Interviewfrage fordert dazu auf, Projekte zu identifizieren, die ihr Budget überschreiten, basierend auf den anteiligen Gehältern der Mitarbeiter.
\n
Datenbanken: Es stehen drei Tabellen zur Verfügung: linkedin_projects (mit Budgets und Daten), linkedin_emp_projects und linkedin_employees.
\n
Das Ziel besteht darin, den Anteil des Jahresgehalts jedes Mitarbeiters, der jedem Projekt zugeordnet ist, zu berechnen und festzustellen, welche Projekte über Budget liegen.
\n
In PostgreSQL lautet die Lösung:
\n
SELECT a.title,\n a.budget,\n CEILING((a.end_date – a.start_date) * SUM(c.salary) / 365) AS prorated_employee_expense\nFROM linkedin_projects a\nINNER JOIN linkedin_emp_projects b ON a.id = b.project_id\nINNER JOIN linkedin_employees c ON b.emp_id = c.id\nGROUP BY a.title,\n a.budget,\n a.end_date,\n a.start_date\nHAVING CEILING((a.end_date – a.start_date) * SUM(c.salary) / 365) > a.budget\nORDER BY a.title ASC;
\n
PostgreSQL verarbeitet die Datumsarithmetik elegant durch direkte Subtraktion (end_date – start_date), was die Anzahl der Tage zwischen den Daten zurückgibt.
\n
Die Berechnung ist einfach und leicht verständlich aufgrund der nativen Datumsverarbeitung der Engine.
\n
In MySQL lautet die Lösung:
\n
SELECT a.title,\n a.budget,\n CEILING(DATEDIFF(a.end_date, a.start_date) * SUM(c.salary) / 365) AS prorated_employee_expense\nFROM linkedin_projects a\nINNER JOIN linkedin_emp_projects b ON a.id = b.project_id\nINNER JOIN linkedin_employees c ON b.emp_id = c.id\nGROUP BY a.title,\n a.budget,\n a.end_date,\n a.start_date\nHAVING CEILING(DATEDIFF(a.end_date, a.start_date) * SUM(c.salary) / 365) > a.budget\nORDER BY a.title ASC;
\n
In MySQL ist die DATEDIFF()-Funktion erforderlich, um die Datumsarithmetik durchzuführen, die explizit berechnet, wie viele Tage zwischen zwei Daten liegen.
\n
Obwohl dies einen Funktionsaufruf hinzufügt, verarbeitet der Abfrageoptimierer von MySQL dies effizient.
\n
Schließlich werfen wir einen Blick auf die SQLite-Lösung:
\n
SELECT a.title,\n a.budget,\n CAST(\n (julianday(a.end_date) – julianday(a.start_date)) * (SUM(c.salary) / 365) + 0.99\n AS INTEGER) AS prorated_employee_expense\nFROM linkedin_projects a\nINNER JOIN linkedin_emp_projects b ON a.id = b.project_id\nINNER JOIN linkedin_employees c ON b.emp_id = c.id\nGROUP BY a.title, a.budget, a.end_date, a.start_date\nHAVING CAST(\n (julianday(a.end_date) – julianday(a.start_date)) * (SUM(c.salary) / 365) + 0.99\n AS INTEGER) > a.budget\nORDER BY a.title ASC;
\n
SQLite verwendet die julianday()-Funktion, um Daten in numerische Werte für arithmetische Operationen umzuwandeln.
\n
Da SQLite keine CEILING()-Funktion hat, können wir dies nachahmen, indem wir 0.99 hinzufügen und in eine Ganzzahl umwandeln, was genau aufrundet.
\n
Abfragen optimieren
\n
Für jede der drei Engines können Indizes auf den Join-Spalten (project_id, emp_id, id) verwendet werden, um die Leistung erheblich zu verbessern. Die Vorteile von PostgreSQL ergeben sich aus der Verwendung von zusammengesetzten Indizes auf (title, budget, end_date, start_date) für die GROUP BY-Klausel.
\n
Die ordnungsgemäße Verwendung von Primärschlüsseln ist entscheidend, da MySQLs InnoDB-Engine die Daten automatisch nach dem Primärschlüssel gruppiert.
\n
Interviewfrage #2: Benutzerkäufe finden
\n
Das Ziel dieser Interviewfrage besteht darin, die IDs von wiederkehrenden Kunden auszugeben, die innerhalb von 1 bis 7 Tagen nach ihrem ersten Kauf einen zweiten Kauf getätigt haben (wobei Käufe am selben Tag ausgeschlossen sind).
\n
Datenbanken: Die einzige Tabelle ist amazon_transactions. Sie enthält Transaktionsdaten mit id, user_id, item, created_at und revenue.
\n
PostgreSQL-Lösung:
\n
WITH daily AS (\n SELECT DISTINCT user_id, created_at::date AS purchase_date\n FROM amazon_transactions\n),\nranked AS (\n SELECT user_id, purchase_date,\n ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date) AS rn\n FROM daily\n),\nfirst_two AS (\n SELECT user_id,\n MAX(CASE WHEN rn = 1 THEN purchase_date END) AS first_date,\n MAX(CASE WHEN rn = 2 THEN purchase_date END) AS second_date\n FROM ranked\n WHERE rn <= 2\n GROUP BY user_id\n)\nSELECT user_id\nFROM first_two\nWHERE second_date IS NOT NULL\n AND (second_date - first_date) BETWEEN 1 AND 7\nORDER BY user_id;
\n
In PostgreSQL wird die Lösung durch die Verwendung von CTEs (Common Table Expressions) erreicht, um das Problem in logische und lesbare Schritte zu unterteilen.
\n
Die Datumsumwandlungsfunktion verwandelt Zeitstempel in Daten, während die Fensterfunktionen mit ROW_NUMBER() die Käufe chronologisch anordnen. Die inhärente Datumsabzug-Funktion von PostgreSQL hält den abschließenden Filter sauber und effektiv.
\n
MySQL-Lösung:
\n
WITH daily AS (\n SELECT DISTINCT user_id, DATE(created_at) AS purchase_date\n FROM amazon_transactions\n),\nranked AS (\n SELECT user_id, purchase_date,\n ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date) AS rn\n FROM daily\n),\nfirst_two AS (\n SELECT user_id,\n MAX(CASE WHEN rn = 1 THEN purchase_date END) AS first_date,\n MAX(CASE WHEN rn = 2 THEN purchase_date END) AS second_date\n FROM ranked\n WHERE rn <= 2\n GROUP BY user_id\n)\nSELECT user_id\nFROM first_two\nWHERE second_date IS NOT NULL\n AND DATEDIFF(second_date, first_date) BETWEEN 1 AND 7\nORDER BY user_id;
\n
Die Lösung von MySQL ähnelt der vorherigen Struktur von PostgreSQL, wobei CTEs und Fensterfunktionen verwendet werden.
\n
Der Hauptunterschied besteht hier in der Verwendung der DATE()- und DATEDIFF()-Funktionen für die Datumsabfrage und den Vergleich. MySQL 8.0+ unterstützt CTEs effizient, während frühere Versionen Subabfragen erforderten.
\n
SQLite-Lösung:
\n
WITH daily AS (\n SELECT DISTINCT user_id, DATE(created_at) AS purchase_date\n FROM amazon_transactions\n),\nranked AS (\n SELECT user_id, purchase_date,\n ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date) AS rn\n FROM daily\n),\nfirst_two AS (\n SELECT user_id,\n MAX(CASE WHEN rn = 1 THEN purchase_date END) AS first_date,\n MAX(CASE WHEN rn = 2 THEN purchase_date END) AS second_date\n FROM ranked\n WHERE rn <= 2\n GROUP BY user_id\n)\nSELECT user_id\nFROM first_two\nWHERE second_date IS NOT NULL\n AND (julianday(second_date) - julianday(first_date)) BETWEEN 1 AND 7\nORDER BY user_id;
\n
SQLite (Version 3.25+) unterstützt ebenfalls CTEs und Fensterfunktionen, wodurch die Struktur identisch zu den beiden vorherigen ist. In diesem Fall besteht der einzige Unterschied in der Datumsarithmetik, die julianday() anstelle von nativer Subtraktion oder DATEDIFF() verwendet.
\n
Abfragen optimieren
\n
Indizes können auch in diesem Fall für eine effiziente Partitionierung in Fensterfunktionen verwendet werden, insbesondere für die user_id. PostgreSQL kann von partiellen Indizes auf aktiven Benutzern profitieren.
\n
Wenn Sie mit großen Datensätzen arbeiten, sollten Sie in PostgreSQL auch in Betracht ziehen, die tägliche CTE zu materialisieren. Für optimale CTE-Leistung in MySQL stellen Sie sicher, dass Sie Version 8.0+ verwenden.
\n
Schwierige Fragen lösen
\n
Interviewfrage #3: Umsatz über die Zeit
\n
Diese Interviewfrage fordert dazu auf, einen 3-Monats-Rolling-Durchschnitt des Gesamtumsatzes aus Käufen zu berechnen.
\n
Das Ziel besteht darin, Jahr-Monat-Werte mit den entsprechenden rollierenden Durchschnitten auszugeben, chronologisch sortiert. Rückgaben (negative Kaufbeträge) sollten ausgeschlossen werden.
\n
Datenbanken:
\n
amazon_purchases: Enthält Kaufdatensätze mit user_id, created_at und purchase_amt
\n
Zuerst überprüfen wir die PostgreSQL-Lösung:
\n
SELECT t.month,\n AVG(t.monthly_revenue) OVER(\n ORDER BY t.month \n ROWS BETWEEN 2 PRECEDING AND CURRENT ROW\n ) AS avg_revenue\nFROM (\n SELECT to_char(created_at::date, ‚YYYY-MM‘) AS month,\n sum(purchase_amt) AS monthly_revenue\n FROM amazon_purchases\n WHERE purchase_amt > 0\n GROUP BY to_char(created_at::date, ‚YYYY-MM‘)\n ORDER BY to_char(created_at::date, ‚YYYY-MM‘)\n) t\nORDER BY t.month ASC;
\n
PostgreSQL übertrifft mit Fensterfunktionen, da die Rahmenbeschreibung ROWS BETWEEN 2 PRECEDING AND CURRENT ROW das rollierende Fenster präzise definiert.
\n
Die to_char()-Funktion formatiert Daten in Jahr-Monat-Strings für die Gruppierung.
\n
Nun die MySQL-Lösung:
\n
SELECT t.`month`,\n AVG(t.monthly_revenue) OVER(\n ORDER BY t.`month` \n ROWS BETWEEN 2 PRECEDING AND CURRENT ROW\n ) AS avg_revenue\nFROM (\n SELECT DATE_FORMAT(created_at, ‚%Y-%m‘) AS month,\n sum(purchase_amt) AS monthly_revenue\n FROM amazon_purchases\n WHERE purchase_amt > 0\n GROUP BY DATE_FORMAT(created_at, ‚%Y-%m‘)\n ORDER BY DATE_FORMAT(created_at, ‚%Y-%m‘)\n) t\nORDER BY t.`month` ASC;
\n
Die Implementierung von MySQL behandelt die Fensterfunktion identisch, verwendet jedoch die DATE_FORMAT()-Funktion anstelle von to_char().
\n
Beachten Sie, dass diese Engine eine spezifische Syntaxanforderung hat, um Konflikte mit Schlüsselwörtern zu vermeiden, weshalb die Backticks um month stehen.
\n
Schließlich die SQLite-Lösung:
\n
SELECT t.month,\n AVG(t.monthly_revenue) OVER(\n ORDER BY t.month \n ROWS BETWEEN 2 PRECEDING AND CURRENT ROW\n ) AS avg_revenue\nFROM (\n SELECT strftime(‚%Y-%m‘, created_at) AS month,\n SUM(purchase_amt) AS monthly_revenue\n FROM amazon_purchases\n WHERE purchase_amt > 0\n GROUP BY strftime(‚%Y-%m‘, created_at)\n ORDER BY strftime(‚%Y-%m‘, created_at)\n) t\nORDER BY t.month ASC;
\n
Die Datumsformatierung in SQLite erfordert die Verwendung von strftime(), und diese Engine unterstützt die gleiche Fensterfunktionssyntax wie PostgreSQL und MySQL (in Version 3.25+). Die Leistung ist vergleichbar für kleine bis mittelgroße Datensätze.
\n
Abfragen optimieren
\n
Fensterfunktionen können rechenintensiv sein.
\n
Für PostgreSQL sollten Sie in Betracht ziehen, einen Index auf created_at zu erstellen und, wenn diese Abfrage häufig ausgeführt wird, eine materialisierte Ansicht für die monatliche Aggregation zu erstellen.
\n
MySQL profitiert von abdeckenden Indizes, die sowohl created_at als auch purchase_amt enthalten.
\n
Für SQLite müssen Sie sicherstellen, dass Sie Version 3.25 oder höher verwenden, um die Unterstützung für Fensterfunktionen zu haben.
\n
Interviewfrage #4: Gemeinsame Freunde von Freunden
\n
Bei dieser Interviewfrage geht es darum, die Anzahl der Freunde jedes Benutzers zu ermitteln, die auch Freunde der anderen Freunde des Benutzers sind (also gegenseitige Verbindungen innerhalb eines Netzwerks). Das Ziel besteht darin, Benutzer-IDs mit der Anzahl dieser gemeinsamen Freundschaftsbeziehungen auszugeben.
\n
Datenbanken:
\n
google_friends_network: Enthält Freundschaftsbeziehungen mit user_id und friend_id.
\n
Die PostgreSQL-Lösung lautet:
\n
WITH bidirectional_relationship AS (\n SELECT user_id, friend_id\n FROM google_friends_network\n UNION\n SELECT friend_id AS user_id, user_id AS friend_id\n FROM google_friends_network\n)\nSELECT user_id, COUNT(DISTINCT friend_id) AS n_friends\nFROM (\n SELECT DISTINCT a.user_id, c.user_id AS friend_id\n FROM bidirectional_relationship a\n INNER JOIN bidirectional_relationship b ON a.friend_id = b.user_id\n INNER JOIN bidirectional_relationship c ON b.friend_id = c.user_id\n AND c.friend_id = a.user_id\n) base\nGROUP BY user_id;
\n
In PostgreSQL wird diese komplexe Multi-Join-Abfrage effizient von seinem ausgeklügelten Abfrageplaner verarbeitet.
\n
Die anfängliche CTE erstellt eine zweiseitige Sicht auf die Verbindungen innerhalb des Netzwerks, gefolgt von drei Selbst-Joins, die dreieckige Beziehungen identifizieren, in denen A mit B befreundet ist, B mit C befreundet ist und C auch mit A befreundet ist.
\n
MySQL-Lösung:
\n
SELECT user_id, COUNT(DISTINCT friend_id) AS n_friends\nFROM (\n SELECT DISTINCT a.user_id, c.user_id AS friend_id\n FROM (\n SELECT user_id, friend_id\n FROM google_friends_network\n UNION\n SELECT friend_id AS user_id, user_id AS friend_id\n FROM google_friends_network\n ) AS a\n INNER JOIN (\n SELECT user_id, friend_id\n FROM google_friends_network\n UNION\n SELECT friend_id AS user_id, user_id AS friend_id\n FROM google_friends_network\n ) AS b ON a.friend_id = b.user_id\n INNER JOIN (\n SELECT user_id, friend_id\n FROM google_friends_network\n UNION\n SELECT friend_id AS user_id, user_id AS friend_id\n
Bildquelle: ai-generated-gemini
