MySQL分区

tips

• MYSQL的分区字段，必须包含在主键字段内。否则会报这样的错误：A PRIMARY KEY must include all columns in the table’s partitioning function。

RANGE

按不同的值的连续的范围做分区，分区的表达式必须返回整型类型。
示例：

CREATE TABLE `user` (
    `user_id` int(11) NOT NULL DEFAULT 0,
    `user_name` varchar(50) NOT NULL DEFAULT '', 
    UNIQUE KEY `key_1` (`user_id`)
)
ENGINE=InnoDB  DEFAULT CHARSET=utf8
PARTITION BY RANGE(user_id)(
     PARTITION p0 VALUES LESS THAN (1000),
     PARTITION p1 VALUES LESS THAN (2000),
     PARTITION p2 VALUES LESS THAN MAXVALUE
);

LIST

按不同的值的的范围（可以不连续）做分区，分区的表达式必须返回整型类型。insert数据时如果分区的列找不到对应的分区，则报错。
示例：

CREATE TABLE `city` (
    `city_id` int(11) NOT NULL DEFAULT 0,
    `city_name` varchar(50) NOT NULL DEFAULT '',
    UNIQUE KEY `key_1` (`city_id`)
)                       
ENGINE=InnoDB  DEFAULT CHARSET=utf8
PARTITION BY LIST(city_id)(
     PARTITION north VALUES IN (1, 4, 6, 7, 9),
     PARTITION south VALUES IN (2, 3, 5, 8, 10)
);

COLUMNS

多列分区，分为RANGE COLUMNS和LIST COLUMNS

RANGE COLUMNS

和RANGE类似，但分区的表达式可以返回字符串类型，并支持多个表达式做分区。
示例：

CREATE TABLE `city` (
    `city_id` int(11) NOT NULL DEFAULT 0,
    `area_id` int(11) NOT NULL DEFAULT 0,
    `city_name` varchar(50) NOT NULL DEFAULT ''
)
ENGINE=InnoDB  DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(city_id, area_id)(
     PARTITION north VALUES LESS THAN (10, 10),
     PARTITION south VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

LIST COLUMNS

和LIST类似，但分区的表达式可以返回字符串类型，并支持多个表达式做分区。

CREATE TABLE `city` (
    `city_id` int(11) NOT NULL DEFAULT 0,
    `city_name` varchar(50) NOT NULL DEFAULT ''
)
ENGINE=InnoDB  DEFAULT CHARSET=utf8
PARTITION BY LIST COLUMNS(city_name)(
     PARTITION north VALUES IN ('beijing', 'tianjin'),
     PARTITION south VALUES IN ('guangzhou', 'shenzhen')
);

HASH

插入一条记录时，用分区表达式的值除以分区数的模即为存储这条记录的分区的编号。表达式必须返回整型值，比较适合根据用户ID对用户表分表的场景。如果用户ID为自增字段并且自增步长为step_num，可以用( user_id div step_num)作为分区表达式。
示例：

CREATE TABLE `user` (
    `user_id` int(11) AUTO_INCREMENT ,
    `user_name` varchar(50) NOT NULL DEFAULT '',
    PRIMARY KEY `key_1` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH (user_id div 2)
PARTITIONS 4 ;

LINEAR HASH

这种方式获取记录所在的分区的编号较为复杂。设num为分区数，expr为分区表达式；计算分区编号N的步骤如下：

1. V = POWER(2, CEILING(LOG(2, num))); //获取大于等于num的第一个2的次幂
2. N=expr & (V - 1)；
3. when N>=num:
4. V=V/2，N=N&(V - 1);

KEY

NDB集群用MD5做哈希函数，对于其他的引擎则使用和PASSWORD函数一样的哈希函数。如果表中有PRIMARY KEY则用PRIMARY KEY的列做分区的列，没有则用UNIQUE KEY，当然也可以手动指定要用哪些列做分区关键字。例如用户表依据用户名做分区时可以用KEY分区。

LINEAR KEY

LINEAR KEY在计算分区编号采用和LINEAR HASH类似的步骤

分区管理

取消分区

 alter table city  remove partitioning;

增删分区

增删分区只是改变分区数目，数据不会丢失

增加(${num}是增加的个数)：

ALTER TABLE `user` ADD PARTITION PARTITIONS ${num};

删除(${num}是删除的个数)：

ALTER TABLE `user` COALESCE PARTITION ${num};