Wie lerne ich die MySQL-Indexprinzipien? Meine eigene Zusammenfassung meiner Indexierungserfahrung

Was ist ein Index? Baidu Encyclopedia beschreibt es so: Ein Index ist ein verstreutes Datenergebnis, das erstellt wird, um das Abrufen von Datenzeilen in einer Tabelle zu beschleunigen. Es besteht aus anderen Indexseiten als den einzelnen Zeilen Die Indexseite enthält logische Zeiger, um das Abrufen physischer Daten zu beschleunigen. In diesem Artikel wird ausführlich erläutert, wie Sie die MySQL-Indexprinzipien erlernen.

Zusammenfassung: Lassen Sie uns über MySQL-Indizes sprechen. Was ist ein Index? Baidu Encyclopedia beschreibt es so: Ein Index ist ein verstreutes Datenergebnis, das erstellt wird, um das Abrufen von Datenzeilen in einer Tabelle zu beschleunigen. Es besteht aus anderen Indexseiten als den einzelnen Zeilen Die Indexseite enthält logische Hinweise, um das Abrufen physischer Daten zu beschleunigen. Tatsächlich ist sich jeder über das Konzept der Indizierung im Klaren, und wir wissen auch, dass die Indizierung die Abfrageeffizienz verbessern kann. Die meisten Kinderschuhe haben jedoch die folgenden häufigen Fragen Wie man einen Index erstellt und auf welchen Feldern er aufgebaut werden soll: Beim Erstellen einer neuen Tabelle ist es nicht erforderlich, einen Index zu erstellen. Alle Felder nach der Where-Bedingung müssen nicht indiziert werden ein Index, nur gemeinsame Abfragen erfordern Indizes. Die Reihenfolge der Felder nach der Where-Bedingung ist weniger differenziert. Neue Indizes werden auch für Felder wie Status, Geschlecht und andere Felder erstellt.

Lassen Sie uns über MySQL-Indizes sprechen.

Was ist ein Index?

Baidu Encyclopedia beschreibt es so:

Der Index ist ein verstreutes Datenergebnis, das erstellt wurde, um das Abrufen von Datenzeilen in der Tabelle zu beschleunigen. Es wurde für die Tabelle erstellt Es besteht aus anderen Indexseiten als Datenseiten. Die Zeilen auf jeder Indexseite enthalten logische Zeiger, um das Abrufen physischer Daten zu beschleunigen.

Tatsächlich ist sich jeder über das Konzept des Index sehr im Klaren , und Wir wissen, dass Indizes die Abfrageeffizienz verbessern können, aber die meisten Kinderschuhe haben die folgenden häufigen Missverständnisse darüber, wie Indizes erstellt werden und auf welchen Feldern aufgebaut werden soll:

Es ist nicht erforderlich, einen Index zu erstellen, wenn ein neuer erstellt wird Tabelle und Indizes werden später hinzugefügt

Die Felder nach der Where-Bedingung werden alle indiziert

Einfaches SQL erfordert keinen Index, nur die gemeinsame Abfrage erfordert einen Index

Die Reihenfolge des gemeinsamen Index ist die Reihenfolge der Felder nach der Where-Bedingung

Neue Indizes werden auch für Felder mit geringer Unterscheidung erstellt, wie z. B. Status, Geschlecht und andere Felder .

Indexunterscheidung

Bevor wir über die oben genannten Themen sprechen, werfen wir einen Blick auf ein anderes Konzept, nämlich Diskriminierung.

Unterscheidung: bezieht sich auf das nicht duplizierte Verhältnis von Feldern in der Datenbank

Unterscheidung hat einen sehr wichtigen Referenzwert beim Erstellen eines neuen Index. In MySQL ist der Unterschied Die Berechnungsregeln lauten wie folgt:

Der Quotient aus der Gesamtzahl der Felder nach der Deduplizierung und der Gesamtzahl der Datensätze in der gesamten Tabelle.

Zum Beispiel:

select count(distinct(name))/count(*) from t_base_user;

Das Ergebnis ist wie folgt folgt:

count(distinct(name))/count(*)
1.0000

