MySQL 分区

分区

分区是将一个表或索引分解成多个更小，更可管理的部分。每个区都是独立的，可以独立处理，也可以作为一个更大对象的一部分进行处理。业务代码无需改动。

分区的好处是：

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

分区的限制和缺点：

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

---

1.RANGE分区：

基于属于一个给定连续区间的列值，把多行分配给分区。

## RANGE  通常是根据日期进行分区，且RANGE 对日期类似查询函数to_days()等有优化


#创建时分区
CREATE TABLE employees (
    id INT NOT NULL,
    job_code INT NOT NULL,
)
PARTITION BY RANGE (job_code) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (10000)
)；
##上边这个就小于100是一个分区，100<x<1000的单独一个分区，1000<x<10000是一个。




#修改为分区
 ALTER TABLE test_rpt_office_lvl
    PARTITION BY RANGE COLUMNS(data_date) ( 
    PARTITION trol_201708 VALUES LESS THAN ('2017-09-01'),
    PARTITION trol_201709 VALUES LESS THAN ('2017-10-01'),
    PARTITION trol_201710 VALUES LESS THAN ('2017-11-01')
    );
##这个是日期小于2017-09-01的是一个分区trol_201708，
##2017-09-01到2017-10-01是一个trol_201709，
##2017-10-01到2017-11-01是一个trol_201710。


##如果查询时传入2017-10-23，所以只会去2017-10-01到2017-11-01的分区trol_201710去查询，不去其他分区。

2.List分区

类似RANGE分区，只不过LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

PARTITION BY LIST(store_id)

    PARTITION pNorth VALUES IN (3,5,6,9,17),

    PARTITION pEast VALUES IN (1,2,10,11,19,20),

    PARTITION pWest VALUES IN (4,12,13,14,18),

    PARTITION pCentral VALUES IN (7,8,15,16)

)

3.HASH分区

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

CREATE TABLE employees (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

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

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job_code INT,

    store_id INT

)

PARTITION BY HASH(YEAR(hired))

PARTITIONS 4；

4.KEY分区

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

CREATE TABLE tk (

    col1 INT NOT NULL,

    col2 CHAR(5),

    col3 DATE

) 

PARTITION BY LINEAR KEY (col1)

PARTITIONS 3;

 

参考：

https://www.cnblogs.com/adolfmc/p/11497261.html

https://www.cnblogs.com/xuchunlin/p/6252330.html