Eines der typischen Szenarien in allen bekannten Anwendungen besteht darin, nach bestimmten Kriterien nach Daten zu suchen und diese in einer leicht lesbaren Form anzuzeigen. Es kann auch zusätzliche Möglichkeiten zum Sortieren, Gruppieren und Paginieren geben. Theoretisch ist die Aufgabe trivial, aber bei der Lösung machen viele Entwickler eine Reihe von Fehlern, die dann unter der Leistung leiden. Lassen Sie uns versuchen, verschiedene Lösungen für dieses Problem in Betracht zu ziehen und Empfehlungen für die Auswahl der effektivsten Implementierung zu formulieren.
Paging-Option 1
Die einfachste Option, die Ihnen in den Sinn kommt, besteht darin, die Suchergebnisse in ihrer klassischsten Form zu paginieren.Angenommen, eine Anwendung verwendet eine relationale Datenbank. In diesem Fall müssen Sie zwei SQL-Abfragen ausführen, um Informationen in diesem Formular anzuzeigen:- Zeilen für die aktuelle Seite abrufen.
- Berechnen Sie die Gesamtzahl der Zeilen, die den Suchkriterien entsprechen - dies ist zum Anzeigen von Seiten erforderlich.
Betrachten Sie die erste Abfrage am Beispiel der Test-MS SQL-Datenbank AdventureWorks for 2016 Server. Zu diesem Zweck verwenden wir die Tabelle Sales.SalesOrderHeader:SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Die obige Abfrage zeigt die ersten 50 Bestellungen aus einer Liste an, sortiert in absteigender Reihenfolge nach dem Datum der Hinzufügung, dh den letzten 50 Bestellungen.Es läuft schnell auf Testbasis, aber schauen wir uns den Ausführungsplan und die E / A-Statistiken an:Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Sie können E / A-Statistiken für jede Anforderung abrufen, indem Sie den Befehl SET STATISTICS IO ON in der Abfragelaufzeit ausführen.Wie Sie dem Ausführungsplan entnehmen können, ist es am ressourcenintensivsten, alle Zeilen der Quelltabelle nach dem Datum des Hinzufügens zu sortieren. Und das Problem ist, dass die Sortierung umso schwieriger ist, je mehr Zeilen in der Tabelle angezeigt werden. In der Praxis sollten solche Situationen vermieden werden. Fügen Sie daher den Index zum Zeitpunkt der Hinzufügung hinzu und prüfen Sie, ob sich der Ressourcenverbrauch geändert hat:Table 'SalesOrderHeader'. Scan count 1, logical reads 165, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Offensichtlich wurde es viel besser. Aber wurden alle Probleme gelöst? Lassen Sie uns die Suchanforderung für Bestellungen ändern, bei denen die Gesamtkosten der Waren 100 USD überschreiten:SELECT * FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Table 'SalesOrderHeader'. Scan count 1, logical reads 1081, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Wir haben eine lustige Situation: Der Abfrageplan ist etwas schlechter als der vorherige, aber die tatsächliche Anzahl der logischen Lesungen ist fast doppelt so hoch wie bei einem vollständigen Tabellenscan. Es gibt eine Lösung: Wenn wir den zusammengesetzten Preis aus dem vorhandenen Index erstellen und den Gesamtpreis der Waren als zweites Feld addieren, erhalten wir erneut 165 logische Lesevorgänge:CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);
Diese Reihe von Beispielen kann noch lange fortgesetzt werden, aber die beiden Hauptgedanken, die ich hier ausdrücken möchte, sind:- Das Hinzufügen eines neuen Kriteriums oder einer neuen Sortierreihenfolge zur Suchabfrage kann die Ausführungsgeschwindigkeit erheblich beeinflussen.
- Wenn wir jedoch nur einen Teil der Daten und nicht alle Ergebnisse subtrahieren müssen, die den Suchbedingungen entsprechen, gibt es viele Möglichkeiten, eine solche Abfrage zu optimieren.
Fahren wir nun mit der zweiten Abfrage fort, die ganz am Anfang erwähnt wurde - mit der Abfrage, die die Anzahl der Datensätze zählt, die die Suchkriterien erfüllen. Nehmen Sie das gleiche Beispiel: Finden Sie Bestellungen, die mehr als 100 US-Dollar kosten:SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
In Gegenwart des oben angegebenen zusammengesetzten Index erhalten wir:Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Die Tatsache, dass die Abfrage den gesamten Index durchläuft, ist nicht überraschend, da sich das Zwischensummenfeld nicht an der ersten Position befindet und die Abfrage es daher nicht verwenden kann. Das Problem wird gelöst, indem dem Feld Zwischensumme ein weiterer Index hinzugefügt wird. Infolgedessen werden bereits 48 logische Lesevorgänge ausgeführt.Sie können noch einige Beispiele für Anforderungen zum Zählen der Menge nennen, aber das Wesentliche bleibt gleich: Das Empfangen eines Teils der Daten und das Zählen der Gesamtmenge sind zwei grundlegend unterschiedliche Anforderungen , und jede erfordert ihre eigenen Optimierungsmaßnahmen. Im allgemeinen Fall können Sie keine Kombination von Indizes finden, die für beide Abfragen gleich gut funktioniert.Dementsprechend ist eine der wichtigen Anforderungen, die bei der Entwicklung einer solchen Suchlösung geklärt werden sollten, ob es für das Unternehmen wirklich wichtig ist, die Gesamtzahl der gefundenen Objekte zu sehen. Es kommt oft vor, dass nein. Die Navigation zu bestimmten Seitenzahlen ist meiner Meinung nach eine Lösung mit einem sehr engen Umfang, da die meisten Paging-Szenarien wie "Zur nächsten Seite gehen" aussehen.Paging-Option 2
Angenommen, Benutzer möchten nicht wissen, wie viele Objekte insgesamt gefunden wurden. Versuchen wir, die Suchseite zu vereinfachen:Tatsächlich hat sich nur die Tatsache geändert, dass es keine Möglichkeit gibt, zu bestimmten Seitenzahlen zu gelangen, und jetzt muss diese Tabelle nicht mehr wissen, wie viele davon angezeigt werden können. Es stellt sich jedoch die Frage: Woher weiß die Tabelle, ob Daten für die nächste Seite vorhanden sind (um den Link „Weiter“ korrekt anzuzeigen)?Die Antwort ist sehr einfach: Sie können einen Datensatz mehr von der Datenbank abziehen, als Sie anzeigen müssen, und das Vorhandensein dieses "zusätzlichen" Datensatzes zeigt an, ob der nächste Teil vorhanden ist. Um eine Seite mit Daten zu erhalten, müssen Sie nur eine Abfrage ausführen. Dies verbessert die Leistung erheblich und erleichtert die Unterstützung dieser Funktionalität. In meiner Praxis gab es einen Fall, in dem die Weigerung, die Gesamtzahl der Datensätze zu zählen, die Ausgabe der Ergebnisse um das 4-5-fache beschleunigte.Für diesen Ansatz gibt es mehrere Optionen für die Benutzeroberfläche: die Befehle "Zurück" und "Vorwärts", wie im obigen Beispiel, die Schaltfläche "Mehr laden", die den angezeigten Ergebnissen einfach einen neuen Teil hinzufügt, "unendliches Scrollen", der nach dem Prinzip "Mehr laden" funktioniert. ", Aber das Signal, um den nächsten Teil zu erhalten, ist der Benutzer, durch alle angezeigten Ergebnisse bis zum Ende zu scrollen. Unabhängig von der visuellen Lösung bleibt das Prinzip der Datenerfassung gleich.Die Nuancen der Implementierung von Paging
In allen Beispielen der obigen Abfragen wird der Ansatz "Offset + Nummer" verwendet, wenn die Abfrage selbst angibt, in welcher Reihenfolge die Ergebniszeilen und wie viele Zeilen zurückgegeben werden sollen. Überlegen Sie zunächst, wie Sie die Übertragung von Parametern in diesem Fall am besten organisieren können. In der Praxis habe ich verschiedene Wege getroffen:- Die Seriennummer der angeforderten Seite (pageIndex), Seitengröße (pageSize).
- Die Seriennummer des ersten zurückgegebenen Datensatzes (startIndex), die maximale Anzahl der Datensätze als Ergebnis (Anzahl).
- Die Seriennummer des ersten zurückgegebenen Datensatzes (startIndex), die Seriennummer des zuletzt zurückgegebenen Datensatzes (endIndex).
Auf den ersten Blick scheint dies so elementar zu sein, dass es keinen Unterschied gibt. Dies ist jedoch nicht der Fall - die bequemste und universellste Option ist die zweite (startIndex, count). Dafür gibt es mehrere Gründe:- +1 , , pageIndex pageSize . , 50 . , , . «+1» , , 1 51, — 51 101 .. 51 pageIndex, 52 102 .. , — «» , .
- Die dritte Option ist überhaupt nicht sinnvoll, da Sie zum Ausführen von Abfragen in den meisten Datenbanken immer noch die Menge und nicht den Index des letzten Datensatzes übertragen müssen. Lassen Sie die Subtraktion von startIndex von endIndex und eine elementare arithmetische Operation, aber es ist hier überflüssig.
Nun sollten wir die Mängel der Implementierung von Paging durch "Offset + Menge" beschreiben:- Das Abrufen jeder nächsten Seite ist teurer und langsamer als die vorherige, da die Datenbank weiterhin alle Datensätze "von Anfang an" gemäß den Such- und Sortierkriterien durchsuchen und dann beim gewünschten Fragment anhalten muss.
- Nicht alle DBMS können diesen Ansatz unterstützen.
Es gibt Alternativen, aber sie sind auch unvollkommen. Der erste dieser Ansätze wird als "Keyset-Paging" oder "Suchmethode" bezeichnet und besteht aus Folgendem: Nach dem Empfang eines Teils können Sie sich die Werte der Felder im letzten Datensatz auf der Seite merken und sie dann verwenden, um den nächsten Teil abzurufen. Zum Beispiel haben wir die folgende Anfrage ausgeführt:SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Und im letzten Datensatz haben wir den Wert des Bestelldatums '2014-06-29' erhalten. Um zur nächsten Seite zu gelangen, können Sie Folgendes versuchen:SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Das Problem ist, dass OrderDate ein nicht eindeutiges Feld ist und die oben erwähnte Bedingung wahrscheinlich viele der erforderlichen Zeilen überspringt. Um diese Anforderung eindeutig zu machen, müssen Sie der Bedingung ein eindeutiges Feld hinzufügen (angenommen, 75074 ist der letzte Wert des Primärschlüssels aus dem ersten Teil):SELECT * FROM Sales.SalesOrderHeader
WHERE (OrderDate = '2014-06-29' AND SalesOrderID < 75074)
OR (OrderDate < '2014-06-29')
ORDER BY OrderDate DESC, SalesOrderID DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Diese Option funktioniert ordnungsgemäß, ist jedoch im Allgemeinen schwierig zu optimieren, da die Bedingung den Operator OR enthält. Wenn der Wert des Primärschlüssels mit dem Wachstum von OrderDate wächst, kann die Bedingung vereinfacht werden, indem nur der Filter von SalesOrderID belassen wird. Wenn jedoch keine strikte Korrelation zwischen den Werten des Primärschlüssels und dem Feld besteht, nach dem das Ergebnis sortiert ist, kann dieses ODER in den meisten DBMS nicht vermieden werden. Die mir bekannte Ausnahme ist PostgreSQL, wo der Tupelvergleich vollständig unterstützt wird und die obige Bedingung als "WHERE (OrderDate, SalesOrderID) <('2014-06-29', 75074) geschrieben werden kann. Wenn Sie einen zusammengesetzten Schlüssel mit diesen beiden Feldern haben, sollte eine ähnliche Anforderung ziemlich einfach sein.Der zweite alternative Ansatz kann beispielsweise in der ElasticSearch-Bildlauf-API oder gefunden werdenCosmos DB - Wenn eine Abfrage zusätzlich zu Daten eine spezielle Kennung zurückgibt, mit der Sie den nächsten Datenstapel abrufen können. Wenn dieser Bezeichner eine unbegrenzte Lebensdauer hat (wie in Comsos DB), ist dies eine hervorragende Möglichkeit, Paging mit sequentiellem Übergang zwischen Seiten zu implementieren (Option 2 oben erwähnt). Mögliche Nachteile: Nicht alle DBMS werden unterstützt. Die empfangene nächste Stapelkennung hat möglicherweise eine begrenzte Lebensdauer, was im Allgemeinen nicht für die Implementierung der Benutzerinteraktion geeignet ist (z. B. die ElasticSearch-Bildlauf-API).Komplexe Filterung
Wir erschweren die Aufgabe weiter. Angenommen, es ist erforderlich, die sogenannte facettierte Suche zu implementieren, die jedem aus Online-Shops bekannt ist. Die obigen Beispiele, die auf der Auftragstabelle basieren, sind in diesem Fall nicht sehr aussagekräftig. Daher wechseln wir aus der AdventureWorks-Datenbank zur Produkttabelle:Was ist die Idee der facettierten Suche? Dabei wird für jedes Filterelement die Anzahl der Datensätze angezeigt, die diesem Kriterium entsprechen, wobei die in allen anderen Kategorien ausgewählten Filter berücksichtigt werden .Wenn wir in diesem Beispiel beispielsweise die Kategorie Fahrräder und die Farbe Schwarz auswählen, werden in der Tabelle nur schwarze Fahrräder angezeigt, aber gleichzeitig:- Für jedes Kriterium der Gruppe „Kategorien“ wird die Anzahl der Produkte aus dieser Kategorie in Schwarz angezeigt.
- Für jedes Kriterium der Gruppe Farben wird die Anzahl der Fahrräder dieser Farbe angezeigt.
Hier ist ein Beispiel für die Ausgabe des Ergebnisses für solche Bedingungen:Wenn neben der Kategorie „Kleidung“ auch die verfügbaren schwarzen Kleidungsstücke in der Tabelle angezeigt werden. Die Anzahl der schwarzen Produkte im Abschnitt "Farbe" wird ebenfalls gemäß den neuen Bedingungen neu berechnet. Nur im Abschnitt "Kategorien" ändert sich nichts ... Ich hoffe, diese Beispiele reichen aus, um den bekannten facettierten Suchalgorithmus zu verstehen.Stellen Sie sich nun vor, wie dies relational implementiert werden kann. Für jede Gruppe von Kriterien wie Kategorie und Farbe ist eine separate Anforderung erforderlich:SELECT pc.ProductCategoryID, pc.Name, COUNT(1) FROM Production.Product p
INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
INNER JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE p.Color = 'Black'
GROUP BY pc.ProductCategoryID, pc.Name
ORDER BY COUNT(1) DESC
SELECT Color, COUNT(1) FROM Production.Product p
INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
WHERE ps.ProductCategoryID = 1
GROUP BY Color
ORDER BY COUNT(1) DESC
Was ist falsch an dieser Entscheidung? Sehr einfach - es skaliert nicht gut. Jeder Filterabschnitt erfordert eine separate Abfrage zum Zählen von Mengen, und diese Abfragen sind nicht die einfachsten. In Online-Shops kann es in einigen Abschnitten mehrere Dutzend Abschnitte des Filters geben, was ein ernstes Leistungsproblem darstellen kann.Normalerweise bieten sie mir nach diesen Aussagen einige Lösungen an, nämlich:- Kombinieren Sie alle Mengenzahlen in einer Abfrage. Technisch ist dies mit dem Schlüsselwort UNION möglich, nur hilft es nicht viel bei der Leistung - auf jeden Fall muss die Datenbank jedes Fragment von Grund auf neu ausführen.
- . , . , . , 10 «», 5 . «» , -. 9- , , . 50 , , 250. , . , 5-10 . , , - , , ( ).
Glücklicherweise hat eine solche Aufgabe seit langem ausreichend effektive Lösungen, die vorhersehbar mit großen Datenmengen funktionieren. Für jede dieser Optionen ist es sinnvoll, die Neuberechnung der Facetten und das Abrufen der Ergebnisseite in zwei parallele Aufrufe an den Server zu unterteilen und die Benutzeroberfläche so zu organisieren, dass das Laden der Daten in die Facetten die Anzeige der Suchergebnisse nicht beeinträchtigt.- «» . , , , , — «1425 , ?» , «». «». , , . -. , , .
- search engine , Solr, ElasticSearch, Sphinx . «» . , , — . , search engine , : , , ; search engine . — . « ». «» , , . , - transactional outbox .
- — , . «» «» — , :
- , — #2.
- faceted search, :
- .
- search engine Solr, ElasticSearch, Sphinx . , , .
- faceted search . , , .
- SQL , , , ( «» ). , — «». , .