MySQL Indizes und Leistungsoptimierung | Datenbank-Performance steigern

Einführung

In diesem Lab lernen Sie MySQL-Indizes und Techniken zur Leistungsoptimierung kennen. Das Lab konzentriert sich auf die Erstellung und Verwaltung von Indizes zur Verbesserung der Datenbankabfrageleistung.

Sie beginnen mit der Erstellung einer users-Tabelle und dem Einfügen von Beispieldaten. Anschließend erstellen Sie einen Einzelspaltenindex für die Spalte username und lernen, wie Sie dessen Erstellung überprüfen. Das Lab behandelt auch die Analyse von Abfrageplänen mit EXPLAIN, das Hinzufügen von zusammengesetzten Indizes für Abfragen mit mehreren Spalten und das Entfernen ungenutzter Indizes zur Aufrechterhaltung der Datenbankeffizienz.

Erstellen eines Einzelspalten-Index auf einer Tabelle

In diesem Schritt lernen Sie, wie Sie einen Einzelspaltenindex in MySQL erstellen. Indizes sind entscheidend für die Verbesserung der Leistung von Datenbankabfragen, insbesondere bei der Arbeit mit großen Tabellen. Ein Index für eine Spalte ermöglicht es der Datenbank, Zeilen, die einem bestimmten Wert in dieser Spalte entsprechen, schnell zu lokalisieren, ohne die gesamte Tabelle durchsuchen zu müssen.

Verständnis von Indizes

Stellen Sie sich einen Index wie das Stichwortverzeichnis in einem Buch vor. Anstatt das gesamte Buch zu lesen, um ein bestimmtes Thema zu finden, können Sie das Stichwortverzeichnis verwenden, um die relevanten Seiten schnell zu finden. Ebenso hilft ein Datenbankindex der Datenbank-Engine, bestimmte Zeilen schnell zu finden.

Erstellen einer Tabelle

Zuerst erstellen wir eine einfache Tabelle namens users, um die Erstellung eines Index zu demonstrieren. Öffnen Sie ein Terminal in der LabEx VM. Sie können die Verknüpfung Xfce Terminal auf dem Desktop verwenden.

Verbinden Sie sich als Root-Benutzer mit dem MySQL-Server:

sudo mysql -u root

Erstellen Sie zuerst eine Datenbank für dieses Lab und wählen Sie sie aus:

CREATE DATABASE lab_db;
USE lab_db;

Erstellen Sie nun die Tabelle users:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Diese SQL-Anweisung erstellt eine Tabelle namens users mit Spalten für id, username, email und created_at. Die Spalte id ist als Primärschlüssel festgelegt und wird automatisch inkrementiert.

Fügen wir einige Beispieldaten in die Tabelle users ein:

INSERT INTO users (username, email) VALUES
('john_doe', 'john.doe@example.com'),
('jane_smith', 'jane.smith@example.com'),
('peter_jones', 'peter.jones@example.com');

Erstellen eines Einzelspaltenindex

Nun erstellen wir einen Index für die Spalte username. Dies wird dazu beitragen, Abfragen zu beschleunigen, die nach Benutzernamen suchen.

CREATE INDEX idx_username ON users (username);

Diese Anweisung erstellt einen Index namens idx_username für die Spalte username der Tabelle users.

Überprüfen des Index

Sie können überprüfen, ob der Index erstellt wurde, indem Sie den Befehl SHOW INDEXES verwenden:

SHOW INDEXES FROM users;

Die Ausgabe zeigt die Details der Indizes für die Tabelle users, einschließlich des gerade erstellten idx_username-Index. Sie sollten eine Zeile sehen, in der Key_name idx_username und Column_name username ist.

+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| users |          0 | PRIMARY      |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          1 | idx_username |            1 | username    | A         |           3 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

Sie haben nun erfolgreich einen Einzelspaltenindex für eine Tabelle erstellt. Dies wird die Leistung von Abfragen verbessern, die Daten basierend auf der indizierten Spalte filtern.

Beispiel für die Erstellung eines MySQL-Index

Abfrageplan mit EXPLAIN analysieren

In diesem Schritt lernen Sie, wie Sie die EXPLAIN-Anweisung in MySQL verwenden, um den Abfrageausführungsplan zu analysieren. Das Verständnis des Abfrageplans ist unerlässlich, um Leistungsengpässe zu identifizieren und Ihre Abfragen zu optimieren.

Was ist ein Abfrageplan?

