MySql创建分区表

原文地址:http://www.xifenfei.com/1030.html

仅做备份,原文的排版更好,推荐直接看原文.

一、前沿知识
1、分区表类型
RANGE分区、LIST分区、HASH分区、KEY分区、子分区

2、查询是否支持分区表
mysql> SHOW VARIABLES LIKE ‘%partition%’;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| have_partition_engine | YES |
+———————–+——-+
1 row in set (0.00 sec)

3、MySQL分区处理NULL值的方式
MySQL中的分区在禁止空值（NULL）上没有进行处理，如果出现NULL，MySQL 把NULL视为0

二、分区表的创建
1、RANGE分区

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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 NOT NULL ,      store_id INT NOT NULL ) PARTITION BY RANGE (store_id) (      PARTITION p0 VALUES LESS THAN (6),      PARTITION p1 VALUES LESS THAN (11),      PARTITION p2 VALUES LESS THAN (16),      PARTITION p3 VALUES LESS THAN MAXVALUE );

2、 LIST分区

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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

Note：LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。

3、 HASH分区

1 2 3 4 5 6 7 8 9 10 11

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( YEAR (hired)) PARTITIONS 4;

3.1、LINEAR HASH分区

1 2 3 4 5 6 7 8 9 10 11

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 LINEAR HASH( YEAR (hired)) PARTITIONS 4;

Note：线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数

4、KEY分区

1 2 3 4 5 6 7

CREATE TABLE tk (      col1 INT NOT NULL ,      col2 CHAR (5),      col3 DATE ) PARTITION BY LINEAR KEY (col1) PARTITIONS 3;

Note：
1）按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的 哈希函数是由MySQL 服务器提供。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则。

2）“CREATE TABLE … PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个列名的一个列表。

5、子分区

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

CREATE TABLE ts (id INT , purchased DATE )      PARTITION BY RANGE( YEAR (purchased))      SUBPARTITION BY HASH(TO_DAYS(purchased))      (          PARTITION p0 VALUES LESS THAN (1990)          (              SUBPARTITION s0                  DATA DIRECTORY = '/disk0/data'                  INDEX DIRECTORY = '/disk0/idx' ,              SUBPARTITION s1                  DATA DIRECTORY = '/disk1/data'                  INDEX DIRECTORY = '/disk1/idx'          ),          PARTITION p1 VALUES LESS THAN (2000)          (              SUBPARTITION s0                  DATA DIRECTORY = '/disk2/data'                  INDEX DIRECTORY = '/disk2/idx' ,              SUBPARTITION s1                  DATA DIRECTORY = '/disk3/data'                  INDEX DIRECTORY = '/disk3/idx'          ),          PARTITION p2 VALUES LESS THAN MAXVALUE          (              SUBPARTITION s0                  DATA DIRECTORY = '/disk4/data'                  INDEX DIRECTORY = '/disk4/idx' ,              SUBPARTITION s1                  DATA DIRECTORY = '/disk5/data'                  INDEX DIRECTORY = '/disk5/idx'          )      );

Note:子分区是分区表中每个分区的再次分割，DATA DIRECTORY/INDEX DIRECTORY确定数据和索引的存储位置