Da das Hinzufügen und Löschen von Partitionen für Bereichs-
und Listenpartitionen ganz ähnlich behandelt wird, fassen wir
die Verwaltung dieser beiden Partitionierungsarten im
vorliegenden Abschnitt zusammen. Über den Umgang mit Hash- oder
schlüsselpartitionierten Tabellen erfahren Sie unter
Abschnitt 17.3.2, „Verwaltung von HASH
- und
KEY
-Partitionen“, Genaueres.
Weil das Löschen einer RANGE
- oder
LIST
--Partition einfacher ist als das
Hinzufügen, beginnen wir mit dem Löschen.
Um eine Partition aus einer RANGE
- oder
LIST
-partitionierten Tabelle zu löschen,
verwenden Sie den ALTER TABLE
-Befehl mit
einer DROP PARTITION
-Klausel. Das folgende
sehr einfach gehaltene Beispiel geht davon aus, dass Sie mit den
folgenden CREATE TABLE
- und
INSERT
-Anweisungen bereits eine nach Bereich
partitionierte Tabelle angelegt und mit 10 Datensätzen
bevölkert haben:
mysql>CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
->PARTITION BY RANGE( YEAR(purchased) ) (
->PARTITION p0 VALUES LESS THAN (1990),
->PARTITION p1 VALUES LESS THAN (1995),
->PARTITION p2 VALUES LESS THAN (2000),
->PARTITION p3 VALUES LESS THAN (2005)
->);
Query OK, 0 rows affected (0.01 sec) mysql>INSERT INTO tr VALUES
->(1, 'desk organiser', '2003-10-15'),
->(2, 'CD player', '1993-11-05'),
->(3, 'TV set', '1996-03-10'),
->(4, 'bookcase', '1982-01-10'),
->(5, 'exercise bike', '2004-05-09'),
->(6, 'sofa', '1987-06-05'),
->(7, 'popcorn maker', '2001-11-22'),
->(8, 'aquarium', '1992-08-04'),
->(9, 'study desk', '1984-09-16'),
->(10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.01 sec)
Welche Daten in die Partition p2
geladen
wurden, sehen Sie hier:
mysql>SELECT * FROM tr
->WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+------+-----------+------------+ | id | name | purchased | +------+-----------+------------+ | 3 | TV set | 1996-03-10 | | 10 | lava lamp | 1998-12-25 | +------+-----------+------------+ 2 rows in set (0.00 sec)
Um nun die Partition p2
zu löschen, führen
Sie folgenden Befehl aus:
mysql> ALTER TABLE tr DROP PARTITION p2;
Query OK, 0 rows affected (0.03 sec)
Hinweis: Die Speicher-Engine NDB Cluster
in
MySQL 5.1 kennt kein ALTER TABLE ... DROP
PARTITION
. Doch immerhin unterstützt sie die anderen
in diesem Kapitel beschriebenen Partitionierungserweiterungen
der ALTER TABLE
-Anweisung.
Es ist sehr wichtig, sich zu merken, dass Sie beim
Löschen einer Partition alle darin gespeicherten Daten mit
löschen. Dies erkennen Sie, wenn Sie die obige
SELECT
-Anfrage erneut ausführen:
mysql>SELECT * FROM tr WHERE purchased
->BETWEEN '1995-01-01' AND '1999-12-31';
Empty set (0.00 sec)
Wenn Sie alle Daten aus allen Partitionen löschen, aber die
Tabellendefinition und ihr Partitionierungsschema erhalten
möchten, verwenden Sie den Befehl TRUNCATE
TABLE
. (Siehe auch Abschnitt 13.2.9, „TRUNCATE
“.)
Wenn Sie die Partitionierung einer Tabelle ändern möchten,
ohne Daten zu verlieren, verwenden Sie
stattdessen ALTER TABLE ... REORGANIZE
PARTITION
. Weiter unten oder in
Abschnitt 13.1.2, „ALTER TABLE
“, finden Sie Informationen über
REORGANIZE PARTITION
.
Wenn Sie nun einen SHOW CREATE TABLE
-Befehl
ausführen, können Sie sehen, wie sich die Partitionierung der
Tabelle geändert hat:
mysql> SHOW CREATE TABLE tr\G
*************************** 1. row ***************************
Table: tr
Create Table: CREATE TABLE `tr` (
`id` int(11) default NULL,
`name` varchar(50) default NULL,
`purchased` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE ( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
)
1 row in set (0.01 sec)
Wenn Sie nun in die geänderte Tabelle Zeilen einfügen, die in
der Spalte purchased
Werte zwischen
'1995-01-01'
und
'2004-12-31'
einschließlich aufweisen,
werden diese Zeilen in der Partition p3
gespeichert. Dies können Sie folgendermaßen überprüfen:
mysql>INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');
Query OK, 1 row affected (0.00 sec) mysql>SELECT * FROM tr WHERE purchased
->BETWEEN '1995-01-01' AND '2004-12-31';
+------+----------------+------------+ | id | name | purchased | +------+----------------+------------+ | 11 | pencil holder | 1995-07-12 | | 1 | desk organiser | 2003-10-15 | | 5 | exercise bike | 2004-05-09 | | 7 | popcorn maker | 2001-11-22 | +------+----------------+------------+ 4 rows in set (0.00 sec) mysql>ALTER TABLE tr DROP PARTITION p3;
Query OK, 0 rows affected (0.03 sec) mysql>SELECT * FROM tr WHERE purchased
->BETWEEN '1995-01-01' AND '2004-12-31';
Empty set (0.00 sec)
Beachten Sie, dass die Anzahl der Zeilen, die aus der Tabelle
mit ALTER TABLE ... DROP PARTITION
gelöscht
wurden, vom Server nicht so gemeldet wird, wie es bei einer
entsprechenden DELETE
-Anweisung der Fall
wäre.
Zum Löschen von LIST
-Partitionen wird genau
dieselbe ALTER TABLE ... DROP
PARTITION
-Syntax verwendet wie zum Löschen von
RANGE
-Partitionen. Allerdings unterscheiden
sich die Auswirkungen, die dieses auf den späteren Gebrauch der
Tabelle hat, in einer wichtigen Hinsicht: In die Tabelle können
nun keine Zeilen mehr eingefügt werden, die irgendwelche in der
Werteliste der gelöschten Partition vorkommenden Werte
aufweisen. (Ein Beispiel finden Sie unter
Abschnitt 17.2.2, „LIST
-Partitionierung“.)
Um einer zuvor partitionierten Tabelle eine neue Bereichs- oder
Listenpartition hinzuzufügen, verwenden Sie die ALTER
TABLE ... ADD PARTITION
-Anweisung. Für Tabellen, die
nach RANGE
partitioniert werden, können Sie
mit dieser Anweisung einen neuen Wertebereich am Anfang oder
Ende der Liste der vorhandenen Partitionen hinzufügen.
Angenommen, Sie haben eine partitionierte Tabelle mit
Mitgliedsdaten Ihrer Organisation wie folgt definiert:
CREATE TABLE members ( id INT, fname VARCHAR(25), lname VARCHAR(25), dob DATE ) PARTITION BY RANGE( YEAR(dob) ) ( PARTITION p0 VALUES LESS THAN (1970), PARTITION p1 VALUES LESS THAN (1980), PARTITION p2 VALUES LESS THAN (1990) );
Nehmen wir nun weiterhin an, das Mindestalter der Mitglieder ist
16 Jahre. Da es bereits auf Ende 2005 zugeht, stellen Sie fest,
dass Sie demnächst Mitglieder aufnehmen müssen, die 1990
geboren wurden, und in den darauf folgenden Jahren wiederum
Mitglieder, die noch später zur Welt kamen. Mit folgender
Anweisung können Sie die members
-Tabelle so
umändern, dass auch Mitglieder zulässig sind, die in den
Jahren 1990 bis 1999 geboren wurden:
ALTER TABLE ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));
Wichtig: Tabellen, die nach
Bereich partitioniert wurden, können Sie mit dem Befehl
ADD PARTITION
nur am oberen Ende der
Wertebereichsliste neue Partitionen hinzufügen. Wenn Sie
versuchen, auf diesem Weg Partitionen zwischen oder vor
vorhandenen Partitionen einzufügen, wird folgender Fehler
generiert:
mysql>ALTER TABLE members
>ADD PARTITION (
>PARTITION p3 VALUES LESS THAN (1960));
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
In ähnlicher Weise können Sie auch einer
LIST
-partitionierten Tabelle neue Partitionen
hinzufügen. Nehmen wir als Beispiel folgende Tabelle:
CREATE TABLE tt ( id INT, data INT ) PARTITION BY LIST(data) ( PARTITION p0 VALUES IN (5, 10, 15), PARTITION p1 VALUES IN (6, 12, 18) );
Mit der folgenden Anweisung können Sie eine neue Partition für
Zeilen einrichten, deren data
-Spalten die
Werte 7
, 14
und
21
aufweisen:
ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));
Achtung: Sie können keine neue
LIST
-Partition hinzufügen, die irgendwelche
bereits in den Wertelisten bestehender Partitionen enthaltenen
Werte aufweist. Wenn Sie dieses versuchen, wird ein Fehler
gemeldet:
mysql>ALTER TABLE tt ADD PARTITION
>(PARTITION np VALUES IN (4, 8, 12));
ERROR 1465 (HY000): Multiple definition of same constant in list partitioning
Da Zeilen mit dem Wert 12
in der
data
-Spalte bereits der Partition
p1
zugewiesen sind, können Sie nicht in
Tabelle tt
eine neue Partition anlegen, die
ebenfalls 12
in ihrer Werteliste hat. Wenn
Sie dies dennoch tun müssen, löschen Sie zuerst
p1
und fügen dann np
und
danach eine neue p1
mit einer modifizierten
Definition hinzu. Allerdings würden dadurch, wie zuvor bereits
gesagt, alle in p1
gespeicherten Daten
verloren gehen, was im Allgemeinen nicht der angestrebte Effekt
ist. Eine andere Lösung könnte so aussehen, dass Sie eine
Kopie der Tabelle mit der neuen Partitionierung anlegen, dann
die Daten mit einer CREATE TABLE ... SELECT
...
hineinkopieren und schließlich die alte Tabelle
löschen und die neue umbenennen. Wenn Sie es mit großen
Datenmengen zu tun haben, könnte dies allerdings eine
zeitraubende Angelegenheit werden, die dort, wo
Hochverfügbarkeit gewährleistet werden muss, vielleicht
vollends unmöglich ist.
Seit MySQL 5.1.6 können Sie mehrere Partitionen in einer
einzigen ALTER TABLE ... ADD
PARTITION
-Anweisung anlegen:
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, hired DATE NOT NULL ) PARTITION BY RANGE( YEAR(hired) ) ( PARTITION p1 VALUES LESS THAN (1991), PARTITION p2 VALUES LESS THAN (1996), PARTITION p3 VALUES LESS THAN (2001), PARTITION p4 VALUES LESS THAN (2005) ); ALTER TABLE employees ADD PARTITION ( PARTITION p5 VALUES LESS THAN (2010), PARTITION p6 VALUES LESS THAN MAXVALUE );
Zum Glück bietet die Implementierung der Partitionierung in
MySQL Möglichkeiten, Partitionen ohne Datenverlust
umzudefinieren. Betrachten wir zunächst eine Reihe von
einfachen Beispielen zur
RANGE
-Partitionierung. Bitte erinnern Sie
sich an die members
-Tabelle, die nun
folgendermaßen definiert ist:
mysql> SHOW CREATE TABLE members\G
*************************** 1. row ***************************
Table: members
Create Table: CREATE TABLE `members` (
`id` int(11) default NULL,
`fname` varchar(25) default NULL,
`lname` varchar(25) default NULL,
`dob` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE ( YEAR(dob) ) (
PARTITION p0 VALUES LESS THAN (1970) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM.
PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM
)
Angenommen, Sie möchten alle Zeilen der Mitglieder, die vor
1960 geboren wurden, in eine separate Partition speichern. Wir
wir bereits sahen, ist dies mit der Anweisung ALTER
TABLE ... ADD PARTITION
nicht möglich. Sie können
jedoch eine andere Partitionierungserweiterung von
ALTER TABLE
benutzen, um dies zu erreichen:
ALTER TABLE members REORGANIZE PARTITION p0 INTO ( PARTITION s0 VALUES LESS THAN (1960), PARTITION s1 VALUES LESS THAN (1970) );
Im Endeffekt spaltet dieser Befehl die Partition
p0
in zwei neue Partitionen
s0
und s1
auf. Außerdem
werden die bisher in p0
gespeicherten Daten
nach den Regeln der beiden PARTITION ... VALUES
...
-Klauseln auf die neuen Partitionen verteilt,
sodass s0
nur Datensätze aufnimmt, deren
YEAR(dob)
-Wert kleiner als 1960 ist, und
s1
nur Datensätze, deren
YEAR(dob)
-Wert größer oder gleich 1960,
aber kleiner als 1970 ist.
Sie können auch mit einer REORGANIZE
PARTITION
-Klausel benachbartete Partitionen
verschmelzen. Mit dem folgenden Befehl stellen Sie die
members
-Tabelle wieder auf ihre vorherige
Partitionierung um:
ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO ( PARTITION p0 VALUES LESS THAN (1970) );
Wenn Sie Partitionen mit REORGANIZE PARTITION
aufteilen oder zusammenführen, gehen keine Daten verloren. Bei
der Ausführung dieser Anweisung verschiebt MySQL alle
Datensätze, die zuvor in den Partitionen s0
und s1
gespeichert waren, wieder in die
Partition p0
.
REORGANIZE PARTITION
hat folgende allgemeine
Syntax:
ALTER TABLEtbl_name
REORGANIZE PARTITIONpartition_list
INTO (partition_definitions
);
tbl_name
ist hier der Name der
partitionierten Tabelle und
partition_list
ist eine
kommagetrennte Liste mit den Namen einer oder mehrerer
vorhandener Partitionen, die geändert werden sollen.
partition_definitions
ist eine
kommagetrennte Liste neuer Partitionsdefinitionen, für die
dieselben Regeln gelten, wie die
partition_definitions
einer
CREATE TABLE
-Anweisung (siehe
Abschnitt 13.1.5, „CREATE TABLE
“). Doch REORGANIZE
PARTITION
kann noch mehr, als nur Partitionen
aufspalten oder zusammenführen: Mit demselben Befehl können
Sie auch beispielsweise aus den vier Partitionen der
members
-Tabelle zwei machen:
ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO ( PARTITION m0 VALUES LESS THAN (1980), PARTITION m1 VALUES LESS THAN (2000) );
REORGANIZE PARTITION
ist auch für
LIST
-partitionierte Tabellen geeignet.
Greifen wir noch einmal das Problem auf, wie man eine neue
Partition zu der listenpartitionierten Tabelle
tt
hinzufügen könnte. Der Versuch
scheiterte, weil die neue Partition einen Wert hatte, der
bereits in den Wertelisten der vorhandenen Partitionen
auftauchte. Dies können wir umgehen, indem wir eine Partition
hinzufügen, deren Liste nur zulässige Werte enthält, und dann
die neue und die vorhandenen Partitionen so umorganisieren, dass
der konfliktbeladene Wert aus der Werteliste der alten in die
Werteliste der neuen Partition übertragen wird:
ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8)); ALTER TABLE tt REORGANIZE PARTITION p1,np INTO ( PARTITION p1 VALUES IN (6, 18), PARTITION np VALUES in (4, 8, 12) );
Die folgenden Punkte sind wichtig, wenn Sie ALTER TABLE
... REORGANIZE PARTITION
zur Neupartitionierung von
RANGE
- oder
LIST
-partitionierten Tabellen einsetzen:
Für die PARTITION
-Klauseln, mit denen
das neue Partitionierungsschema festgelegt wird, gelten
dieselben Regeln wie für die, die in einer CREATE
TABLE
-Anweisung benutzt werden.
Zuallererst müssen Sie daran denken, dass das neue
Partitionierungsschema keine sich überschneidenden
Wertebereiche (RANGE
-partitionierte
Tabellen) bzw. keine Überschneidungen in den Wertemengen
(LIST
-partitionierte Tabellen) haben
darf.
Hinweis: Vor MySQL 5.1.4
konnten Sie die Namen vorhandener Partitionen nicht in der
INTO
-Klausel wiederverwenden, selbst wenn
diese Partitionen gelöscht oder umdefiniert werden sollten.
Weitere Informationen gibt es unter
Abschnitt D.1.3, „Änderungen in Release 5.1.4 (21. Dezember 2005)“.
Die Kombination der Partitionen in der
partition_definitions
-Liste
sollte insgesamt denselben Wertebereich oder dieselbe
Wertemenge abdecken wie die Kombination der Partitionen, die
in der partition_list
aufgeführt
sind.
In der members
-Tabelle, die in diesem
Abschnitt als Beispiel dient, decken zum Beispiel die
Partitionen p1
und p2
zusammen die Jahre 1980 bis 1999 ab. Daher muss jede
Neuorganisation dieser beiden Partitionen insgesamt
denselben Zeitraum in Jahren abdecken.
In RANGE
-partitionierten Tabellen können
Sie nur benachbarte Partitionen reorganisieren, da keine
Bereichspartitionen übersprungen werden dürfen.
Sie können beispielsweise die
members
-Tabelle nicht mit einer Anweisung
reorganisieren, die mit ALTER TABLE members
REORGANIZE PARTITION p0,p2 INTO ...
anfängt, da
p0
die Jahre vor 1970 und
p2
die Jahre von 1990 bis einschließlich
1999 umfasst und somit die beiden Partitionen nicht
benachbart sind.
Sie können REORGANIZE PARTITION
nicht
einsetzen, um den Partitionierungstyp einer Tabelle zu
ändern, also beispielsweise nicht aus
RANGE
-Partitionen
HASH
-Partitionen oder umgekehrt machen.
Auch können Sie diesen Befehl nicht verwenden, um den
Partitionierungsausdruck oder die Partitionierungsspalte zu
ändern. Um dieses zu tun, ohne die Tabelle löschen und
rekonstruieren zu müssen, verwenden Sie ALTER
TABLE ... PARTITION BY ...
. Zum Beispiel:
ALTER TABLE members PARTITION BY HASH( YEAR(dob) ) PARTITIONS 8;
Hinweis: In MySQL 5.1
5.1.5-alpha ist ALTER TABLE ... PARTITION BY
...
noch nicht implementiert. Stattdessen müssen
Sie die Tabelle entweder löschen und mit der gewünschten
Partitionierung neu erzeugen oder, wenn Sie die Daten der
Tabelle bewahren möchten, mit CREATE TABLE ...
SELECT ...
die neue Tabelle anlegen und die Daten
aus der alten herüberkopieren, um anschließend die alte
Tabelle zu löschen und in einem letzten Schritt die neue
Tabelle umzubenennen, wenn dies gewünscht wird.
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.