本篇文章給大家帶來的內容是關于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.其他
-
查看表分區情況的SQL
select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_name='metric_data';
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
