MySQL大数据表水平分区优化的详细步骤

将运行中的大表修改为分区表

本文章代码仅限于以数据时间按月水平分区，其他需求可自行修改代码实现

1. 创建一张分区表

这张表的表字段和原表的字段一摸一样，附带分区

CREATE TABLE `metric_data_tmp` (
 id bigint primary key auto_increment,
 metric varchar(128),
 datadt datetime not null unqine,
 value decimal(30, 6)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8
partition by range (to_days(DATADT)) (
 PARTITION p201811 VALUES LESS THAN (to_days("2018-12-01")),
 PARTITION p201812 VALUES LESS THAN (to_days("2019-01-01")),
 PARTITION p201901 VALUES LESS THAN (to_days("2019-02-01")),
 PARTITION p201902 VALUES LESS THAN (to_days("2019-03-01")),
);

2. 将原表数据复制到临时表

insert into metric_data_tmp select * from metric_data;

SELECT * INTO OUTFILE 'data.txt' FIELDS TERMINATED BY ',' FROM metric_data;
LOAD DATA INFILE 'data.txt' INTO TABLE metric_data_tmp FIELDS TERMINATED BY ',';

3. 重命名分区表和历史表：

rename table metric_data to metric_data_bak;
rename table metric_data_tmp to metric_data;

4. 通过数据库的定时任务定时自动创建下月的分区

delimiter $$
use `db_orbit`$$
drop procedure if exists `create_partition_by_month`$$
create procedure `create_partition_by_month`(in_schemaname varchar(64), in_tablename varchar(64))
begin
 # 用于判断需要创建的表分区是否已经存在
 declare rows_cnt int unsigned;
 # 要创建表分区的时间
 declare target_date timestamp;
 #分区的名称，格式为p201811
 declare partition_name varchar(8);
 
 #要创建的分区时间为下个月
 set target_date = date_add(now(), interval 1 month);
 set partition_name = date_format( target_date, 'p%Y%m' );
 
 # 判断要创建的分区是否存在
 select count(1) into rows_cnt from information_schema.partitions t where table_schema = in_schemaname and table_name = in_tablename and ifnull(t.partition_name, '') = partition_name;
 if rows_cnt = 0 then
 set @sql = concat(
 'alter table `', 
 in_schemaname, 
 '`.`', 
 in_tablename, 
 '`',
 ' add partition (partition ', 
 partition_name, 
 " values less than (to_days('",
 date_format(DATE_ADD(target_date, INTERVAL 1 month), '%Y-%m-01'), 
 "')) engine = innodb);" 
 );
 prepare stmt from @sql;
 execute stmt;
 deallocate prepare stmt;
 else
 select concat("partition `", partition_name, "` for table `",in_schemaname, ".", in_tablename, "` already exists") as result;
 end if;
end$$
delimiter ;

DELIMITER $$
#该表所在的数据库名称
USE `db_orbit`$$
CREATE EVENT IF NOT EXISTS `generate_partition_for_metric_data`
ON SCHEDULE EVERY 1 MONTH #执行周期，还有天、月等等
STARTS '2019-03-15 00:00:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Creating partitions'
DO BEGIN
 #调用刚才创建的存储过程，第一个参数是数据库名称，第二个参数是表名称
 CALL db_orbit.create_partition_by_month('db_orbit', 'metric_data');
END$$
DELIMITER ;

5.其他

select 
 partition_name part, 
 partition_expression expr, 
 partition_description descr, 
 table_rows 
from information_schema.partitions where table_name='metric_data';