— mysql分區(qū), 子分區(qū)以及對錄入null值的處理情況.?看完官方文檔做的筆記.
— KEY Partitioning
Partitioning by key is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression, the hashing function for key partitioning is supplied by the mysql server. This internal hashing function is based on the same algorithm as PASSWORD().
KEY is used rather than HASH.
KEY takes only a list of one or more column names. The column or columns used as the partitioning key must comprise part or all of the table’s primary key, if the table has one.
KEY takes a list of zero or more column names. Where no column name is specified as the partitioning key, the table’s primary key is used, if there is one. For example, the following CREATE TABLE statement is valid in MySQL 5.5:?<br>
?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)
However, if the unique key column were not defined as NOT NULL, then the previous statement would fail.
In both of these cases, the partitioning key is the id column, even though it is not shown in the output of SHOW CREATE TABLE or in the PARTITION_EXPRESSION column of the INFORMATION_SCHEMA.PARTITIONS table.
As below:
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)
Unlike the case with other partitioning types, columns used for partitioning by KEY are not restricted to integer or NULL values.
For example, the following CREATE TABLE statement is valid:
沒有primary key，沒有在定義時候指定分區(qū)字段，會抱錯：
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 在定義中加入分區(qū)字段，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>
?
子分區(qū) Subpartitioning
Subpartitioning—also known as composite partitioning—is the further pision of each partition in a partitioned table.
For example, consider the following CREATE TABLE statement:
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 ????????) ????);
?
(1) Each partition must have the same number of subpartitions. if not ,fail
?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>
(2) Each SUBPARTITION clause must include (at a minimum) a name for the subpartition.
Otherwise, you may set any desired option for the subpartition or allow it to assume its default setting for that option.
(3) Subpartition names must be unique across the entire table.
(4)? Subpartitions can be used with especially large tables to distribute data and indexes across many disks. Suppose that you have 6 disks mounted as /disk0, /disk1, /disk2, and so on. Now consider the following example:
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;
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)
?
In future, when the number of purchases for the decade beginning with the year 2000 grows to a point where the default location no longer provides sufficient space, the corresponding rows can be moved using an ALTER TABLE … REORGANIZE PARTITION statement. See Section 17.3, “Partition Management”, for an explanation of how this can be done.
The DATA DIRECTORY and INDEX DIRECTORY options are disallowed in partition definitions when the NO_DIR_IN_CREATE server SQL mode is in effect. Beginning with MySQL 5.5.5, these options are also disallowed when defining subpartitions (Bug#42954).
How MySQL Partitioning Handles NULL
Partitioning in MySQL does nothing to disallow NULL as the value of a partitioning expression,
whether it is a column value or the value of a user-supplied expression. Even though it is permitted to use NULL as the value of an expression that must otherwise yield an integer, it is important to keep in mind that NULL is not a number. MySQL’s partitioning implementation treats NULL as being less than any non-NULL value, just as ORDER BY does.
?
This means that treatment of NULL varies between partitioning of different types, and may produce behavior which you do not expect if you are not prepared for it.
This being the case, we discuss in this section how each MySQL partitioning type handles NULL values when determining the partition in which a row should be stored,
and provide examples for each.
?
Handling of NULL with RANGE partitioning.? If you insert a row into a table partitioned by RANGE such that the column value used to determine the partition is NULL,
the row is inserted into the lowest partition. For example, consider these two tables in a database named p, created as follows:
?
(1) Rang Partition,OK
You can see the partitions created by these two CREATE TABLE statements using the following query against the PARTITIONS table in the INFORMATION_SCHEMA database:
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)
?
Now let us populate each of these tables with a single row containing a NULL in the column used as the partitioning key,
and verify that the rows were inserted using a pair of SELECT statements:
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
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,
and?then?re-running?the?SELECT?statements:
?<br>
(2) Handling of NULL with LIST partitioning. 必須將null在定義中加入才能錄入null的分區(qū)數(shù)據(jù)
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)
否則insert null的分區(qū)數(shù)據(jù)會抱錯: ERROR 1504 (HY000): Table has no partition for value NULL
?
(3) Handling of NULL with HASH and KEY partitioning.
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)
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對分區(qū)中null值得處理, rang,key,以及hash中,都是直接放入min的分區(qū)中. list分區(qū)中則是放入事先定義好的包含null的分區(qū)中,如果list分區(qū)事先沒有定義包含null值的分區(qū),那么錄入的時候會抱錯
?以上就是MySQL 分區(qū)表 partition線上修改分區(qū)字段，后續(xù)進(jìn)一步學(xué)習(xí)partition (2) –> 子分區(qū)以及對錄入Null值的處理情況.的內(nèi)容，更多相關(guān)內(nèi)容請關(guān)注PHP中文網(wǎng)（www.php.cn）！?
