MySql分区优化

g21121

浏览: 694708 次
性别:
来自: 北京

最近访客更多访客>>

lbyzx123

cuityang

jin361612388

knight_black_bob

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

我们已经讨论过了分表优化适应的场合，分表优化属于“显示”优化，这种优化我们看得见，但日后修改及维护非常麻烦，MySQL就为我们提供了“分区”的概念，类似于我们的系统分区。

分区允许根据设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。借助 MySQL 分区，可以提高数据库性能，并简化超大型数据库的管理。MySQL 支持水平分区，可将数据库中的行拆分为更小的数据集，随后分布于多个目录和磁盘中。

分区有助于提高查询性能，这是因为当特定的操作只需访问较小的数据集而非一整个大型表。此外，还可以将分区后的表指派到不同的物理驱动器上，这样便可在同时访问多个分区时减少物理 I/O 争用，磁盘raid是一个很好的选择。

分区还有助于简化数据管理。举例来说，我们可以从分区的表中删除特定分区，同时确保其余分区完整无缺（与对整个表做批量删除操作后会产生碎片截然不同）。整个过程无需我们干预。

引用一张MySQL官方的图片可以很好的认识分区

我们可以通过多种分区方法来准确控制数据的分区方式：

范围分区(Range Partitioning)
每一个分区中都包含能对应到一组特定值（范围）的一些行，适用的情景包括常用查询直接以对表进行分区的列为条件，或者需要通过删除表来快速删除旧数据时。比如：按主键id范围来分区，1-10000,10001-20000...等等。

列示分区(List Partitioning)
列示分区与范围分区类似。主要区别在于，列示分区模式允许根据我们预先定义的值列表（而不是一系列连续值构成的范围）来对数据进行划分。比如：一个类型字段“type”，它有100种值，我们可以利用列示分区来分成100个区，这种分区具有局限性，维护起来也较范围分区麻烦。

列分区(Columns Partitioning)
列分区支持在分区键中使用多个列。这些列将用于决定在分区中放置那些行并在分区修剪操作中决定在哪那些行要清掉。列分区是对范围分区和列表分区的扩展。

散列分区(Hash Partition)
散列分区主要用于确保在预先设定数量的多个分区上平均分配数据。在范围分区和列表分区中，您必须明确指定特定列值存储在哪个分区中。而在散列分区中，MySQL 将自动为您做这个工作，您只需根据执行散列的列值和分区表所划分成的分区数量来指定列值或表达式即可。比如：有10个已建好的分区，当新插入数据时根据算法会分配到指定分区上去。

线性散列分区是列表分区的一个变体，它使用一种更复杂的数据分区算法，可在处理 TB 级表时大幅提高添加、删除、合并和拆分分区的速度。

键分区(Key Partitioning)
键分区与散列分区类似，MySQL 通过一个系统生成的散列键来确保数据的平均分布，而不是使用用户定义的表达式做散列分区。键分区支持在分区中使用各种类型的列数据。

子分区(Sub-Partitioning)
子分区支持对分区表中各个分区进行进一步划分，专用于处理超大型表，可将数据和索引分布在许多服务器和磁盘上。

通过上述介绍认识到分区给我们带来的好处显而易见，可以大大提升查询速度、平均分配I/O访问的瓶颈限制、查询总数时只需返回每个分区总数之和就可、可以更有规律的组织数据的存储等等。

那么下面就开始我们的分区操作吧！

我们首先要确定我们的MySQL版本是否支持分区，在MySQL命令符状态下输入：

mysql> SHOW VARIABLES LIKE '%partition%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

看到结果显示为“YES”，表示支持分区。

对于创建了分区的表，可以使用你的MySQL 服务器所支持的任何存储引擎；MySQL 分区引擎在一个单独的层中运行，并且可以和任何这样的层进行相互作用。在MySQL中，同一个分区表的所有分区必须使用同一个存储引擎；例如，不能对一个分区使用MyISAM，而对另一个使用InnoDB。但是，这并不妨碍在同一个 MySQL 服务器中，甚至在同一个数据库中，对于不同的分区表使用不同的存储引擎。

