MySQL InnoDB table partitioning

Some of the benefits of partitioning include:
1), more data can be stored than a single disk or file system partition.
2), for those data that has lost its meaning, it is often possible to delete those data by deleting the partitions associated with those data. Conversely, in some cases, the process of adding new data can be easily implemented by adding a new partition specifically for those new data. Other benefits that are typically related to partitioning include those listed below. These features in the MySQL partition are not yet implemented, but we have high priority in our priority list, and we want to include these features in the 5.1 production version.
3), some queries can be greatly optimized, this is mainly through the use of a given where statement data can only be persisted in one or more partitions, so that the lookup without looking for other remaining partitions. Because partitions can be modified after the partition table has been created, the data can be re-organized when the partitioning scheme is first configured, to improve the efficiency of those common queries.
4), queries involving aggregate functions such as SUM () and count () can be processed easily in parallel. A simple example of such a query such as "Select Salesperson_id, COUNT (orders) as Order_total from sales GROUP by salesperson_id;". By "parallelism," this means that the query can be performed concurrently on each partition, and the final result will only be obtained by totaling all partitions.
5), to achieve greater query throughput by dispersing data queries across multiple disks.

Partition type:
· 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 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 that contain integer values.

Introduction to Range type partitioning-these intervals are contiguous and cannot overlap each other, and are defined using the values less than operator.

The table structure before partitioning:

Mysql> Show CREATE TABLE ca_tophitsdata;
+----------------+--------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------+
| table          | Create table                                                                                                                                                                                                                                                       |
+----------------+--------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------+
| Ca_tophitsdata | CREATE TABLE ' Ca_tophitsdata ' (
' Defid ' int (ten) unsigned not NULL,
' Urldefid ' int (ten) unsigned not NULL,
' Count ' int (ten) unsigned not NULL,
' Day ' date is not NULL,
PRIMARY KEY (' Defid ', ' Day ', ' Urldefid ')
) Engine=innodb DEFAULT Charset=utf8 |
+----------------+--------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------+

To partition this table:

Mysql> ALTER TABLE Ca_tophitsdata

PARTITION by RANGE (year)

(PARTITION P07 VALUES less THAN) ENGINE = InnoDB,

PARTITION P08 VALUES less THAN ENGINE = InnoDB,

PARTITION P09 VALUES less THAN ENGINE = InnoDB,

PARTITION P10 VALUES less THAN () ENGINE = InnoDB,

PARTITION PMAX VALUES less THAN MAXVALUE ENGINE = InnoDB);
Query OK, 1328977 rows Affected (10.88 sec)
records:1328977 duplicates:0 warnings:0

The table structure after partitioning:

Mysql> Show CREATE TABLE ca_tophitsdata;
+----------------+--------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------+
| table          | Create table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+----------------+--------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------+
| Ca_tophitsdata | CREATE TABLE ' Ca_tophitsdata ' (
' Defid ' int (ten) unsigned not NULL,
' Urldefid ' int (ten) unsigned not NULL,
' Count ' int (ten) unsigned not NULL,
' Day ' date is not NULL,
PRIMARY KEY (' Defid ', ' Day ', ' Urldefid ')
) Engine=innodb DEFAULT Charset=utf8/*!50100 PARTITION by RANGE (year) (PARTITION P07 VALUES less THAN) ENGINE = InnoDB, PARTITION P08 values less THAN engine = InnoDB, PARTITION P09 values less THAN () engine = InnoDB, P Artition P10 Values less THAN (+) ENGINE = InnoDB, PARTITION PMAX values less THAN MAXVALUE ENGINE = InnoDB) */|
+----------------+--------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------+

Comparison of data table files before and after partitioning:

Ca_tophitsdata.frm

Ca_tophitsdata.ibd

---------------------

Ca_tophitsdata.frm

Ca_tophitsdata.par

Ca_tophitsdata#p#p07.ibd

Ca_tophitsdata#p#p08.ibd

Ca_tophitsdata#p#p09.ibd

Ca_tophitsdata#p#p10.ibd

Ca_tophitsdata#p#pmax.ibd

Introduction to the LIST type partition the--list partition is implemented by using "PARTITION by LIST", where "expr" is a column value or an expression based on a column value and returns an integer value, and then through "values in (value_list)" The way to define each partition, where "Value_list" is a comma-delimited list of integers.

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,
store_id INT
)

PARTITION by LIST (store_id)
PARTITION Pnorth VALUES in (3,5,6,9,17),
PARTITION peast VALUES in (1,2,10,11,19,20),
PARTITION pwest VALUES in (4,12,13,14,18),
PARTITION pcentral VALUES in (7,8,15,16)
)；

Introduction to hash partitioning-to use a hash partition to split a table, add a PARTITION by HASH (expr) clause on the CREATE TABLE statement, where "expr" is an expression that returns an integer. It can just be the name of a column with the field type of MySQL integer. In addition, you will probably need to add a "partitions num" clause later, where NUM is a non-negative integer that represents the number of partitions the table will be partitioned into.

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,
store_id INT
)
PARTITION by HASH (store_id)
Partitions 4;

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 that contain integer values.

CREATE TABLE tk (
Col1 INT not NULL,
Col2 CHAR (5),
Col3 DATE
)
PARTITION by LINEAR KEY (col1)
Partitions 3;

Using the keyword linear in the key partition and using it in the hash partition has the same effect, the partition number is obtained by the Power of the 2 (powers-of-two) algorithm, not by the modulus algorithm.

This article is from the "Bodhi" blog, so be sure to keep this source http://zhangxingnan.blog.51cto.com/3241217/1435249