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:

Die Listenpartitionierung in MySQL ähnelt in vieler Hinsicht der Bereichspartitionierung. Wie bei dieser muss jede Partition explizit definiert werden. Der Hauptunterschied besteht darin, dass bei einer Listenpartitionierung die einzelnen Partitionen anhand der Frage gebildet werden, ob ein Spaltenwert in einer von mehreren Wertelisten vorkommt, während bei der Bereichspartitionierung gefragt wird, ob er in einer von mehreren Wertefolgen vorkommt. Diese Form der Partitionierung nehmen Sie mit PARTITION BY LIST(expr) vor, wobei expr ein Spaltenwert oder ein auf einem Spaltenwert basierender Ausdruck ist, der einen Integer zurückgibt. Die einzelnen Partitionen werden sodann durch VALUES IN (value_list) definiert, wobei value_list eine kommagetrennte Liste von Integern ist.

Hinweis: In MySQL 5.1 kann der Spaltenwert bei einer LIST-Partitionierung nur mit einer Integer-Liste verglichen werden.

Im Gegensatz zu Bereichspartitionen müssen Listenpartitionen nicht in einer bestimmten Reihenfolge definiert werden. Genauere Hinweise zur Syntax finden Sie unter Abschnitt 13.1.5, „CREATE TABLE“.

In den nachfolgenden Beispielen gehen wir davon aus, dass die Grunddefinition der zu partitionierenden Tabelle die der nachfolgenden CREATE TABLE-Anweisung ist:

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
);

(Dies ist dieselbe Tabelle, die auch als Grundlage der Beispiele in Abschnitt 17.2.1, „RANGE-Partitionierung“, diente.)

Angenommen, wir haben eine Kette von 20 Videotheken, die auf 4 Franchisenehmer verteilt ist, wie in der folgenden Tabelle gezeigt:

Um diese Tabelle so zu partitionieren, dass jeweils die Zweigstellen einer Region zusammenhängend gespeichert werden, könnten Sie die folgende CREATE TABLE-Anweisung einsetzen:

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 LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

Nun ist es ganz einfach, regionale Angestelltendaten in die Tabelle zu laden oder aus ihr zu löschen. Nehmen wir beispielsweise an, alle Zweigstellen der Region West werden an ein anderes Unternehmen verkauft. Dann könnten alle Angestelltendaten der Zweigstellen dieser Region mit der Anfrage ALTER TABLE employees DROP PARTITION pWest; gelöscht werden, die viel schneller als die entsprechende DELETE-Anfrage DELETE FROM employees WHERE store_id IN (4,12,13,14,18); ausgeführt wird.

Wichtig: Wenn Sie versuchen, eine Zeile einzufügen, deren Spaltenwert (oder Rückgabewert für den Partitionierungsausdruck) in den Listen mit den Partitionierungswerten nicht vorkommt, scheitert die INSERT-Anfrage mit einer Fehlermeldung. So würde beispielsweise die folgende Anfrage bei dem oben skizzierten Schema einer LIST-Partitionierung fehlschlagen:

INSERT INTO employees VALUES 
    (224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);

Dieser Fehler tritt ein, da der store_id-Spaltenwert 21 in keiner der Wertelisten auftritt, die zur Definition der Partitionen pNorth, pEast, pWest und pCentral angegeben wurden. Es ist wichtig zu wissen, dass für Listenpartitionen keine „Catchall“-Definition wie VALUES LESS THAN MAXVALUE existiert, um Werte unterzubringen, die in keiner der Wertelisten auftauchen. Mit anderen Worten: Jeder Partitionierungswert muss in einer der Wertelisten vorhanden sein.

Wie die RANGE-Partitionierung kann auch die LIST-Partitionierung mit einer Hash- oder Schlüsselpartitionierung kombiniert werden, um eine zusammengesetzte Partitionierung (Teilpartitionierung) zu bilden. Siehe Abschnitt 17.2.5, „Unterpartitionen“.

