Details of mysql automatic partition
Why auto partition: because there are more than one million pieces of data in a table, and the query speed is very slow, the auto partition function is added to improve the query ability.
First, define the steps of partition:
- New backup table
- Delete new table partition
- Swap partition
- Delete old table partition
The specific implementation method is as follows
New backup table:
set @back=concat('create table Newly build schema Name.Partition name like Table name'); PREPARE stmt1 FROM @back; EXECUTE stmt1; DEALLOCATE PREPARE stmt1;
Delete new table partition
set @deleNewPartition=concat('alter table Newly build schema Name.Partition name REMOVE PARTITIONING'); PREPARE stmt1 FROM @deleNewPartition; EXECUTE stmt1; DEALLOCATE PREPARE stmt1;
Exchange partition table
set @exchangePartition=concat('alter table Table name exchange partition ',The name of the old table partition,' with table Newly build schema Name.Create partition name); PREPARE stmt2 FROM @exchangePartition; EXECUTE stmt2; DEALLOCATE PREPARE stmt2;
Delete old table partition
SET @v_delete=concat('alter table pack_data_info drop PARTITION 'Name of the old table partition); PREPARE stmt3 from @v_delete; EXECUTE stmt3; DEALLOCATE PREPARE stmt3;
SET @v_add=concat('ALTER TABLE pack_data_info ADD PARTITION (PARTITION Last partition name VALUES LESS THAN (TO_DAYS(Time of partition))'); PREPARE stmt4 from @v_add; EXECUTE stmt4; DEALLOCATE PREPARE stmt4;
Bad view area
SELECT partition_name,partition_expression,partition_description,table_rows FROM information_schema.PARTITIONS WHERE table_schema = SCHEMA() AND table_name='Table name'
create schema name
What is called schema: schema can be simply understood as a library. To create a schema is to create a database. We put the data to be backed up in this schema library.