Was sind Ihre Grundsätze für die Gestaltung von Indizes? Wie lassen sich Indexfehler vermeiden?

Wir haben zuvor die Prinzipien der Indizierung und Indexabfrage ausführlich vorgestellt. Wie das Sprichwort sagt: Wenn Sie Ihre Arbeit gut machen möchten, müssen Sie zuerst Ihre Werkzeuge schärfen. Während der Lernphase müssen Sie sich dieses Wissen Schritt für Schritt aneignen. Seien Sie nicht zu ehrgeizig. Sie müssen geduldig sein und danach streben, jeden Wissenspunkt nach dem Lernen sofort zu beherrschen und ihn dann anzuwenden.

Wir haben im vorherigen Artikel besprochen, dass das Design des Indexes auf WHERE -Bedingung und den Feldern nach ORDER BY und GROUP BY Die spezifischen Gründe dafür werden in meinem vorherigen Artikel „Die Prinzipien von MySQL-Indizes“ ausführlich erläutert. Hier stellen wir es kurz dar.

MySQL verwaltet eine B+-Baumstruktur für den Primärschlüsselindex, die wir als Clustered-Index bezeichnen. Für Nicht-Primärschlüssel (im Allgemeinen gemeinsame Indizes) werden die Indexfelder der Reihe nach sortiert, und dann beginnt der Vergleich mit dem ersten Feldwert. Wenn der erste Feldwert gleich ist, wird der nächste Feldwert verglichen und so weiter.

Sind die Feldwerte im gemeinsamen Index gleich, erfolgt eine Sortierung nach dem Primärschlüssel. Darüber hinaus speichert der B + -Baum des gruppierten Index (Primärschlüsselindex) alle Informationen einer Datensatzzeile, während der nicht gruppierte Index (Nicht-Primärschlüsselindex) nur die Indexfeldwerte und die Primärschlüsselfeldwerte speichert.

Nun, das ist alles, was wir zur Überprüfung der Indexprinzipien zu sagen haben. In diesem Artikel werden wir weiterhin die Grundprinzipien der MySQL-Einstellungen vorstellen. Dies ist auch leicht zu verstehen. Es geht darum, welche Prinzipien beim Entwerfen und Erstellen von Indizes befolgt werden müssen und wie Indizes gemäß den „Standards“ erstellt werden. Heute erklären wir alle Prinzipien des Indexdesigns auf einmal.

Lassen Sie mich noch ein paar Worte zu diesem Wissenspunkt sagen. Bei Vorstellungsgesprächen stelle ich den Kandidaten diese Frage häufig, um herauszufinden, ob sie den Index wirklich verstehen, anstatt einfach nur Fachjargon aufzusagen!

Primärschlüsselindex

Der Primärschlüsselindex ist eigentlich der Einfachste, allerdings gibt es hier einige Dinge zu beachten, die ich nochmal erläutern möchte.

Beim Entwurf des Primärschlüssels muss dieser automatisch inkrementell sein. Es wird dringend empfohlen, UUID nicht als Primärschlüssel zu verwenden.

Warum? Da UUID ungeordnet ist, sortiert MySQL die Daten bei der Pflege des Clustered-Index in der Reihenfolge des Primärschlüssels. Das heißt, die Daten auf jeder Datenseite müssen entsprechend dem Primärschlüssel von klein nach groß sortiert werden. Darüber hinaus sind die Daten durch eine einseitig verknüpfte Liste miteinander verbunden. Der Wert des größten Primärschlüssels auf der vorherigen Datenseite muss kleiner sein als der Wert des kleinsten Primärschlüssels auf der nächsten Datenseite. Die Datenseiten werden durch eine zweiseitig verknüpfte Liste gepflegt.

Wir befolgen noch immer die alten Regeln und zeichnen ein Bild, damit es jeder versteht

Wenn der Primärschlüssel automatisch inkrementiert wird, muss MySQL nur das Primärschlüsselverzeichnis verwenden, um schnell zu finden, wo der neue Datensatz eingefügt werden soll. Wenn der Primärschlüssel nicht automatisch inkrementiert wird, muss jedes Mal von vorne begonnen werden, die richtige Position gefunden und dann der Datensatz eingefügt werden. Dies würde die Effizienz erheblich beeinträchtigen, daher muss der Primärschlüssel so konzipiert sein, dass er automatisch inkrementiert.

