分区设计
将表分成多个文件存储
官方地址: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值会使分区过滤无效
- 分区列和索引列不匹配,会导致查询无法进行分区过滤
- 选择分区的成本可能很高
- 打开并锁住所有底层表的成本可能很高
- 维护分区的成本可能很高
睁开眼,书在面前 闭上眼,书在心里