MySQL Table partitioning

Reference Address: http://dev.mysql.com/doc/refman/5.6/en/partitioning.html

When to use partitions

1. Massive data
2. Data table index is larger than server valid memory

Restrictions on partitioning

1. Most of the data table's integer columns can only be partitioned, or data columns may be converted to integer columns by partitioning functions (where columns supports integer/string/date/datetime types)
2. Maximum number of partitions cannot exceed 1024
3. If there is a unique index or primary key, the partition column must be contained within all unique indexes or primary keys
4. Foreign keys are not supported
5. Full-text indexing is not supported (fulltext)
6. Partitioning support Function Limitations

ABS ()	CEILING ()	Day ()
DayOfMonth ()	DAYOFWEEK ()	DayOfYear ()
DATEDIFF ()	EXTRACT ()	Floor ()
HOUR ()	Microsecond ()	MINUTE ()
MOD ()	MONTH ()	QUARTER ()
SECOND ()	Time_to_sec ()	To_days ()
To_seconds ()	Unix_timestamp ()	WEEKDAY ()
Year ()		Yearweek ()

MySQL supports the Range,list,hash,key partition type, which is most commonly used with range:

1. Range – This mode allows data to be divided into different ranges. For example, you can divide a table into several partitions by year.
2. Hash (hash) – This mode allows the calculation of the hash key of one or more columns of the table, and finally partitions the data regions of the hash code with different values. For example, you can create a table that partitions the primary key of a table.
3. Key (key value)-an extension of the above hash mode, where the hash key is generated by the MySQL system.
4. List (pre-defined list) – This mode allows the system to split the data through predefined list values.
5. Composite (composite mode) – The combination of the above modes is used

Partitioned SQL

The Where condition of the filtered partition table must be a column of the Shard partition table

Range partition

ALTER TABLE Employees 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)

);

List partition

ALTER TABLE Employees 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)

);

Hash partition

The purpose of the hash partition is to distribute the data evenly across the predefined partitions, ensuring that the data volume of each partition is roughly the same.

ALTER TABLE Employees PARTITION by HASH (store_id)

Partitions 4;

The hash partition function page needs to return an integer value. The value in the Partitions clause is a non-negative integer, without the addition of the partitions clause, by default the number of partitions is 1.

Key partition

The key partition and the hash partition are similar, the difference is that the hash partition is the user custom function partition, the key partition uses the function which the MySQL database provides to partition, NDB cluster uses the MD5 function to partition, for other storage engine MySQL uses the internal hash function, These functions are based on the same algorithm as password ().

ALTER TABLE tm1 PARTITION by KEY (S1)

Partitions 10;

Columns partition

In the range above, list, HASH, key four kinds of partitions, the condition of the partition must be shaping, if it is not shaping need to convert it to shaping through a function.

mysql-5.5 begins to support the columns partition, which can be considered as the evolution of the range and list partition, and the columns partition can be partitioned directly using non-shaping data. The columns partition supports the following data types:

All shaping, such as int SMALLINT TINYINT BIGINT. float and decimal are not supported.

Date types, such as Date and DateTime. The remaining date types are not supported.

String types, such as char, VARCHAR, binary, and varbinary. Blob and text types are not supported.

Columns can be partitioned using multiple columns.

Sub-partition subpartitioning

ALTER TABLE ts 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

);

Managing partitions

Add partition

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES less THAN (2000));

Delete Partition

ALTER TABLE tr DROP PARTITION p2;

Merging partitions

ALTER TABLE Members

REORGANIZE PARTITION P0 into (

PARTITION n0 VALUES less THAN (1960),

PARTITION N1 VALUES less THAN (1970)

);

MySQL Table partitioning