52 SQL-Anweisungen, die Ihnen Leistungsoptimierung beibringen

1. Um die Abfrage zu optimieren, sollten Sie vollständige Tabellenscans vermeiden. Erwägen Sie zunächst die Erstellung von Indizes für die Spalten, die an „where“ und „order by“ beteiligt sind.

2. Versuchen Sie, die Nullwertbeurteilung von Feldern in der Where-Klausel zu vermeiden. NULL ist der Standardwert beim Erstellen einer Tabelle, aber in den meisten Fällen sollten Sie NOT NULL verwenden oder einen speziellen Wert wie 0 oder -1 als Standardwert verwenden.

3. Vermeiden Sie möglichst die Verwendung der Operatoren != oder <> in der Where-Klausel. MySQL verwendet nur Indizes für die folgenden Operatoren: <, <=, =, >, >=, BETWEEN, IN und in einigen Fällen LIKE.

4. Vermeiden Sie die Verwendung von oder zum Verbinden von Bedingungen in der Where-Klausel, da die Engine sonst die Verwendung von Indizes aufgibt und einen vollständigen Tabellenscan durchführt. Sie können UNION verwenden, um Abfragen zu kombinieren: select id from t where num=10 union all select id from t where num=20

5. Verwenden Sie in und nicht in mit Vorsicht, da sonst ein vollständiger Tabellenscan durchgeführt wird. Verwenden Sie für aufeinanderfolgende Werte between statt in: Select id from t where num between 1 and 3

6. Die folgenden Abfragen führen ebenfalls zu einem vollständigen Tabellenscan: select id from t where name like '%abc%' oder select id from t where name like '%abc'. Um die Effizienz zu verbessern, sollten Sie eine Volltextsuche in Betracht ziehen. Der Index wird nur verwendet, wenn eine ID aus t ausgewählt wird, bei der der Name wie „abc%“ lautet.

7. Wenn in der Where-Klausel Parameter verwendet werden, wird auch ein vollständiger Tabellenscan durchgeführt.

8. Vermeiden Sie Ausdrucksoperationen auf Feldern in der Where-Klausel so weit wie möglich und vermeiden Sie Funktionsoperationen auf Feldern in der Where-Klausel so weit wie möglich.

9. In vielen Fällen ist es sinnvoll, exists statt in zu verwenden: select num from a where num in(select num from b). Ersetzen Sie es durch die folgende Anweisung: select num from a where exists(select 1 from b where num=a.num)

10. Indizes können sicherlich die Effizienz der entsprechenden Auswahl verbessern, sie verringern jedoch auch die Effizienz von Einfügungen und Aktualisierungen, da der Index während der Einfügungen oder Aktualisierungen neu erstellt werden kann. Daher muss die Erstellung des Index je nach konkreter Situation sorgfältig überlegt werden. Die Anzahl der Indizes für eine Tabelle sollte 6 nicht überschreiten. Wenn es zu viele sind, sollten Sie überlegen, ob Indizes auf Spalten, die nicht oft verwendet werden, notwendig sind.

11. Vermeiden Sie die Aktualisierung von Clustered-Index-Datenspalten so weit wie möglich, da die Reihenfolge der Clustered-Index-Datenspalten die physische Speicherreihenfolge der Tabellendatensätze ist. Sobald sich der Spaltenwert ändert, wird die Reihenfolge der gesamten Tabellendatensätze angepasst, was erhebliche Ressourcen verbraucht. Wenn das Anwendungssystem die Datenspalten des Clustered-Index häufig aktualisieren muss, müssen Sie überlegen, ob der Index als Clustered-Index erstellt werden soll.

12. Versuchen Sie, numerische Felder zu verwenden. Wenn das Feld nur numerische Informationen enthält, sollten Sie es nicht als Zeichenfeld gestalten, da dies die Leistung von Abfragen und Verbindungen verringert und den Speicheraufwand erhöht.

