Schlüsselpunkte

• Verwenden Sie den Befehl von MySQL EXPLAIN, um Abfrageausführungspl?ne zu analysieren und zu optimieren, um effizientere Datenbankvorg?nge zu gew?hrleisten, indem Schlüsselinformationen wie Verbindungstyp und Index verwendet werden.
• Umfrageanalyse implementieren, um die tats?chliche Laufzeit einer Abfrage zu messen, wodurch gezielte Optimierungen durchgeführt werden, um die Ausführungszeit zu verkürzen und die Gesamtleistung zu verbessern.
• Fügen Sie geeignete Indizes hinzu, basierend auf dem Feedback aus dem Befehl EXPLAIN, wobei der Schwerpunkt auf den in der WHERE -Anklausel verwendeten Spalten liegt, um das Abrufen von Daten zu beschleunigen und die Abfrageleistung zu verbessern.
• Für Spalten, die an Suchvorg?ngen beteiligt sind, sollten Sie die Volltextindexierung verwenden, um die Leistung zu optimieren, insbesondere wenn der Operator LIKE in Abfragen verwendet wird.
• Beachten Sie die Verwendung von ORDER BY in Kombination mit LIMIT, da sie die Leistungsvorteile von begrenzenden Ergebnissen ausgleichen k?nnen, insbesondere wenn der Index nicht effektiv verwendet wird.

Die

-Datenbankoptimierung ist normalerweise der Hauptaugenmerk auf die Verbesserung der Anwendungsleistung und der h?ufigste Engpass. Wie misst und versteht man, was verbessert werden muss?

Ein einfaches und effektives Werkzeug ist die Abfrageanalyse. Die Aktivierung der Analyse erm?glicht genauere Sch?tzungen der Laufzeit einer Abfrage. Dies ist ein zweistufiger Prozess: Erstens an die Analyse; show profiles

Angenommen, der folgende Einfügungsvorgang ist in der Datenbank vorhanden (und unter der Annahme, dass Benutzer 1 und Galerie 1 erstellt wurden):

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

Eine kleine Datenmenge verursacht keine Probleme, aber wir k?nnen sie für eine einfache Analyse verwenden. Betrachten Sie die folgende Anfrage:

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

Wenn es viele Fotoeintr?ge gibt, kann diese Abfrage in Zukunft zu einem Problem werden.

Um die genaue Laufzeit dieser Abfrage zu erhalten, k?nnen Sie den folgenden SQL verwenden:

set profiling = 1;
SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';
show profiles;

Die Ergebnisse sind wie folgt:

Der Befehl

show profiles; zeigt nicht nur die Zeit der ursprünglichen Abfrage, sondern auch die Zeit aller anderen Abfragen an, so dass die Abfrage genau analysiert werden kann.

Wie kann man die Anfrage verbessern?

Sie k?nnen sich auf SQL -Wissen verlassen, um sich zu verbessern oder auf den Befehl von MySQL zu stützen und die Abfrageleistung auf der Grundlage der tats?chlichen Informationen zu verbessern. EXPLAIN

wird verwendet, um den Abfrageausführungsplan zu erhalten, dh wie MySQL die Abfrage ausführt. Es eignet sich für EXPLAIN, SELECT, DELETE, INSERT und REPLACE Anweisungen und zeigt Informationen über den Anweisungsausführungsplan durch den Optimierer an. Die offizielle Dokumentation beschreibt gut, wie UPDATE wie EXPLAIN uns helfen kann:

Mit EXPLAIN k?nnen Sie sehen, welche Tabellen Sie Indizes hinzufügen sollten, damit Anweisungen schneller ausführen k?nnen, indem Sie Indizes verwenden, um Zeilen zu finden. Sie k?nnen auch EXPLAIN verwenden, um zu überprüfen, ob der Optimierer in der besten Reihenfolge den Tabellen verbindet.

Um ein Beispiel zu geben, um die Verwendung von EXPLAIN zu veranschaulichen, verwenden wir die Abfrage, um Benutzer -E -Mails in UserManager.php:

zu finden

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

Um den Befehl EXPLAIN zu verwenden, fügen Sie ihn einfach vor der Abfrage SELECT hinzu:

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

Das Ergebnis ist wie folgt (scrollen Sie rechts, um alles zu sehen):

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	const	UNIQ_1483A5E9E7927C74	UNIQ_1483A5E9E7927C74	182	const	1	100.00	NULL

