MySql表分区——partition

KitorinZero 发布于2019-08-01 13:28 / 2867人阅读

摘要：二分区方法将某张表的数据，分别存储到不同的区域中。分区的另一个亮点是支持多列分区子分区子分区是分区表中对每个分区的再次分割，又被称为符合分区。一般情况下，的分区把当作零值，或者一个最小值进行处理。

一、分区的优点

和单个磁盘或文件系统分区相比，可以存储更多的数据。

优化查询。

where 子句中包含分区条件时，可以只扫描必要的分区。

涉及聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需汇总得到结果。

对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据。

跨多个磁盘来分散数据查询，以获得更大的查询吞吐量。

二、分区方法

将某张表的数据，分别存储到不同的区域中。每个分区都是独立的表，都要存储该分区数据的数据、索引等信息。
使用mysql的分区功能，可以把一个大的数据表分成多个小份，用户不需要区分不同的表名。
表中有主键的时候，分区只能使用主键

1. KEY 分区，按照某个字段取余

create table post (
    id int unsigned not null AUTO_INCREMENT,
    title varchar(255),
    PRIMARY KEY (id)
) engine = innodb
partition by key (id) partitions 5;

2. HASH 分区，基于给定的分区个数，把数据分配到不同的分区。

create table student_hash(
    id int unsigned not null auto_increment,
    birthday date,
    PRIMARY KEY(id,birthday);
) engine=myisam
partition by hash (month(birthday)) patitions 12;

key 和 hash 分区方法可以有效的分散热点数据。

3. RANGE 分区，基于一个给定连续区间范围，把数据分配到不同的分区

create table goods (
 id int,
 uname char(10)
 )engine myisam
 partition by range(id) (
 partition p1 values less than (10),
 partition p2 values less than (20),
 partition p3 values less than MAXVALUE
 );

4.LIST 分区，类似 RANGE 分区，区别在 LIST 分区是基于枚举出的值列表分区，RANGE 是局域给定的连续区间范围分区。

create table user (
uid int,
pid int,
uname 
)engine myisam
partition by list(pid) (
partition bj values in (1),
partition ah values in (2),
partition xb values in (4,5,6)
);
# 如果试图插入的列值不包含分区值列表中时，那么 insert 操作会失败并报错，要重点注意的是，list 分区不存在类似 values less than maxvalue 这样包含其他值在内的定义方式，将要匹配的任何值都必须在值列表中找得到。

5. Clumns 分区

是在mysql5.5引入的分区类型，解决了之前版本 range 和 list 分区只支持整数分区，从而导致需要额外的函数计算得到整数或通过额外的转换表来转换为整数再分区的问题。
Columns 分区可以细分为 range columns 分区和 list columns 分区，这两种分区都支持整数，日期和字符串三大数据类型。
columns 分区的另一个亮点是支持多列分区：

mysql> create table rc3(
    a int,
    b int
)
partition by range columns(a,b)(
    partition p01 values less than (0,10),
    partition p02 values less than (10,10),
    partition p03 values less than (10,20),
    partition p04 values less than (10,35),
    partition p05 values less than (10,maxvalue),
    partition p06 values less than (maxvalue,maxvalue)
);

6. 子分区

子分区(subpartitioning)是分区表中对每个分区的再次分割，又被称为符合分区（composite partitioning）。mysql 从 mysql 5.1 开始支持对已经通过 range 或者 list 分区了的表再进行子分区，子分区既可以使用 hash 分区，也可以使用 key 分区。

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

表 ts 有3个 range 分区，这 3 个分区中的每个分区又进一步分成 2 个子分区，实际上，整个表被分成了 3*2=6 个分区，由于 partition by range 子句的作用，第一和第二个分区只保存 purchased 列中值小于 1990 的记录。
复合分区适用于保存非常大量的数据记录。

三、分区管理

1. 取余算法 key hash

采用取余算法的分区数量的修改，不会导致已有分区数据的丢失，需要重新分配数据到新的分区
增加分区： add partition N；
减少分区：coalesce partition N；

2. 条件算法 list range

添加分区

alert table goods add partition(
partition p4 values less than 40);

删除分区

alert table goods drop partition p1;
注意：删除条件算法的分区，会导致分区数据的丢失

四、mysql 分区处理 null 值的方式

mysql 不禁止在分区键值上使用 null ，分区键可能是一个字段或者一个用户定义的额表达式。一般情况下，mysql 的分区把 null 当作零值，或者一个最小值进行处理。
range 分区中，null 值会被当作最小值来处理；
list 分区中，null 值必须出现在枚举列表中，否则不被接受；
hash/key 分区中，null 值会被当作零值来处理。

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/48687.html

［MySQL］MySQL分区与传统的分库分表

摘要：传统的分库分表传统的分库分表都是通过应用层逻辑实现的，对于数据库层面来说，都是普通的表和库。分区也存在与传统分表一样的问题，可扩展性差。子分区子分区是分区表中每个分区的再次分割。查询优化分区的目的是为了传统的分库分表传统的分库分表都是通过应用层逻辑实现的，对于数据库层面来说，都是普通的表和库。分库分库的原因首先，在单台数据库服务器性能足够的情况下，分库对于数据库性能是没有影响...

xuexiangjys 2019-08-01 13:24 评论0 收藏0
MYSQL分区

摘要：显示所有插件，如果有插件则表明支持分区。如果对数据进行测试，请参考分区的测试来操作。子分区子分区是分区表中每个分区的再次分割，子分区既可以使用希分区，也可以使用分区。这也被称为复合分区。一、什么是数据库分区 mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存...

helloworldcoding 2019-07-31 17:45 评论0 收藏0
Mysql 架构及优化之-表分区

摘要：例如可以将一个表通过年份划分成若干个分区。哈希这中模式允许通过对表的一个或多个列的进行计算，最后通过这个码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。写在前面单张表超过1000W行已经算作是大数据存储场景常规海量数据优化：大表拆小表、sql语句优化今天我们重点介绍大表拆小表的优化拆表方式水平拆表将表user中的1000w行数据拆成user1表和use...

megatron 2019-07-31 16:12 评论0 收藏0
Mysql面试知识点总结(进阶篇)

摘要：第二范式建立在第一范式的基础上，要求数据库表中的每个实例或记录必须是可以唯一被区分的，即唯一标识。不同点标识符不同，函数的标识符是，存储过程是。上一篇主要介绍一些基础的mysql知识点，这一篇我们介绍一下mysql比较重要但在开发中我们程序员很少知道的几个大点(自以为是的观点)。数据库设计三范式：第一范式：数据库表的每一列都是不可分割的原子数据项，即列不可拆分。第二范式：建立在...

plus2047 2019-08-01 10:36 评论0 收藏0
实战mysql分区(PARTITION)

摘要：建这表的绝对是个人才这是一个日志表，记录了游戏中物品的产出与消耗，原先有一个后台对这个表进行统计。。。。。数据太大，决定用分区来重构。这些区间要连续且不能相互重叠，使用操作符来进行定义。前些天拿到一个表，将近有4000w数据，没有任何索引，主键。（建这表的绝对是个人才）这是一个日志表，记录了游戏中物品的产出与消耗，原先有一个后台对这个表进行统计。。。。。（这要用超级计算机才能统计得...

AprilJ 2019-08-01 10:06 评论0 收藏0