Der maximale Wert der Unterscheidung beträgt 1,000 und der minimale Wert beträgt 0,0000. Je größer der Wert der Unterscheidung, dh je größer die Nichtduplizierungsrate der Daten, desto besser ist der neue Indexeffekt Der Primärschlüssel und der eindeutige Schlüssel sind 1,0000. Der Unterscheidungswert bei Feldern wie Status und Geschlecht ist am geringsten. (Dies hängt von der Datenmenge ab. Wenn nur wenige Daten vorhanden sind, ist die Unterscheidung ziemlich hoch. Wenn die Datenmenge groß ist, beträgt die Unterscheidung grundsätzlich 0,0000. Das heißt, nach dem Hinzufügen von Indizes zu diesen Feldern , der Effekt ist nicht gut.

Es ist zu beachten, dass das Ergebnis der Berechnung der Diskriminierung ein Nullwert ist ​sind zwischen 0,0000-1,0000 verteilt.

So erstellen Sie einen Index

(1): Unterscheidung
Ich empfehle dringend, beim Erstellen eines Index aus folgenden Gründen zunächst die Unterscheidung dieses Feldes zu berechnen:

1. Einspaltiger Index
Anhand des Unterscheidungsgrades können Sie auch grob erkennen, ob und wie effektiv der neue Index für das Feld ist. Je größer die Unterscheidung, desto deutlicher ist der Indexierungseffekt.

2. Mehrspaltiger Index (gemeinsamer Index)
Tatsächlich gibt es ein Problem mit der Reihenfolge der Felder im mehrspaltigen Index. Im Allgemeinen werden diejenigen mit höherer Differenzierung zuerst platziert Der gemeinsame Index ist effektiver, zum Beispiel:

select * from t_base_user where name="" and status=1;

Eine Anweisung wie oben, wenn ein gemeinsamer Index erstellt wird, sollte sein:

alter table t_base_user add index idx_name_status(name,status);

statt:

alter table t_base_user add index idx_status_name(status, name);

(2) Prinzip des Abgleichs des Präfixes ganz links
MySQL führt den Abgleich nach rechts fort, bis eine Bereichsabfrage (>, <.) auftritt ;, between, like) und stoppt die Übereinstimmung, wie z (nur zur Demonstration)

In der obigen Anweisung wird der Status nicht an den Index übergeben, da MySQL den Abgleich beendet hat, wenn < . Die Reihenfolge kann angepasst werden, der Status kann jedoch nicht erreicht werden. Zu diesem Zeitpunkt muss die Anweisung wie folgt geändert werden:

select * from t_base_user where type=10 and status=1 andcreated_at< „03.11.2017“

Der Statusindex kann durchlaufen werden.

(3) Funktionsoperation

Führen Sie keine Funktionsoperation für die Indexspalte durch, da sonst der Index ungültig wird. Da der B + -Baum alle Feldwerte in der Datentabelle speichert, müssen Sie beim Abrufen jedoch Funktionen auf alle zu vergleichenden Elemente anwenden, was offensichtlich zu kostspielig ist.

(4) Zuerst die Erweiterung
Zuerst die Erweiterung, keinen neuen Index erstellen, sondern versuchen, den vorhandenen Index zu ändern. Wie folgt:

wählen Sie * aus t_base_user aus, wobei name="andyqian" und email="andytohome" sind

Der Index idx_name ist bereits in der Tabelle t_base_user vorhanden, wenn Sie möchten Ich muss hinzufügen idx_name_email Der Index sollte darin bestehen, den Index idx_name zu ändern, anstatt einen neuen Index zu erstellen.

Korrektur von Missverständnissen

Wie oben erwähnt, wie man einen neuen Index erstellt, können wir nun im ersten Schritt die Missverständnisse beantworten.

Missverständnis 1: Beim Erstellen einer neuen Tabelle muss kein Index erstellt werden, und der Index wird später hinzugefügt

Antwort: Ein gutes Datentabellendesign sollte die Erstellung von Indizes berücksichtigen Am Anfang, anstatt bis später zu warten Wenn etwas schief geht und die Geschäftsnutzung beeinträchtigt wird, wird ein neuer Index erstellt, um die Situation zu retten, und die Kosten für die anschließende Indexerstellung sind relativ hoch. (Dadurch besteht die Möglichkeit, dass Produktionsunfälle Wurzeln schlagen und entstehen)

Missverständnis 2: Die Felder nach der Where-Bedingung sind alle indiziert

Antwort: Dieses Missverständnis kommt relativ häufig vor, aber die Felder danach Die Where-Bedingung wird indiziert. Es ist nicht notwendig, alle Indizes zu erstellen, was zu einem starken Anstieg der Indexdateien führt und nicht den gewünschten Effekt erzielt. Einzelheiten finden Sie oben im Abschnitt zum Erstellen neuer Indizes.

Missverständnis 3: Einfaches SQL erfordert keine Indizierung, und gemeinsame Abfragen erfordern eine Indizierung

Antwort: Dieses Missverständnis muss nun sorgfältig erklärt werden, insbesondere unter der B/S-Struktur Geschäft Die Logik wird auf der Code-Logikebene entfernt. Auf der letzten SQL-Ebene handelt es sich tatsächlich um einfaches SQL mit nur einigen Verbindungsabfragen und mehr Einzeltabellenoperationen /S-Architektur), Sie sagten, diese Aussagen seien nicht einfach.

Missverständnis 4: Die Reihenfolge des gemeinsamen Index ist die Reihenfolge der Felder nach der Where-Bedingung

Antwort: Wie wir gerade gesagt haben, basiert die Reihenfolge des gemeinsamen Index auf dem Präfix ganz links Prinzip und Grad der Unterscheidung Die Unterscheidung hat nichts mit der Reihenfolge der Felder nach der Where-Bedingung zu tun.

Missverständnis 5: Erstellen Sie neue Indizes für Felder mit weniger Unterscheidung

Antwort: Das Erstellen neuer Indizes für Felder mit weniger Unterscheidung ist grundsätzlich wirkungslos und führt auch dazu, dass eine große Anzahl von Indexdateien hinzugefügt wird Ist es den Verlust nicht wert?