13. Verwenden Sie nach Möglichkeit varchar/nvarchar statt char/nchar. Erstens benötigen Felder mit variabler Länge weniger Speicherplatz, was Speicherplatz sparen kann. Zweitens ist die Suche in einem relativ kleinen Feld bei Abfragen offensichtlich effizienter.

14. Am besten verwenden Sie nicht „Alles zurückgeben: Wählen Sie aus t aus“, ersetzen Sie „*“ durch eine bestimmte Feldliste und geben Sie keine nicht verwendeten Felder zurück.

15. Versuchen Sie, die Rückgabe großer Datenmengen an den Client zu vermeiden. Wenn die Datenmenge zu groß ist, überlegen Sie, ob die entsprechende Nachfrage angemessen ist.

16. Verwenden Sie Tabellenaliase: Wenn Sie mehrere Tabellen in einer SQL-Anweisung verbinden, verwenden Sie Tabellenaliase und stellen Sie den Alias ​​jeder Spalte voran. Dies kann die Analysezeit verkürzen und Syntaxfehler aufgrund von Spaltenmehrdeutigkeiten reduzieren.

17. Verwenden Sie "temporäre Tabellen", um Zwischenergebnisse zu speichern

Eine wichtige Möglichkeit, SQL-Anweisungen zu vereinfachen, besteht darin, temporäre Tabellen zum vorübergehenden Speichern von Zwischenergebnissen zu verwenden. Die Vorteile temporärer Tabellen gehen jedoch weit darüber hinaus. Durch das vorübergehende Speichern temporärer Ergebnisse in temporären Tabellen werden nachfolgende Abfragen in tempdb ausgeführt, wodurch das mehrmalige Scannen der Haupttabelle im Programm vermieden und die Blockierung der „gemeinsamen Sperre“ durch die „Aktualisierungssperre“ während der Programmausführung erheblich reduziert werden kann, wodurch Blockierungen verringert und die Parallelitätsleistung verbessert wird.

18. Einige SQL-Abfrageanweisungen sollten mit nolock versehen werden. Lesen und Schreiben blockieren sich gegenseitig. Um die Parallelitätsleistung zu verbessern, kann einigen Abfragen nolock hinzugefügt werden, sodass das Schreiben während des Lesens erlaubt ist. Der Nachteil besteht jedoch darin, dass nicht festgeschriebene schmutzige Daten gelesen werden können. Es gibt drei Prinzipien für die Verwendung von Nolock. Die für "Einfügen, Löschen, Ändern" verwendeten Abfrageergebnisse können nicht mit nolock hinzugefügt werden! Bei der abgefragten Tabelle kommt es häufig zu Seitenaufteilungen. Verwenden Sie nolock daher mit Vorsicht! Durch die Verwendung einer temporären Tabelle kann auch das „vorherige Datenbild“ gespeichert werden, das eine ähnliche Funktion wie der Undo-Tablespace von Oracle hat. Wenn eine temporäre Tabelle zur Verbesserung der Parallelitätsleistung verwendet werden kann, verwenden Sie kein Nolock.

19. Allgemeine Vereinfachungsregeln lauten wie folgt: Verwenden Sie nicht mehr als 5 Tabellenverbindungen (JOIN) und erwägen Sie die Verwendung temporärer Tabellen oder Tabellenvariablen zum Speichern von Zwischenergebnissen. Verwenden Sie Unterabfragen seltener und verschachteln Sie Ansichten nicht zu tief. Generell ist es ratsam, nicht mehr als 2 Ansichten zu verschachteln.

20. Berechnen Sie die abzufragenden Ergebnisse vorab, tragen Sie sie in die Tabelle ein und wählen Sie sie dann bei der Abfrage aus. Dies war das wichtigste Mittel vor SQL7.0. Beispielsweise bei der Berechnung von Krankenhausgebühren.

21. Die OR-Klausel kann in mehrere Abfragen zerlegt und über UNION verbunden werden. Ihre Geschwindigkeit hängt nur davon ab, ob der Index verwendet wird. Wenn die Abfrage einen gemeinsamen Index verwenden muss, ist es effizienter, UNION all zu verwenden. Mehrere OR-Klauseln verwenden keine Indizes, daher werden sie in Form von UNION neu geschrieben und versuchen dann, den Index abzugleichen. Eine zentrale Frage ist, ob Indizes verwendet werden sollen.

