MySql创建分区表

来源：互联网发布：c语言代码图片壁纸编辑：程序博客网时间：2024/05/01 02:58

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

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

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

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 (
    idINTNOT NULL,
    fnameVARCHAR(30),
    lnameVARCHAR(30),
    hiredDATENOT NULL DEFAULT '1970-01-01',
    separatedDATENOT NULL DEFAULT '9999-12-31',
    job_codeINTNOT NULL,
    store_idINTNOT NULL
)
PARTITION BYRANGE (store_id) (
    PARTITION p0VALUESLESS THAN (6),
    PARTITION p1VALUESLESS THAN (11),
    PARTITION p2VALUESLESS THAN (16),
    PARTITION p3VALUESLESS THAN MAXVALUE
);

2、 LIST分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE employees (
    idINTNOT NULL,
    fnameVARCHAR(30),
    lnameVARCHAR(30),
    hiredDATENOT NULL DEFAULT '1970-01-01',
    separatedDATENOT NULL DEFAULT '9999-12-31',
    job_codeINT,
    store_idINT
)
PARTITION BYLIST(store_id)
    PARTITION pNorthVALUESIN(3,5,6,9,17),
    PARTITION pEastVALUESIN(1,2,10,11,19,20),
    PARTITION pWestVALUESIN(4,12,13,14,18),
    PARTITION pCentralVALUESIN(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 (
    idINTNOT NULL,
    fnameVARCHAR(30),
    lnameVARCHAR(30),
    hiredDATENOT NULL DEFAULT '1970-01-01',
    separatedDATENOT NULL DEFAULT '9999-12-31',
    job_codeINT,
    store_idINT
)
PARTITION BYHASH(YEAR(hired))
PARTITIONS 4;

3.1、LINEAR HASH分区

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE employees (
    idINTNOT NULL,
    fnameVARCHAR(30),
    lnameVARCHAR(30),
    hiredDATENOT NULL DEFAULT '1970-01-01',
    separatedDATENOT NULL DEFAULT '9999-12-31',
    job_codeINT,
    store_idINT
)
PARTITION BYLINEAR HASH(YEAR(hired))
PARTITIONS 4;

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

4、KEY分区

1
2
3
4
5
6
7
CREATE TABLE tk (
    col1INTNOT NULL,
    col2CHAR(5),
    col3DATE
)
PARTITION BYLINEARKEY (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, purchasedDATE)
    PARTITIONBYRANGE(YEAR(purchased))
    SUBPARTITIONBYHASH(TO_DAYS(purchased))
    (
        PARTITION p0VALUESLESS THAN (1990)
        (
            SUBPARTITION s0
                DATA DIRECTORY ='/disk0/data'
                INDEXDIRECTORY ='/disk0/idx',
            SUBPARTITION s1
                DATA DIRECTORY ='/disk1/data'
                INDEXDIRECTORY ='/disk1/idx'
        ),
        PARTITION p1VALUESLESS THAN (2000)
        (
            SUBPARTITION s0
                DATA DIRECTORY ='/disk2/data'
                INDEXDIRECTORY ='/disk2/idx',
            SUBPARTITION s1
                DATA DIRECTORY ='/disk3/data'
                INDEXDIRECTORY ='/disk3/idx'
        ),
        PARTITION p2VALUESLESS THAN MAXVALUE
        (
            SUBPARTITION s0
                DATA DIRECTORY ='/disk4/data'
                INDEXDIRECTORY ='/disk4/idx',
            SUBPARTITION s1
                DATA DIRECTORY ='/disk5/data'
                INDEXDIRECTORY ='/disk5/idx'
        )
    );

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