图文课程 职群旗下

  1. 首页
  2. 所有分类
  3. Mysql
  4. [MySQL Reference Manual] 20 分区

[MySQL Reference Manual] 20 分区

20 分区

20 分区... 1

20.1 MySQL的分区概述... 2

20.2 分区类型... 3

20.2.1 RANGE分区... 3

20.2.2 LIST分区... 5

20.2.3 COLUMNS分区... 7

20.2.3.1 RANGE COLUMNS分区... 7

20.2.3.2 LIST COLUMNS分区... 7

20.2.4 Hash分区... 8

20.2.4.1 LINEAR HASH分区... 8

20.2.5 Key分区... 9

20.2.6 子分区... 9

20.2.7 MySQL分区如何处理NULL. 11

20.3 分区管理... 11

20.3.1 RANGELIST分区管理... 11

20.3.2 管理HASHKEY分区... 12

20.3.3移动表的分区和子分区... 13

20.3.4 管理分区... 15

20.3.5 获取分区的信息... 15

20.4 分区裁剪(Pruning)16

20.5 分区选择... 19

20.6 分区的限制和缺点... 19

20.6.1 分区键,主键,唯一键... 21

20.6.2 各个存储引擎分区表限制... 21

20.6.3 分区表对函数的限制... 21

20.6.4 分区和锁... 21

 

可以通过show plugins查看是否支持分区。

mysql> SHOW PLUGINS;

+------------+----------+----------------+---------+---------+

| Name       | Status   | Type           | Library | License |

+------------+----------+----------------+---------+---------+

| binlog     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| partition  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| ARCHIVE    | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| BLACKHOLE  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| CSV        | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| FEDERATED  | DISABLED | STORAGE ENGINE | NULL    | GPL     |

| MEMORY     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| InnoDB     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| MRG_MYISAM | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| MyISAM     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| ndbcluster | DISABLED | STORAGE ENGINE | NULL    | GPL     |

+------------+----------+----------------+---------+---------+

11 rows in set (0.00 sec)

也可以检查information_schema.plugins表检查是否支持。

如果partitionstatus不是active,或者没有记录。那么就不支持分区。

如果编译的时候已经支持了分区,就不需要去启动。如果想要不支持分区,可以使用参数—skip-partition选项。不启动分区后,不能看到已经分区的表,也不能删除他们。

20.1 MySQL的分区概述

SQL标准一般不会涉及到关于数据存放物理方面。SQL语言本身尽量从数据结构,schema,表,行,列中独立出来。但是很多高级的数据库管理系统都涉及了某些数据存放的物理位置,文件系统,硬件等。在MySQLInnoDB存储引擎,支持表空间已经很久,MySQL服务可以把不同的数据库存放到不同的物理目录中。

分区者更进了一步,把一个表根据规则分布到文件系统。实际上表的不同分区以独立表的方式被保存在不同的位置上。用户选择的分区规则,在MySQL中可以是一个模块,一个range或者一个值的列表,或者内部hash函数,或者线性hash函数。这个方法根据用户指定的分区类型决定,参数为用户提供表达式的值。这个表达式可以是一个列的值,可以是一个或者多个列的值,也可以是列值的集合,根据分区类型决定。

比如RANGE,LIST,[LINEAR]HASH分区,把分区列传递到分区函数,然后返回一个整型表示该行应该存放的分区号。函数必须是非常量或者非随机。也不能包含查询但是可以使用SQL表达式只要表达式返回要不是NULL要不是整型数据。

对于[LINEAR] KEY,RANGE COLUMNS,LIST COLUMNS分区,分区表达式由一个或者多个列组成。

对于[LINEAR] KEY表达式,分区函数由MySQL提供。

这个就是水平分区,对于垂直分区目前不支持。大多数存储引擎是支持分区的,MySQL分区引擎是独立的一层,可以和其他引擎进行交互。在MySQL 5.7一个表的所有分区必须使用相同的引擎。以下引擎不支持分区:MERGECSVFEDERATED存储引擎。

