Shulou(Shulou.com)05/31 Report--

This article is to share with you about the table partition types in MySQL, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

What is a table partition

Generally speaking, table partitioning is to divide a large table into several small tables according to the conditions. Mysql5.1 has begun to support data table partitioning. For example, if a user table has more than 6 million records, the table can be partitioned according to the date of entry, or the table can be partitioned according to the location. Of course, it can also be divided according to other conditions.

Second, why partition the table

In order to improve the scalability, manageability and database efficiency of large tables and tables with various access modes.

Some of the advantages of partitioning include:

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

For data that has lost its preservation meaning, it is usually easy to delete that data by deleting the partitions associated with that data. On the contrary, in some cases, the process of adding new data can be easily achieved by adding a new partition to those new data. Other benefits typically associated with partitioning include the ones listed below. These features in the MySQL partition have not been implemented yet, but they are high on our priority list; we hope to include them in the production version of 5.1.

Some queries can be greatly optimized, mainly because the data that satisfies a given WHERE statement can be saved in only one or more partitions, so that you don't have to look for other partitions when looking. Because partitions can be modified after the partition table is created, you can reorganize the data to improve the efficiency of commonly used queries when you first configure the partitioning scheme.

Queries involving aggregate functions such as SUM () and COUNT () can be easily processed in parallel. A simple example of such a 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 partition at the same time, and the final result only needs to be obtained by the total results of all partitions.

Greater query throughput is achieved by dispersing data queries across multiple disks.

III. Type of zoning

RANGE partition: assigns multiple rows to a partition based on column values that belong to a given contiguous interval.

LIST partitions: similar to partitioning by RANGE, except that LIST partitions are selected based on column values matching a value in a set of discrete values.

HASH partition: a partition that is selected based on the return value of a user-defined expression that uses the column values of the rows to be inserted into the table. This function can contain any valid expression in MySQL that produces a non-negative integer value.

KEY partitions: similar to partitioning by HASH, except that KEY partitions only support computing one or more columns, and the MySQL server provides its own hash function. One or more columns must contain integer values.

RANGE partition

Assign multiple rows to a partition based on column values that belong to a given contiguous interval.

These intervals should be contiguous and cannot overlap each other and are defined using the VALUES LESS THAN operator. Here is an example.

Sql Code:

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR (30), lname VARCHAR (30), hired DATE NOT NULL DEFAULT '1970-01-01, separated DATE NOT NULL DEFAULT' 9999-12-31, job_code INT NOT NULL, store_id INT NOT NULL) partition BY RANGE (store_id) (partition p0 VALUES LESS THAN (6), partition p1 VALUES LESS THAN (11), partition p2 VALUES LESS THAN (16), partition p3 VALUES LESS THAN (21))

According to this partitioning scheme, all rows corresponding to employees working in stores 1 to 5 are saved in partition P0, employees in stores 6 to 10 are saved in P1, and so on. Note that each partition is defined sequentially, from the lowest to the highest. This is a requirement of PARTITION BY RANGE syntax; at this point, it is similar to the "switch... case" statement in C or Java. For a new row that contains data (72, 'Michael',' Widenius', '1998-06-25, NULL, 13), it's easy to determine that it will be inserted into the p2 partition, but what happens if a store number 21 is added? In this scenario, since there are no rules to include stores with store_id greater than 20, the server will not know where to save the row, which will result in an error. To avoid this error, you can use a "catchall" VALUES LESS THAN clause in the CREATE TABLE statement, which provides all values that are greater than the highest value explicitly specified:

Sql Code:

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR (30), lname VARCHAR (30), hired DATE NOT NULL DEFAULT '1970-01-01, separated DATE NOT NULL DEFAULT' 9999-12-31, job_code INT NOT NULL, store_id INT NOT NULL) PARTITION BY RANGE (store_id) (PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN MAXVALUE)

MAXVALUE represents the largest possible integer value. Now, all rows with store_id column values greater than or equal to 16 (the highest value defined) will be saved in partition p3. At some point in the future, when the number of stores has grown to 25, 30, or more, you can use the ALTER TABLE statement to add new partitions for stores 21-25, 26-30, and so on. In almost the same structure, you can also split the table based on the employee's work code, that is, a contiguous interval based on job_code column values. For example-suppose a 2-digit work code is used to represent an ordinary (in-store) worker, three numeric codes represent office and support staff, and four numeric codes represent management, you can create the partition table using the following statement:

Sql Code:

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR (30), lname VARCHAR (30), hired DATE NOT NULL DEFAULT '1970-01-01, separated DATE NOT NULL DEFAULT' 9999-12-31, job_code INT NOT NULL, store_id INT NOT NULL) PARTITION BY RANGE (job_code) (PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN (10000))

In this example, all lines related to the workers in the store will be saved in partition p0, all lines related to the office and support staff in partition p1, and all rows related to management in partition p2. It is also possible to use an expression in the VALUES LESS THAN clause. The most noteworthy limitation here is that MySQL must be able to calculate the return value of the expression as LESS THAN (= num: set V = CEIL (V / 2) set N = N & (V-1) for example, suppose table T1, which uses a linear hash partition and has four partitions, is created with the following statement: CREATE TABLE T1 (col1 INT, col2 CHAR (5), col3 DATE) PARTITION BY LINEAR HASH (YEAR (col3)) PARTITIONS 6 Now suppose you want to insert two rows of records into table T1, one of which has a col3 column value of '2003-04-14' and the other record has a col3 column value of '1998-10-19'. The partition to which the first record will be saved is determined as follows: v = POWER (2 CEILING (LOG (2p7)) = 8 N = YEAR ('2003-04-14') & (8-1) = 2003 & 7 = 3 (3 > = 6 is false (FALSE): the record will be saved to partition # 3) the partition number to which the second record will be saved is calculated as follows: v = 8 N = YEAR ('1998-10-19') & (8-1) = 1998 & 7 = 6 (6 > = 4 is true (TRUE) Additional steps are required) N = 6 & CEILING (5 / 2) = 6 & 3 = 2 (2 > = 4 is false (FALSE): the record will be saved to the # 2 partition) according to the advantage of linear hash partition, Deleting, merging, and splitting partitions will become faster It is helpful to deal with tables that contain an extremely large amount of (1000 gigabytes) of data. Its disadvantage is that compared with the data distribution obtained by using conventional HASH partitions, the distribution of data in each interval is unlikely to be balanced.

KSY partition

Similar to partitioning by HASH, the difference is that KEY partitions only support calculating one or more columns, and the MySQL server provides its own hash function. One or more columns must contain integer values.

Sql Code:

CREATE TABLE tk (col1 INT NOT NULL, col2 CHAR (5), col3 DATE) PARTITION BY LINEAR KEY (col1) PARTITIONS 3

The use of the keyword LINEAR in the KEY partition has the same effect as in the HASH partition, where the partition number is obtained by the power of 2 (powers-of-two) algorithm, not by the modular algorithm.

These are the table partition types in MySQL, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.