分区表概述¶

分区是一种表的设计模式，正确的分区可以极大地提升数据库的查询效率，完成更高质量的SQL编程。

MySQL数据库在**5.1版本及以上**时添加了对分区的支持，分区的过程是将一个表或索引分解为多个更小、更可管理的部分。

就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。

MySQL数据库支持的分区类型为水平分区（指将同一个表中不同行的记录分配到不同的物理文件中），并不支持垂直分区（指将同一表中不同列的记录分配到不同的物理文件中）。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据库还不支持全局分区。

分区的好处是： - 可以让单表存储更多的数据 - 分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作 - 部分查询能够从查询条件确定只落在少数分区上，速度会很快（查询条件尽量扫描少的分区） - 分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备 - 可以使用分区表来避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争 - 可以备份和恢复单个分区

分区的限制和缺点： - 一个表最多只能有1024个分区 - 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来 - 分区表无法使用外键约束 - NULL值会使分区过滤无效 - 所有分区必须使用相同的存储引擎

分区类型¶

第一种分区类型是RANGE分区，也是最常用的一种分区类型，基于属于一个给定连续区间的列值，把多行分配给分区。

这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。不使用COLUMNS关键字时RANGE括号内必须为整数字段名或返回确定整数的函数。

下面创建一个以id列为区间分区表，当id小于10时，数据插入p0分区；当id大于等于10小于20时，数据插入p1分区。

CREATE TABLE employees (
    id INT NOT NULL COMMENT '员工唯一编号',
    fname VARCHAR(30) COMMENT '员工名',
    lname VARCHAR(30) COMMENT '员工姓',
    hired DATE NOT NULL DEFAULT '1970-01-01' COMMENT '入职日期',
    separated DATE NOT NULL DEFAULT '9999-12-31' COMMENT '离职日期（9999-12-31 表示在职）',
    job_code INT NOT NULL COMMENT '职位代码',
    store_id INT NOT NULL COMMENT '所属门店ID'
) COMMENT='员工基本信息表';


CREATE TABLE employees (
    id INT NOT NULL COMMENT '员工唯一编号',
    fname VARCHAR(30) COMMENT '员工名',
    lname VARCHAR(30) COMMENT '员工姓',
    hired DATE NOT NULL DEFAULT '1970-01-01' COMMENT '入职日期',
    separated DATE NOT NULL DEFAULT '9999-12-31' COMMENT '离职日期（9999-12-31 表示在职）',
    job_code INT NOT NULL COMMENT '职位代码',
    store_id INT NOT NULL COMMENT '所属门店ID（分区键）'
) COMMENT='员工基本信息表，按门店ID分区存储'
PARTITION BY RANGE (store_id) (  -- 按照门店ID范围进行分区
    PARTITION p0 VALUES LESS THAN (6)  COMMENT '门店ID < 6 的员工（分区 p0）',
    PARTITION p1 VALUES LESS THAN (11) COMMENT '6 <= 门店ID < 11 的员工（分区 p1）',
    PARTITION p2 VALUES LESS THAN (16) COMMENT '11 <= 门店ID < 16 的员工（分区 p2）',
    PARTITION p3 VALUES LESS THAN (21) COMMENT '16 <= 门店ID < 21 的员工（分区 p3）'
);