Study on the theory of _mysql division in Xiao Bei

MySQL partition Brief:first, why MySQL partition is requiredii. types of MySQL partitionsthird, MySQL partition technology comparison

first, why MySQL partition is required

1, first, the business data increases, will inevitably affect the data operation. Usually for big data, it is like the following optimization method:

A, large table to remove the small table

b, SQL statement optimization

2. Insufficient:

A, SQL statement optimization can be adjusted by increasing the index, but as the amount of data increases, the maintenance cost of the index will increase

b, large table split small table can be subdivided into vertical or horizontal sub-table. However, you need to implement logical operations. That's the choice of the watch.

3, Summary: For big data, adding indexes will cause the index file large and maintain the index cost, split the table, you need to implement logical operation. Therefore, the MySQL partition needs to be used here. Partitioning is essentially the practice of splitting small tables. is to split into small tables on a physical level, without the need for logical operations, which is transparent to the user.

second, MySQL partition type

1. Range Partition: Assign multiple rows to a partition based on the column values for a given continuous interval

2. List partition: Similar to by range partition, the difference is that the list partition is selected based on a value in a set of discrete values matching a column value

3. Hash partition: A partition that is selected based on the return value of a user-defined expression using the column values of those rows that will be inserted into the table, which can contain any expression that is valid in MySQL and produces a nonnegative integer

4, Key partition: Similar to the hash partition, the difference is that the key partition only supports the calculation of one or more columns, and the MySQL server provides its own hash function

Summary: Using priority Range>list>hash>key

Hash is typically used to test partitions

third, MySQL partition technology comparison

Partition name	Advantages	Disadvantages	Common
Range	Fits with date type, supports composite partitioning	Limited partitioning	Typically only for a single column
List	Suitable for columns with fixed values, support for composite partitioning	Limited partition, insert record in this column value is not in list, data loss	Typically only for a single column
Hash	Linear hashing makes it faster to add, delete, and merge partitions	The data distribution of the linear hash is uneven, while the general hash data is evenly distributed.	Typically only for a single column
Key	Columns can be other non-int types such as strings	Efficiency is lower than the previous, because functions are complex functions such as MD5 or SHA functions	Typically only for a single column

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Study on the theory of _mysql division in Xiao Bei