Beckham _ mysql partition theory learning, Beckham _ mysql theory learning

Beckham _ mysql partition theory learning, Beckham _ mysql theory learning
Mysql PartitionBrief:I. Why mysql partition II? mysql partition type III. mysql partition technology comparison

 

I. Why mysql partitioning?

1. When business data increases, data operations will inevitably be affected. There are the following Optimization Methods for big data:

A. Split large tables and small tables

B. SQL statement Optimization

2. Deficiency:

A. You can adjust the SQL statement optimization by adding indexes. However, as the data volume increases, the index maintenance cost increases.

B. Large table sharding small tables can be subdivided into vertical or horizontal table sharding. However, logical operations are required. Select the table

3. Conclusion: for big data, adding an index will result in a large index file and a high index maintenance cost. to split a table, logical operations are required. Therefore, mysql partitions are used here. In fact, partitioning also achieves small table splitting in essence. It is split into small tables based on the physical layer and does not require logical operations. It is transparent to users.

 

Ii. mysql partition type

1. range partition: allocate multiple rows to the partition based on the column values in the given continuous interval.

2. list partitioning: similar to partitioning by range. The difference is that list partitioning is based on column values matching a value in a discrete value set.

3. hash partition: select a partition based on the return value of the User-Defined expression. This expression uses the column values of the rows to be inserted into the table for calculation, this function can contain valid mysql and generate non-negative integers which are worthy of any expression.

4. key partition: similar to hash partition, the difference is that key partition only supports computing one or more columns, and mysql server provides its own hash function.

Conclusion: Use priority range> list> hash> key

Hash is generally used to test partitions.

 

Iii. mysql partition technology comparison

Partition name	Advantages	Disadvantages	Commonalities
Range	Suitable for date type, supports composite Partition	Limited partitions	Generally, only one column is specified.
List	Suitable for columns with fixed values and supporting composite partitions	If the value of the inserted record in this column is not in the list, the data is lost.	Generally, only one column is specified.
Hash	Linear hash makes adding, deleting, and merging partitions faster.	The linear hash data distribution is uneven, while the hash data distribution is average.	Generally, only one column is specified.
Key	The column can be a string or other non-int type.	Efficiency is lower than before, because the function is a complex function such as md5 or sha Function	Generally, only one column is specified.

 

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.