MySQL-Indizes und Performance-Optimierung

Einführung

In diesem Labor lernen Sie MySQL-Indizes und Performance-Optimierungsmethoden kennen. Das Labor konzentriert sich auf die Erstellung und Verwaltung von Indizes, um die Datenbankabfrageleistung zu verbessern.

Sie beginnen mit der Erstellung einer users-Tabelle und der Einfügung von Beispieldaten. Anschließend erstellen Sie einen Einspaltigen Index auf der Spalte username und lernen, wie Sie dessen Erstellung verifizieren. Das Labor behandelt auch die Analyse von Abfrageplänen mithilfe von EXPLAIN, das Hinzufügen von Verbundindizes für Abfragen mit mehreren Spalten und das Entfernen unbenutzter Indizes, um die Datenbankleistung zu erhalten.

Erstellen eines Einspaltigen Index in einer Tabelle

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

Verständnis von Indizes

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

Erstellen einer Tabelle

Erstellen wir zunächst 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 mit dem MySQL-Server als root-Benutzer:

sudo mysql -u root

Erstellen Sie zunächst eine Datenbank für dieses Labor 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 definiert und wird automatisch erhöht.

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

INSERT INTO users (username, email) VALUES
('john_doe', '[email protected]'),
('jane_smith', '[email protected]'),
('peter_jones', '[email protected]');

Erstellen eines Einspaltigen Index

Erstellen wir nun einen Index auf der Spalte username. Dies beschleunigt Abfragen, die Benutzer nach ihrem Benutzernamen suchen.

CREATE INDEX idx_username ON users (username);

Diese Anweisung erstellt einen Index namens idx_username auf der Spalte username der Tabelle users.

Überprüfung 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 in der Tabelle users, einschließlich des gerade erstellten Index idx_username. 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 einspaltigen Index in einer Tabelle erstellt. Dies verbessert die Leistung von Abfragen, die Daten basierend auf der indizierten Spalte filtern.

exit;

Analysieren eines Abfrageplans mit EXPLAIN

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

Was ist ein Abfrageplan?

Ein Abfrageplan ist eine Roadmap, die die Datenbank-Engine verwendet, um eine Abfrage auszuführen. Er beschreibt die Reihenfolge, in der Tabellen zugegriffen werden, die Indizes, die verwendet werden, und die Algorithmen, die angewendet werden, um die Daten abzurufen. Durch die Analyse des Abfrageplans können Sie verstehen, wie die Datenbank Ihre Abfrage ausführt, und Bereiche für Verbesserungen identifizieren.

Verwendung der Anweisung EXPLAIN

Die Anweisung EXPLAIN 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.

Fahren wir mit der Tabelle users fort, die wir im vorherigen Schritt erstellt haben. Öffnen Sie ein Terminal in der LabEx-VM (verwenden Sie die Verknüpfung "Xfce Terminal" auf dem Desktop) und verbinden Sie sich mit dem MySQL-Server als root-Benutzer:

sudo mysql -u root

Wählen Sie die Datenbank aus, die wir im vorherigen Schritt erstellt haben:

USE lab_db;

Analysieren wir nun eine einfache Abfrage mit EXPLAIN.

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

Die Ausgabe der Anweisung EXPLAIN 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 abgerufene Tabelle.
type: Der Join-Typ. Dies ist eine der wichtigsten Spalten. Häufige Werte sind:
- system: Die Tabelle enthält nur eine Zeile.
- const: Die Tabelle enthält 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 verwendet, wenn auf einer indizierten Spalte verbunden wird.
- ref: Für jede Kombination von Zeilen aus den vorherigen Tabellen werden alle übereinstimmenden Zeilen aus dieser Tabelle gelesen. Dies wird verwendet, wenn auf einer indizierten Spalte verbunden wird.
- range: Nur Zeilen innerhalb eines bestimmten Bereichs werden abgerufen, wobei ein Index verwendet wird.
- index: Ein vollständiger Indexscan wird durchgeführt.
- ALL: Ein vollständiger Tabellenscan wird durchgeführt. Dies ist der am wenigsten effiziente Typ.
possible_keys: Die Indizes, die MySQL verwenden könnte, um die Zeilen in der Tabelle zu finden.
key: Der Index, den MySQL tatsächlich verwendet hat.
key_len: Die Länge des verwendeten Schlüssels.
ref: Die Spalten oder Konstanten, die mit dem Index verglichen werden.
rows: Die Anzahl der Zeilen, die MySQL schätzt, dass sie untersuchen muss, um die Abfrage auszuführen.
Extra: Zusätzliche Informationen darüber, wie MySQL die Abfrage ausführt. Häufige Werte sind:
- Using index: Die Abfrage kann nur mit dem Index erfüllt werden.
- Using where: MySQL muss die Zeilen filtern, nachdem die Tabelle aufgerufen wurde.
- Using temporary: MySQL muss eine temporäre Tabelle erstellen, um die Abfrage auszuführen.
- Using filesort: MySQL muss die Zeilen sortieren, nachdem die Tabelle aufgerufen wurde.

Interpretieren der EXPLAIN-Ausgabe

Für die Abfrage SELECT * FROM users WHERE username = 'john_doe' sollte die EXPLAIN-Ausgabe ungefähr 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 an, was bedeutet, dass MySQL den Index idx_username verwendet, den wir im vorherigen Schritt erstellt haben.
rows ist 1, was bedeutet, dass MySQL schätzt, dass es nur eine Zeile untersuchen muss, um die Abfrage auszuführen.

