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

Mysql partition table

The mysql partition table is a separate logical table, but the underlying layer consists of multiple physical word tables.

Requests for partitions are converted into calls to the storage engine interface through the handle object. So the partition is for

For the sql layer, it is a black box that completely encapsulates the underlying implementation and is transparent to the application.

The way Mysql implements partitioned tables-- the encapsulation of the underlying tables-- means that indexes are also defined by partitioned word tables, rather than global indexes.

Suitable for the scene:

Only the last part of the data in the table is hot data, and the rest is temporary data.

The data in the partition table is easier to maintain, and independent partitions can be optimized, checked, repaired, etc.

Partition table data can be distributed across different physical devices, making efficient use of multiple hardware devices

Independent partitions can be backed up and restored, which works very well in scenarios with very large datasets.

Partition restrictions:

A partition table can only have 1024 partitions

If there is a primary key or unique index column in the partition field, then all primary key columns and unique index columns must be included.

Use partitioned tables to avoid some special bottlenecks, such as mutually exclusive access to ext3 file system inode lock contention of a single index of Innodb.

Foreign key constraints cannot be used for partitioned tables.

The principle of partitioned tables

From the perspective of the storage engine, the underlying table is no different from a normal table, and the storage engine does not need to know whether it is a normal table or part of a partitioned table.

Select query

Type of partition table

Mysql supports multiple partition tables. A partition expression can be a column or an expression that contains a column. For example, the following table can store each year's sales in a different partition.

Create table sales (

Order_date datetime not null

-- Other columns

) engine=InnoDB partition by range (Year (order_date)) (

Partition packs 2010 values less then (2010)

Partition packs 2011 values less then (2011)

Partition p_catchall values less then maxvalue)

Mysql also supports keys, hashes and list partitions.

The system can reduce the competition for mutually exclusive access of the index through sub-partitions. In the last year, partitioned data will be accessed very frequently, which will lead to a lot of competition for mutexes. You can use a hash subpartition

The data is cut into multiple small pieces, which greatly reduces the competition problem of mutexes.

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.