理解mysql 表分区(mysql partition)

读mysql帮助手册后的一点总结

概述　　

分区允许以一个任意的大小，跨系统、跨磁盘的将一个表分为多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。

partition的一些优点包括：

• myisam支持将表的多个部分存储于不同的文件系统或是磁盘，可以存储更多数据
• 提供直接删除一个partition的语句，速度较快
• 通过partitioning-pruning优化某些查询语句 

partition 的局限：

• innodb不支持directory选择，partition后的表不能存储于多个路径中
• 目前的mysql版本(截止到mysql5.5)中还没有对partition进行并行优化（如 sum() count() max()等）
• 目前的mysql版本中还不支持多磁盘的并行IO

partition类型

• RANGE分区

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 RANGE (YEAR(separated)) (

    PARTITION p0 VALUES LESS THAN (1991),

    PARTITION p1 VALUES LESS THAN (1996),

    PARTITION p2 VALUES LESS THAN (2001),

    PARTITION p3 VALUES LESS THAN MAXVALUE

)；

• LIST分区

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)

)；

• HASH分区

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；

• LINENEAR HASH

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；

• KEY分区

CREATE TABLE tk (

    col1 INT NOT NULL,

    col2 CHAR(5),

    col3 DATE

) 

PARTITION BY LINEAR KEY (col1)

PARTITIONS 3;

• 子分区

CREATE TABLE ts (id INT, purchased DATE)

    PARTITION BY RANGE(YEAR(purchased))

    SUBPARTITION BY HASH(TO_DAYS(purchased))

    SUBPARTITIONS 2

    (

        PARTITION p0 VALUES LESS THAN (1990),

        PARTITION p1 VALUES LESS THAN (2000),

        PARTITION p2 VALUES LESS THAN MAXVALUE

    )；

 分区修剪（Partition Pruning）

分区修剪是MySQL5.1.6版本后实现的，其原理很简单，一句话来描述就是“不要扫描那些没有匹配值的分区”。

分区管理（Partition Management）

分区维护（Partition Maintenance）