要指定分区存储引擎,和非分区表一样,指定engine参数:

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
    ENGINE=INNODB
    PARTITION BY HASH( MONTH(tr_date) )
    PARTITIONS 6;

每个分区都可以指定存储引擎,但是在MySQL 5.7中没有效果。

每个分区的数据和索引可以适应data directory index directory选项分配独立的目录。DATA DIRECTOYINDEX DIRECTORY对于windowsmyisam存储引擎不支持。Innodb所有平台都支持。

分区表达式中的涉及到的列必须是唯一索引的一部分,包括主键。也就是说以下表不能创建分区:

CREATE TABLE tnp (
    id INT NOT NULL AUTO_INCREMENT,
    ref BIGINT NOT NULL,
    name VARCHAR(255),
    PRIMARY KEY pk (id),
    UNIQUE KEY uk (name)
);

因为pkuk没有相同的列,没有列可以用来作为分区列。要么pk上加上nameid加到uk,也可以直接删掉uk

分区表的一些好处:

1.分区表可以把一个表的数据分散到不同的文件系统或者磁盘中。

2.分区表的数据比较容易删除,可以直接删除一个分区。

3.如果where子句可以满足分区列,那么查询性能会得到提升。

其他的好处:

1.聚合函数,如果在分区表上可以并发。

2.数据分散在多个磁盘上,加大查询的吞吐量。

20.2 分区类型

20.2.1 RANGE分区

安装RANGE分区表的,行会根据RANGE的划分存放到分区中。RANGE是连续的但是没有重叠,使用VALEUS LESS THAN定义。对于store_id进行分区:

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

在这个分区框架,所有的行store_id15都保存在p0分区中,610保存在p1中类推。在这里如果插入21就会报错因为没有地方存放这个记录。可以做一下修改存放大记录:

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

MAXVALUE表示最大值,比int型最大值还要大。所以大于16的都会被放在p3分区中。分区列也可以使用表达式:

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

那么在1991年之前离职的员工都会保存在p0依次类推。如果分区列时个时间戳字段,可以如下分区:

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

RANGE在以下场景下非常有用:

1.想要删除老的数据,刚好在p0上,那么可以直接alter table employee drop partition p0删除分区,达到删除数据的目的

2.想要使用包含时间或者日期的列,或者其他连续的升序列。

3.想要频繁的根据分区列进行查询。可以快速的定位到某个分区。

有个RANGE分区的变种RANGE COLUMNS,可以多个列一起决定一个分区。

20.2.2 LIST分区

很多情况下range分区和list分区很像。LISTRANGE的区别是LIST是一组值,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 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)
);

LIST分区删除数据也会很快比如要删除pWest分区数据,用阶段分区比delete快。和RANGE不同没有MAXVALUE。所有的值都要在分区里面,不然就报错。

mysql> CREATE TABLE h2 (

    ->   c1 INT,

    ->   c2 INT

    -> )

    -> PARTITION BY LIST(c1) (

    ->   PARTITION p0 VALUES IN (1, 4, 7),

    ->   PARTITION p1 VALUES IN (2, 5, 8)

    -> );

Query OK, 0 rows affected (0.11 sec)

 

mysql> INSERT INTO h2 VALUES (3, 5);

ERROR 1525 (HY000): Table has no partition for value 3

报错的时候,如果表示innodb表就会全部回滚。如果是非事务表,错误之前都插入了,之后的都没插入。

也可以通过IGNORE关键字,对错误行进行忽略,这样错误行就会自动被忽略,正常行就可以被全部插入。

mysql> TRUNCATE h2;

Query OK, 1 row affected (0.00 sec)

 

mysql> SELECT * FROM h2;

Empty set (0.00 sec)

 

mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);

Query OK, 3 rows affected (0.00 sec)

Records: 5  Duplicates: 2  Warnings: 0

 

