Wie optimiert man eine MySQL-Abfrage? Einführung in Methoden zur MySQL-Abfrageoptimierung

Der Inhalt dieses Artikels befasst sich mit dem einfachen Implementierungscode des Prozesspools in Python. Ich hoffe, dass er für Freunde hilfreich ist.

1. Fügen Sie Indizes für alle Spalten hinzu, die für where, order by und group by

verwendet werden. Der

-Index stellt nicht nur sicher, dass ein Datensatz eindeutig gekennzeichnet ist, sondern kann auch MySQL aktivieren Server zum Aktualisieren. Erhalten Sie schnell Ergebnisse aus der Datenbank. Auch beim Sortieren spielen Indizes eine sehr wichtige Rolle.

Der MySQL-Index belegt möglicherweise zusätzlichen Speicherplatz und verringert die Leistung beim Einfügen, Löschen und Aktualisieren bis zu einem gewissen Grad. Wenn Ihre Tabelle jedoch mehr als 10 Datenzeilen enthält, kann die Indizierung die Ausführungszeit der Suche erheblich verkürzen.

Es wird dringend empfohlen, zum Testen von MySql-Abfragen „Worst-Case-Datenbeispiele“ zu verwenden, um ein klareres Verständnis dafür zu erhalten, wie sich die Abfrage in der Produktion verhält.

Angenommen, Sie führen die folgende Abfrageanweisung für eine Datenbanktabelle mit mehr als 500 Zeilen aus:

mysql>select customer_id, customer_name from customers where customer_id='345546'

Die obige Abfrage zwingt den MySQL-Server, einen vollständigen Tabellenscan durchzuführen, um die vorhandenen Daten zu erhalten gesucht.

-Modell bietet MySQL eine spezielle Explain-Anweisung, um die Leistung Ihrer Abfrageanweisung zu analysieren. Wenn Sie nach dem Schlüsselwort eine Abfrageanweisung hinzufügen, zeigt MySql alle Informationen an, die der Optimierer über die Anweisung hat.

Wenn wir die EXPLAIN-Anweisung verwenden, um die obige Abfrage zu analysieren, erhalten wir die folgenden Analyseergebnisse:

mysql> explain select customer_id, customer_name from customers where customer_id='140385';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

Wie Sie sehen, zeigt der Optimierer sehr wichtige Informationen an, die uns helfen können -Datenbanktabellen optimieren. Zunächst führt MySql einen vollständigen Tabellenscan durch, da die Schlüsselspalte Null ist. Zweitens hat der MySql-Server klargestellt, dass er 500 Datenzeilen scannen wird, um diese Abfrage abzuschließen.

Um die obige Abfrage zu optimieren, müssen wir nur einen Index m zur Spalte customer_id hinzufügen:

mysql> Create index customer_id ON customers (customer_Id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Wenn wir die EXPLAIN-Anweisung erneut ausführen, erhalten wir die folgenden Ergebnisse :

mysql> Explain select customer_id, customer_name from customers where customer_id='140385';
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | ref  | customer_id   | customer_id | 13      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

Aus der obigen Ausgabe geht hervor, dass der MySQL-Server den Index customer_id zum Abfragen der Tabelle verwenden wird. Sie können sehen, dass die Anzahl der zu scannenden Zeilen 1 beträgt. Obwohl ich diese Abfrage nur für eine Tabelle mit 500 Zeilen ausführe, wird der Index beim Abrufen eines größeren Datensatzes noch optimierter.

2. Verwenden Sie Union, um die Like-Anweisung zu optimieren.

Manchmal müssen Sie zum Vergleich den Operator „or“ in der Abfrage verwenden. Wenn das Schlüsselwort or in der where-Klausel zu häufig verwendet wird, kann es dazu führen, dass der MySQL-Optimierer fälschlicherweise einen vollständigen Tabellenscan auswählt, um Datensätze abzurufen. Die Union-Klausel kann die Ausführung von Abfragen beschleunigen, insbesondere wenn eine der Abfragen über einen optimierten Index verfügt und die andere Abfrage ebenfalls über einen optimierten Index verfügt.

Wenn beispielsweise Indizes für first_name bzw. last_name vorhanden sind, führen Sie die folgende Abfrageanweisung aus:

mysql> select * from students where first_name like 'Ade%' or last_name like 'Ade%'

Die obige und die folgende Abfrage verwenden Union, um die beiden Abfrageanweisungen zusammenzuführen Um sie voll auszunutzen, ist die Geschwindigkeit im Vergleich zur Abfrage viel langsamer.

mysql> select * from students where first_name like 'Ade%' union all select * from students where last_name like 'Ade%'

3. Vermeiden Sie die Verwendung von Ausdrücken mit führenden Platzhaltern.

Mysql kann den Index nicht verwenden, wenn die Abfrage führende Platzhalter enthält. Am Beispiel der Studententabelle oben führt die folgende Abfrage dazu, dass MySQL einen vollständigen Tabellenscan durchführt und rechtzeitig einen Index zum Feld first_name hinzufügt.

mysql> select * from students where first_name like '%Ade'

Mithilfe der EXPLAIN-Analyse werden die folgenden Ergebnisse erhalten:

mysql> explain select * from students where first_name like  '%Ade'  ;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

Wie oben gezeigt, scannt MySQL alle 500 Datenzeilen, was die Abfrage extrem langsam macht.

4. Nutzen Sie die Volltextsuche von MySQL voll aus.

Wenn Sie beim Abfragen von Daten Platzhalterzeichen verwenden müssen, aber die Leistung der Datenbank nicht beeinträchtigen möchten, sollten Sie dies in Betracht ziehen Verwenden der Volltextsuche (FTS) von MySQL, da diese viel schneller ist als die Wildcard-Suche. Darüber hinaus ist FTS in der Lage, qualitativ hochwertigere relevante Ergebnisse zu liefern.

Die Anweisung zum Hinzufügen eines Volltextsuchindex zur Studentenbeispieltabelle lautet wie folgt:

mysql> alter table students add fulltext(first_name, last_name)';
mysql> select * from students where match(first_name, last_name) against ('Ade');

Im obigen Beispiel haben wir die Spalte (Vorname) angegeben, für die wir einen Abgleich durchführen möchten Suchbegriff Ade, Nachname). Wenn der Abfrageoptimierer die obige Anweisung ausführt, erhalten Sie die folgenden Ergebnisse:

