MySQL Index-Tutorial – Erstellen, Hinzufügen und Löschen

Was ist ein Index?

Indizes in MySQL Sortieren Sie Daten in einer organisierten sequentiellen Weise. Sie werden für die Spalte(n) erstellt, die zum Filtern der Daten verwendet werden. Stellen Sie sich einen Index als eine alphabetisch sortierte Liste vor. Es ist einfacher, Namen nachzuschlagen, die in alphabetischer Reihenfolge sortiert wurden, als nach Namen, die nicht sortiert sind.

Die Verwendung eines Index für Tabellen, die häufig aktualisiert werden, kann zu einer schlechten Leistung führen. Das ist weil MySQL erstellt jedes Mal einen neuen Indexblock, wenn Daten zur Tabelle hinzugefügt oder aktualisiert werden. Im Allgemeinen sollten Indizes für Tabellen verwendet werden, deren Daten sich nicht häufig ändern, aber häufig in ausgewählten Suchanfragen verwendet werden.

Wozu dient ein Index?

Niemand mag langsame Systeme. Bei fast allen Datenbanksystemen ist eine hohe Systemleistung von größter Bedeutung. Die meisten Unternehmen investieren viel in Hardware, damit Datenabrufe und -manipulationen schneller erfolgen können. Den Hardware-Investitionen, die ein Unternehmen tätigen kann, sind jedoch Grenzen gesetzt. Die Optimierung Ihrer Datenbank ist eine günstigere und bessere Lösung.

Die langsame Antwortzeit ist normalerweise darauf zurückzuführen, dass die Datensätze zufällig in Datenbanktabellen gespeichert werden. Suchanfragen müssen die gesamten zufällig gespeicherten Datensätze nacheinander durchlaufen, um die gewünschten Daten zu finden. Dies führt zu einer schlechten Leistung der Datenbanken, wenn es darum geht, Daten aus großen Tabellen abzurufen. Daher werden Indizes verwendet, die Daten sortieren, um die Suche zu erleichtern.

Syntax: Index erstellen

Indizes können auf zwei Arten definiert werden

1. Zum Zeitpunkt der Tabellenerstellung
2. Nachdem die Tabelle erstellt wurde

Ejemplo:

Für unsere myflixdb erwarten wir viele Suchanfragen in der Datenbank nach dem vollständigen Namen.

Wir werden die Spalte „full_names“ zum Index in einer neuen Tabelle „members_indexed“ hinzufügen.

Das unten gezeigte Skript hilft uns dabei.

CREATE TABLE `members_indexed` (
  `membership_number` int(11) NOT NULL AUTO_INCREMENT,
  `full_names` varchar(150) DEFAULT NULL,
  `gender` varchar(6) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `physical_address` varchar(255) DEFAULT NULL,
  `postal_address` varchar(255) DEFAULT NULL,
  `contact_number` varchar(75) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`membership_number`),INDEX(full_names)
) ENGINE=InnoDB;

Führen Sie das obige SQL-Skript aus MySQL Workbench gegen die „myflixdb“.

Beim Aktualisieren der myflixdb wird die neu erstellte Tabelle mit dem Namen „members_indexed“ angezeigt.

"Hinweis" Die Mitgliedertabelle „members_indexed“ enthält „full_names“ im Indexknoten.

Wenn die Mitgliederbasis wächst und die Anzahl der Datensätze zunimmt, werden Suchanfragen in der Tabelle „members_indexed“, die die Klauseln WHERE und ORDER BY verwenden, viel schneller sein als Suchanfragen, die in der Mitgliedertabelle ohne definierten Index durchgeführt werden.

Fügen Sie die grundlegende Indexsyntax hinzu

Im obigen Beispiel wurde der Index beim Definieren der Datenbanktabelle erstellt. Angenommen, wir haben bereits eine Tabelle definiert und Suchanfragen darauf sind sehr langsam. Es dauert zu lange, bis die Ergebnisse zurückgegeben werden. Nachdem wir das Problem untersucht haben, stellen wir fest, dass wir die Systemleistung erheblich verbessern können, indem wir INDEX für die am häufigsten verwendete Spalte in der WHERE-Klausel erstellen.

Wir können die folgende Abfrage verwenden, um einen Index hinzuzufügen

CREATE INDEX id_index ON table_name(column_name);

Nehmen wir an, dass Suchanfragen für die Tabelle „Filme“ sehr langsam sind und wir einen Index für den „Filmtitel“ verwenden möchten, um die Abfragen zu beschleunigen. Dazu können wir das folgende Skript verwenden.

CREATE INDEX `title_index` ON `movies`(`title`);

Durch Ausführen der obigen Abfrage wird ein Index für das Titelfeld in der Filmtabelle erstellt.

Dies bedeutet, dass alle Suchanfragen in der Filmtabelle, die den „Titel“ verwenden, schneller erfolgen.

Suchanfragen zu anderen Feldern in der Filmtabelle sind jedoch immer noch langsamer als solche, die auf dem indizierten Feld basieren.

Hinweis: Abhängig von den Feldern, die Sie für Ihre Datenbanksuchmaschine verwenden möchten, können Sie bei Bedarf Indizes für mehrere Spalten erstellen.

Wenn Sie die für eine bestimmte Tabelle definierten Indizes anzeigen möchten, können Sie dazu das folgende Skript verwenden.

SHOW INDEXES FROM table_name;

Werfen wir nun einen Blick auf alle Indizes, die in der Tabelle „movies“ in der myflixdb definiert sind.

SHOW INDEXES FROM `movies`;

Ausführen des obigen Skripts in MySQL Werkbank gegen die myflixdb gibt uns Ergebnisse zum erstellten Index.

Hinweis: Die Primär- und Fremdschlüssel der Tabelle wurden bereits indiziert von MySQL. Jeder Index hat seinen eigenen eindeutigen Namen und die Spalte, für die er definiert ist, wird ebenfalls angezeigt.

Syntax: Index löschen

Der Drop-Befehl wird verwendet, um bereits definierte Indizes für eine Tabelle zu entfernen.

Es kann vorkommen, dass Sie bereits einen Index für eine Tabelle definiert haben, der häufig aktualisiert wird. Möglicherweise möchten Sie die Indizes für eine solche Tabelle entfernen, um die Leistung der UPDATE- und INSERT-Abfragen zu verbessern. Die grundlegende Syntax zum Löschen eines Index für eine Tabelle lautet wie folgt.

DROP INDEX `index_id` ON `table_name`;

Schauen wir uns nun ein praktisches Beispiel an.

DROP INDEX ` full_names` ON `members_indexed`;

Durch Ausführen des obigen Befehls wird der Index mit der ID „full_names“ aus der Tabelle „members_indexed“ gelöscht.

Zusammenfassung

• Indizes sind sehr leistungsfähig, wenn es darum geht, die Leistung von erheblich zu verbessern MySQL Suchanfragen.
• Indizes können beim Erstellen einer Tabelle definiert oder später hinzugefügt werden, nachdem die Tabelle bereits erstellt wurde.
• Sie können Indizes für mehr als eine Spalte einer Tabelle definieren.
• Mit SHOW INDEX FROM table_name werden die definierten Indizes für eine Tabelle angezeigt.
• Der DROP-Befehl wird verwendet, um einen definierten Index für eine bestimmte Tabelle zu entfernen.