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

This article introduces the relevant knowledge of "what is the implementation method of partitioning tables for existing tables in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Mode of operation

@ 1 can use ALTER TABLE to change the table to a partitioned table, which creates a partitioned table, then automatically copy the data and then deletes the original table

Guess that the server consumes a lot of resources.

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 new partitioned table that is the same as the original table, then export the data from the original table, and then pour into the new table.

(the original table primary key is only id, but my partition field is stsdate, where the primary key is changed to id,stsdate federated primary key, and the partition table requires the partition field to be a primary key or part of a primary key)

Operation process

The second scheme is adopted. First create a partitioned table, then export the original table data, change the name of the new table to the original table name, then insert it, and finally establish a general 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)

Export data

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

Change the table name, import the data (200w, a little more than 8g in 10 minutes), test the ok, and delete the original table.

The test can be used normally, finish work and observe for 2 days. no, no, no.

-10.16

Through the observation of these two days, the query speed of the page has changed from unable to open to the speed that can be opened in seconds. This optimization is valid.

This is the end of the content of "what is the implementation method of MySQL partitioning the existing tables of data tables". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.