Indexe in MySQL optimal nutzen
Einleitung
Relationale Datenbanken können zur Arbeit mit Daten beliebiger Größe verwendet werden, einschließlich großer Datenbanken mit Millionen von Zeilen. Die Structured Query Language (SQL) bietet eine präzise und einfache Möglichkeit, bestimmte Zeilen in Datenbanktabellen anhand spezifischer Kriterien zu finden. Je größer die Datenbanken werden, desto schwieriger wird es, bestimmte Zeilen in ihnen zu finden – vergleichbar mit der Suche nach einer Nadel im Heuhaufen.
Da Datenbanken eine Vielzahl von Abfragebedingungen akzeptieren können, ist es für die Datenbank-Engine schwierig, vorherzusehen, welche Abfragen am häufigsten vorkommen werden. Die Engine muss in der Lage sein, Zeilen in Datenbanktabellen effizient zu lokalisieren, unabhängig von deren Größe. Mit wachsender Datenmenge kann jedoch die Suchleistung leiden. Je größer der Datensatz, desto schwieriger ist es für die Datenbank-Engine, die Dokumente, die zur Abfrage passen, schnell zu finden.
Datenbankadministratoren können Indexe verwenden, um die Datenbank-Engine zu unterstützen und deren Leistung zu verbessern.
In diesem Tutorial erfahren Sie, was Indexe sind, wie man sie erstellt und ob sie zur Abfrage der Datenbank verwendet werden.
Voraussetzungen für Indexe in MySQL
Um dieser Anleitung zu folgen, benötigen Sie einen Computer mit einem SQL-basierten relationalen Datenbankverwaltungssystem (RDBMS). Die Anweisungen und Beispiele in dieser Anleitung wurden mit der folgenden Umgebung validiert:
- Ein Server mit Ubuntu 20.04, einem nicht-root Benutzer mit administrativen Rechten und einer mit UFW konfigurierten Firewall, wie in unserer Initialserver-Setup-Anleitung für Ubuntu 20.04 beschrieben.
- MySQL, installiert und gesichert auf dem Server, wie im How To Install MySQL on Ubuntu 20.04 beschrieben. Diese Anleitung wurde mit einem nicht-root MySQL-Benutzer verifiziert, der mit dem in Schritt 3 beschriebenen Prozess erstellt wurde.
- Grundlegende Vertrautheit mit der Ausführung von SELECT-Abfragen, um Daten aus der Datenbank abzurufen, wie in unserer Anleitung How To SELECT Rows FROM Tables in SQL beschrieben.
Hinweis: Viele RDBMS verwenden ihre eigenen einzigartigen SQL-Implementierungen. Obwohl die in diesem Tutorial beschriebenen Befehle in den meisten RDBMS funktionieren, sind Indexe kein Teil der Standard-SQL-Syntax. Daher können die genaue Syntax oder die Ausgabe abweichen, wenn Sie sie auf einem anderen System als MySQL testen.
Sie benötigen außerdem eine Datenbank mit einigen Tabellen, die mit Beispieldaten geladen sind, damit Sie die Verwendung von Indexen üben können. Wir empfehlen Ihnen, den folgenden Abschnitt zur Verbindung mit MySQL und zum Einrichten einer Beispieldatenbank zu lesen, um sich mit der Verbindung zu einem MySQL-Server und dem Erstellen der Testdatenbank vertraut zu machen, die in den Beispielen in dieser Anleitung verwendet wird.
Verbindung zu MySQL herstellen und eine Beispieldatenbank einrichten
In diesem Abschnitt verbinden Sie sich mit einem MySQL-Server und erstellen eine Beispieldatenbank, damit Sie den Beispielen in dieser Anleitung folgen können.
Wenn Ihr SQL-Datenbanksystem auf einem Remote-Server läuft, melden Sie sich per SSH von Ihrem lokalen Rechner aus auf Ihrem Server an:
ssh sammy@your_server_ip
Öffnen Sie dann die MySQL-Server-Eingabeaufforderung und ersetzen Sie „sammy“ durch den Namen Ihres MySQL-Benutzerkontos:
mysql -u sammy -p
Erstellen Sie eine Datenbank namens „indexes“:
CREATE DATABASE indexes;
Wenn die Datenbank erfolgreich erstellt wurde, erhalten Sie eine Ausgabe wie diese:
Ausgabe
Query OK, 1 row affected (0.01 sec)
Um die Datenbank „indexes“ auszuwählen, führen Sie die folgende USE-Anweisung aus:
USE indexes;
Sie erhalten die folgende Ausgabe:
Ausgabe
Database changed
Nachdem Sie die Datenbank ausgewählt haben, können Sie eine Beispieltabelle darin erstellen. In dieser Anleitung verwenden Sie eine imaginäre Mitarbeiterdatenbank, um Informationen über aktuelle Mitarbeiter und deren Arbeitsgeräte zu speichern.
Die Tabelle „employees“ wird vereinfachte Daten über Mitarbeiter in der Datenbank enthalten. Sie wird die folgenden Spalten enthalten:
- employee_id: Diese Spalte enthält die Mitarbeiter-ID, dargestellt durch den Datentyp „int“. Diese Spalte wird zum Primärschlüssel der Tabelle und jeder Wert wird zu einer eindeutigen Kennung für die jeweilige Zeile.
- first_name: Diese Spalte enthält den Vornamen jedes Mitarbeiters, dargestellt durch den Datentyp „varchar“ mit maximal 50 Zeichen.
- last_name: Diese Spalte enthält den Nachnamen jedes Mitarbeiters, dargestellt durch den Datentyp „varchar“ mit maximal 50 Zeichen.
- device_serial: Diese Spalte enthält die Seriennummer des dem Mitarbeiter zugewiesenen Computers, dargestellt durch den Datentyp „varchar“ mit maximal 15 Zeichen.
- salary: Diese Spalte enthält das Gehalt jedes Mitarbeiters, dargestellt durch den Datentyp „int“, der numerische Daten speichert.
Erstellen Sie die Beispieltabelle mit dem folgenden Befehl:
CREATE TABLE employees (
employee_id int,
first_name varchar(50),
last_name varchar(50),
device_serial varchar(15),
salary int
);Wenn die folgende Ausgabe gedruckt wird, wurde die Tabelle erstellt:
Ausgabe
Query OK, 0 rows affected (0.00 sec)
Laden Sie anschließend die Tabelle „employees“ mit einigen Beispieldaten, indem Sie die folgende INSERT INTO-Operation ausführen:
INSERT INTO employees VALUES
(1, 'John', 'Smith', 'ABC123', 60000),
(2, 'Jane', 'Doe', 'DEF456', 65000),
(3, 'Bob', 'Johnson', 'GHI789', 70000),
(4, 'Sally', 'Fields', 'JKL012', 75000),
(5, 'Michael', 'Smith', 'MNO345', 80000),
(6, 'Emily', 'Jones', 'PQR678', 85000),
(7, 'David', 'Williams', 'STU901', 90000),
(8, 'Sarah', 'Johnson', 'VWX234', 95000),
(9, 'James', 'Brown', 'YZA567', 100000),
(10, 'Emma', 'Miller', 'BCD890', 105000),
(11, 'William', 'Davis', 'EFG123', 110000),
(12, 'Olivia', 'Garcia', 'HIJ456', 115000),
(13, 'Christopher', 'Rodriguez', 'KLM789', 120000),
(14, 'Isabella', 'Wilson', 'NOP012', 125000),
(15, 'Matthew', 'Martinez', 'QRS345', 130000),
(16, 'Sophia', 'Anderson', 'TUV678', 135000),
(17, 'Daniel', 'Smith', 'WXY901', 140000),
(18, 'Mia', 'Thomas', 'ZAB234', 145000),
(19, 'Joseph', 'Hernandez', 'CDE567', 150000),
(20, 'Abigail', 'Smith', 'FGH890', 155000);
Die Datenbank wird die Erfolgsmeldung anzeigen:
Ausgabe
Query OK, 20 rows affected (0.010 sec)
Records: 20 Duplicates: 0 Warnings: 0
Hinweis: Der Datensatz ist nicht groß genug, um den Leistungseffekt von Indexen direkt zu veranschaulichen. Dieser Datensatz zeigt jedoch, wie MySQL die Indexe verwendet, um die Anzahl der durchsuchten Zeilen zu begrenzen, um Abfragen durchzuführen und Ergebnisse zu erhalten.
Damit sind Sie bereit, dem Rest der Anleitung zu folgen und mit der Verwendung von Indexen in MySQL zu beginnen.
Einführung in Indexe in MySQL
Normalerweise muss die MySQL-Datenbank, wenn Sie eine Abfrage gegen die Datenbank ausführen, alle Zeilen in der Tabelle einzeln durchgehen. Beispielsweise möchten Sie möglicherweise nach Mitarbeiter-Nachnamen suchen, die mit „Smith“ übereinstimmen, oder nach allen Mitarbeitern mit einem Gehalt über 100.000 USD. Jede Zeile in der Tabelle wird einzeln überprüft, ob sie der Bedingung entspricht. Wenn dies der Fall ist, wird sie zur Liste der zurückgegebenen Zeilen hinzugefügt. Wenn nicht, durchsucht MySQL die nachfolgenden Zeilen, bis die gesamte Tabelle durchsucht ist.
Obwohl diese Methode zur Suche nach übereinstimmenden Zeilen effektiv ist, kann sie bei zunehmender Tabellengröße langsam und ressourcenintensiv werden. Diese Methode eignet sich möglicherweise nicht für große Tabellen oder Abfragen, die häufigen oder schnellen Datenzugriff erfordern.
Um die Leistungsprobleme bei großen Tabellen und Abfragen zu lösen, können Sie Indexe verwenden. Indexe sind einzigartige Datenstrukturen, die nur einen sortierten Teil der Daten getrennt von den Tabellenzeilen speichern. Sie ermöglichen es der Datenbank-Engine, schneller und effizienter zu arbeiten, wenn es darum geht, Werte zu suchen oder nach einem bestimmten Feld oder einer bestimmten Gruppe von Feldern zu sortieren.
Am Beispiel der „employees“-Tabelle ist eine typische Abfrage, die Sie ausführen könnten, die Suche nach Mitarbeitern anhand ihres Nachnamens. Ohne Indexe würde MySQL jeden Mitarbeiter aus der Tabelle abrufen und prüfen, ob der Nachname mit der Abfrage übereinstimmt. Mit einem Index hält MySQL jedoch eine separate Liste der Nachnamen, die nur Zeiger auf Zeilen der jeweiligen Mitarbeiter in der Haupttabelle enthält. Es verwendet dann diesen Index, um die Ergebnisse abzurufen, ohne die gesamte Tabelle durchsuchen zu müssen.
Indexe können als Analogie zu einem Telefonbuch betrachtet werden. Um eine Person namens John Smith im Telefonbuch zu finden, blättern Sie zunächst zu der Seite, auf der Personen mit Nachnamen, die mit „S“ beginnen, aufgeführt sind, und suchen dann auf den Seiten nach Personen mit Nachnamen, die mit „Sm“ beginnen. Indem Sie dieser Logik folgen, können Sie schnell viele Einträge ausschließen, von denen Sie wissen, dass sie nicht der gesuchten Person entsprechen. Dieser Prozess funktioniert nur, weil die Daten im Telefonbuch alphabetisch sortiert sind, was bei direkt in der Datenbank gespeicherten Daten selten der Fall ist. Ein Index in der Datenbank-Engine erfüllt eine ähnliche Funktion wie ein Telefonbuch, da er alphabetisch geordnete Referenzen auf die Daten enthält und somit der Datenbank hilft, die erforderlichen Zeilen schnell zu finden.
Die Verwendung von Indexen in MySQL bietet mehrere Vorteile. Die häufigsten sind die Beschleunigung von WHERE-Bedingungsabfragen (mit genauen Übereinstimmungsbedingungen und Vergleichen), das schnellere Sortieren von Daten mit ORDER BY-Klauseln und die Erzwingung der Eindeutigkeit von Werten.
Die Verwendung von Indexen kann jedoch die Spitzenleistung der Datenbank in einigen Fällen beeinträchtigen. Indexe sollen die Datenabrufgeschwindigkeit erhöhen und werden mithilfe zusätzlicher Datenstrukturen implementiert, die neben den Tabellendaten gespeichert werden. Diese Strukturen müssen bei jeder Änderung in der Datenbank auf dem neuesten Stand gehalten werden, was die Leistung von INSERT-, UPDATE- und DELETE-Abfragen verlangsamen kann. Bei großen Datensätzen, die sich häufig ändern, können die Vorteile der erhöhten Geschwindigkeit von SELECT-Abfragen manchmal durch die spürbar langsamere Leistung von Abfragen, die Daten in die Datenbank schreiben, übertroffen werden.
Es wird empfohlen, Indexe nur dann zu erstellen, wenn ein klarer Bedarf besteht, beispielsweise wenn die Leistung einer Anwendung nachlässt. Bei der Auswahl der zu erstellenden Indexe sollten Sie die Abfragen berücksichtigen, die am häufigsten ausgeführt werden und die längste Zeit in Anspruch nehmen, und Indexe basierend auf den Abfragebedingungen erstellen, die den größten Nutzen daraus ziehen.
Hinweis: Diese Anleitung soll eine Einführung in das Thema Datenbank-Indexe in MySQL geben und die gängigen Anwendungen und Index-Typen veranschaulichen. Die Datenbank-Engine unterstützt viele komplexere Szenarien zur Verwendung von Indexen zur Erhöhung der Datenbankleistung, die jedoch nicht in den Rahmen dieser Anleitung fallen. Wir empfehlen Ihnen, die offizielle MySQL-Dokumentation zu Indexen zu konsultieren, um eine vollständigere Beschreibung der Datenbankfunktionen zu erhalten.
In den folgenden Schritten werden Sie Indexe verschiedener Typen für eine Vielzahl von Szenarien erstellen. Sie erfahren, wie Sie überprüfen können, ob die Indexe in einer Abfrage verwendet werden. Schließlich erfahren Sie, wie Sie Indexe bei Bedarf entfernen können.
Verwendung von Einzelspalten-Indexen in MySQL
Ein Einzelspalten-Index ist der häufigste und einfachste Index-Typ, den Sie zur Optimierung der Abfrageleistung verwenden können. Dieser Index-Typ hilft der Datenbank, Abfragen zu beschleunigen, die den Datensatz basierend auf Werten aus einer einzelnen Spalte filtern. Indexe, die auf einer einzelnen Spalte erstellt wurden, können viele Bedingungsabfragen beschleunigen, einschließlich exakter Übereinstimmungen mit dem Operator „=“ und Vergleiche mit den Operatoren „>“ oder „<“.
In der Beispieldatenbank, die Sie im vorherigen Schritt erstellt haben, gibt es keine Indexe. Bevor Sie einen Index erstellen, testen Sie zunächst, wie die Datenbank SELECT-Abfragen in der „employees“-Tabelle verarbeitet, wenn die WHERE-Klausel verwendet wird, um nur einen Teil der Daten aus der Tabelle anzufordern.
Angenommen, Sie möchten Mitarbeiter mit einem Gehalt von genau 100.000 USD finden. Führen Sie die folgende Abfrage aus:
SELECT * FROM employees WHERE salary = 100000;
Die WHERE-Klausel fordert eine genaue Übereinstimmung von Mitarbeitern mit dem Gehalt an, das dem angeforderten Wert entspricht. In diesem Beispiel antwortet die Datenbank wie folgt:
Ausgabe
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
| 9 | James | Brown | YZA567 | 100000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)
Hinweis: Wie in der obigen Ausgabe gezeigt, hat die Datenbank fast sofort auf die Abfrage reagiert. Da die Datenbank nur eine Handvoll Beispielzeilen enthält, wirkt sich die Verwendung von Indexen nicht sichtbar auf die Abfrageleistung aus. Bei großen Datensätzen werden Sie jedoch deutliche Änderungen an der Abfrageausführungszeit beobachten, die von der Datenbank nach der Ausführung der Abfrage gemeldet wird.
Aus der Abfrageausgabe können Sie nicht erkennen, wie die Datenbank-Engine das Problem gelöst hat, die übereinstimmenden Zeilen in der Tabelle zu finden. MySQL bietet jedoch eine Möglichkeit, Einblick in den Abfrageplan zu erhalten, d. h. in die Art und Weise, wie die Engine die Abfrage ausführt: EXPLAIN-Anweisungen.
Um auf den Abfrageplan für die SELECT-Abfrage zuzugreifen, führen Sie Folgendes aus:
EXPLAIN SELECT * FROM employees WHERE salary = 100000;
Der Befehl EXPLAIN weist MySQL an, die SELECT-Abfrage auszuführen, aber anstatt die Ergebnisse zurückzugeben, werden Informationen darüber angezeigt, wie die Datenbank-Engine die Abfrage intern ausgeführt hat.
Der Ausführungsplan wird in etwa wie folgt aussehen (Ihre Tabelle kann leicht abweichen):
Ausgabe
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
In dieser Tabelle beschreiben die Spalten viele Aspekte der Abfrageausführung. Abhängig von Ihrer MySQL-Version kann Ihre Ausgabe zusätzliche Spalten enthalten, aber für dieses Tutorial sind dies die wichtigsten Informationen:
- possible_keys listet die Indexe auf, die MySQL für die Verwendung in Betracht gezogen hat. In diesem Fall gibt es keine (NULL).
- key beschreibt den Index, den MySQL bei der Ausführung der Abfrage verwendet hat. In diesem Fall wurde kein Index verwendet (NULL).
- rows zeigt die Anzahl der Zeilen, die MySQL einzeln analysieren musste, bevor die Ergebnisse zurückgegeben wurden. Hier sind es 20, was der Anzahl aller möglichen Zeilen in der Tabelle entspricht. Das bedeutet, dass MySQL jede Zeile in der „employees“-Tabelle durchsuchen musste, um die eine zurückgegebene Zeile zu finden.
- Extra zeigt zusätzliche, beschreibende Informationen zum Abfrageplan an. In diesem Beispiel bedeutet die Anmerkung „Using where“, dass die Datenbank die Ergebnisse direkt innerhalb der Tabelle mithilfe der WHERE-Anweisung gefiltert hat.
Da keine Indexe vorhanden sind, musste die Datenbank 20 Zeilen durchsuchen, um eine einzige Zeile abzurufen. Wenn die Tabelle Millionen von Zeilen enthielte, müsste MySQL diese eine nach der anderen durchgehen, was zu einer schlechten Abfrageleistung führen würde.
Hinweis: Neuere MySQL-Versionen zeigen beim Verwenden von EXPLAIN 1 row in set, 1 warning in der Ausgabe an, während ältere MySQL-Versionen und MySQL-kompatible Datenbanken stattdessen oft nur 1 row in set anzeigen. Die Warnung ist kein Zeichen für ein Problem. MySQL verwendet seinen Warnmechanismus, um erweiterte Informationen zum Abfrageplan bereitzustellen. Die Verwendung dieser zusätzlichen Informationen fällt nicht in den Umfang dieses Tutorials. Sie können mehr darüber im Abschnitt „Extended EXPLAIN Output Format“ der MySQL-Dokumentation erfahren.
Die gerade ausgeführte SELECT-Abfrage verwendete die exakte Abfragebedingung WHERE salary = 100000. Als nächstes überprüfen wir, ob die Datenbank bei einer Vergleichsbedingung ähnlich reagiert. Versuchen Sie, Mitarbeiter mit einem Gehalt unter 70.000 abzurufen:
SELECT * FROM employees WHERE salary < 70000;
Diesmal hat die Datenbank zwei Zeilen für John Smith und Jane Doe zurückgegeben:
Ausgabe
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
| 1 | John | Smith | ABC123 | 60000 |
| 2 | Jane | Doe | DEF456 | 65000 |
+-------------+------------+-----------+---------------+--------+
8 rows in set (0.000 sec)
Wenn Sie jedoch EXPLAIN verwenden, um die Abfrageausführung wie folgt zu verstehen:
EXPLAIN SELECT * FROM employees WHERE salary < 70000;
werden Sie feststellen, dass die Tabelle fast identisch mit der vorherigen Abfrage ist:
Ausgabe
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 33.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Wie bei der vorherigen Abfrage hat MySQL alle 20 Zeilen in der Tabelle durchsucht, um die von Ihnen über die WHERE-Klausel der Abfrage angeforderten Zeilen zu finden. Obwohl die Anzahl der zurückgegebenen Zeilen im Vergleich zur Anzahl aller Zeilen in der Tabelle klein ist, muss die Datenbank-Engine viel Arbeit leisten, um sie zu finden.
Um dies zu beheben, können Sie einen Index für die Spalte „salary“ erstellen, wodurch MySQL eine zusätzliche, stark optimierte Datenstruktur insbesondere für die Gehaltsdaten der Tabelle „employees“ pflegt. Führen Sie dazu die folgende Abfrage aus:
CREATE INDEX salary ON employees(salary);
Die Syntax der CREATE INDEX-Anweisung erfordert:
- Der Indexname, in diesem Fall salary. Der Name muss innerhalb einer einzelnen Tabelle eindeutig sein, kann jedoch in verschiedenen Tabellen derselben Datenbank wiederholt werden.
- Der Tabellenname, für den der Index erstellt wird. In diesem Fall ist es employees.
- Die Liste der Spalten, für die der Index erstellt wird. Hier verwenden Sie eine einzelne Spalte namens salary, um den Index zu erstellen.
Hinweis: Abhängig von den Berechtigungen Ihres MySQL-Benutzers kann beim Ausführen des CREATE INDEX-Befehls ein Fehler auftreten: ERROR 1142 (42000): INDEX command denied to user ‚user’@’host‘ for table ‚employees‘. Um Ihrem Benutzer INDEX-Berechtigungen zu erteilen, melden Sie sich als root in MySQL an und führen Sie die folgenden Befehle aus, wobei Sie den MySQL-Benutzernamen und -Host nach Bedarf anpassen:
GRANT INDEX on *.* TO 'sammy'@'localhost';
FLUSH PRIVILEGES;
Nach der Aktualisierung der Benutzerberechtigungen melden Sie sich als root ab und als Benutzer wieder an und führen Sie die CREATE INDEX-Anweisung erneut aus.
Die Datenbank wird bestätigen, dass der Index erfolgreich erstellt wurde:
Ausgabe
Query OK, 0 rows affected (0.024 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mit dem Index können Sie die vorherigen Abfragen wiederholen, um zu überprüfen, ob sich etwas geändert hat. Beginnen Sie mit dem Abrufen des einzelnen Mitarbeiters mit einem Gehalt von genau 100.000:
SELECT * FROM employees WHERE salary = 100000;
Das Ergebnis wird das gleiche wie zuvor sein, nur James Brown wird zurückgegeben:
Ausgabe
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
| 9 | James | Brown | YZA567 | 100000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)
Wenn Sie jedoch MySQL auffordern zu erklären, wie es die Abfrage verarbeitet hat, werden einige Unterschiede zu früher sichtbar. Führen Sie die EXPLAIN-Abfrage wie folgt aus:
EXPLAIN SELECT * FROM employees WHERE salary = 100000;
Diesmal wird die Ausgabe wie folgt angezeigt:
Ausgabe
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | employees | NULL | ref | salary | salary | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
MySQL gibt an, dass es aus dem einen möglichen Schlüssel, der in „possible_keys“ angezeigt wird, den als „salary“ bezeichneten Schlüssel verwendet hat, den Sie erstellt haben. Die Spalte „rows“ zeigt jetzt 1 statt 20 an. Da es den Index verwendet hat, musste die Datenbank nicht alle Zeilen durchsuchen und konnte die angeforderte Zeile sofort zurückgeben. Die Spalte „Extra“ erwähnt jetzt „Using WHERE“ nicht mehr, da es nicht erforderlich war, die Haupttabelle zu durchlaufen und jede Zeile gegen die Abfragebedingung zu prüfen, um die Abfrage auszuführen.
Bei einem kleinen Beispieldatensatz ist die Wirkung der Verwendung des Indexes nicht sehr spürbar. Die Datenbank musste jedoch deutlich weniger arbeiten, um das Ergebnis abzurufen, und bei einem größeren Datensatz wäre der Effekt dieser Änderung erheblich.
Versuchen Sie, die zweite Abfrage zu wiederholen, um Mitarbeiter mit einem Gehalt unter 70.000 abzurufen, um zu überprüfen, ob der Index dort ebenfalls verwendet wird.
Führen Sie die folgende Abfrage aus:
SELECT * FROM employees WHERE salary < 70000;
Die gleichen beiden Zeilen für John Smith und Jane Doe werden zurückgegeben:
Ausgabe
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
| 1 | John | Smith | ABC123 | 60000 |
| 2 | Jane | Doe | DEF456 | 65000 |
+-------------+------------+-----------+---------------+--------+
8 rows in set (0.000 sec)
Wenn Sie jedoch EXPLAIN wie folgt verwenden:
EXPLAIN SELECT * FROM employees WHERE salary < 70000;
wird die Tabelle von der vorherigen Ausführung derselben Abfrage abweichen:
Ausgabe
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | range | salary | salary | 5 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
Die Spalte „key“ zeigt an, dass MySQL den Index verwendet hat, um die Abfrage auszuführen. In der Spalte „rows“ wurden nur zwei Zeilen analysiert, um das Ergebnis zurückzugeben. Diesmal steht in der Spalte „Extra“ „Using index condition“, was bedeutet, dass MySQL in diesem speziellen Fall durch den Index gefiltert und dann die Haupttabelle nur zum Abrufen der bereits übereinstimmenden Zeilen verwendet hat.
Hinweis: Manchmal wird MySQL trotz vorhandenen Indexes entscheiden, ihn nicht zu verwenden. Beispiel: Wenn Sie ausführen:
EXPLAIN SELECT * FROM employees WHERE salary < 140000;
Der Ausführungsplan wird wie folgt angezeigt:
Ausgabe
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | salary | NULL | NULL | NULL | 20 | 80.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Obwohl „salary“ in „possible_keys“ aufgeführt ist, bedeutet die leere Spalte „key“ (NULL), dass MySQL sich entschied, den Index nicht zu verwenden, was durch die 20 gescannten Zeilen bestätigt werden kann. Der Abfrageplaner der Datenbank analysiert jede Abfrage gegen mögliche Indexe, um den schnellsten Ausführungspfad zu bestimmen. Wenn die Kosten für den Zugriff auf den Index die Vorteile der Verwendung überwiegen (z. B. wenn die Abfrage einen erheblichen Teil der Originaltabellendaten zurückgibt), kann die Datenbank entscheiden, dass es tatsächlich schneller ist, einen vollständigen Tabellenscan durchzuführen.
Ähnlich beschreiben die Anmerkungen in der Spalte „Extra“, wie die Datenbank-Engine die Abfrage im Detail ausgeführt hat, wie etwa „Using index condition“ oder „Using where“. Abhängig vom Kontext kann die Datenbank eine andere Methode zur Ausführung der Abfrage wählen, und Sie können Ausgaben ohne „Using index condition“-Anmerkung oder mit einer anderen Anmerkung erhalten. Dies bedeutet nicht, dass der Index nicht richtig verwendet wird, sondern lediglich, dass die Datenbank entschieden hat, dass eine andere Methode zur Zugriffszeilen performanter sein wird.
In diesem Abschnitt haben Sie Einzelspalten-Indexe erstellt und verwendet, um die Leistung von SELECT-Abfragen zu verbessern, die auf die Filterung gegen eine einzelne Spalte angewiesen sind. Im nächsten Abschnitt werden Sie erkunden, wie Indexe verwendet werden können, um die Einzigartigkeit der Werte in einer bestimmten Spalte zu garantieren.
Verwendung eindeutiger Indexe in MySQL zur Vermeidung von Daten-Duplikaten
Wie Sie im letzten Abschnitt erfahren haben, ist eine häufige Verwendung von Indexen, Daten effizienter abzurufen, indem die Datenbank-Engine bei derselben Abfrage weniger Arbeit leisten muss. Ein anderer Zweck ist sicherzustellen, dass die Daten in dem Teil der Tabelle, auf den sich der Index bezieht, nicht wiederholt werden. Dies ist die Aufgabe eines eindeutigen Indexes.
Das Vermeiden doppelter Werte ist oft notwendig, um die Datenintegrität entweder aus logischer oder technischer Sicht zu gewährleisten. Beispielsweise sollten nicht zwei verschiedene Personen dieselbe Sozialversicherungsnummer verwenden, oder ein Online-System sollte nicht mehreren Benutzern mit demselben Benutzernamen oder derselben E-Mail-Adresse die Registrierung erlauben.
Im Beispiel der Tabelle „employees“ in diesem Leitfaden ist die Seriennummer des zugewiesenen Geräts ein Feld, das keine Duplikate enthalten sollte. Wenn dies der Fall wäre, würde dies bedeuten, dass zwei Mitarbeiter denselben Computer erhalten haben. Zu diesem Zeitpunkt könnten Sie jedoch problemlos neue Mitarbeiter mit wiederholten Seriennummern einfügen.
Versuchen Sie, einen weiteren Mitarbeiter mit einer bereits verwendeten Geräte-Seriennummer einzufügen:
INSERT INTO employees VALUES (21, 'Sammy', 'Smith', 'ABC123', 65000);
Die Datenbank wird das Einfügen der Zeile zulassen und den Erfolg melden:
Ausgabe
Query OK, 1 row affected (0.009 sec)
Wenn Sie jetzt jedoch die Datenbank nach Mitarbeitern abfragen, die den Computer „ABC123“ verwenden, wie folgt:
SELECT * FROM employees WHERE device_serial = 'ABC123';
erhalten Sie zwei verschiedene Personen als Ergebnis:
Ausgabe
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
| 1 | John | Smith | ABC123 | 60000 |
| 21 | Sammy | Smith | ABC123 | 65000 |
+-------------+------------+-----------+---------------+--------+
2 rows in set (0.000 sec)
Dies ist kein erwartetes Verhalten, um die Mitarbeiterdatenbank gültig zu halten. Lassen Sie uns diese Änderung rückgängig machen, indem wir die neu erstellte Zeile löschen:
DELETE FROM employees WHERE employee_id = 21;
Sie können dies bestätigen, indem Sie die vorherige SELECT-Abfrage erneut ausführen:
SELECT * FROM employees WHERE device_serial = 'ABC123';
Nur John Smith verwendet das Gerät mit der Seriennummer „ABC123“:
Ausgabe
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
| 1 | John | Smith | ABC123 | 60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)
Um die Datenbank vor solchen Fehlern zu schützen, können Sie einen eindeutigen Index auf die Spalte „device_serial“ erstellen.
Führen Sie dazu Folgendes aus:
CREATE UNIQUE INDEX device_serial ON employees(device_serial);
Das Hinzufügen des Schlüsselworts UNIQUE bei der Erstellung des Indexes weist die Datenbank an, sicherzustellen, dass sich die Werte in der Spalte „device_serial“ nicht wiederholen. Bei eindeutigen Indexen werden alle neuen Zeilen, die der Tabelle hinzugefügt werden, gegen den Index überprüft, um zu bestimmen, ob der Spaltenwert die Einschränkung erfüllt.
Die Datenbank wird die Erstellung des Indexes bestätigen:
Ausgabe
Query OK, 0 rows affected (0.021 sec)
Records: 0 Duplicates: 0 Warnings: 0
Überprüfen Sie nun, ob das Hinzufügen eines doppelten Eintrags zur Tabelle weiterhin möglich ist. Versuchen Sie, die zuvor erfolgreiche INSERT-Abfrage erneut auszuführen:
INSERT INTO employees VALUES (21, 'Sammy', 'Smith', 'ABC123', 65000);
Diesmal wird die Fehlermeldung angezeigt:
Ausgabe
ERROR 1062 (23000): Duplicate entry 'ABC123' for key 'device_serial'
Sie können überprüfen, dass die neue Zeile nicht zur Tabelle hinzugefügt wurde, indem Sie erneut die SELECT-Abfrage verwenden:
SELECT * FROM employees WHERE device_serial = 'ABC123';
Es wird wieder eine einzige Zeile zurückgegeben:
Ausgabe
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
| 1 | John | Smith | ABC123 | 60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)
Eindeutige Indexe bieten nicht nur Schutz vor doppelten Einträgen, sondern sind auch voll funktionsfähige Indexe zur Beschleunigung von Abfragen. Die Datenbank-Engine wird eindeutige Indexe auf die gleiche Weise wie im vorherigen Schritt verwenden. Sie können dies überprüfen, indem Sie Folgendes ausführen:
EXPLAIN SELECT * FROM employees WHERE device_serial = 'ABC123';
Der Ausführungsplan wird in etwa wie folgt aussehen (Ihre Tabelle kann leicht abweichen):
Ausgabe
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | employees | NULL | const | device_serial | device_serial | 63 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
Der „device_serial“-Index wird sowohl in „possible_keys“ als auch in der „key“-Spalte angezeigt, was bestätigt, dass der Index bei der Ausführung der Abfrage verwendet wurde.
Sie haben eindeutige Indexe verwendet, um doppelte Daten in der Datenbank zu verhindern. Im nächsten Abschnitt werden Sie Indexe verwenden, die sich über mehr als eine Spalte erstrecken.
Verwendung von Indexen auf mehreren Spalten
Bis jetzt wurden alle Indexe, die Sie in den vorherigen Abschnitten erstellt haben, unter Verwendung eines einzelnen Spaltennamens definiert und beziehen sich auf Werte aus der ausgewählten Spalte. Die meisten Datenbanksysteme unterstützen Indexe, die sich über mehrere Spalten erstrecken. Solche Indexe, die als Multi-Column-Indexe bezeichnet werden, bieten eine Möglichkeit, Werte für mehrere Spalten in einem einzigen Index zu speichern, sodass die Datenbank-Engine Abfragen, die die Gruppe von Spalten gemeinsam verwenden, schneller und effizienter ausführen kann.
Häufig verwendete Abfragen, die für die Leistung optimiert werden sollen, verwenden oft mehrere Bedingungen in der WHERE-Filterklausel. Ein Beispiel für diese Art von Abfragen wäre die Aufforderung an die Datenbank, eine Person anhand ihres Vor- und Nachnamens zu finden:
SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';
Der erste Gedanke zur Optimierung dieser Abfrage mit Indexen könnte darin bestehen, zwei einzelne Indexe zu erstellen, einen für die Spalte „last_name“ und einen anderen für die Spalte „first_name“. Dies ist jedoch nicht die beste Wahl für diese Situation.
Wenn Sie auf diese Weise zwei separate Indexe erstellt haben, weiß MySQL, wie man alle Mitarbeiter mit dem Nachnamen „Smith“ findet. Es weiß auch, wie man alle Mitarbeiter mit dem Vornamen „John“ findet. Es wüsste jedoch nicht, wie man Personen namens John Smith findet.
Um das Problem der Verwendung von zwei einzelnen Indexen zu verdeutlichen, stellen Sie sich vor, Sie hätten zwei separate Telefonbücher, eines nach Nachnamen und eines nach Vornamen geordnet. Beide Telefonbücher ähneln den Indexen, die auf den Spalten „last_name“ und „first_name“ erstellt wurden. Als Benutzer des Telefonbuchs könnten Sie das Problem, John Smith zu finden, auf drei mögliche Arten angehen:
- Nutzen Sie das nach Nachnamen geordnete Telefonbuch, um alle Smith-Personen manuell zu durchsuchen und John Smith zu finden.
- Verwenden Sie das nach Vornamen geordnete Telefonbuch, um alle Johns manuell zu durchsuchen und John Smith zu finden.
- Verwenden Sie beide Telefonbücher: Finden Sie alle Johns und Smiths und überschneiden Sie die Ergebnisse manuell.
Keine dieser Ansätze ist ideal, und MySQL hat ähnliche Auswahlmöglichkeiten, wenn es sich mit mehreren unabhängigen Indexen und einer Abfrage befasst, die mehr als eine Filterbedingung verlangt.
Ein weiterer Ansatz wäre die Verwendung von Indexen, die nicht nur eine einzelne Spalte, sondern mehrere Spalten berücksichtigen. Sie können sich dies als ein Telefonbuch vorstellen, das in ein weiteres Telefonbuch eingebettet ist: Zuerst schlagen Sie den Nachnamen „Smith“ nach, was Sie zum zweiten Katalog für alle Personen mit dem Namen „Smith“ führt, die alphabetisch nach Vornamen geordnet sind, was Sie verwenden können, um John schnell zu finden.
Hinweis: Es wird oft gesagt, dass MySQL für jede in einer Abfrage verwendete Tabelle nur einen Index verwenden kann. Dies ist nicht immer wahr, da MySQL Index-Merge-Optimierungen unterstützt, um mehrere Indexe gemeinsam zu verwenden, wenn eine Abfrage ausgeführt wird. Diese Einschränkung ist jedoch eine gute Faustregel beim Erstellen von Indexen. MySQL kann sich entscheiden, mehrere Indexe nicht zu verwenden; selbst wenn es dies tut, dienen sie in vielen Szenarien nicht so gut wie ein dedizierter Index.
Um in MySQL einen Multi-Column-Index für Nachnamen und Vornamen in der „employees“-Tabelle zu erstellen, führen Sie Folgendes aus:
CREATE INDEX names ON employees(last_name, first_name);
In diesem Fall unterscheidet sich die CREATE INDEX-Anweisung geringfügig. Jetzt sind in den Klammern nach dem Tabellennamen (employees) zwei Spalten aufgelistet: last_name und dann first_name. Dadurch wird ein Multi-Column-Index für beide Spalten erstellt. Die Reihenfolge, in der die Spalten in der Index-Definition aufgelistet sind, ist wichtig, wie Sie gleich sehen werden.
Die Datenbank zeigt die folgende Meldung an, die bestätigt, dass der Index erfolgreich erstellt wurde:
Ausgabe
Query OK, 0 rows affected (0.024 sec)
Records: 0 Duplicates: 0 Warnings: 0
Versuchen Sie nun, die SELECT-Abfrage auszuführen, um Zeilen mit dem Vornamen „John“ und dem Nachnamen „Smith“ zu finden:
SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';
Das Ergebnis ist eine einzelne Zeile mit einem Mitarbeiter namens John Smith:
Ausgabe
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
| 1 | John | Smith | ABC123 | 60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)
Verwenden Sie nun die EXPLAIN-Abfrage, um zu überprüfen, ob der Index verwendet wurde:
EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';
Der Ausführungsplan wird in etwa wie folgt aussehen (Ihre Tabelle kann leicht abweichen):
Ausgabe
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | employees | NULL | ref | names | names | 406 | const,const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
Die Datenbank hat den „names“-Index verwendet. Eine einzelne Zeile wurde durchsucht, sodass die Tabelle nicht mehr als nötig durchsucht wurde. Die Spalte „Extra“ sagt „Using index condition“, was bedeutet, dass MySQL die Filterung ausschließlich unter Verwendung des Indexes abschließen konnte.
Das Filtern nach Vor- und Nachnamen mithilfe des Multi-Column-Indexes, der sich über diese beiden Spalten erstreckt, bietet der Datenbank eine direkte, schnelle Möglichkeit, die gewünschten Ergebnisse zu finden.
Mit dem auf beiden Spalten definierten Index: Was passiert, wenn Sie versuchen, alle Mitarbeiter mit dem Nachnamen „Smith“ zu finden, ohne nach dem Vornamen zu filtern? Führen Sie die geänderte Abfrage aus:
SELECT * FROM employees WHERE last_name = 'Smith';
Die Ausgabe gibt Folgendes zurück:
Ausgabe
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
| 20 | Abigail | Smith | FGH890 | 155000 |
| 17 | Daniel | Smith | WXY901 | 140000 |
| 1 | John | Smith | ABC123 | 60000 |
| 5 | Michael | Smith | MNO345 | 80000 |
+-------------+------------+-----------+---------------+--------+
4 rows in set (0.000 sec)
Vier Mitarbeiter tragen den Nachnamen „Smith“.
Zugriff auf den Abfrageausführungsplan:
EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';
Der Ausführungsplan wird in etwa wie folgt aussehen (Ihre Tabelle kann leicht abweichen):
Ausgabe
+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | employees | NULL | ref | names | names | 203 | const | 4 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set (0.01 sec)
Diesmal wurden vier Zeilen zurückgegeben, da mehr als ein Mitarbeiter diesen Nachnamen trägt. Der Ausführungsplan zeigt, dass die Datenbank den Multi-Column-Index „names“ verwendet und nur vier Zeilen durchsucht hat.
In den vorherigen Abfragen war die Spalte, die zum Filtern der Ergebnisse verwendet wurde (last_name), die erste Spalte in der CREATE INDEX-Anweisung. Jetzt filtern Sie die Tabelle „employees“ nach „first_name“, das die zweite Spalte in der Spaltenliste für diesen Multi-Column-Index war. Führen Sie die folgende Abfrage aus:
SELECT * FROM employees WHERE first_name = 'John';
Die Ausgabe wird wie folgt zurückgegeben:
Ausgabe
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
| 1 | John | Smith | ABC123 | 60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)
Zugriff auf den Abfrageausführungsplan:
EXPLAIN SELECT * FROM employees WHERE first_name = 'John';
Die Ausgabe wird wie folgt zurückgegeben:
Ausgabe
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Einmal mehr enthält das Ergebnis nur einen Mitarbeiter, aber diesmal wurde kein Index verwendet. Die Datenbank durchsuchte die gesamte Tabelle. Dies wird durch die Anmerkung „Using where“ in der Spalte „Extra“ sowie durch die 20 gescannten Zeilen veranschaulicht.
In diesem Fall hat die Datenbank den Index nicht verwendet, da die Reihenfolge der Spalten, die in der CREATE INDEX-Anweisung beim ersten Erstellen des Indexes übergeben wurde, last_name, first_name war. Die Datenbank nutzt den Index nur, wenn die Abfrage die erste Spalte oder die ersten beiden Spalten verwendet.
Mit einem auf mehreren Spalten erstellten Index kann die Datenbank den Index verwenden, um Abfragen zu beschleunigen, die alle indizierten Spalten oder ein wachsendes „Left-Hand-Prefix“ aller indizierten Spalten beinhalten. Ein Multi-Column-Index, der die Spalten (a, b, c) enthält, kann beispielsweise verwendet werden, um Abfragen zu beschleunigen, die alle drei Spalten betreffen, und Abfragen, die nur die ersten beiden Spalten betreffen, oder sogar Abfragen, die nur die erste Spalte betreffen. Andererseits hilft der Index nicht bei Abfragen, die nur die letzte Spalte „c“ oder die letzten beiden Spalten „b“ und „c“ betreffen.
Ein sorgfältig gewählter Multi-Column-Index kann verschiedene Abfragen in derselben Tabelle effizient beschleunigen.. Dieses Beispiel zeigt, dass die Spaltenreihenfolge im „names“-Index die Suche nach Vor- und Nachnamen beschleunigt.
In diesem Abschnitt haben Sie Multi-Column-Indexe verwendet und die Spaltenreihenfolge bei der Angabe eines solchen Indexes gelernt. Im nächsten Abschnitt erfahren Sie, wie Sie vorhandene Indexe verwalten.
Auflisten und Entfernen vorhandener Indexe
In den vorherigen Abschnitten haben Sie neue Indexe erstellt. Da Indexe Namen haben und auf bestimmte Tabellen beschränkt sind, können Sie sie auch bei Bedarf auflisten und manipulieren.
Um alle in diesem Tutorial für die Tabelle „employees“ erstellten Indexe aufzulisten, führen Sie die folgende Anweisung aus:
SHOW INDEXES FROM employees;
Die Ausgabe wird wie folgt aussehen:
Ausgabe
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employees | 0 | device_serial | 1 | device_serial | A | 20 | NULL | NULL | YES | BTREE | | | YES | NULL |
| employees | 1 | salary | 1 | salary | A | 20 | NULL | NULL | YES | BTREE | | | YES | NULL |
| employees | 1 | names | 1 | last_name | A | 16 | NULL | NULL | YES | BTREE | | | YES | NULL |
| employees | 1 | names | 2 | first_name | A | 20 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)
Abhängig von Ihrer MySQL-Version kann Ihre Ausgabe geringfügig abweichen, aber sie wird alle Indexe enthalten, einschließlich ihrer Namen, der Spalten, die zur Definition des Indexes verwendet werden, Informationen zur Eindeutigkeit und andere umfangreiche Details zur Indexdefinition.
Um vorhandene Indexe zu löschen, können Sie die DROP INDEX-SQL-Anweisung verwenden. Stellen Sie sich vor, Sie möchten die Einzigartigkeit der Spalte „device_serial“ nicht mehr erzwingen. Daher wird der Index „device_serial“ nicht mehr benötigt. Führen Sie den folgenden Befehl aus:
DROP INDEX device_serial ON employees;
„device_serial“ ist der Indexname und „employees“ ist die Tabelle, auf die sich der Index bezieht. Die Datenbank wird das Löschen des Indexes bestätigen:
Ausgabe
Query OK, 0 rows affected (0.018 sec)
Records: 0 Duplicates: 0 Warnings: 0
Manchmal ändern sich die Muster typischer Abfragen im Laufe der Zeit oder neue Abfragetypen werden prominent. Dann müssen Sie möglicherweise die verwendeten Indexe neu bewerten, neue erstellen oder ungenutzte löschen. SO können Sie eine Verschlechterung der Datenbankleistung durch die Aktualisierung der Indexe zu vermeiden.
Mit den Befehlen CREATE INDEX und DROP INDEX können Sie Indexe auf einer bestehenden Datenbank verwalten.
Fazit
Mit dieser Anleitung haben Sie gelernt, was Indexe sind und wie Sie die gebräuchlichsten Typen in MySQL verwenden können.. Sie haben Indexe verwendet, um die Eindeutigkeit von Spaltendaten sicherzustellen, und gelernt, wie Indexe Abfragen beeinflussen können.
Sie können Indexe verwenden, um die Leistung der Datenbank entsprechend den am häufigsten ausgeführten Abfragetypen zu gestalten. Des Weiteren können Sie das richtige Gleichgewicht zwischen Lese- und Schreibleistung für typische Anwendungsfälle zu finden. Dieses Tutorial behandelt nur die Grundlagen der Verwendung von Indexen zu diesem Zweck. Sie können komplexere Abfragen durch Indexe unterstützen, indem Sie verstehen, wie MySQL entscheidet, welche Indexe wann verwendet werden. Weitere Informationen dazu finden Sie in der MySQL-Dokumentation zu Indexen.
Für weitere Konzepte zur SQL-Sprache und Anwendung empfehlen wir die Anleitungen in der Serie ‚How To Use SQL‘.
