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

This article mainly introduces "the partition principle of MySQL partition table and its advantages and disadvantages". In daily operation, I believe that many people have doubts about the partition principle and advantages and disadvantages of MySQL partition table. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "partition principle and advantages and disadvantages of MySQL partition table". Next, please follow the editor to study!

1. The principle of partitioned tables

Partitioned tables are implemented by multiple related underlying tables, which are also represented by handle objects, so we can also access each partition directly. Each underlying table managed by the storage engine is the same as managing ordinary tables (all underlying tables must use the same storage engine). The index of the partitioned table only adds the same index to each underlying table, from the point of view of the storage engine. The underlying table is no different from a regular table, and the storage engine does not need to know whether it is a normal table or part of a partitioned table.

The operation on the partition table follows the following operation logic:

Select query:

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

Insert operation:

When writing a record, 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 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.

Update operation:

When updating a piece of data, the partition layer first opens and locks all the underlying tables. Mysql first determines the partition in which the records need to be updated, then takes out the data and updates it, then determines which partition the updated data should be placed in, then writes to the underlying table and deletes the underlying table where the original data is located.

Although each operation will open and lock all the underlying tables, this does not mean that the partitioned table locks the whole table during processing. If the storage engine can implement row-level locking on its own, such as innodb, it will release the corresponding table lock in the partition layer. This locking and unlocking process is similar to the query on ordinary Innodb.

two。 Partitioning can play a very important role in the following scenarios:

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

B: the data of the partition table is easier to maintain. For example, if you want to delete a large amount of data in bulk, you can use the method of clearing the entire partition. In addition, a separate partition can be optimized, checked, repaired, etc.

C: the data of the partition table can be distributed across different physical devices, making efficient use of multiple hardware devices

D: partitioned tables can be used to avoid some special bottlenecks, such as mutually exclusive access to a single index of innodb, inode lock contention of ext3 file systems, etc.

E: if necessary, you can also back up and restore separate partitions, which works very well in scenarios with very large datasets

F: optimize the query so that when the partition column is included in the where sentence, only the necessary partitions can be used to improve query efficiency, and when queries involving aggregate functions such as sum () and count () are involved, they can be processed in parallel on each partition, and finally only need to summarize the results of all partitions.

3. The partition itself has some limitations:

A: a table can only have a maximum of 1024 partitions (8192 partitions are supported 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 directly use string columns and date type columns for partitioning (when using varchar string type columns, the date of the string is usually used as the partition).

C: 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, and if there is a primary key or unique index in the table, then the partition key must be a primary key or unique index

D: foreign key constraints cannot be used in partitioned tables

The partition type supported by E:mysql database is horizontal partition, but not vertical partition. Therefore, the index in the partition of mysql database is local partition index, in which both data and index are stored in one partition, while global partition means that the database is placed in each partition, but the index of all data is placed in another object.

F: currently, mysql does not support partitioning between space types and temporary table types. Full-text indexing is not supported

4. The establishment of subpartitions needs to pay attention to the following issues:

A: the number of subpartitions must be the same

B: as long as you use subpartition on any partition of a partition table to explicitly define any subpartitions, you must define subpartitions on all partitions, and you must not miss some partitions that do not advance subpartitions.

C: each subpartition clause must include a name of the subpartition

D: the name of the subpartition must be unique, and the subpartition with the same name cannot appear in a table.

The partition of the E:mysql database always treats null as a smaller value than any non-null, which is the same as the order by operation that handles null values in the database, and null always comes first when sorting in ascending order, so the mysql database handles null differently for different partition types. For range partitions, if null is inserted into the partition column, the mysql database places the value in the leftmost partition. Note that if you delete the partition, everything under the partition is deleted from disk, the partition where null is located is deleted, and the null value is deleted. To use null under a list partition, it must be explicitly defined in the partition's hash value, otherwise an error will be reported when inserting the null. Hash and key partitions do not handle null in the same way as range,list partitions. Any partition function returns null as 0. 0.

At this point, the study on "the partitioning principle of MySQL partition table and its advantages and disadvantages" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.