Eine Partitionierung nach HASH wird hauptsächlich eingesetzt, um eine gleichmäßige Verteilung der Daten auf eine im Voraus festgelegte Anzahl von Partitionen zu erzielen. Bei einer Bereichs- oder Listenpartitionierung müssen Sie explizit angeben, in welcher Partition Spaltenwerte gespeichert werden sollen, während MySQL Ihnen dies bei einer Hash-Partitionierung abnimmt. Hier müssen Sie lediglich einen Spaltenwert oder einen auf einem Spaltenwert basierenden Ausdruck für den Hash angeben und sagen, auf wie viele Partitionen die partitionierte Tabelle verteilt werden soll.

Um eine Tabelle mit einer HASH-Partitionierung aufzuteilen, müssen Sie an die CREATE TABLE-Anweisung eine PARTITION BY HASH (expr)-Klausel anfügen, wobei expr ein Ausdruck ist, der einen Integer zurückgibt. Das kann auch einfach der Name einer Spalte sein, die einen der Integer-Typen von MySQL hat. Zusätzlich wird normalerweise noch eine PARTITIONS num-Klausel angefügt, wobei num ein nichtnegativer Integer ist und angibt, auf wie viele Partitionen die Tabelle verteilt werden soll.

Die folgende Anweisung erzeugt beispielsweise eine Tabelle, die einen Hash der Spalte store_id verwendet und auf 4 Partitionen verteilt 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,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

Wenn Sie keine PARTITIONS-Klausel verwenden, ist die Anzahl der Partitionen nach Voreinstellung 1. Ausnahme: Bei NDB Cluster-Tabellen ist die vorgegebene Anzahl der Partitionen gleich der Anzahl der Datenknoten im Cluster, eventuell berichtigt um eine MAX_ROWS-Einstellung, um zu gewährleisten, dass alle Zeilen in die Partitionen hineinpassen. (Siehe Kapitel 16, MySQL Cluster.)

Wenn Sie das Schlüsselwort PARTITIONS ohne darauf folgende Zahlenangabe verwenden, wird ein Syntaxfehler ausgelöst.

Sie können für expr auch einen SQL-Ausdruck einsetzen, der einen Integer zurückliefert. Wenn Sie Ihre Tabelle beispielsweise anhand des Einstellungsjahres der Angestellten partitionieren möchten, gehen Sie folgendermaßen vor:

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 HASH( YEAR(hired) )
PARTITIONS 4;

Sie können für expr alle Funktionen und Ausdrücke verwenden, die in MySQL zulässig sind, vorausgesetzt, der Rückgabewert ist ein nichtkonstanter, nichtzufälliger Integer. (Mit anderen Worten: Der Wert soll variieren, aber deterministisch sein.) Sie müssen allerdings daran denken, dass dieser Ausdruck jedes Mal, wenn eine Zeile eingefügt, aktualisiert oder eventuell auch gelöscht wird, ausgewertet werden muss. Das bedeutet, dass komplexe Ausdrücke die Leistung beeinträchtigen können, insbesondere, wenn Operationen ausgeführt werden, die viele Zeilen auf einmal betreffen (wie etwa Masseneinfügungen).

Am effizientesten sind Hash-Funktionen, die nur eine einzige Tabellenspalte bearbeiten und deren Wert mit dem Spaltenwert konsistent zu- oder abnimmt, da hierdurch ein „Pruning“ von Partitionsbereichen möglich wird. D. h.: Je enger sich der Wert des Ausdrucks an dem Wert der zugrunde liegenden Spalte orientiert, umso effizienter kann MySQL ihn für die Hash-Partitionierung einsetzen.

