MySQL-Index

Ein MySQL-Index ist eine Datenstruktur, die verwendet wird, um die Geschwindigkeit und Leistung von Datenbankabfragen zu erhöhen.

Das Einrichten von MySQL-Indizes ist wichtig für den effizienten Betrieb von MySQL; Indizes können die Abrufgeschwindigkeit von MySQL erheblich verbessern.

Ein MySQL-Index ähnelt einem Buchindex. Durch das Speichern von Zeigern auf Datenzeilen kann er bestimmte Daten in einer Tabelle schnell lokalisieren und darauf zugreifen.

Als Analogie: Wenn eine MySQL-Datenbank mit gut entworfenen und ordnungsgemäß verwendeten Indizes ein Lamborghini ist, dann ist eine MySQL-Datenbank ohne entworfene und verwendete Indizes ein durch Menschenkraft betriebenes Dreirad.

Nehmen wir die Indexseite eines chinesischen Wörterbuchs als Beispiel: Mithilfe des nach Pinyin, Strichzahl, Radikalen usw. sortierten Inhaltsverzeichnisses (Index) können wir die benötigten Schriftzeichen schnell finden.

Indizes werden in Einzelspaltenindizes und zusammengesetzte Indizes unterteilt:

  • Ein Einzelspaltenindex bedeutet, dass ein Index nur eine einzelne Spalte enthält; eine Tabelle kann mehrere Einzelspaltenindizes haben.
  • Ein zusammengesetzter Index bedeutet, dass ein Index mehrere Spalten enthält.

Beim Erstellen eines Index müssen Sie sicherstellen, dass der Index auf die Bedingungen in SQL-Abfrageanweisungen angewendet wird (im Allgemeinen als Bedingungen in der WHERE-Klausel).

Tatsächlich ist ein Index auch eine Tabelle, die den Primärschlüssel und die Indexfelder speichert und auf die Datensätze in der Entitätstabelle verweist.

Obwohl Indizes die Abfrageleistung verbessern können, müssen Sie auch die folgenden Punkte beachten:

  • Indizes benötigen zusätzlichen Speicherplatz.
  • Bei Einfüge-, Aktualisierungs- und Löschvorgängen für eine Tabelle müssen Indizes gepflegt werden, was die Leistung beeinträchtigen kann.
  • Zu viele oder unangemessene Indizes können zu Leistungseinbußen führen, daher müssen Indizes sorgfältig ausgewählt und geplant werden.

Normaler Index

Indizes können die Abfragegeschwindigkeit erheblich verbessern, insbesondere bei der Suche in großen Tabellen. Durch die Verwendung von Indizes kann MySQL die Datenzeilen, die die Abfragebedingungen erfüllen, direkt lokalisieren, ohne die gesamte Tabelle Zeile für Zeile zu durchsuchen.

Index erstellen

Verwenden Sie dieCREATE INDEX-Anweisung, um einen normalen Index zu erstellen.

Ein normaler Index ist der häufigste Indextyp und wird verwendet, um Abfragen auf Daten in einer Tabelle zu beschleunigen.

CREATE INDEXDie Syntax lautet wie folgt:

CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
  • CREATE INDEX: Das Schlüsselwort, das zum Erstellen eines normalen Index verwendet wird.
  • index_name: Gibt den Namen des zu erstellenden Index an. Der Indexname muss innerhalb der Tabelle eindeutig sein.
  • table_name: Gibt an, in welcher Tabelle der Index erstellt werden soll.
  • (column1, column2, ...): Gibt die Spaltennamen der Tabelle an, die indiziert werden sollen. Sie können eine oder mehrere Spalten als zusammengesetzten Index angeben. Die Datentypen dieser Spalten sind normalerweise numerisch, Text oder Datum.
  • ASCundDESC(Optional): Wird verwendet, um die Sortierreihenfolge des Index anzugeben. Standardmäßig wird der Index in aufsteigender Reihenfolge (ASC) sortiert.

Das folgende Beispiel geht davon aus, dass wir eine Tabelle namens students haben, die die Spalten id, name und age enthält. Wir werden einen normalen Index für die Spalte name erstellen.

CREATE INDEX idx_name ON students (name);

Die obige Anweisung erstellt einen normalen Index namens idx_name für die Spalte name der Tabelle students, was die Abfrageleistung für die Suche nach name verbessert.

Beachten Sie, dass bei einer großen Datenmenge in der Tabelle das Erstellen eines Index einige Zeit dauern kann, aber sobald er erstellt wurde, wird die Abfrageleistung erheblich verbessert.

Tabellenstruktur ändern (Index hinzufügen)

Wir können denALTER TABLE-Befehl verwenden, um einen Index in einer vorhandenen Tabelle zu erstellen.

