Advantages of MySQL database partitioning

We all know that MySQL database partitioning is a physical database-related design technology. DBA and MySQL database personnel are quite familiar with it. Although there are many partition technologies, the main purpose is to reduce the total amount of data read/write in a specific SQL operation to reduce the response time. There are two main partitions:

We all know that MySQL database partitioning is a physical database-related design technology. DBA and MySQL database personnel are quite familiar with it. Although there are many partition technologies, the main purpose is to reduce the total amount of data read/write in a specific SQL operation to reduce the response time. There are two main partitions:

We all know that MySQL database partitioning is a physical database-related design technology. DBA and MySQL database personnel are quite familiar with it. Although there are many partition technologies, the main purpose is to reduce the total amount of data read/write in a specific SQL operation to reduce the response time.

There are two main types of partitions:

Pay attention to the concept of rows and columns (row and column)

1. horizontal Partitioning (Horizontal Partitioning) is used to partition the MySQL database for the rows of the table. In this way, datasets separated by physical columns in different groups can be combined, in this way, individual partitions (single partitions) or collective partitions (one or more partitions) are separated ). All columns defined in the table can be found in each dataset, so the table features are maintained.

For example, a table containing ten-year invoice records can be partitioned into ten different partitions, each of which contains one-year records. (Peng Yi Note: here we will talk about the specific partitioning method. You can say that you must use an attribute column to separate it. For example, the column used here is the year)

2. vertical Partitioning generally reduces the width of the target table by Vertical Partitioning of the table, so that certain columns are divided into specific partitions, each partition contains the rows corresponding to the columns.

For example, a table that contains large text and BLOB columns is not frequently accessed, at this time, we need to divide these infrequently used text and BLOB into another partition, which can improve the access speed while ensuring their data relevance.

When database providers start to create MySQL database partitions (mainly horizontal partitions) in their database engines, DBAs and modelers must design the physical partition structure of the table, do not store redundant data (different tables also contain data in the parent table) or join each other into a logical parent object (usually a view ). This will invalidate most of the functions of the horizontal partition, and sometimes affect the vertical partition.

Partition has the following advantages:

You can store more data than a single disk or file system partition.

For data that has lost its meaning, you can easily Delete the data by deleting the partitions related to the data. On the contrary, in some cases, the process of adding new data can be easily implemented by adding a new partition for those new data.

Other advantages related to partitions are listed below. These features are not yet implemented in MySQL database partitions, but they have a high priority in our priority list. We hope to include these features in the 5.1 production version.

Some queries can be greatly optimized. This is mainly because data that satisfies a given WHERE statement can be saved in only one or more partitions, in this way, you do not need to find other remaining partitions. Because partitions can be modified after the partition table is created, data can be re-organized when the partition scheme is not configured for the first time to improve the efficiency of common queries.

The query involving Aggregate functions such as SUM () and COUNT () can be easily processed in parallel. A simple example of this query is "SELECT salesperson_id, COUNT (orders) as order_total FROM sales group by salesperson_id ;". Through "Parallel", this means that the query can be performed on each MySQL database partition at the same time. The final result is only the result obtained through the total of all partitions.

Data Query is distributed across multiple disks to increase the query throughput.