什么是MySQL分区？

当数据库服务器因日常使用性能开始下降时，有多种优化选项可以帮助改善性能。 一种常见的MySQL数据库优化方法是分区。

本文将介绍MySQL分区的基础知识，如何在数据库中应用分区，以及它与分片的关系。

MySQL的分区

分区（Partitioning）是指将一个大的数据量拆分成较小的块。在MySQL中，分区意味着将数据库中的单个表拆分成多个部分。

当对MySQL中的表进行分区时，表会被拆分为多个逻辑单元，称为分区（Partitions） ，它们分别存储在磁盘上。写入表的数据会通过MySQL的分区函数来决定存储在哪个分区中——这一过程通常基于所写入行中一个或多个列的值。

MySQL内置了多种分区函数，以下部分会介绍其中的一些示例。

如何在MySQL中对表进行分区

根据应用的需求，数据库开发者可以选择多种方法对数据进行分区。以下是几种常见的分区策略：

使用 RANGE 分区

RANGE分区是最简单的分区策略之一。通过RANGE，可以定义一组数值范围，MySQL会根据这些范围将数据放入相应的分区中。可以使用 MINVALUE 和 MAXVALUE 设置范围的最小和最大值。

以下示例创建了一个图书馆的表 library_books，它的书籍会根据其出版年份（publication_year）进行分区：

CREATE TABLE library_books (
  book_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  author VARCHAR(255),
  publication_year INT,
  isbn VARCHAR(13),
  genre VARCHAR(50),
  checked_out BOOLEAN DEFAULT FALSE,
  checked_out_date DATE,
  due_date DATE,
  shelf_location VARCHAR(50)
)
PARTITION BY RANGE (publication_year) (
  PARTITION p0 VALUES LESS THAN (2001),
  PARTITION p1 VALUES LESS THAN (2011),
  PARTITION p2 VALUES LESS THAN (2021),
  PARTITION p3 VALUES LESS THAN MAXVALUE
);

使用 LIST 分区

LIST分区是另一种相对简单的分区策略。

在LIST分区中，需要为每个分区提供一组固定值，MySQL根据这些值决定如何存储数据。该策略适用于列值范围固定的场景，但缺点是如果插入的行包含无效值，会导致错误。

以下示例根据作者（author）值将数据分区：

CREATE TABLE library_books (
  book_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  author VARCHAR(255),
  publication_year INT,
  isbn VARCHAR(13),
  genre VARCHAR(50),
  checked_out BOOLEAN DEFAULT FALSE,
  checked_out_date DATE,
  due_date DATE,
  shelf_location VARCHAR(50)
)
PARTITION BY LIST (author) (
  PARTITION p0 VALUES IN ('William Shakespeare', 'Jane Austen', 'George Orwell'),
  PARTITION p1 VALUES IN ('J.K. Rowling', 'Agatha Christie', 'Stephen King'),
  PARTITION p2 VALUES IN ('J.R.R. Tolkien', 'Gabriel García Márquez', 'Toni Morrison'),
  PARTITION p3 VALUES IN ('Haruki Murakami', 'Neil Gaiman', 'Chimamanda Ngozi Adichie')
);

使用 KEY 分区

如果你希望使用分区，但不想明确指定划分规则，可以使用KEY分区。

KEY分区允许MySQL自动使用表的主键或唯一键来决定分配数据的方式。它使用内部算法将数据均匀分布到多个分区中。

以下示例使用KEY对图书馆表 library_books 进行分区：

CREATE TABLE library_books (
  book_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  author VARCHAR(255),
  publication_year INT,
  isbn VARCHAR(13),
  genre VARCHAR(50),
  checked_out BOOLEAN DEFAULT FALSE,
  checked_out_date DATE,
  due_date DATE,
  shelf_location VARCHAR(50)
)
PARTITION BY KEY()
PARTITIONS 4;

MySQL分区的优缺点

优点

分区有许多好处，前提是正确应用：

• 查询优化：对于大的表，分区可以减少查询的数据量。比如在RANGE分区中查询2007年的书，只需要扫描p1分区，而不是整个表。这种操作甚至可以在多个分区上并行执行，尤其是在分区存储在不同存储设备时，速度更快。
• 维护效率：比如删除某个分区的所有数据要比使用 DELETE ... WHERE 快得多。还可以对分区单独进行备份、索引重建和回收未使用空间，而不需要对整个表进行操作。

缺点

分区并不是万能的，存在以下缺点：

• 数据类型限制：某些分区策略可能不支持特定数据类型。例如，ENUM类型在大部分分区策略中不被允许。
• 分区不均衡问题：如果对数据访问模式理解不足，可能会使分区变得不均衡，从而影响性能优化效果。

如果你正在评估是否需要在MySQL中应用分区，建议先在测试系统上进行验证，以确保它能为你的环境带来实际效益。

分区 vs 分片

分区与分片的概念十分相似，但存在一些关键区别：

• 分区发生在同一服务器上，由MySQL原生支持；而分片是将表拆分到不同的服务器，需要额外机制来实现。
• 分区属于垂直扩展（通过优化单个服务器提升性能）；而分片是水平扩展（通过增加服务器数量分散负载）。

实际上，分区是提升性能的过渡方案。由于分区依赖单一服务器，当该MySQL服务器发生故障时仍然存在单点故障问题。而利用我们在分片方面的专业知识，分区所能带来的价值对于我们目前的能力来说相对有限。