Wenn eine Tabelle nach Wertebereichen partitioniert wird,
enthält später jede Partition die Zeilen, für die der
Partitionierungsausdruck einen Wert hat, der in einem bestimmten
Wertebereich liegt. Die Wertebereiche sollten aneinander
grenzen, aber sich nicht überschneiden, und sie sollten mit dem
VALUES LESS THAN
-Operator definiert werden.
Bei den nächsten Beispielen gehen wir davon aus, dass wie unten
beschrieben eine Tabelle für die Personaldaten einer Kette von
20 Videotheken (mit den Nummern 1 bis 20) eingerichtet wird:
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL );
Diese Tabelle kann je nach Bedarf auf unterschiedliche Weise
nach Bereichen partitioniert werden. Eine Möglichkeit wäre es,
die store_id
-Spalte zu verwenden. Sie
könnten die Tabelle beispielsweise mit einer PARTITION
BY RANGE
-Klausel auf 4 Partitionen verteilen:
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21) );
In diesem Partitionierungsschema werden die Angestelltendaten
der Zweigstellen 1 bis 5 in Partition p0
gespeichert, die Angestelltendaten der Zweigstellen 6 bis 10 in
Partition p1
und so weiter. Beachten Sie,
dass die Partitionen der Reihe nach von der niedrigsten bis zur
höchsten Nummer definiert werden. Dies verlangt die Syntax von
PARTITION BY RANGE
, die in dieser Hinsicht
einer switch ... case
-Anweisung in C oder
Java ähnelt.
Es ist einfach, festzustellen, dass eine neue Zeile mit den
Daten (72, 'Michael', 'Widenius', '1998-06-25', NULL,
13)
in die Partition p2
eingefügt
wurde, doch was geschieht, wenn die Videothekenkette eine 21.
Zweigstelle eröffnen möchte? In dem vorliegenden Schema gibt
es keine Regeln für Zeilen mit einer
store_id
größer 20. Daher wird ein Fehler
gemeldet, weil der Server nicht weiß, wohin damit. Dieses
Verhalten können Sie mit einem so genannten
„Catchall“ verhindern: einer VALUES LESS
THAN
-Klausel in der CREATE
TABLE
-Anweisung, die für alle Werte Vorsorge trifft,
die den größten explizit angegebenen Wert übersteigen:
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
MAXVALUE
ist der größte mögliche
Integer-Wert. Nun werden alle Zeilen, deren
store_id
-Spaltenwert größer oder gleich dem
größten definierten Wert 16 ist, in Partition
p3
gespeichert. Irgendwann einmal, wenn die
Anzahl der Zweigstellen auf 25, 30 oder mehr angewachsen ist,
können Sie mit einer ALTER TABLE
-Anweisung
neue Partitionen für die Zweigstellen 21 bis 25, 26 bis 30 und
so weiter hinzufügen (Einzelheiten zur Vorgehensweise finden
Sie unter Abschnitt 17.3, „Partitionsverwaltung“.)
In ähnlicher Weise können Sie die Tabelle auf der Grundlage
von Job-Codes partitionieren, d. h. anhand der Werte der Spalte
job_code
. Nehmen wir beispielsweise an,
reguläre (in der Filiale arbeitende) Angestellte haben einen
zweistelligen Job-Code, Büro- und Support-Mitarbeiter einen
dreistelligen und Manager einen vierstelligen. Dann könnten Sie
Ihre partitionierte Tabelle folgendermaßen definieren:
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (job_code) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN (10000) );
In diesem Beispiel würden die Daten der Filialmitarbeiter in
der Partition p0
, die der Büro- und
Support-Mitarbeiter in der Partition p1
und
die der Manager in der Partition p2
gespeichert.
In VALUES LESS THAN
-Klauseln kann auch ein
Ausdruck verwendet werden, allerdings nur mit der Maßgabe, dass
MySQL in der Lage sein muss, den Rückgabewert dieses Ausdrucks
in einem LESS THAN
(<
)-Vergleich auszuwerten; der Wert des
Ausdrucks darf also nicht NULL
sein. Dies ist
der Grund, weshalb die Spalten hired
,
separated
, job_code
und
store_id
der Tabelle
employees
als NOT NULL
definiert wurden.
Anstatt die Tabellendaten anhand der Zweigstellennummer zu
verteilen, können Sie auch einen Ausdruck verwenden, der auf
den beiden DATE
-Spalten basiert. Nehmen wir
beispielsweise an, Sie möchten die Tabelle nach dem Jahr
partitionieren, in welchem die Mitarbeiter das Unternehmen
verlassen, also nach dem Wert der Spalte
YEAR(separated)
. Folgende CREATE
TABLE
-Anweisung würde ein solches
Partitionierungsschema implementieren:
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT ) PARTITION BY RANGE ( YEAR(separated) ) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1996), PARTITION p2 VALUES LESS THAN (2001), PARTITION p3 VALUES LESS THAN MAXVALUE );
In diesem Schema werden die Daten der Mitarbeiter, die das
Unternehmen vor 1991 verließen, in der Partition
p0
gespeichert, die Daten derjenigen, die
zwischen 1991 und 1995 gingen, in Partition
p1
, die Daten derjenigen, die zwischen 1996
und 2000 gingen, in Partition p2
und die
Daten derjenigen, die nach 2000 gingen, in Partition
p3
.
Eine Bereichspartitionierung ist in folgenden Fällen besonders nützlich:
Sie möchten oder müssen „alte“ Daten
löschen. Wenn Sie das soeben gezeigte
Partitionierungsschema umsetzen, brauchen Sie nur noch
ALTER TABLE employees DROP PARTITION p0;
aufzurufen, um alle Datensätze der Angestellten zu
löschen, die vor 1991 das Unternehmen verlassen haben.
(Weitere Informationen finden Sie unter
Abschnitt 13.1.2, „ALTER TABLE
“, und
Abschnitt 17.3, „Partitionsverwaltung“.) Wenn Sie eine
Tabelle mit sehr vielen Zeilen haben, kann diese
Vorgehensweise sehr viel effizienter als eine
DELETE
-Anfrage wie etwa DELETE
FROM employees WHERE YEAR(separated) <= 1990;
sein.
Sie möchten eine Spalte benutzen, die Datums- oder Uhrzeitwerte oder Werte aus einer anderen Wertfolge enthält.
Es werden oft Anfragen ausgeführt, die direkt von einer
für die Partitionierung der Tabelle verwendeten Spalte
abhängen. So kann MySQL beispielsweise bei einer Anfrage
wie SELECT COUNT(*) FROM employees WHERE
YEAR(separated) = 2000 GROUP BY store_id;
ganz
schnell herausfinden, dass nur die Partition
p2
durchsucht werden muss, da die
restlichen Partitionen gar keine zu der
WHERE
-Klausel passenden Einträge
enthalten können. Hinweis:
Dies Optimierung wurde in den Quelldateien von MySQL 5.1
zwar noch nicht aktiviert, aber wir arbeiten daran.
Dies ist eine Übersetzung des MySQL-Referenzhandbuchs, das sich auf dev.mysql.com befindet. Das ursprüngliche Referenzhandbuch ist auf Englisch, und diese Übersetzung ist nicht notwendigerweise so aktuell wie die englische Ausgabe. Das vorliegende deutschsprachige Handbuch behandelt MySQL bis zur Version 5.1.