Techniken zur Optimierung von SQL-Abfragen

Neuauflage von Plato

Verfolger: 0

Techniken zur Optimierung von SQL-Abfragen
Bild vom Autor

Auf der Anfängerstufe konzentrieren wir uns nur darauf, die SQL-Abfragen zu schreiben und auszuführen. Wir kümmern uns nicht darum, wie lange die Ausführung dauert oder ob es Millionen von Datensätzen verarbeiten kann. Aber auf der mittleren Ebene erwarten die Leute, dass Ihre Abfrage optimiert ist und nur minimale Zeit für die Ausführung benötigt.

Das Schreiben einer optimierten Abfrage in großen Anwendungen mit Millionen von Datensätzen, wie E-Commerce-Plattformen oder Banksystemen, ist unerlässlich. Angenommen, Sie besitzen ein E-Commerce-Unternehmen mit mehr als einer Million Produkten, und ein Kunde möchte nach einem Produkt suchen. Was ist, wenn die Abfrage, die Sie im Backend geschrieben haben, länger als eine Minute dauert, um dieses Produkt aus der Datenbank abzurufen? Glauben Sie, dass die Kunden Produkte von Ihrer Website kaufen?

Sie müssen die Bedeutung der SQL-Abfrageoptimierung verstehen. In diesem Tutorial zeige ich Ihnen einige Tipps und Tricks, um Ihre SQL-Abfragen zu optimieren und schneller auszuführen. Grundvoraussetzung sind grundlegende SQL-Kenntnisse.

Um zu überprüfen, ob ein bestimmtes Element in der Tabelle vorhanden ist, verwenden Sie die EXIST() Stichwort statt des COUNT() führt die Abfrage optimierter aus.

Die richtigen COUNT(), muss die Abfrage alle Vorkommen dieses bestimmten Elements zählen, was ineffizient sein kann, wenn die Datenbank umfangreich ist. Andererseits, EXIST() prüft nur das erste Vorkommen dieses Elements und stoppt dann, wenn es das erste Vorkommen findet. Das spart viel Zeit.

Außerdem sind Sie nur daran interessiert herauszufinden, ob ein bestimmtes Element vorhanden ist oder nicht. Sie sind nicht daran interessiert, die Anzahl der Vorkommen zu ermitteln. Deswegen auch EXIST() ist besser.

SELECT EXISTS( SELECT * FROM table WHERE myColumn = 'val' );

Die obige Abfrage wird zurückgegeben 1 wenn mindestens eine Tabellenzeile einen Eintrag enthält, in dem eine Spalte benannt ist myColumn hat einen Wert gleich Welle. Sonst geht es zurück 0.

Beide char und varchar Datentypen werden verwendet, um Zeichenfolgen in der Tabelle zu speichern. Aber varchar ist viel speichereffizienter als char.

Der Datentyp char kann nur die definierte Zeichenkette fester Länge speichern. Wenn die Länge der Zeichenfolge kleiner als die feste Länge ist, werden die Leerzeichen aufgefüllt, um ihre Länge gleich der festgelegten Länge zu machen. Dadurch wird unnötig Speicher beim Auffüllen verschwendet. Zum Beispiel,CHAR(100) benötigt 100 Byte Speicher, selbst wenn ein einzelnes Zeichen gespeichert wird.

Andererseits speichert der Datentyp varchar die Zeichenkette variabler Länge, deren Länge kleiner als die angegebene maximale Länge ist. Es füllt die Leerzeichen nicht auf und benötigt nur den Speicher, der der tatsächlichen Länge der Zeichenfolge entspricht. Zum Beispiel, VARCHAR(100) benötigt nur 1 Byte Speicher beim Speichern eines einzelnen Zeichens.

CREATE TABLE myTable ( id INT PRIMARY KEY, charCol CHAR(10), varcharCol VARCHAR(10)
);

Im obigen Beispiel eine Tabelle myTable wird mit zwei Spalten erstellt, charCol und varcharCol mit char- bzw. varchar-Datentypen. charCol nimmt immer 10 Byte Speicher. Im Gegensatz, varcharCol nimmt Speicher gleich der tatsächlichen Größe der darin gespeicherten Zeichenfolge.

Wir müssen die Verwendung von Unterabfragen innerhalb der WHERE-Klausel vermeiden, um eine SQL-Abfrage zu optimieren. Da die Unterabfragen teuer und schwierig auszuführen sein können, wenn sie eine große Anzahl von Zeilen zurückgeben.

Anstatt die Unterabfrage zu verwenden, können Sie dasselbe Ergebnis erhalten, indem Sie eine Verknüpfungsoperation verwenden oder eine korrelierte Unterabfrage schreiben. Eine korrelierte Unterabfrage ist eine Unterabfrage, bei der die innere Abfrage von der äußeren Abfrage abhängt. Und sie sind im Vergleich zu nicht korrelierten Unterabfragen sehr effizient.

Nachfolgend finden Sie ein Beispiel, um den Unterschied zwischen den beiden zu verstehen.

# Using a subquery
SELECT * FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE country = 'INDIA' ); # Using a join operation
SELECT orders.* FROM orders JOIN customers ON orders.customer_id = customers.id WHERE customers.country = 'INDIA';

