MySQL's Partitioning technology

I. Overview

When the total number of MySQL records exceeds 1 million, will there be a significant decrease in performance? The answer is yes, but the rate of performance degradation > varies, depends on the system architecture, applications, and > including indexes, server hardware and many other factors. When a netizen asked me this question, my most common answer > is: Sub-table, can be based on the ID range or time sequence and other rules to divide the table. The table is easy to do, but the resulting application and even architectural changes are not > underestimated, including future extensibility.

Previously, a solution was to use the MERGE
Type, which is a very convenient cooking. Architecture and procedures are largely non-change, but their drawbacks are obvious:

• Can only be used on MyISAM tables of the same structure
• Unable to enjoy full functionality of MyISAM, such as unable to perform Fulltext search on the MERGE type
• It needs to use more file descriptors
• Slower reading index

This time, the advantages of the new partition (Partition) feature in MySQL 5.1 are obvious:

• More data can be stored than a single disk or file system partition
• It's easy to delete unused or obsolete data.
• Some queries can be greatly optimized.
• When it comes to aggregate functions such as SUM ()/count (), you can do it in parallel
• Greater IO Throughput

Partitioning allows rules to be set to any size, assigning multiple parts of a single table across file systems. In fact, different parts of the table are stored as separate tables in different locations.

Partitioning should be aware of:

1. When partitioning, either do not define the primary key or add the partition field to the primary key.

2, the partition field cannot be null, otherwise how to determine the partition range, so try not null

Ii. Types of partitions

• RANGE partition: Assigns multiple rows to a partition based on column values that belong to a given contiguous interval.
• List partitioning: Similar to by range partitioning, the difference is that a list partition is selected based on a value in a set of discrete values that match a column value.
• Hash partition: A partition that is selected based on the return value of a user-defined expression that is evaluated using the column values of those rows that will be inserted into the table. This function can package > contain any expression that is valid in MySQL that produces a non-negative integer value.
• Key partitioning: Similar to partitioning by hash, 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. You must have one or more columns containing > integer values.

You can determine whether MySQL supports partitioning by using the show variables command, for example:

SHOW VARIABLES like '%partition% ';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| Have_partition_engine | YES   |

+-----------------------+-------+

1 row in Set (0.00 sec)

1. Range partition

CREATE TABLE T_range (
ID Int (11),
Money Int (one) unsigned NOT NULL,
Date datetime
) partition by range (date) (
Partition p2007 values less than (2008),
Partition p2008 values less than (2009),
Partition p2009 values less than (2010)
Partition p2010 values less than MaxValue
)；

2.list Partitioning

CREATE TABLE T_list ( a int (11), b Int (11) ) (Partition by list (b) Partition P0 values in (1,3,5,7,9), Partition P1 values in (2,4,6,8,0) ); For InnoDB and MyISAM engines, when a statement is inserted into multiple records, if there are values in the middle that cannot be inserted, InnoDB is rolled back, and the data MyISAM before the error value can be inserted into the table.

For InnoDB and MyISAM engines, when a statement is inserted into multiple records, if there are values in the middle that cannot be inserted, InnoDB is rolled back, and the data MyISAM before the error value can be inserted into the table. 3.hash Partitioning

The purpose of the hash partition is to distribute the data evenly across the predefined partitions, ensuring that the data volume of each partition is roughly the same.

CREATE TABLE T_hash ( a int (11), b datetime ) partition by hash (year (b) Partitions 4;

The hash partition function page needs to return an integer value. The value in the Partitions clause is a non-negative integer, without the addition of the partitions clause, by default the number of partitions is 1.

4.key Partitioning

The key partition and the hash partition are similar, the difference is that the hash partition is the user custom function partition, the key partition uses the function which the MySQL database provides to partition, NDB cluster uses the MD5 function to partition, for other storage engine MySQL uses the internal hash function, These functions are based on the same algorithm as password ().

CREATE TABLE T_key ( a int (11), b datetime) Partition by key (b) Partitions 4;

5. Columns Partition

In the range above, list, HASH, key four kinds of partitions, the condition of the partition must be shaping, if it is not shaping need to convert it to shaping through a function.

mysql-5.5 begins to support the columns partition, which can be considered as the evolution of the range and list partition, and the columns partition can be partitioned directly using non-shaping data. The columns partition supports the following data types:

All shaping, such as int SMALLINT TINYINT BIGINT. float and decimal are not supported.

Date types, such as Date and DateTime. The remaining date types are not supported.

String types, such as char, VARCHAR, binary, and varbinary. Blob and text types are not supported.

Columns can be partitioned using multiple columns.

New Partition

mysql> ALTER TABLE sale_data

ADD PARTITION (PARTITION p201010 VALUES less THAN (201011));

Query OK, 0 rows affected (0.36 sec)

records:0 duplicates:0 warnings:0

Delete Partition

--When a partition is deleted, all the data in that partition is also deleted.

mysql> ALTER TABLE sale_data DROP PARTITION p201010;

Query OK, 0 rows affected (0.22 sec)

records:0 duplicates:0 warnings:0

Merging of partitions

The following SQL, merges p201001-p201009 into 3 partitions p2010q1-p2010q3

mysql> ALTER TABLE sale_data

REORGANIZE PARTITION p201001,p201002,p201003,

p201004,p201005,p201006,

-p201007,p201008,p201009 into

(

-PARTITION p2010q1 VALUES less THAN (201004),

-PARTITION p2010q2 VALUES less THAN (201007),

-PARTITION p2010q3 VALUES less THAN (201010)

);

Query OK, 0 rows affected (1.14 sec)

records:0 duplicates:0 warnings:0

MySQL's Partitioning technology