输入下列命令查看MySQL支持的存储引擎：

mysql> SHOW PLUGINS;
+------------+----------+----------------+---------+---------+
| Name       | Status   | Type           | Library | License |
+------------+----------+----------------+---------+---------+
| binlog     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| partition  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| ARCHIVE    | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| BLACKHOLE  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| CSV        | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| FEDERATED  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MEMORY     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| InnoDB     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MRG_MYISAM | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MyISAM     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| ndbcluster | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
+------------+----------+----------------+---------+---------+
11 rows in set (0.00 sec)

存储引擎版本：

mysql> SELECT PLUGIN_NAME as Name,PLUGIN_VERSION as Version,PLUGIN_STATUS as Status FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_TYPE='STORAGE ENGINE';
 
+--------------------+---------+--------+
| Name               | Version | Status |
+--------------------+---------+--------+
| binlog             | 1.0     | ACTIVE |
| CSV                | 1.0     | ACTIVE |
| MEMORY             | 1.0     | ACTIVE |
| MRG_MYISAM         | 1.0     | ACTIVE |
| MyISAM             | 1.0     | ACTIVE |
| PERFORMANCE_SCHEMA | 0.1     | ACTIVE |
| BLACKHOLE          | 1.0     | ACTIVE |
| ARCHIVE            | 3.0     | ACTIVE |
| InnoDB             | 5.6     | ACTIVE |
| partition          | 1.0     | ACTIVE |
+--------------------+---------+--------+
10 rows in set (0.00 sec)

Range分区：

分区之前必须注意几点：

1.Range分区的列只接受列名，不接受表达式；

2.Range分区的列可以是多个列；

3.Range分区的列并不会只限于整数，字符串，时间，日期等等，你可以做很多选择；

4.Range分区的列是基于元组（连续的值），而不是单纯比较数值。

开始建立分区：

还是拿前面的用户表为例子，在SQL命令行状态下输入：

CREATE TABLE `USER` (
  `USR_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  `CUSTORM_ID` int(11) DEFAULT NULL COMMENT '客户代码',
  `STATUS` int(11) DEFAULT NULL COMMENT '用户状态',
  `CREATE_DATE` datetime DEFAULT NULL COMMENT '创建日期',
  `ACTIV_CODE` varchar(32) DEFAULT NULL COMMENT '激活码',
  `USR_TYPE` int(11) DEFAULT NULL COMMENT '用户类型',
  PRIMARY KEY (`USR_ID`)
)

PARTITION BY RANGE (USR_ID) (
    PARTITION partition_0 VALUES LESS THAN (10000),
    PARTITION partition_1 VALUES LESS THAN (20000)
);

上半部分为建表语句，下半部分为创建分区语句，注意他们中间没有“;”间隔，意思很容易理解：USR_ID小于1万的分区为partition_0，1万--2万之间分区为partition_1。这种分区方法比较简单，是针对一个字段来分区的，下面我们看看多个字段的分区方法是怎样的。

在分区之前需要查看你的MySQL版本，只有版本在5.5以上才支持以下分区方法，这点需要注意以下，查看MySQL版本的命令：

SELECT VERSION();

我们同样在SQL状态下输入命令：

CREATE TABLE `USER` (
  `USR_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  `CUSTORM_ID` int(11) DEFAULT NULL COMMENT '客户代码',
  `STATUS` int(11) DEFAULT NULL COMMENT '用户状态',
  `CREATE_DATE` datetime DEFAULT NULL COMMENT '创建日期',
  `ACTIV_CODE` varchar(32) DEFAULT NULL COMMENT '激活码',
  `USR_TYPE` int(11) DEFAULT NULL COMMENT '用户类型',
  PRIMARY KEY (`USR_ID`)
)

PARTITION BY RANGE COLUMNS(USR_ID,USR_TYPE,YEAR(CREATE_DATE)) (
    PARTITION partition_0 VALUES LESS THAN (10000,10,1990),
    PARTITION partition_1 VALUES LESS THAN (20000,20,2000),
    PARTITION partition_2 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);

