MySQL的分区表（Partitioned Table）功能详解

topphoenix  收藏于2021-05-01   转藏1次

MySQL分区简介

数据库分区

数据库分区是一种物理数据库设计技术。其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间，同时对于应用来说分区完全是透明的。

分区的作用

数据库性能的提升和简化数据管理，在扫描操作中MySQL优化器只扫描数据的那个分区以减少扫描范围获得性能的提高。分区技术使得数据管理变得简单，删除某个分区不会对另外的分区造成影响。MySQL从5.1版本开始支持分区，同个表中的分区表名称要唯一

MYSQL的分区形式

主要有两种形式：水平分区和垂直分区

水平分区

这种形式的分区是根据表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。水平分区一定要通过某个属性列来分割。常见的比如年份，日期等。

垂直分区（VerticalPartitioning）

这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应所有行。

可以用 show variables like '%parts%' 命令查询当前的MySQL数据库版本是否支持分区。

MySQL分区类型

现详细介绍下水平分区。水平分区根据所使用的不同分区规则可以分成几大分区类型。

  1. RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。

  2. LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

  3. HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。

  4. KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

  5. 复合分区：基于RANGE/LIST 类型的分区表中每个分区的再次分割。子分区可以是 HASH/KEY等类型。

创建分区表

创建range分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE  TABLE  cxy7_product (
id  BIGINT  NOT  NULL ,
NAME  VARCHAR  (20),
price  INT
) PARTITION  BY  RANGE (price)(
PARTITION less_1000  -- 小于 1000
VALUES
less than (1000),
PARTITION b_1000_2000  -- 1000~2000
VALUES
less than (2000),
PARTITION greater_2000  -- >2000
VALUES
less than MAXVALUE
);

以价格为依据做范围分区，这里最值得注意的是表达式必须有返回值。

创建list分区

1
2
3
4
5
6
7
8
9
10
11
12
CREATE  TABLE  cxy7_book (
id  BIGINT  NOT  NULL ,
NAME  VARCHAR  (20),
category  INT
) PARTITION  BY  LIST (category)(
PARTITION edu
VALUES
IN  (1, 3),
PARTITION com
VALUES
IN  (2, 4)
);

以分类作为分区依据，每个分类做一分区。

创建hash分区

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪 个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是为将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

1
2
3
4
5
CREATE  TABLE  cxy7_order (
id  BIGINT  NOT  NULL ,
NAME  VARCHAR  (20),
create_date  date  NOT  NULL
) PARTITION  BY  HASH ( YEAR (create_date))

创建key分区

按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL 服务器提供。KEY分区只采用一个或多个列名。

1
2
3
4
5
CREATE  TABLE  cxy7_user (
id  BIGINT  NOT  NULL ,
NAME  VARCHAR  (20),
birthday  date  NOT  NULL
) PARTITION  BY  KEY  (birthday)

创建复合分区

range - hash(范围哈希)复合分区

1
2
3
4
5
6
7
8
9
10
11
12
CREATE  TABLE  cxy7_sales (
user_id  INT  UNSIGNED  NOT  NULL  AUTO_INCREMENT  PRIMARY  KEY ,
prod_id  BIGINT  NOT  NULL ,
num  INT  NOT  NULL
) PARTITION  BY  RANGE (user_id) SUBPARTITION  BY  HASH (user_id % 2) SUBPARTITIONS 2 (
PARTITION less_1000
VALUES
LESS THAN (1000),
PARTITION greater_1000
VALUES
LESS THAN MAXVALUE
)

range- key复合分区

1
2
3
4
5
6
7
8
9
10
11
12
CREATE  TABLE  cxy7_user_1 (
id  BIGINT  NOT  NULL ,
NAME  VARCHAR  (20),
birthday  date  NOT  NULL
) PARTITION  BY  RANGE (id) subpartition  BY  KEY  (birthday) SUBPARTITIONS 2 (
PARTITION less_1000
VALUES
LESS THAN (1000),
PARTITION greater_1000
VALUES
LESS THAN MAXVALUE
)

list-hash复合分区

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE  TABLE  cxy7_user_2 (
id  BIGINT  NOT  NULL ,
dep_no  BIGINT  NOT  NULL ,
NAME  VARCHAR  (20),
birthday  date  NOT  NULL
) PARTITION  BY  list (dep_no) subpartition  BY  HASH ( YEAR (birthday)) subpartitions 2 (
PARTITION p1
VALUES
IN  (10),
PARTITION p2
VALUES
IN  (20)
);

list - key 复合分区

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE  TABLE  cxy7_user_3 (
id  BIGINT  NOT  NULL ,
dep_no  BIGINT  NOT  NULL ,
NAME  VARCHAR  (20),
birthday  date  NOT  NULL
) PARTITION  BY  list (dep_no) subpartition  BY  KEY  (birthday) subpartitions 2 (
PARTITION p1
VALUES
IN  (10),
PARTITION p2
VALUES
IN  (20)
);

分区表的管理操作

分区操作

