Mysql已有亿级数据大表按时间分区

上一篇Mysql数据库快速插入亿级数据，介绍了如何造亿级数据。OK，现在数据有了，怎么分区？常见的思路有两个：
①使用ALTER TABLE创建分区；
②先创建一张与原来一样的新表，对新的空表分区，然后将原表数据备份到新表，然后删除原表，将新表改名为原表名。
下面就来实践这两种思路。

原表sql：

CREATE TABLE `t_send_message_send` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `plan_id` bigint(20) DEFAULT NULL,
  `job_uuid` varchar(36) DEFAULT NULL,
  `send_port` varchar(16) DEFAULT NULL,
  `mobile` varchar(16) DEFAULT NULL,
  `content` varchar(200) DEFAULT NULL,
  `product_code` varchar(16) DEFAULT 'HELP',
  `fake` bit(1) DEFAULT b'0',
  `date_push` datetime DEFAULT NULL,
  `activity_id` bigint(20) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `mobile` (`mobile`),
  KEY `date_push` (`date_push`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

原表一个自增主键id，两个索引mobile、date_push，数据量如下图：

1. 思路1：ALTER TABLE创建分区
①删除原有主键，创建联合主键。至于为什么，参见这边博文。MYSQL的分区字段，必须包含在主键字段内
脚本如下：

ALTER TABLE `t_send_message_send` DROP PRIMARY KEY,ADD PRIMARY KEY (id, date_push);

②创建分区。

ALTER TABLE `t_send_message_send` PARTITION by RANGE COLUMNS (date_push)
(PARTITION p2016 VALUES LESS THAN ('2017-01-01') ENGINE = InnoDB,
 PARTITION p2017 VALUES LESS THAN ('2018-01-01') ENGINE = InnoDB,
 PARTITION p2018 VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB,
 PARTITION p2019 VALUES LESS THAN ('2020-01-01') ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN ('2021-01-01') ENGINE = InnoDB);

如果数据库中存在date_push大于2021-01-01的记录，将会报错，因为没有可以用来存放该条记录的分区。需要在脚本最后加上

PARTITION pend VALUES LESS THAN (maxvalue) ENGINE = InnoDB;

我的数据是可以不用加上这一句的，因为我造的数据肯定没有2021-01-01往后的记录。没有这一句的好处是，以后可以方便新增分区，就像这样：

ALTER TABLE `t_send_message_send` ADD PARTITION (partition p2021 VALUES LESS THAN('2022-01-01'));

这样思路1就完成了。实际上，我并没有实践完成。第①步都没有走完，太耗时，中途我就中止了。我当时另外建了一张一样的表，插入了20万条记录。执行第①步，大概花了1分钟，执行第②步，也花了点时间，具体多少，忘了。早在实践之前就通过其他博文知道已经有亿级记录的大表按这么操作是不行的。当然也不是说这种方法不行，在数据量还少的时候，创建分区表是可行的。

2. 思路2：建新表–>备份–>删原表–>改名
①建新表

	CREATE TABLE `t_send_message_send2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `plan_id` bigint(20) DEFAULT NULL,
  `job_uuid` varchar(36) DEFAULT NULL,
  `send_port` varchar(16) DEFAULT NULL,
  `mobile` varchar(16) DEFAULT NULL,
  `content` varchar(200) DEFAULT NULL,
  `product_code` varchar(16) DEFAULT 'HELP',
  `fake` bit(1) DEFAULT b'0',
  `date_push` datetime NOT NULL,
  `activity_id` bigint(20) DEFAULT '0',
  PRIMARY KEY (`id`,`date_push`),
  KEY `mobile` (`mobile`),
  KEY `date_push` (`date_push`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE  COLUMNS(date_push)
(PARTITION p2016 VALUES LESS THAN ('2017-01-01') ENGINE = InnoDB,
 PARTITION p2017 VALUES LESS THAN ('2018-01-01') ENGINE = InnoDB,
 PARTITION p2018 VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB,
 PARTITION p2019 VALUES LESS THAN ('2020-01-01') ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN ('2021-01-01') ENGINE = InnoDB);

这一步完成了建表、建索引、创建联合主键、创建分区。当然也可以跟思路1一样一步一步来：a、创建表；b、drop原主键，创建联合主键；c、创建分区。

②备份
备份的方式有两类：
a、在线备份
数据一直在数据库中不离线。

insert into t_send_message_send2 (select * from t_send_message_send);

b、离线备份
数据先导出到本地，再从本地导回数据库。

③删原表

drop table t_send_message_send;

注意：删原表前一定要确认数据备份完成，且完整。
备份之前可以先count下原表记录条数，备份后也count下新表记录条数，没法一条一条看数据，数下条数对不对还是必要的。如果实在下不去手，把原表改名也是可以的。

rename table t_send_message_send to t_send_message_send_bak;

④改名

rename table t_send_message_send2 to t_send_message_send;

这样思路2也完成了。实际上，第①③④步操作几乎是不耗时间的，最耗时的操作就是第②步备份。不管是在线备份还是离线备份，都非常耗时。具体耗时多久，咱下一篇博文揭晓。

附：
1、查询表分区情况

select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema = schema() and table_name='t_send_message_send';

2、查询表分区数据

select * from t_send_message_send partition(p2020);