MySQL分区

来源：互联网发布：电话线端口转换器编辑：程序博客网时间：2024/06/08 11:00

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=utf8PARTITION 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=utf8PARTITION 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=utf8PARTITION 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=utf8PARTITION 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=utf8PARTITION BY HASH (user_id div 2)PARTITIONS 4 ;

LINEAR HASH

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

V = POWER(2, CEILING(LOG(2, num))); //获取大于等于num的第一个2的次幂
N=expr & (V - 1)；
when N>=num:
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};

0 0