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

本篇文章给大家带来的内容是关于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=utf8partition 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语句insert into metric_data_tmp select * from metric_data;数据量非常大，可使用select into outfile, Load data file方式导出导入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;#分区的名称，格式为p201811declare 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 thenset @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;elseselect 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 PRESERVEENABLECOMMENT 'Creating partitions'DO BEGIN#调用刚才创建的存储过程，第一个参数是数据库名称，第二个参数是表名称CALL db_orbit.create_partition_by_month('db_orbit', 'metric_data');END$$DELIMITER ;5.其他

查看表分区情况的SQLselectpartition_name part,partition_expression expr,partition_description descr,table_rowsfrom information_schema.partitions where table_name='metric_data';以上就是MySQL大数据表水平分区优化的详细步骤的详细内容，更多请关注小潘博客其它相关文章！