Abfragen sind die am häufigsten verwendete Operation in der Datenbanktechnologie. Der Vorgang des Abfragevorgangs ist relativ einfach. Nach dem Empfang der vom Client gesendeten SQL-Anweisung führt der Datenbankserver die SQL-Anweisung aus und gibt dann die Abfrageergebnisse an den Client zurück. Obwohl der Prozess sehr einfach ist, haben unterschiedliche Abfragemethoden und Datenbankeinstellungen einen großen Einfluss auf die Abfrageleistung.
Daher werden in diesem Artikel Techniken zur Abfrageoptimierung erläutert, die häufig in MySQL verwendet werden. Zu den Diskussionen gehören: Verbesserung der Abfragegeschwindigkeit durch Abfragepufferung; automatische Optimierung von Abfragen durch MySQL; Verwendung verschiedener Abfrageoptionen zur Verbesserung der Leistung.
1. Verbessern Sie die Abfragegeschwindigkeit durch Abfragepufferung
Wenn wir SQL-Anweisungen zum Abfragen verwenden, führt der Datenbankserver diese SQL-Anweisung im Allgemeinen jedes Mal aus, wenn er SQL vom Client empfängt. Wenn jedoch innerhalb eines bestimmten Intervalls (z. B. innerhalb einer Minute) genau dieselbe SQL-Anweisung empfangen wird, wird sie auf die gleiche Weise ausgeführt. Obwohl dies die Echtzeitnatur der Daten gewährleisten kann, ist für die Daten in den meisten Fällen keine vollständige Echtzeit erforderlich, was bedeutet, dass es zu einer gewissen Verzögerung kommen kann. Wenn dies der Fall ist, lohnt es sich nicht, in kurzer Zeit genau das gleiche SQL auszuführen.
Glücklicherweise stellt uns MySQL die Abfragepufferungsfunktion zur Verfügung (Abfragepufferung kann nur in MySQL 4.0.1 und höher verwendet werden). Wir können die Abfrageleistung bis zu einem gewissen Grad durch Abfrage-Caching verbessern.
Wir können den Abfragepuffer über die Datei my.ini im MySQL-Installationsverzeichnis festlegen. Die Einstellung ist auch sehr einfach, setzen Sie einfach query_cache_type auf 1. Nach dem Festlegen dieses Attributs prüft MySQL vor der Ausführung einer SELECT-Anweisung in seinem Puffer, ob dieselbe SELECT-Anweisung ausgeführt wurde. Wenn dies der Fall ist und das Ausführungsergebnis nicht abgelaufen ist, wird das Abfrageergebnis direkt an den Client zurückgegeben. Beachten Sie jedoch beim Schreiben von SQL-Anweisungen, dass der Abfragepuffer von MySQL die Groß-/Kleinschreibung beachtet. Die folgenden zwei SELECT-Anweisungen lauten wie folgt: SELECT * from TABLE1
Wählen Sie * aus Tabelle 1 aus
Die beiden oben genannten SQL-Anweisungen sind völlig unterschiedliche SELECTs für die Abfragepufferung. Darüber hinaus verarbeitet der Abfragepuffer Leerzeichen nicht automatisch. Daher sollten Sie beim Schreiben von SQL-Anweisungen versuchen, die Verwendung von Leerzeichen zu reduzieren, insbesondere die Leerzeichen am Anfang und Ende von SQL (da der Abfragecache die Leerzeichen nicht automatisch abfängt). Anfang und Ende).
Obwohl das Nichteinrichten eines Abfragepuffers manchmal zu Leistungseinbußen führen kann, gibt es einige SQL-Anweisungen, die Daten in Echtzeit abfragen müssen oder nicht häufig verwendet werden (vielleicht ein- oder zweimal am Tag ausgeführt). Dies erfordert das Ausschalten der Pufferung. Natürlich können Sie den Abfragecache deaktivieren, indem Sie den Wert von query_cache_type festlegen, aber dadurch wird der Abfragecache dauerhaft deaktiviert. MySQL 5.0 bietet eine Methode zum vorübergehenden Deaktivieren des Abfragecaches: SELECT SQL_NO_CACHE field1, field2 FROM TABLE1
Da die obige SQL-Anweisung SQL_NO_CACHE verwendet, sucht der Server unabhängig davon, ob diese SQL-Anweisung zuvor ausgeführt wurde, nicht im Puffer und führt sie jedes Mal aus.
Wir können query_cache_type in my.ini auch auf 2 setzen, sodass der Abfragecache erst verwendet wird, nachdem SQL_CACHE verwendet wurde. SELECT SQL_CALHE * FROM TABLE1
2. Die automatische Optimierung von Abfragen durch MySQL
Indizes sind für Datenbanken sehr wichtig. Indizes können verwendet werden, um die Leistung bei Abfragen zu verbessern. Aber manchmal kann die Verwendung von Indizes die Leistung beeinträchtigen. Wir können uns die folgende SALES-Tabelle ansehen: CREATE TABLE SALES
(
ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
NAME VARCHAR(100) NICHT NULL,
PREIS-FLOAT NICHT NULL,
SALE_COUNT INT NICHT NULL,
SALE_DATE DATUM NICHT NULL,
PRIMÄRSCHLÜSSEL (ID) ，
INDEX (NAME),
INDEX (SALE_DATE)
)
Angenommen, in dieser Tabelle sind Millionen von Daten gespeichert, und wir möchten den Durchschnittspreis der Produktnummer 1000 in den Jahren 2004 und 2005 abfragen. Wir können die folgende SQL-Anweisung schreiben: SELECT AVG(PRICE) FROM SALES
WHERE ID = 1000 UND SALE_DATE ZWISCHEN „01.01.2004“ UND „31.12.2005“;
Wenn die Menge dieses Produkts sehr groß ist, macht es fast 50 % oder mehr der Datensätze in der Tabelle SALES aus. Dann ist die Verwendung des Index für das Feld SALE_DATE zur Berechnung des Durchschnitts etwas langsam. Denn wenn Sie einen Index verwenden, müssen Sie den Index sortieren. Wenn sehr viele Datensätze vorhanden sind, die die Bedingungen erfüllen (z. B. 50 % oder mehr der Datensätze in der gesamten Tabelle), verringert sich die Geschwindigkeit, sodass es besser ist, die gesamte Tabelle zu scannen. Daher entscheidet MySQL automatisch, ob der Index für die Abfrage verwendet wird, basierend auf dem Datenanteil, der die Bedingungen in der gesamten Tabelle erfüllt.
Bei MySQL wird der Index nicht verwendet, wenn der Anteil der oben genannten Abfrageergebnisse an den Datensätzen in der gesamten Tabelle etwa 30 % beträgt. Dieser Anteil wird von MySQL-Entwicklern aufgrund ihrer Erfahrung abgeleitet. Der tatsächliche Skalierungswert variiert jedoch je nach verwendeter Datenbank-Engine.
3. Indexbasierte Sortierung
Eine der Schwächen von MySQL ist die Sortierung. Obwohl MySQL etwa 15.000 Datensätze in einer Sekunde abfragen kann, kann MySQL bei der Abfrage höchstens einen Index verwenden. Wenn daher die WHERE-Bedingung den Index bereits belegt, wird der Index nicht zum Sortieren verwendet, was die Geschwindigkeit der Abfrage erheblich verringert. Wir können uns die folgende SQL-Anweisung ansehen: SELECT * FROM SALES WHERE NAME = „name“ ORDER BY SALE_DATE DESC;
Der Index für das NAME-Feld wurde in der WHERE-Klausel der obigen SQL verwendet, sodass der Index beim Sortieren von SALE_DATE nicht mehr verwendet wird. Um dieses Problem zu lösen, können wir einen zusammengesetzten Index für die SALES-Tabelle erstellen: ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME, SALE_DATE)
Auf diese Weise wird die Geschwindigkeit erheblich verbessert, wenn die obige SELECT-Anweisung zum Abfragen verwendet wird. Seien Sie jedoch vorsichtig, wenn Sie diese Methode verwenden, stellen Sie sicher, dass in der WHERE-Klausel kein Sortierfeld vorhanden ist. Im obigen Beispiel können Sie SALE_DATE nicht zum Abfragen verwenden. Andernfalls ist die Sortierung zwar schneller, es gibt jedoch keinen separaten Index für das Feld SALE_DATE , sodass die Abfrage langsamer wird.
4. Erkennung nicht erreichbarer Anfragen
Beim Ausführen von SQL-Anweisungen werden Sie zwangsläufig auf einige Bedingungen stoßen, die falsch sein müssen. Die sogenannte Must-False-Bedingung besagt, dass diese Bedingung unabhängig von der Änderung der Daten in der Tabelle falsch ist. Zum Beispiel WHERE-Wert < 100 UND Wert > 200. Wir können nie eine Zahl finden, die sowohl kleiner als 100 als auch größer als 200 ist.
Wenn Sie auf solche Abfragebedingungen stoßen, ist es nicht erforderlich, solche SQL-Anweisungen auszuführen. Glücklicherweise kann MySQL diese Situation automatisch erkennen. Wir können uns zum Beispiel die folgende SQL-Anweisung ansehen: SELECT * FROM SALES WHERE NAME = „name1“ AND NAME = „name2“
Die obige Abfrageanweisung sucht nach Datensätzen, deren NAME sowohl name1 als auch name2 entspricht. Offensichtlich handelt es sich hierbei um eine nicht erreichbare Abfrage, und die WHERE-Bedingung muss falsch sein. Bevor MySQL die SQL-Anweisung ausführt, analysiert es zunächst, ob die WHERE-Bedingung eine nicht erreichbare Abfrage ist. Wenn ja, wird die SQL-Anweisung nicht mehr ausgeführt. Um dies zu überprüfen. Wir verwenden zunächst EXPLAIN, um das folgende SQL zu testen: EXPLAIN SELECT * FROM SALES WHERE NAME = „name1“
Die obige Abfrage ist eine normale Abfrage. Wir können sehen, dass das Tabellenelement in den von EXPLAIN zurückgegebenen Ausführungsinformationsdaten SALES ist. Dies zeigt, dass MySQL SALES betreibt. Schauen Sie sich die folgenden Anweisungen noch einmal an: EXPLAIN SELECT * FROM SALES WHERE NAME = „name1“ AND NAME = „name2“
Wir können sehen, dass das Tabellenelement leer ist, was bedeutet, dass MySQL die SALES-Tabelle nicht bearbeitet hat.
5. Verwenden Sie verschiedene Abfrageauswahlmöglichkeiten, um die Leistung zu verbessern
Zusätzlich zur normalen Verwendung der SELECT-Anweisung stellt uns MySQL auch viele Optionen zur Verfügung, die die Abfrageleistung verbessern können. Wie oben erwähnt, sind SQL_NO_CACHE und SQL_CACHE, die zur Steuerung der Abfragepufferung verwendet werden, zwei der Optionen. In diesem Abschnitt stelle ich einige häufig verwendete Abfrageoptionen vor.
1. STRAIGHT_JOIN: Verbindungsreihenfolge erzwingen
Wenn wir zwei oder mehr Tabellen für eine Abfrage verbinden, müssen wir uns nicht darum kümmern, welche Tabelle MySQL zuerst verbindet und welche Tabelle es zuletzt verbindet. All dies wird durch eine Verbindungsreihenfolge bestimmt, die MySQL durch eine Reihe interner Berechnungen und Auswertungen ermittelt. In den folgenden SQL-Anweisungen sind TABLE1 und TABLE2 nicht unbedingt miteinander verbunden: SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1, TABLE2 WHERE …
Wenn Entwickler manuell in die Reihenfolge der Verbindungen eingreifen müssen, müssen sie das Schlüsselwort STRAIGHT_JOIN verwenden, beispielsweise die folgende SQL-Anweisung: SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …
Wie aus der obigen SQL-Anweisung hervorgeht, wird STRAIGHT_JOIN verwendet, um MySQL zu zwingen, Tabellen in der Reihenfolge TABLE1 und TABLE2 zu verknüpfen. Wenn Sie der Meinung sind, dass es effizienter ist, in Ihrer eigenen Reihenfolge beizutreten als in der von MySQL empfohlenen Reihenfolge, können Sie STRAIGHT_JOIN verwenden, um die Verbindungsreihenfolge zu bestimmen.
2. Greifen Sie in die Indexnutzung ein und verbessern Sie die Leistung
Die Verwendung von Indizes wurde oben erwähnt. Unter normalen Umständen entscheidet MySQL, ob und welcher Index bei der Abfrage verwendet werden soll. In einigen Sonderfällen möchten wir jedoch, dass MySQL nur einen oder wenige Indizes verwendet, oder wir möchten einen bestimmten Index nicht verwenden. Dies erfordert die Verwendung einiger Abfrageoptionen von MySQL, um den Index zu steuern.
Beschränken Sie den Umfang der Verwendung von Indizes
Manchmal erstellen wir viele Indizes in der Datentabelle, wenn MySQL den Index auswählt. Manchmal möchten wir jedoch, dass MySQL nur einige Indizes anstelle aller Indizes berücksichtigt. Dies erfordert die Verwendung von USE INDEX zum Festlegen der Abfrageanweisung. * AUS TABELLE1 AUSWÄHLEN, INDEX VERWENDEN (FELD1, FELD2) …
Wie aus der obigen SQL-Anweisung ersichtlich ist, berücksichtigt MySQL bei der Auswahl der Indizes nur die in FIELD1 und FIELD2 erstellten Indizes, unabhängig davon, wie viele Indizes in TABELLE1 eingerichtet wurden.
Beschränken Sie den Bereich der nicht verwendeten Indizes
Wenn wir viele Indizes berücksichtigen müssen und nur wenige ungenutzte Indizes haben, können wir IGNORE INDEX für die umgekehrte Auswahl verwenden. Im obigen Beispiel wird der Index ausgewählt, der berücksichtigt wird, während die Verwendung von IGNORE INDEX dazu dient, den Index auszuwählen, der nicht berücksichtigt wird. * AUS TABELLE1 AUSWÄHLEN INDEX IGNORIEREN (FELD1, FELD2) …
In der obigen SQL-Anweisung werden nur die Indizes für FIELD1 und FIELD2 in der Tabelle TABLE1 nicht verwendet.
Erzwingen Sie die Verwendung eines Index
Die beiden oben genannten Beispiele bieten MySQL beide eine Auswahlmöglichkeit, was bedeutet, dass MySQL diese Indizes nicht verwenden muss. Manchmal hoffen wir, dass MySQL einen bestimmten Index verwenden muss (da MySQL beim Abfragen nur einen Index verwenden kann, kann MySQL nur dazu gezwungen werden, einen Index zu verwenden). Dies erfordert die Verwendung von FORCE INDEX, um diese Funktion abzuschließen. AUSWÄHLEN * AUS TABELLE1 FORCE INDEX (FELD1) …
Die obige SQL-Anweisung verwendet nur den für FELD1 erstellten Index, nicht die Indizes für andere Felder.
3. Verwenden Sie temporäre Tabellen, um die Abfrageleistung zu verbessern
Wenn die Ergebnismenge unserer Abfrage viele Daten enthält, können wir die Ergebnismenge über die Option SQL_BUFFER_RESULT in eine temporäre Tabelle zwingen, sodass die MySQL-Tabellensperre schnell aufgehoben werden kann (damit andere SQL-Anweisungen diese abfragen können). Datensätze)) und können Clients über lange Zeiträume mit großen Datensätzen versorgen. SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …
Ähnlich wie bei der Option SQL_BUFFER_RESULT gibt es die Option SQL_BIG_RESULT. Diese Option wird im Allgemeinen zum Gruppieren oder DISTINCT-Schlüsselwörtern verwendet. Diese Option benachrichtigt MySQL, dass die Abfrageergebnisse bei Bedarf in einer temporären Tabelle abgelegt oder sogar sortiert werden. SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1
6. Fazit
Auch beim Programmieren gibt es ein „20/80-Prinzip“, das heißt, 20 % des Codes nehmen 80 % der Zeit in Anspruch. Das Gleiche gilt für die Entwicklung von Datenbankanwendungen. Die Optimierung von Datenbankanwendungen konzentriert sich auf die Ausführungseffizienz von SQL. Der Schwerpunkt der Datenabfrageoptimierung besteht darin, den Datenbankserver dazu zu bringen, weniger Daten von der Festplatte zu lesen und Seiten sequentiell statt nicht sequentiell zu lesen.