Mysql里的分区

一，分区 的一些定义

“分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问的数据库的应用而言，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。”“MySQL数据库支持的分区类型为水平分区，并不支持垂直分区。水平分区指的是同一表中不同行的记录分配到不同的物理文件中；垂直分区指将同一表中不同的列分配到不同的物理文件中。”“此外，MySQL数据库的分区是局部分区索引，一个分区中既存放数据以存放索引；而全局分区是指，数据存放各个分区，但是所有数据的索引放在一个对象中。目前，MYSQL暂时不支持全局分区。”

两个命令都可查看当前数据库是否启用了分区：

show variables like '%partition%';

show plugins;

 

二，当前MYSQL支持的几种分区类型

（1）RANGE分区：行数据基于属于一个给定连续区间的列值放入分区。

（2）LIST分区：和RANGE分区类型一样，只是LIST分区面向的是离散的值。

（3）HASH分区：根据用户自定义表达式的返回值来进行分区，返回值不能为负数。

（4）KEY分区：根据MSYQL数据库提供的散列函数来进行分区。

三，分区时应注意的地方

（1）不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。如下面这个就是错的：

mysql> create table s(col1 int not null,col2 int not null,col3 int not null,unique key(col1,col2))
    -> partition by hash(col3)
    -> partitions 4;
1503 - A PRIMARY KEY must include all columns in the table's partitioning function

（2）当建表时没有指定主键和唯一索引时，可以指定任何一个列为分区列

四，四种分区的具体说明

（1）RANGE分——它是最常用的一种分区。这里有个例子：

create table t_range(id int)

 partition by range(id)(

  partition p0 values less than (10),

  partition p1 values less than (20) );

接着写入一条数据：

insert into t_range select 9;
Query OK, 1 row affected

但是这就不行了：

mysql> insert into t_range select 30;
1526 - Table has no partition for value 30

回为没有大于20的分区，可以加一个分区：

mysql> alter table t_range add partition (partition p2 values less than maxvalue);
Query OK, 0 rows affected

maxvalue可理解为正无穷大，大于或等于20的值都会被放入p2分区里。

RANGE分区很大的一个用处在于日期列的分区，这样就可以根据年份来分区存放销售，订单等记录。比如：

create table sale(

money int unsigned not null,

date datetime)

partition by range(year(date))(

 partition p2008 values less than (2009),

partition p2009 values less than(2010),

partition p2010 values less than (2011)

);

假如表中已存有记录。

如果要删除2008年的数据，只需如此：

alter table sale drop partition p2008;而不用：

delect from sale where date>=.........这类的操作，且前者效率要高许多。

又如果要查询2008的记录，应这样写：

select * from sale where date>='2008 -01-01' and date < ='2008-12-31' 而不应该写成这样：

select * from sale where date>='2008 -01-01' and date < ‘2009-01-01'。

因为用EXPLAIN PARTITIONS SELECT * FROM.......会发现前一句只检索p2008一个分区，后一句会检索p2008，p2009两个分区，它们的速度肯定就不同了。

关于RANGE分区还有这样一个例子：

有个人想对sale表按照每年每月来进行分区，于是他就这样写：

create table sale(

money int unsigned not null,

date datetime)

partition by range(year(date) * 100 + month(date) )(  /*如果date为2008－02－02，则表达式为200802*/

 partition p2008 values less than (2009),

partition p2009 values less than(2010),

partition p2010 values less than (2011)

);

等他分好区，插入了数据，再进行选择性的查询时，却发现优化器对全部分区都进行了查找，这就没起到分区的作用嘛。

原因是这样的：

对RANGE 分区，优化器只能对YEAR(),TO_DAYS(),TO_SECONDS() 和 UNIX_TIMESTAMP() 这些函数进行优化选。因此将红字部改为 TO_DAYS(date) 就起作用了。

顺带提一下TO_DAYS(DATE)返回从0年1月1日至DATE的天数

mysql> select to_days('0001-01-01');
+-----------------------+
| to_days('0001-01-01') |
+-----------------------+
|                   366 |
+-----------------------+
1 row in set

