Wie optimiert man SQL-Abfragen? (ausführliche Erklärung)

Warum sollten wir optimieren

Der Durchsatzengpass des Systems zeigt sich oft in der Zugriffsgeschwindigkeit der Datenbank, Das heißt, während die Anwendung ausgeführt wird, befinden sich immer mehr Daten in der Datenbank und die Verarbeitungszeit verlangsamt sich entsprechend. Darüber hinaus werden die Daten auf der Festplatte gespeichert und die Lese- und Schreibgeschwindigkeit ist nicht vergleichbar das des Speichers.

Wie wäre es mit der Optimierung

1. Beim Entwurf der Datenbank: Entwurf von Datenbanktabellen und -feldern, Speicher-Engine

2. Nutzen Sie die von MySQL selbst bereitgestellten Funktionen wie Indizes und Optimierung des Anweisungsschreibens

3. MySQL-Cluster, Unterdatenbank und Untertabelle, Lese-/Schreibtrennung

Das Internet hat viel Erfahrung in der Optimierung von SQL-Anweisungen, daher legt dieser Artikel diese beiseite und versucht, die DAO-Schicht und die Optimierung des Datenbankdesigns zu optimieren und zwei einfache Beispiele aufzulisten

Beispiel 1: ERP Abfrageoptimierung

Aktuelle Situationsanalyse:

1. Fehlende zugehörige Indizes
2. MySQL selbst hat eine begrenzte Leistung und unterstützt die Zuordnung mehrerer Tabellen nicht gut . Die aktuelle Leistung konzentriert sich hauptsächlich auf Listenabfragen, die viele Tabellen verknüpfen

Gegenmaßnahmen:

1 Erforderliche Indizes hinzufügen: Zeigen Sie die Ausführungsdatensätze über EXPLAIN an und fügen Sie Indizes entsprechend hinzu Ausführungsplan;
2 Zählen Sie zuerst die Primärschlüssel der Haupttabelle der Geschäftsdaten, erhalten Sie eine kleinere Ergebnismenge und verwenden Sie dann die mit der Ergebnismenge verknüpfte Abfrage.
1) Fragen Sie zuerst den Primärschlüssel ab und zeigen Sie ihn an Geschäftsdaten basierend auf der Haupttabelle und den Bedingungen
2) Verwenden Sie den Primärschlüssel als Abfragebedingung und verknüpfen Sie dann andere verwandte Tabellen, um die erforderlichen Geschäftsfelder abzufragen
3 ) Beim Abfragen der Haupttabelle für Abfragebedingungen das mit anderen Tabellen verknüpft werden müssen, müssen Sie die Tabellenzuordnung nur dann einrichten, wenn Sie diese Bedingung festlegen

例如 有如下表 TT_A   TT_B    TT_C  TT_D

假设未优化前的SQL是这样的

SELECT
    A.ID,
    ....
    B.NAME,
    .....
    C.AGE,
    ....
    D.SEX
    .....

FROM  TT_A A
LEFT JOIN TT_B B ON A.ID  = B.ITEM_ID
LEFT JOIN TT_C C ON B.ID = C.ITEM_ID
LEFT JOIN TT_D D ON C.ID = D.ITEM_ID
WHERE 1=1AND A.XX = ?AND A.VV = ?.....

那么优化后的SQL是

第一步

SELECT
    A.ID

FROM  TT_A A
WHERE 1=1AND A.XX = ?AND A.VV = ?第二步

SELECT
    A.ID,
    ....
    B.NAME,
    .....
    C.AGE,
    ....
    D.SEX
    .....
FROM  ( SELECT A.ID,..... FROM  TT_A  WHERE ID IN (1,2,3..)  ) A
LEFT JOIN TT_B B ON A.ID  = B.ITEM_ID
LEFT JOIN TT_C C ON B.ID = C.ITEM_ID
LEFT JOIN TT_D D ON C.ID = D.ITEM_ID
WHERE 1=1AND A.XX = ?AND A.VV = ?

Zusammenfassung:

Diese Art der Optimierung ist Geeignet für Listenabfragen, da die Bedingungen einer Listenabfrage im Allgemeinen mit der Haupttabelle verknüpft sind. Verwenden Sie diese Funktion daher zum Erstellen von Schlüsselfeldindizes und reduzieren Sie gleichzeitig die Haupttabelle durch die Einschränkung der Abfragebedingungen erheblich. Auf diese Weise können andere Tabellen viel schneller verknüpft werden

Beispiel 2: Optimierung der Artikelsuche

Angenommen, Sie möchten eine Artikelsuchfunktion für Tieba erstellen Der einfachste und direkteste Weg Die Speicherstruktur besteht darin, eine relationale Datenbank zu verwenden, um eine solche relationale Datenbanktabelle TT_ARTICLES zum Speichern von Artikeln zu erstellen:

Wenn dann das aktuelle Suchschlüsselwort „ target“, können wir den String-Matching verwenden, um passende Abfragen für die Spalte CONTENT durchzuführen:

select * from ARTICLES where CONTENT like '% 目标 %';

Dadurch wird die Suchfunktion einfach implementiert. Dieser Ansatz weist jedoch offensichtliche Probleme auf, das heißt, die Verwendung von % für den Zeichenfolgenabgleich ist sehr ineffizient, sodass eine solche Abfrage die gesamte Tabelle durchlaufen muss (vollständiger Tabellenscan). Bei wenigen oder Dutzenden Artikeln ist das kein Problem, bei Hunderttausenden oder Millionen Artikeln ist diese Methode jedoch völlig undurchführbar. Ganz zu schweigen davon, dass eine einzelne relationale Datenbanktabelle nicht so große Datenmengen aufnehmen kann, der Zeitaufwand ist hier unvorstellbar, wenn sie gescannt werden muss

Also müssen wir „einführen“. Inversion"-"Indizierungs"-Technologie. Im oben beschriebenen Szenario können wir dieses Konzept zur Erläuterung in zwei Teile aufteilen: Okay, die obige ARTICLES-Tabelle existiert noch, aber jetzt muss eine Schlüsselworttabelle KEYWORDS hinzugefügt werden und die KEYWORD-Spalte muss daher indiziert werden Dieses Schlüsselwort kann schnell gefunden werden:

Natürlich benötigen wir auch eine Beziehungstabelle, um die KEYWORDS-Tabelle und die ARTICLES-Tabelle, KEYWORD_ID und ARTICLE_ID als Unions-Primärschlüssel

Sie sehen, es handelt sich tatsächlich um eine Viele-zu-Viele-Beziehung, das heißt, dasselbe Schlüsselwort kann in mehreren Artikeln vorkommen und ein Artikel kann mehrere verschiedene Schlüsselwörter enthalten. Auf diese Weise können wir zuerst die entsprechende KEYWORD_ID aus der Tabelle KEYWARDS basierend auf den indizierten Schlüsselwörtern finden, dann die ARTICLE_ID basierend auf der obigen Zuordnungstabelle finden und sie dann verwenden, um den entsprechenden Artikel in der Tabelle ARTICLES zu finden.

Zusammenfassung:

Dies scheint drei Suchvorgänge zu sein, aber da der Index jedes Mal verwendet wird, entfällt ein vollständiger Tabellenscan, wenn die Datenmenge gering ist , Die Geschwindigkeit ist nicht langsam und bei der Implementierung mit SQL kann dieser Prozess vollständig in eine SQL-Anweisung eingefügt werden. Wenn die Datenmenge klein ist, ist die obige Methode ausreichend. Dadurch werden die Leistungsprobleme gelöst, die durch vollständige Tabellenscans und String-%-Match-Abfragen verursacht werden.

Zusammenfassung:

Wenn Sie während des technischen Interviews praktische Beispiele geben oder direkt über die Probleme und Vorteile in Ihrem Entwicklungsprozess sprechen können, wird der Interviewzweig verwendet Fügen Sie viel hinzu, und Ihre Antworten sollten logischer sein. Gehen Sie nicht hier und da vor, was Sie leicht verwirren kann. Wenn Sie beispielsweise gefragt werden, wie Sie SQL optimieren können, sollten Sie nicht direkt mit dem Hinzufügen von Indizes antworten. Sie können wie folgt antworten:

Hallo Interviewer, zunächst einmal ist unser Projekt-DB-Datenvolumen auf einen Engpass gestoßen, der dazu führt, dass die Listenabfrage sehr langsam ist und den Benutzern ein schlechtes Erlebnis beschert. Um dieses Problem zu lösen, gibt es viele Methoden, wie zum Beispiel die meisten Grundlegendes Datenbanktabellendesign. Grundlegende SQL-Optimierung, MYSQL-Clustering, Lese-/Schreibtrennung, Unterdatenbanken und Untertabellen, Hinzufügen von Cache-Ebenen zur Architektur usw. Ihre Vor- und Nachteile ... Wir kombinieren diese und kombinieren sie dann mit dem Merkmale unseres Projekts Schließlich entscheiden wir bei der Auswahl der Technologie.

Wenn Sie die Fragen so geordnet und fundiert beantworten und auch über so viele Wissenspunkte außerhalb der Fragen sprechen, wird der Interviewer denken, dass Sie nicht nur eine Person sind, die Code schreiben kann, sondern dass Sie haben eine klare Logik. Sie haben Ihr eigenes Verständnis und Denken über die Technologieauswahl

Dieser Artikel stammt aus der Rubrik SQL-Tutorial, willkommen zum Lernen!

Das obige ist der detaillierte Inhalt vonWie optimiert man SQL-Abfragen? (ausführliche Erklärung). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!