22. Setzen Sie in der Werteliste nach IN den am häufigsten vorkommenden Wert an den Anfang und den am seltensten vorkommenden Wert ans Ende, um die Anzahl der Urteile zu verringern.

23. Versuchen Sie, die Datenverarbeitung auf dem Server zu platzieren, um den Netzwerk-Overhead zu reduzieren, beispielsweise durch die Verwendung gespeicherter Prozeduren. Eine gespeicherte Prozedur ist eine kompilierte, optimierte SQL-Anweisung, die in einem Ausführungsplan organisiert und in der Datenbank gespeichert wird. Es ist eine Sammlung von Kontrollflusssprachen und natürlich schnell. Dynamisches SQL, das wiederholt ausgeführt wird, kann eine temporär gespeicherte Prozedur verwenden, die (temporäre Tabelle) in Tempdb platziert wird.

24. Wenn der Server über genügend Speicher verfügt, konfigurieren Sie die Anzahl der Threads = maximale Anzahl von Verbindungen + 5, um maximale Effizienz zu erreichen. Andernfalls verwenden Sie die Anzahl der konfigurierten Threads < maximale Anzahl von Verbindungen, um den Threadpool von SQL SERVER zu aktivieren und das Problem zu lösen. Wenn die Anzahl immer noch = maximale Anzahl von Verbindungen + 5 ist, wird die Serverleistung ernsthaft beeinträchtigt.

25. Die Reihenfolge, in der die Abfrage mit der Schreibreihenfolge verknüpft ist

Wählen Sie a.personMemberID, * aus chineseresume a,personmember b, wobei personMemberID = b.referenceid und a.personMemberID = ‚JCNPRH39681‘ (A = B, B = ‚Nummer‘)

Wählen Sie a.personMemberID, * aus chineseresume a,personmember b, wobei a.personMemberID = b.referenceid und a.personMemberID = ‚JCNPRH39681‘ und b.referenceid = ‚JCNPRH39681‘ (A = B, B = ‚Nummer‘, A = ‚Nummer‘)

Wählen Sie a.personMemberID, * aus chineseresume a,personmember b, wobei b.referenceid = ‚JCNPRH39681‘ und a.personMemberID = ‚JCNPRH39681‘ (B = ‚Nummer‘, A = ‚Nummer‘)

26. Versuchen Sie, exists statt select count(1) zu verwenden, um zu bestimmen, ob ein Datensatz vorhanden ist. Die Funktion count wird nur verwendet, um die Anzahl der Zeilen in einer Tabelle zu zählen, und count(1) ist effizienter als count(*).

27. Versuchen Sie, „>=“ statt „>“ zu verwenden.

28. Indexnutzungsspezifikationen: Die Erstellung von Indizes sollte in Verbindung mit der Anwendung betrachtet werden. Es wird empfohlen, dass große OLTP-Tabellen nicht mehr als 6 Indizes haben. Verwenden Sie so oft wie möglich Indexfelder als Abfragebedingungen, insbesondere gruppierte Indizes. Bei Bedarf können Sie den angegebenen Index über den Index index_name erzwingen. Vermeiden Sie Tabellenscans bei Abfragen großer Tabellen. Erwägen Sie bei Bedarf die Erstellung neuer Indizes. Wenn Sie Indexfelder als Bedingungen verwenden und der Index ein gemeinsamer Index ist, muss das erste Feld im Index als Bedingung verwendet werden, um sicherzustellen, dass das System den Index verwendet, da der Index andernfalls nicht verwendet wird. Achten Sie auf die Indexwartung, erstellen Sie Indizes regelmäßig neu und kompilieren Sie gespeicherte Prozeduren neu.

29. Die Spalten in den folgenden bedingten SQL-Anweisungen haben entsprechende Indizes, aber die Ausführungsgeschwindigkeit ist sehr langsam:

