MySQL partitions existing tables in the data table

Create a partitioned table for an existing table and migrate the data to the new table. The table can be partitioned by time. However, this table is not updated in real time and there is one insert operation per day.
There is plenty of time, but there are other applications on the server, so using smaller resources is the main approach.

How to operate

@1 You can use ALTER TABLE to change the table to a partitioned table. This operation will create a partitioned table, automatically copy the data, and then delete the original table.
I guess the server resource consumption is relatively large.

Similar operations

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 new partition table that is the same as the original table, then export the data from the original table and then import it into the new table.
(The original table primary key is only id, and my partition field is stsdate. Here the primary key should be changed to id, stsdate joint primary key. The partition table requires the partition field to be the primary key or part of the primary key.)

Operation process

Adopt the second option. First create a partitioned table, then export the original table data, change the new table name to the original table name, then insert, and finally create a normal index.

Create a partition table

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
);

Exporting Data

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

Modify the table name, import data (it took 10 minutes to import 2 million data, a little over 8GB), test it and it is ok, then delete the original table.

The test is working fine, and we will observe for 2 days. .

– 10.16
Through two days of observation, the page query speed has gone from being unable to open to being able to open in seconds, and this optimization is effective.

This is the end of this article about how to partition an existing table in MySQL. For more information about how to partition an existing table in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!