下文主要给大家带来优化MySQL大数据表水平分区的详细方法,希望这些文字能够带给大家实际用处,这也是我编辑优化MySQL大数据表水平分区的详细方法这篇文章的主要目的。好了,废话不多说,大家直接看下文吧。
1。创建一张分区表
这张表的表字段和原表的字段一摸一样,附带分区
创建表“metric_data_tmp”( id auto_increment bigint主键, 度量varchar (128), datadt datetime not null unqine, 6)值小数(30日 )引擎=InnoDB AUTO_INCREMENT=0的默认字符集=utf8 范围分区(to_days (DATADT)) ( 分区p201811值小于(to_days (“2018 - 12 - 01 -“)), 分区p201812值小于(to_days (“2019 - 01 - 01 -“)), 分区p201901值小于(to_days (“2019 - 02 - 01 -“)), 分区p201902值小于(to_days (“2019 - 03 - 01 -“)), );
2。将原表数据复制到临时表
直接通过插入语句
<>之前插入metric_data_tmp select * from metric_data;数据量非常大,可使用选择输出文件,加载数据文件方式导出导入
select *到输出文件& # 39;data.txt& # 39;& # 39;字段终止,& # 39;从metric_data; 数据加载INFILE & # 39; data.txt& # 39;到表metric_data_tmp字段终止& # 39;& # 39;;
3。重命名分区表和历史表:
<>之前重命名表metric_data metric_data_bak; 重命名表metric_data_tmp metric_data;4。通过数据库的定时任务定时自动创建下月的分区
存储过程
分隔符$ $ 使用‘db_orbit’$ $ 下降过程中如果存在create_partition_by_month $ $ 创建过程“create_partition_by_month”(in_schemaname varchar (64), in_tablename varchar (64)) 开始 #用于判断需要创建的表分区是否已经存在 声明rows_cnt int无符号; #要创建表分区的时间 声明target_date时间戳; #分区的名称,格式为p201811 声明partition_name varchar (8); #要创建的分区时间为下个月 设置target_date=date_add(现在(),间隔1个月); 设置partition_name=date_format (target_date & # 39; p Y % %猴# 39;); #判断要创建的分区是否存在 (1)从information_schema rows_cnt选择计数。分区t table_schema=in_schemaname table_name=in_tablename ifnull (t。partition_name & # 39; & # 39;)=partition_name; 如果rows_cnt=0 设置@sql=concat ( & # 39;alter table " & # 39; in_schemaname, & # 39;”。“& # 39; in_tablename, & # 39;“& # 39; & # 39;添加分区(分区& # 39; partition_name, “;值小于(to_days (& # 39;“, date_format (DATE_ADD (target_date,间隔1个月),& # 39;Y % - % m-01& # 39;), “& # 39;))引擎=innodb);“ ); 准备从@sql支撑; 执行支撑; 释放准备支撑; 其他的 选择concat(“分区的partition_name“,“表”“、in_schemaname“干净,in_tablename,“‘已经exists")结果; 如果; 结束$ $ 分隔符;
创建定时任务,定时执行存储过程创建分区
分隔符$ $ #该表所在的数据库名称 使用‘db_orbit’$ $ 创建事件如果不存在“generate_partition_for_metric_data” 按计划每1个月#执行周期,还有天,月等等 开始& # 39;2019-03-15就是# 39; 完成后保存 启用 评论& # 39;创建分区# 39; 做开始 #调用刚才创建的存储过程,第一个参数是数据库名称,第二个参数是表名称 调用db_orbit.create_partition_by_month (& # 39; db_orbit& # 39; & # 39; metric_data& # 39;); 结束$ $ 分隔符;
5。其他
查看表分区情况的SQL
<>之前的选择 partition_name部分, partition_expression expr。 partition_description备注说明, table_rows 从information_schema。分区table_name=& # 39; metric_data& # 39;;对于以上关于优化MySQL大数据表水平分区的详细方法,大家是不是觉得非常有帮助。如果需要了解更多内容,请继续关注我们的行业资讯,相信你会喜欢上这些内容的。