Wenn beispielsweise date_col eine Spalte vom Typ DATE ist, dann ändert sich der Ausdruck TO_DAYS(date_col) unmittelbar mit dem Wert von date_col, da jede Änderung des Werts von date_col auch den Wert des Ausdrucks ändert, und zwar in völlig konsistenter Form. Der Ausdruck YEAR(date_col) ändert sich bei date_col-Änderungen nicht ganz so unmittelbar wie TO_DAYS(date_col), da nicht jede Änderung in date_col eine entsprechende Änderung in YEAR(date_col) nach sich zieht. Dennoch ist auch YEAR(date_col) ein guter Kandidat für eine Hash-Funktion, da es sich direkt mit einem Teil von date_col ändert und nicht die Gefahr besteht, dass eine Änderung von date_col zu einer unverhältnismäßigen Änderung von YEAR(date_col) führt.

Nehmen wir dagegen an, Sie hätten eine Spalte namens int_col vom Typ INT. Nun betrachten Sie den Ausdruck POW(5-int_col,3) + 6. Dieser wäre ganz schlecht als Hash-Funktion geeignet, da nicht garantiert ist, dass Änderungen von int_col proportionale Änderungen im Wert des Ausdrucks nach sich ziehen. Wenn Sie den Wert von int_col um einen gegebenen Betrag ändern, können dadurch ganz unterschiedliche Änderungen im Wert des Ausdrucks eintreten. Ändern Sie beispielsweise int_col von 5 in 6, so ändert sich der Wert des Ausdrucks um -1, ändern Sie dagegen int_col von 6 in 7, so ändert sich der Wert des Ausdrucks um -7.

Mit anderen Worten: Je enger der Graph des Spaltenwerts im Verhältnis zum Wert des Ausdrucks einer geraden Linie folgt, wie sie durch die Gleichung y=nx vorgegeben ist, wenn n eine von null verschiedene Konstante ist, umso besser eignet sich der Ausdruck für das Hashing. Denn je weniger linear ein Ausdruck ist, umso ungleichmäßiger werden die Daten auf die Partitionen verteilt, die dieser Ausdruck anlegt.

Theoretisch ist bei Ausdrücken, an denen mehrere Spaltenwerte beteiligt sind, auch Pruning möglich, aber es kann schwierig und langwierig sein, herauszufinden, welche dieser Ausdrücke nun wirklich für Pruning geeignet sind. Daher sind Hashing-Ausdrücke mit mehreren Spalten nicht sonderlich zu empfehlen.

Wenn PARTITION BY HASH verwendet wird, ermittelt MySQL anhand des Modulus des Ergebnisses der Benutzerfunktion, welche von num Partitionen verwendet wird. Anders ausgedrückt: Für einen Ausdruck expr wird der Datensatz in der Partition N gespeichert, wobei N = MOD(expr, num) ist. Nehmen wir beispielsweise an, Tabelle t1 ist folgendermaßen definiert, sodass sie 4 Partitionen hat:

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY HASH( YEAR(col3) )
    PARTITIONS 4;

Wenn Sie in t1 einen Datensatz einfügen, dessen col3-Wert '2005-09-15' ist, dann wird die Partition, in der er gespeichert wird, folgendermaßen ermittelt:

MOD(YEAR('2005-09-01'),4)
=  MOD(2005,4)
=  1

MySQL 5.1 unterstützt auch eine Variante der HASH-Partitionierung namens lineares Hashing, die einen komplexen Algorithmus einsetzt, um neu in eine partitionierte Tabelle eingefügte Zeilen zu platzieren. Eine Beschreibung dieses Algorithmus finden Sie unter Abschnitt 17.2.3.1, „LINEAR HASH-Partitionierung“.

Die Benutzerfunktion wird bei jeder Einfügung oder Aktualisierung und unter Umständen auch bei einer Löschung eines Datensatzes ausgewertet.

Hinweis: Wenn die Tabelle, die partitioniert werden soll, einen UNIQUE-Schlüssel hat, müssen Spalten, die als Argumente an die HASH-Benutzerfunktion oder die column_list des KEYs übergeben werden, Teil dieses Schlüssels sein.

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 LINEAR HASH( YEAR(hired) )
PARTITIONS 4;

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY LINEAR HASH( YEAR(col3) )
    PARTITIONS 6;