SELECT * FROM record WHERE substrINg(card_no,1,4)='5378' (13 Sekunden)

SELECT * FROM Datensatz WHERE Betrag/30< 1000 (11 Sekunden)

SELECT * FROM Datensatz WHERE convert(char(10),date,112)='19991201' (10 Sekunden)

analysieren:

Die Ergebnisse aller Operationen an der Spalte in der WHERE-Klausel werden bei der Ausführung von SQL Spalte für Spalte berechnet, es muss also eine Tabellensuche durchgeführt werden, ohne den Index der Spalte zu verwenden. Wenn diese Ergebnisse beim Kompilieren der Abfrage abgerufen werden können, können sie vom SQL-Optimierer optimiert werden, um den Index zu verwenden und eine Tabellensuche zu vermeiden. Daher wird SQL wie folgt umgeschrieben:

SELECT * FROM Datensatz WHERE Kartennr. wie ‚5378 %‘ (< 1 Sekunde)

SELECT * FROM Datensatz WHERE Betrag< 1000*30 (< 1 Sekunde)

SELECT * FROM Datensatz WHERE Datum = '1999/12/01' (< 1 Sekunde)

30. Wenn Sie einen Datenstapel einfügen oder aktualisieren, verwenden Sie die Stapeleinfügung oder Stapelaktualisierung und aktualisieren Sie niemals jeden Datensatz einzeln!

31. Unter allen gespeicherten Prozeduren werde ich niemals Schleifen verwenden, um etwas zu implementieren, das mit SQL-Anweisungen implementiert werden kann!

(Um beispielsweise alle Tage des vorherigen Monats aufzulisten, würde ich „connect by“ für die rekursive Abfrage verwenden und niemals eine Schleife vom ersten Tag des vorherigen Monats bis zum letzten Tag verwenden.)

32. Wählen Sie die effizienteste Reihenfolge der Tabellennamen (nur gültig im regelbasierten Optimierer):

Der Oracle-Parser verarbeitet die Tabellennamen in der FROM-Klausel von rechts nach links. Die letzte Tabelle in der FROM-Klausel (die Basistabelle, die die Tabelle steuert) wird zuerst verarbeitet. Wenn die FROM-Klausel mehrere Tabellen enthält, müssen Sie die Tabelle mit der geringsten Anzahl von Datensätzen als Basistabelle auswählen. Wenn mehr als 3 Tabellen verknüpft werden müssen, müssen Sie die Schnittmengentabelle als Basistabelle auswählen. Die Schnittmengentabelle bezieht sich auf die Tabelle, auf die andere Tabellen verweisen.

33. Verbessern Sie die Effizienz von GROUP BY-Anweisungen, indem Sie unnötige Datensätze vor GROUP BY herausfiltern. Die folgenden beiden Abfragen liefern dieselben Ergebnisse, aber die zweite ist viel schneller.

Ineffizienz:

JOB AUSWÄHLEN, AVG(SAL)

VON EMP

Nach Job gruppieren

JOB = 'PRÄSIDENT'

ODER JOB = 'MANAGER'

Effizient:

JOB AUSWÄHLEN, AVG(SAL)

VON EMP

WO JOB = 'PRÄSIDENT'

ODER JOB = 'MANAGER'

NACH JOB GRUPPIEREN

34. SQL-Anweisungen werden in Großbuchstaben geschrieben, da Oracle SQL-Anweisungen immer zuerst analysiert und Kleinbuchstaben vor der Ausführung in Großbuchstaben umwandelt.

35. Verwendung von Aliasnamen. Aliase sind eine Anwendungstechnik für große Datenbanken. Tabellennamen und Spaltennamen sind Aliase mit einem Buchstaben in Abfragen. Die Abfragegeschwindigkeit ist 1,5-mal schneller als das Erstellen einer verknüpften Tabelle.