这样就可以很轻松的对多个字段进行分区操作了，"MAXVALUE"很好理解，表示最大的可能的整数值，同理于Integer.MAX_VALUE。YEAR(CREATE_DATE)) 就是将日期转换成“年”，这种用法也比较普遍。

我们还可以将"VALUES LESS THAN"替换成"VALUES IN"，eg：PARTITION partition_1 VALUES IN (1,2,3,4,5,6,7,8,9,10) 这样只有在括号里的值才会被分区。

前面已经说过可以为分区提供单独的存储引擎，但是一个表中的所有分区必须是相同的存储引擎，想配置单独的引擎只需要在分区语句之前加入引擎声明"ENGINE"即可，ENGINE（和其他的表选项）必须列在用在CREATE TABLE语句中的其他任何分区选项之前，分区语句如下：

CREATE TABLE `USER` (
  `USR_ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  `CUSTORM_ID` INT(11) DEFAULT NULL COMMENT '客户代码',
  `STATUS` INT(11) DEFAULT NULL COMMENT '用户状态',
  `CREATE_DATE` DATETIME DEFAULT NULL COMMENT '创建日期',
  `ACTIV_CODE` VARCHAR(32) DEFAULT NULL COMMENT '激活码',
  `USR_TYPE` INT(11) DEFAULT NULL COMMENT '用户类型',
  PRIMARY KEY (`USR_ID`)
)
ENGINE=INNODB PARTITION BY RANGE (USR_ID) (
    PARTITION partition_0 VALUES LESS THAN (10000),
    PARTITION partition_1 VALUES LESS THAN (20000),
    PARTITION partition_2 VALUES LESS THAN (MAXVALUE)
);

也可以将"ENGINE=INNODB"放置在每条分区规则之后，如：

PARTITION BY RANGE (USR_ID)(
    PARTITION partition_0 VALUES LESS THAN (10000) ENGINE=INNODB,
    PARTITION partition_1 VALUES LESS THAN (20000) ENGINE=INNODB,
    PARTITION partition_2 VALUES LESS THAN (MAXVALUE) ENGINE=INNODB
);

当然分区很多情况并不是建表的时候就已经分好的，或者后期需要调整分区方案，这时我们需要使用以下SQL修改分区方式：

ALTER TABLE USER PARTITION BY RANGE(USR_ID,USR_TYPE,CREATE_DATE)(
    PARTITION partition_0 VALUES LESS THAN (10000,10,'1999-12-31'),
    PARTITION partition_1 VALUES LESS THAN (20000,20,'2999-12-31'),
    PARTITION partition_2 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);

修改分区方式与修改表语义差不多比较好理解，下面是一些跟分区相关的常用命令：

#删除分区
ALTER TABLE USER DROP PARTITION partition_0; //删除partition_0分区，你也可以指定任意分区

#删除分区数据
ALTER TABLE USER TRUNCATE PARTITION partition_0,partition_1; //删除partition_0,partition_1两个分区下的数据
ALTER TABLE USER TRUNCATE PARTITION ALL; //删除全部数据

#重定义分区（包括重命名分区，转移数据；合并分区)
ALTER TABLE USER REORGANIZE PARTITION partition_0,partition_1 INTO (PARTITION p1 VALUES LESS THAN(20000),
PARTITION p2 VALUES LESS THAN(40000));
 
#重建分区
ALTER  TABLE USER REBUILD PARTITION partition_0/ALL; //相当于DROP所有记录，然后再REINSERT；可以解决磁盘碎片

#优化表
ALTER  TABLE USER OPTIMIZE PARTITION partition_0; //在大量DELETE表数据后，可以回收空间和碎片整理。但在5.5.30后支持。在5.5.30之前可以通过RECREATE+ANALYZE来替代，如果用REBUILD+ANALYZE速度慢

#ANALZYE表
ALTER  TABLE USER ANALYZE PARTITION partition_0/ALL;

#CHECK表
ALTER  TABLE USER CHECK PARTITION partition_0/ALL;

#查看建表语句
SHOW CREATE TABLE USER;

#查看表是否是分区表,字段CREATE_OPTIONS: PARTITIONED
SHOW TABLE STATUS LIKE 'USER';

#查看索引
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='USER';

#查看分区表
SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='USER';

#查看分区是否被SELECT使用
EXPLAIN PARTITIONS SELECT * FROM USER WHERE CREATE_DATE < '1990-01-01' OR CREATE_DATE > '2016-01-01';

以上代码均通过了测试，可以放心使用。

注意：如果我们在建立分区的时候分区字段采用的是非主键，例如：

CREATE TABLE `USER` (
  `USR_ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  `CUSTORM_ID` INT(11) DEFAULT NULL COMMENT '客户代码',
  `STATUS` INT(11) DEFAULT NULL COMMENT '用户状态',
  `CREATE_DATE` DATETIME DEFAULT NULL COMMENT '创建日期',
  `ACTIV_CODE` VARCHAR(32) DEFAULT NULL COMMENT '激活码',
  `USR_TYPE` INT(11) DEFAULT NULL COMMENT '用户类型',
  PRIMARY KEY (`USR_ID`)
)
PARTITION BY LIST (USR_TYPE)(
    PARTITION partition_0 VALUES IN (1,2,3),
    PARTITION partition_1 VALUES IN (4,5,6),
    PARTITION partition_2 VALUES IN (7,8,9)
);