V = POWER(2, CEILING( LOG(2,7) )) = 8
N = YEAR('2003-04-14') & (8 - 1)
   = 2003 & 7
   = 3

(3 >= 6 is FALSE: record stored in partition #3)

V = 8
N = YEAR('1998-10-19') & (8-1)
  = 1998 & 7
  = 6

(6 >= 6 is TRUE: additional step required)

N = 6 & CEILING(5 / 2)
  = 6 & 3
  = 2

(2 >= 6 is FALSE: record stored in partition #2)

Die Partitionierung durch Schlüssel gleicht der Partitionierung durch Hash, nur dass dort, wo die Hash-Partitionierung einen benutzerdefinierten Ausdruck verwendet, die Schlüsselpartitionierung eine vom MySQL Server gelieferte Hash-Funktion einsetzt. MySQL Cluster verwendet zu diesem Zweck MD5(); für Tabellen, die andere Speicher-Engines benutzen, setzt der Server seine eigene interne Hash-Funktion ein, die auf demselben Algorithmus wie PASSWORD() beruht.

Die Syntaxregeln für CREATE TABLE ... PARTITION BY KEY sind dieselben wie bei der Erstellung einer Hash-partitionierten Tabelle. Die Hauptunterschiede sind:

Es ist auch möglich, eine Tabelle durch linearen Schlüssel zu partitionieren. Hier sehen Sie ein einfaches Beispiel:

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
) 
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

Die Verwendung von LINEAR hat auf die KEY-Partitionierung denselben Effekt wie auf die HASH-Partitionierung, wobei die Partitionsnummer mit einem Zweierpotenz-Algorithmus anstatt mit Modulo-Arithmetik abgeleitet wird. Eine Beschreibung dieses Algorithmus und seiner Implikationen finden Sie unter Abschnitt 17.2.3.1, „LINEAR HASH-Partitionierung“.

Teilpartitionierung, auch als zusammengesetzte Partitionierung bezeichnet, ist die weitere Unterteilung von Partitionen einer partitionierten Tabelle. Betrachten Sie als Beispiel die folgende CREATE TABLE-Anweisung:

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE(YEAR(purchased))
    SUBPARTITION BY HASH(TO_DAYS(purchased))
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

Die Tabelle ts hat 3 RANGE-Partitionen. Jede dieser Partitionen, also p0, p1 und p2, ist ihrerseits in 2 Teilpartitionen unterteilt. Im Endeffekt ist die gesamte Tabelle auf 3 * 2 = 6 Partitionen verteilt. Durch die PARTITION BY RANGE-Klausel speichern allerdings die ersten beiden dieser Partitionen nur Datensätze, die in der purchased-Spalte einen Wert kleiner als 1990 aufweisen.

In MySQL 5.1 können Sie Tabellen, die durch RANGE oder LIST partitioniert werden, noch weiter aufteilen. Teilpartitionen können entweder die HASH- oder die KEY-Partitionierung verwenden. Dies bezeichnet man auch als zusammengesetzte Partitionierung.

Außerdem ist es möglich, Teilpartitionen explizit mit SUBPARTITION-Klauseln zu definieren, um Optionen für einzelne Teilpartitionen angeben zu können. So könnte man dieselbe ts-Tabelle wie im vorigen Beispiel auch wortreicher erzeugen:

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

An dieser Syntax ist Folgendes bemerkenswert:

Teilpartitionen können bei extrem großen Tabellen helfen, die Daten und Indizes über viele Festplatten zu verteilen. Angenommen, Sie haben 6 Festplatten als /disk0, /disk1, /disk2 und so weiter gemountet. Nun schauen Sie sich folgendes Beispiel an:

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0 
                DATA DIRECTORY = '/disk0/data' 
                INDEX DIRECTORY = '/disk0/idx',
            SUBPARTITION s1 
                DATA DIRECTORY = '/disk1/data' 
                INDEX DIRECTORY = '/disk1/idx'
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s0 
                DATA DIRECTORY = '/disk2/data' 
                INDEX DIRECTORY = '/disk2/idx',
            SUBPARTITION s1 
                DATA DIRECTORY = '/disk3/data' 
                INDEX DIRECTORY = '/disk3/idx'
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s0 
                DATA DIRECTORY = '/disk4/data' 
                INDEX DIRECTORY = '/disk4/idx',
            SUBPARTITION s1 
                DATA DIRECTORY = '/disk5/data' 
                INDEX DIRECTORY = '/disk5/idx'
        )
    );

