Implementation of partition table of existing data table by MySQL

Source of the article ： Learn through http://www.bdgxy.com/

Create a partition table for an existing table , And migrate the data to the new table , You can partition by time , Then the table is not updated in real time , Insert once a day .

There's plenty of time , But there are other applications on the server , Using smaller resources is the main way .

Mode of operation

@1 have access to ALTER TABLE To change the table into a partitioned table , This operation will create a partition table , Then automatically perform data processing copy Then delete the original table ,
Guess that the server resource consumption is relatively large .

Similar operation

ALTER TABLE tbl_rtdata PARTITION BY RANGE (Month(fld_date))
(   
PARTITION p_Apr VALUES LESS THAN (TO_DAYS('2012-05-01')),
PARTITION p_May VALUES LESS THAN (TO_DAYS('2012-06-01')), 
PARTITION p_Dec VALUES LESS THAN MAXVALUE );

@2 Create a partition table that is the same as the original table , Then export the data from the original table , Then pour in the new watch .
（ The primary key of the original table is only id, And my partition field is stsdate, Here, the primary key should be changed to id,stsdate Combined the primary key , Partition table requires partition fields to be primary keys or part of primary keys ）

The operation process

Adopt the second scheme . First create a partition table , Then export the original table data , The new table name is changed to the original table name , Then insert , Finally, establish a general index .

Create partition tables

CREATE TABLE `apdailysts_p` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `ap_id` INT(11) NOT NULL,
  `mac` VARCHAR(17) NOT NULL,
  `liveinfo` LONGTEXT NOT NULL,
  `livetime` INT(11) NOT NULL,
  `stsdate` DATE NOT NULL,
  `lastmodified` DATETIME NOT NULL,
   PRIMARY KEY (`id`, `stsdate`)
) 
PARTITION BY RANGE COLUMNS(stsdate) (
    PARTITION p0 VALUES LESS THAN ('2016-06-01'),
    PARTITION p1 VALUES LESS THAN ('2016-07-01'),
    PARTITION p2 VALUES LESS THAN ('2016-08-01'),
    PARTITION p3 VALUES LESS THAN ('2016-09-01'),
    PARTITION p4 VALUES LESS THAN ('2016-10-01'),
    PARTITION p5 VALUES LESS THAN ('2016-11-01'),
    PARTITION p6 VALUES LESS THAN ('2016-12-01'),
    PARTITION p7 VALUES LESS THAN ('2017-01-01'),
    PARTITION p8 VALUES LESS THAN ('2017-02-01'),
    PARTITION p9 VALUES LESS THAN ('2017-03-01'),
    PARTITION p10 VALUES LESS THAN ('2017-05-01'),
    PARTITION p11 VALUES LESS THAN ('2017-06-01'),
    PARTITION p12 VALUES LESS THAN ('2017-07-01'),
    PARTITION p13 VALUES LESS THAN ('2017-08-01'),
    PARTITION p14 VALUES LESS THAN ('2017-09-01'),
    PARTITION p15 VALUES LESS THAN MAXVALUE
);

Derived data

mysqldump -u dbname -p --no-create-info dbname apdailysts  > apdailysts.sql

Modify the name of the table , Import data (10 The import is finished in minutes ,200w, 8g A little more data ), Under test ok, Delete the original table .

The test can be used normally , Call it a day , Observe 2 God ..

– 10.16
Through these two days of observation , The query speed of the page has changed from unable to open to the speed of seconds , This optimization is established .

This is about MySQL This is the end of the article on the implementation of partitioning the existing table of the data table , More about MySQL There is already a table to partition. Please search the rookie tutorial www.piaodoo.com Previous articles or continue to browse the relevant articles below. I hope you can support rookie tutorials in the future www.piaodoo.com！