Was ist ein Index?
Indizes in MySQL sortieren Daten nacheinander. Sie werden in den Spalten erstellt, die zum Filtern der Daten verwendet werden. Stellen Sie sich einen Index als alphabetisch sortierte Liste vor. Es ist einfacher, Namen zu suchen, die in alphabetischer Reihenfolge sortiert wurden, als solche, die nicht sortiert sind.
Die Verwendung eines Index für Tabellen, die häufig aktualisiert werden, kann zu einer schlechten Leistung führen. Dies liegt daran, dass MySQL jedes Mal, wenn Daten in der Tabelle hinzugefügt oder aktualisiert werden, einen neuen Indexblock erstellt. 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.
Was nützt ein Index?
Niemand mag langsame Systeme. Eine hohe Systemleistung ist in fast allen Datenbanksystemen von größter Bedeutung. Die meisten Unternehmen investieren stark in Hardware, damit Daten schneller abgerufen und bearbeitet werden können. Es gibt jedoch Grenzen für Hardware-Investitionen, die ein Unternehmen tätigen kann. Die Optimierung Ihrer Datenbank ist eine billigere und bessere Lösung.
Die Langsamkeit der Antwortzeit ist normalerweise darauf zurückzuführen, dass die Datensätze zufällig in Datenbanktabellen gespeichert werden. Suchanfragen müssen nacheinander die gesamten zufällig gespeicherten Datensätze durchlaufen, um die gewünschten Daten zu finden. Dies führt zu Datenbanken mit schlechter Leistung, wenn Daten aus großen Tabellen abgerufen werden sollen. Daher werden Indizes verwendet, die Daten sortieren, um die Suche zu vereinfachen.
Syntax: Index erstellen
Indizes können auf zwei Arten definiert werden
- Zum Zeitpunkt der Tabellenerstellung
- Nachdem die Tabelle erstellt wurde
Für unsere myflixdb erwarten wir viele Suchanfragen in der Datenbank mit vollem Namen.
Wir werden die Spalte "full_names" zu 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 in der MySQL-Workbench für die "myflixdb" aus.
Beim Aktualisieren der myflixdb wird die neu erstellte Tabelle mit dem Namen member_indexed angezeigt.
Die Tabelle "Hinweis" "member_indexed" enthält "vollständige_Namen" im Indexknoten.
Wenn die Mitgliederbasis erweitert wird und die Anzahl der Datensätze zunimmt, sind Suchabfragen in der Tabelle "members_indexed", die die Klauseln "WHERE" und "ORDER BY" verwenden, viel schneller als diejenigen, die in der Tabelle "Members" ohne den definierten Index ausgefü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 sind sehr langsam. Sie brauchen zu lange, um die Ergebnisse zurückzugeben. 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 in der Filmtabelle sehr langsam sind und wir einen Index für den "Filmtitel" verwenden möchten, um die Abfragen zu beschleunigen. Wir können das folgende Skript verwenden, um dies zu erreichen.
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 mit dem "Titel" schneller sind.
Suchanfragen in anderen Feldern in der Filmtabelle sind jedoch immer noch langsamer als in Abfragen, 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;
Schauen wir uns nun alle Indizes an, die in der Filmtabelle in der myflixdb definiert sind.
SHOW INDEXES FROM `movies`;
Wenn Sie das obige Skript in der MySQL-Workbench für die myflixdb ausführen, erhalten Sie Ergebnisse zum erstellten Index.
Hinweis: Der Primär- und der Fremdschlüssel in der Tabelle wurden bereits von MySQL indiziert. Jeder Index hat einen eigenen eindeutigen Namen und die Spalte, in der er definiert ist, wird ebenfalls angezeigt.
Syntax: Drop-Index
Mit dem Befehl drop werden bereits definierte Indizes für eine Tabelle entfernt.
Es kann vorkommen, dass Sie bereits einen Index für eine Tabelle definiert haben, die häufig aktualisiert wird. Möglicherweise möchten Sie die Indizes für eine solche Tabelle entfernen, um die Leistung von 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 MySQL-Suchanfragen erheblich zu verbessern.
- 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 in einer Tabelle definieren.
- Der SHOW INDEX FROM Tabellenname wird verwendet, um die definierten Indizes für eine Tabelle anzuzeigen.
- Der Befehl DROP wird verwendet, um einen definierten Index für eine bestimmte Tabelle zu entfernen.