MySQL从5.1版本开始支持分区功能，它允许可设置的一定逻辑，跨文件系统分配单个表的多个部分，但是就访问数据库而言，逻辑上还是只有一个表。

还是老样子，在学习新知识之前都先带着问题去寻找想要的答案：

1、什么是分区？

2、为什么分区？好处在哪？

3、如何分区？

一开始也讲了，根据一定逻辑规则，将一个表拆成多个更小更容易管理的部分。例如我们新建一张表利用range分区

逻辑上还是只有一张表，但是实际上确有3个物理分区对象组成

我们查看是否支持分区时对于5.6以下的版本可以使用如下命令：

?

1

show variables like '%partition%';

而在5.6及以上用如上命令会显示empty set，但是并不是表示不支持分区，而是我们应该这样查看：

?

1

show plugins;

当看到有partition并且status是active时表示支持。

1、分区可以在一个表中存储比单个磁盘或文件系统分区上的数据更多的数据，因为我们可以将分区表存储在不同物理磁盘上

2、对已过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据，他的效率远比delete高；

3、优化查询，在where子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率；例如下面语句：

SELECT * FROM t PARTITION（p0，p1）WHERE c 1506 - Foreign keys are not yet supported in conjunction with partitioning

有以下四种分区类型：

RANGE分区：基于一个给定连续区间范围，把数据分配到不同的分区；

LIST分区：类似RANGE分区，区别在LIST分区是基于枚举出的值列表分区，RANGE是基于给定连续区间范围分区；

HASH分区：基于用户定义的表达式返回值来选择分区，该表达式对要插入到表的行中列值操作；

KEY分区：类似HASH，但是HASH允许使用用户自定义表达式，而KEY分区不允许，它需要使用MySQL服务器提供的HASH函数，同时HASH分区只支持整数分区，而KEY分区支持除BLOB和TEXT类型外其他列；

但是无论是哪一种分区类型，要么分区表上没有主键/唯一键，要么分区表的主键/唯一键都必须包含分区键，否则会报错：

?

1

A PRIMARY KEY must include all columns in the table's partitioning function

?

1

A UNIQUE INDEX must include all columns in the table's partitioning function

RANGE分区：利用取值范围将数据分区，区间要连续并且不可以重叠，使用VALUES LESS THAN 进行分区定义，举例如下：

分别创建两张表，分区的没有分区的：

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

create table testwithoutpartition(

id int DEFAULT null,

name char(5),

datedata date

)

create table testwithpartition(

id int DEFAULT null,

name char(5),

datedata date

)

PARTITION BY RANGE (year(datedata)) (

PARTITION p1 VALUES LESS THAN (1996) ,

PARTITION p2 VALUES LESS THAN (1997) , 

PARTITION p3 VALUES LESS THAN (1998) ,

PARTITION p4 VALUES LESS THAN (1999) , 

PARTITION p5 VALUES LESS THAN (2000) ,

PARTITION p6 VALUES LESS THAN MAXVALUE );

上面分区语句的VALUES LESS THAN MAXVALUE子句是表示当有大于2000的时候都插入到p6中，MAXVALUE代表最大可能整数值，否则当我们插入一条记录包含2001时会报错；

从上面我们可以看到顺序是有规定的，当我们把p2设为1998而p3设为1997时会报错：

?

1

VALUES LESS THAN value must be strictly increasing for each partition

接着我们使用存储过程分别给两张表插入数据

?

1

2

3

4

5

6

7

8

9

CREATE DEFINER=`root`@`localhost` PROCEDURE `insertdata`()

BEGIN

    DECLARE numb int DEFAULT 0;

    while i < 20000

    do

        insert into testwithpartition values(numb,'lanco',DATE_ADD('1996-04-01',INTERVAL numb DAY));

        set numb =numb+1;

    end while;

END

当要删除过期的数据时候，只需要简单的语句如此来删除p0分区中的数据：

?

1

ALTER TABLE testwithpartition drop PARTITION p0

LIST分区：建立离散值列表指定特定值属于哪一个分区

?

1

2

3

4

5

6

7

8

9

10

11

create table testwithlistpartition(

id int not null,

name char(5),

category VARCHAR(30)

)

PARTITION BY LIST (id) (

PARTITION p0 VALUES IN (1,5) ,

PARTITION p1 VALUES IN (11,15) ,

PARTITION p2 VALUES IN (6,10) ,

PARTITION p3 VALUES IN (16,20)

);

如上，可以看出和RANGE分区不同的是，我们不必遵循特定的顺序，而如果我们试图插入的记录不在分区值列表中，他不像RANGE有VALUES LESS THAN MAXVALUE这样包含其它的值方式，

他是在MySQL5.5引入的分区类型，实际上就是为了解决RANGE和LIST分区只支持整数分区问题，COLUMNS可以细分为RANGE?COLUMNS和LIST?COLUMNS分区，他们都支持整数，日期时间，字符串三大数据类型：

1、所有的整数类型，int，tinyint，bigint等，但不支持decimal，float等；

2、日期时间类型：date和datetime；

3、字符类型：char，varchar，binary，varbinary；不支持text和blob类型；