Ein Abfrageplan ist eine Art Fahrplan, den die Datenbank-Engine zur Ausführung einer Abfrage verwendet. Er beschreibt die Reihenfolge, in der auf Tabellen zugegriffen wird, welche Indizes verwendet werden und welche Algorithmen zur Datenabfrage angewendet werden. Durch die Analyse des Abfrageplans können Sie verstehen, wie die Datenbank Ihre Abfrage ausführt, und Bereiche für Verbesserungen identifizieren.

Verwendung der EXPLAIN-Anweisung

Die EXPLAIN-Anweisung liefert Informationen darüber, wie MySQL eine Abfrage ausführt. Sie zeigt die beteiligten Tabellen, die verwendeten Indizes, die Join-Reihenfolge und andere Details, die Ihnen helfen können, die Leistung der Abfrage zu verstehen.

Analysieren wir nun eine einfache Abfrage mit EXPLAIN.

EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

Die Ausgabe der EXPLAIN-Anweisung ist eine Tabelle mit mehreren Spalten. Hier ist eine Aufschlüsselung einiger der wichtigsten Spalten:

id: Die ID der SELECT-Anweisung.
select_type: Der Typ der SELECT-Abfrage (z. B. SIMPLE, PRIMARY, SUBQUERY).
table: Die zugegriffene Tabelle.
type: Der Join-Typ. Dies ist eine der wichtigsten Spalten. Gängige Werte sind:
- system: Die Tabelle hat nur eine Zeile.
- const: Die Tabelle hat höchstens eine übereinstimmende Zeile, die zu Beginn der Abfrage gelesen wird.
- eq_ref: Für jede Kombination von Zeilen aus den vorherigen Tabellen wird eine Zeile aus dieser Tabelle gelesen. Dies wird beim Verknüpfen über eine indizierte Spalte verwendet.
- ref: Für jede Kombination von Zeilen aus den vorherigen Tabellen werden alle übereinstimmenden Zeilen aus dieser Tabelle gelesen. Dies wird beim Verknüpfen über eine indizierte Spalte verwendet.
- range: Nur Zeilen innerhalb eines bestimmten Bereichs werden mithilfe eines Index abgerufen.
- index: Ein vollständiger Index-Scan wird durchgeführt.
- ALL: Ein vollständiger Tabellenscan wird durchgeführt. Dies ist der ineffizienteste Typ.
possible_keys: Die Indizes, die MySQL zum Auffinden der Zeilen in der Tabelle verwenden könnte.
key: Der Index, den MySQL tatsächlich verwendet hat.
key_len: Die Länge des Schlüssels, den MySQL verwendet hat.
ref: Die Spalten oder Konstanten, die mit dem Index verglichen werden.
rows: Die Anzahl der Zeilen, die MySQL schätzt, untersuchen zu müssen, um die Abfrage auszuführen.
Extra: Zusätzliche Informationen darüber, wie MySQL die Abfrage ausführt. Gängige Werte sind:
- Using index: Die Abfrage kann nur mit dem Index erfüllt werden.
- Using where: MySQL muss die Zeilen nach dem Zugriff auf die Tabelle filtern.
- Using temporary: MySQL muss eine temporäre Tabelle erstellen, um die Abfrage auszuführen.
- Using filesort: MySQL muss die Zeilen nach dem Zugriff auf die Tabelle sortieren.

Interpretation der EXPLAIN-Ausgabe

Für die Abfrage SELECT * FROM users WHERE username = 'john_doe' sollte die EXPLAIN-Ausgabe etwa so aussehen:

+------+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
| id   | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                 |
+------+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | users | ref  | idx_username  | idx_username | 767     | const | 1    | Using index condition |
+------+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+

In diesem Beispiel:

type ist ref, was bedeutet, dass MySQL einen Index verwendet, um die übereinstimmende Zeile zu finden.
possible_keys und key zeigen beide idx_username, was bedeutet, dass MySQL den im vorherigen Schritt erstellten idx_username-Index verwendet.
rows ist 1, was bedeutet, dass MySQL schätzt, nur eine Zeile untersuchen zu müssen, um die Abfrage auszuführen.

Analyse einer Abfrage ohne Index

Analysieren wir nun eine Abfrage, die keinen Index verwendet. Fügen wir zuerst eine neue Spalte namens city zur Tabelle users hinzu:

ALTER TABLE users ADD COLUMN city VARCHAR(255);

Führen wir nun erneut eine EXPLAIN-Anweisung für eine Abfrage aus, die nach city sucht. Da wir noch keine Daten in die Spalte city eingefügt haben, aktualisieren wir eine der Zeilen:

UPDATE users SET city = 'New York' WHERE username = 'john_doe';

Führen Sie nun erneut die EXPLAIN-Anweisung aus:

EXPLAIN SELECT * FROM users WHERE city = 'New York';

Die Ausgabe könnte wie folgt aussehen:

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 3    | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

In diesem Beispiel:

type ist ALL, was bedeutet, dass MySQL einen vollständigen Tabellenscan durchführt.
possible_keys und key sind beide NULL, was bedeutet, dass MySQL keine Indizes verwendet.
rows ist 3, was bedeutet, dass MySQL schätzt, alle 3 Zeilen in der Tabelle untersuchen zu müssen, um die Abfrage auszuführen.
Extra zeigt Using where, was bedeutet, dass MySQL die Zeilen nach dem Zugriff auf die Tabelle filtern muss.

Dies deutet darauf hin, dass die Abfrage nicht optimiert ist und von einem Index für die Spalte city profitieren könnte.

Hinzufügen eines zusammengesetzten Index für Abfragen mit mehreren Spalten

In diesem Schritt lernen Sie, wie Sie einen zusammengesetzten Index (Composite Index) in MySQL erstellen. Ein zusammengesetzter Index ist ein Index für zwei oder mehr Spalten in einer Tabelle. Er kann die Leistung von Abfragen, die Daten basierend auf mehreren Spalten filtern, erheblich verbessern.

Was ist ein zusammengesetzter Index?

Ein zusammengesetzter Index ist ein Index, der mehrere Spalten abdeckt. Er ist nützlich, wenn Abfragen häufig mehrere Spalten in der WHERE-Klausel verwenden. Die Reihenfolge der Spalten im zusammengesetzten Index ist wichtig. Der Index ist am effektivsten, wenn die Spalten in der WHERE-Klausel der Abfrage in derselben Reihenfolge angegeben werden.

Fügen wir der Tabelle users weitere Daten hinzu, einschließlich verschiedener Städte:

INSERT INTO users (username, email, city) VALUES
('alice_brown', 'alice.brown@example.com', 'Los Angeles'),
('bob_davis', 'bob.davis@example.com', 'Chicago'),
('charlie_wilson', 'charlie.wilson@example.com', 'New York'),
('david_garcia', 'david.garcia@example.com', 'Los Angeles');

Erstellen eines zusammengesetzten Index

Angenommen, Sie führen häufig Abfragen aus, die Benutzer sowohl nach city als auch nach username filtern. In diesem Fall können Sie einen zusammengesetzten Index für die Spalten city und username erstellen.

CREATE INDEX idx_city_username ON users (city, username);

Diese Anweisung erstellt einen Index namens idx_city_username für die Spalten city und username der Tabelle users.

Überprüfen des Index

Sie können überprüfen, ob der Index erstellt wurde, indem Sie den Befehl SHOW INDEXES verwenden:

SHOW INDEXES FROM users;

Die Ausgabe zeigt die Details der Indizes für die Tabelle users, einschließlich des gerade erstellten idx_city_username-Index. Sie sollten zwei Zeilen für idx_city_username sehen, eine für die Spalte city und eine für die Spalte username.

+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| users |          0 | PRIMARY           |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          1 | idx_username      |            1 | username    | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          1 | idx_city_username |            1 | city        | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| users |          1 | idx_city_username |            2 | username    | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

Verwendung des zusammengesetzten Index

Um den Vorteil des zusammengesetzten Index zu sehen, können Sie den Befehl EXPLAIN verwenden, um eine Abfrage zu analysieren, die sowohl die Spalten city als auch username in der WHERE-Klausel verwendet.

EXPLAIN SELECT * FROM users WHERE city = 'New York' AND username = 'charlie_wilson';

Die EXPLAIN-Ausgabe zeigt, dass die Abfrage den idx_city_username-Index verwendet, was bedeutet, dass die Datenbank die übereinstimmende Zeile schnell finden kann, ohne die gesamte Tabelle zu durchsuchen. Achten Sie in der Ausgabe auf die Spalten possible_keys und key. Wenn der Index verwendet wird, sehen Sie idx_city_username in diesen Spalten.

+------+-------------+-------+------+--------------------------------+--------------+---------+-------+------+------------------------------------+
| id   | select_type | table | type | possible_keys                  | key          | key_len | ref   | rows | Extra                              |
+------+-------------+-------+------+--------------------------------+--------------+---------+-------+------+------------------------------------+
|    1 | SIMPLE      | users | ref  | idx_username,idx_city_username | idx_username | 767     | const | 1    | Using index condition; Using where |
+------+-------------+-------+------+--------------------------------+--------------+---------+-------+------+------------------------------------+