（2）LIST分区

它与RANGE分区很相似，只是它的分区列的值是离散的

create table t(

a int ,

b int)

partition by LIST(b)(

partition p0 values in(1,3,5,7,9),

parttition p1 values in(2,4,6,8)

);

这里使用的是values in而非values less than

在插入多行数据过程中如碰到分区未指定的值MyISAM引擎会将之前的行数据都插入，之后的则不会；而InnoDB会将其看成一个事务，不会插入任何数据。

（3）HASH分区

HASH分区同前两个有点不一样，它的目的是将数据均匀分布到先前定义的分区中，保证各分区 的数据量大致是一样的。它是这样的：

create table t(

a int ,

b int)

partition by HAHS(b)  （HASH(expr)里的expr返回一个整数表达式）

partitions 4;   (如果省略这句，分区数量默认为1）

（4）KEY分区

它与HASH相似，不同之处在于HASH分区通过用户定义的函数进分区，KEY分区使用MYSQL数据库提供的函数进行分区。

五 COLUMNS 分区（RANGE与LIST的替代）

前面说的RANGE,LIST,HASH,KEY的分区条件必须是整型，如果不是就要借助函数进行转换, 像YEAR(),TO_DAYS().....

而COLUMNS则可直接使用非整型的数据时行分区，它支持：

（1）所有整型，INT,BIGINT,SMALLINT,TININT.不支持FLOAT,DECIMAL.

（2）日期，DATE,DATETIM。别的日期类型不支持。

（3）字符串，CHAR,VARCHAR,BINARY,VARBINARY,不支持BLOB,TEXT.

像以前的RANGE分区可这样改：

create table sale(

money int unsigned not null,

date datetime)

partition by range columns(b)(

 partition p2008 values less than ('2009-01-01'),

partition p2009 values less than('2010-01-01'),

partition p2010 values less than ('2011-01-01')

);

对于之前的RANGE,LIST我们可以用RANGE COLOMNS和LIST COLUMNS进行代替，至于前面单独说RANGE与LIST，而后面又说用别的代替，主要是因为COLUMNS是MYSQL5.5开始支持，另外一点就是想讲讲它们的历史。

六 子分区

MSYQL可以在RANGE与LIST分区上再进行HASH或KEY分区，如：

mysql> create table ts (a int,b date)
     partition by range(year(b))
     subpartition by hash(to_days(b))
     subpartitions 2(
     partition p0 values less than (1990),
     partition p1 values less than (2000),
     partition p2 values less than maxvalue
     );
Query OK, 0 rows affected

表ts先根据b列进行了RANGE分区，后到进行了一次HASH分区，因此分区数量一共为6个：p0与p1与p2分区中各存在两个hash分区。上面也可以改成这样：

mysql> create table ts (a int,b date)
     partition by range(year(b))
     subpartition by hash(to_days(b))
     (
       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）每个分区数量必须相同

（2）如果在一个分区表的任何分区上使用SUBPARTITION来明确定义任何子分区，那么就必须定义所有的子分区，要是在上面的两个红星那里将（subpartitions  s2,subpartitions s3）省略就不对。

（3）每个SUBPARTITION子句必须包括子分区的一个名字，且名字须唯一。

七，分区中的NULL值

   （1）对于RANGE分区，如果向分区中插入了NULL值，那么MYSQL会将该值放入最左边的分区中。就是说分区会将NULL值视为小于任何的一个非NULL值，如果删除了最左边的分区，那含有NULL值的记录也会被删掉，这个要注意。

（2）在LIST分区下使用NULL，要显式指出哪个分区存放NULL值，不然会出错。下面是对的：

create table t(

a int ,

b int)

partition by LIST(b)(

partition p0 values in(1,3,5,7,9,NULL),

parttition p1 values in(2,4,6,8)

);

(3)在HASH与KEY分区中，对NULL值的处理与RANGE,LIST又不同了,任何分区函数都会将含有NULL值的记录返回为0.