Diese Ergebnisse sind zu Beginn nicht leicht zu verstehen. Schauen wir uns jeden genauer an:

• id: Dies ist die sequentielle Kennung für jede Abfrage in SELECT.
• select_type: SELECT Abfrageart. Dieses Feld kann mehrere verschiedene Werte annehmen, daher werden wir uns auf die wichtigsten konzentrieren:
  • SIMPLE: einfache Abfrage ohne Unterabfragen oder Gewerkschaften
  • PRIMARY: select befindet sich in der ?u?ersten Abfrage in der Verbindung
  • DERIVED: select ist Teil der Neutronenabfrage from
  • : die erste in der Unterabfrage SUBQUERY select
  • : UNION ist die zweite oder nachfolgende Erkl?rung der Union. Die vollst?ndige Liste der Feldwerte select finden Sie hier. select_type
• : Die durch Zeilen verwiesene Tabelle. table
• : Dieses Feld repr?sentiert den Typ der Tabelle, der von MySQL Connection verwendet wird. Dies ist wahrscheinlich das wichtigste Feld in der Ausgabe type. Es kann den fehlenden Index angeben oder wie die Abfrage überschrieben wird. M?gliche Werte für dieses Feld sind wie folgt (sortiert von besten zum schlimmsten Typ): EXPLAIN
  • : Die Tabelle hat keine Zeilen oder eine Zeile. system
  • : In der Tabelle befindet sich nur eine Zeile, die der Zeile entspricht, und die Zeile wurde indiziert. Dies ist der schnellste Verbindungstyp. const
  • : Alle Teile des Index werden vom Join verwendet und der Index ist eq_ref oder PRIMARY_KEY. UNIQUE NOT NULL
  • : Für jede Zeilenkombination aus der vorherigen Tabelle werden alle übereinstimmenden Zeilen der Indexspalte gelesen. Diese Art von Join tritt normalerweise in indizierten Spalten auf, die mit ref oder Operatoren verglichen werden. =
  • : Tragen Sie den vollst?ndigen Textindex der Tabelle bei. fulltext
  • : Das Gleiche wie ref_or_null, enth?lt aber auch die Zeilen aus dem Wert der Spalte ref. NULL
  • : Verbindungen verwenden Indexlisten, um Ergebnissets zu generieren. Die index_merge -Spalte von EXPLAIN enth?lt die verwendeten Schlüssel. KEY
  • : unique_subquery Unterabfrage gibt nur ein Ergebnis aus der Tabelle zurück und verwendet den Prim?rschlüssel. IN
  • : Verwenden Sie Indizes, um übereinstimmende Zeilen innerhalb eines bestimmten Bereichs zu finden. range
  • : Scannen Sie den gesamten Indexbaum, um passende Zeilen zu finden. index
  • : Scannen Sie die gesamte Tabelle, um die passenden Zeilen für den Join zu finden. Dies ist die schlechteste Art von Join, die normalerweise einen fehlenden geeigneten Index in der Tabelle angibt. ALL
• : Zeigt die Schlüssel an, die MySQL verwenden kann, um Zeilen aus einer Tabelle zu finden. Diese Schlüssel k?nnen in der Praxis verwendet werden oder nicht. possible_keys
• : Zeigt den Index an, den MySQL tats?chlich verwendet. MySQL findet immer die besten Schlüssel, die für Abfragen verwendet werden k?nnen. Beim Beitritt zu mehreren Tabellen finden Sie m?glicherweise einige Schlüssel, die nicht in keys aufgeführt sind, aber besser sind. possible_keys
• : Zeigt die L?nge des Index an, der vom Abfrageoptimierer verwendet werden soll. key_len
• : Zeigt eine Spalte oder Konstante an, die sich mit dem benannten Index in der Spalte ref vergleicht.
• rows: Listet die Anzahl der auf die Generierung von Ausgaben überprüften Datens?tze auf. Dies ist ein sehr wichtiger Indikator.
• Extra: Enth?lt andere Informationen. Das in dieser Spalte ?quivalente Using filesort oder Using temporary ?quivalent kann eine fragliche Abfrage anzeigen.

EXPLAIN Die vollst?ndige Dokumentation des Ausgabeformates finden Sie auf der offiziellen MySQL -Seite.