Reihenfolge der Spalten im Index

Die Reihenfolge der Spalten im zusammengesetzten Index ist wichtig. Wenn Sie einen Index auf (username, city) anstelle von (city, username) erstellen, ist der Index für Abfragen, die zuerst nach city und dann nach username filtern, weniger effektiv.

Wenn wir beispielsweise einen Index auf (username, city) hätten und die folgende Abfrage ausführen würden:

EXPLAIN SELECT * FROM users WHERE city = 'New York' AND username = 'charlie_wilson';

MySQL verwendet den Index möglicherweise nicht oder nur teilweise, da die Spalte city nicht die führende Spalte im Index ist.

Entfernen eines ungenutzten Index

In diesem Schritt lernen Sie, wie Sie einen ungenutzten Index in MySQL entfernen. Während Indizes die Abfrageleistung erheblich verbessern können, verursachen sie auch zusätzlichen Aufwand bei Schreiboperationen (INSERT, UPDATE und DELETE). Daher ist es wichtig, nicht mehr verwendete Indizes zu identifizieren und zu entfernen.

Warum ungenutzte Indizes entfernen?

Ungenutzte Indizes belegen Speicherplatz und können Schreiboperationen verlangsamen. Wenn Daten in einer Tabelle geändert werden, muss die Datenbank-Engine auch alle Indizes dieser Tabelle aktualisieren. Wenn ein Index von keiner Abfrage verwendet wird, verursacht er lediglich unnötigen Mehraufwand.

In den vorherigen Schritten haben wir einen Index namens idx_username für die Spalte username erstellt. Nehmen wir an, Sie stellen nach der Analyse Ihrer Abfragemuster fest, dass dieser Index nicht mehr verwendet wird.

Entfernen des Index

Um den Index idx_username zu entfernen, können Sie die Anweisung DROP INDEX verwenden:

DROP INDEX idx_username ON users;

Diese Anweisung entfernt den Index idx_username aus der Tabelle users.

Überprüfen der Indexentfernung

Sie können überprüfen, ob der Index entfernt wurde, indem Sie den Befehl SHOW INDEXES verwenden:

SHOW INDEXES FROM users;

Die Ausgabe zeigt die Details der Indizes für die Tabelle users. Der Index idx_username sollte in der Ausgabe nicht mehr angezeigt werden.

+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| users |          0 | PRIMARY           |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          1 | idx_city_username |            1 | city        | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| users |          1 | idx_city_username |            2 | username    | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

Identifizieren ungenutzter Indizes

In einem realen Szenario kann die Identifizierung ungenutzter Indizes schwierig sein. MySQL bietet verschiedene Werkzeuge und Techniken, um Sie bei dieser Aufgabe zu unterstützen:

MySQL Enterprise Audit: Diese Funktion ermöglicht die Protokollierung aller auf Ihrem Server ausgeführten Abfragen. Sie können dann die Abfrageprotokolle analysieren, um festzustellen, welche Indizes verwendet werden.
Performance Schema: Das Performance Schema liefert detaillierte Informationen zur Serverleistung, einschließlich der Indexnutzung.
Tools von Drittanbietern: Mehrere Tools von Drittanbietern können Ihnen helfen, die Indexnutzung zu überwachen und ungenutzte Indizes zu identifizieren.

Durch regelmäßige Überwachung Ihrer Indexnutzung und das Entfernen ungenutzter Indizes können Sie die Gesamtleistung Ihrer Datenbank verbessern.

Nachdem wir nun alle Schritte abgeschlossen haben, beenden wir die MySQL-Konsole:

exit;

Beispiel für die Entfernung eines Index in der MySQL-Tabelle users

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie einen Index für eine einzelne Spalte in MySQL erstellen, um die Abfrageleistung zu verbessern. Sie haben auch gelernt, wie Sie die EXPLAIN-Anweisung verwenden, um Abfrageausführungspläne zu analysieren und Leistungsengpässe zu identifizieren. Darüber hinaus haben Sie das Erstellen von zusammengesetzten Indizes für Abfragen mit mehreren Spalten und das Entfernen ungenutzter Indizes geübt, um die Datenbankeffizienz aufrechtzuerhalten. Das effektive Verstehen und Nutzen von Indizes ist eine grundlegende Fähigkeit zur Optimierung der Datenbankleistung.