36. Vermeiden Sie Deadlocks, indem Sie in Ihren gespeicherten Prozeduren und Triggern immer in der gleichen Reihenfolge auf dieselbe Tabelle zugreifen. Halten Sie die Transaktionen so kurz wie möglich und minimieren Sie die an einer Transaktion beteiligte Datenmenge. Warten Sie bei einer Transaktion nie auf Benutzereingaben.

37. Vermeiden Sie die Verwendung temporärer Tabellen. Vermeiden Sie die Verwendung temporärer Tabellen, sofern dies nicht unbedingt erforderlich ist. Verwenden Sie stattdessen Tabellenvariablen. Die meiste Zeit (99 %) befinden sich Tabellenvariablen im Speicher, sodass sie schneller sind als temporäre Tabellen. Temporäre Tabellen befinden sich in der TempDb-Datenbank, sodass Vorgänge an temporären Tabellen eine datenbankübergreifende Kommunikation erfordern, die natürlich langsam ist.

38. Es ist am besten, keine Trigger zu verwenden. Das Auslösen eines Triggers und Ausführen eines Trigger-Ereignisses ist an sich schon ein ressourcenintensiver Prozess. Wenn dies durch Einschränkungen erreicht werden kann, sollten Sie möglichst keine Trigger verwenden. Verwenden Sie nicht denselben Trigger für verschiedene Trigger-Ereignisse (Einfügen, Aktualisieren und Löschen). Verwenden Sie in Triggern keinen Transaktionscode.

39. Regeln zur Indexerstellung:

Der Primärschlüssel und der Fremdschlüssel der Tabelle müssen Indizes haben;

Tabellen mit mehr als 300 Datenelementen sollten Indizes haben;

Bei Tabellen, die häufig mit anderen Tabellen verknüpft sind, sollten Indizes für die Verbindungsfelder erstellt werden.

Felder, die häufig in der Where-Klausel vorkommen, insbesondere in großen Tabellen, sollten indiziert werden.

Indizes sollten auf Feldern mit hoher Selektivität aufgebaut sein;

Indizes sollten auf kleinen Feldern erstellt werden. Erstellen Sie keine Indizes auf großen Textfeldern oder sogar sehr langen Feldern.

Die Erstellung zusammengesetzter Indizes erfordert eine sorgfältige Analyse. Stattdessen sollten Indizes mit einzelnen Feldern in Betracht gezogen werden.

Wählen Sie das primäre Spaltenfeld im zusammengesetzten Index richtig aus. Im Allgemeinen handelt es sich dabei um ein Feld mit guter Selektivität.

Kommen mehrere Felder eines zusammengesetzten Indexes oft in der Where-Klausel in UND-Form vor? Gibt es wenige oder keine Einzelfeldabfragen? Wenn ja, können Sie einen zusammengesetzten Index erstellen. Andernfalls ziehen Sie einen Einzelfeldindex in Betracht.

Wenn die im zusammengesetzten Index enthaltenen Felder in der Where-Klausel häufig allein vorkommen, werden sie in mehrere Einzelfeldindizes zerlegt.

Wenn der zusammengesetzte Index mehr als drei Felder enthält, prüfen Sie sorgfältig die Notwendigkeit und reduzieren Sie die Anzahl der zusammengesetzten Felder.

Wenn für diese Felder sowohl ein Einzelfeldindex als auch ein zusammengesetzter Index vorhanden sind, können Sie den zusammengesetzten Index im Allgemeinen löschen.

Erstellen Sie nicht zu viele Indizes für Tabellen, die häufig Datenoperationen durchführen;

Löschen Sie nicht benötigte Indizes, um negative Auswirkungen auf den Ausführungsplan zu vermeiden.

Jeder für eine Tabelle erstellte Index erhöht den Speicheraufwand und der Index erhöht auch den Verarbeitungsaufwand für Einfüge-, Lösch- und Aktualisierungsvorgänge. Darüber hinaus sind zu viele zusammengesetzte Indizes im Allgemeinen wertlos, wenn es Einzelfeldindizes gibt. Im Gegenteil, sie verringern die Leistung beim Hinzufügen und Löschen von Daten, insbesondere bei Tabellen, die häufig aktualisiert werden, wo die negativen Auswirkungen größer sind.

