Einführung
In diesem Lab lernen Sie, wie Sie MySQL-Partitionierung für große Datenmengen einsetzen, um die Abfrageleistung und Datenverwaltung zu verbessern. Im Mittelpunkt steht die Partitionierung einer Tabelle nach Wertebereichen. Dafür verwenden Sie die Spalte sale_date der Tabelle sales.
Zu Beginn verbinden Sie sich mit dem MySQL-Server und erstellen die Datenbank sales_data. Anschließend legen Sie die Tabelle sales an und partitionieren sie anhand des Jahres von sale_date in Partitionen für die Jahre 2020, 2021, 2022 und 2023 sowie in eine Partition für zukünftige Daten. In den folgenden Schritten fragen Sie Daten aus bestimmten Partitionen ab, organisieren Partitionen mit ALTER TABLE neu und untersuchen, wie sich die Partitionierung auf die Abfragegeschwindigkeit auswirkt.
Hinweis: In diesem Lab müssen Sie die MySQL-Shell nur zu Beginn einmal öffnen und am Ende wieder verlassen. Führen Sie alle SQL-Befehle der folgenden Schritte in derselben MySQL-Sitzung aus.
Eine partitionierte Tabelle erstellen
In diesem Schritt erstellen Sie eine Datenbank und eine partitionierte Tabelle in MySQL. Partitionierung erleichtert die Verwaltung großer Datenmengen, indem sie eine Tabelle nach einer festgelegten Regel in kleinere, besser handhabbare Teile aufteilt. Das kann die Abfrageleistung deutlich verbessern, insbesondere wenn Abfragen Daten anhand des Partitionierungsschlüssels filtern.
Öffnen Sie zuerst ein Terminal in der LabEx-VM. Sie sollten sich bereits im Verzeichnis ~/project befinden.
Verbinden Sie sich als Root-Benutzer mit dem MySQL-Server. Führen Sie diesen Schritt nur einmal zu Beginn des Labs aus:
sudo mysql -u root
Sie befinden sich nun in der MySQL-Shell. Führen Sie alle weiteren SQL-Befehle bis zum Ende des Labs in dieser Sitzung aus.
Aktivieren Sie eine Mitschrift, damit die Schrittprüfungen die in dieser Sitzung eingegebenen SQL-Befehle lesen können, solange die Sitzung geöffnet ist:
tee /home/labex/project/partitioning-session.log
Lassen Sie die Mitschrift bis zum Ende des Labs aktiviert. SQL-Befehle, die Sie hier eingeben, werden nicht im Befehlsverlauf der Terminal-Shell aufgezeichnet.
Erstellen Sie die Datenbank sales_data, in der Sie die Tabelle speichern:
CREATE DATABASE sales_data;
Wechseln Sie zur neu erstellten Datenbank:
USE sales_data;
Erstellen Sie nun die Tabelle sales und partitionieren Sie sie nach dem Jahr der Spalte sale_date. Legen Sie Partitionen für die Jahre 2020, 2021, 2022 und 2023 sowie eine Auffangpartition für zukünftige Daten an.
CREATE TABLE sales (
sale_id INT NOT NULL,
sale_date DATE NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (sale_id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION pFuture VALUES LESS THAN MAXVALUE
);
Die Klausel PARTITION BY RANGE funktioniert folgendermaßen:
PARTITION BY RANGE (YEAR(sale_date)): Legt fest, dass die Tabelle anhand der Wertebereiche partitioniert wird, die die FunktionYEAR()auf die Spaltesale_dateanwendet.PARTITION p2020 VALUES LESS THAN (2021): Erstellt die Partitionp2020. Alle Zeilen, derensale_dateein Jahr vor 2021 hat (also 2020), werden in dieser Partition gespeichert.PARTITION p2021 VALUES LESS THAN (2022): Erstellt die Partitionp2021für Daten aus dem Jahr 2021.PARTITION p2022 VALUES LESS THAN (2023): Erstellt die Partitionp2022für Daten aus dem Jahr 2022.PARTITION p2023 VALUES LESS THAN (2024): Erstellt die Partitionp2023für Daten aus dem Jahr 2023.PARTITION pFuture VALUES LESS THAN MAXVALUE: Erstellt die PartitionpFuturefür alle Daten, bei denen das Jahr vonsale_datemindestens 2024 beträgt.MAXVALUEist ein spezieller Wert, der immer größer ist als alle anderen Werte.
Prüfen Sie nach dem Ausführen der Anweisung CREATE TABLE mit folgendem Befehl die Tabellenstruktur und ihre Partitionen:
SHOW CREATE TABLE sales;
Suchen Sie in der Ausgabe nach der Klausel PARTITION BY RANGE. Sie bestätigt, dass die Tabelle mit den angegebenen Partitionen erstellt wurde.
Fügen Sie nun Beispieldaten in die Tabelle sales ein. MySQL ordnet jede Zeile anhand von sale_date automatisch der passenden Partition zu.
INSERT INTO sales (sale_id, sale_date, amount) VALUES
(1, '2020-12-31', 100.00),
(2, '2021-01-15', 150.00),
(3, '2021-12-25', 200.00),
(4, '2022-06-01', 120.00),
(5, '2022-12-31', 180.00),
(6, '2023-03-10', 250.00),
(7, '2023-09-20', 300.00),
(8, '2024-01-01', 350.00);
Sie haben die partitionierte Tabelle erstellt und Daten eingefügt. Im nächsten Schritt fragen Sie Daten aus bestimmten Partitionen ab.
Daten aus bestimmten Partitionen abfragen
In diesem Schritt lernen Sie, Daten effizient aus einer partitionierten Tabelle abzufragen, indem Sie gezielt bestimmte Partitionen ansprechen. Das ist einer der wichtigsten Vorteile der Partitionierung: MySQL kann dadurch nur die relevanten Partitionen durchsuchen. So muss weniger Datenmaterial verarbeitet werden und Abfragen können schneller ausgeführt werden.
Zur Erinnerung: Sie sollten sich weiterhin in der MySQL-Shell befinden und die Datenbank sales_data verwenden. Falls nicht, führen Sie Folgendes aus:
USE sales_data;
Um Daten aus bestimmten Partitionen abzufragen, können Sie eine WHERE-Klausel verwenden, die nach dem Partitionierungsschlüssel filtert. Der MySQL-Abfrageoptimierer erkennt oft anhand der WHERE-Klausel, welche Partitionen relevant sind.
Mit der folgenden Abfrage rufen Sie beispielsweise alle Verkäufe aus dem Jahr 2021 ab. Beachten Sie, dass die Abfrage eine direkte Bereichsbedingung für sale_date verwendet. Funktionen wie YEAR(sale_date) in der WHERE-Klausel können verhindern, dass MySQL Partition Pruning durchführt. In diesem Fall würde MySQL alle Partitionen durchsuchen.
SELECT * FROM sales WHERE sale_date >= '2021-01-01' AND sale_date < '2022-01-01';
Um zu sehen, auf welche Partitionen MySQL bei dieser Abfrage zugreift, verwenden Sie die Anweisung EXPLAIN PARTITIONS:
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2021-01-01' AND sale_date < '2022-01-01';
Sehen Sie sich in der Ausgabe von EXPLAIN PARTITIONS die Spalte partitions an. Sie sollte p2021 enthalten. Das zeigt, dass MySQL für diese Abfrage nur die Partition p2021 durchsucht.
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | sales | p2021 | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
Sie können auch Daten abfragen, die sich über mehrere Partitionen erstrecken. Mit der folgenden Abfrage rufen Sie beispielsweise Verkaufsdaten aus den Jahren 2022 und 2023 ab:
SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2024-01-01';
Wenn Sie erneut EXPLAIN PARTITIONS verwenden, sehen Sie, dass MySQL auf die Partitionen p2022 und p2023 zugreift:
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2024-01-01';
Die Spalte partitions zeigt p2022,p2023 an.
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | sales | p2022,p2023 | ALL | PRIMARY | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+
Das zeigt, wie MySQL durch Partitionierung während der Abfrageausführung irrelevante Partitionen ausschließen kann. Dadurch werden Ergebnisse schneller geliefert. Besonders bei sehr großen Tabellen spart das Zeit, da nicht die gesamte Tabelle durchsucht werden muss.
Um die Zeilenanzahl in jeder Partition zu sehen, fragen Sie die Tabelle INFORMATION_SCHEMA.PARTITIONS ab:
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
Diese Abfrage zeigt übersichtlich, wie die Daten auf die Partitionen verteilt sind.
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020 | 1 |
| p2021 | 2 |
| p2022 | 2 |
| p2023 | 2 |
| pFuture | 1 |
+----------------+------------+
Sie haben erfolgreich Daten aus bestimmten Partitionen abgefragt und gesehen, wie MySQL die Partitionierung zur Abfrageoptimierung nutzt.
Partitionen neu organisieren und verwalten
In diesem Schritt lernen Sie, wie Sie die Partitionsstruktur einer vorhandenen Tabelle mit der Anweisung ALTER TABLE ändern. Das ist hilfreich, wenn Ihre Daten wachsen oder sich Ihre Anforderungen ändern und Sie Ihr Partitionierungsschema anpassen müssen.
Zur Erinnerung: Sie sollten sich weiterhin in der MySQL-Shell befinden und die Datenbank sales_data verwenden. Falls nicht, führen Sie Folgendes aus:
USE sales_data;
Angenommen, Sie möchten eine neue Partition für das Jahr 2024 hinzufügen. Derzeit liegen Daten aus dem Jahr 2024 und später in der Partition pFuture. Mit ADD PARTITION können Sie keine neue Partition hinzufügen, da pFuture mit VALUES LESS THAN MAXVALUE definiert ist und immer die letzte Partition sein muss.
Teilen Sie stattdessen die Partition pFuture mit REORGANIZE in zwei Partitionen auf: eine für das Jahr 2024 (p2024) und eine neue Partition pFuture für alle späteren Jahre.
ALTER TABLE sales REORGANIZE PARTITION pFuture INTO (
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION pFuture VALUES LESS THAN MAXVALUE
);
Dieser Befehl nimmt die vorhandene Partition pFuture, verschiebt alle Daten aus dem Jahr 2024 in die neue Partition p2024 und legt pFuture so neu fest, dass sie Daten ab 2025 enthält. Die Zeile mit dem Wert '2024-01-01' in sale_date wird nach p2024 verschoben.
Prüfen Sie die aktualisierte Partitionsstruktur und die Zeilenanzahlen:
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
Die neue Partition p2024 sollte nun angezeigt werden. Die Zeile aus dem Jahr 2024 befindet sich jetzt in p2024.
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020 | 0 |
| p2021 | 2 |
| p2022 | 2 |
| p2023 | 2 |
| p2024 | 0 |
| pFuture | 0 |
+----------------+------------+
Führen Sie nun das Zusammenführen von Partitionen durch. Angenommen, Sie möchten die Partitionen p2020 und p2021 zu einer Partition mit dem Namen p2020_2021 zusammenführen.
ALTER TABLE sales REORGANIZE PARTITION p2020, p2021 INTO (
PARTITION p2020_2021 VALUES LESS THAN (2022)
);
Dieser Befehl führt die Daten aus p2020 und p2021 in einer neuen Partition namens p2020_2021 zusammen. Die Klausel VALUES LESS THAN (2022) legt die neue Grenze dieser zusammengeführten Partition fest.
Überprüfen Sie erneut die Partitionsstruktur:
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
Die Partitionen p2020 und p2021 sollten nicht mehr vorhanden sein. Stattdessen sehen Sie p2020_2021 mit der zusammengefassten Zeilenanzahl.
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020_2021 | 3 |
| p2022 | 2 |
| p2023 | 2 |
| p2024 | 0 |
| pFuture | 0 |
+----------------+------------+
Löschen Sie abschließend eine Partition. Mit dem folgenden Befehl entfernen Sie die Partition p2024. Beachten Sie, dass dabei auch alle Daten in dieser Partition gelöscht werden.
ALTER TABLE sales DROP PARTITION p2024;
Prüfen Sie ein letztes Mal die Partitionsstruktur:
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
Die Partition p2024 sollte nicht mehr aufgeführt sein.
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020_2021 | 3 |
| p2022 | 2 |
| p2023 | 2 |
| pFuture | 0 |
+----------------+------------+
Sie haben Partitionen mit ALTER TABLE neu organisiert, zusammengeführt und gelöscht. Das zeigt, wie flexibel sich partitionierte Tabellen an die Entwicklung Ihrer Daten anpassen lassen.
Auswirkungen der Partitionierung auf die Abfragegeschwindigkeit untersuchen
In diesem Schritt untersuchen Sie, wie sich Partitionierung auf die Abfrageleistung auswirken kann. Unser aktueller Datensatz ist klein, aber Sie können trotzdem das Prinzip des Partition Pruning beobachten: MySQL durchsucht nur die benötigten Partitionen. Bei größeren Datenmengen fällt dieser Effekt deutlich stärker aus.
Zur Erinnerung: Sie sollten sich weiterhin in der MySQL-Shell befinden und die Datenbank sales_data verwenden. Falls nicht, führen Sie Folgendes aus:
USE sales_data;
Um die Auswirkungen der Partitionierung zu beobachten, verwenden Sie die Anweisung EXPLAIN. Sie zeigt den Ausführungsplan einer Abfrage. Mit EXPLAIN PARTITIONS sehen Sie insbesondere, auf welche Partitionen zugegriffen wird.
Führen Sie eine Abfrage aus, die nach dem Partitionierungsschlüssel filtert, also nach dem Jahr von sale_date:
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';
Sehen Sie sich in der Ausgabe die Spalte partitions an. Sie sollte anzeigen, dass nur die Partition p2023 durchsucht wird.
Führen Sie nun eine Abfrage aus, die nicht direkt nach dem Partitionierungsschlüssel, sondern nach einer anderen Spalte (amount) filtert:
EXPLAIN PARTITIONS SELECT * FROM sales WHERE amount > 200;
Da sich die Abfragebedingung nicht direkt auf den Partitionierungsschlüssel (sale_date) bezieht, muss MySQL möglicherweise mehrere oder alle Partitionen durchsuchen, um passende Zeilen zu finden. Die Spalte partitions in der Ausgabe von EXPLAIN PARTITIONS zeigt, welche Partitionen berücksichtigt wurden. Bei unserem kleinen Datensatz kann es sein, dass MySQL trotzdem alle Partitionen durchsucht.
Mit der Profiling-Funktion von MySQL können Sie die Abfrageausführung und die dafür benötigte Zeit genauer untersuchen.
Aktivieren Sie das Profiling:
SET profiling = 1;
Führen Sie die beiden Abfragen nun erneut aus:
SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';
SELECT * FROM sales WHERE amount > 200;
Zeigen Sie die Profiling-Ergebnisse an:
SHOW PROFILES;
Die Ausgabe enthält die ausgeführten Abfragen und ihre jeweilige Dauer. Mit der zugehörigen Query_ID können Sie anschließend die Details einer bestimmten Abfrage untersuchen:
SHOW PROFILE FOR QUERY [Query_ID];
Ersetzen Sie [Query_ID] durch die ID der Abfrage, die Sie in der Ausgabe von SHOW PROFILES analysieren möchten. Sehen Sie sich die einzelnen Ausführungsphasen und die jeweils dafür benötigte Zeit an.
Bei unserem kleinen Datensatz ist der Zeitunterschied möglicherweise kaum zu erkennen. In einer realen Umgebung mit Millionen von Zeilen sind Abfragen, die Partition Pruning nutzen können (wie die Abfrage nach YEAR(sale_date)), jedoch deutlich schneller als Abfragen, für die mehrere oder alle Partitionen durchsucht werden müssen.
Deaktivieren Sie abschließend das Profiling:
SET profiling = 0;
In diesem Schritt haben Sie mit EXPLAIN PARTITIONS und Profiling untersucht, wie sich die Partitionierung auf Abfrageausführung und -leistung auswirkt.
Zusammenfassung
In diesem Lab haben Sie gelernt, wie Sie MySQL-Partitionierung für große Datenmengen einsetzen, um die Abfrageleistung und Datenverwaltung zu verbessern. Zunächst haben Sie eine Datenbank und eine nach Wertebereichen partitionierte Tabelle erstellt, deren Partitionierung auf dem Jahr einer Datumsspalte basiert. Anschließend haben Sie Daten aus bestimmten Partitionen abgefragt und beobachtet, wie MySQL Partition Pruning zur Abfrageoptimierung nutzt. Schließlich haben Sie mit der Anweisung ALTER TABLE Partitionen hinzugefügt, aufgeteilt und zusammengeführt sowie mit EXPLAIN PARTITIONS und Profiling untersucht, wie sich die Partitionierung auf die Abfragegeschwindigkeit auswirkt. Partitionierung ist eine leistungsfähige Technik, um große Tabellen in MySQL effizient zu verwalten und abzufragen.
Wenn Sie alle Schritte abgeschlossen haben, können Sie die MySQL-Shell mit folgendem Befehl verlassen:
exit;