MySQL会提示错误：[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function，意思就是分区字段必须包含在主键之中，这是MySQL的硬性规定，我们只有遵守，不知道新版本可不可以，解决办法就是将分区字段加入到联合主键中：

CREATE TABLE `USER` (
  `USR_ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  `CUSTORM_ID` INT(11) DEFAULT NULL COMMENT '客户代码',
  `STATUS` INT(11) DEFAULT NULL COMMENT '用户状态',
  `CREATE_DATE` DATETIME DEFAULT NULL COMMENT '创建日期',
  `ACTIV_CODE` VARCHAR(32) DEFAULT NULL COMMENT '激活码',
  `USR_TYPE` INT(11) DEFAULT NULL COMMENT '用户类型',
  PRIMARY KEY (USR_ID,USR_TYPE)
)
PARTITION BY LIST (USR_TYPE)(
    PARTITION partition_0 VALUES IN (1,2,3),
    PARTITION partition_1 VALUES IN (4,5,6),
    PARTITION partition_2 VALUES IN (7,8,9)
);

List分区：

MySQL中的LIST分区在很多方面类似于RANGE分区。和按照RANGE分区一样，每个分区必须明确定义。它们的主要区别在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表，不像按照RANGE定义分区的情形，LIST分区不必声明任何特定的顺序。

下面就是一个具体例子：

CREATE TABLE `USER` (
  `USR_ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  `CUSTORM_ID` INT(11) DEFAULT NULL COMMENT '客户代码',
  `STATUS` INT(11) DEFAULT NULL COMMENT '用户状态',
  `CREATE_DATE` DATETIME DEFAULT NULL COMMENT '创建日期',
  `ACTIV_CODE` VARCHAR(32) DEFAULT NULL COMMENT '激活码',
  `USR_TYPE` INT(11) DEFAULT NULL COMMENT '用户类型',
  PRIMARY KEY (USR_ID,USR_TYPE)
)

PARTITION BY LIST(USR_TYPE)(
    PARTITION partition_0 VALUES IN (1,2,3),
    PARTITION partition_1 VALUES IN (4,5,6),
    PARTITION partition_2 VALUES IN (7,8,9)
);

这使得用户类型为"1,2,3"分配到partition_0区中，用户类型为"4,5,6"分配到partition_1区中，用户类型为"7,8,9"分配到partition_2区中。

可以使用查询

ALTER TABLE USER DROP PARTITION partition_0;

来进行删除，它与具有同样作用的DELETE QUERY

DELETE QUERY DELETE FROM USER WHERE USR_TYPE IN (1,2,3);

比起来要有效率得多。

注意：如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“INSERT”查询将失败并报错。例如，假定LIST分区的采用上面的方案，下面的插入操作将失败：

INSERT INTO USER VALUES(1,1,1,1,1,10);

这是因为“USR_TYPE”列值"10"不包含在已定义的分区值内。要重点注意的是，LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义，将要匹配的任何值都必须在值列表中找到。

Hash分区：

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS NUM”子句，其中NUM 是一个非负的整数，它表示表将要被分割成分区的数量。

例如，下面的语句创建了一个使用基于“USR_ID”列进行哈希处理的表，该表被分成了4个分区：

CREATE TABLE `USER` (
  `USR_ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  `CUSTORM_ID` INT(11) DEFAULT NULL COMMENT '客户代码',
  `STATUS` INT(11) DEFAULT NULL COMMENT '用户状态',
  `CREATE_DATE` DATETIME DEFAULT NULL COMMENT '创建日期',
  `ACTIV_CODE` VARCHAR(32) DEFAULT NULL COMMENT '激活码',
  `USR_TYPE` INT(11) DEFAULT NULL COMMENT '用户类型',
  PRIMARY KEY (`USR_ID`)
)
PARTITION BY HASH (USR_ID)(
    PARTITION 4
);

如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。例外：对于NDB Cluster（簇）表，默认的分区数量将与簇数据节点的数量相同，这种修正可能是考虑任何MAX_ROWS 设置，以便确保所有的行都能合适地插入到分区中。

如果在关键字“PARTITIONS”后面没有加上分区的数量，将会出现语法错误。

“expr”还可以是一个返回一个整数的SQL表达式。例如：(YEAR(date)。

“expr”还可以是MySQL 中有效的任何函数或其他表达式，只要它们返回一个既非常数、也非随机数的整数。（换句话说，它既是变化的但又是确定的）。但是应当记住，每当插入或更新（或者可能删除）一行，这个表达式都要计算一次；这意味着非常复杂的表达式可能会引起性能问题，尤其是在执行同时影响大量行的运算（例如批量插入）的时候。

最有效率的哈希函数是只对单个表列进行计算，并且它的值随列值进行一致地增大或减小，因为这考虑了在分区范围上的“修剪”。也就是说，表达式值和它所基于的列的值变化越接近，MySQL就可以越有效地使用该表达式来进行HASH分区。

例如，“date” 是一个DATE（日期）类型的列，那么表达式TO_DAYS(date)就可以说是随列“date”值的变化而发生直接的变化，因为列“date”值的每个变化，表达式的值也将发生与之一致的变化。而表达式YEAR(date)的变化就没有表达式TO_DAYS(date)那么直接，因为不是列“date”每次可能的改变都能使表达式YEAR(date)发生同等的改变。即便如此，表达式YEAR(date)也还是一个用于哈希函数的、比较好的候选表达式，因为它随列date的一部分发生直接变化，并且列date的变化不可能引起表达式YEAR(date)不成比例的变化。

换句话说，如果列值与表达式值之比的曲线图越接近由等式“y=nx（其中n为非零的常数）描绘出的直线，则该表达式越适合于哈希。这是因为，表达式的非线性越严重，分区中数据产生非均衡分布的趋势也将越严重。

理论上讲，对于涉及到多列的表达式，“修剪（pruning）”也是可能的，但是要确定哪些适于哈希是非常困难和耗时的。基于这个原因，实际上不推荐使用涉及到多列的哈希表达式。

当使用了“PARTITION BY HASH”时，MySQL将基于用户函数结果的模数来确定使用哪个编号的分区。换句话，对于一个表达式“expr”，将要保存记录的分区编号为N ，其中“N = MOD(expr, num)”。例如，假定表t1 定义如下，它有4个分区：

CREATE TABLE t1 (
	col1 INT,
	col2 CHAR(5),
	col3 DATE
)
PARTITION BY HASH(YEAR(col3) ){
	PARTITIONS 4
};

如果插入一个col3列值为'2005-09-15'的记录到表t1中，那么保存该条记录的分区确定如下：

MOD(YEAR('2005-09-01'),4)
=  MOD(2005,4)
=  1

每当插入或更新一条记录，用户函数都要计算一次。当删除记录时，用户函数也可能要进行计算，这取决于所处的环境。

Linear Hash分区
MySQL还支持线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数。

线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“PARTITION BY” 子句中添加“LINEAR”关键字，如下所示：

PARTITION BY LINEAR HASH(USR_ID)

Key分区：

按照KEY进行分区类似于按照HASH分区，相对于HASH分区使用的用户定义的表达式，KEY分区的哈希函数是由MySQL 服务器提供。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的函数，这些函数是基于与PASSWORD()一样的运算法则。

“CREATE TABLE ... PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个列名的一个列表。

通过线性KEY分割一个表也是可能的。下面是一个简单的例子：

CREATE TABLE `USER` (
  `USR_ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  `CUSTORM_ID` INT(11) DEFAULT NULL COMMENT '客户代码',
  `STATUS` INT(11) DEFAULT NULL COMMENT '用户状态',
  `CREATE_DATE` DATETIME DEFAULT NULL COMMENT '创建日期',
  `ACTIV_CODE` VARCHAR(32) DEFAULT NULL COMMENT '激活码',
  `USR_TYPE` INT(11) DEFAULT NULL COMMENT '用户类型',
  PRIMARY KEY (`USR_ID`)
)
PARTITION BY LINEAR KEY (USR_ID)(
  PARTITIONS 4
);

在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。

MySQL分区处理NULL值的方式

MySQL 中的分区在禁止空值（NULL）上没有进行处理，无论它是一个列值还是一个用户定义表达式的值。一般而言，在这种情况下MySQL 把NULL视为0。如果你希望回避这种做法，你应该在设计表时不允许空值；最可能的方法是，通过声明列“NOT NULL”来实现这一点。

相信大家已经对分区有了一些了解，下面是MySQL官方给出的一些分区的优点：

1.与单个磁盘或文件系统分区相比，可以存储更多的数据。

2.对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。

3.通常和分区有关的其他优点包括下面列出的这些。MySQL 分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。

4.一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE 语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。

5.涉及到例如SUM() 和 COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。

6.通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

限制

当然分区并不是万能的，他也是有限制的：

· 一个数据表最多只能有1024个分区

· 在MYSQL5.1 版本中分区表达式的结果必须是整数，在MYSQL5.5分区表达式可以使用列

· 如果分区字段中有主键或者是唯一索引列，则所有的主键或者是唯一索引列必须全部包含进来

· 分区表无法使用外键

· 对于同一个表的各个分区表必须使用相同的存储引擎

· 分区函数有限制，只可以是MySQL 中有效的任何函数或其他表达式，且它们返回一个既非常数、也非随机数的整数

· 某些存储引擎不支持分区

示例

以表gp_city 为例：

CREATE TABLE `gp_city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `NAME` varchar(32) NOT NULL COMMENT '城市名称',
  `CODE` varchar(16) NOT NULL COMMENT '城市编码',
  `PROVINCE_CODE` varchar(16) NOT NULL COMMENT '所属省份编码',
  `COUNTRY_CODE` varchar(16) NOT NULL COMMENT '所属国家',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=346 DEFAULT CHARSET=utf8 COMMENT='城市';
ALTER TABLE gp_city PARTITION BY RANGE(ID)(
    PARTITION partition_0 VALUES LESS THAN (10),
    PARTITION partition_1 VALUES LESS THAN (20),
    PARTITION partition_2 VALUES LESS THAN (30),
    PARTITION partition_3 VALUES LESS THAN (40),
    PARTITION partition_4 VALUES LESS THAN (MAXVALUE)
);

分区前gp_city只有gp_city.frm与gp_city.ibd两个数据文件其中*.frm存储表结构，*.ibd 存储表数据。

分区后*.ibd将变为多个，如下所示：