ALTER TABLE ermöglicht es Ihnen, die Struktur einer Tabelle zu ändern, einschließlich des Hinzufügens, Änderns oder Löschens von Indizes.

Die Syntax zum Erstellen eines Index mit ALTER TABLE:

ALTER TABLE table_name
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
  • ALTER TABLE: Das Schlüsselwort, das zum Ändern der Tabellenstruktur verwendet wird.
  • table_name: Gibt den Namen der zu ändernden Tabelle an.
  • ADD INDEX: Die Klausel zum Hinzufügen eines Index.ADD INDEXWird verwendet, um einen normalen Index zu erstellen.
  • index_name: Gibt den Namen des zu erstellenden Index an. Der Indexname muss innerhalb der Tabelle eindeutig sein.
  • (column1, column2, ...): Gibt die Spaltennamen der Tabelle an, die indiziert werden sollen. Sie können eine oder mehrere Spalten als zusammengesetzten Index angeben. Die Datentypen dieser Spalten sind normalerweise numerisch, Text oder Datum.
  • ASCundDESC(Optional): Wird verwendet, um die Sortierreihenfolge des Index anzugeben. Standardmäßig wird der Index in aufsteigender Reihenfolge (ASC) sortiert.

Nachfolgend ein Beispiel. Wir erstellen einen normalen Index für eine vorhandene Tabelle namens employees:

ALTER TABLE employees
ADD INDEX idx_age (age);

Die obige Anweisung erstellt einen normalen Index namens idx_age für die Spalte age der Tabelle employees.

Direkt beim Erstellen der Tabelle angeben

Beim Erstellen einer Tabelle können SieCREATE TABLEdirekt Indizes in der Anweisung angeben, um eine Kombination aus Tabelle und Indizes zu erstellen.

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);
  • CREATE TABLE: Das Schlüsselwort, das zum Erstellen einer neuen Tabelle verwendet wird.
  • table_name: Gibt den Namen der zu erstellenden Tabelle an.
  • (column1 data_type, column2 data_type, ...): Definiert die Spaltennamen und Datentypen der Tabelle. Sie können eine oder mehrere Spalten als zusammengesetzten Index festlegen. Die Datentypen dieser Spalten sind normalerweise numerisch, Text oder Datum.
  • INDEX: Das Schlüsselwort, das zum Erstellen eines normalen Index verwendet wird.
  • index_name: Gibt den Namen des zu erstellenden Index an. Der Indexname muss innerhalb der Tabelle eindeutig sein.
  • (column1, column2, ...): Gibt die Spaltennamen der Tabelle an, die indiziert werden sollen. Sie können eine oder mehrere Spalten als zusammengesetzten Index angeben. Die Datentypen dieser Spalten sind normalerweise numerisch, Text oder Datum.
  • ASCundDESC(Optional): Wird verwendet, um die Sortierreihenfolge des Index anzugeben. Standardmäßig wird der Index in aufsteigender Reihenfolge (ASC) sortiert.

Nachfolgend finden Sie ein Beispiel. Wir erstellen eine Tabelle namens students und erstellen einen normalen Index für die Spalte age.

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  INDEX idx_age (age)
);

Im obigen Beispiel haben wir einen normalen Index namens idx_age für die Spalte age der Tabelle students erstellt.

Syntax zum Löschen eines Index

Wir können dieDROP INDEX-Anweisung verwenden, um einen Index zu löschen.

Die Syntax für DROP INDEX:

DROP INDEX index_name ON table_name;
  • DROP INDEX: Das Schlüsselwort, das zum Löschen eines Index verwendet wird.
  • index_name: Gibt den Namen des zu löschenden Index an.
  • ON table_name: Gibt an, in welcher Tabelle der Index gelöscht werden soll.

Verwenden SieALTER TABLEDie Syntax zum Löschen eines Index mit dieser Anweisung lautet wie folgt:

ALTER TABLE table_name
DROP INDEX index_name;
  • ALTER TABLE: Das Schlüsselwort, das zum Ändern der Tabellenstruktur verwendet wird.
  • table_name: Gibt den Namen der zu ändernden Tabelle an.
  • DROP INDEX: Die zum Löschen eines Index verwendete Klausel.
  • index_name: Gibt den Namen des zu löschenden Index an.

Das folgende Beispiel geht davon aus, dass wir eine Tabelle namens employees mit einem Index namens idx_age auf der Spalte age haben. Jetzt möchten wir diesen Index löschen:

DROP INDEX idx_age ON employees;

Oder verwenden SieALTER TABLEAnweisung:

ALTER TABLE employees
DROP INDEX idx_age;

Beide Befehle löschen den Index namens idx_age aus der Tabelle employees.

