Use mysql table partition back to optimize tables with large data volumes

Using mysql table partitions to optimize tables with large data volumes according to the actual situation of the company database, order tables may expand faster than expected. Here, you may need to prepare an optimization solution in advance, the traditional solution is table sharding or database sharding, but currently the best solution is to use mysql table partitions for optimization. However, after the table partition is created, the mysql query cache will become invalid.

Using mysql table partitions to optimize tables with large data volumes according to the actual situation of the company database, order tables may expand faster than expected. Here, you may need to prepare an optimization solution in advance, the traditional solution is table sharding or database sharding, but currently the best solution is to use mysql table partitions for optimization. However, after the table partition is created, the mysql query cache will become invalid.

Use mysql table partitions to optimize tables with large data volumes

According to the actual situation of the company's database, the Order table may be faster than expected, and the optimization scheme may need to be prepared in advance. The traditional scheme is table sharding or database sharding, however, the best solution is to optimize table partitions in mysql. However, it should be noted that after the table partition is created, the mysql query cache will become invalid. It can be said that the benefits of temporary table sharding are that the update, deletion, and lock processing time will be reduced, however, if the query is not for table partition fields, the query time will increase due to invalid query cache, which requires a trade-off.

Step 1: Because table partitions must be created when the table is created, the existing tables that have not created the table partition rules need to be re-imported. The method is as follows:

# HASH Table Partitioning is used here. mysql will automatically allocate data to different table partitions Based on the HASH field. This situation is applicable when there is no table partition rule but there is a need for table sharding for query optimization. Create table 'creater _ Bak' ('id' int (11) not null, 'name' varchar (100) default null, primary key ('id') ENGINE = InnoDB default charset = utf8PARTITION by hash (id) PARTITIONS 2

? Import the original table data after creation:

insert into creater_bak select * from creater;

? The new table data after import is distributed in two different table partitions.

If the data volume is very large and the number of partitions in the preset table is too small, you can add new table partitions and mysql will automatically re-allocate them:

# Eight new table partitions are added here, and two new table partitions are added. A total of 10 table partitions are alter table 'creater _ Bak' add partition partitions 8;

? Modify the table name to the original table name.

PS: The following table partitions are in the RANGE format. Note the same for HASH table partitions:

1. If you use RANGE to partition a table, you must set rules, for example:

CREATE TABLE `creater_bak` (  `id` int(11) NOT NULL,  `name` varchar(100) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8PARTITION BY RANGE(id) (    PARTITION p0 VALUES LESS THAN (500),    PARTITION p1 VALUES LESS THAN (1000),    PARTITION p2 VALUES LESS THAN MAXVALUE)

? 2. If you want to modify a table partition with rules, note that only new partitions can be added. Do not delete them at will. Deleting a table partition will also delete the internal data of the table partition. Pay attention to this. In addition, if MAXVALUE is set, it cannot be added. Although the table partition of MAXVALUE can be added after it is deleted, check whether the deleted MAXVALUE partition contains data. If yes, you cannot delete it at will, the best way is to re-create a new table. When creating a new table, re-create the rule and import the old table to the new table. This ensures that no data is lost. Although it is better not to delete partitions, the following describes how to delete table partitions and add new table partitions:

# Delete the PARTITION of the MAXVALUE rule TABLE above (if the PARTITION has data, do not use this operation) alter table 'creater _ Bak' drop PARTITION p2; # Add a PARTITION of the rule TABLE, note that an error will be reported if you add a value based on the Rule step. The step size is 500 alter table 'creater _ Bak' add PARTITION (PARTITION p2 values less than (1500 )) alter table 'creater _ Bak' add PARTITION (PARTITION p3 values less than maxvalue)

? Finally, use the following statement to view the partition search information:

EXPLAIN PARTITIONS select * from `creater_bak` b1 where b1.`id`=11

? At last, the official Chinese documents are attached:

? Http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html

Enhanced MySQL 5.5 partition functions