In diesem Fall wird für die Daten und die Indizes jedes RANGE eine eigene Festplatte genutzt. Es sind aber auch viele andere Varianten möglich; ein anderes Beispiel wäre:

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE(YEAR(purchased))
    SUBPARTITION BY HASH(TO_DAYS(purchased)) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0a 
                DATA DIRECTORY = '/disk0' 
                INDEX DIRECTORY = '/disk1',
            SUBPARTITION s0b 
                DATA DIRECTORY = '/disk2' 
                INDEX DIRECTORY = '/disk3'
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s1a 
                DATA DIRECTORY = '/disk4/data' 
                INDEX DIRECTORY = '/disk4/idx',
            SUBPARTITION s1b 
                DATA DIRECTORY = '/disk5/data' 
                INDEX DIRECTORY = '/disk5/idx'
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s2a,
            SUBPARTITION s2b
        )
    );

Hier wird nach folgenden Regeln gespeichert:

Die Partitionierung in MySQL gestattet durchaus auch einen Partitionierungsausdruck mit dem Wert NULL, sei es nun ein Spaltenwert oder der Wert eines vom Benutzer angegebenen Ausdrucks. Normalerweise behandelt MySQL NULL in solchen Fällen als null. Wenn Sie dieses Verhalten unterbinden möchten, müssen Sie Tabellen so entwerfen, dass sie keine Nullwerte erlauben, indem Sie die Spalten als NOT NULL deklarieren.

In diesem Abschnitt geben wir einige Beispiele, die zeigen sollen, wie MySQL NULL-Werte verarbeitet, wenn die passende Partition für eine Zeile ermittelt werden soll.

Wenn Sie eine Zeile in eine RANGE- oder LIST-partitionierte Tabelle einfügen und der Spaltenwert, der zur Bestimmung der Partition herangezogen wird, NULL ist, so wird dieser Wert als 0 interpretiert. Betrachten Sie beispielsweise die folgenden beiden Tabellen:

mysql> CREATE TABLE tnlist (
    ->     id INT,
    ->     name VARCHAR(5)
    -> )
    -> PARTITION BY LIST(id) (
    ->     PARTITION p1 VALUES IN (0),
    ->     PARTITION p2 VALUES IN (1)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE tnrange (
    ->     id INT,
    ->     name VARCHAR(5)
    -> )
    -> PARTITION BY RANGE(id) (
    ->     PARTITION p1 VALUES LESS THAN (1),
    ->     PARTITION p2 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO tnlist VALUES (NULL, 'bob');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tnrange VALUES (NULL, 'jim');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tnlist;
+------+------+
| id   | name |
+------+------+
| NULL | bob  |
+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tnrange;
+------+------+
| id   | name |
+------+------+
| NULL | jim  |
+------+------+
1 row in set (0.00 sec)

In beiden Tabellen war die Spalte id nicht als NOT NULL deklariert, kann also NULL-Werte annehmen. Dass die Zeilen in den p1-Partitionen der Tabellen gespeichert wurden, können Sie überprüfen, indem Sie diese Partitionen löschen und dann die SELECT-Anweisungen erneut ausführen:

mysql> ALTER TABLE tnlist DROP PARTITION p1;
Query OK, 0 rows affected (0.16 sec)

mysql> ALTER TABLE tnrange DROP PARTITION p1;
Query OK, 0 rows affected (0.16 sec)

mysql> SELECT * FROM tnlist;
Empty set (0.00 sec)

mysql> SELECT * FROM tnrange;
Empty set (0.00 sec)

Bei einer HASH- oder KEY-Partitionierung wird ein Partitionierungsausdruck, der NULL ergibt, so behandelt, als sei sein Rückgabewert null. Dieses Verhalten können wir überprüfen, indem wir betrachten, wie sich die Erstellung einer HASH-partitionierten Tabelle, in die eine Zeile mit den entsprechenden Daten geladen wird, auf das Dateisystem auswirkt. Angenommen, Sie haben eine Tabelle namens tnhash in der Datenbank test mit folgender Anweisung angelegt:

CREATE TABLE tnhash (
    id INT,
    name VARCHAR(5)
)
PARTITION BY HASH(id)
PARTITIONS 2;

Wenn wir eine RPM-Installation von MySQL auf Linux zugrunde legen, erzeugt diese Anweisung zwei .MYD-Dateien in /var/lib/mysql/test, die in der bash-Shell wie folgt angezeigt werden können:

/var/lib/mysql/test> ls *.MYD -l
-rw-rw----  1 mysql mysql 0 2005-11-04 18:41 tnhash#P#p0.MYD
-rw-rw----  1 mysql mysql 0 2005-11-04 18:41 tnhash#P#p1.MYD

(Hinweis: Vor MySQL 5.1.5 hätten diese Dateien tnhash_p0.MYD und tnhash_p1.MYD geheißen. Unter Abschnitt D.1.1, „Änderungen in Release 5.1.6 (Noch nicht veröffentlicht)“, und Bug#13437 finden Sie weitere Informationen darüber, wie sich diese Änderung auf Upgrades auswirkt.)

Beachten Sie, dass die beiden Dateien 0 Byte groß sind. Fügen Sie nun in tnhash eine Zeile ein, die in der Spalte id den Wert NULL aufweist, und überprüfen Sie, ob die Zeile tatsächlich eingefügt wurde:

mysql> INSERT INTO tnhash VALUES (NULL, 'sam');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tnhash;
+------+------+
| id   | name |
+------+------+
| NULL | sam  |
+------+------+
1 row in set (0.01 sec)

Bitte erinnern Sie sich, dass für einen Integer N der Wert von NULL MOD N immer NULL ist. Dieses Ergebnis wird behandelt, um 0 als die korrekte Partition festzulegen. Wenn wir nun wieder in die System-Shell gehen (wobei weiterhin bash zugrunde gelegt wird), können wir erkennen, dass der Wert in die erste Partition (die nach Voreinstellung p0 heißt) eingefügt wurde, indem wir die Datendateien erneut auflisten:

var/lib/mysql/test> ls *.MYD -l
-rw-rw----  1 mysql mysql 20 2005-11-04 18:44 tnhash#P#p0.MYD
-rw-rw----  1 mysql mysql  0 2005-11-04 18:41 tnhash#P#p1.MYD

Wie Sie sehen, hat die INSERT-Anweisung nur die Datei tnhash_p0.MYD modifiziert, deren Umfang auf der Platte angewachsen ist, ohne auf die andere Datendatei Einfluss zu nehmen.

Nehmen wir nun an, wir hätten folgende Tabelle:

CREATE TABLE tndate (
    id INT,
    dt DATE
)
PARTITION BY RANGE( YEAR(dt) ) (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

Wie andere MySQL-Funktionen gibt auch YEAR(NULL) den Wert NULL zurück. Eine Zeile, die in der dt-Spalte den Wert NULL hat, wird behandelt, als wäre der Partitionierungsausdruck in Wirklichkeit 0, und folglich in die Partition p0 eingefügt.

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 TABLE tbl_name 
    REORGANIZE PARTITION partition_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:

Die Partitionierung von Hash- oder schlüsselpartitionierten Tabellen lässt sich in ähnlicher Weise ändern, wobei sich Tabellen mit diesen beiden Formen der Partitionierung in mehrerer Hinsicht von bereichs- oder listenpartitionierten Tabellen unterscheiden. Daher werden in diesem Abschnitt nur Änderungen von Hash- oder schlüsselpartitionierten Tabellen behandelt. Das Hinzufügen und Löschen der Partitionen von bereichs- oder listenpartitionierten Tabellen wird in Abschnitt 17.3.1, „Verwaltung von RANGE- und LIST-Partitionen“, beschrieben.

Sie können Partitionen aus HASH- oder KEY-partitionierten Tabellen nicht genauso löschen wie aus RANGE- oder LIST-partitionierten Tabellen. Doch zum Zusammenführen von HASH- oder KEY-partitionierten Tabellen können Sie ebenfalls den ALTER TABLE ... COALESCE PARTITION-Befehl verwenden. Angenommen, Sie haben eine Tabelle mit Kundendaten, die in 12 Partitionen aufgeteilt ist. Diese clients-Tabelle ist folgendermaßen definiert:

CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;

Um die Anzahl der Partitionen von 12 auf 6 zu reduzieren, führen Sie folgenden ALTER TABLE-Befehl aus:

mysql> ALTER TABLE clients COALESCE PARTITION 6;
Query OK, 0 rows affected (0.02 sec)

COALESCE funktioniert genauso gut mit HASH-, KEY-, LINEAR HASH- oder LINEAR KEY-partitionierten Tabellen. Hier sehen Sie ein ähnliches Beispiel wie oben, allerdings dieses Mal mit einer Tabelle, die nach LINEAR KEY partitioniert ist:

mysql> CREATE TABLE clients_lk (
    ->     id INT,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30),
    ->     signed DATE
    -> )
    -> PARTITION BY LINEAR KEY(signed)
    -> PARTITIONS 12;
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE clients_lk COALESCE PARTITION 6;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

COALESCE kann nicht verwendet werden, um die Anzahl der Partitionen zu erhöhen. Ein Versuch, dies zu tun, verursacht folgenden Fehler:

mysql> ALTER TABLE clients COALESCE PARTITION 18;
ERROR 1478 (HY000): Cannot remove all Partitionen, use DROP TABLE instead

Um die Anzahl der Partitionen der clients-Tabelle von 12 auf 18 zu erhöhen, erteilen Sie folgende ALTER TABLE ... ADD PARTITION-Anweisung:

ALTER TABLE clients ADD PARTITION PARTITIONS 18;

Hinweis: ALTER TABLE ... REORGANIZE PARTITION kann nicht für HASH- oder KEY-partitionierte Tabellen verwendet werden.

Hinweis: Die Befehle, die dieser Abschnitt beschreibt, sind in MySQL 5.1 noch nicht implementiert. Sie werden vor allem vorgestellt, um Feedback von Benutzern zu bekommen, die die Software während des Entwicklungszyklus der Version 5.1 vor der Produktionsreife testen. (Mit anderen Worten: Bitte schicken Sie uns keine Bugreports mit dem Hinweis, dass diese Befehle nicht funktionieren.) Die Informationen in diesem Abschnitt ändern sich noch ständig, da die Entwicklung der Partitionierung für MySQL 5.1 weitergeht. Wir werden den Abschnitt aktualisieren, wenn Partitionierungsfeatures implementiert oder verbessert werden.

MySQL 5.1 ermöglicht viele Wartungsarbeiten im Bereich der Partitionierung. MySQL unterstützt für partitionierte Tabellen nicht die Befehle CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE oder REPAIR TABLE. Dagegen können Sie jedoch eine Reihe von Erweiterungen des ALTER TABLE-Befehls einsetzen, um diese Operationen auf einer oder mehreren Partitionen direkt auszuführen. Diese Erweiterungen sind im Folgenden dargestellt:

Diese Aufgaben können Sie auch lösen, indem Sie mysqlcheck oder myisamchk auf den separaten .MYI-Dateien ausführen, die bei der Partitionierung einer Tabelle generiert werden. Siehe Abschnitt 8.9, „mysqlcheck — Hilfsprogramm für die Wartung und Reparatur von Tabellen“. (Diese Möglichkeit steht auch bereits im Pre-Alpha-Code zur Verfügung.)

Dieser Abschnitt beschreibt, wie Sie sich Informationen über bestehende Partitionen beschaffen können. Da sich diese Funktionalität noch in der Planung befindet, sind die folgenden Ausführungen zurzeit nur eine Absichtserklärung über die Dinge, die wir in MySQL 5.1 implementieren wollen.

Wie bereits an anderer Stelle in diesem Kapitel gesagt, zeigt die Ausgabe von SHOW CREATE TABLE auch eine PARTITION BY-Klausel an, mit der eine partitionierte Tabelle angelegt wurde. Zum Beispiel:

mysql> SHOW CREATE TABLE trb3\G
*************************** 1. row ***************************
       Table: trb3
Create Table: CREATE TABLE `trb3` (
  `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 p2 VALUES LESS THAN (2000) ENGINE = MyISAM, 
  PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
)
1 row in set (0.00 sec)

Hinweis: In frühen MySQL 5.1-Releases wurde die PARTITIONS-Klausel nicht für Tabellen angezeigt, die nach HASH oder KEY partitioniert waren. Dies wurde in MySQL 5.1.6 behoben.

SHOW TABLE STATUS funktioniert auch für partitionierte Tabellen und hat dieselbe Ausgabe wie für nichtpartitionierte Tabellen, nur dass die Spalte Engine immer den Wert 'PARTITION' hat. (Mehr über diesen Befehl erfahren Sie in Abschnitt 13.5.4.21, „SHOW TABLE STATUS“.)

Informationen über Partitionen liefert Ihnen auch das INFORMATION_SCHEMA, zu dem auch eine PARTITIONS-Tabelle gehört. Siehe Abschnitt 22.19, „Die Tabelle INFORMATION_SCHEMA PARTITIONS“.

Seit MySQL 5.1.5 können Sie mit EXPLAIN PARTITIONS herausfinden, welche Partitionen einer partitionierten Tabelle an einer SELECT-Anfrage beteiligt sind. Das Schlüsselwort PARTITIONS fügt der Ausgabe von EXPLAIN eine partitions-Spalte hinzu, in der die Partitionen aufgeführt sind, in denen die Anfrage Datensätze erkannt hat.

Angenommen, Sie haben eine Tabelle trb1, die folgendermaßen definiert und mit Daten gefüllt ist:

CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)
    PARTITION BY RANGE(id)
    (
        PARTITION p0 VALUES LESS THAN (3),
        PARTITION p1 VALUES LESS THAN (7),
        PARTITION p2 VALUES LESS THAN (9),
        PARTITION p3 VALUES LESS THAN (11)
    );

INSERT INTO trb1 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');

Mit folgendem Befehl können Sie sich darüber informieren, welche Partitionen in einer Anfrage wie SELECT * FROM trb1; benutzt werden:

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trb1
   partitions: p0,p1,p2,p3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using filesort

In diesem Fall wurden 4 vier Partitionen durchsucht. Wenn Sie der Anfrage jedoch eine einschränkende Bedingung hinzufügen, die den Partitionierungsschlüssel enthält, können Sie erkennen, dass nur diejenigen Partitionen gescannt werden, die passende Werte enthalten:

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trb1
   partitions: p0,p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where

EXPLAIN PARTITIONS informiert über verwendete und mögliche Schlüssel ebenso wie die standardmäßige EXPLAIN SELECT-Anweisung:

mysql> ALTER TABLE trb1 ADD PRIMARY KEY (id);
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trb1
   partitions: p0,p1
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 7
        Extra: Using where

Beachten Sie jedoch folgende Restriktionen von EXPLAIN PARTITIONS: