Hinzufügen eines zusammengesetzten Indexes für Mehrspaltenabfragen
In diesem Schritt lernen Sie, wie Sie in MySQL einen zusammengesetzten Index (composite index) erstellen. Ein zusammengesetzter Index ist ein Index, der sich auf zwei oder mehr Spalten in einer Tabelle bezieht. Er kann die Leistung von Abfragen, die Daten anhand mehrerer 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 gleichen 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 das Xfce Terminal-Kürzel auf dem Desktop) und verbinden Sie sich als Root-Benutzer mit dem MySQL-Server:
mysql -u root -proot
Fügen wir einige weitere Daten in die Tabelle users ein, 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 Indexes
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üfen des Indexes
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 von Ihnen gerade erstellten Indexes 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 | Visible | Expression |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | | YES | NULL |
| users | 1 | idx_username | 1 | username | A | 7 | NULL | NULL | | BTREE | | | YES | NULL |
| users | 1 | idx_city_username | 1 | city | A | 3 | NULL | NULL | YES | BTREE | | | YES | NULL |
| users | 1 | idx_city_username | 2 | username | A | 7 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
Verwenden des zusammengesetzten Indexes
Um den Nutzen des zusammengesetzten Indexes 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 Ausgabe von EXPLAIN zeigt, dass die Abfrage den Index idx_city_username verwendet. Das bedeutet, dass die Datenbank die passende Zeile schnell finden kann, ohne die gesamte Tabelle zu scannen. Suchen Sie in der Ausgabe nach den Spalten possible_keys und key. Wenn der Index verwendet wird, sehen Sie idx_city_username in diesen Spalten.
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | users | NULL | ref | idx_city_username | idx_city_username | 770 | const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------+
Reihenfolge der Spalten im Index
Die Reihenfolge der Spalten im zusammengesetzten Index ist von Bedeutung. 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.
Beispielsweise würde, 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 möglicherweise den Index nicht verwenden oder ihn nur teilweise nutzen, da die Spalte city nicht die führende Spalte im Index ist.
exit;
