這篇文章帶給大家的內容是關於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語句
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.其他
select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_name='metric_data';
以上是MySQL大數據表水平分區優化的詳細步驟的詳細內容。更多資訊請關注PHP中文網其他相關文章！