Analysieren einer Abfrage ohne Index

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

ALTER TABLE users ADD COLUMN city VARCHAR(255);

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

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

Führen Sie nun die Anweisung EXPLAIN erneut aus:

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

Die Ausgabe könnte so 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, dass es alle 3 Zeilen in der Tabelle untersuchen muss, um die Abfrage auszuführen.
Extra zeigt Using where an, was bedeutet, dass MySQL die Zeilen filtern muss, nachdem die Tabelle aufgerufen wurde.

Dies zeigt an, dass die Abfrage nicht optimiert ist und von einem Index auf der Spalte city profitieren könnte.

exit;

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

In diesem Schritt erfahren Sie, wie Sie einen zusammengesetzten Index in MySQL erstellen. Ein zusammengesetzter Index ist ein Index auf zwei oder mehr Spalten in einer Tabelle. Er kann die Leistung von Abfragen, die Daten basierend auf mehreren Spalten filtern, deutlich 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 derselben Reihenfolge in der WHERE-Klausel der Abfrage angegeben werden.

Wir verwenden weiterhin die Tabelle users, die wir in den vorherigen Schritten erstellt haben. Öffnen Sie ein Terminal in der LabEx-VM (verwenden Sie die Verknüpfung "Xfce Terminal" auf dem Desktop) und verbinden Sie sich mit dem MySQL-Server als root-Benutzer:

sudo mysql -u root

Wählen Sie die Datenbank aus, die wir in den vorherigen Schritten erstellt haben:

USE lab_db;

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

INSERT INTO users (username, email, city) VALUES
('alice_brown', '[email protected]', 'Los Angeles'),
('bob_davis', '[email protected]', 'Chicago'),
('charlie_wilson', '[email protected]', 'New York'),
('david_garcia', '[email protected]', '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 auf den Spalten city und username erstellen.

CREATE INDEX idx_city_username ON users (city, username);

Diese Anweisung erstellt einen Index namens idx_city_username auf den Spalten city und username der Tabelle users.

Überprüfung 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 in der Tabelle users, einschließlich des gerade erstellten Index idx_city_username. 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 Spalte city als auch die Spalte 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 Index idx_city_username verwendet, was bedeutet, dass die Datenbank die übereinstimmende Zeile schnell finden kann, ohne die gesamte Tabelle zu durchsuchen. Suchen Sie nach den Spalten possible_keys und key in der Ausgabe. 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 nach city und dann nach username filtern, weniger effektiv.

Zum Beispiel, wenn wir 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.

exit;

Entfernen eines nicht verwendeten Index

In diesem Schritt erfahren Sie, wie Sie einen nicht verwendeten Index in MySQL entfernen. Während Indizes die Abfrageleistung erheblich verbessern können, erhöhen sie auch die Overhead-Kosten für Schreibvorgänge (Einfügen, Aktualisieren und Löschen). Daher ist es wichtig, Indizes zu identifizieren und zu entfernen, die nicht mehr verwendet werden.

Warum nicht verwendete Indizes entfernen?

Nicht verwendete Indizes belegen Festplattenspeicherplatz und können Schreibvorgänge verlangsamen. Wenn Daten in einer Tabelle geändert werden, muss die Datenbank-Engine auch alle Indizes auf dieser Tabelle aktualisieren. Wenn ein Index von keiner Abfrage verwendet wird, verursacht er nur unnötigen Overhead.

sudo mysql -u root

Wählen Sie die Datenbank aus, die wir in den vorherigen Schritten erstellt haben:

USE lab_db;

In den vorherigen Schritten haben wir einen Index namens idx_username auf der Spalte username erstellt. Nehmen wir an, dass Sie nach der Analyse Ihrer Abfragemuster festgestellt haben, 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üfung 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 in der Tabelle users. Der Index idx_username sollte in der Ausgabe nicht mehr vorhanden sein.

+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| 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 nicht verwendeter Indizes

In einer realen Umgebung kann die Identifizierung nicht verwendeter Indizes eine Herausforderung darstellen. MySQL bietet verschiedene Tools und Techniken, die Ihnen bei dieser Aufgabe helfen:

MySQL Enterprise Audit: Diese Funktion ermöglicht es Ihnen, alle auf Ihrem Server ausgeführten Abfragen zu protokollieren. Sie können dann die Abfrageprotokolle analysieren, um zu ermitteln, welche Indizes verwendet werden.
Performance Schema: Das Performance Schema liefert detaillierte Informationen über die Serverleistung, einschließlich der Indexnutzung.
Tools von Drittanbietern: Verschiedene Tools von Drittanbietern können Ihnen helfen, die Indexnutzung zu überwachen und nicht verwendete Indizes zu identifizieren.

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

exit;

Zusammenfassung

In diesem Labor haben Sie gelernt, wie man einen einspaltigen Index in MySQL erstellt, um die Abfrageleistung zu verbessern. Sie haben auch gelernt, wie man die Anweisung EXPLAIN verwendet, um Abfragepläne zu analysieren und Leistungsprobleme zu identifizieren. Darüber hinaus haben Sie die Erstellung von zusammengesetzten Indizes für Abfragen mit mehreren Spalten und das Entfernen nicht verwendeter Indizes geübt, um die Datenbank-Effizienz zu erhalten. Das Verständnis und die effektive Nutzung von Indizes ist eine grundlegende Fähigkeit zur Optimierung der Datenbankleistung.