表分区

概念：表分区就是将一个大表按照mysql提供的几种方式，分成几个小表。日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长、性能低下，如果涉及联合查询的情况，性能会更加糟糕。对表进行分区，目的就是减少数据库的负担，提高数据库的效率，通常来讲就是提高表的增删改查效率。
分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。
分区后，表面上还是一张表，但数据散列到多个位置了。应用程序读写的时候操作的还是大表名字，数据库系统自动去组织分区的数据。
MySQL 5.1以后新增了表分区(Partition)的功能。

与单个磁盘或文件系统分区相比，可以存储更多的数据。
很容易就能删除不用或者过时的数据。
一些查询可以得到极大的优化。
涉及到SUM()/COUNT()等聚合函数时，可以并行进行。
IO吞吐量更大。
分区允许可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。

如果通过有效隔离，全表扫描就变成了分区扫描，降低IO，但如果已经使用索引，且结果集较小时，做分区不一定会提高效率，这点要注意。从表的方向上来看，又可分为按行分区和按列分区。

范围分区

通常是使用频率最高的分区，如按月份划分，这样的数据保持均匀性比较好，如果划分的均匀性不是很好，需要考虑其他分区方法。
例如，可以将一个表通过年份划分成两个分区，2001 -2010年、2011-2020。

哈希分区

如果数据不是那么容易进行划分，通过这种方式就很灵活了。可以将数据均匀的插入到不同的块，在并发时有利于提高效率，当无法用Range分区时，就可以用Hash分区。

列表分区

当需要明确控制如何将数据进行分区时，采用这种方式。只能进行单列分区，可以讲数据进行分组，比如按城市分区，几个城市放一起。

复合分区

侧重于数据归档了，将上述三个组合起来用。根据业务需求的数据分布来了选择合适的组合。

范围分区举例

PARTITION BY RANGE (table_ column) (
PARTITION p0 VALUES LESS THAN (values1)，
PARTITION p1 VALUES LESS THAN (values2),
PARTITION p2 VALUES LESS THAN (values3)
)

列表分区举例

PARTITION BY LIST(table_ column)(
PARTITION p0 VALUES IN (0,4,8,12)，
PARTITION p1 VALUES IN (1,5,9,13)，
PARTITION p2 VALUES IN (2,6,10,14)，
PARTITION p3 VALUES IN (3,7 ,11,15)
)

hash分区举例

分五个区域，从0开始，到4结束。余数为1则进1分区，余数为2则进2分区，以此类推。

PARTITION BY HASH(table_ column)
PARTITIONS 5

复合分区举例

CREATE TABLE ts (id INT, birth_ day DATE)
PARTITION BY RANGE(YEAR(birth_day))
SUBPARTITION BY HASH(TO_DAYS(birth_ day))
(
    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
    )
);

实际开发中遇到的问题

MYSQL的分区字段，必须包含在主键字段内。
在对表进行分区时，如果分区字段没有包含在主键字段内，如表A的主键为ID，分区字段为createtime ，按时间范围分区，代码如下：

CREATE TABLE T1 (
     id int(8) NOT NULL AUTO_INCREMENT,
     createtime datetime NOT NULL,
     PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS(createtime))
(
PARTITION p0 VALUES LESS THAN (TO_DAYS('2010-04-15')),
PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-05-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2010-05-15')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-05-31')),
PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-06-15')),
PARTITION p5 VALUES LESS THAN  MAXVALUE);

错误提示：#1503 A PRIMARY KEY MUST INCLUDE ALL COLUMNS IN THE TABLE'S PARTITIONING FUNCTION MySQL主键的限制，每一个分区表中的公式中的列，必须在主键/unique key 中包括
下面讨论解决办法，毕竟在一张表里，日期做主键的还是不常见。

方法1：

顺应MYSQL的要求，就把分区字段加入到主键中，组成复合主键。

CREATE TABLE T1 (
     id int(8) NOT NULL AUTO_INCREMENT,
     createtime datetime NOT NULL,
     PRIMARY KEY (id,createtime)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS (createtime))
(
PARTITION p0 VALUES LESS THAN (TO_DAYS('2010-04-15')),
PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-05-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2010-05-15')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-05-31')),
PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-06-15')),
PARTITION p5 VALUES LESS THAN  MAXVALUE);

方法2：

既然MYSQL要把分区字段包含在主键内才能创建分区，那么在创建表的时候，指定主键字段。

CREATE TABLE T1 (
     id int(8) NOT NULL ,
     createtime datetime NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS (createtime))
(
PARTITION p0 VALUES LESS THAN (TO_DAYS('2010-04-15')),
PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-05-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2010-05-15')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-05-31')),
PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-06-15')),
PARTITION p5 VALUES LESS THAN  MAXVALUE);

MySQL表分区意义及使用方法，以及在开发中遇到的问题。