MySQL partition table

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.

Third, partition operation

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

Managing operations on partitioned tables

To delete a partition:

ALTER TABLE EMP drop partition P1;

You cannot delete a hash or key partition.

Delete multiple partitions at once, ALTER TABLE EMP drop partition P1,P2;

Decomposition partition:

The Reorganizepartition keyword can modify some or all of the table's partitions without losing data. The overall range of partitions before and after decomposition should be consistent.

ALTER TABLE TE

Reorganize partition P1 into

(

Partition P1 values less than (+),

Partition P3 values less than (+)

); ----No data loss

Merge partitions:

Merge partition: Merge 2 partitions into one.
ALTER TABLE TE

Reorganize partition P1,P3 into

(partition P1 values less than ());

----No data loss

Redefine the hash partition table:

Alter table EMP Partition by hash (salary) partitions 7;

----No data loss

To redefine a range partition table:

Alter table emp Partitionbyrange (Salary)

(

Partition P1 values less than (+),

Partition P2 values less than (4000)

); ----No data loss

To delete all partitions of a table:

Alter table emp removepartitioning;--does not lose data

To rebuild a partition:

This has the same effect as deleting all the records that were saved in the partition and then re-inserting them. It can be used to defragment partition fragments.

ALTER TABLE EMP Rebuild PARTITIONP1,P2;

Optimize partitions:

If you delete a large number of rows from a partition, or make many changes to a row with variable length (that is, a column with Varchar,blob, or text type), you can use the ALTER TABLE ... OPTIMIZE PARTITION "to reclaim unused space and defragment the partition data file.

ALTER TABLE emp optimize partition p1,p2;

Parsing partitions:

Reads and saves the partition's key distribution.

ALTER TABLE EMP Analyze partition P1,P2;

Patch Partition:

Patch the damaged partition.

ALTER TABLE emp repairpartition p1,p2;

Check partition:

You can check a partition in the same way you would use a check table on a non-partitioned table.

ALTER TABLE emp CHECK partition P1,P2;

This command can tell you whether the data or index in the partition P1,P2 of the table EMP has been corrupted. If this happens, use "ALTER TABLE ... REPAIR PARTITION "to patch the partition.

Iv. limitations of the MySQL partition table

1. In version 5.1, the partitioned table has explicit rules for unique constraints, and each unique constraint must contain the partition key (and also the primary KEY constraint) of the partitioned table.

CREATE TABLE Emptt (

empno varchar() not NULL,

EmpName varchar(+),

Deptno int,

Birthdate Date not NULL,

Salary int ,

Primary KEY (EMPNO)

)

PARTITION by Range (salary)

(

PARTITION P1 VALUES less than (+),

PARTITION P2 VALUES less than ($)

);

Such statements will be an error. MySQL Database error:a PRIMARY KEY must include allcolumns in the table ' s partitioning function;

CREATE TABLE Emptt (

empno varchar() not NULL,

EmpName varchar(+),

Deptno int(one),

Birthdate Date not NULL,

Salary int(one),

Primary KEY (Empno,salary)

)

PARTITION by Range (salary)

(

PARTITION P1 VALUES less than (+),

PARTITION P2 VALUES less than ($)

);

Adding the salary column to the primary key is normal.

2. How the MySQL partition handles null values

If the partition key is in the same column, there is no notnull constraint.

If it is a range partition table, then the null row is saved in the partition with the smallest range.

If it is a list partition table, then the null row is saved to the partition with list 0.

In the case of partitioning by hash and key, any expression that produces a null value, MySQL, is treated with a return value of 0.

To avoid this situation, it is recommended that the partition key be set to not NULL.

3. The partition key must be of type int, or it can be null by returning an int type through an expression. The only exception is when the

When the zone type is a key partition, other types of columns can be used as partition keys (except BLOB or TEXT columns).

4. Create an index on the partition key of the partitioned table, the index will also be partitioned, and the partition key does not have a global index.

5. Only rang and list partitions can be sub-partitioned, and hash and key partitions cannot be sub-partitioned.

6. Temporary tables cannot be partitioned.

v. Ways to obtain MySQL partition table information

1. Show CREATE TABLE name
You can view the CREATE statement that created the partitioned table

2. Show Table Status
You can see if the table is a partitioned table

3. View the Information_schema.partitions table
Select
Partition_name part,
Partition_expression expr,
Partition_description Descr,
Table_rows
From Information_schema.partitions where
Table_schema = schema ()//This line is not
and table_name= ' test ';
You can see what partitions the table has, how it is partitioned, the number of records in the data in the partition, and more

4. Explain partitions select * FROM sales statement
This statement shows which partitions are scanned and how they are used.

MySQL partition table