Wenn der Index nicht existiert, tritt bei der Ausführung des Befehls ein Fehler auf. Daher ist es am besten, vor dem Löschen eines Index zu prüfen, ob der Index existiert, oder einen Fehlerbehandlungsmechanismus zu verwenden, um mögliche Fehlersituationen zu behandeln.

Eindeutiger Index

In MySQL können Sie dieCREATE UNIQUE INDEXAnweisung verwenden, um einen eindeutigen Index zu erstellen.

Ein eindeutiger Index stellt sicher, dass die Werte im Index eindeutig sind und doppelte Werte nicht zulässig sind.

Index erstellen

CREATE UNIQUE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
  • CREATE UNIQUE INDEX: Die Kombination von Schlüsselwörtern, die zum Erstellen eines eindeutigen Index verwendet wird.
  • index_name: Gibt den Namen des zu erstellenden eindeutigen Index an. Der Indexname muss innerhalb der Tabelle eindeutig sein.
  • table_name: Gibt an, in welcher Tabelle der eindeutige Index erstellt werden soll.
  • (column1, column2, ...): Gibt die Spaltennamen der Tabelle an, die indiziert werden sollen. Sie können eine oder mehrere Spalten als zusammengesetzten Index angeben. Die Datentypen dieser Spalten sind normalerweise numerisch, Text oder Datum.
  • ASCundDESC(Optional): Wird verwendet, um die Sortierreihenfolge des Index anzugeben. Standardmäßig wird der Index in aufsteigender Reihenfolge (ASC) sortiert.

Das Folgende ist ein Beispiel für das Erstellen eines eindeutigen Index: Angenommen, wir haben eine Tabelle namens employees mit den Spalten id und email. Jetzt möchten wir einen eindeutigen Index für die Spalte email erstellen, um sicherzustellen, dass die E-Mail-Adresse jedes Mitarbeiters eindeutig ist.

CREATE UNIQUE INDEX idx_email ON employees (email);

Das obige Beispiel erstellt einen eindeutigen Index namens idx_email für die Spalte email der Tabelle employees.

Tabellenstruktur ändern, um einen Index hinzuzufügen

Wir können den BefehlALTER TABLEverwenden, um einen eindeutigen Index zu erstellen.

ALTER TABLEMit dem Befehl können Sie die Struktur einer vorhandenen Tabelle ändern, einschließlich des Hinzufügens neuer Indizes.

ALTER table table_name 
ADD CONSTRAINT unique_constraint_name UNIQUE (column1, column2, ...);
  • ALTER TABLE: Das Schlüsselwort, das zum Ändern der Tabellenstruktur verwendet wird.
  • table_name: Gibt den Namen der zu ändernden Tabelle an.
  • ADD CONSTRAINT: Dies ist das Schlüsselwort zum Hinzufügen von Constraints (einschließlich eindeutiger Indizes).
  • unique_constraint_name : Gibt den Namen des zu erstellenden eindeutigen Index an. Der Constraint-Name muss innerhalb der Tabelle eindeutig sein.
  • UNIQUE (column1, column2, ...): Gibt die Spaltennamen der Tabelle an, die indiziert werden sollen. Sie können eine oder mehrere Spalten als zusammengesetzten Index angeben. Die Datentypen dieser Spalten sind normalerweise numerisch, Text oder Datum.

Das Folgende ist ein Beispiel für die Verwendung vonALTER TABLEzum Erstellen eines eindeutigen Index mit einem Befehl: Angenommen, wir haben eine Tabelle namens employees, die die Spalten id und email enthält, und jetzt möchten wir einen eindeutigen Index für die Spalte email erstellen, um sicherzustellen, dass die E-Mail-Adresse jedes Mitarbeiters eindeutig ist.

ALTER TABLE employees
ADD CONSTRAINT idx_email UNIQUE (email);

Das obige Beispiel erstellt einen eindeutigen Index namens idx_email für die Spalte email der Tabelle employees.

Beachten Sie, dass das Hinzufügen eines eindeutigen Index fehlschlägt, wenn bereits doppelte email-Werte in der Tabelle vorhanden sind. Bevor Sie einen eindeutigen Index erstellen, müssen Sie möglicherweise sicherstellen, dass die Spalte email in der Tabelle keine doppelten Werte enthält.

Direkt beim Erstellen der Tabelle angeben

Wir können die Tabelle auch erstellen und gleichzeitig können Sie dasUNIQUESchlüsselwort in derCREATE TABLEAnweisung verwenden, um einen eindeutigen Index zu erstellen.

Dies definiert die Constraint für den eindeutigen Index gleichzeitig mit der Erstellung der Tabelle.

