The principles and pros and cons of MySQL partitioned tables

Known as one of the most popular open source database, MySQL is widely used in various scenarios, Alibaba Cloud provides high available ApsaraDB RDS for MySQL with enhanced MySQL service reduced enterprise’s database expenses, and helped enterprises utilize technology to fight against novel coronavirus.

1. Principle of Partitioned Tables

Partitioned tables are implemented by a number of related underlying tables, which are also represented by handle objects, so we can also access individual partitions directly, and the storage engine manages the various underlying tables of the partition and manages the normal tables (all underlying tables must use the same storage engine). The index of a partitioned table simply adds an identical index to each underlying table, and from the storage engine's point of view, the underlying table is no different from a normal table, and the storage engine does not need to know whether this is a normal table or part of a partitioned table.

The operations on the partitioned table are performed according to the following logic:

Select query:

When querying a partitioned table, the partition layer first opens and locks all the underlying tables, and the optimizer determines whether a partial partition can be filtered and then calls the corresponding storage engine interface to access the data for each partition

Insert operation:

When a record is written, the partition layer opens and locks all the underlying tables, then determines which partition accepts the record, and then writes the record to the corresponding underlying table

Delete operation:

When a record is deleted, the partition layer opens and locks all the underlying tables, then determines which partition the data corresponds to, and finally deletes the corresponding underlying table.

Update operation:

When a data is updated, the partition layer opens and locks all the underlying tables first,MySQL determines which partition the record needs to be updated, then extracts the data and updates it, then determines which partition the updated data should be placed on, then writes to the underlying table and deletes the underlying table where the original data resides.

Although each operation will open and lock all the underlying tables, this is not to say that the partition table is locked in the full table during processing, if the storage engine can implement its own row-level locks, such as:InnoDB, the partition layer will release the corresponding table locks, this lock and unlock process with the ordinary Similar to the query on InnoDB.

2. In the following scenario, the partition can play a very large role:

A: The table is so big that it can't all be in memory, or only the last part of the table has hot data, and all the rest is historical data

B: Partition table data is easier to maintain, such as: to bulk delete large amounts of data can use the way to clear the entire partition. In addition, you can optimize, check, repair, and so on for a separate partition

C: Data from partitioned tables can be distributed across different physical devices, enabling efficient use of multiple hardware devices

D: You can use partitioned tables to avoid specific bottlenecks such asmutually exclusive access to a single index of InnoDB,ext3 file system inode lock contention, etc.

E: You can also back up and restore separate partitions if needed, which works very well in very large data set scenarios

3. The partitioning itself has some limitations:

A: A table can have a maximum of 1024 partitions ( 8,192 partitions after mysql5.6)

B: In mysql5.1, the partition expression must be an integer, or an expression that returns an integer, after 5.5, some scenarios can be partitioned directly using string columns and date type columns (when using a varchar string type column, is usually the date of the string as a partition).

C: If there is a primary key or a unique index column in the Partition field, all primary key columns and unique index columns must be included, and if the table has a primary key or unique index, the partition key must be a primary key or a unique index

D: FOREIGN KEY constraints cannot be used in partitioned tables

The principles and pros and cons of MySQL partitioned tables