Zurück zu unserer einfachen Abfrage: Es ist ein SIMPLE Typ select mit einer Verbindung von const Typ. Dies ist der beste Abfragefall, den wir haben. Aber was passiert, wenn wir gr??ere und komplexere Fragen brauchen?

Zurück zu unserem Anwendungsmodus m?chten wir alle Galeriebilder erhalten. M?glicherweise m?chten wir auch nur Fotos mit dem Wort "Katze" in die Beschreibung aufnehmen. Dies ist definitiv eine Situation, die wir in den Projektanforderungen finden k?nnen. Schauen wir uns die Frage an:

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

In dieser komplexeren Situation sollten wir weitere Informationen in EXPLAIN erhalten, um zu analysieren:

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

Dies liefert die folgenden Ergebnisse (scrollen Sie Recht, alle Zellen zu sehen):

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	index	PRIMARY,UNIQ_1483A5E9BF396750	UNIQ_1483A5E9BF396750	108	NULL	1	100.00	Using index
1	SIMPLE	gal	NULL	ref	PRIMARY,UNIQ_F70E6EB7BF396750,IDX_F70E6EB7A76ED395	UNIQ_1483A5E9BF396750	108	homestead.users.id	1	100.00	NULL
1	SIMPLE	img	NULL	ref	IDX_E01FBE6A4E7AF8F	IDX_E01FBE6A4E7AF8F	109	homestead.gal.id	1	25.00	Using where

Schauen wir uns genauer an und sehen, was wir in der Abfrage verbessern k?nnen.

Wie bereits erw?hnt, sind die Hauptspalten, die zuerst angezeigt werden sollten, die Spalten type Spalten und rows Spalten. Das Ziel sollte darin bestehen, bessere Werte in der Spalte type zu erhalten und die Werte der Spalte rows zu minimieren.

Das Ergebnis der ersten Abfrage ist index, was überhaupt kein gutes Ergebnis ist. Dies bedeutet, dass wir es m?glicherweise verbessern k?nnen.

Zeigen Sie unsere Abfrage an, es gibt zwei M?glichkeiten, sie zu l?sen. Zun?chst wird die Users -Tabelle nicht verwendet. Entweder erweitern wir die Abfrage, um sicherzustellen, dass wir den Benutzer abzielen, oder wir sollten den Benutzerabschnitt der Abfrage vollst?ndig l?schen. Es erh?ht nur die Komplexit?t und Zeit unserer Gesamtleistung.

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

Jetzt erhalten wir genau das gleiche Ergebnis. Mal sehen EXPLAIN:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gal	NULL	ALL	PRIMARY,UNIQ_1483A5E9BF396750	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	img	NULL	ref	IDX_E01FBE6A4E7AF8F	IDX_E01FBE6A4E7AF8F	109	homestead.gal.id	1	25.00	Using where

Was uns übrig bleibt, ist der Typ ALL. W?hrend ALL wahrscheinlich die schlimmste Art von Verbindung ist, gibt es einige F?lle, in denen es die einzige Option ist. Nach unseren Anforderungen m?chten wir alle Galeriebilder, daher müssen wir die gesamte galleries Tabelle durchsuchen. Wenn wir alle Informationen in der Tabelle ben?tigen, sind die Indizes gro?artig, wenn wir versuchen, bestimmte Informationen in der Tabelle zu finden, aber sie helfen uns nicht. Wenn wir auf diese Situation sto?en, müssen wir auf andere Methoden wie das Caching zurückgreifen.

Da wir an LIKE arbeiten, ist die letzte Verbesserung, die wir vornehmen k?nnen, unserem Feld description einen Volltextindex hinzuzufügen. Auf diese Weise k?nnen wir LIKE in match() ?ndern und die Leistung verbessern. Weitere Informationen zur Volltextindizierung finden Sie hier.

Wir müssen uns auch zwei sehr interessante Situationen ansehen: die neuesten und verwandten Funktionen in der Anwendung. Diese gelten für Galerie und beinhalten einige extreme Situationen, auf die wir achten sollten:

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

Die oben genannten sind verwandte Galerien.

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

Das obige ist die neueste Galerie.

