MySql第二章，数据库分区
一、分区概述
数据分区是一种物理数据库的设计技术，它的目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。
分区并不是生成新的数据表，而是将表的数据均衡分摊到不同的硬盘，实际上还是一张表。另外，分区可以做到将表的数据均衡到不同的地方，提高数据检索的效率，降低数据库的频繁IO压力值 。
1、水平分区
这种形式分区是对表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。
举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。
注：这里具体使用的分区方式我们后面再说，可以先说一点，一定要通过某个属性列来分割，譬如这里使用的列就是年份。
2、垂直分区
这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。
举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。
在数据库供应商开始在他们的数据库引擎中建立分区（主要是水平分区）时，DBA和建模者必须设计好表的物理分区结构，不要保存冗余的数据（不同表中同时都包含父表中的数据）或相互联结成一个逻辑父对象（通常是视图）。这种做法会使水平分区的大部分功能失效，有时候也会对垂直分区产生影响。
3、分区的优点
1、数据分割，放置于不同的物理文件中2、存储更多的数据，突破单个文件存储的最大限度3、精准定位分区查询数据，不需要全表扫描查询，大大提高数据检索效率4、数据管理比较方便，比如要清理或废弃某年的数据，就可以直接删除该日期的分区数据即可5、可跨磁盘分区查询，提高磁盘的I/O性能6、SUM()和COUNT()等聚合函数查询，可以更容易的进行并行处理7、可备份和恢复独立的分区
二、分区类型
1>、range => 连续区间的列值：如0~99、100~1992>、list => 类似range,离散集合：如[1,3,5,7,9]、[2,4,6,8,10]3>、hash => 根据哈希函数分配，用户可自定义表达式。确保数据在预先确定数目的分区中平均分布4>、key => 类似hash，但是由MySQL自身提供的函数
注：
1、若表存在 primary key 【PK】或 unique key【UK】，分区函数列只能从PK或UK中取子集。若无PK和UK，则可随意指定列作为分区列。2、MySQL-5.5version以前分区键必须为整型如int，5.5以后支持非整型3、InnoDB支持分区。MyISAM mysql8以前支持，之后不再支持了。
查看数据库是否支持分区：
5.6版本以后使用如下：show plugins;
结果：
partition ACTIVE 即支持分区
三、分区创建
1、range分区（常用）
CREATE TABLE `shop_user_info` (`user_info_id` bigint(30) NOT NULL COMMENT '主键ID',`user_code` varchar(64) NOT NULL COMMENT '用户编码',`user_name` varchar(100) NOT NULL COMMENT '用户名称',`gender` int(2) DEFAULT NULL COMMENT '性别 1:女 2:男 3:保密',`age` int(3) DEFAULT NULL COMMENT '年龄',`create_time` timestamp NULL DEFAULT NULL COMMENT '信息创建时间',`update_time` timestamp NULL DEFAULT NULL COMMENT '信息修改时间'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='电商用户信息表'PARTITION BY RANGE (user_info_id)(PARTITION P0 VALUES LESS THAN (5),PARTITION P1 VALUES LESS THAN (10),PARTITION P2 VALUES LESS THAN (15),PARTITION P3 VALUES LESS THAN MAXVALUE
);
分区后默认的文件存放路径：
【自定义存放路径后面有写】
1.1、查看分区是否成功建立
show create table shop_user_info;
1.2、查询分区中的数据
select * from shop_user_info partition (p0);
1.3、查看数据所在分区
explain partitions select * from shop_user_info where user_info_id=1;
2、list分区（常用）
CREATE TABLE `shop_user_info2` (`user_info_id` bigint(30) NOT NULL COMMENT '主键ID',`user_code` varchar(64) NOT NULL COMMENT '用户编码',`user_name` varchar(100) NOT NULL COMMENT '用户名称',`gender` int(2) DEFAULT NULL COMMENT '性别 1:女 2:男 3:保密',`age` int(3) DEFAULT NULL COMMENT '年龄',`create_time` timestamp NULL DEFAULT NULL COMMENT '信息创建时间',`update_time` timestamp NULL DEFAULT NULL COMMENT '信息修改时间'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='电商用户信息表'PARTITION BY list (user_info_id)(PARTITION P0 VALUES in (1,3,5,7,9),PARTITION P1 VALUES in (2,4,6,8,10)
);
注：
因为在上面 定义分区的时候，user_info_id设置了 1 3 5 7 9 2 4 6 8 10
所以user_info_id的值也只能在1 3 5 7 9 2 4 6 8 10中
##插入语句会操作失败
insert into shop_user_info2 value (66,'u66','name66',2,20,'2020-03-09 12:22:33','2020-03-09 12:25:55');
3、hash分区
【只支持整数分区】
【由于range和list分区无法保证数据的平均分布，所以有了hash分区和key分区】
CREATE TABLE `shop_user_info3` (`user_info_id` bigint(30) NOT NULL COMMENT '主键ID',`user_code` varchar(64) NOT NULL COMMENT '用户编码',`user_name` varchar(100) NOT NULL COMMENT '用户名称',`gender` int(2) DEFAULT NULL COMMENT '性别 1:女 2:男 3:保密',`age` int(3) DEFAULT NULL COMMENT '年龄',`create_time` timestamp NULL DEFAULT NULL COMMENT '信息创建时间',`update_time` timestamp NULL DEFAULT NULL COMMENT '信息修改时间'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='电商用户信息表'PARTITION BY hash (user_info_id)
PARTITIONS 3;##这里数据会按 user_info_id % 3进行分配
注：
hash分区，数据的插入、更新、删除都会根据hash表达式计算一次选择分区，因此会有性能问题。
所以自定义表达式的时候尽量不要太复杂，最好为单列
4、linear hash 线性分区
partition by linear hash (id)
partition 3;
注：
线性分区在插入、更新、删除时性能会更好更快，但是数据会分布不均，所以一般不使用
5、key分区
key分区类似hash分区，但不允许自定义表达式，默认使用mysql自身提供的函数
可以分区除了支持整数分区外，还支持除了blob、text之外的分区
partition by linear key (id)
partition 3;
四、MySQL指定分区路径
MySQL可以针对分区表的每个分区指定各自的存储路径。
1、对于innodb存储引擎的表只能指定数据路径，因为数据和索引是存储在一个文件当中。2、对于MYISAM存储引擎可以分别指定数据文件和索引文件。3、一般也只有RANGE、LIST分区、sub子分区才有可能需要单独指定各个分区的路径，HASH和KEY分区的所有分区的路径都是一样。RANGE分区指定路径和LIST分区是一样的。
1、MyISAM存储引擎
CREATE TABLE th (id INT, createDate DATE)
engine='MyISAM'PARTITION BY LIST(YEAR(createDate))
(PARTITION p0 VALUES IN (1995, 1999, 2003)DATA DIRECTORY = '/data/data'INDEX DIRECTORY = '/data/idx',PARTITION p1 VALUES IN (1996, 2000, 2004)DATA DIRECTORY = '/data/data'INDEX DIRECTORY = '/data/idx',PARTITION p2 VALUES IN (1997, 2001, 2005)DATA DIRECTORY = '/data/data'INDEX DIRECTORY = '/data/idx',PARTITION p3 VALUES IN (1998, 2002, 2006)DATA DIRECTORY = '/data/data'INDEX DIRECTORY = '/data/idx'
);
2、INNODB存储引擎
CREATE TABLE thex (id INT, createDate DATE)
engine='InnoDB'
PARTITION BY LIST(YEAR(createDate))
(PARTITION p0 VALUES IN (1995, 1999, 2003)DATA DIRECTORY = '/data/data',PARTITION p1 VALUES IN (1996, 2000, 2004)DATA DIRECTORY = '/data/data',PARTITION p2 VALUES IN (1997, 2001, 2005)DATA DIRECTORY = '/data/data',PARTITION p3 VALUES IN (1998, 2002, 2006)DATA DIRECTORY = '/data/data');
五、子分区
子分区：分区表中可以对每个分区再次切割，适合保存大量的数据。
range和hash组合：
CREATE TABLE `shop_user_info4` (`user_info_id` bigint(30) NOT NULL COMMENT '主键ID',`user_code` varchar(64) NOT NULL COMMENT '用户编码',`user_name` varchar(100) NOT NULL COMMENT '用户名称',`gender` int(2) DEFAULT NULL COMMENT '性别 1:女 2:男 3:保密',`age` int(3) DEFAULT NULL COMMENT '年龄',`create_time` datetime NULL DEFAULT NULL COMMENT '信息创建时间',`update_time` datetime NULL DEFAULT NULL COMMENT '信息修改时间'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='电商用户信息表'partition by range (YEAR(create_time))
subpartition by hash (TO_DAYS(create_time))
subpartitions 2
(partition p0 values less than (2015),partition p1 values less than (2020),partition p2 values less than MAXVALUE
);
①、如果创建子分区，每一个分区必须有 相同数量 的子分区
②、对于已经通过range和list分区了的表，可再进行子分区。
子分区既可以用hash分区，也可用key分区====> 我们称之为 复合分区
③、若指定分区名称，每一个分区和子分区的名称必须全局唯一
partition by range (YEAR(create_time))
subpartition by hash (TO_DAYS(create_time))
(partition p0 values less than (2015)(subpartition s0,subpartition s1),partition p1 values less than (2020)(subpartition s2,subpartition s3),partition p2 values less than MAXVALUE(subpartition s4,subpartition s5)
);
六、分区管理
分区管理即：对分区进行 添加、删除、重新定义、合并、拆分等操作
1、range和list分区
1.1、删除分区–数据丢失
alter table users drop partition p0;##数据会直接丢失
##list分区drop删除后，对应的删除值就不可再添加了
1.2、添加分区
alter table users add partition (partition p4 values less than (100));##range添加分区只能往高处添加，不能删除了p0再添加p0.
##有MAXVALUE的，先把MAXVALUE所在的分区删除后，再添加新的.##list分区,不能添加已存在的值
1.3、重新定义–数据不丢失
拆分p1分区
alter table users reorganize partition p1 into(partition s0 values less than (5),partition s1 values less than (10)
);
合并s0、s1分区
alter table users reorganize partition s0,s1 into(partition p0 less than (10)
);
1.4、删除所有分区–保留数据
alter table users remove partitioning;
2、hash和key分区
【hash和key分区只能减少或增加分区数量，数据不会丢失】
减少分区数：
alter table users coalesce partition 1; ##减少1个分区
添加分区数：
alter table users add partition partitions 2; ##添加2个分区
3、重建分区
alter table users rebuild partition p2,p3;##先删除分区中的记录，然后再重新插入。可用于整理分区碎片
4、分析分区
alter table users analyze partition p2,p3;##读取并保存分区键分布
5、检查分区
alter table users check partition p2,p3;##检查分区中的数据or索引是否被破坏
6、修补分区
alter table users repair partition p2,p3;##修补被破坏的分区
7、优化分区 = 分析+检查+修补分区
alter table users optimize partition p2,p3;##若删除大量行，优化后可收回没有使用的空间，并整理分区的碎片
七、注意事项
1、MySql分区处理NULL的方式
若分区键有的值为NULL，MySQL会默认将NULL视为0做处理，但这样数据就会分布不均匀，
所以我们在设计表和分区时尽量将分区列声明为 NOT NULL
2、注意
1、最大分区数不超过1024，一般建议不超过150个2、若有PK或UK，必须包含在分区键内3、不支持外键4、不支持全文索引，对分区表的分区键创建索引，那么这个索引也将被分区5、一般会按照 整数 or 日期 分区6、只有range和list可进行子分区，hash和key不可以7、临时表不能被分区8、分区表对单条记录的查询无优势9、注意分区成本（分区后数据的增删改性能会有影响）10、分区字段尽量不要使用NULL
】)