Darüber hinaus ähnelt der eindeutige Index dem Primärschlüsselindex, er wächst jedoch nicht unbedingt selbst, sodass die Kosten für die Pflege des eindeutigen Index definitiv höher sind als für den Primärschlüsselindex.

Der Wert eines eindeutigen Indexes ist jedoch eindeutig (ein eindeutiger Index kann den Wert NULL haben), sodass Sie einen Datensatz schneller über das Indexfeld ermitteln können, dafür aber möglicherweise eine Tabellenabfrage durchführen müssen (ich werde hier nicht näher darauf eingehen, was eine Tabellenabfrage ist, da dies bereits im vorherigen Artikel ausführlich erläutert wurde).

Erstellen Sie Indizes für häufig abgefragte Felder

Wenn wir Indizes erstellen, müssen wir Indizes für Felder erstellen, die häufig als Abfragebedingungen verwendet werden, wodurch die Abfragegeschwindigkeit der gesamten Tabelle verbessert werden kann.

Da die Abfragebedingung jedoch meist kein einzelnes Feld ist, werden meist mehrere gemeinsame Indizes erstellt.

Darüber hinaus gibt es in den Abfragebedingungen normalerweise Fuzzy-Abfragen wie „like“. Wenn es sich um eine Fuzzy-Abfrage handelt, ist es am besten, dem Abfrageprinzip mit dem am weitesten links stehenden Präfix zu folgen.

Vermeiden Sie die Indizierung großer Felder

Anders ausgedrückt heißt das: Versuchen Sie, Felder mit kleinem Datenvolumen als Indizes zu verwenden.

Angenommen, es gibt zwei solche Felder, eines ist varchar(5) und das andere ist varchar(200) . In diesem Fall ist es vorzuziehen, einen Index für varchar(5) zu erstellen, da MySQL die Feldwerte bei der Indexpflege zusammen verwaltet. Dies führt zwangsläufig dazu, dass der Index mehr Platz einnimmt und das Vergleichen beim Sortieren mehr Zeit in Anspruch nimmt.

Was ist, wenn Sie einen Index für varchar(100) erstellen möchten? Nehmen Sie dann einige Daten. Wenn address Adresstyp beispielsweise varchar(200) ist, können Sie ihn beim Erstellen des Indexes folgendermaßen schreiben:

INDEX ERSTELLEN tbl_address ON dual(adresse(20));

Wählen Sie eine Spalte mit hoher Trennschärfe als Index

Was bedeutet das? Ich glaube, jeder wird es sofort verstehen, wenn ich Ihnen ein Beispiel gebe.

Angenommen, es gibt ein Feld „Geschlecht“ und der Wert der darin gespeicherten Daten ist entweder männlich oder weiblich, dann ist ein solches Feld nicht als Index geeignet.

Das Hauptmerkmal des Wertes eines solchen Feldes ist, dass die Unterscheidung nicht hoch genug ist und Felder mit geringer Unterscheidung nicht für die Indizierung geeignet sind. Warum?

Denn wenn die Wahrscheinlichkeit für das Auftreten eines Wertes nahezu gleich ist, ist es wahrscheinlich, dass Sie die Hälfte der Daten erhalten, unabhängig davon, nach welchem ​​Wert Sie suchen.

In diesen Fällen ist es besser, keinen Index zu haben, da MySQL auch über einen Abfrageoptimierer verfügt. Wenn der Abfrageoptimierer feststellt, dass ein bestimmter Wert in einem hohen Prozentsatz der Datenzeilen in der Tabelle vorkommt, ignoriert er im Allgemeinen den Index und führt einen vollständigen Tabellenscan durch.

