Mysql 分区

mysql 从5.1开始支持分区，分区的话又可以分为水平分区跟垂直分区

水平分区（根据列属性按行分）

水平分区的几种模式：

• Range（范围） – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980's）的数据，90年代（1990's）的数据以及任何在2000年（包括2000年）后的数据。 
• Hash（哈希） – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区，。例如DBA可以建立一个对表主键进行分区的表。 
• Key（键值） – 上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。 
• List（预定义列表） – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2000年2001年和2002年值所对应的数据。 
• Composite（复合模式） - 很神秘吧，哈哈，其实是以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。 

垂直分区（按列分）
举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。

Partition by 分区算法 (分区参数) 分区选项
(*分区所参与的字段必须为主键的一部分)

例子：

CREATE TABLE `customer` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `init_time` datetime NOT NULL COMMENT '时间',
  PRIMARY KEY (`id`,`init_time`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户表'
/*!50100 PARTITION BY RANGE (YEAR(init_time))
SUBPARTITION BY HASH (MONTH((init_time)))
SUBPARTITIONS 12
(PARTITION p0 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2021) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

分析：

上面 50100 是 mysql 的特有的注释，就是 mysql 可以执行的意思，表示 mysql 版本在 5.0.1以上才能执行

1、/*...*/ 是注释，mysql不会执行。
2、mysql对标准sql进行了扩展，包含了一些自己的特性。
3、/*!...*/ 是一种特殊的注释，其他的数据库产品当然不会执行。mysql特殊处理，会选择性的执行。可以认为是：预编译中的条件编译。
4、特别注意 50100，它表示5.01.00 版本或者更高的版本，才执行。

主分区按年份进行分区，总共3个，子分区按月份进行分区，总共12个

 

 或者：

ALTER TABLE `customer` 
PARTITION BY RANGE (YEAR(init_time)) PARTITIONS 3 
SUBPARTITION BY HASH (MONTH((init_time))) SUBPARTITIONS 12 ( #子分区按月分成12个
 PARTITION `p0` VALUES LESS THAN (2020),#19年
 PARTITION `p1` VALUES LESS THAN (2021),#20年
 PARTITION `p2` VALUES LESS THAN (MAXVALUE)#21年后
);

 

 修改分区：

#拆分增加分区：将p2拆分成p2和p3
ALTER TABLE customer REORGANIZE PARTITION p2 INTO (
 PARTITION p2 VALUES LESS THAN (2022),
 PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

 

报错解决：

报错：A PRIMARY KEY must include all columns in the table’s partitioning function 
意思是说分区的字段必须是要包含在主键当中。 
所以当我们在使用 日期进行分区的时候,就需要将日期字段包含到主键当中去,

查看表分区数据量：
SELECT PARTITION_NAME,SUBPARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'customer';

 

备注：

1、分区和分表的区别就是，数据迁移麻烦点，分表可以一张表一张表慢慢迁移，就是分表的多张表可以同时迁移，分区只能慢慢导出

2、还有就是如果不是按照id主键分，按照时间等字段分，就是加到主键列里，这样id没有唯一限制，需要代码层次的限制唯一性，主键列表有唯一性，但是单个id没有唯一性，如果按照主键id分就不会有这个问题

3、分区是把数据分成一个个分区文件，从而减小数据文件大小，减少数据查询的量