MySQL分区表

分区不是在引擎层实现的，所以常见的引擎都支持，至少MyISAM和InnoDB是支持的。就访问数据库的程序而言，从逻辑上将，只有一个表或一个索引，但在物理上这个表或者索引可能由数十个物理分区组成。每个分区都是独立的对象，可以单独处理，也可以作为一个更大对象的一部分处理。

MySQL支持水平分区，即按行分区，不支持垂直分区。MySQL数据库的分区是局部分区索引，一个分区既存放数据也存放索引。

可通过如下命令查看当前数据库是否启用了分区功能。

show variables like '%partition%';
或者
show pulgins;

MySQL支持如下几种类型的分区

    RANGE分区：行数据基于一个给定连续范围分区。不好理解，看例子吧。5.5开始支持RANGE  COLUMNS分区。

    LIST分区：同RANGE，区别在于给定的不是连续范围，是离散的值。5.5开始支持LIST COLUMNS分区。

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

    KEY分区：根据MySQL内部提供的哈希函数进行分区。

    COLUMNS分区：5.5开始支持，可以直接使用非整形的数据进行分区，分区根据类型直接比较而得，不需要转换为整形。看例子吧。

无论创建何种类型的分区，如果表中存在主键或唯一索引的列，则分区列必须是主键或唯一索引的一部分。索引列可以是null值。在没有主键和唯一索引的表中可以指定任意列为索引列。表中只能最多有一个唯一索引，即primary key 和unique key不能同时存在，primary key包含在unique key中时除外。下面是例子

有唯一索引，分区列必须是唯一索引的一部分，索引列也可以是null
create table t1(
    col1 int(11),
    col2 int(11),
    col3 int(11) null,
    unique key (col2, col3)
) partition by hash(col3) partitions 4;

没有唯一索引，可以指定任何列
create table t2(
    col1 int(11),
    col2 int(11),
    col3 int(11)
) partition by hash(col1) partitions 4;

主键和唯一索引都存在，主键包含在唯一索引中，只能以主键进行分区
create table t100(
    col1 int(11),
    col2 int(11) null,
    primary key(col1),
    unique key (col2,col1)
) partition by hash(col1) partitions 2;

