MySQL大数据表水平分区优化的详细步骤
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL大数据表水平分区优化的详细步骤,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3173字,纯文字阅读大概需要5分钟。
内容图文
![MySQL大数据表水平分区优化的详细步骤](/upload/InfoBanner/zyjiaocheng/537/72621822a8c94173afea305459478fa4.jpg)
将运行中的大表修改为分区表
本文章代码仅限于以数据时间按月水平分区,其他需求可自行修改代码实现
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
语句
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; #分区的名称,格式为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.其他
查看表分区情况的SQL
select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_name='metric_data';
以上就是MySQL大数据表水平分区优化的详细步骤的详细内容,更多请关注Gxl网其它相关文章!
内容总结
以上是互联网集市为您收集整理的MySQL大数据表水平分区优化的详细步骤全部内容,希望文章能够帮你解决MySQL大数据表水平分区优化的详细步骤所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。