Versuchen Sie, keinen Index für ein Feld in Ihrer Datenbank zu erstellen, das eine große Anzahl wiederholter Werte enthält.

40. Zusammenfassung der MySQL-Abfrageoptimierung: Verwenden Sie das Protokoll langsamer Abfragen, um langsame Abfragen zu finden, verwenden Sie den Ausführungsplan, um zu bestimmen, ob die Abfrage normal ausgeführt wird, und testen Sie Ihre Abfragen immer, um zu sehen, ob sie unter den besten Bedingungen ausgeführt werden. Die Leistung ändert sich mit der Zeit immer. Vermeiden Sie die Verwendung von count(*) für die gesamte Tabelle, da dies die gesamte Tabelle sperren kann. Halten Sie die Abfrage konsistent, damit nachfolgende ähnliche Abfragen den Abfragecache verwenden können.

, verwenden Sie GROUP BY statt DISTINCT, wo es angebracht ist, verwenden Sie indizierte Spalten in WHERE-, GROUP BY- und ORDER BY-Klauseln, halten Sie Indizes einfach und schließen Sie nicht dieselbe Spalte in mehrere Indizes ein. Manchmal verwendet MySQL den falschen Index. Verwenden Sie in dieser Situation USE INDEX. Überprüfen Sie, ob Probleme mit der Verwendung von SQL_MODE=STRICT auftreten. Verwenden Sie für Indexfelder mit weniger als 5 Datensätzen LIMIT statt OR in UNION.

Um SELECT vor UPDATE zu vermeiden, verwenden Sie INSERT ON DUPLICATE KEY oder INSERT IGNORE statt UPDATE. Verwenden Sie nicht MAX. Verwenden Sie indizierte Felder und ORDER BY-Klauseln. LIMIT M, N kann Abfragen in manchen Fällen sogar verlangsamen. Verwenden Sie es sparsam. Verwenden Sie UNION statt Unterabfragen in der WHERE-Klausel. Denken Sie vor dem Neustart von MySQL daran, Ihre Datenbank aufzuwärmen, um sicherzustellen, dass Ihre Daten im Speicher sind und Abfragen schnell sind. Erwägen Sie dauerhafte Verbindungen statt Mehrfachverbindungen, um den Overhead zu reduzieren. Benchmarken Sie Abfragen, einschließlich der Belastung Ihres Servers. Manchmal kann eine einfache Abfrage andere Abfragen beeinflussen, wenn die Belastung Ihres Servers zunimmt. Verwenden Sie SHOW PROCESSLIST, um langsame und problematische Abfragen anzuzeigen. Testen Sie alle verdächtigen Abfragen an gespiegelten Daten, die in einer Entwicklungsumgebung generiert wurden.

41.MySQL-Sicherungsprozess:

Erstellen Sie das Backup vom sekundären Replikationsserver. Stoppen Sie die Replikation während der Sicherung, um Inkonsistenzen bei Datenabhängigkeiten und Fremdschlüsseleinschränkungen zu vermeiden. Stoppen Sie MySQL vollständig und erstellen Sie eine Sicherungskopie der Datenbankdateien.

Wenn Sie MySQL Dump zur Sicherung verwenden, sichern Sie auch die binären Protokolldateien, um sicherzustellen, dass die Replikation nicht unterbrochen wird. Vertrauen Sie nicht auf LVM-Snapshots, da diese wahrscheinlich Dateninkonsistenzen erzeugen, die Ihnen in Zukunft Probleme bereiten. Um die Wiederherstellung einzelner Tabellen zu vereinfachen, exportieren Sie Daten in Tabelleneinheiten – sofern die Daten von anderen Tabellen isoliert sind.

Verwenden Sie --opt, wenn Sie mysqldump verwenden. Überprüfen und optimieren Sie Tabellen, bevor Sie sie sichern. Um den Import zu beschleunigen, deaktivieren Sie vorübergehend Fremdschlüsseleinschränkungen während des Imports.

