Vorbei sind die Zeiten, in denen Sie sich keine Gedanken über die Optimierung der Datenbankleistung machen mussten. Die Zeit steht nicht still. Jeder neue Tech-Unternehmer möchte das nächste Facebook erschaffen und dabei versuchen, alle Daten zu sammeln, die er in die Finger bekommen kann. Unternehmen benötigen diese Daten, um Modelle besser trainieren zu können, mit denen sie Geld verdienen können. Unter solchen Bedingungen müssen Programmierer APIs erstellen, die es ihnen ermöglichen, schnell und zuverlässig mit riesigen Informationsmengen zu arbeiten.
Wenn Sie schon seit längerem Anwendungs- oder Datenbank-Backends entwerfen, haben Sie wahrscheinlich Code zum Ausführen paginierter Abfragen geschrieben. Zum Beispiel so:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Wie es ist?
Aber wenn Sie Ihre Paginierung auf diese Weise durchgeführt haben, muss ich leider sagen, dass Sie es nicht auf die effizienteste Art und Weise gemacht haben.
Möchten Sie Einwände gegen mich erheben? . , и Sie wenden bereits die Techniken an, über die ich heute sprechen möchte.
Nennen Sie mindestens einen Backend-Entwickler, der noch nie etwas verwendet hat OFFSET и LIMIT um paginierte Abfragen durchzuführen. Bei MVP (Minimum Viable Product) und bei Projekten, bei denen kleine Datenmengen verwendet werden, ist dieser Ansatz durchaus anwendbar. Es „funktioniert einfach“ sozusagen.
Wenn Sie jedoch von Grund auf zuverlässige und effiziente Systeme erstellen müssen, sollten Sie sich im Voraus um die Effizienz der Abfrage der in solchen Systemen verwendeten Datenbanken kümmern.
Heute sprechen wir über die Probleme mit häufig verwendeten (schade) Implementierungen paginierter Abfrage-Engines und darüber, wie man bei der Ausführung solcher Abfragen eine hohe Leistung erzielt.
Was ist falsch an OFFSET und LIMIT?
Wie schon gesagt, OFFSET и LIMIT Sie eignen sich gut für Projekte, die nicht mit großen Datenmengen arbeiten müssen.
Das Problem entsteht, wenn die Datenbank so groß wird, dass sie nicht mehr in den Speicher des Servers passt. Wenn Sie jedoch mit dieser Datenbank arbeiten, müssen Sie paginierte Abfragen verwenden.
Damit sich dieses Problem manifestiert, muss es eine Situation geben, in der das DBMS bei jeder paginierten Abfrage auf einen ineffizienten Full Table Scan-Vorgang zurückgreift (während Einfüge- und Löschvorgänge auftreten können und wir keine veralteten Daten benötigen!).
Was ist ein „vollständiger Tabellenscan“ (oder „sequentieller Tabellenscan“, Sequential Scan)? Hierbei handelt es sich um einen Vorgang, bei dem das DBMS nacheinander jede Zeile der Tabelle, also die darin enthaltenen Daten, liest und sie auf Einhaltung einer bestimmten Bedingung prüft. Diese Art des Tabellenscans ist bekanntermaßen die langsamste. Tatsache ist, dass bei der Ausführung viele Ein-/Ausgabevorgänge ausgeführt werden, an denen das Festplattensubsystem des Servers beteiligt ist. Die Situation wird durch die Latenz, die mit der Arbeit mit auf Festplatten gespeicherten Daten einhergeht, und die Tatsache, dass die Übertragung von Daten von der Festplatte in den Speicher ein ressourcenintensiver Vorgang ist, noch verschlimmert.
Sie haben beispielsweise Datensätze von 100000000 Benutzern und führen eine Abfrage mit dem Konstrukt aus OFFSET 50000000. Das bedeutet, dass das DBMS alle diese Datensätze laden muss (und wir brauchen sie nicht einmal!), sie im Speicher ablegen und anschließend beispielsweise 20 Ergebnisse eintragen muss LIMIT.
Nehmen wir an, es könnte so aussehen: „Zeilen von 50000 bis 50020 von 100000 auswählen“. Das heißt, das System muss zunächst 50000 Zeilen laden, um die Abfrage abzuschließen. Sehen Sie, wie viel unnötige Arbeit sie leisten muss?
Wenn Sie mir nicht glauben, schauen Sie sich das Beispiel an, das ich mit den Funktionen erstellt habe .
Beispiel bei db-fiddle.com
Dort links im Feld Schema SQLgibt es Code, der 100000 Zeilen in die Datenbank und rechts in das Feld einfügt Query SQLwerden zwei Abfragen angezeigt. Das erste, langsame, sieht so aus:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Und die zweite, die eine wirksame Lösung für dasselbe Problem darstellt, sieht so aus:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Um diese Anfragen zu erfüllen, klicken Sie einfach auf den Button Run oben auf der Seite. Anschließend vergleichen wir Informationen über die Ausführungszeit der Abfrage. Es stellt sich heraus, dass die Ausführung einer ineffektiven Abfrage mindestens 30-mal länger dauert als die Ausführung der zweiten (diese Zeit variiert von Ausführung zu Ausführung; das System meldet beispielsweise möglicherweise, dass die erste Abfrage 37 ms bis zum Abschluss gedauert hat, die Ausführung der Sekunde - 1 ms).
Und wenn es mehr Daten gibt, wird alles noch schlimmer aussehen (um sich davon zu überzeugen, werfen Sie einen Blick auf meine mit 10 Millionen Zeilen).
Was wir gerade besprochen haben, soll Ihnen einen Einblick in die tatsächliche Verarbeitung von Datenbankabfragen geben.
Bitte beachten Sie, dass der Wert umso höher ist OFFSET — desto länger dauert die Bearbeitung der Anfrage.
Was soll ich anstelle der Kombination aus OFFSET und LIMIT verwenden?
Statt einer Kombination OFFSET и LIMIT Es lohnt sich, eine Struktur zu verwenden, die nach folgendem Schema aufgebaut ist:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Dies ist eine Abfrageausführung mit Cursor-basierter Paginierung.
Anstatt aktuelle lokal zu speichern OFFSET и LIMIT Um sie bei jeder Anfrage zu übermitteln, müssen Sie den zuletzt empfangenen Primärschlüssel speichern (normalerweise ist dies der Fall). ID) Und LIMITAls Ergebnis werden Abfragen erhalten, die den oben genannten ähneln.
Warum? Der Punkt ist, dass Sie Ihrem DBMS durch die explizite Angabe der Kennung der zuletzt gelesenen Zeile mitteilen, wo es mit der Suche nach den erforderlichen Daten beginnen muss. Darüber hinaus wird die Suche dank der Verwendung des Schlüssels effizient durchgeführt; das System muss nicht durch Linien außerhalb des angegebenen Bereichs abgelenkt werden.
Schauen wir uns den folgenden Leistungsvergleich verschiedener Abfragen an. Hier ist eine ineffektive Abfrage.
Langsame Anfrage
Und hier ist eine optimierte Version dieser Anfrage.
Kurze Anfrage
Beide Abfragen geben genau die gleiche Datenmenge zurück. Aber der erste Vorgang dauert 12,80 Sekunden und der zweite 0,01 Sekunden. Spüren Sie den Unterschied?
Mögliche Probleme
Damit die vorgeschlagene Abfragemethode effektiv funktioniert, muss die Tabelle über eine Spalte (oder Spalten) verfügen, die eindeutige, sequentielle Indizes enthält, beispielsweise einen ganzzahligen Bezeichner. In bestimmten Fällen kann dies den Erfolg der Verwendung solcher Abfragen zur Beschleunigung der Arbeit mit der Datenbank bestimmen.
Natürlich müssen Sie beim Erstellen von Abfragen die spezifische Architektur der Tabellen berücksichtigen und diejenigen Mechanismen auswählen, die für die vorhandenen Tabellen am besten funktionieren. Wenn Sie beispielsweise Abfragen mit großen Mengen zusammengehöriger Daten bearbeiten müssen, könnte es für Sie interessant sein Artikel
Wenn wir mit dem Problem konfrontiert sind, dass ein Primärschlüssel fehlt, beispielsweise wenn wir eine Tabelle mit einer Viele-zu-Viele-Beziehung haben, dann ist der traditionelle Ansatz die Verwendung OFFSET и LIMIT, passt garantiert zu uns. Seine Verwendung kann jedoch zu potenziell langsamen Abfragen führen. In solchen Fällen würde ich die Verwendung eines automatisch inkrementierenden Primärschlüssels empfehlen, auch wenn dieser nur zur Verarbeitung paginierter Abfragen benötigt wird.
Wenn Sie sich für dieses Thema interessieren - , и - mehrere nützliche Materialien.
Ergebnisse
Die wichtigste Schlussfolgerung, die wir daraus ziehen können, ist, dass es unabhängig von der Größe der Datenbanken immer notwendig ist, die Geschwindigkeit der Abfrageausführung zu analysieren. Heutzutage ist die Skalierbarkeit von Lösungen äußerst wichtig, und wenn von Anfang an bei der Arbeit an einem bestimmten System alles richtig gestaltet ist, kann dies dem Entwickler in Zukunft viele Probleme ersparen.
Wie analysieren und optimieren Sie Datenbankabfragen?
Source: habr.com