Practice of Partition Table under MySQL--RDS

Practical background

Some tablespaces in the project are too large and there are too many rows, so it is decided to divide some tables into databases and tables. When studying the selection scheme, it is found that some commonly used sub-library and sub-table solutions have more modifications to the business code, so I decided to adopt the partition scheme of MySQL.

In fact, in my personal opinion, partition table is MySQL to help us achieve the underlying database sub-table, do not need to involve business code changes, do not need to pay attention to distributed transactions. Because in terms of accessing the database, there is still only one table logically, but it is actually composed of multiple physical partition objects, and the specific partition will be queried according to the specific partition rules.

Introduce the table of this practice, the tablespace size is 172G, 120 million records. Zhengzhou Infertility Hospital: http://jbk.39.net/yiyuanzaixian/zztjyy/

Database version: RDS MySQL 5.6

Tool: Aliyun DTS

First, why the zoning? Advantages:

For data that has expired or does not need to be saved, you can delete data quickly by deleting the partitions related to the data, which is much more efficient than DELETE.

When partition conditions are included in the where clause, only one or more necessary partitions can be scanned to improve query efficiency

For example, the following statement:

Migration of SELECT * FROM t PARTITION (p0 ~ (P _ 1)) WHERE c B

Stop the task of writing data, and when the task queue is empty, wait a few minutes to pause and finish the migration task

Finally, modify the table name to complete the data migration and switching (it takes me some time to change the partition table name in the test environment, but RDS changes the table name in seconds)