Um den Import zu beschleunigen, deaktivieren Sie vorübergehend die Eindeutigkeitsprüfung während des Imports. Berechnen Sie die Größe von Datenbanken, Tabellen und Indizes nach jedem Backup, um das Wachstum der Datengröße besser überwachen zu können.

Überwachen Sie Replikationsinstanzen über automatisierte Planungsskripte auf Fehler und Latenzen. Führen Sie regelmäßig Backups durch.

42. Der Abfragepuffer verarbeitet Leerzeichen nicht automatisch. Daher sollten Sie beim Schreiben von SQL-Anweisungen versuchen, die Verwendung von Leerzeichen zu minimieren, insbesondere am Anfang und Ende des SQL (da der Abfragepuffer das erste und letzte Leerzeichen nicht automatisch abfängt).

43. Ist es praktisch abzufragen, ob das Mitglied mid als Standard zum Aufteilen der Tabelle verwendet? Bei allgemeinen Geschäftsanforderungen wird der Benutzername grundsätzlich als Abfragegrundlage verwendet. Normalerweise sollte der Benutzername als Hash-Modul verwendet werden, um die Tabelle aufzuteilen. Möchte man die Tabelle aufteilen, so wird hierfür die für den Code transparente Partitionsfunktion von MySQL verwendet.

Es erscheint unvernünftig, es auf Codeebene zu implementieren.

44. Wir sollten für jede Tabelle in der Datenbank eine ID als Primärschlüssel festlegen. Am besten verwenden Sie einen INT-Typ (UNSIGNED wird empfohlen) und setzen das Flag AUTO_INCREMENT auf automatische Erhöhung.

45. Setzen Sie SET NOCOUNT ON am Anfang aller gespeicherten Prozeduren und Trigger und SET NOCOUNT OFF am Ende.

Es ist nicht erforderlich, nach der Ausführung jeder Anweisung in gespeicherten Prozeduren und Triggern eine DONE_IN_PROC-Nachricht an den Client zu senden.

46. ​​​​MySQL-Abfragen können einen Hochgeschwindigkeits-Abfrage-Cache aktivieren. Dies ist eine der effektiven MySQL-Optimierungsmethoden zur Verbesserung der Datenbankleistung. Wenn dieselbe Abfrage mehrmals ausgeführt wird, ist es viel schneller, Daten aus dem Cache abzurufen und direkt aus der Datenbank zurückzugeben.

47. EXPLAIN SELECT Abfrage wird verwendet, um den Effekt zu verfolgen und anzuzeigen

Mithilfe des Schlüsselworts EXPLAIN können Sie sehen, wie MySQL Ihre SQL-Anweisungen verarbeitet. Dies kann Ihnen dabei helfen, die Leistungsengpässe Ihrer Abfrageanweisungen oder Tabellenstrukturen zu analysieren. Die Ergebnisse der EXPLAIN-Abfrage zeigen Ihnen auch, wie Ihr Index-Primärschlüssel verwendet wird, wie Ihre Datentabelle durchsucht und sortiert wird usw.

48. Verwenden Sie LIMIT 1, wenn nur eine Datenzeile benötigt wird

Manchmal wissen Sie bei der Abfrage einer Tabelle bereits, dass nur ein Ergebnis zurückgegeben wird, Sie müssen jedoch möglicherweise den Cursor abrufen oder möchten die Anzahl der zurückgegebenen Datensätze überprüfen. In diesem Fall kann das Hinzufügen von LIMIT 1 die Leistung steigern. Auf diese Weise beendet die MySQL-Datenbank-Engine die Suche, nachdem ein Datenelement gefunden wurde, anstatt mit der Suche nach dem nächsten übereinstimmenden Datensatz fortzufahren.

49. Wählen Sie die entsprechende Speicher-Engine für die Tabelle aus:

MyISAM: Wird hauptsächlich für Lese- und Einfügevorgänge mit nur wenigen Aktualisierungs- und Löschvorgängen verwendet und erfordert keine hohe Transaktionsintegrität und Parallelität.

