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

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

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

1. 创建一张分区表

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

1 2 3 4 5 6 7 8 9 10 11 12

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语句

1	insert into metric_data_tmp select * from metric_data;

• 数据量非常大，可使用select into outfile, Load data file方式导出导入

1 2	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. 重命名分区表和历史表：

1 2	rename table metric_data to metric_data_bak; rename table metric_data_tmp to metric_data;

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

• 存储过程

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

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 ;

• 创建定时任务，定时执行存储过程创建分区

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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.其他

• 查看表分区情况的SQL

1 2 3 4 5 6

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