Advantages of MySQL database partitioning

Source: Internet
Author: User

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:

The concept of rows and columns is "row" and "column)

1. horizontal partition Horizontal Partitioning) this form of Partitioning is to partition the table's rows in the MySQL database, through this way the physical columns in different groups can be combined to separate individual partitions in a single partition) or group one or more partitions ). 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 first talk about it. You must use an attribute column to separate it. For example, the column used here is the year)

2. vertical partition Vertical Partitioning) This partition method is generally used to reduce the width of the target table by Vertical partition of the table, so that some specific 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 in their database engines), DBAs and modelers must design the physical partition structure of the table, do not save redundant data. Different tables contain data in the parent table at the same time) or join each other into a logical parent object, which is 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.