MySQL partition table

A partitioned table is a separate logical table for the user, but the bottom layer consists of multiple physical sub-tables. The code that implements the partition is actually the encapsulation of the handle object of a set of underlying tables.

MySQL uses the partition by clause to define the data stored in each partition when creating the table. When executing a query, the optimizer filters the partitions that do not have the data we need based on the partition definition, so that the query does not have to scan all the partitions--just the partition that contains the data that needs to be queried.

One of the main purposes of partitioning is to divide the data into different tables in a coarser granularity, so that the relevant data can be put together, and it will be convenient to delete the entire partition data at once.

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

1. The table is so large that it cannot be all in memory, or only the last part of the table has hot data, and the others are historical data.

2. Data for partitioned tables is easier to maintain. For example, to bulk delete large amounts of data can be used to clear the entire partition way. In addition, a separate partition can be optimized, inspected, repaired, and so on.

3. Partitioned table data can be distributed across different physical devices, enabling efficient use of multiple hardware devices.

4. You can use partitioned tables to avoid certain special bottlenecks, such as mutually exclusive access to a single index of InnoDB, ext3 file system Inode lock contention, and so on.

5. If required, you can also back up and restore separate partitions, which works well in very large datasets.

The partition table itself has some limitations, and here are some of the more important:

1. A table can have a maximum of 1024 partitions.

2. In mysql5.1, the partition expression must be an integer, or an expression that returns an integer. In mysql5.5, you can use columns to partition directly in some scenarios.

3. If the partition field has a primary key or a unique indexed column, all primary key columns and unique index columns must be included.

4. Foreign KEY constraints cannot be used in partitioned tables.

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

Select query

When querying a partitioned table, the partition layer opens and locks all the underlying tables first, and the optimizer first 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 receives the record, and then writes the record to the 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 record is updated, the partition layer opens and locks all the underlying tables first, MySQL first determines which partition the record needs to be updated, then takes out the data and updates, then determines which partition the updated data is in, writes the underlying data, and deletes the underlying table of the original.

Although each operation has "open and lock all underlying tables first", this does not mean that the partitioned table is locked in the full table during processing. If the storage engine can implement its own row-level locks, such as InnoDB, the corresponding table locks are released at the partition layer. This lock and unlock process is similar to a query on an ordinary InnoDB.

MySQL partition table