Die übliche Prozentgrenze liegt bei „30 %“. (Wenn die Menge der übereinstimmenden Daten eine bestimmte Grenze überschreitet, wird die Abfrage die Verwendung des Indexes abbrechen (dies ist auch eines der Szenarien, in denen der Index fehlschlägt).

Hier ist der Grund. Nach der Lektüre dieses Artikels sollte meiner Meinung nach jeder wissen, warum wir die Verwendung von Feldern mit geringer Kardinalität als Indizes vermeiden sollten. Tatsächlich handelt es sich hierbei um einen MySQL -Begriff [Kardinalität (Indexkardinalität) ist ein sehr wichtiges Konzept des MySQL-Index]

Versuchen Sie, Indizes für Felder nach ORDER BY und GROUP BY zu erstellen

Erstellen Sie einen Index für das Feld nach Order By , damit Sie beim Abfragen nicht erneut sortieren müssen, da wir bereits wissen, dass die Datensätze im B+-Baum nach der Indexerstellung sortiert werden.

GROUP BY 和ORDER BY sind eigentlich ähnlich, deshalb werde ich sie zusammen besprechen.

Denn wenn GROUP BY verwendet wird, müssen zuerst die auf GROUP BY folgenden Felder sortiert werden und dann wird die Aggregationsoperation ausgeführt.

Wenn die Felder nach GROUP BY nicht sortiert sind, muss MySQL sie zuerst sortieren, wodurch eine temporäre Tabelle, eine sortierte temporäre Tabelle generiert wird, und dann Aggregationsvorgänge in der temporären Tabelle ausführen. Dies ist natürlich sehr ineffizient. Wenn die Felder nach GROUP BY indiziert wurden, muss MySQL sie nicht erneut sortieren, und es wird keine temporäre Tabelle generiert.

Der schwierige Teil besteht jedoch darin, dass, wenn sich GROUP BY von ORDER BY unterscheidet, eine temporäre Tabelle generiert wird, auch wenn beide über Indizes verfügen. Tatsächlich habe ich online gesucht und es scheint viele solcher Fälle zu geben. Ich werde sie hier auflisten. Ehrlich gesagt, obwohl dies Standards sind, scheint dieser Standard schwierig umzusetzen zu sein, da das tatsächliche Szenario definitiv nicht so einfach und rein ist.

1. Wenn die Spalte GROUP BY keinen Index hat, wird eine temporäre Tabelle erstellt.
2. Wenn während GROUP BY mehr als eine GROUP BY-Spalte in der SELECT-Anweisung vorhanden ist und die GROUP BY-Spalte nicht der Primärschlüssel ist, wird eine temporäre Tabelle generiert.
3. Wenn die Spalte GROUP BY einen Index hat und die Spalte ORDER BY nicht, wird eine temporäre Tabelle erstellt.
4. Wenn sich die Spalte GROUP BY von der Spalte ORDER BY unterscheidet, wird eine temporäre Tabelle generiert, auch wenn beide über Indizes verfügen.
5. Wenn die Spalte in GROUP BY oder ORDER BY nicht aus der ersten Tabelle in der JOIN-Anweisung stammt, wird eine temporäre Tabelle generiert.
6. Wenn die Spalten DISTINCT und ORDER BY keinen Index haben, wird eine temporäre Tabelle erstellt.
7. Wenn die Spalten in GROUP BY und ORDER BY identisch sind und Primärschlüssel darstellen, die SELECT-Spalte jedoch andere Spalten als GROUP BY-Spalten enthält, wird ebenfalls eine temporäre Tabelle generiert.

Verwenden Sie keine Funktionen in Bedingungssätzen

Wenn eine Funktionsoperation auf einem Feld eines festgelegten Indexes ausgeführt wird, kann der Index nicht verwendet werden.

warum ist das so?

Da der MySQL für den Index verwaltete B + -Baum auf den Originaldaten des Felds basiert, betrachtet MySQL dies nicht als das Originalfeld, wenn während der Verwendung eine Funktion hinzugefügt wird, und verwendet den Index mit Sicherheit nicht.

Aber was soll ich tun, wenn jemand stur ist und ich die Funktion nutzen möchte? Sie können Ihr Geschäft nicht nur der Indexierung wegen ändern, oder? Wenn der Index aufgrund der Verwendung interner MySQL -Funktionen ungültig ist, können Sie die Funktion beim Erstellen des Index gemeinsam erstellen.

Was bedeutet das? Angenommen, es gibt ein Feld namens age und es wird ein Index dafür erstellt. Wenn es jedoch verwendet wird, sieht es so aus

SELECT * FROM student WHERE rund(Alter) = 2;

Der Index wird derzeit nicht verwendet. Wenn Sie wirklich möchten, dass round(age) indiziert wird, können Sie einen Index wie folgt erstellen:

Erstelle den Index stu_age_round bei Test(round(age));

Wenn Sie zu diesem Zeitpunkt die Abfrage mit der oben beschriebenen Methode durchführen, wird der Index wirksam. Ich glaube, das kann jeder verstehen.

Erstellen Sie nicht zu viele Indizes

Da MySQL zum Verwalten der Indizes Speicherplatz benötigt und Leistung verbraucht, verwaltet MySQL für jedes Indexfeld einen B+-Baum.

Wenn also zu viele Indizes vorhanden sind, erhöht dies zweifellos die Belastung von MySQL.

Erstellen Sie keine Indizes für Felder, die häufig hinzugefügt, gelöscht oder geändert werden.

Dies ist leicht zu verstehen, da MySQL , wie wir bereits zuvor erläutert haben, den Index neu verwalten muss, wenn sich das Feld ändert.

Angenommen, ein Feld wird häufig geändert, bedeutet dies, dass der Index häufig neu erstellt werden muss, was sich zwangsläufig auf die Leistung von MySQL auswirkt. Mehr verrate ich hier nicht.

Das meiste, worüber wir hier gesprochen haben, sind einige Prinzipien, die beim Entwerfen beachtet werden müssen. Tatsächlich müssen die tatsächlichen Prinzipien noch entsprechend dem tatsächlichen Geschäft geändert werden. Es gibt keine sogenannte „Formel“. Das Design, das zu Ihrem tatsächlichen Geschäftsszenario passt, ist das Beste. Streben Sie daher nicht zu sehr nach „Optimierung“, denn das wird oft nach hinten losgehen. Schließlich ist es einfach nur Hooliganismus, über Technologie zu sprechen, ohne das Geschäft zu berücksichtigen.

Okay, schauen wir uns die Situationen genauer an, in denen der Index fehlschlägt. (PS: Dieser Artikel ist im Grunde reine Theorie. Ich wollte ein Bild zeichnen, um es auszudrücken, aber ich habe festgestellt, dass ich überhaupt nicht anfangen konnte. Ich hoffe, jeder kann dabei bleiben. Es wird bald fertig sein.)

Häufige Szenarien für Indexfehler

1. Die Verwendung des Schlüsselworts OR führt dazu, dass der Index ungültig wird. Wenn Sie jedoch OR verwenden möchten und nicht möchten, dass der Index ungültig wird, müssen Sie für jede Spalte in or Bedingung einen Index erstellen. Dies steht offensichtlich im Widerspruch zum obigen Rat, nicht zu viele Indizes zu erstellen.
2. Wenn der gemeinsame Index nicht dem Prinzip des ganz linken Präfixes folgt, ist der Index ebenfalls ungültig.
3. Bei Verwendung einer Fuzzy-Abfrage führt das Beginnen mit % ebenfalls zu einem Indexierungsfehler (ich werde den Grund hier nicht wiederholen, da er bereits im vorherigen Artikel erwähnt wurde. Dies soll Ihnen helfen, sich noch einmal daran zu erinnern).
4. Wenn die Indexspalte eine implizite Konvertierung verwendet, wird der Index ebenfalls ungültig.

Vorausgesetzt, das Feld age ist vom Typ int, fragen wir normalerweise folgendermaßen ab

SELECT * FROM student WHERE Alter=15

Die obige Situation kann den Index verwenden, aber wenn Sie schreiben

Wählen Sie * aus Schüler, wobei Alter = '15' ist.

In diesem Fall kann der Index nicht verwendet werden, d. h. der Index age ist ungültig.

Wenn die Feldkardinalität klein ist, kann es auch zu Indexfehlern kommen, wie im vorherigen Teil dieses Artikels ausführlich erläutert wurde. Die Ursache hierfür liegt im MySQL Abfrageoptimierer.

Weitere Prinzipien finden Sie in den Prinzipien der Indizierung und den Grundprinzipien der Abfrage. Ohne die vorherige Vorbereitung können diese etwas leer erscheinen. Lernen Sie daher bitte Schritt für Schritt den Index kennen. Dies ist im Grunde der Kern des Wissens, wenn wir MySQL verwenden.

Oben finden Sie den detaillierten Inhalt von „Was sind die Prinzipien Ihres Indexdesigns? Wie vermeide ich Indexfehler?“. Weitere Informationen zu den Prinzipien des Indexdesigns finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!