Innodb: Anforderungen an Transaktionsverarbeitung und Datenkonsistenz unter gleichzeitigen Bedingungen. Dazu gehören neben Einfügungen und Abfragen auch jede Menge Aktualisierungen und Löschungen. (Innodb reduziert effektiv die durch Löschungen und Aktualisierungen verursachten Sperren). Bei InnoDB-Tabellen, die Transaktionen unterstützen, liegt der Hauptgrund für die Geschwindigkeit darin, dass die Einstellung AUTOCOMMIT standardmäßig aktiviert ist und das Programm BEGIN nicht explizit aufruft, um die Transaktion zu starten. Dies führt dazu, dass jeder eingefügte Datensatz automatisch festgeschrieben wird, was die Geschwindigkeit erheblich beeinträchtigt. Sie können „begin“ aufrufen, bevor SQL ausgeführt wird, und mehrere SQL-Anweisungen können in einer Transaktion kombiniert werden (auch wenn Autocommit aktiviert ist), was die Leistung erheblich verbessert.

50. Optimieren Sie den Datentyp der Tabelle und wählen Sie den entsprechenden Datentyp aus:

Grundsatz: Kleiner ist normalerweise besser, einfach ist gut, alle Felder müssen Standardwerte haben, versuchen Sie Nullwerte zu vermeiden.

Beispiel: Verwenden Sie beim Entwerfen einer Datenbanktabelle möglichst einen kleineren Integer-Typ, um weniger Speicherplatz zu belegen. (Mediumint ist besser geeignet als int)

Beispielsweise die Zeitfelder: Datum/Uhrzeit und Zeitstempel. Datum/Uhrzeit belegt 8 Bytes, während Zeitstempel 4 Bytes belegt, also nur die Hälfte davon. Der durch Zeitstempel dargestellte Bereich liegt zwischen 1970 und 2037, was für die Aktualisierungszeit geeignet ist.

MySQL kann die Speicherung und den Zugriff auf große Datenmengen gut unterstützen, aber im Allgemeinen gilt: Je kleiner die Tabelle in der Datenbank ist, desto schneller sind die darauf ausgeführten Abfragen.

Um eine bessere Leistung zu erzielen, können wir beim Erstellen einer Tabelle die Breite der Felder in der Tabelle so klein wie möglich einstellen. Zum Beispiel,

Wenn Sie beim Definieren des Postleitzahlenfelds diesen Wert auf CHAR(255) setzen, wird der Datenbank offensichtlich unnötiger Speicherplatz hinzugefügt.

Sogar die Verwendung von VARCHAR ist überflüssig, da CHAR(6) die Aufgabe genauso gut erledigt. Ebenso, wenn möglich,

Wir sollten MEDIUMINT statt BIGIN verwenden, um ganzzahlige Felder zu definieren.

Damit die Datenbank bei zukünftigen Abfragen nicht mehr mit NULL-Werten vergleichen muss, sollten Sie versuchen das Feld auf NOT NULL zu setzen.

Für einige Textfelder, wie etwa „Provinz“ oder „Geschlecht“, können wir sie als ENUM-Typ definieren. Weil in MySQL der ENUM-Typ als numerische Daten behandelt wird.

Numerische Daten können viel schneller verarbeitet werden als Textdaten. Auf diese Weise können wir die Performance der Datenbank nochmals verbessern.

51. String-Datentypen: char, varchar, Textauswahlunterschiede

52. Jede Operation an einer Spalte führt zu einem Tabellenscan, einschließlich Datenbankfunktionen, Berechnungsausdrücken usw. Versuchen Sie beim Abfragen, die Operation auf die rechte Seite des Gleichheitszeichens zu verschieben.

Damit ist der Artikel über 52 SQL-Anweisungen zur Leistungsoptimierung abgeschlossen. Weitere relevante Inhalte zur Leistungsoptimierung der SQL-Sprache finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den verwandten Artikeln weiter unten. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!