Detailed introduction of MySQL partitioning technology

　　 I. Overview

When the total number of MySQL records exceeds 1 million, there will be a significant decline in performance? Yes, but the rate of performance degradation > is different, depends on the architecture of the system, the application, and > including indexes, server hardware and many other factors. When a netizen asks me this question, my most common answer > is: The table, may according to the ID interval or the time order and so on many kinds of rules to divide the table. The table is easy, but the resulting application and even architectural changes are not > underestimated, including future extensibility.

In the past, one solution was to use the MERGE

Type, which is a very convenient cooking. Architecture and procedures are largely not used for change, but the drawbacks are obvious:

1. Can only be used on MyISAM tables of the same structure

2. Unable to enjoy the full functionality of the MyISAM, such as the inability to perform fulltext searches on the MERGE type

3. It needs to use more file descriptors

4. Read index more slowly

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

1. More data can be stored than a single disk or file system partition

2. It's easy to delete unused or obsolete data.

3. Some queries can be greatly optimized

4. When it comes to aggregate functions such as SUM ()/count (), it can be done in parallel

Greater 5.IO Throughput

Partitions allow rules that can be set to any size, allocating multiple portions of a single table across a file system. In fact, different parts of the table are stored as separate tables in different places.

What should be noted for partitioning:

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

2, the partition field can not be null, otherwise how to determine the scope of the partition, so try NOT null

　　Ii. Types of partitions

1.RANGE partitioning: Assigning multiple rows to a partition based on a column value that belongs to a given contiguous interval.

2.LIST partitions: Similar to a range partition, the difference is that the list partition is selected based on a column value matching a value in a set of discrete values.

2.HASH partition: A partition that is selected based on the return value of a user-defined expression, calculated using the column values of the rows that will be inserted into the table. This function can be wrapped > contains any expressions that are valid in MySQL and produce non-negative integer values.

3.KEY partitions: Similar to a hash partition, 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. Must have one or more columns containing > integer values.

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

The code is as follows:

Mysql> show VARIABLES like '%partition% ';

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

| variable_name | Value |

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

| Have_partition_engine | YES |

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

1 row in Set (0.00 sec)

The code is as follows:

Mysql> show VARIABLES like '%partition% ';

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

| variable_name | Value |

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

| Have_partition_engine | YES |

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

1 row in Set (0.00 sec)

　　1, Range partition

The code is as follows:

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 partition

The code is as follows:

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 the InnoDB and MyISAM engines, when a statement inserts more than one record, if a value cannot be inserted in the middle, the InnoDB rolls back all, myisam the data before the error value can be inserted into the table. For the InnoDB and MyISAM engines, when a statement inserts more than one record, if a value cannot be inserted in the middle, the InnoDB rolls back all, myisam the data before the error value can be inserted into the table.

　　3.hash partition

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

The code is as follows:

CREATE TABLE T_hash (

a int (11),

b datetime

Partition by hash (year (b))

Partitions 4;

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

　　4.key partition

A key partition is similar to a hash partition, except that the hash partition is partitioned by user-defined functions, and the key partition is partitioned using functions provided by the MySQL database, NDB cluster uses the MD5 function to partition, and for other storage engines MySQL uses the internal hash function, These functions are based on the same algorithm as password ().

The code is as follows:

CREATE TABLE T_key (

a int (11),

b datetime)

Partition by key (b)

Partitions 4;

　　5. Columns partition

In the above range, list, HASH, key four types of partitions, the conditions of the partition must be plastic, if not the shape of the need to pass the function of the conversion to the plastic.

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

All cosmetic, 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

The code is as follows:

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

The code is as follows:

--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

　　Merge of Partitions

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

The code is as follows:

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