MySQL Partition performance Awareness

One, the concept of partitioning

Partitioning allows multiple parts of a single table to be allocated across the file system according to the specified rules. Different parts of the table are stored as separate tables in different locations.

Differences between partitions and sub-tables

Partition:1> A data table 2> no risk of data duplication 3> write a table 4 > constraint restrictions imposed

Table:1> Multiple data Tables,2> risk of duplicate data 3> write multiple Tables 4 > No agreed restrictions

MySQL supports the Range,list,hash,key partition type, which is most commonly used with range;

Range-This mode allows data to be divided into different ranges. For example, you can divide a table into several partitions by year.

Hash--This mode allows the calculation of the hash key of one or more columns of a table, and finally partitions the data region of the hash code with different values. For example, you can create a table that partitions the primary key of a table.

Key (value)--an extension of the above hash mode, where the hash key is generated by the MySQL system.

List (pre-defined list)-this mode allows the system to split the data through predefined list values.

Composite composite mode)--Use the combination of the above modes

Second, what can the division do

1, logical data segmentation

2, improve the single write and read application speed

3, improve the speed of the partition range of query

4, split data can have multiple different physical file paths

5. Save historical data efficiently

6, constraint checking on a table

7, different master-slave server Analysis policy, such as Master by hash partition, salve by range partition

Third, zoning restrictions

1, you can only partition the integer column of the data table, or the data column can be converted into an integer column by partitioning function

2, the maximum number of partitions cannot exceed 1024

3, if there is a unique index or primary key, the partition column must be included in all unique indexes or primary keys

4, foreign key not supported

5, full-text indexing not supported (fulltext)

6, partitioning by date is ideal, because many date functions are available. But not too many partitioning functions are appropriate for a string

Iv. when to use partitions

1, massive data sheet

2, History table quick Query, can adopt archive+partition way.

3, the data table index is larger than the server's valid memory

4, for large tables, especially if the index is much larger than the server valid memory, you can not use the index, the partition efficiency will be more efficient

MySQL Partition performance Awareness