对MySQL 8.0分区表探索总结

解决问题no解决代码问题

于 2022-04-04 18:48:37 发布

阅读量5.4k

点赞数

分类专栏： # mysql 数据库文章标签： mysql dba 数据库

本文链接：https://blog.csdn.net/qq_39308071/article/details/122310408

版权

mysql 同时被 2 个专栏收录

17 篇文章 1 订阅

订阅专栏

数据库

13 篇文章 0 订阅

订阅专栏

Partitioning

MySQL :: MySQL 8.0 Reference Manual :: 24 Partitioning

一，分区表是什么？

1，分而治理

能够根据可以根据需要大量设置的规则在文件系统中分布各个表的部分。

比如说现在需要把表的数据按照业务日期分为历史数据和热点数据，一年位单位分不同的文件存储。

2，属于水平分区

先分别说下这两种分区的概念

水平分区：一个表的行可以分配给不同的物理分区。

垂直分区：将表的不同列分配给不同的物理分区。(MySQL 8.0 不支持垂直分区，目前没有计划在 MySQL 中引入垂直分区)

与分库分表不同：

垂直切分：例如一个庞大的业务系统，有上百张表，可以按照业务属性，把表分别放到不同的服务七中，实际是把不同的请求分发到不同的服务器中，减少某单台服务器的对应的服务压力力（按业务划分物理机器）。

水平切分：一个数据量巨大的表，有几亿数据，每次检索自然是如同蜗牛，这时可以把这些数据按照数据量千万的级别切分（按数据量划分物理机器）。

3，限制

在 MySQL 8.0 中，唯一支持分区的存储引擎是InnoDB和 NDB。

4，同表一致性

分区适用于一个表的所有数据和索引；您不能只对数据进行分区而不对索引进行分区，反之亦然，也不能只对表的一部分进行分区（针对的时整张表而不是表的一部分）。

5，优点

分区使得在一个表中存储的数据比单个磁盘或文件系统分区上的数据更多成为可能。
通过删除仅包含该数据的分区（或多个分区），通常可以轻松地从分区表中删除失去其用处的数据。相反，在某些情况下，通过添加一个或多个新分区来专门存储该数据，可以极大地促进添加新数据的过程。（批量删除时可使用删除整个分区的方式删除，减少了检索便利整个文件的过程。对独立分区的文件进行优化，检查和修复等操作，分区后的数据具备某种共性，操作时可以对整个文件进行操作，而不是针对某n行的操作，数据量大的时候不一定会走索引）
某些查询可以极大地优化，因为满足给定WHERE子句的数据只能存储在一个或多个分区上，这会自动从搜索中排除任何剩余的分区。由于在创建分区表后可以更改分区，因此您可以重新组织数据以增强在首次设置分区方案时可能不经常使用的频繁查询。

核心就是由于分文件存储，减少了遍历(IO)

二，分区类型

范围分区。 这种类型的分区根据落在给定范围内的列值将行分配给分区。
LIST 分区。 类似于分区 by RANGE，不同之处在于分区是根据与一组离散值中的一个匹配的列来选择的。
哈希分区。 使用这种类型的分区，根据用户定义的表达式返回的值选择一个分区，该表达式对要插入表的行中的列值进行操作。该函数可以由任何在 MySQL 中有效的表达式组成，该表达式产生一个非负整数值。
KEY分区。 这种类型的分区类似于分区 by HASH，只是只提供了一个或多个要评估的列，并且 MySQL 服务器提供了自己的散列函数。这些列可以包含非整数值，因为 MySQL 提供的散列函数保证整数结果，而不管列数据类型如何。

1，范围`COLUMNS`分区

每个分区都包含分区表达式值位于给定范围内的行，范围应该是连续的但不能重叠。

最后需要用MAXVALUE,意义是不能让插入找不到位置。

按数值范围：

CREATE TABLE employees (
id INT NOT NULL, 
fname VARCHAR(30), 
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 );

按日期范围：

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 COLUMNS分区RANGE在以下方面与分区有很大不同：

RANGE COLUMNS 不接受表达式，只接受列名。
RANGE COLUMNS 接受一列或多列的列表。

