Wann muss ein Index für ein SQL-Tabellenfeld (MySQL) hinzugefügt werden?

Hier ist ein Update mit einigen Tasten, die verwendet werden Ссылка

Der Motor muss die Kosten für die Verwendung eines Index mit den Kosten vergleichen, die entstehen, wenn er nicht verwendet wird. Sie werden feststellen, dass ich einige weitere Zeilen hinzufügen musste, um die verwendeten Indizes zu erhalten.

Bei einem Index muss die Engine den Index verwenden, um übereinstimmende Werte zu erhalten, was schnell ist. Dann muss es die Übereinstimmungen verwenden, um die tatsächlichen Zeilen in der Tabelle nachzuschlagen. Wenn der Index die Anzahl der Zeilen nicht einschränkt, kann es schneller sein, alle Zeilen in der Tabelle nachzuschlagen.

Ich bin mir nicht sicher, ob mysql etwas Ähnliches wie SQL Server-Clustered-Indizes hat. In diesem Fall sind die Index- und Tabellendaten in der gleichen Struktur, so dass Sie den zweiten Schritt der Indexsuche nicht haben.

Ich habe Indizes auf zwei verschiedene Arten eingeführt, erstens in der Benutzertabelle, indem ich einen Primärschlüssel definiert habe. Dadurch wird implizit ein eindeutiger Index für die Spalte user_id erstellt. Ein eindeutiger Index bedeutet, dass Sie nicht denselben Satz von Werten zweimal einfügen können. Für einen einzelnen Spaltenindex bedeutet dies nur, dass Sie nicht zweimal denselben Wert haben können.

Wenn Sie sich ein Buch mit Benutzern für die Tabelle mit einem Benutzer pro Seite vorstellen, gibt der erstellte Index eine sortierte Liste von Benutzer-ID mit jeweils der Seitennummer des Benutzers. Die Liste wird normalerweise in einer Art Baumform gespeichert, um das Nachschlagen einer bestimmten Nummer schnell zu machen. Denken Sie über die Art nach, wie Sie einen Namen in einem Telefonbuch nachschlagen. Sie scannen nicht einfach alle Seiten, bis Sie sie gefunden haben. Sie machen eine Vermutung, wo sie sein werden, und blättern Sie dann weiter oder zurück, bis Sie in der Nähe sind . Normalerweise können Sie Werte in einem Index in O (log _{2 ) nachschlagen, wobei n die Anzahl der Zeilen ist und Sie eine ähnliche Anzahl von Indexseiten lesen müssen.}

Wenn die DB-Engine jetzt die Abfrage select * from users Where user_id = 3 erhält, hat sie zwei Möglichkeiten. es kann jede Datenseite lesen und nach dem richtigen Wert suchen (es könnte die Tatsache verwenden, dass ein Primärschlüssel bei der ersten anhält). Die Alternative besteht darin, den Index zu lesen, um die richtige Datenseite zu erhalten, und dann die Datenseite nachzuschlagen.

Nehmen Sie für Konkretheit und Einfachheit an, dass die Tabelle 1024 Einträge hat. Angenommen, jeder Eintrag benötigt eine Datenseite. Angenommen, jeder Eintrag in der Indexstruktur benötigt eine Indexseite. Nehmen wir an, der Index ist ausgeglichen, also hat er 10 Stufen und insgesamt 2047 Seiten. (All diese Annahmen sind verdächtig, aber sie haben den entscheidenden Punkt, insbesondere Indexseiten sind fast immer kleiner als Datenseiten, da Sie nicht dazu neigen, alle Spalten gleichzeitig zu indizieren).

Um den Tabellenscan-Ansatz durchzuführen, müssen 1024 Datenseiten gelesen werden. Um den Index zu verwenden, müssen 10 Indexseiten und eine Datenseite gelesen werden. Bei fast allen Datenbankperformances geht es darum, die Anzahl der gelesenen Seiten zu minimieren.

Multi-Spalten-Indizes ermöglichen das schnelle Nachschlagen von Datensätzen. Wenn Sie einen Index mit (col1, col2) haben, wird sogar nur der Vergleich auf col1 verbessert.

Die Anweisung create index sagt nur, welche Spalten indiziert sind und ob doppelte Werte zulässig sind.

Wenn Sie die Buchanalogie erneut verwenden, erstellt Create Index ix_comment_id on votes (comment_id, voter_id) eine geordnete Liste von comment_id und dann voter_id mit dem Verweis auf die entsprechende Datenzeile.

Es ist nicht immer klar, wo Indizes in SQL-Tabellen verwendet werden. Aber es gibt einige allgemeine Faustregeln, die Ihnen in den meisten Fällen helfen können.

1. Geben Sie den Index für Spalten ein, die in where-Klauseln
verwendet werden
2. Geben Sie den Index für Spalten ein, mit denen Sie sich verbinden.
3. Versuchen Sie nicht, mehr als 4-5 Indizes für Spalten in derselben Tabelle zu verwenden.

Und die allgemeinen Konzepte, die Sie beachten sollten, sind:

1. Jeder von Ihnen verwendete Index wird die Suche in diesen Spalten beschleunigen.
2. Jeder von Ihnen hinzugefügte Index verursacht das Einfügen in diese Tabelle, um etwas langsamer zu sein.
3. Von den vorherigen zwei. Es liegt in Ihrer Verantwortung zu entscheiden, wie viele Einfügungen und Abfragen Sie an Tabellen durchführen, um zu entscheiden, ob Sie den Index und welche Spalten verwenden möchten.

BEARBEITEN

@AndrewLazarus Kommentar ist wirklich wichtig und ich beschloss, es zur Antwort hinzuzufügen:

1. Verwenden Sie keine Indizes für Spalten mit nur wenigen unterschiedlichen Werten. Zum Beispiel eine Spalte, die einen Zustand enthält, wenn es nur wenige Zustände gibt, oder einen booleschen Wert. Der Grund dafür ist, dass der Index Ihnen nicht wirklich hilft, da er nur durch die Anzahl der Werte geteilt wird, und da Sie nur wenige davon haben, wird es keinen wirklichen Nutzen geben. Die Tabelle würde mehr Speicherplatz beanspruchen, wenn der Index und der Vorformling beim Einfügen langsamer werden. Bei der Abfrage von
erhalten Sie jedoch keine wesentlich bessere Leistung