MySQL Key Partition

Introduction

The key partition is similar to the hash partition, but the key partition supports partitioning of all data types except text and blobs, while the hash partition supports only digital partitioning, and the key partition does not allow partitioning with user-defined expressions, and the key partition is partitioned using the system-provided hash function. When a primary key or unique key exists in a table, if you create a key partition without specifying a field, the system defaults to the primary key column as the partition character column, and if the primary key column does not exist, select the non-NULL unique key column as the partition column, and note that the unique column as the partition column cannot be null.

First, the general key

1. Create a partition

 create  table   Tb_key (id  int  ,  var  char  (32   by  key  (var  ) partitions  10 ; 

SELECTfromWHERE table_schema=SCHEMA and table_name  ='tb_key';

INSERT  intoTb_key ()VALUES(1,'Monday'),(2,'1998-10-19'),(3,'New'),(4,'very good'),(5,'5');

Second, LINEAR KEY

The same key partition also has a linear key partition, with the same concept as a linear hash partition.

1. Create a partition

CREATE TABLE tb_keyline (    INTnotNULL,    CHAR(5   byKEY3;

Third, partition management

Key partition management and hash partition management is the same, can only delete and add partitions, here is no longer detailed introduction.

1. Delete 2 partitions

ALTER TABLE COALESCE 2;

2. Add three partitions

ALTER TABLE Add 3;

Iv. removing partitions from a table

ALTER TABLE Tablenameremove Partitioning;

Note: Removing a partition using remove removes only the definition of the partition and does not delete the data and the drop partition, which is deleted along with the data

Reference:

Range Partition: http://www.cnblogs.com/chenmh/p/5627912.html

List partition: http://www.cnblogs.com/chenmh/p/5643174.html

Column partition: http://www.cnblogs.com/chenmh/p/5630834.html

Hash Partition: Http://www.cnblogs.com/chenmh/p/5644496.html

Sub-partition: http://www.cnblogs.com/chenmh/p/5649447.html

Specify each partition path: http://www.cnblogs.com/chenmh/p/5644713.html

Partition Build Index: http://www.cnblogs.com/chenmh/p/5761995.html

Partition Introduction Summary: http://www.cnblogs.com/chenmh/p/5623474.html

Summary

The key partition is similar to hash partition, which can effectively disperse data hotspots when processing large amount of data records.

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly give the link. Welcome to the exchange of discussions

MySQL Key Partition