但是要注意，Columns分区仅支持一个或者多个字段名作分区键，而不支持表达式作分区键，如上面的RANGE分区year(datadate),即使返回整型也不可以；

但是其中一大亮点是能够支持多列分区：

RANGE COLUMNS

我们先创建一个RANGE分区：

?

1

2

3

4

5

6

7

8

CREATE TABLE test1 (

    a INT,

    b INT

)

PARTITION BY RANGE COLUMNS(a, b) (

    PARTITION p0 VALUES LESS THAN (5, 12),

    PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)

);

那这样子我插入三条记录id=5，那他是插入到哪里呢？

?

1

INSERT INTO test1 VALUES (5,10), (5,11), (5,12);

我们如何查看他是插入到哪一个分区：

?

1

2

3

4

5

6

7

8

9

select partition_name part,

partition_expression expr,

partition_description descr,

table_rows

from

information_schema.partitions

where

TABLE_SCHEMA = schema()

and table_name='test1';

如图可以看出是插入到p1分区表里；继续写入一条记录，并且发现是写入到p3分区表里，

接着我们创建一个RANGE COLUMNS分区表：

?

1

2

3

4

5

6

7

8

CREATE TABLE rc1 (

    a INT,

    b INT

)

PARTITION BY RANGE COLUMNS(a, b) (

    PARTITION p0 VALUES LESS THAN (5, 12),

    PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)

);

依旧和test1表一样的三条数据插入，查看插入到哪里：

可以看出来两者对于数据插入的比较差别了吗？

这是因为我们比较的是元组值而非标量值，你看

例如我们这里插入的记录(5,10),它先取第一个值5与分区限制行值比较，因为p0(5,12),记录5不小于限制行值5，所以比较记录第二位10，他小于限制行值12，所以属于p0区，

假如我们又插入新数据(2,13),查看可知：

还是p0区，如此便清晰明了，它先用插入的数据的第一个字段值和分区的第一个值进行比较，如果插入的第一个值小于分区的第一个值那么就不需要比较第二个值就属于该分区；如果第一个值等于分区的第一个值，开始比较第二个值同样如果第二个值小于分区的第二个值那么就属于该分区。

LIST COLUMNS:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

CREATE TABLE test3 (

    first_name VARCHAR(25),

    last_name VARCHAR(25),

    street_1 VARCHAR(30),

    street_2 VARCHAR(30),

    city VARCHAR(15),

    renewal DATE

)

PARTITION BY LIST COLUMNS(city) (

    PARTITION pRegion_1 VALUES IN('Oskarshamn', 'H?gsby', 'M?nster?s'),

    PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'V?stervik'),

    PARTITION pRegion_3 VALUES IN('N?ssj?', 'Eksj?', 'Vetlanda'),

    PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'V?xjo')

);

和RANGE COLUMNS一样，支持其他数据类型作分区键；

基于给定的分区个数，将数据分配到不同分区，HASH分区只能对整数进行分区，对于非整型字段只能通过表达式转为整型，MySQL支持两种HASH分区-常规hash和线性hash。

例如创建如下hash分区表：

?

1

2

3

4

5

6

7

8

CREATE TABLE employees (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

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

)

PARTITION BY HASH( id )

PARTITIONS 4;

它表示根据id值hash分区，分区数为4，如果不包含PARTITIONS子句，则分区数缺省为1。

?

1

insert into employees

?

1

values(1,'liu','lanco','2018-05-01'),

?

1

(2,'liu','lanco','2018-05-02'),

?

1

(3,'liu','lanco','2018-05-03'),

?

1

(4,'liu','lanco','2018-05-04'),

?

1

(5,'liu','lanco','2018-05-05');

查看分区范围：

实际上常规的hash是基于取模运算进行判断新记录应该插入到哪一个分区,例如上面新记录id=2，运算表达式为mod(2,4)

常规的hash分区看起来挺不错的，但是当我们需要增加分区或者合并分区时候，问题就来了，这里有4个分区，现在需要增加一个分区，或者合并分区，原来的取模算法是MOD(expr,4),现在新增一个分区，取模算法变成MOD(expr,5),数据都要重新计算分区，这个代价实在太大了。

线性HASH分区：

线性HASH分区与常规HASH分区不同之处在于线性HASH使用线性的2的幂运算法则，并且分区关键字为LINEAR HASH。

?

1

2

3

4

5

6

7

8

CREATE TABLE employees (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

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

)

PARTITION BY HASH( id )

PARTITIONS 4;

假设将要保存的记录分区编号为N，num是分区数，我们可以通过如下计算得到指定记录保存在哪一个分区：

1、找到下一个大于等于num的2次幂，这个值设为V ，可以通过如下公式

V =POWER(2,Ceilling(Log(2,num)));

2、其次，设置N=F(column_list)&(V-1)

例如上述给出的例子num=4，根据1的式子计算得出V=4，现在要插入一条新纪录，id=234；现在来计算它对应的N值，代入这里给的式子N=234&(4-1)=2;

3、当N>=num时，设置V=Ceiling(V/2),设置N=N&(V-1)，由于id=234这条记录，N=2