RANGE COLUMNS分区基于元组（列值列表）之间的比较，而不是标量值之间的比较。RANGE COLUMNS分区中行的放置也是基于元组之间的比较；这将在本节后面进一步讨论。
RANGE COLUMNS分区列不限于整数列；字符串， DATE并且 DATETIME列也可以作为分区列。

使用组合和不同的数据类型

CREATE TABLE rc4 (
    a INT,
    bTIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    c CHAR(3)
)
PARTITION BY RANGE COLUMNS(a,UNIX_TIMESTAMP(b),c) (
    PARTITION p0 VALUES LESS THAN (0,UNIX_TIMESTAMP('2008-01-01 00:00:00'),'ggg'),
    PARTITION p1 VALUES LESS THAN (10,UNIX_TIMESTAMP('2009-01-01 00:00:00'),'mmm'),
    PARTITION p2 VALUES LESS THAN (10,UNIX_TIMESTAMP('2010-01-01 00:00:00'),'sss')
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
 );

2，LIST 分区

与范围分区类似，但不同的是可以能自定义的设置分区的值，且列表分区不需要以任何特定顺序声明。

比如我要存一组数1到10的自然数，划分奇数偶数。当然这个例子可能有点蠢，不过能表达意思就好。

CREATE TABLE group_numbers(
    id INT NOT NULL,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pOddNum VALUES IN (1,3,5,7，9),
    PARTITION pEvenNum VALUES IN (2,4,6,8,10)
);

MySQL 8.0 还提供对LIST COLUMNS分区的支持，这是一种LIST分区变体，使您能够使用整数类型以外的类型的列对列进行分区，并使用多个列作为分区键。与RANGE COLUMNS分区相同

3，哈希分区

分区依据HASH主要用于确保数据在预定数量的分区之间均匀分布。要使用分区对表进行HASH分区，必须在CREATE TABLE语句后附加一个子句，其中是一个返回整数的表达式。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

如果不包含PARTITIONS子句，则分区数默认为1; 使用PARTITIONS后面没有数字的关键字会导致语法错误。

同样有扩展：线性哈希分区

MySQL 还支持线性散列，它与常规散列的不同之处在于，线性散列使用线性二次幂算法，而常规散列使用散列函数值的模数。

在语法上，线性哈希分区和常规哈希的唯一区别是LINEAR在PARTITION BY子句中添加了关键字。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01'
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 4;

通过线性散列进行分区的优势在于，分区的添加、删除、合并和拆分速度更快，这在处理包含大量（TB）数据的表时非常有用。缺点是与使用常规散列分区获得的分布相比，数据不太可能在分区之间均匀分布。

4，KEY分区

按键分区类似于按哈希分区，不同之处在于哈希分区使用用户定义的表达式，键分区的哈希函数由 MySQL 服务器提供。

KEY只接受零个或多个列名的列表。任何用作分区键的列都必须包含表的部分或全部主键（如果表有主键）。如果没有指定列名作为分区键，则使用表的主键（如果有）。

CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;


CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;

分区键不支持带有索引前缀的列。这意味着 CHAR, VARCHAR, BINARY, 和 VARBINARY列可以在分区键中使用，只要它们不使用索引；因为必须为BLOB和指定前缀TEXT索引定义中的列，不能在分区键中使用这两种类型的列。

也可以通过线性键对表进行分区。

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

线性关键字对键分区的影响与对散列分区的影响相同，分区号是使用二次幂算法而不是模算法推导出来的。

三，分区管理

一个分区表的所有分区必须有相同数量的子分区；一旦创建了表，就无法更改子分区。

在给定的语句中只能使用单个PARTITION BY、ADD PARTITION、DROP PARTITION、 REORGANIZE PARTITION或COALESCE PARTITION子句ALTER TABLE。如果您（例如）希望删除分区并重新组织表的剩余分区，则必须在两个单独的 ALTER TABLE语句中执行此操作（一个 using DROP PARTITION，然后第二个 using REORGANIZE PARTITION）。

可以删除一个或多个选定分区中的所有行