Ist der Index wichtig?

Das Obige stellt das Konzept des MySQL-Index und einige Techniken beim Erstellen eines neuen Index vor. So eine theoretische Sache: Bei Kinderschuhen, die nicht oder relativ selten verwendet werden, ist die Bedeutung der Indizierung derzeit möglicherweise nicht so intuitiv. Lassen Sie mich also über die Verluste und Fallstricke sprechen, die ich bei der Indizierung erlitten habe! Es ist auch ein häufiges Problem, keinen Index zu erstellen!

0. Verursacht langsame Abfragen
Dieses Problem ist ein häufiges Problem ohne Indizierung (hier gibt es auch viele Details, wie zum Beispiel: implizite Typkonvertierung usw.)

Verursacht Zeitüberschreitung beim Dienst

Szenario:
Wenn Sie als Dienstleister zu einem bestimmten Zeitpunkt online gehen, bieten Sie Dienstleistungen für Geschäftsparteien an. Zuerst dachte ich, es sei nur ein einfacher Gottesdienst, und der Test ist abgeschlossen. Insgeheim bin ich immer noch froh, dass ich heute endlich früher nach Hause gehen kann!

Beschreibung:
Sobald es tatsächlich gestartet wurde, forderte die Geschäftspartei einen Anruf in der Produktionsumgebung an, und bei jeder Anfrage kam es zu einer Zeitüberschreitung, und die Daten waren zu diesem Zeitpunkt nur gelandet Überprüfen Sie den Code und stellen Sie schließlich fest, dass in der Produktion etwas nicht stimmt. Aufgrund der langsamen Abfrage können Sie sich nicht vorstellen, wie einfach diese Anweisung ist Stellungnahme. Sie sagen, dass dieser Grund dazu führt, dass der Dienst nicht verfügbar ist. Haben Sie Recht oder Unrecht? (Aus diesem Grund sage ich, dass für ein gutes Datentabellendesign neue Indizes von Anfang an berücksichtigt werden müssen).

2. CPU des Datenbankservers 100 %

Wenn die Abfrage bei SQL mit relativ hoher Abfragehäufigkeit langsam ist, weil kein Index erstellt wird, führt dies dazu, dass die CPU des Datenbankservers 100 % beträgt , Auswirkungen aber auf das gesamte System.

Zusammenfassung
Es gibt mehrere oben erwähnte Probleme, die dadurch verursacht werden, dass kein Index erstellt wird. Dies kann zu einer langsamen Abfrage führen und die Systemeffizienz zu 100 % beeinträchtigen Halten Sie die Indizierung zum jetzigen Zeitpunkt für wichtig?

Abschließend

Wie oben kurz erwähnt: Was ist ein Index? Was ist seine Verwendung, sowie einige Tipps zum Erstellen eines Index und betont auch die Bedeutung der Indizierung. Die Indizierung ist also so wichtig. Wie kann sie beim täglichen Codieren vermieden werden? Das Folgende sind meine persönlichen Vorschläge:

1. Beim Erstellen einer Tabelle sollten Sie das Hinzufügen von Indizes wie Fremdschlüsselfeldern usw. in Betracht ziehen.

2. Überprüfen Sie nach dem Schreiben der SQL unbedingt den Ausführungsplan. Versuchen Sie, vollständige Tabellenscans zu vermeiden.

3. Wenn Sie einen Index zu einer vorhandenen Tabelle hinzufügen, müssen Sie zunächst die Unterscheidung des Feldes berechnen.

4. Gemeinsamer Index, stellen Sie die größte Unterscheidung in den Vordergrund.

5. Befolgen Sie das MySQL-Prinzip der linken Spalte

[2]H Berenson, P. Bernstein, J. Gray, J. Melton, E. O ' Neil und P. O'Neil. Eine Kritik der ANSI-SQL-Isolationsstufen, InProceedings of the SIGMOD International Conference on Management of Data, Seiten 1–10, Mai 1995.

[3]Michael J. Cahill, Uwe Röhm und Alan D. Fekete. In SIGMOD '08: Proceedings of the 2008 ACM SIGMOD International Conference on Management of Data, Seiten 729–738, New York, NY, USA. 4 ]Michael James Cahill. 2009. Serialisierbare Isolierung für Snapshot-Datenbanken, University of Sydney, School of Information Technologies[5] A. Fekete, D. Liarokapis, E. O'Neil, undD .Shasha. Snapshot-Isolation serialisierbar machen. In ACM-Transaktionen auf Datenbanksystemen, Band 39(2), Seiten 492–528 🎜 >MySQL-Index--(MySQL-Lernen 2)_MySQL

MySQL-Index-Lernen (1)_MySQL

Ähnliche Videos:

Eine kurze Einführung in die Indizierung – ein Video-Tutorial, das Sie in sechs Tagen durch MySQL führt

Das obige ist der detaillierte Inhalt vonWie lerne ich die MySQL-Indexprinzipien? Meine eigene Zusammenfassung meiner Indexierungserfahrung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!