10 SQL-Anweisungsoptimierungstechniken zur Verbesserung der MySQL-Abfrageeffizienz

Die Ausführungseffizienz der MySQL-Datenbank hat einen großen Einfluss auf die Ausführungsgeschwindigkeit des Programms. Eine effektive Verarbeitung und Optimierung der Datenbank ist sehr nützlich. Insbesondere wenn große Datenmengen verarbeitet werden müssen.

1. Optimieren Sie Ihren MySQL-Abfrage-Cache

Bei Abfragen auf einem MySQL-Server können Sie einen Hochgeschwindigkeitsabfragecache aktivieren. Eine der effektivsten Möglichkeiten zur Leistungssteigerung besteht darin, die Datenbank-Engine unbemerkt im Hintergrund arbeiten zu lassen. Wenn die gleiche Abfrage mehrmals ausgeführt wird, geht es recht schnell, wenn die Ergebnisse aus dem Cache geholt werden.
Das Hauptproblem besteht jedoch darin, dass es so leicht zu verbergen ist, dass die meisten von uns Programmierern es ignorieren. Bei einigen Verarbeitungsaufgaben können wir tatsächlich verhindern, dass der Abfragecache funktioniert.

// Abfrage-Cache funktioniert NICHT

$r = mysql_query("SELECT Benutzername FROM Benutzer WHERE Anmeldedatum >= CURDATE()"); 
// Abfrage-Cache funktioniert! 
$heute = Datum("Jmd"); 
$r = mysql_query("SELECT Benutzername FROM Benutzer WHERE Anmeldedatum >= '$today'"); 
// Abfrage-Cache funktioniert NICHT 
$r = mysql_query("SELECT Benutzername FROM Benutzer WHERE Anmeldedatum >= CURDATE()"); 
// Abfrage-Cache funktioniert! 
$heute = Datum("Jmd"); 
$r = mysql_query("SELECT Benutzername FROM Benutzer WHERE Anmeldedatum >= '$today'");

2. Machen Sie Ihre SELECT-Abfragen mit EXPLAIN klarer

Die Verwendung des Schlüsselworts EXPLAIN ist ein weiterer MySQL-Optimierungstipp, mit dem Sie sehen können, welche Art von Abfragevorgängen MySQL ausführt. Dies kann Ihnen helfen, Engpässe zu finden und Ihnen zu zeigen, wo es Probleme mit der Abfrage oder der Tabellenstruktur gibt.

Die Ergebnisse der EXPLAIN-Abfrage können Ihnen sagen, auf welche Indizes verwiesen wird, wie die Tabelle gescannt und sortiert wird usw.

Implementieren Sie eine SELECT-Abfrage (vorzugsweise eine komplexere mit Verknüpfungen) und fügen Sie Ihre Schlüsselworterklärung hinzu. Hier können wir phpMyAdmin verwenden, es zeigt Ihnen die Ergebnisse in der Tabelle an. Wenn ich beispielsweise beim Ausführen von Joins vergesse, einem Index eine Spalte hinzuzufügen, kann mir EXPLAIN helfen, das Problem zu finden.

3. Verwenden Sie LIMIT 1, um die einzige Zeile zu erhalten

Manchmal wissen Sie bei der Abfrage einer Tabelle, dass Sie nur eine Zeile betrachten müssen. Möglicherweise suchen Sie nach einem ganz bestimmten Datensatz oder prüfen einfach, ob es beliebig viele Datensätze gibt, die Ihre WHERE-Klausel erfüllen.

In diesem Fall wird Ihre Abfrage durch das Hinzufügen eines LIMIT 1 effizienter. Auf diese Weise stoppt die Datenbank-Engine den Scanvorgang, nachdem nur 1 Ergebnis gefunden wurde, anstatt die gesamte Tabelle oder den gesamten Index zu scannen.

// habe ich Benutzer aus Alabama? 
// was Sie NICHT tun sollten: 
$r = mysql_query("SELECT * FROM Benutzer WHERE Staat = 'Alabama'"); 
wenn (mysql_num_rows($r) > 0) { 
 // ... 
}  
// viel besser: 
$r = mysql_query("SELECT 1 FROM Benutzer WHERE Staat = 'Alabama' LIMIT 1"); 
wenn (mysql_num_rows($r) > 0) { 
 // ... 
}

4. Suchfelder im Index

Indizes sind nicht nur Primärschlüssel oder eindeutige Schlüssel. Wenn Sie eine beliebige Spalte in einer Tabelle durchsuchen möchten, sollten Sie immer auf den Index verweisen.

5. Stellen Sie sicher, dass die verknüpften Indizes vom gleichen Typ sind

Wenn Ihre Anwendung mehrere Join-Abfragen enthält, müssen Sie sicherstellen, dass die Spalten, die Sie verknüpfen, in beiden Tabellen indiziert sind. Dies wirkt sich darauf aus, wie MySQL Inner-Join-Operationen optimiert.

Darüber hinaus müssen die hinzugefügten Spalten vom gleichen Typ sein. Wenn Sie beispielsweise eine DECIMAL-Spalte verknüpfen und gleichzeitig eine int-Spalte aus einer anderen Tabelle verknüpfen, kann MySQL mindestens einen der Indizes nicht verwenden. Sogar die Zeichenkodierung muss mit dem Zeichenfolgentyp übereinstimmen.

