MENU


Creation

A unique index must include all columns in the partitioning function.
RANGE
CREATE TABLE tbl(
a INT
) PARTITION BY RANGE (a) (  -- expression or any data type
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
CREATE TABLE tbl (
ts TIMESTAMP
) PARTITION BY RANGE (UNIX_TIMESTAMP(ts) ) (
PARTITION p0 VALUES LESS THAN
(UNIX_TIMESTAMP('2013-01-01 00:00:00') ),
PARTITION p1 VALUES LESS THAN
(UNIX_TIMESTAMP('2014-01-01 00:00:00') ),
PARTITION p2 VALUES LESS THAN
(UNIX_TIMESTAMP('2015-01-01 00:00:00') ),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);


RANGE COLUMN
CREATE TABLE tbl (
d DATE,
s VARCHAR(10)
) PARTITION BY RANGE COLUMNS (s,d) ( -- no expression
PARTITION p0 VALUES LESS THAN ('d','2015-01-01'),
PARTITION p1 VALUES LESS THAN ('f','2015-01-01'),
PARTITION p2 VALUES LESS THAN ('n','2015-01-01'),
PARTITION p3 VALUES LESS THAN (MAXVALUE,
MAXVALUE)
);
SELECT PARTITION_NAME, TABLE ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = ‘tb’;
SELECT (0,25,50)< (20,20,0),  -- 1  
(100,0,0)< (0,10,20);  -- 0


LIST
CREATE TABLE tbl(
a INT
) PARTITION BY LIST (a) (  -- only integer columns allowed
PARTITION odd VALUES IN(1,3,5,7,9),
PARTITION even VALUES IN(0,2,4,6,8)
);


LIST COLUMN
CREATE TABLE tbl(
a VARCHAR(3),
b VARCHAR(3)
) PARTITION BY LIST COLUMNS (a,b) (
PARTITION pt1 VALUES IN(('a','x'),('c','x')),
PARTITION pt2 VALUES IN(('b','y'))
);


HASH
CREATE TABLE tbl(
d1 DATE,
d2 DATE,
UNIQUE(c,d)
) PARTITION BY LINEAR HASH (YEAR(d1)+YEAR(d2)) #integer
PARTITIONS 4;
HASH uses a simple modulo function to map the rows to the partitions.


LINEAR HASH
CREATE TABLE tbl(
d DATE
) PARTITION BY LINEAR HASH (YEAR(d)) # integer
PARTITIONS 6;
To determine the partition which stores a row
1. Find the next power of 2 greater than the partitions total:
v = POWER(2, CEILING(LOG(2, pTotal)))
2. Set N = F(columns) & (v-1)
3. While N>= pTotal
Set v=CEILING(v/2)
Set N=N & (v -1)

For instance, if ‘1998-01-01’ is inserted into tbl, the partition number is given by:
v = POWER(2,CESILING(LOG(2,6))) = 8
N = Year(‘1998-01-01’) & (8-1)
= 1998 & 7
= 6         (>=pTotal)
N’= 6 & (CEILING(8/2)-1)
= 6 & 3
= 2
Linear hashing allows adding, dropping, merging, and splitting of partitions to be much faster. The disadvantage is that data is less likely to be evenly distributed compared to regular hashing.


KEY
CREATE TABLE tbl(
a DATE,
b VARCHAR(10),
PRIMARY KEY (a,b)
) PARTITION BY KEY(a,b)  -- non-integers allowed
PARTITIONS 6;
CREATE TABLE tbl(
a DATE PRIMARY KEY,
b VARCHAR(10)
) PARTITION BY KEY() -- a used
PARTITIONS 6;
This causes MySQL to use its own hashing function, one which is the same for PASSWORD().


LINEAR KEY
CREATE TABLE tbl(
a DATE,
b VARCHAR(10),
PRIMARY KEY (a,b)
) PARTITION BY LINEAR KEY(a,b)  -- non-integers allowed
PARTITIONS 6;


subpartitioning
CREATE TABLE tbl (a INT, b DATE)
PARTITION BY RANGE(YEAR(b))  -- RANGE or LIST
SUBPARTITION BY HASH(TO_DAYS(b))  -- HASH or KEY
SUBPARTITIONS 2 (  -- 2 subpartitions per partition
PARTITION p0 VALUES LESS THAN (2016),
PARTITION p1 VALUES LESS THAN (2018),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
CREATE TABLE tbl(a INT, b DATE)
PARTITION BY RANGE(YEAR(b))
SUBPARTITION BY HASH(TO_DAYS(b)) (
PARTITION p0 VALUES LESS THAN (2016) ( -- explicit
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 (2018) (
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'
)
);