MySQL Table Partition A

There are generally four types of partitioning tables in MySQL:

The first type: Range partition, based on a given interval range, to assign data to different partitions.

The second type: the list partition, the base enumeration's value lists are partitioned.

The Third Kind: Hast partition, based on the number of partitions to divide the data into different partitions.

The fourth type: Key partitioning, similar to hash partition.

--------------------------------------------------------------------------------------------------------------- ----------------------------------------------------

The first: Range partition, the field that only supports int type in MySQL 5.1, and 5.5 adds support for datetime and varchar just to change range to range columns

Example one: Range partition of the Int class CREATE TABLE t3 (x int, y int) partition by range (x) (partition p0 values less than, partition P1 Val UEs less than (200));

Example two: Adding a partition to a table

ALTER TABLE t3 add partition (partition P2 values less than (MaxValue));

Example three: a varchar or datetime type partition

CREATE table t4 (x varchar (+), y varchar (+)) partition by range columns (x) (partition P0 values less than ('), partitio n P2 values less than (MaxValue));

Second: The list partition does not have the definition of values less than (maxvalues) in the list partition, meaning that the inserted value will be incorrect if it is not listed in the list.

List is also supported for data types such as varchar just to add columns this keyword

Example one: Create TABLE T5 (x int,y int) partition by list (x) (partition P0 values in (1,3,5,7,9), partition P1 values in (2,4,6,8) );

Example two: CREATE TABLE T6 (x varchar (), y varchar (+)) partition by list columns (x) (partition P0 values in (' A '), partition P1 Values in (' B '));

The third type: hash partition, mainly used to disperse hot read, to ensure that the data in each partition as far as possible evenly distributed. Ordinary hash is to take the modulus algorithm, for example, if divided into four districts,

which partition the newly inserted data will go to is based on the partition key in addition to the four modulo. This, however, can be problematic, as the old data will be recalculated when we add the partition to 5.

Once you can determine which partition this row of data will exist in. This can cause performance problems.

CREATE TABLE t7 (x int,y int) partition by hash (x) partitions 5;--hash (expr) expr is also possible if it is an expression that returns an integer.

The fourth kind: The key partition, and the hash partition difference is not big. It has two great advantages. One it supports all data types except Text,blob, and two it can specify multiple columns.

CREATE TABLE T8 (x varchar (+), y varchar (+)) partition by key (x, y) partitions 4;

MySQL Table Partition A