mysql> SELECT * FROM h2;

+------+------+

| c1   | c2   |

+------+------+

|    7 |    5 |

|    1 |    9 |

|    2 |    5 |

+------+------+

3 rows in set (0.00 sec)

20.2.3 COLUMNS分区

COLUMNS分区是RANGELIST的变种。COLUMNS分区可以可以使用多个列作为分解键。2个列都可以用来分配分区。另外RANGE COLUMNSLIST COLUMNS分区支持费insert定义range或者list项。允许的字段类型如下:

1.所有int类型

2.datedatetime类型

3.字符串类型,charvarcharbinaryvarbinaryTEXTBLOB列不支持分区。

20.2.3.1 RANGE COLUMNS分区

RANGE列分区和RANGE分区很像。就是启用了多列range。另外你可以使用其他数据类型,不一定只用int类型。

RANGE COLUMNSRANGE明显的区别有以下几点:

1.RANGE COLUMNS不能用表达式,只能是列名

2.RANGE COLUMNS可以使用一个或者多个列。

3.RANGE COLUMNS分区列可以不是int类型。

mysql> CREATE TABLE rcx (

    ->     a INT,

    ->     b INT,

    ->     c CHAR(3),

    ->     d INT

    -> )

    -> PARTITION BY RANGE COLUMNS(a,d,c) (

    ->     PARTITION p0 VALUES LESS THAN (5,10,'ggg'),

    ->     PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),

    ->     PARTITION p2 VALUES LESS THAN (15,30,'sss'),

    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)

    -> );

Query OK, 0 rows affected (0.15 sec)

如创建了一个rcx表,有4个列,a,b,c进行分区。那么如果有一样要插入,根据a,b,c依次对比,来决定放入哪个分区。

20.2.3.2 LIST COLUMNS分区

MySQL 5.7支持LIST COLUMNS分区。是LIST分区的变种可以使用非int类型作为分区列,而且可以使用多个列。

20.2.4 Hash分区

使用HASH分区可以保证数据均匀的分布在各分区上面。使用RANGELIST分区需要显示给定值进行分区。使用hash分区,MySQL会帮你处理。

使用hash分区,使用create table子句PARTITION BY HASH(expr),表达式返回int类型。然后需要指定分区个数,如PARTITIONS 4

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;

如果不包含PARTITIONS 那么默认分区个数是1个。

20.2.4.1 LINEAR HASH分区

MySQL也支持线性hash分区,和传统分区不一样线性hash利用线性2的指数算法,来代替老的hash函数。

算法大致如下:

num表示分区个数

1.V=POWER(2,CEILING(LOG(2,NUM)))Vnum2的最小幂次的值。

2.N=expr&(V-1)计算分区号

3.如果N>=num,N=N&(ceil(V/2)-1),否则就用N编号的分区。

如表如下:

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY LINEAR HASH( YEAR(col3) )
    PARTITIONS 6;

插入了col3为‘20030414’的列:

V = POWER(2, CEILING( LOG(2,6) )) = 8

N = YEAR('2003-04-14') & (8 - 1)

   = 2003 & 7

   = 3

 

(3 >= 6 is FALSE: record stored in partition #3)

如果N>=num那么就需要额外计算,比如:

V = 8

N = YEAR('1998-10-19') & (8-1)

  = 1998 & 7

  = 6

 

(6 >= 6 is TRUE: additional step required)

 

猜你喜欢
学校项目过程中知识点 Java 2015/9/15 晚 iOS常识名词解释 2016/04/05
JS实现回到页面顶部动画效果 2016.03.23 网页icon和文本对齐神技 2016.03.23
node.js 小爬虫 imooc 2016.03.06 css布局小技巧 2016.03.06
打开自定义链接新窗口(safari JS prompt的坑!)2016.03.08 CSS3实用方法小记 2016.03.16
CSS3弹性盒模型flex box快速入门 2016.03.16 Mysql 表分区