1. 确认MySQL服务器是否支持分区表

命令：

show plugins;

2. MySQL分区表的特点

• 在逻辑上为一个表，在物理上存储在多个文件中

HASH分区（HASH）

HASH分区的特点

• 根据MOD(分区键，分区数)的值把数据行存储到表的不同分区中
• 数据可以平均的分布在各个分区中
• HASH分区的键值必须是一个INT类型的值，或是通过函数可以转为INT类型

如何建立HASH分区表

以INT类型字段 customer_id为分区键

CREATE TABLE `customer_login_log` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户登录日志表'

PARTITION BY HASH(customer_id)  PARTITIONS 4;

以非INT类型字段 login_time 为分区键(需要先转换成INT类型)

CREATE TABLE `customer_login_log` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户登录日志表'

PARTITION BY HASH(UNIX_TIMESTAMP(login_time))  PARTITIONS 4;

customer_login_log 表如果不分区，在物理磁盘上文件为

customer_login_log.frm    # 存储表原数据信息
customer_login_log.ibd    # Innodb数据文件

如果按上面的建HASH分区表，则有五个文件

customer_login_log.frm    
customer_login_log#P#p0.ibd
customer_login_log#P#p1.ibd
customer_login_log#P#p2.ibd
customer_login_log#P#p3.ibd

演示

使用起来和不分区是一样的，看起来只有一个数据库，其实有多个分区文件，比如我们要插入一条数据，不需要指定分区，MySQL会自动帮我们处理

查询

范围分区（RANGE）

RANGE分区特点

• 根据分区键值的范围把数据行存储到表的不同分区中
• 多个分区的范围要连续，但是不能重叠
• 默认情况下使用VALUES LESS THAN属性，即每个分区不包括指定的那个值

如何建立RANGE分区

如果没有定义p3分区，当插入的customer_id大于29999时会报错，定义了则超过的数据都存入p3中

RANGE分区的适用场景

• 分区键为日期或是时间类型 （可以使得各个分区表的数据比较均衡，如果按上面的例子中以整型id为分区键，假如活跃用户集中在10000-19999之间，则p1中的数据量就会比其他分区的数据量大很多，这就失去了分区的意义；而且按时间类型分区，如果要按时间顺序进行数据的归档，则只需要对某一个分区进行归档就可以了）
• 所有查询中都包括分区键（避免跨分区查询）
• 定期按分区范围清理历史数据

LIST分区

LIST分区的特点

• 按分区键取值的列表进行分区
• 同范围分区一样，各分区的列表值不能重复
• 每一行数据必须能找到对应的分区列表，否则数据插入失败

如何建立LIST分区

如果插入一条login_type为10的数据行，则会报错

3. 如何为登录日志表（customer_login_log）分区

业务场景

• 用户每次登录都会记录customer_login_log日志
• 用户登录日志保存一年，1年后可以删除或者归档

登录日志表的分区类型及分区键

• 使用RANGE分区
• 以login_time为分区键

分区后的用户登录日志表

按年份分区存储，所以用YEAR函数进行了转化

CREATE TABLE `customer_login_log` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` DATETIME NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB 
PARTITION BY RANGE (YEAR(login_time))(
PARTITION p0 VALUES LESS THAN (2017),
PARTITION p1 VALUES LESS THAN (2018),
PARTITION p2 VALUES LESS THAN (2019)
) 

插入并查询数据

查询指定表中的分区数据情况

SELECT table_name,partition_name,partition_description,table_rows FROM
information_schema.`PARTITIONS`  WHERE table_name = 'customer_login_log';

再插入2条18年的日志，会存入p2表中

之前说过建立分区表时，最好建立一个MAXVALUE的分区，这里之所以没有建立，是为了数据维护的方便，如果我们建立了MAXVALUE分区，很容易忽视一个问题，当我们2019年有的数据插入时，会自动存入那个MAXVALUE分区中，之后在做数据维护时会不方便，所以没有建立MAXVALUE分区
而是通过计划任务的方式，在每年年底的时候增加这个分区，比如我们现在在2018年年底，我们需要在日志表中为2019年建立日志分区，否则2019年的日志都会插入失败

我们可以通过下面语句

增加分区

ALTER TABLE customer_login_log ADD PARTITION (PARTITION p3 VALUES LESS THAN(2020))

增加分区，并插入数据

删除分区

假如我们现在要删除2016年到2017年间一年的数据，因为我们已经做了分区，所以只需要通过一条语句，删除p0分区即可

ALTER TABLE customer_login_log D