MySQL中的分区

hudson2022
344 阅读7分钟

MySQL中的分区

Lukas Vileikis [hudson译]

2020年12月18日

在这篇博客文章中，我们将讨论MySQL 最广泛使用的特性之一 –分区。

什么是分区？

在MySQL中分区是一种数据库设计技术，在这种技术中，数据库将数据拆分为多个表，但仍通过SQL层将数据视为单个表。简单地说，当您对一个表进行分区时，您需要将其拆分为多个子表：之所以使用分区，是因为它只允许某些查询访问一部分数据，从而提高了某些查询的性能，从而使它们更快。还可以改进I/O操作，因为可以跨多个磁盘卷拆分数据和索引。

分区是如何工作的？

  • 使用SELECT查询时，分区层打开并锁定分区，查询优化器确定是否可以修剪任何分区，然后分区层将处理程序API的调用转发给处理分区的存储引擎。
  • 当使用INSERT查询时，分区层打开并锁定分区，确定该行应属于哪个分区，然后将该行转发到该分区。
  • 当使用DELETE查询时，分区层打开并锁定分区，确定哪个分区包含该行，然后从该分区中删除该行。
  • 当使用UPDATE查询时，分区层打开并锁定分区，找出包含该行的分区，获取该行并对其进行修改，然后确定哪个分区应包含新行，用插入请求将该行转发到新分区，然后将删除请求转发到原分区。

什么时候应该使用分区？

通常，分区在以下情况下很有用：

  • 您有许多需要查询的数据。
  • 你的表太大，无法适应内存。
  • 您的表包含历史数据，新数据将添加到最新分区中。
  • 您认为需要在不同的存储设备之间分发表的内容。
  • 您认为需要恢复单独分区。

如果上面描述的一个或多个场景和您的情况文吻合，那么分区可能会有所帮助。但是，在对数据进行分区之前，请记住MySQL分区有其自身的限制：

  • 分区表达式不允许使用存储过程、存储函数、用户定义函数（UDF）或插件，并且对SQL函数的支持有限。也不能使用声明或存储的变量。
  • 分区表不能包含外键或被外键引用。
  • 每个表最多有1024个分区（从MariaDB 10.0.4开始，表最多可以包含8192个分区）。
  • 仅当存储引擎支持分区时，才能对表进行分区。
  • 查询缓存不会感知分区或分区修剪。
  • 所有分区都必须使用相同的存储引擎。
  • 不支持FullTEXT(全文搜索)索引
  • 不能对对临时表进行分区

上面的选项应该可以帮助您决定分区是否适合您。

分区类型

如果您决定使用分区，请记住您有许多分区类型可供选择。我们将简要介绍以下选项，然后深入探讨：

  • 按RANGE分区可以帮助您根据给定范围内的列值对行进行分区。
  • 按LIST分区可以帮助您根据给定列表中列值的成员身份对行进行分区。
  • HASH分区可以帮助您根据用户定义的表达式返回的值对行进行分区。
  • 按KEY分区可以帮助您根据MySQL提供的哈希函数对行进行分区。

按范围分区

RANGE分区是最流行的MySQL表分区形式之一。当您按RANGE对表进行分区时，您可以按照这样的方式对表进行分区，即每个分区都包含一定数量的位于给定范围内的行。要定义分区，请定义分区的名称，然后告诉它应该包含哪些值–要按范围划分表，请添加PARTITION BY RANGE 子句。例如，如果您想将分区命名为p0，并使其包含每个小于5的值，则需要确保查询包含partition p0 VALUES less than（5）。以下是分区表的示例：

CREATE TABLE sample_table (

id INT(255) NOT NULL AUTO_INCREMENT PRIMARY KEY,

column_name VARCHAR(255) NOT NULL DEFAULT ‘’

...

) PARTITION BY RANGE (column_name) (

PARTITION p0 VALUES LESS THAN (5),

PARTITION p1 VALUES LESS THAN (10),

PARTITION p2 VALUES LESS THAN (15),

PARTITION p3 VALUES LESS THAN (20),

...

);

您还可以定义一个分区，该分区包含不在特定范围内的所有值，如下所示：

PARTITION p5 VALUES LESS THAN MAXVALUE

上面的分区名为p5，它包含其他分区不包含的所有值–MAXVALUE表示一个始终高于最大可能值的值。您还可以使用函数定义分区：

PARTITION BY RANGE (YEAR(date)) (

    PARTITION p0 VALUES LESS THAN (2000),

    PARTITION p1 VALUES LESS THAN (2010),

    PARTITION p2 VALUES LESS THAN (2020),

    PARTITION p3 VALUES LESS THAN MAXVALUE

);

在这种情况下，所有小于2000的值都存储在分区p0中，所有小于2010的值都保存在分区p1中，所有低于2020的值都储存在分区p2中，所有不属于这些范围的值都存放在分区p3中。

按列表分区

按LIST对MySQL表进行分区类似于按RANGE分区–主要区别在于，当按LIST分区表时，每个分区都是根据一组值列表中列值的成员身份而不是一系列值来定义和选择的。例如，当您知道可以将数据划分为多个较小的数据集（例如，区域）时，按LIST进行分区会很有用。假设你有一家拥有4个特许经营权的商店：一家在市中心，第二家在北部，第三家在东部，第四家在西部。您可以对表进行分区，这样属于某个特许经营的数据将存储在专用于该特许经营的分区中：

PARTITION BY LIST(store) (

PARTITION central VALUES IN (1,3,5),

PARTITION north VALUES IN (2,4,7),

PARTITION east VALUES IN (8,9),

PARTITION west VALUES IN (10,11)

);

HASH分区

通过HASH对MySQL表进行分区可以确保跨分区的数据均匀分布。如果按HASH对表进行分区，则只需指定需要将数据划分为多少个分区，其余的由MySQL负责。您可以通过向CREATE TABLE添加以下语句来使用HASH分区：

PARTITION BY HASH(id)

PARTITIONS 5;

将5替换为指定需要将数据划分为多少个分区的数字–默认数字为1。

MySQL还支持使用LINEAR HASH进行分区–线性哈希不同于常规哈希，因为线性哈希利用了两种算法的线性幂。要按LINEAR HASH对表进行分区，请将partition by HASH替换为 PARTITION BY LINEAR HASH。

按 KEY分区

按KEY对MySQL表进行分区类似于按HASH对MySQL表格进行分区–在本例中，键分区的哈希函数由MySQL服务器提供。用作分区键的任何列都必须包含整个表的主键，或者至少是表主键的一部分。如果没有指定列名作为分区键，那么将使用主键。如果没有主键，但有唯一键，则将使用唯一键。例如，以下语句都是有效的，即使第一个语句甚至没有指定分区键：

CREATE TABLE demo_table (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL DEFAULT ''
)
PARTITION BY KEY()
PARTITIONS 2;

CREATE TABLE demo_table (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
)
PARTITION BY KEY(id)
PARTITIONS 5;

结论

总而言之，如果您有大量数据、表太大而无法放入内存或者它们包含历史数据，那么分区会很有用。如果您认为需要将表的数据分布到不同的存储介质中，或者您想选择删除或恢复单个分区，那么分区也很有用。

但是，请记住MySQL中的分区有自己的缺点。分区的一个主要缺点是它会使表变大 —— 如果不牺牲空间，就无法提高速度。如果你有一个非常大的数据集，这可能是一个很大问题。

avatar
文章
阅读
粉丝