Shulou(Shulou.com)05/31 Report--

This article focuses on "what are the knowledge points of the partition table for MySQL performance tuning". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "what are the knowledge points of the partition table of MySQL performance tuning"?

For users, the partitioned table is a separate logical table, but the underlying layer is made up of multiple physical subtables. For the user, the partitioned table is a black box that completely encapsulates the underlying implementation, and it is transparent to the user. You can see multiple table files named with # delimited in the file system.

Mysql uses the partition by clause to define the data stored in each partition when creating the table, and when executing the query, the optimizer filters those partitions that do not have the data we need, so that the query does not have to scan all partitions.

The main purpose of partitioning is to secure the data with a thicker strength in different tables so that the relevant data can be stored together.

Next, I will talk about the partitioned table from the following six aspects: the application scenario of the partitioned table, the limitation of the partitioned table, the principle of the partitioned table, the type of the partitioned table, how to use the partitioned table, and the problems that should be paid attention to when using the partitioned table.

First, the application scenario of partition table

1. The table is too large to be stored in memory, or there is hot data only in the last part of the table, and the rest is historical data.

2. The data of the partition table is easier to maintain.

(1) batch deletion of a large amount of data can be done by clearing the entire partition.

(2) optimize, check and repair an independent partition

3. The data of the partition table can be distributed on different physical devices, thus making efficient use of multiple hardware devices.

4. Partition tables can be used to avoid some special bottlenecks

(1) Mutual exclusive access to a single index of innodb

(2) inode lock competition of ext3 file system

5. Independent partitions can be backed up and restored

II. Restrictions on partitioned tables

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

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

3. 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.

4. Foreign key constraints cannot be used in partition tables

Third, the principle of partition table

The partition table is implemented by multiple related underlying tables, which are also identified by handle objects, and we can access each partition directly. The storage engine manages the underlying tables of the partition the same as the ordinary tables (all underlying tables must use the same storage engine), and the index knowledge of the partitioned tables adds exactly the same index to each underlying table. From the perspective of the storage engine, the underlying table is no different from the normal table, and the storage engine does not need to know whether it is a normal table or part of a partitioned table. The operation of the partitioned table follows the following operation logic:

1. Select query

When querying a partition table, the partition layer first opens and locks all the underlying tables. 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 a record, the partition layer first opens and locks all the 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 the underlying tables, then determines the partition corresponding to the data, and finally deletes the corresponding underlying tables.

4. Update operation

When updating a record, the partition layer first opens and locks all the underlying tables. Mysql first determines which partition of the record that needs to be updated, then takes out the data and updates it, and then determines which partition the updated data should be. Finally, it writes to the underlying table and deletes 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 matches the partition expression, it can filter out all partitions that do not contain the record, which is also valid for update. If it is an insert operation, only one partition is hit, and all other partitions are filtered out. Mysql first determines which partition the record belongs to, and then writes the record to the corresponding partition table without having to operate on any other partition.

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

At this point, I believe you have a deeper understanding of "what are the knowledge points of the MySQL performance-tuned partition table?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.