1. Wählen Sie die am besten geeigneten Feldattribute aus.
MySQL kann den Zugriff auf große Datenmengen gut unterstützen, aber im Allgemeinen gilt: Je kleiner die Tabelle in der Datenbank, desto schneller werden die darauf ausgeführten Abfragen. Um eine bessere Leistung zu erzielen, können wir daher beim Erstellen einer Tabelle die Breite der Felder in der Tabelle so klein wie möglich einstellen. Wenn Sie beispielsweise das Feld „Postleitzahl“ auf CHAR(255) festlegen, wird der Datenbank offensichtlich unnötiger Speicherplatz hinzugefügt. Selbst die Verwendung des Typs VARCHAR ist überflüssig, da CHAR(6) in Ordnung ist. Ebenso sollten wir, wenn möglich, MEDIUMINT anstelle von BIGIN verwenden, um Ganzzahlfelder zu definieren.
Eine weitere Möglichkeit zur Verbesserung der Effizienz besteht darin, Felder nach Möglichkeit auf NOT NULL zu setzen, damit die Datenbank bei zukünftigen Abfragen keine NULL-Werte vergleichen muss.
Für einige Textfelder, wie zum Beispiel „Provinz“ oder „Geschlecht“, können wir sie als ENUM-Typen definieren. Denn in MySQL wird der ENUM-Typ als numerische Daten behandelt und numerische Daten werden viel schneller verarbeitet als Texttypen. Auf diese Weise können wir die Leistung der Datenbank verbessern.
2. Verwenden Sie Joins (JOIN) anstelle von Unterabfragen (Sub-Queries).
MySQL unterstützt SQL-Unterabfragen ab 4.1. Mit dieser Technik können Sie mit einer SELECT-Anweisung eine einzelne Spalte mit Abfrageergebnissen erstellen und dieses Ergebnis dann als Filterbedingung in einer anderen Abfrage verwenden. Wenn wir beispielsweise Kunden löschen möchten, die keine Bestellungen in der Basis-Kundeninformationstabelle haben, können wir mithilfe einer Unterabfrage zunächst die IDs aller Kunden, die Bestellungen aufgegeben haben, aus der Verkaufsinformationstabelle abrufen und dann die Ergebnisse an übergeben die Hauptabfrage, wie unten gezeigt:
DELETE FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)
Durch die Verwendung von Unterabfragen können viele SQL-Vorgänge ausgeführt werden, für deren Ausführung logischerweise mehrere Schritte gleichzeitig erforderlich sind. Außerdem können Transaktions- oder Tabellensperren vermieden werden, und das Schreiben ist ebenfalls einfach. In einigen Fällen können Unterabfragen jedoch durch effizientere Verknüpfungen (JOIN) ersetzt werden. Angenommen, wir möchten alle Benutzer abrufen, die keine Bestelldatensätze haben, können wir die folgende Abfrage verwenden:
SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)
Wenn Sie die Verbindung (JOIN) verwenden, um diese Abfrage abzuschließen, ist die Geschwindigkeit viel schneller. Insbesondere wenn in der Salesinfo-Tabelle ein Index für CustomerID vorhanden ist, ist die Leistung wie folgt:
SELECT * FROM customerinfo
LINKS BEITRAGEN salesinfoON customerinfo.CustomerID=salesinfo.
Kunden-ID
WHERE salesinfo.CustomerID IS NULL
-Verbindung (JOIN). Der Grund, warum es effizienter ist, besteht darin, dass MySQL keine temporäre Tabelle im Speicher erstellen muss, um diese logische zweistufige Abfrage abzuschließen.
3. Verwenden Sie Union (UNION), um manuell erstellte temporäre Tabellen zu ersetzen.
MySQL unterstützt UNION-Abfragen ab Version 4.0, die zwei oder mehr SELECT-Abfragen, die die Verwendung temporärer Tabellen erfordern, in einer Abfrage zusammenführen können. Wenn die Abfragesitzung des Clients endet, wird die temporäre Tabelle automatisch gelöscht, um sicherzustellen, dass die Datenbank aufgeräumt und effizient ist. Wenn wir UNION zum Erstellen einer Abfrage verwenden, müssen wir UNION nur als Schlüsselwort verwenden, um mehrere SELECT-Anweisungen zu verbinden. Es ist zu beachten, dass die Anzahl der Felder in allen SELECT-Anweisungen gleich sein muss. Das folgende Beispiel zeigt eine Abfrage mit UNION.
WÄHLEN Sie Name und Telefon vom Kunden aus
UNION
SELECT Name, BirthDate FROM Author
UNION
SELECT Name, Lieferant AUS Produkt
4. Transaktionen
Obwohl wir Unterabfragen, Verbindungen (JOIN) und Unions (UNION) verwenden können, um eine Vielzahl von Abfragen zu erstellen, können nicht alle Datenbankoperationen mit nur einer ausgeführt werden. Oder sie können mit nur einer abgeschlossen werden ein paar SQL-Anweisungen. Häufiger ist eine Reihe von Erklärungen erforderlich, um eine bestimmte Art von Arbeit abzuschließen. Wenn in diesem Fall jedoch eine bestimmte Anweisung in diesem Anweisungsblock fehlerhaft ausgeführt wird, wird die Funktionsweise des gesamten Anweisungsblocks unsicher. Stellen Sie sich vor, Sie möchten bestimmte Daten gleichzeitig in zwei verwandte Tabellen einfügen. Dies kann passieren: Nachdem die erste Tabelle erfolgreich aktualisiert wurde, tritt eine unerwartete Situation in der Datenbank auf, die dazu führt, dass der Vorgang in der zweiten Tabelle nicht abgeschlossen wird. Auf diese Weise werden die Daten unvollständig und sogar die Daten in der Datenbank werden zerstört. Um diese Situation zu vermeiden, sollten Sie Transaktionen verwenden. Ihre Funktion ist: Entweder ist jede Anweisung im Anweisungsblock erfolgreich oder schlägt fehl. Mit anderen Worten: Die Konsistenz und Integrität der Daten in der Datenbank kann gewahrt bleiben. Die Dinge beginnen mit dem Schlüsselwort BEGIN und enden mit dem Schlüsselwort COMMIT. Wenn in diesem Zeitraum ein SQL-Vorgang fehlschlägt, kann der ROLLBACK-Befehl die Datenbank in den Zustand vor dem Start von BEGIN zurückversetzen.
BEGINNEN;
INSERT INTO salesinfo SET CustomerID=14;
Inventar aktualisieren SET Menge=11
WHERE item='book';
COMMIT;
Eine weitere wichtige Rolle von Transaktionen besteht darin, dass sie, wenn mehrere Benutzer gleichzeitig dieselbe Datenquelle verwenden, die Methode zum Sperren der Datenbank verwenden können, um Benutzern eine sichere Zugriffsmethode bereitzustellen, die sicherstellen kann, dass die Vorgänge des Benutzers nicht blockiert werden durch andere Benutzer gestört.
5. Sperren von Tabellen
Obwohl Transaktionen eine sehr gute Möglichkeit sind, die Integrität der Datenbank aufrechtzuerhalten, beeinträchtigen sie aufgrund ihrer Exklusivität manchmal die Leistung der Datenbank, insbesondere in großen Anwendungssystemen. Da die Datenbank während der Ausführung der Transaktion gesperrt wird, können andere Benutzeranforderungen nur warten, bis die Transaktion beendet ist. Wenn ein Datenbanksystem nur von wenigen Benutzern verwendet wird, stellen die Auswirkungen von Transaktionen kein großes Problem dar. Wenn jedoch Tausende von Benutzern gleichzeitig auf ein Datenbanksystem zugreifen, beispielsweise auf eine E-Commerce-Website, führt dies zu ernsthaften Problemen Reaktionsverzögerung.
Tatsächlich können wir in manchen Fällen eine bessere Leistung erzielen, indem wir die Tabelle sperren. Das folgende Beispiel verwendet die Sperrtabellenmethode, um die Transaktionsfunktion im vorherigen Beispiel abzuschließen.
LOCK TABLE-Inventar SCHREIBEN
WÄHLEN Sie die Menge AUS dem Lagerbestand aus
WHEREItem='book';
...
Inventar aktualisieren SET Menge=11
WHEREItem='book';
UNLOCK TABLES
Hier verwenden wir eine SELECT-Anweisung, um die Anfangsdaten abzurufen, und verwenden durch einige Berechnungen eine UPDATE-Anweisung, um die neuen Werte in der Tabelle zu aktualisieren. Die LOCK TABLE-Anweisung mit dem Schlüsselwort WRITE stellt sicher, dass vor der Ausführung des UNLOCK TABLES-Befehls kein weiterer Zugriff auf den Bestand zum Einfügen, Aktualisieren oder Löschen erfolgt.
6.
Die Verwendung von Fremdschlüsseln zum Sperren der Tabelle kann die Integrität der Daten aufrechterhalten, jedoch nicht die Relevanz der Daten garantieren. Zu diesem Zeitpunkt können wir Fremdschlüssel verwenden. Beispielsweise kann ein Fremdschlüssel sicherstellen, dass jeder Verkaufsdatensatz auf einen bestehenden Kunden verweist. Hier kann der Fremdschlüssel die CustomerID in der Tabelle „customerinfo“ der Tabelle „customerID“ in der Tabelle „salesinfo“ zuordnen. Jeder Datensatz ohne gültige CustomerID wird nicht aktualisiert oder in salesinfo eingefügt.
TABELLE Kundeninformationen ERSTELLEN
(
CustomerID INT NOT NULL ,
PRIMÄRSCHLÜSSEL (Kunden-ID)
) TYPE = INNODB;
CREATE TABLE salesinfo
(
SalesID INT NICHT NULL,
CustomerID INT NOT NULL,
PRIMÄRSCHLÜSSEL (CustomerID, SalesID),
FOREIGN KEY (CustomerID) REFERENZEN customerinfo
(CustomerID) AUF DELETECASCADE
) TYPE = INNODB;
Beachten Sie den Parameter „ON DELETE CASCADE“ im Beispiel. Dieser Parameter stellt sicher, dass beim Löschen eines Kundendatensatzes in der Tabelle „customerinfo“ auch alle Datensätze, die sich auf den Kunden in der Tabelle „salesinfo“ beziehen, automatisch gelöscht werden. Wenn Sie Fremdschlüssel in MySQL verwenden möchten, müssen Sie beim Erstellen der Tabelle daran denken, den Tabellentyp als transaktionssicheren InnoDB-Typ zu definieren. Dieser Typ ist nicht der Standardtyp für MySQL-Tabellen. Die definierte Methode besteht darin, TYPE=INNODB zur CREATE TABLE-Anweisung hinzuzufügen. Wie im Beispiel gezeigt.
7. Die Verwendung von Indizes
ist eine gängige Methode zur Verbesserung der Datenbankleistung. Dadurch kann der Datenbankserver bestimmte Zeilen viel schneller abrufen als ohne Index, insbesondere wenn die Abfrageanweisung MAX(), MIN() und ORDERBY enthält. Die Leistungsverbesserung ist deutlicher. Welche Felder sollten also indiziert werden? Im Allgemeinen sollten Indizes auf Feldern erstellt werden, die für die JOIN-, WHERE-Beurteilung und ORDER BY-Sortierung verwendet werden. Versuchen Sie, kein Feld in der Datenbank zu indizieren, das eine große Anzahl doppelter Werte enthält. Bei einem Feld vom Typ ENUM ist es sehr wahrscheinlich, dass es eine große Anzahl doppelter Werte gibt, wie zum Beispiel das Feld „Provinz“. Die Erstellung eines Indexes für ein solches Feld ist im Gegenteil nicht hilfreich Reduzieren Sie die Datenbankleistung. Wir können beim Erstellen der Tabelle gleichzeitig entsprechende Indizes erstellen oder später mit ALTER TABLE oder CREATE INDEX Indizes erstellen. Darüber hinaus unterstützt MySQL ab Version 3.23.23 die Volltextindizierung und -suche. Der Volltextindex ist ein Index vom Typ FULLTEXT in MySQL, kann jedoch nur für Tabellen vom Typ MyISAM verwendet werden. Bei einer großen Datenbank geht es sehr schnell, die Daten in eine Tabelle ohne FULLTEXT-Index zu laden und dann mit ALTER TABLE oder CREATE INDEX den Index zu erstellen. Wenn Sie jedoch Daten in eine Tabelle laden, die bereits über einen FULLTEXT-Index verfügt, ist der Ausführungsprozess sehr langsam.
8. Optimierte Abfrageanweisungen
In den meisten Fällen kann die Verwendung von Indizes die Abfragegeschwindigkeit verbessern, aber wenn SQL-Anweisungen unsachgemäß verwendet werden, kann der Index nicht seine ihm gebührende Rolle spielen. Im Folgenden sind einige Aspekte aufgeführt, auf die geachtet werden sollte. Zunächst ist es am besten, Vergleichsoperationen zwischen Feldern desselben Typs durchzuführen. Vor MySQL-Version 3.23 war dies sogar eine erforderliche Bedingung. Beispielsweise kann ein indiziertes INT-Feld nicht mit einem BIGINT-Feld verglichen werden. Als Sonderfall ist jedoch ein Vergleich möglich, wenn ein Feld vom Typ CHAR und ein Feld vom Typ VARCHAR dieselbe Größe haben. Zweitens: Versuchen Sie, keine Funktionen für die Bearbeitung indizierter Felder zu verwenden.
Wenn Sie beispielsweise die Funktion YEAE() für ein Feld vom Typ DATE verwenden, funktioniert der Index nicht ordnungsgemäß. Obwohl die folgenden beiden Abfragen dieselben Ergebnisse liefern, ist die letztere daher viel schneller als die erstere.
SELECT * FROM order WHERE (OrderDate)＜2001;
SELECT*
FROM order WHERE OrderDate＜"2001-01-01";
Die gleiche Situation tritt auch bei der Berechnung numerischer Felder auf:
SELECT * FROM inventory WHERE Amount/ 7<24;
* FROM inventory WHERE Amount<24*7;
Die beiden oben genannten Abfragen liefern ebenfalls die gleichen Ergebnisse, aber die letztere Abfrage ist viel schneller als die vorherige. Drittens verwenden wir bei der Suche nach Zeichenfeldern manchmal LIKE-Schlüsselwörter und Platzhalter. Obwohl dieser Ansatz einfach ist, geht er auch auf Kosten der Systemleistung. Die folgende Abfrage vergleicht beispielsweise jeden Datensatz in der Tabelle.
WÄHLEN Sie * AUS Büchern
WHERE-Name wie „MySQL%“.
Wenn Sie jedoch die folgende Abfrage verwenden, sind die zurückgegebenen Ergebnisse dieselben, aber die Geschwindigkeit ist viel schneller:
SELECT * FROM Books
WHERE name>="MySQL" und name<"MySQM"
Schließlich sollten Sie darauf achten, dass MySQL keine automatische Typkonvertierung in der Abfrage durchführt, da der Konvertierungsprozess auch den Index unwirksam macht.