MySQL表分区使用详解

MySQL表分区使用详解

一、表分区概述

1. 什么是表分区？

    表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张        表，但是底层却是由多个物理分区组成。

2. 表分区与分表的区别

   分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。 分表与分  区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

3. 表分区有什么好处？

    1）分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。

    2）和单个磁盘或者文件系统相比，可以存储更多数据

    3）优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。

    4）分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。

    5）可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问，ext3问价你系统的inode锁竞争等。

4. 分区表的限制因素

1）一个表最多只能有1024个分区

2）MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

3）如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

 4）分区表中无法使用外键约束

5）mysql的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

*效率

http://blog.csdn.net/king_818/article/details/51395589

二、分区方式

1.RANGE Partitioning(范围分区)

   This type ofpartitioning assigns rows to partitions based on column values falling within agivenrange

   例子：

CREATE TABLE employees_range(

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT'9999-12-31',

    job_code INT NOT NULL,

    store_id INT NOT NULL

)

PARTITION BY RANGE (store_id) (

    PARTITION p0 VALUES LESS THAN (6),

    PARTITION p1 VALUES LESS THAN (11),

    PARTITION p2 VALUES LESS THAN (16),

    PARTITION p3 VALUES LESS THAN MAXVALUE

);

备注：时间等类型均可

2.LIST Partitioning（预定义列表分区）

Similar topartitioning by RANGE, except that the partition is selected based on columnsmatching one of a set of discrete values

例子：

CREATE TABLE employees_list (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT'9999-12-31',

    job_code INT,

    store_id INT

)

PARTITION BY LIST(store_id)(

    PARTITION pNorthVALUES IN (3,5,6,9,17),

    PARTITION pEast VALUES IN(1,2,10,11,19,20),

    PARTITION pWest VALUES IN(4,12,13,14,18),

    PARTITION pCentral VALUES IN(7,8,15,16)

);

3.HASH Partitioning（哈希分区）

With this type ofpartitioning, a partition is selected based on the value returned by auser-defined expression that operates on column values in rows to be insertedinto the table. The function may consist of any expression valid in MySQL thatyields a nonnegative integer value. An extension to this type, LINEAR HASH, isalso available

CREATE TABLE employees_hash(

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT'9999-12-31',

    job_code INT,

    store_id INT

)

PARTITION BY HASH(store_id)

PARTITIONS 4;

备注：根据哈希函数，下篇文章进行详解

4.KEY Partitioning(键值分区

This type ofpartitioning is similar to partitioning by HASH, except that only one or morecolumns to be evaluated are supplied, and the MySQL server provides its ownhashing function. These columns can contain other than integer values, sincethe hashing function supplied by MySQL guarantees an integer result regardlessof the column data type. An extension to this type, LINEAR KEY, is alsoavailable

例子：

CREATE TABLE employees_key (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT'9999-12-31',

    job VARCHAR(30) NOT NULL,

    store_id INT

)

PARTITION BY KEY(job)

PARTITIONS 4

备注：

*不支持用户自定义表达式

*支持blob或text类型外的其它类型作为分区键

*创建Key分区表的时候，可以不指定分区键，默认会首先选择使用主        键作为分区键

*liner key expr

V = POWER(2,CEILING(LOG(2, num)))

5.COMPOSITE( 复合分区)

MySQLVersion>=5.6

子分区其实是对每个分区表的每个分区进行再次分隔，目前只有 RANGE 和 LIST 分区的表可以再进行子分区，子分区只能是 HASH 或者 KEY 分区。子分区可以将原本的数据进行再次的分区划分。

例子：

CREATE TABLE employees_range_hash (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY RANGE(TO_DAYS(hired))
SUBPARTITION BY HASH(store_id) (
	PARTITION p0 VALUES LESS THAN (1990) (
		SUBPARTITION s0,
		SUBPARTITION s1
	),
	PARTITION p1 VALUES LESS THAN (2000) (
		SUBPARTITION s2,
		SUBPARTITION s3
	),
	PARTITION p2 VALUES LESS THAN MAXVALUE (
		SUBPARTITION s4,
		SUBPARTITION s5
	)
);

三、分区操作命令整理

1.查询分区

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME=' employees_list ';

2.增加/删除./合并

FOR RANGE /LIST

*增加分区

alter table table_name add partition(partition p0 values ...(exp))

*删除分区

alter table table_name drop partition[partition name];

*拆分合并分区

ALTER TABLE table_nameREORGANIZEPARTITION p0 INTO (

    PARTITION s0 VALUES LESS THAN (3),

    PARTITION s1 VALUES LESS THAN (6)

);

FOR HASH /KEY

*增加分区

alter table table_name add partitionpartitions[num];

*删除分区

alter table table_name coalesce partition[num];

*拆分合并分区

ALTER TABLE table_nameREORGANIZEPARTITION p0 INTO (

    PARTITION s0 VALUES LESS THAN (3),

    PARTITION s1 VALUES LESS THAN (6)

)

*移除表的分区

ALTER TABLE tablename REMOVE PARTITIONING ;

*分区针对日期字段进行分区可以使用时间类型的函数进行转换成整形，但是如果你的查询语句需要利用分区那么查询语句也需要使用相同的时间函数进行查询。to_days(date), year(date)