Shulou(Shulou.com)06/01 Report--

The following mainly brings you detailed methods to optimize the horizontal partitioning of MySQL big data tables. I hope these words can bring you practical use, which is also the main purpose of this article. All right, don't talk too much nonsense, let's just read the following.

1. Create a partition table

The table fields of this table are the same as those of the original table, with partitions.

CREATE TABLE `metric_data_ tmp` (id bigint primary key auto_increment, metric varchar (12828), 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"))

two。 Copy the original table data to a temporary table

Directly through the insert statement

Insert into metric_data_tmp select * from metric_data

The amount of data is very large, so you can use select into outfile and Load data file to export and import.

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 partition and history tables:

Rename table metric_data to metric_data_bak;rename table metric_data_tmp to metric_data

4. Automatically create partitions for next month through the scheduled tasks of the database

Stored procedure

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 # is used to determine whether the table partition to be created already exists declare rows_cnt int unsigned; # the name of the declare target_date timest # partition when the table partition is to be created, in the format p201811 declare partition_name varchar (8) # the partition time to be created is next month set target_date = date_add (now (), interval 1 month); set partition_name = date_format (target_date,'p% Y% m') # determine whether the partition to be created exists 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),'% Ymuri% mmur01'), "') 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

Create scheduled tasks and regularly execute stored procedures to create partitions

DELIMITER $$# the database name of the table is USE `db_ orbit` $$CREATE EVENT IF NOT EXISTS `generate_partition_for_metric_ data`on SCHEDULE EVERY 1 MONTH # execution cycle, and days, months, etc. STARTS '2019-03-15 00:00:00'ON COMPLETION PRESERVEENABLECOMMENT' Creating partitions'DO BEGIN # calls the stored procedure just created. The first parameter is the database name. The second parameter is the table name CALL db_orbit.create_partition_by_month ('db_orbit',' metric_data') END$$DELIMITER

5. Other

Check the SQL of table partitions

Select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_name='metric_data'

For the above detailed methods to optimize the horizontal partitioning of MySQL big data tables, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.