MySQL performance tuning partition table (summary sharing)

This article brings you relevant knowledge about mysql, which mainly introduces related issues about performance tuning, mainly introduces the relevant content of partition tables. For users, partitioning The table is an independent logical table, but the bottom layer is composed of multiple physical sub-tables. Let's take a look at it together. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

For users, the partition table is an independent logical table, but the bottom layer is Composed of multiple physical sub-tables. The partition table is a black box that completely encapsulates the underlying implementation and is transparent to the user. Multiple table files named using # to separate them can be seen from the file system.
Mysql uses the partition by clause to define the data stored in each partition when creating a table. When executing a query, the optimizer will filter those partitions that do not have the data we need based on the partition definition, so that the query does not need to scan all partitions.
The main purpose of partitioning is to divide the data into different tables, so that related data can be stored together.
Next, I will talk about the partition table from the following six aspects, namely the application scenarios of the partition table, the limitations of the partition table, the principle of the partition table, the types of the partition table, how to use the partition table, and how to use the partition table. Things to pay attention to when making a table.

1. Application scenarios of partition tables

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

2. Partitioned table data is easier to maintain

(1) To delete a large amount of data in batches, you can use the method of clearing the entire partition

(2) Optimize an independent partition , check, repair and other operations

3. The data of the partition table can be distributed on different physical devices, thereby efficiently utilizing multiple hardware devices

4. The partition table can be used to avoid certain Some special bottlenecks

(1) Mutually exclusive access of a single index of innodb

(2) Inode lock competition of ext3 file system

5. Can be backed up and restored independently Partition

2. Limitations of partition table

1. A table can only have a maximum of 1024 partitions. In version 5.7, it can support 8196 partitions

2. In the early MySQL, the partition expression must be an integer or an expression that returns an integer. In MySQL 5.5, columns can be used directly for partitioning in some scenarios.

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

4. Partitioned tables cannot use foreign key constraints

3. Principle of partitioned tables

Partitioned tables are implemented by multiple related underlying tables. This underlying table is also identified by a handle object, and we can directly access each partition. The storage engine manages each underlying table of the partition in the same way as it manages an ordinary table (all underlying tables must use the same storage engine). The index knowledge of the partition table adds an identical index to each underlying table. From the perspective of the storage engine, the underlying table is no different from an ordinary table, and the storage engine does not need to know whether it is an ordinary table or part of a partitioned table. The operation of the partition table is carried out according to the following operation logic:

1. Select query

When querying a partition table, the partition layer first opens and locks all For the underlying table, the optimizer first determines whether some partitions can be filtered, and then calls the corresponding storage engine interface to access the data of each partition

2. Insert operation

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

3. Delete operation

When deleting a record, the partition layer first opens and locks all underlying tables, then determines the partition corresponding to the data, and finally performs the corresponding Delete the underlying table.

4. Update operation

When updating a record, the partition layer first opens and locks all underlying tables, and mysql first determines which partition the record needs to be updated. , then take out the data and update it, then determine which partition the updated data should be in, and finally write to the underlying table and delete the underlying table where the source data is located.

Some operations support filtering. For example, when deleting a record, MySQL needs to find the record first. If the where condition happens to match the partition expression, all partitions that do not contain this record can be All are filtered out, which is also effective for update. If it is an insert operation, it will only hit one partition, and other partitions will be filtered out. MySQL first determines which partition this record belongs to, and then writes the record to the corresponding partition table without operating on any other partitions.

Although each operation will "first open and lock all underlying tables", this does not mean that the partition table locks the entire table during processing. If the storage engine can implement row-level locks by itself, such as innodb , the corresponding table lock will be released at the partition level.

Recommended learning: mysql video tutorial

The above is the detailed content of MySQL performance tuning partition table (summary sharing). For more information, please follow other related articles on the PHP Chinese website!