creates a partitioned table from an existing table and migrates the data to a new table, which can be partitioned by time, and then the table is not updated in real time and inserts once a day.
time is relatively abundant, but there are other applications on the server, using smaller resources as the main way.
operation mode
@1 ALTER TABLE can be used to change the TABLE to a partitioned TABLE. This operation will create a partitioned TABLE, then automatically copy the data and then delete the original TABLE.
guess the server resource consumption is relatively large.
is similar to
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 partitioned table that looks like the original table, export the data from the original table, and then pour the data into the new table.
(original table primary key only id, but my partition field is stsdate, here the primary key should be changed to id,stsdate joint primary key, partition table requires partition field to be primary key or a part of the primary key)
operation procedure
adopts the second scheme. The partitioned table is created, the original table data is exported, the new table name is changed to the original table name, then inserted, and finally the normal index is created.
create 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
);
exported data
mysqldump -u dbname -p --no-create-info dbname apdailysts > apdailysts.sql
change the table name, import data (10 minutes to import, 200w, little more than 8g of data), test ok, delete the original table.
test can be used normally, stop work, observation for 2 days.
— 10.16
through these two days of observation, the page query speed from not open to the basic speed can be seconds open, this optimization is tenable.
) 
)
) never tried 