Auf den ersten Blick sollten diese Abfragen sehr schnell sein, da sie LIMIT verwenden. Dies ist in den meisten Abfragen der Fall, die LIMIT verwenden. Leider verwenden diese Abfragen für uns und unsere Anwendungen auch ORDER BY. Da wir alle Ergebnisse sortieren müssen, bevor wir die Abfrage einschr?nken, verlieren wir den Vorteil der Verwendung von LIMIT.

Da wir wissen, dass ORDER BY schwierig sein kann, wenden wir unsere zuverl?ssige EXPLAIN an.

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gal	NULL	ALL	IDX_F70E6EB7A76ED395	NULL	NULL	NULL	1	100.00	Using where; Using filesort
1	SIMPLE	u	NULL	eq_ref	PRIMARY,UNIQ_1483A5E9BF396750	PRIMARY	108	homestead.gal.id	1	100.00	NULL

und,

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gal	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using filesort

Wir k?nnen sehen, dass wir für unsere beiden Fragen den schlimmsten Verbindungstyp haben: ALL.

In der Vergangenheit war die Implementierung von MySQL, insbesondere bei Verwendung von ORDER BY, oft die Quelle von MySQL -Leistungsproblemen. Diese Kombination wird auch in den meisten interaktiven Anwendungen mit gro?en Datens?tzen verwendet. Funktionen wie neue registrierte Benutzer und beliebte Tags verwenden diese Kombination h?ufig. LIMIT

Da dies ein h?ufiges Problem ist, sollten wir auch einige h?ufige L?sungen anwenden, um Leistungsprobleme zu l?sen.

Stellen Sie sicher, dass wir Indizes verwenden. In unserem Fall ist
• ein guter Kandidat, weil es das Feld ist, das wir sortieren. Auf diese Weise k?nnen wir created_at und ORDER BY ohne Scannen und Sortieren des vollst?ndigen Ergebnissatzes tun. LIMIT
Sortieren nach Spalten in der führenden Tabelle. Wenn
• nach Feldern sortiert wird, die nicht die erste Tabelle in der Join -Reihenfolge sind, kann der Index nicht verwendet werden. ORDER BY
sortieren Sie nicht nach Ausdruck. Ausdrücke und Funktionen erlauben nicht die Verwendung von
• Indizes. ORDER BY
achten Sie auf den gro?en Wert von
• . Large LIMIT Werte erzwingen LIMIT, um mehr Zeilen zu sortieren. Dies wirkt sich auf die Leistung aus. ORDER BY

Wenn wir sowohl

als auch LIMIT verwenden, sollten wir einige der Ma?nahmen ergreifen, um die Leistungsprobleme zu minimieren. ORDER BY

Schlussfolgerung

Wie wir gesehen haben, ist

sehr nützlich, um Probleme in Fragen so früh wie m?glich zu identifizieren. Es gibt viele Probleme, die nur dann bemerkt werden, wenn sich unsere Anwendung in der Produktion befindet, und es gibt viele Daten oder viele Besucher, die auf die Datenbank zugreifen k?nnen. Wenn Sie EXPLAIN verwenden k?nnen, um diese Probleme so früh wie m?glich zu erkennen, ist die M?glichkeit von Leistungsproblemen in Zukunft viel kleiner. EXPLAIN

Unsere Anwendung enth?lt alle ben?tigten Indizes und ist schnell, aber wir wissen jetzt, dass wir uns jederzeit an

und Indizes wenden k?nnen. EXPLAIN

FAQs über die MySQL -Leistungsindexierung (FAQ)

Was ist die Bedeutung der MySQL -Leistungsindexierung?

MySQL Performance Indexierung ist entscheidend, um die Datenbankleistung zu optimieren. Sie beschleunigen die Datenabnahmeoperationen erheblich, indem sie basierend auf den Werten in den indizierten Spalten schnell auf Zeilen in der Datentabelle zugreifen. Ohne einen Index muss MySQL jede Zeile in der Tabelle durchqueren, um die relevanten Zeilen zu finden, die sehr zeitaufw?ndig sein k?nnen, insbesondere für gro?e Datenbanken.

Wie hilft der Befehl erkl?ren, die MySQL -Leistung zu verbessern?

Der Befehl

in MySQL ist ein leistungsstarkes Tool, das Informationen darüber liefert, wie MySQL Abfragen ausführt. Es zeigt die Reihenfolge der Lesetabellen, die Art der ausgeführten Lesevorg?nge, den ausgew?hlten Index und die gesch?tzte Anzahl der zu überprüfen. Diese Informationen k?nnen Entwicklern helfen, Abfragen zu optimieren und die Datenbankleistung zu verbessern. EXPLAIN