CREATE TABLESyntax zum Erstellen eines eindeutigen Index in der Anweisung:

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  CONSTRAINT index_name UNIQUE (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);
  • CREATE TABLE: Schlüsselwort, das verwendet wird, um eine neue Tabelle zu erstellen.
  • table_name: Gibt den Namen der zu erstellenden Tabelle an.
  • (column1 data_type, column2 data_type, ...): Definiert die Spaltennamen und Datentypen der Tabelle. Sie können eine oder mehrere Spalten als Indexkombination angeben. Die Datentypen dieser Spalten sind normalerweise numerisch, Text oder Datum.
  • CONSTRAINT: Schlüsselwort, das verwendet wird, um eine Einschränkung hinzuzufügen.
  • index_name: Gibt den Namen des zu erstellenden eindeutigen Index an. Der Constraint-Name muss innerhalb der Tabelle eindeutig sein.
  • UNIQUE (column1, column2, ...): Gibt den Namen der Tabellenspalte an, die indiziert werden soll.
  • ASCundDESC(Optional): Wird verwendet, um die Sortierreihenfolge des Index anzugeben. Standardmäßig wird der Index in aufsteigender Reihenfolge (ASC) sortiert.

Das folgende ist ein Beispiel für das Erstellen eines eindeutigen Index beim Erstellen einer Tabelle: Angenommen, wir möchten eine Tabelle namens employees erstellen, die die Spalten id, name und email enthält, und wir möchten, dass die Werte in der Spalte email eindeutig sind, also definieren wir beim Erstellen der Tabelle einen eindeutigen Index.

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100) UNIQUE
);

In diesem Beispiel wird die Spalte email als eindeutiger Index definiert, weil das Schlüsselwort UNIQUE danach hinzugefügt wird.

Beachten Sie, dass nach der Verwendung desUNIQUESchlüsselworts der Indexname automatisch generiert wird und Sie bei Bedarf auch einen Indexnamen angeben können.

Verwenden des ALTER-Befehls zum Hinzufügen und Löschen von Indizes

Es gibt vier Möglichkeiten, Indizes zu einer Datentabelle hinzuzufügen:

  • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):Diese Anweisung fügt einen Primärschlüssel hinzu. Die Werte in der Primärschlüsselspalte müssen eindeutig sein. Die Liste der Primärschlüsselspalten kann eine oder mehrere Spalten umfassen und darf keine NULL-Werte enthalten.
  • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):Die Werte des durch diese Anweisung erstellten Index müssen eindeutig sein (außer NULL, das mehrfach vorkommen darf).
  • ALTER TABLE tbl_name ADD INDEX index_name (column_list):Fügt einen normalen Index hinzu; Indexwerte können mehrfach vorkommen.
  • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):Diese Anweisung legt den Index als FULLTEXT fest, der für die Volltextindizierung verwendet wird.

Das folgende Beispiel fügt einer Tabelle einen Index hinzu.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

Sie können auch die DROP-Klausel im ALTER-Befehl verwenden, um einen Index zu löschen. Versuchen Sie das folgende Beispiel, um einen Index zu löschen:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

Verwenden Sie den ALTER-Befehl zum Hinzufügen und Löschen von Primärschlüsseln

Ein Primärschlüssel wirkt auf eine Spalte (er kann eine einzelne Spalte oder mehrere kombinierte Spalten als Primärschlüssel umfassen). Beim Hinzufügen eines Primärschlüssels müssen Sie sicherstellen, dass der Primärschlüssel standardmäßig nicht NULL ist (NOT NULL). Beispiel:

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

Sie können auch den ALTER-Befehl verwenden, um einen Primärschlüssel zu löschen:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

Beim Löschen eines Primärschlüssels müssen Sie nur PRIMARY KEY angeben, aber beim Löschen eines Index müssen Sie den Indexnamen kennen.

Indexinformationen anzeigen

Sie können den BefehlSHOW INDEXverwenden, um die relevanten Indexinformationen in einer Tabelle aufzulisten.

Sie können\Ghinzufügen, um die Ausgabeinformationen zu formatieren.

SHOW INDEXAnweisung:

mysql> SHOW INDEX FROM table_name\G
........
  • SHOW INDEX: Schlüsselwort, das verwendet wird, um Indexinformationen anzuzeigen.
  • FROM table_name: Gibt den Namen der Tabelle an, deren Indexinformationen Sie anzeigen möchten.
  • \G: Formatiert die Ausgabeinformationen.

Nach Ausführung des obigen Befehls werden detaillierte Informationen zu allen Indizes in der angegebenen Tabelle angezeigt, einschließlich des Indexnamens (Key_name), der Indexspalte (Column_name), ob es sich um einen eindeutigen Index handelt (Non_unique), der Sortiermethode (Collation), der Indexkardinalität (Cardinality) und so weiter.

Weitere Erweiterungen