Das Datenbanksystem ist der Kern des Managementinformationssystems. Datenbankbasierte Online-Transaktionsverarbeitung (OLTP) und Online-Analyseverarbeitung (OLAP) sind eine der wichtigsten Computeranwendungen in Banken, Unternehmen, Regierungen und anderen Abteilungen. Basierend auf Anwendungsbeispielen und kombiniert mit der Datenbanktheorie stellt dieser Artikel die Anwendung der Abfrageoptimierungstechnologie in realen Systemen vor. Gemessen an den Anwendungsbeispielen der meisten Systeme machen Abfrageoperationen den größten Anteil verschiedener Datenbankoperationen aus, und die SELECT-Anweisung, auf der die Abfrageoperation basiert, ist die teuerste Anweisung unter den SQL-Anweisungen. Wenn sich beispielsweise die Datenmenge auf ein bestimmtes Niveau ansammelt, wie beispielsweise die Tabelleninformationen einer Bankkontodatenbank, die sich auf Millionen oder sogar Dutzende Millionen Datensätze ansammeln, dauert ein vollständiger Tabellenscan oft Dutzende Minuten oder sogar Stunden. Wenn Sie eine bessere Abfragestrategie als einen vollständigen Tabellenscan anwenden, können Sie die Abfragezeit häufig auf einige Minuten reduzieren, was die Bedeutung der Abfrageoptimierungstechnologie zeigt.
Während der Implementierung des Anwendungsprojekts stellte der Autor fest, dass sich viele Programmierer bei der Entwicklung von Datenbankanwendungen mit einigen Front-End-Datenbankentwicklungstools (wie PowerBuilder, Delphi usw.) nur auf die Schönheit der Benutzeroberfläche konzentrieren und nicht zahlen Achten Sie auf die Effizienz von Abfrageanweisungen, was zu Problemen führt. Das entwickelte Anwendungssystem ist ineffizient und verursacht eine erhebliche Ressourcenverschwendung. Daher ist es sehr wichtig, effiziente und sinnvolle Abfrageanweisungen zu entwerfen. Basierend auf Anwendungsbeispielen und kombiniert mit der Datenbanktheorie stellt dieser Artikel die Anwendung der Abfrageoptimierungstechnologie in realen Systemen vor.
Analysieren Sie das Problem
Viele Programmierer glauben, dass die Abfrageoptimierung die Aufgabe des DBMS (Datenbankverwaltungssystem) ist und wenig mit den von Programmierern geschriebenen SQL-Anweisungen zu tun hat. Das ist falsch. Ein guter Abfrageplan kann die Programmleistung oft um ein Vielfaches verbessern. Der Abfrageplan ist eine Sammlung von SQL-Anweisungen, die vom Benutzer übermittelt werden, und der Abfrageplan ist eine Sammlung von Anweisungen, die nach der Optimierung generiert werden. Der Prozess der DBMS-Verarbeitung des Abfrageplans ist wie folgt: Nach Abschluss der lexikalischen und syntaktischen Prüfung der Abfrageanweisung wird die Anweisung an den DBMS-Abfrageoptimierer übermittelt. Nachdem der Optimierer die algebraische Optimierung und Zugriffspfadoptimierung abgeschlossen hat, wird das vorkompilierte Modul verarbeitet Anweisung und generieren Sie einen Abfrageplan, übermitteln Sie ihn dann zur Verarbeitung und Ausführung zum richtigen Zeitpunkt an das System und geben Sie schließlich die Ausführungsergebnisse an den Benutzer zurück. In hohen Versionen tatsächlicher Datenbankprodukte (wie Oracle, Sybase usw.) werden kostenbasierte Optimierungsmethoden verwendet. Diese Optimierung kann die Kosten verschiedener Abfragepläne basierend auf den aus der Systemwörterbuchtabelle erhaltenen Informationen schätzen und dann auswählen a Bessere Planung. Obwohl aktuelle Datenbankprodukte bei der Abfrageoptimierung immer besser werden, sind die von Benutzern übermittelten SQL-Anweisungen die Grundlage für die Systemoptimierung. Es ist schwer vorstellbar, dass ein ursprünglich schlechter Abfrageplan nach der Systemoptimierung effizient wird Entscheidend ist, welche Aussagen die Nutzer schreiben. Wir werden vorerst nicht auf die vom System durchgeführte Abfrageoptimierung eingehen. Im Folgenden konzentrieren wir uns auf Lösungen zur Verbesserung der Benutzerabfragepläne.
Probleme lösen
Im Folgenden wird das relationale Datenbanksystem Informix als Beispiel verwendet, um Methoden zur Verbesserung von Benutzerabfrageplänen vorzustellen.
1. Angemessene Verwendung von Indizes
Der Index ist eine wichtige Datenstruktur in der Datenbank und dient hauptsächlich dazu, die Abfrageeffizienz zu verbessern. Die meisten Datenbankprodukte verwenden heute die zuerst von IBM vorgeschlagene ISAM-Indexstruktur. Die Verwendung von Indizes muss angemessen sein und die Grundsätze für ihre Verwendung lauten wie folgt:
●Erstellen Sie Indizes für Spalten, die häufig verbunden, aber nicht als Fremdschlüssel gekennzeichnet sind, während der Optimierer automatisch Indizes für selten verbundene Felder generiert.
● Erstellen Sie Indizes für Spalten, die häufig sortiert oder gruppiert werden (d. h. nach Vorgängen gruppieren oder sortieren).
●Erstellen Sie Suchen für Spalten mit vielen unterschiedlichen Werten, die häufig in bedingten Ausdrücken verwendet werden. Erstellen Sie keine Indizes für Spalten mit wenigen unterschiedlichen Werten. Beispielsweise gibt es in der Spalte „Geschlecht“ der Mitarbeitertabelle nur zwei unterschiedliche Werte, „Männlich“ und „Weiblich“, sodass kein Index erstellt werden muss. Wenn Sie einen Index erstellen, wird dadurch nicht nur die Abfrageeffizienz nicht verbessert, sondern auch die Aktualisierungsgeschwindigkeit erheblich verringert.
●Wenn mehrere Spalten sortiert werden müssen, können Sie einen zusammengesetzten Index für diese Spalten erstellen.
●Verwenden Sie Systemtools. Beispielsweise verfügt die Informix-Datenbank über ein tbcheck-Tool, das verdächtige Indizes überprüfen kann. Auf einigen Datenbankservern ist der Index möglicherweise ungültig oder die Leseeffizienz kann aufgrund häufiger Vorgänge verringert werden. Wenn eine Abfrage, die den Index verwendet, ohne ersichtlichen Grund langsamer wird, können Sie versuchen, die Integrität des Indexes mit dem Tool tbcheck zu überprüfen und ggf. reparieren. Wenn eine Datenbanktabelle außerdem eine große Datenmenge aktualisiert, kann das Löschen und Neuerstellen des Index die Abfragegeschwindigkeit verbessern.
2. Vermeiden oder vereinfachen Sie das Sortieren
Wiederholtes Sortieren großer Tabellen sollte vereinfacht oder vermieden werden. Der Optimierer vermeidet den Sortierschritt, wenn er einen Index verwenden kann, um die Ausgabe automatisch in der richtigen Reihenfolge zu erzeugen. Hier einige Einflussfaktoren:
●Der Index enthält keine oder mehrere zu sortierende Spalten;
●Die Reihenfolge der Spalten in der Group by- oder Order by-Klausel unterscheidet sich von der Reihenfolge des Index.
●Die sortierten Spalten stammen aus verschiedenen Tabellen.
Um unnötiges Sortieren zu vermeiden, ist es notwendig, Indizes korrekt hinzuzufügen und Datenbanktabellen sinnvoll zusammenzuführen (obwohl dies manchmal die Normalisierung der Tabelle beeinträchtigen kann, lohnt sich die Verbesserung der Effizienz). Wenn eine Sortierung unvermeidbar ist, sollten Sie versuchen, sie zu vereinfachen, indem Sie beispielsweise den Bereich der zu sortierenden Spalten einschränken usw.
3. Eliminieren Sie den sequentiellen Zugriff auf große Tabellenzeilendaten
Bei verschachtelten Abfragen kann der sequenzielle Zugriff auf Tabellen schwerwiegende Auswirkungen auf die Abfrageeffizienz haben. Wenn beispielsweise bei einer sequentiellen Zugriffsstrategie eine Abfrage mit drei verschachtelten Ebenen 1.000 Zeilen auf jeder Ebene abfragt, fragt diese Abfrage 1 Milliarde Datenzeilen ab. Die wichtigste Möglichkeit, dies zu vermeiden, besteht darin, die verbundenen Spalten zu indizieren. Zum Beispiel zwei Tabellen: Studententabelle (Studierendennummer, Name, Alter...) und Kursauswahltabelle (Studierendennummer, Kursnummer, Noten). Sollen zwei Tabellen verbunden werden, muss ein Index auf dem Verbindungsfeld „Studierendennummer“ erstellt werden.
Sie können Gewerkschaften auch verwenden, um sequentiellen Zugriff zu vermeiden. Obwohl für alle Prüfspalten Indizes vorhanden sind, zwingen einige Formen von Where-Klauseln den Optimierer dazu, sequenziellen Zugriff zu verwenden. Die folgende Abfrage erzwingt sequentielle Vorgänge in der Auftragstabelle: SELECT * FROMorders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
Obwohl es Indizes für customer_num und order_num gibt, verwendet der Optimierer in der obigen Anweisung immer noch den sequentiellen Zugriffspfad, um die gesamte Tabelle zu scannen. Da diese Anweisung eine separate Sammlung von Zeilen abruft, sollte sie in die folgende Anweisung geändert werden:
SELECT * AUS Bestellungen WHERE customer_num=104 AND order_num>1001
UNION
SELECT * FROMorders WHERE order_num=1008
Dadurch kann der Indexpfad zur Verarbeitung von Abfragen verwendet werden.
4. Vermeiden Sie korrelierte Unterabfragen
Wenn eine Spaltenbezeichnung sowohl in der Hauptabfrage als auch in der Abfrage in der Where-Klausel erscheint, muss die Unterabfrage wahrscheinlich erneut abgefragt werden, wenn sich der Spaltenwert in der Hauptabfrage ändert. Je mehr Ebenen eine Abfrage verschachtelt ist, desto geringer ist die Effizienz. Daher sollten Unterabfragen so weit wie möglich vermieden werden. Wenn eine Unterabfrage unvermeidbar ist, filtern Sie so viele Zeilen wie möglich aus der Unterabfrage heraus.
5. Vermeiden Sie schwierige reguläre Ausdrücke
Die Schlüsselwörter MATCHES und LIKE unterstützen Wildcard-Matches, technisch gesehen reguläre Ausdrücke. Allerdings ist diese Art des Matchings besonders zeitaufwändig. Zum Beispiel: SELECT * FROM customer WHERE zipcode LIKE „98_ _ _“
Auch wenn ein Index für das Postleitzahlenfeld erstellt wird, wird in diesem Fall weiterhin sequenzielles Scannen verwendet. Wenn Sie die Anweisung in SELECT * FROM customer WHERE zipcode > „98000“ ändern, wird bei der Ausführung der Abfrage der Index zur Abfrage verwendet, was die Geschwindigkeit offensichtlich erheblich verbessert.
Vermeiden Sie außerdem nicht beginnende Teilzeichenfolgen. Beispielsweise verwendet die Anweisung: SELECT * FROM customer WHERE zipcode[2, 3]>"80" eine nicht beginnende Teilzeichenfolge in der where-Klausel, sodass diese Anweisung den Index nicht verwendet.
6. Verwenden Sie temporäre Tabellen, um Abfragen zu beschleunigen
Das Sortieren einer Teilmenge der Tabelle und das Erstellen einer temporären Tabelle kann manchmal Abfragen beschleunigen. Dies hilft, mehrere Sortiervorgänge zu vermeiden und vereinfacht ansonsten die Arbeit des Optimierers. Zum Beispiel: SELECT cust.name, rcVBles.balance,...andere Spalten
SELECT cust.name,rcVBles.balance,...andere Spalten
VON kunden, rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
UND rcvblls.balance>0
UND Kundenpostleitzahl>"98000"
NACH Kundenname BESTELLEN
Soll diese Abfrage mehrmals statt nur einmal ausgeführt werden, können Sie alle unbezahlten Kunden in einer temporären Datei finden und nach Kundennamen sortieren: SELECT cust.name, rcvbles.balance,...other columns
SELECT cust.name,rcvbles.balance,...andere Spalten
VON kunden, rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
UND rcvblls.balance>0
NACH Kundenname BESTELLEN
INTO TEMP cust_with_balance
Fragen Sie dann die temporäre Tabelle wie folgt ab: SELECT * FROM cust_with_balance
WO PLZ>"98000"
Es gibt weniger Zeilen in der temporären Tabelle als in der Haupttabelle, und die physische Reihenfolge entspricht der erforderlichen Reihenfolge, wodurch die Festplatten-E/A reduziert wird, sodass die Abfragearbeitslast erheblich reduziert werden kann.
Hinweis: Nachdem die temporäre Tabelle erstellt wurde, spiegelt sie nicht die Änderung der Haupttabelle wider. Wenn Daten in der Haupttabelle häufig geändert werden, achten Sie darauf, keine Daten zu verlieren.
7. Verwenden Sie die Sortierung, um den nicht sequentiellen Zugriff zu ersetzen
Der nicht sequentielle Plattenzugriff ist der langsamste Vorgang und wird durch die Hin- und Herbewegung des Plattenzugriffsarms dargestellt. SQL-Anweisungen verbergen diese Situation und erleichtern uns das Schreiben von Abfragen, die beim Schreiben von Anwendungen Zugriff auf eine große Anzahl nicht sequentieller Seiten erfordern. Manchmal kann die Verwendung der Sortierfunktionen der Datenbank anstelle eines nicht sequentiellen Zugriffs die Abfrage verbessern.
-