// suche nach Unternehmen in meinem Bundesland 
$r = mysql_query("SELECT company_name FROM users 
 LINKS BEITRETEN von Unternehmen EIN (Benutzer.Status = Unternehmen.Status) 
 WO users.id = $user_id"); 
// beide Statusspalten sollten indiziert werden 

// und beide sollten vom gleichen Typ und mit der gleichen Zeichenkodierung sein 

// oder MySQL führt möglicherweise vollständige Tabellenscans durch

6. Verwenden Sie nicht den Befehl BY RAND()

Dies ist eine Falle, in die viele Programmieranfänger tappen. Möglicherweise haben Sie eine schreckliche Ruhe verursacht, ohne es zu merken. Diese Falle wird erstellt, wenn Sie den Befehl BY RAND() verwenden.

Wenn Sie Ihre Ergebnisse wirklich zufällig sortieren müssen, gibt es dafür viel bessere Möglichkeiten. Dies erfordert zwar mehr Code, vermeidet jedoch Leistungsengpässe. Das Problem besteht darin, dass MySQL möglicherweise den Befehl BY RAND() für jede einzelne Zeile in der Tabelle ausführt (was Prozessorleistung verbraucht) und Ihnen dann nur eine Zeile zurückgibt.

// was Sie NICHT tun sollten: 
$r = mysql_query("SELECT Benutzername FROM Benutzer ORDER BY RAND() LIMIT 1"); 
// viel besser: 
$r = mysql_query("SELECT count(*) FROM Benutzer"); 
$d = mysql_fetch_row($r); 
$rand = mt_rand(0,$d[0] - 1); 
$r = mysql_query("SELECT Benutzername FROM Benutzer LIMIT $rand, 1");

7. Vermeiden Sie SELECT *-Befehle

Je mehr Daten Sie aus der Tabelle lesen, desto langsamer wird die Abfrage. Dadurch erhöht sich die für den Festplattenbetrieb erforderliche Zeit, insbesondere wenn der Datenbankserver und der Webserver getrennt sind. Es kommt zu sehr langen Netzwerkverzögerungen, einfach weil Daten unnötigerweise zwischen Servern übertragen werden. Es ist eine gute Angewohnheit, immer die Spalten anzugeben, die Sie benötigen.

// nicht bevorzugt 
$r = mysql_query("SELECT * FROM Benutzer WHERE Benutzer-ID = 1"); 
$d = mysql_fetch_assoc($r); 
echo "Willkommen {$d['username']}"; 
// besser: 
$r = mysql_query("SELECT Benutzername FROM Benutzer WHERE Benutzer-ID = 1"); 
$d = mysql_fetch_assoc($r); 
echo "Willkommen {$d['username']}"; 
// die Unterschiede sind bei größeren Ergebnismengen signifikanter

8. Holen Sie sich Vorschläge von PROCEDURE ANALYSE()

Mit PROCEDURE ANALYSE() kann MySQL die Spaltenstruktur und die tatsächlichen Daten in der Tabelle analysieren, um Ihnen einige Vorschläge zu unterbreiten. Wenn Ihre Tabelle bereits über aktuelle Daten verfügt, können diese Ihnen dabei helfen, wichtige Entscheidungen zu treffen.

9. Vorbereitete Aussagen

Vorbereitete Anweisungen können Ihnen hinsichtlich Leistungsoptimierung und Sicherheit helfen.

Vorbereitete Anweisungen filtern standardmäßig bereits gebundene Variablen und bieten Anwendungen so wirksamen Schutz vor SQL-Injection-Angriffen. Natürlich können Sie auch manuell filtern, aber aufgrund der Vergesslichkeit der meisten Programmierer ist es schwierig, den gewünschten Effekt zu erzielen.

// eine vorbereitete Anweisung erstellen 
if ($stmt = $mysqli->prepare("SELECT Benutzername FROM Benutzer WHERE Status=?")) { 
 // Parameter binden 
 $stmt->bind_param("s", $zustand); 
 // ausführen 
 $stmt->execute(); 
 // Ergebnisvariablen binden 
 $stmt->bind_result($Benutzername);  
 // Wert holen 
 $stmt->fetch(); 
 printf("%s ist von %s\n", $Benutzername, $Status);  
 $stmt->schließen(); 
}

10. Speichern von IP-Adressen als vorzeichenlose Ganzzahlen

Viele Programmierer erstellen ein VARCHAR(15), ohne zu wissen, dass sie die IP-Adresse als Ganzzahl speichern können. Wenn Sie einen INT-Typ haben, belegen Sie nur 4 Byte Speicherplatz, es ist ein Feld mit fester Größe.
Sie müssen sicherstellen, dass die Spalte, mit der Sie arbeiten, vom Typ UNSIGNED INT ist, da IP-Adressen eine vorzeichenlose 32-Bit-Ganzzahl verwenden.
1. $r = "UPDATE Benutzer SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";
Es gibt viele MySQL-Abfrageanweisungen. Heute werden wir über diese 10 sprechen