Improve MySQL performance through partitioning (Partition)

The reason why I want to write an article today is that MySQL5.1 's release brings a powerful weapon to design a super power database, and any MySQL DBA should learn and use it as soon as possible. I think that if you can use the new features of this version 5.1, DBAs can manage the VLDB (do not know what is VLDB?). To tell you, it's a good big database meaning, Very Large DB, or a data warehouse that magically gets a huge performance boost.

What is a database partition?

A database partition is a physical database design technique that is fairly familiar to DBAs and database modelers. Although partitioning technology can achieve many effects, its primary purpose is to reduce the amount of data read and write in a particular SQL operation to decrease response time.

There are two main forms of partitioning://Here you must pay attention to the concept of rows and columns (row is row, column is columns)

Horizontal Partitioning (horizontal partitioning)

This form of partitioning is the partitioning of the rows of the table, in such a way that the data sets divided by the physical columns within the groupings are grouped together for individual division (single partition) or group partitioning (one or more partitions). All the columns defined in the table are found in each dataset, so the table's attributes remain.

As A simple example: a table containing ten-year invoice records can be partitioned into 10 different partitions, each containing a record of one year. (Friends of the game note: Here the specific use of the partition method we say later, you can say a little, you must pass a property column to split, such as the use of the column here is the year)

Vertical partitions (Vertical partitioning)

This partitioning method typically reduces the width of the target table by dividing the table vertically, so that certain columns are divided into specific partitions, each containing the rows of the columns in which they correspond.

For A simple example: a table containing large text and BLOB columns, which are not frequently accessed, divides these infrequently used text and blobs into another partition, which increases the speed of access while ensuring their data dependencies.

when the database vendors begin to create partitions in their database engine (primarily horizontal partitions), DBAs and modelers must design the physical partitioning structure of the table, not to save redundant data (both of which contain data from the parent table) or to connect to each other as a logical parent object (usually a view). This practice invalidates most of the functionality of the horizontal partition, and sometimes affects the vertical partition.

partitioning in MySQL 5.1

The most exciting new feature in MySQL5.1 should be support for horizontal partitioning. This is really good news for users of MySQL, and she has supported most of the partitioning mode:

Range (range) –

This pattern allows DBAs to divide data into different scopes. For example, a DBA can divide a table into three partitions by year, data from the 80 's (1980 ' s), data from the 90 's (1990 's), and any data after 2000 (including 2000).

Hash (hash) –

This mode allows the DBA to compute the hash key of one or more columns of the table, and finally partition the data region corresponding to the hash code's different values. For example, a DBA can establish a table that partitions the primary key of a table.

Key (Value) –

An extension of the hash pattern above, where the hash key is generated by the MySQL system.

List (predefined list) –

This pattern allows the system to split the row data corresponding to the value of the list defined by the DBA. For example, the DBA established a table across three partitions, based on the data for the 2004 2005 and 2006 values respectively.

Composite (composite mode)-

Very mysterious bar, haha, in fact, the combination of the above mode of use, it is not explained. For example: on a table that initializes a range-wide partition, we can hash the partition again.

　　

Total 4 page: prev 1 [2] [3] [4] Next page