查看分区表

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SHOW  CREATE  TABLE  cxy7_user_3;
CREATE  TABLE  `cxy7_user_3` (
   `id`  bigint (20)  NOT  NULL ,
   `dep_no`  bigint (20)  NOT  NULL ,
   ` NAME `  varchar (20)  DEFAULT  NULL ,
   `birthday`  date  NOT  NULL
) ENGINE=InnoDB  DEFAULT  CHARSET=latin1
  PARTITION  BY  LIST (`dep_no`)
SUBPARTITION  BY  KEY  (`birthday`)
SUBPARTITIONS 2
(PARTITION `p1`  VALUES  IN  (10) ENGINE = InnoDB,
  PARTITION `p2`  VALUES  IN  (20) ENGINE = InnoDB)

删除分区 (不可以删除hash或者key分区)

1
2
ALTER  TABLE  cxy7_user_3  DROP  PARTITION p1;
ALTER  TABLE  cxy7_user_3  DROP  PARTITION p1, p2 一次性删除多个分区


增加分区

1
2
3
4
5
ALTER  TABLE  cxy7_book  ADD  PARTITION (
PARTITION less_4000
VALUES
IN  (5, 6)
);

拆分分区

reorganize partition关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据。分解前后分区的整体范围应该一致。

1
2
3
4
5
6
7
8
ALTER  TABLE  cxy7_book REORGANIZE PARTITION edu  INTO  (
PARTITION edu_1
VALUES
IN  (1),
PARTITION edu_3
VALUES
IN  (3)
);

合并分区

1
2
ALTER  TABLE  cxy7_book REORGANIZE PARTITION edu_1,
  edu_3  INTO  (PARTITION edu  VALUES  IN (1, 3));


重新定义分区表

1
ALTER  TABLE  cxy7_order PARTITION  BY  HASH (id) PARTITIONS 10;

删除表的所有分区

1
ALTER  TABLE  cxy7_user_2 REMOVE PARTITIONING;

重建分区

这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。它可用于整理分区碎片。

1
ALTER  TABLE  cxy7_user_3 REBUILD PARTITION p2;

分析分区

优化分区

如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB，或TEXT类型的列）作了许多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间，并整理分区数据文件的碎片。

1
ALTER  TABLE  cxy7_user_3 optimize partition p2;

分析分区

读取并保存分区的键分布。

1
ALTER  TABLE  cxy7_user_3 ANALYZE PARTITION p2;

修补分区

修补被破坏的分区

1
ALTER  TABLE  cxy7_user_3 REPAIR PARTITION p2;

检查分区

可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。

1
ALTER  TABLE  cxy7_user_3  CHECK  PARTITION p2;

这个命令可以告诉你表cxy7_user_3的分区p2中的数据或索引是否已经被破坏。如果发生了这种情况，使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区。

MySQL分区表的局限性

  1. MySQL分区处理NULL值的方式

  2. 如果分区键所在列没有not null约束。

  3. 如果是range分区表，那么null行将被保存在范围最小的分区。

  4. 如果是list分区表，那么null行将被保存到list为0的分区。

  5. 在按HASH和KEY分区的情况下，任何产生NULL值的表达式MySQL都视同它的返回值为0。

  6. 为了避免这种情况的产生，建议分区键设置成NOT NULL。

  7. 分区键必须是INT类型，或者通过表达式返回INT类型，可以为NULL。唯一的例外是当分区类型为KEY分区的时候，可以使用其他类型的列作为分区键（ BLOB or TEXT 列除外）。

  8. 对分区表的分区键创建索引，那么这个索引也将被分区,分区键没有全局索引一说。

  9. 只有RANG和LIST分区能进行子分区，HASH和KEY分区不能进行子分区。

  10. 临时表不能被分区。

获取MySQL分区表信息的几种方法

show create table 表名

可以查看创建分区表的create语句

show table status

可以查看表是不是分区表

1
SHOW  TABLE  STATUS  LIKE  '%cxy7_user_2%' ;

查看information_schema.partitions表

1
2
3
4
5
6
7
8
9
10
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM
information_schema.PARTITIONS
WHERE
table_schema =  SCHEMA  ()
AND  table_name =  'cxy7_user_2' ;

可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息

explain partitions select语句

1
EXPLAIN PARTITIONS  SELECT  *  FROM  cxy7_user_2;

通过此语句来显示扫描哪些分区，及他们是如何使用的.
 阅读全部内容  
点击查看
特别重申：本篇文档资料为 “好网角收藏夹” 注册用户(收藏家)上传共享，仅供参考之用，请谨慎辨别，不代表本站任何观点。
好网角收藏夹为网友提供资料整理云存储服务，仅提供信息存储共享平台。

信息删除举报或发邮件到：dongye2016@qq.com
类似文章
最新转藏文章

文档下载

下载连接中...
返回
TOP

提示信息

确定 返回

添加文章收藏

更多设置 (选填)

阅读权限：

转藏到我的收藏夹

非法或侵权内容举报：

提交 (如果是侵权投诉，请将相关证明材料发送到邮箱：374524223@qq.com)