Im ersten Beispiel sammelt die Unterabfrage zuerst alle Kundennummern, die zu INDIEN gehören, und dann ruft die äußere Abfrage alle Bestellungen der ausgewählten Kundennummern ab. Und im 1. Beispiel haben wir das gleiche Ergebnis erzielt, indem wir dem beigetreten sind customers und orders Tabellen und dann nur Bestellungen auswählen, zu denen die Kunden aus INDIEN gehören.

Auf diese Weise können wir die Abfrage optimieren, indem wir die Verwendung von Unterabfragen innerhalb der WHERE-Klausel vermeiden und sie leichter lesbar und verständlich machen.

Anwendung der JOIN Der Vorgang von einer größeren Tabelle zu einer kleineren Tabelle ist eine gängige SQL-Optimierungstechnik. Weil das Verknüpfen einer größeren Tabelle mit einer kleineren Tabelle Ihre Abfrage schneller ausführen lässt. Wenn wir a JOIN Operation von einer kleineren Tabelle zu einer größeren Tabelle, muss unsere SQL-Engine in einer größeren Tabelle nach übereinstimmenden Zeilen suchen. Dies ist ressourcenintensiver und zeitaufwändiger. Aber andererseits, wenn die JOIN von einer größeren Tabelle auf eine kleinere Tabelle angewendet wird, muss die SQL-Engine in einer kleineren Tabelle nach übereinstimmenden Zeilen suchen.

Hier ein Beispiel zum besseren Verständnis.

# Order table is larger than the Customer table # Join from a larger table to a smaller table
SELECT * FROM Order JOIN Customer ON Customer.id = Order.id # Join from a smaller table to a larger table
SELECT * FROM Customer JOIN Order ON Customer.id = Order.id

im Gegensatz zu den LIKE Klausel, regexp_like wird auch für die Mustersuche verwendet. Der LIKE -Klausel ist ein grundlegender Mustervergleichsoperator, der nur grundlegende Operationen wie ausführen kann _ or %, die verwendet werden, um ein einzelnes Zeichen oder eine beliebige Anzahl von Zeichen abzugleichen. Der LIKE -Klausel muss die gesamte Datenbank durchsuchen, um das bestimmte Muster zu finden, was für große Tabellen langsam ist.

Auf der anderen Seite, regexp_like ist eine effizientere, optimierte und leistungsfähigere Mustersuchtechnik. Es verwendet komplexere reguläre Ausdrücke, um bestimmte Muster in einer Zeichenfolge zu finden. Diese regulären Ausdrücke sind spezifischer als einfache Wildcard-Übereinstimmungen, da sie es Ihnen ermöglichen, genau nach dem Muster zu suchen, das wir finden. Dadurch wird die zu durchsuchende Datenmenge reduziert und die Abfrage wird schneller ausgeführt.

Bitte beachten Sie, dass regexp_like möglicherweise nicht in allen Datenbankverwaltungssystemen vorhanden. Seine Syntax und Funktionalität kann in anderen Systemen variieren.

Hier ein Beispiel zum besseren Verständnis.

# Query using the LIKE clause
SELECT * FROM mytable WHERE ( name LIKE 'A%' OR name LIKE 'B%' ); # Query using regexp_like clause
SELECT * FROM mytable WHERE regexp_like(name, '^[AB].*');

Die obigen Abfragen werden verwendet, um die Elemente zu finden, deren Name mit A oder B beginnt. Im ersten Beispiel LIKE wird verwendet, um alle Namen zu suchen, die mit A oder B beginnen. A% bedeutet, dass das erste Zeichen ein A ist; danach können beliebig viele Zeichen vorhanden sein. Im zweiten Beispiel regexp_like wird eingesetzt. Innen ^[AB], ^ stellt dar, dass das Symbol am Anfang der Zeichenfolge übereinstimmt, [AB] stellt dar, dass das Anfangszeichen A oder B sein kann, und .* repräsentiert alle Zeichen danach.

Die richtigen regexp_like, kann die Datenbank schnell die Zeilen herausfiltern, die nicht mit dem Muster übereinstimmen, wodurch die Leistung verbessert und die Ressourcennutzung reduziert wird.

In diesem Artikel haben wir verschiedene Methoden und Tipps zur Optimierung der SQL-Abfrage besprochen. Dieser Artikel gibt Ihnen ein klares Verständnis dafür, wie Sie effiziente SQL-Abfragen schreiben und wie wichtig es ist, sie zu optimieren. Es gibt viele weitere Möglichkeiten, die Abfragen zu optimieren, z. B. die Verwendung von Ganzzahlwerten anstelle von Zeichen oder die Verwendung von Union All anstelle von Union, wenn Ihre Tabelle keine Duplikate enthält usw.

Arischer Garg ist ein B.Tech. Student der Elektrotechnik, derzeit im letzten Jahr seines Studiums. Sein Interesse liegt im Bereich Webentwicklung und maschinelles Lernen. Er hat dieses Interesse verfolgt und ist bestrebt, mehr in diese Richtungen zu arbeiten.