-- MySQL-Datenbank, keine Null-Datenbank. 看完官方文档做的笔记.
-- SCHLÜSSEL-Partitionierung
Partitionierung nach Schlüssel ähnelt der Partitionierung nach Hash, mit der Ausnahme, dass bei der Hash-Partitionierung eine benutzerdefinierte Partitionierung verwendet wird Ausdruck wird die Hashing-Funktion für die Schlüsselpartitionierung vom MySQL-Server bereitgestellt. Diese interne Hashing-Funktion basiert auf dem
Gleicher Algorithmus wie PASSWORD().
KEY wird anstelle von HASH verwendet.
KEY akzeptiert nur eine Liste mit einem oder mehreren Spaltennamen. Die als Partitionierungsschlüssel verwendete(n) Spalte(n) muss(en) einen Teil oder den gesamten Primärschlüssel der Tabelle umfassen, sofern die Tabelle einen hat.
KEY nimmt eine Liste mit null oder mehr Spaltennamen an. Wenn kein Spaltenname als Partitionierungsschlüssel angegeben ist, wird der Primärschlüssel der Tabelle verwendet, sofern vorhanden. Beispielsweise ist die folgende CREATE TABLE-Anweisung in MySQL 5.5 gültig:
mysql> CREATE TABLE k1 (
-> id INT NOT NULL PRIMARY KEY,
-> name VARCHAR(20)
-> )
-> PARTITION BY KEY()
-> PARTITIONS 2;
Query OK, 0 rows affected (0.06 sec)
If there is no primary key but there is a unique key, then the unique key is used for the partitioning key:
mysql> CREATE TABLE k2 (
-> id INT NOT NULL,
-> name VARCHAR(20),
-> UNIQUE KEY (id)
-> )
-> PARTITION BY KEY()
-> PARTITIONS 2;
Query OK, 0 rows affected (0.02 sec)
Nach dem Login kopieren
Wenn die eindeutige Schlüsselspalte jedoch nicht als NOT NULL definiert wäre, dann die vorherige Anweisung würde fehlschlagen.
In beiden Fällen ist der Partitionierungsschlüssel die ID-Spalte, auch wenn sie nicht in der Ausgabe von SHOW CREATE TABLE oder in der Spalte PARTITION_EXPRESSION des INFORMATION_SCHEMA angezeigt wird .PARTITIONS-Tabelle.
Wie unten:
mysql> SELECT t.TABLE_NAME, t.PARTITION_NAME,t.TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS t WHERE table_name='k2';
+------------+----------------+------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS |
+------------+----------------+------------+
| k2 | p0 | 3 |
| k2 | p1 | 4 |
+------------+----------------+------------+
2 rows in set (0.01 sec)
Nach dem Login kopieren
Anders als bei anderen Partitionierungstypen sind die für die Partitionierung nach KEY verwendeten Spalten nicht auf Ganzzahl- oder NULL-Werte beschränkt.
Zum Beispiel ist die folgende CREATE TABLE-Anweisung gültig: <br/>没有primary key，没有在定义时候指定分区字段，会抱错：
mysql> CREATE TABLE tm3 (
-> s1 CHAR(32)
-> )
-> PARTITION BY KEY()
-> PARTITIONS 10;
ERROR 1488 (HY000): Field in list of fields for partition function not found in table
在定义中加入分区字段，add the column in define , it is ok
mysql> CREATE TABLE tm3 (
-> s1 CHAR(32)
-> )
-> PARTITION BY KEY(s1)
-> PARTITIONS 10;
Query OK, 0 rows affected (0.07 sec)
mysql>Nach dem Login kopieren
子分区 Unterpartitionierung
Unterpartitionierung – auch als zusammengesetzte Partitionierung bekannt – ist die weitere Partitionierung jeder Partition in einer partitionierten Tabelle.
Betrachten Sie beispielsweise die folgende CREATE TABLE-Anweisung:
mysql> 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
-> )
-> );
Query OK, 0 rows affected (0.04 sec)
CREATE TABLE ts3 (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),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2,
SUBPARTITION s3
)
);Nach dem Login kopieren
(1) Jede Partition muss die gleiche Anzahl an Unterpartitionen haben. Wenn nicht, schlagen Sie fehl
mysql> CREATE TABLE ts3 (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),
-> PARTITION p2 VALUES LESS THAN MAXVALUE (
-> SUBPARTITION s2,
-> SUBPARTITION s3
-> )
-> );
ERROR 1064 (42000): Wrong number of subpartitions defined, mismatch with previous setting near '
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2,
' at line 8
mysql>Nach dem Login kopieren
(2) Jede SUBPARTITION-Klausel muss (mindestens) einen Namen für die Unterpartition enthalten.
Andernfalls können Sie jede gewünschte Option für die Unterpartition festlegen oder zulassen, dass sie die Standardeinstellung für diese Option annimmt.
(3) Unterpartitionsnamen müssen überall eindeutig sein die gesamte Tabelle.
(4) Unterpartitionen können bei besonders großen Tabellen verwendet werden, um Daten und Indizes auf viele Festplatten zu verteilen. Angenommen, Sie haben 6 Festplatten als gemountet /disk0, /disk1, /disk2 usw. Betrachten Sie nun das folgende Beispiel:
mysql> CREATE TABLE ts5 (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 s2
-> DATA DIRECTORY = '/disk2/data'
-> INDEX DIRECTORY = '/disk2/idx',
-> SUBPARTITION s3
-> DATA DIRECTORY = '/disk3/data'
-> INDEX DIRECTORY = '/disk3/idx'
-> ),
-> PARTITION p2 VALUES LESS THAN MAXVALUE (
-> SUBPARTITION s4
-> DATA DIRECTORY = '/disk4/data'
-> INDEX DIRECTORY = '/disk4/idx',
-> SUBPARTITION s5
-> DATA DIRECTORY = '/disk5/data'
-> INDEX DIRECTORY = '/disk5/idx'
-> )
-> );
Query OK, 0 rows affected (0.04 sec)
In this case, a separate disk is used for the data and for the indexes of each RANGE. Many other variations are possible;Nach dem Login kopieren
another example might be:
mysql> CREATE TABLE ts6 (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
-> )
-> );
Query OK, 0 rows affected (0.04 sec)Nach dem Login kopieren
In der Zukunft, wenn die Anzahl der Käufe für das Jahrzehnt beginnend mit dem Jahr 2000 bis zu einem Punkt anwächst, an dem der Standardwert erreicht ist Sollte der Speicherort nicht mehr ausreichend Platz bieten, können die entsprechenden Zeilen mit einer ALTER TABLE ... REORGANIZE PARTITION-Anweisung verschoben werden.
Eine Erläuterung dazu finden Sie in Abschnitt 17.3, „Partitionsverwaltung“.
Die Optionen DATA DIRECTORY und INDEX DIRECTORY sind in Partitionsdefinitionen nicht zulässig, wenn der Server-SQL-Modus NO_DIR_IN_CREATE aktiv ist. Ab MySQL 5.5.5 sind diese Optionen auch beim Definieren von Unterpartitionen nicht zulässig (Bug#42954).
Wie MySQL-Partitionierung mit NULL umgeht
Partitionierung in MySQL unternimmt nichts, um NULL als Wert eines Partitionierungsausdrucks zu verbieten,
unabhängig davon, ob es sich um einen Spaltenwert oder den Wert eines vom Benutzer bereitgestellten Ausdrucks handelt. Obwohl es zulässig ist, NULL als Wert eines Ausdrucks zu verwenden, der andernfalls eine Ganzzahl ergeben muss, ist es wichtig zu bedenken, dass NULL keine Zahl ist. Partitionierung von MySQL
Die Implementierung behandelt NULL als kleiner als jeden Nicht-NULL-Wert, genau wie ORDER BY.
Das bedeutet, dass die Behandlung von NULL je nach Partitionierung unterschiedlicher Typen unterschiedlich ist und ein Verhalten hervorrufen kann, das Sie nicht sehen Erwarten Sie nichts, wenn Sie nicht darauf vorbereitet sind.
Aus diesem Grund besprechen wir in diesem Abschnitt, wie jeder MySQL-Partitionierungstyp NULL-Werte verarbeitet, wenn er die Partition bestimmt, in der eine Zeile gespeichert werden soll,
und geben Beispiele für jeden.
Verarbeitung von NULL mit RANGE-Partitionierung. Wenn Sie eine Zeile in eine durch RANGE partitionierte Tabelle einfügen, sodass der zur Bestimmung der Partition verwendete Spaltenwert NULL ist,
wird die Zeile in die unterste Partition eingefügt. Betrachten Sie beispielsweise diese beiden Tabellen in einer Datenbank mit dem Namen p, die wie folgt erstellt wurde:
(1) Rang Partition,OK
Sie können die Partitionen sehen erstellt durch diese beiden CREATE TABLE-Anweisungen unter Verwendung der folgenden Abfrage für die Tabelle PARTITIONS in der Datenbank INFORMATION_SCHEMA:
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1 | p0 | 0 | 0 | 16384 |
| t1 | p1 | 0 | 0 | 16384 |
| t1 | p2 | 0 | 0 | 16384 |
| t2 | p0 | 0 | 0 | 16384 |
| t2 | p1 | 0 | 0 | 16384 |
| t2 | p2 | 0 | 0 | 16384 |
| t2 | p3 | 0 | 0 | 16384 |
| ts | p0 | 0 | 0 | 16384 |
| ts | p0 | 0 | 0 | 16384 |
| ts | p1 | 0 | 0 | 16384 |
| ts | p1 | 0 | 0 | 16384 |
| ts | p2 | 0 | 0 | 16384 |
| ts | p2 | 0 | 0 | 16384 |
+------------+----------------+------------+----------------+-------------+
14 rows in set (0.00 sec)Nach dem Login kopieren
Lassen Sie uns nun jede dieser Tabellen mit einer einzelnen Zeile füllen enthält einen NULL-Wert in der Spalte, die als Partitionierungsschlüssel verwendet wird,
und überprüfen Sie, ob die Zeilen mithilfe eines Paares von SELECT-Anweisungen eingefügt wurden:
You can see which partitions are used to store the inserted rows by rerunning the previous query against INFORMATION_SCHEMA.PARTITIONS and inspecting the output:
mysql> INSERT INTO t1 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t1;
+------+--------+
| c1 | c2 |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.01 sec)
mysql> SELECT * FROM t2;
+------+--------+
| c1 | c2 |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS
Nach dem Login kopieren
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1 | p0 | 1 | 16384 | 16384 |
| t1 | p1 | 0 | 0 | 16384 |
| t1 | p2 | 0 | 0 | 16384 |
| t2 | p0 | 1 | 16384 | 16384 |
| t2 | p1 | 0 | 0 | 16384 |
| t2 | p2 | 0 | 0 | 16384 |
| t2 | p3 | 0 | 0 | 16384 |
| ts | p0 | 0 | 0 | 16384 |
| ts | p0 | 0 | 0 | 16384 |
| ts | p1 | 0 | 0 | 16384 |
| ts | p1 | 0 | 0 | 16384 |
| ts | p2 | 0 | 0 | 16384 |
| ts | p2 | 0 | 0 | 16384 |
+------------+----------------+------------+----------------+-------------+
13 rows in set (0.00 sec)
You can also demonstrate that these rows were stored in the lowest partition of each table by dropping these partitions,
Nach dem Login kopieren
and then re-running the SELECT statements:
Nach dem Login kopieren
<br/>
Nach dem Login kopieren
(2) Handling of NULL with LIST partitioning. 必须将null在定义中加入才能录入null的分区数据
mysql> CREATE TABLE ts3 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7, NULL),
-> PARTITION p2 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.01 sec)Nach dem Login kopieren
否则insert null的分区数据会抱错: ERROR 1504 (HY000): Table has no partition for value NULL
(3) Handling of NULL with HASH and KEY partitioning. <br/>
mysql> CREATE TABLE th (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY HASH(c1)
-> PARTITIONS 2;
Query OK, 0 rows affected (0.00 sec)
There is no data record in beginnig.
mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th | p0 | 0 | 0 | 16384 |
| th | p1 | 0 | 0 | 16384 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM th;
+------+--------+
| c1 | c2 |
+------+--------+
| NULL | mothra |
| 0 | gigan |
+------+--------+
2 rows in set (0.00 sec)
mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th | p0 | 2 | 8192 | 16384 |
| th | p1 | 0 | 0 | 16384 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)Nach dem Login kopieren
Recall that for any integer N, the value of NULL MOD N is always NULL. For tables that are partitioned by HASH or KEY, this result is treated for determining the correct partition as 0. Checking the INFORMATION_SCHEMA.PARTITIONS table once again, we can
see that both rows were inserted into partition p0:
MySQL对分区中null值得处理, rang,key,以及hash中,都是直接放入min的分区中. list分区中则是放入事先定义好的包含null的分区中,如果list分区事先没有定义包含null值的分区,那么录入的时候会抱错
以上就是MySQL 分区表 partition线上修改分区字段，后续进一步学习partition (2) --> 子分区以及对录入Null值的处理情况.的内容，更多相关内容请关注PHP中文网（www.php.cn）！