主键和唯一索引存在，主键没有包含在唯一索引中，不能创建，下面是错误的例子
create table t101(
    col1 int(11),
    col2 int(11) null,
    primary key(col1),
    unique key (col2)
) partition by hash(col1) partitions 2;
[Err] 1503 - A UNIQUE INDEX must include all columns in the table's partitioning function
create table t101(
    col1 int(11),
    col2 int(11) null,
    primary key(col1),
    unique key (col2)
) partition by hash(col2) partitions 2;
[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function

两个唯一索引列也不能创建分区
create table t102(
    col1 int(11),
    col2 int(11) null,
    unique key(col1),
    unique key (col2)
) partition by hash(col2) partitions 2;
[Err] 1503 - A UNIQUE INDEX must include all columns in the table's partitioning function

（这么多的限制，感觉这分区真没法用）

RANGE分区，直接看例子

create table t(
    id int(11)
)engine=innodb
partition by range(id)(
    partition p0 values less than(10),
    partition p1 values less than(20)
);

查看表在磁盘上的物理文件，启用分区之后，表不再由一个ibd文件组成了，而是由建立分区时各个分区的ibd文件组成，如下图所示的，t#p#p0.ibd，t#p#p1.ibd。

插入数据

insert into t values(9),(10);

则9会被插入到p0分区，10会被插入的到p1分区。通过information_schema.PARTITIONS表查看每个分区的具体信息

select * from information_schema.PARTITIONS where table_schema=database() and table_name='t';

部分截图如下，其中分区PARTITION_METHOD表示分区类型，TABLE_ROWS反应每个分区的记录数量（图中未展示）

当插入不在分区中定义的值时，MySQL会报错，如插入30

insert into t select 30;
[Err] 1526 - Table has no partition for value 30

对于上述问题，我们可以创建一个MAXVALUES分区，MAXVALUES 可以理解为正无穷大，所有大于等于20的值就会插入到该分区，如下

create table t(
    id int(11)
)engine=innodb
partition by range(id)(
    partition p0 values less than(10),
    partition p1 values less than(20),
    partition p2 values less than(MAXVALUE)
);

RANGE分区主要用于日期列的分区，如销售类的表，可以根据年份来分区存放销售记录

create table sales(
    money int(11) unsigned,
    date datetime
)engine=innodb
    partition by range (year(date))(
    partition p2017 values less than(2018),
    partition p2018 values less than(2019),
    partition p2019 values less than(2020)
);

如果需要删除2018年的数据，不需要删除 delete from sales where date>='2018-01-01' and date<'2019-01-01',只需要删除2018分区就行了

alter table sales drop partition p2018;

可以加快某些查询操作，如查询2018年的销售记录

explain partition select * from sales where date<='2018-12-31' and date>='2018-01-01;

 这样查询优化器只会搜索p2018这个分区，而不是搜索所有分区。下面这条语句则会搜索p2018和p2019这两个分区

explain partition select * from sales where date<'2019-01-01' and date>='2018-01-01;

对于sales这张表，如果按照每年每月来进行分区，有人这样设计表

create table sales(
    money int(11) unsigned,
    date datetime
)engine=innodb
    partition by range (year(date)*100+month(date)(
    partition p201801 values less than(201802),
    partition p201802 values less than(201803),
    partition p201803 values less than(201804)
);

但是在执行下面的sql语句时并不会进行查询优化，会搜索所有分区

explain partition select * from sales where date<='2018-03-31' and date>='2018-03-01';

产生这个问题的主要原是，对于 RANGE 分区的查询，优化器只能对 YEAR(), TO_DAYS(), TO_SECONDS(), UNIX_TIMESTAMP()这类函数进行优化。对于上面的要求，可以使用TO_DAYS()进行修改，如下

create table sales(
    money int(11) unsigned,
    date datetime
)engine=innodb
    partition by range (to_days(date))(
    partition p201801 values less than(to_days('2018-02-01'),
    partition p201802 values less than(to_days('2018-03-01'),
    partition p201803 values less than(to_days('2018-04-01')
);

LIST分区

LIST分区和RANGE分区非常相似，只是LIST分区的值是离散的。与RANGE分区的 VALUES LESS THAN 不同，LIST分区使用 VALUES IN，所以每个分区的值是离散的，只能是定义的值。如

create table t(
    a int(11),
    b int(11)
)engine=innodb
partition by list(b)(
    partition p0 values in(1,3,5,7,9),
    partition p1 values in(2,4,6,8,10)
);

同样，如果插入的值不在分区定义中，会报错

insert into t select 11;
[Err] 1526 - Table has no partition for value 11

用insert插入多个行数据的过程中如果遇到分区未定义的值时，MyISAM和InnoDB存储引擎的处理完全不同。MyISAM会将之前的行数据都插入，之后的行数据不会插入。InnoDB则会将其视为一个事务，不会插入任何行。

HASH分区

要使用HASH分区分割一个表，要在CREATE TABLE 语句上加一个 PARTITION BY HASH(expr)子句，其中expr是一个返回整数的表达式，如果没有PARTITIONS num子句，则默认的分区数量是1（没啥意义啊）。

看例子

create table t(
    id int(11),
    date datetime
)engine=innodb
partition by hash(year(date))
partitions 4;

如果插入日期为2018-03-07，保存该记录的分区如下

mod(year('2018-03-01'))=2

所以该记录会存放到分区 2 中。

MySQL数据库还支持一种LINEAR HASH的分区，他使用一个更复杂的算法来确定新行插入的分区。语法为 linear hash(expr)，只是将关键字 HASH 换成了 LINEAR HASH。

对于LINEAR HASH分区类型的表，如果插入日期为2018-03-07，确定保存该记录的分区如下

（1）取大于分区数量4的下一个2的幂值V：V=power(2, celing(log(2, num)))=4;（num是分区数量partitions，本例中是4）

（2）分区N=year(date)&(V-1)

LINEAR HASH分区的优点是：增加、删除、合并和拆分分区变得更加快捷，这有利于处理含有大量数据的表。

缺点：与HASH分区相比，数据分布可能不大均匀。

（这个优缺点我也不懂）

KEY分区

KEY分区和HASH分区相似。KEY分区支持除text和BLOB之外的所有数据类型的分区，而HASH分区只支持数字分区，KEY分区不允许使用用户自定义的表达式进行分区，KEY分区使用系统提供的HASH函数进行分区。

在KEY 分区中使用LINEAR 和在HASH分区中一样，分区的编号是通过2的幂（powers-of-two）算法得到的，而不是取模算法。（看不懂）