MySQL Performance erhöhen sich mit Indizes und erklären Sie

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):

<code class="language-sql">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');</code>

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

<code class="language-sql">SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';</code>

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:

<code class="language-sql">set profiling = 1;
SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';
show profiles;</code>

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

<code class="language-sql">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');</code>

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

<code class="language-sql">SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';</code>

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:

<code class="language-sql">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');</code>

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

<code class="language-sql">SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';</code>

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.

<code class="language-sql">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');</code>

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:

<code class="language-sql">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');</code>

Die oben genannten sind verwandte Galerien.

<code class="language-sql">SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';</code>

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!