MySQL分区

MySQL分区优点：
1，和单个磁盘或者文件系统分区相比，可以存储更多数据
2，优化查询。在where子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率；同时涉及sum()和count()这类聚合查询时，可以容易的在每个分区上并行处理，最终只需要汇总所有分区得到的结果
3，对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据
4，跨多个磁盘来分散数据查询，以获得更大的查询吞吐量
【注：MySQL分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区；反过来也是一样的，不能只对索引分区而不对表分区，同时也不能只对表的一部分数据进行分区。MySQL的分区表上创建的索引也一定是本地的local索引】

 分区类型：
      1，range分区：基于一个给定连续区间范围，把数据分配到不同的分区【null代表最小值】
      2，list分区：类似range分区，区别在list分区是基于枚举出的值列表分区[无序]，range是基于给定的连续区间范围分区【list不支持values less than maxvalue(range支持)；所给出的值需要包含在表中】
      3，hash分区：基于给定的分区个数，把数据分配到不同的分区【基于分区个数的取模（%）运算。根据余数插入到指定的分区；常规hash的分区非常的简便，通过取模的方式可以让数据非常平均的分布每一个分区，但是由于分区在创建表的时候已经固定了。如果新增或者收缩分区的数据迁移比较大。】
      4，key分区：类似于hash分区【hash分区允许使用用户自己定义的表达式，而key分区只支持使用用户自定义的表达式，需要使用mysql服务器提供的HASH函数；同事HASH分区只支持整数分区，而key只支持使用出blob和text类型除外；】
      5，columns分区(mysql5.5)：解决了5.5版本之前range分区和list分区只支持整数分区

 【注：在5.1版本中，range分区，list分区，hash分区都要求分区键必须是int类型或者通过表达式返回int类型，唯一例外的就是key分区，可以使用其他类型的列(blob和text除外)做为分区键】
 【注：无论是哪种MySQL分区类型，要么分区表上没有主键/唯一键，要么分区的主键/唯一键都必须包含分区键】
 【注：分区名不区分大小写】

 range分区适用情况：
      1，当需要删除过期的数据的时候，删除一个分区比delete语句要有效的多
      2，经常运行包含分区键的查询，MySQL可以很快的确定只有某一个或者某些分区需要扫描

 columns分区：
      columns分区可以细分为range columns分区和list columns分区，range columns分区和list columns分区都支持证书，日期时间和字符串三大类型。如下，
      1，所有整数类型：tinyint，smallint，mediumint，int和bigint；其他数值类型都不支持，例如不支持decimal和datetime
      2，日期时间类型：date和datetime
      3，字符串类型：char，varchar，binary和varbinary；不支持text和blob类型作为分区键

【注：MySQL5.5中，columns分区仅支持一个或多个字段名作为分区键，不支持表达式作为分区键，区别于range分区和list分区】

 COLUMNS和RANGE和LIST分区的区别
      1.针对日期字段的分区就不需要再使用函数进行转换了，例如针对date字段进行分区不需要再使用YEAR()表达式进行转换。
      2.COLUMN分区支持多个字段作为分区键但是不支持表达式作为分区键。

【查询分区数据分布：
SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME=‘members’;
】
【注：字段分区正好处于某一区间上则属于下一区间；先比较a的值，当a的值小于分区键某一区间的值不用比较b，当a的值等于某一区间的值再比较b的值】
【注：分析执行计划的时候
explain partitions select id,joined from tb_partition.members where joined=‘1963-01-01’;可以发现只查找了一个分区
explain partitions select id,joined from tb_partition.members where joined=YEAR(now());查找所有分区
】
【注：RANGE COLUMN的多列分区第一列的分区值一定是顺序增长的，不能出现交叉值，第二列的值随便】
线性HASH的计算原理如下：
假设分区个数num=6,N表示数据最终存储的分区
sep1:V = POWER(2, CEILING(LOG(2, num))),LOG()是计算NUM以2为底的对数，CEILING()是向上取整，POWER()是取2的次方值；如果num的值是2的倍数那么这个表达式计算出来的结果不变。
V=POWER(2，CEILING(LOG(2，6)))
V=POWER(2,3)
V=8
sep2:N=values&(V-1);&位与运算，将两个值都转换成2进行求与运算，当都为1才为1；当num是2的倍数时由于V计算出来的结果不变，这时values&(V-1)=MOD(values/num)和时间HASH取模算出的结果是一致的，这时特殊情况只有当分区是2的倍数才是这种情况。values是YEAR(hired)的值
【增加分区：ALTER TABLE tblinhash add PARTITION partitions 4;减少分区：ALTER TABLE tblinhash COALESCE PARTITION 3;】

 KEY分区

KEY分区其实跟HASH分区差不多，不同点如下：

1. KEY分区允许多列，而HASH分区只允许一列。

2. 如果在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。

3. KEY分区对象必须为列，而不能是基于列的表达式。

4. KEY分区和HASH分区的算法不一样，PARTITION BY HASH (expr)，MOD取值的对象是expr返回的值，而PARTITION BY KEY (column_list)，基于的是列的MD5值。

   子分区
   子分区是分区表中每个分区的再次分割，子分区既可以使用HASH希分区，也可以使用KEY分区。这 也被称为复合分区（composite partitioning）。
   1，如果一个分区中创建了子分区，其他分区也要有子分区
   2，如果创建了了分区，每个分区中的子分区数必有相同
   3，同一分区内的子分区，名字不相同，不同分区内的子分区名子可以相同（5.1.50不适用）

   MySQL分区处理null值的方式：
   1，range分区中，null值会被当做最小值来处理；
   2，list分区中，null值必须出现在枚举列表中；
   3，hash/key分区中，null值会被当做零值来处理；
   【注：由于针对不同的分区类型，null值时而被当做零值来处理，时而被当做最小值处理，为了避免在处理null值时出现误判，更推荐通过设置字段非空和默认值来绕开MySQL默认对null值的处理】

   分区管理
   range&list分区
   删除分区(分区删除，表中记录也实际被删除，range新增原分区记录会添加到一下分区中去，然而list会由于原来枚举的值已不在，新增原分区记录会添加不进去)
   alter table drop partition 分区名
   新增分区(range分区只能添加新的分区到分区列表的最大一端；添加list分区的时候对一个固定的分区键值，必须指定并且只能指定一个唯一的分区，否则会出现错误)
   alter table add partition (partition p4[分区名] values less than (2030))
   重定义分区
   重新定义range分区的时候，只能重新定义相邻的分区，不能跳过range分区进行重新定义，同时重新定义的分区区间必须和原分区区间覆盖的区间相同(简单来说就是把原来的一个区间拆成两个区间)；也不能使用重新定义的分区来改变分区的类型，例如就是不能把range分区改变为hash分区，也不能把hash分区改变为range分区。
   重新定义list分区的时候，只能够重新定义相邻的分区，不能跳过list分区进行重新定义(list分区即使要新增一个分区到最后一行，这时候如果要和合并到底第一，三个，那么同时需要操作倒数第二个，把倒数第一个三合并在一起，到底第二个还是不变但是需要带上)，同事重新定义的分区区间必须和元分区区间覆盖相同的区间；不能重新定义分区来改变表分区的类型。

   HASH&key分区
   减少分区
   alter table 表名 coalesce partition 2；(4个家减少为两个)
   新增分区
   alter table 表名 add partition partitions 8；【注：这里增加八个是新增了八个而不是增加到八个，也就是说加上以前两个一共是十个】