Warum verwendet MySQL keine m?glichen Schlüssel?

Mysql verwendet aus mehreren Gründen keine m?glichen Schlüssel. Ein Grund kann sein, dass die Optimierersch?tzung, dass die Verwendung von Indizes den gr??ten Teil der Tabelle scannen muss, und entscheidet, dass das Scannen von Tabellen schneller ist. Ein weiterer Grund k?nnte sein, dass die Spalten in der WHERE -Klausel nicht mit den Spalten im Index übereinstimmen.

Wie optimieren Sie meine MySQL -Abfrage?

Es gibt verschiedene M?glichkeiten, MySQL -Abfragen zu optimieren. Eine M?glichkeit besteht darin, Indizes effektiv zu verwenden. Indizes k?nnen das Abrufen von Daten erheblich beschleunigen. Sie verlangsamen jedoch Datenmodifikationsvorg?nge wie INSERT, UPDATE und DELETE. Daher ist es sehr wichtig, einen Gleichgewichtspunkt zu finden. Eine andere M?glichkeit besteht darin, den Befehl EXPLAIN zu verwenden, um zu verstehen, wie MySQL Abfragen ausführt und potenzielle Engp?sse findet.

Was ist der Unterschied zwischen Prim?rschlüssel und Indizes in MySQL?

Der Hauptschlüssel in MySQL ist ein Index. Der Prim?rschlüssel ist ein eindeutiger Kennung für die Zeile in der Tabelle. Es erzwingt die Einzigartigkeit einer Spalte oder eine Spaltenkombination und stellt sicher, dass die Spalten- oder Spaltenkombination keine NULL -Werte enth?lt. Andererseits ist ein Index eine Datenstruktur, die die Geschwindigkeit der Datenabrufvorg?nge erh?hen kann. Es kann auf jede Spalte oder Kombination von Spalten angewendet werden.

Wie erstelle ich einen Index in MySQL?

Sie k?nnen mit der Anweisung CREATE INDEX verwenden, um einen Index in MySQL zu erstellen. Die Syntax ist wie folgt: CREATE INDEX index_name ON table_name (column1, column2, …);. Dadurch wird in der angegebenen Spalte der angegebenen Tabelle einen Index erstellt.

Was ist der zusammengesetzte Index in MySQL?

zusammengesetzter Index, auch als Multi-S?ulen-Index bezeichnet, ist ein Index, der mehrere Spalten enth?lt. In MySQL kann ein zusammengesetzter Index bis zu 16 Spalten enthalten, aber die Gesamtgr??e der indizierten Spalten darf 767 Bytes nicht überschreiten.

Wie l?sche ich den Index in MySQL?

Sie k?nnen die Anweisung DROP INDEX verwenden, um den Index in MySQL zu l?schen. Die Syntax ist wie folgt: DROP INDEX index_name ON table_name;. Dadurch wird der angegebene Index aus der angegebenen Tabelle gel?scht.

Was ist der Unterschied zwischen Clustered Index und nicht klusterer Index in MySQL?

Der clusterierte Index

bestimmt die physikalische Reihenfolge der Daten in der Tabelle. Jede Tabelle kann nur einen Cluster -Index haben. Andererseits ?ndern nicht klusterische Indizes die physische Reihenfolge der Daten in der Tabelle nicht. Stattdessen wird eine separate Datenstruktur (Index) beibehalten, die auf die Datenzeile hinweist und ein schnelleres Datenabruf erm?glicht.

Wie w?hle ich den Index aus, der in MySQL verwendet werden soll?

MySQL verwendet einen kostenbasierten Optimierer, um den zu verwendenden Index auszuw?hlen. Der Optimierer sch?tzt die Kosten für die Ausführung von Pl?nen für verschiedene Abfragen und w?hlt den niedrigsten Preisplan aus. Die Kosten werden auf der Grundlage von Faktoren wie der Anzahl der zu lesen, die Anzahl der Festplattensuche, CPU -Kosten und Speicherverbrauch gesch?tzt.

Das obige ist der detaillierte Inhalt vonMySQL Performance erh?hen sich mit Indizes und erkl?ren Sie. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!