mysql> explain Select * from students where match(first_name, last_name) AGAINST ('Ade');
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table    | partitions | type     | possible_keys | key        | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | students | NULL       | fulltext | first_name    | first_name | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+

5. Optimieren Sie die Datenbankarchitektur

Normalisierung

Normalisieren Sie zunächst alle Datenbanktabellen Wenn möglich, wird es einige Verluste geben. Wenn Sie beispielsweise zwei Tabellen zum Aufzeichnen von Kunden- und Bestelldaten erstellen müssen, sollten Sie den Kunden anhand der Kunden-ID in der Bestelltabelle referenzieren und nicht umgekehrt. Das folgende Diagramm zeigt die Datenbankarchitektur, die ohne Datenredundanz konzipiert ist.

Andernfalls verwenden Sie dieselbe Datentypklasse, um ähnliche Werte zu speichern.

Verwenden Sie die besten Datentypen

MySQL unterstützt verschiedene Datentypen, einschließlich Ganzzahl, Float, Double, Datum, Datum/Uhrzeit, Varchar, Text usw. Beim Entwerfen von Datenbanktabellen sollten Sie versuchen, den kürzesten Datentyp zu verwenden, der die Merkmale erfüllen kann.

Wenn Sie beispielsweise eine Systembenutzertabelle entwerfen und die Anzahl der Benutzer 100 nicht überschreitet, sollten Sie den Typ „TINYINT“ für user_ud verwenden. Der Wertebereich dieses Typs liegt zwischen -128 und 128 . Wenn in einem Feld Werte vom Typ „Datum“ gespeichert werden müssen, ist es besser, den Typ „Datum/Uhrzeit“ zu verwenden, da bei der Abfrage keine komplexe Typkonvertierung erforderlich ist.

Wenn die Werte ausschließlich numerische Typen sind, verwenden Sie Integer. Werte vom Typ Integer sind bei der Durchführung von Berechnungen schneller als Werte vom Typ Text.

NULL vermeiden

NULL bedeutet, dass die Spalte keinen Wert hat. Sie sollten diese Art von Werten nach Möglichkeit vermeiden, da sie die Datenbankergebnisse beeinträchtigen können. Sie müssen beispielsweise die Summe der Beträge aller Bestellungen in der Datenbank abrufen, aber der Betrag in einem Bestelldatensatz ist null. Wenn Sie den Nullzeiger nicht beachten, führt dies wahrscheinlich zu Ausnahmen bei der Berechnung Ergebnisse. In einigen Fällen müssen Sie möglicherweise einen Standardwert für eine Spalte definieren.

Das obige ist der detaillierte Inhalt vonWie optimiert man eine MySQL-Abfrage? Einführung in Methoden zur MySQL-Abfrageoptimierung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!