分区设计

将表分成多个文件存储

官方地址：https://dev.mysql.com/doc/refman/5.7/en/partitioning-overview.html

分区表应用场景

表非常大以至于无法全部都放在内存中

• 只在表的最后部分有热点数据，其他均是历史数据

分区表的数据更容易维护

• 批量删除大量数据可以使用清除整个分区的方式
• 对一个独立分区进行优化、检查、修复等操作

分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备

可以使用分区表来避免某些特殊的瓶颈

• innodb的单个索引的互斥访问
• ext3文件系统的inode锁竞争

可以备份和恢复独立的分区

创建分区表

官方地址：https://dev.mysql.com/doc/refman/5.7/en/partitioning-range.html

普通建表

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 NOT NULL,
    store_id INT NOT NULL
);

根据列值范围分区建表

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 NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

拓展范围

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 RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

按照日期存储（可以区分冷、热数据）

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

分区表的限制

一个表最多只能有1024个分区，在5.7版本的时候可以支持8196个分区

在早期的mysql中，分区表达式必须是整数或者是返回整数的表达式，在mysql5.5中，某些场景可以直接使用列来进行分区

如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来

分区表无法使用外键约束

分区表底层原理

• 分区表由多个相关的底层表实现，这个底层表也是由句柄对象标识，我们可以直接访问各个分区
• 存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引知识在各个底层表上各自加上一个完全相同的索引
• 从存储引擎的角度来看，底层表和普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分

select查询

当查询一个分区表的时候

• 分区层先打开并锁住所有的底层表
• 优化器先判断是否可以过滤部分分区
• 然后再调用对应的存储引擎接口访问各个分区的数据

insert操作

当写入一条记录的时候

• 分区层先打开并锁住所有的底层表
• 然后确定哪个分区接受这条记录
• 再将记录写入对应底层表

delete操作

当删除一条记录时

• 分区层先打开并锁住所有的底层表
• 然后确定数据对应的分区
• 最后对相应底层表进行删除操作

update操作

当更新一条记录时

• 分区层先打开并锁住所有的底层表
• mysql先确定需要更新的记录再哪个分区
• 然后取出数据并更新
• 再判断更新后的数据应该再哪个分区
• 最后对底层表进行写入操作
• 并对源数据所在的底层表进行删除操作

有些操作时支持过滤的

当删除一条记录时，MySQL需要先找到这条记录

• 如果where条件恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉，这对update同样有效
• 如果是insert操作，则本身就是只命中一个分区，其他分区都会被过滤掉
• mysql先确定这条记录属于哪个分区，再将记录写入对应得曾分区表，无须对任何其他分区进行操作

每个操作都会“先打开并锁住所有的底层表”

并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁，例如innodb，则会在分区层释放对应表锁

分区表的类型

范围分区

根据列值在给定范围内将行分配给分区

官方地址：https://dev.mysql.com/doc/refman/5.7/en/partitioning-range.html

列表分区

类似于按range分区，区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择

官方地址：https://dev.mysql.com/doc/refman/5.7/en/partitioning-list.html

列分区

mysql从5.5开始支持column分区，可以认为i是range和list的升级版

在5.5之后，可以使用column分区替代range和list，但是column分区只接受普通列不接受表达式

官方地址：https://dev.mysql.com/doc/refman/5.7/en/partitioning-columns.html

hash分区

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

这个函数可以包含myql中有效的、产生非负整数值的任何表达式

官方地址：https://dev.mysql.com/doc/refman/5.7/en/partitioning-hash.html

key分区

类似于hash分区，区别在于key分区只支持一列或多列，且mysql服务器提供其自身的哈希函数，必须有一列或多列包含整数值

官方地址：https://dev.mysql.com/doc/refman/5.7/en/partitioning-key.html

子分区

在分区的基础之上，再进行分区后存储

官方地址：https://dev.mysql.com/doc/refman/5.7/en/partitioning-subpartitions.html

如何使用分区表

如果需要从非常大的表中查询出某一段时间的记录，而这张表中包含很多年的历史数据，数据是按照时间排序的，此时应该如何查询数据呢？

• 因为数据量巨大，肯定不能在每次查询的时候都扫描全表。
• 考虑到索引在空间和维护上的消耗，使用索引，会发现会产生大量的碎片，还会产生大量的随机IO
• 但是当数据量超大的时候，索引也就无法起作用了，此时可以考虑使用分区来进行解决

使用分区表注意事项

• null值会使分区过滤无效
• 分区列和索引列不匹配，会导致查询无法进行分区过滤
• 选择分区的成本可能很高
• 打开并锁住所有底层表的成本可能很高
• 维护分区的成本可能很高