The principles and pros and cons of MySQL partitioned tables

Partitioned tables are implemented by a number of related underlying tables, which are also represented by handle objects, so we can also access individual partitions directly, and the storage engine manages the various underlying tables of the partition and manages the normal tables (all underlying tables must use the same storage engine). The index of a partitioned table simply adds an identical index to each underlying table, and from the storage engine's point of view, the underlying table is no different from a normal table, and the storage engine does not need to know whether this is a normal table or part of a partitioned table.

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

Select query:

When querying a partitioned table, the partition layer first opens and locks all the underlying tables, and the optimizer 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 accepts the record, and then writes the record to the corresponding 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 data is updated, the partition layer opens and locks all the underlying tables first,MySQL determines which partition the record needs to be updated, then extracts the data and updates it, then determines which partition the updated data should be placed on, then writes to the underlying table and deletes the underlying table where the original data resides.

Although each operation will open and lock all the underlying tables, this is not to say that the partition table is locked in the full table during processing, if the storage engine can implement its own row-level locks, such as:InnoDB, the partition layer will release the corresponding table locks, this lock and unlock process with the ordinary Similar to the query on InnoDB.

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

A: The table is so big that it can't all be in memory, or only the last part of the table has hot data, and all the rest is historical data

B: Partition table data is easier to maintain, such as: to bulk delete large amounts of data can use the way to clear the entire partition. In addition, you can optimize, check, repair, and so on for a separate partition

C: Data from partitioned tables can be distributed across different physical devices, enabling efficient use of multiple hardware devices

D: You can use partitioned tables to avoid specific bottlenecks such asmutually exclusive access to a single index of InnoDB,ext3 file system inode lock contention, etc.

E: You can also back up and restore separate partitions if needed, which works very well in very large data set scenarios

F: Optimization query, when the WHERE clause contains partition column, you can use only the necessary partitions to improve query efficiency, while the sum () and count () such as the aggregate function of the query, can be processed in parallel on each partition, Ultimately, you only need to summarize the results from all partitions.

3. The partitioning itself has some limitations:

A: A table can have a maximum of 1024 partitions ( 8,192 partitions 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 be partitioned directly using string columns and date type columns (when using a varchar string type column, is usually the date of the string as a partition).

C: If there is a primary key or a unique index column in the Partition field, all primary key columns and unique index columns must be included, and if the table has a primary key or unique index, the partition key must be a primary key or a unique index

D: FOREIGN KEY constraints cannot be used in partitioned tables

The E:mysql database supports a horizontal partition and does not support vertical partitioning, so the index in the partition of the MySQL database is a local partition index, where both the data is stored and the index is stored, and the global partition refers to the database that is placed in each partition. But the index of all the data is placed in another object

F: Currently MySQL does not support partitioning of spatial types and temporary table types. Full-text indexing is not supported

4. The establishment of sub-partitions requires attention to the following issues:

A: The number of sub-partitions must be the same

B: As long as you use subpartition on any partition of a partitioned table to explicitly define any sub-partitions, you must define the sub-partitions on all partitions, and you cannot omit some partitions from being sub-partitioned.

C: Each subpartition clause must include a name for the sub-partition

D: The name of the sub-partition must be unique and cannot have sub-partitions with duplicate names in a single 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 is always the first in ascending order, so for different partition types, The MySQL database also handles null differently. For a range partition, if NULL is inserted into the partition column, the MySQL database puts the value in the leftmost partition, noting that if the partition is deleted, all content under the partition is deleted from the disk, the null partition is removed, and the null value is then deleted. To use null under the list partition, you must explicitly define the hash value of the partition, or you will get an error when inserting null. Hash and key partitioning do not treat NULL as a range,list partition, and any partition function returns null as 0.

The principles and pros and cons of MySQL partitioned tables