mysql数据库表分区小结

定义：将数据分成多个位置存放，可以是同一磁盘，也可以是不同机器；分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。

1、分区类型

根据所使用的不同分区规则可以分成几大分区类型。

RANGE 分区：

基于属于一个给定连续区间的列值，把多行分配给分区。

LIST 分区：

类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

HASH分区：

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。

KEY
分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

复合分区：

基于RANGE/LIST 类型的分区表中每个分区的再次分割。子分区可以是 HASH/KEY 等类型。

2、用法

以部门员工表为例子：

1)       创建range分区

 

create table emp

(empno varchar(20)not null ,

empname varchar(20),

deptno int,

birthdate date,

salary int

)

partition by range(salary)

(

partition p1 values less than (1000),

partition p2 values less than (2000),

partition p3 values less than maxvalue

);

以员工工资为依据做范围分区。

 

create table emp

(empno varchar(20)not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by range(year(birthdate))

(

partition p1 values less than (1980),

partition p2 values less than (1990),

partition p3 values less than maxvalue

);

以year(birthdate)表达式（计算员工的出生日期）作为范围分区依据。这里最值得注意的是表达式必须有返回值。

 

2)       创建list分区

 

create table emp

(empno  varchar(20)not null ,

empname varchar(20),

deptno  int,

birthdate date not null,

salary int

)

partition by list(deptno)

(

partition p1 values in  (10),

partition p2 values in  (20),

partition p3 values  in  (30)

);

以部门作为分区依据，每个部门做一分区。

 

3)       创建hash分区

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

create table emp

(empno varchar(20)not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by hash(year(birthdate))

partitions 4;

4)       创建key分区

按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL 服务器提供，服务器使用其自己内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则。“CREATE TABLE ...PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个 列名的一个列表。

create table emp

(empno varchar(20)not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by key(birthdate)

partitions 4;

 

 

5)       创建复合分区

 

range - hash(范围哈希)复合分区

 

create table emp

(empno varchar(20)not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by range(salary)

subpartition by hash(year(birthdate))

subpartitions 3

(

partition p1 values less than (2000),

partition p2 values less than maxvalue

);

range- key复合分区

 

create table emp

(empno varchar(20)not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by range(salary)

subpartition by key(birthdate)

subpartitions 3

(

partition p1 values less than (2000),

partition p2 values less than maxvalue

);

list - hash复合分区

CREATE TABLE emp (

empno varchar(20)NOT NULL,

empname varchar(20) ,

deptno int,

birthdate date NOT NULL,

salary int

)

PARTITION BY list (deptno)

subpartition by hash(year(birthdate))

subpartitions 3

(

PARTITION p1 VALUES in  (10),

PARTITION p2 VALUES in  (20)

)

;

list - key 复合分区

 

CREATE TABLE empk (

empno varchar(20)NOT NULL,

empname varchar(20) ,

deptno int,

birthdate date NOT NULL,

salary int

)

PARTITION BY list (deptno)

subpartition by key(birthdate)

subpartitions 3

(

PARTITION p1 VALUES in  (10),

PARTITION p2 VALUES in  (20)

)

;

6)       分区表的管理操作

删除分区:

alter table empdrop partition p1;

不可以删除hash或者key分区。

一次性删除多个分区，alter table empdrop partition p1,p2;

增加分区:

alter table empadd partition (partition p3 values less than (4000));

alter table empladd partition (partition p3 values in (40));

 

分解分区:

Reorganizepartition关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据。分解前后分区的整体范围应该一致。

alter table te

reorganize partition p1 into

(

partition p1 values less than (100),

partition p3 values less than (1000)

); ----不会丢失数据

 

合并分区:

Merge分区：把2个分区合并为一个。
alter table te

reorganize partition p1,p3 into

(partition p1 values less than (1000));

----不会丢失数据

 

重新定义hash分区表:

Alter table emp partitionby hash(salary)partitions 7;

----不会丢失数据

重新定义range分区表:

Alter table emp partitionbyrange(salary)

(

partition p1 values less than (2000),

partition p2 values less than (4000)

); ----不会丢失数据

 

删除表的所有分区:

 

Alter table emp remove partitioning;--不会丢失数据

 

重建分区：

这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。它可用于整理分区碎片。

ALTER TABLE emp rebuild partitionp1,p2;

 

优化分区：

如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB，或TEXT类型的列）作了许多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间，并整理分区数据文件的碎片。

ALTER TABLE empoptimize partition p1,p2;

 

分析分区：

读取并保存分区的键分布。

ALTER TABLE empanalyze partition p1,p2;

 

修补分区：

修补被破坏的分区。

ALTER TABLE emp repairpartition p1,p2;

 

检查分区：

可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。

ALTER TABLE empCHECK partition p1,p2;

这个命令可以告诉你表emp的分区p1,p2中的数据或索引是否已经被破坏。如果发生了这种情况，使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区。

3、效率分析

现在我本机有两个表b41sgk和备份表b41sgk_copy，数据量一样，都有350万左右；主键ID是索引，查询字段username未建立索引；

查询350万数据：有索引，扫描23行，未建立索引，几乎全表扫描，由此可见索引对提高查询效率的作用；分区多次查询平均值7s,未分区13s，可见分区性能上来说有很大提高；更新操作效率比较明显，比如更新一条数据，未分区15s左右，分区数据大概7s，大概是因为表扫描多个分区并行进行，效率在更新时更明显。但是一个奇怪现象是有时候多次对一个语句查询，分析耗时，发现未分区的反而低于分区，是不是因为表建立了索引，所以是索引提高了速度（待验证）;结合网上资料来分析：索引使用场景，一般使用日期字段分类归档数据，提高查询效率；如果更新类比较多的大表，可以考虑使用分区；如果一般表字段使用了索引，没必要来分区，因为查询效率和分区查不多；表分区还有一个重要的功能就是可以将表的各分区放到不同的磁盘上，以增加表容量；range分区比hash分区的查询性能高；这是自己的总结，希望有点用，具体还要自己多实践，实践中发现规律。

其他注意事项：

*分区字段要包含在主键内，这个是个问题；如果不想键主键，可以先把表主键删除，再分区。。。。。。

*查看表分区sql：SELECT

  partition_name part,
  partition_expression expr,
  partition_description descr,
  FROM_DAYS(partition_description) lessthan_sendtime,
  table_rows
FROM
  INFORMATION_SCHEMA.partitions
WHERE
  TABLE_SCHEMA = SCHEMA()

  AND TABLE_NAME='b41sgk_copy'; 后面是表名称