MySQL 分区
一、MySQL 分区 介绍
MySQL 中的分区用于将表的行拆分或分区为不同位置的单独表，但仍将其视为单个表。它根据我们根据要求设置的规则在文件系统中分配表的部分数据。我们为完成表数据划分而设置的规则称为分区函数（模数、线性或内部散列函数等）。所选函数基于我们指定的分区类型，并以用户提供的表达式作为其参数。用户表达式可以是列值或作用于列值的函数，具体取决于所使用的分区类型。
MySQL 8.0 仅支持 InnoDB 和 NDB 存储引擎中的分区。MyISAM、MERGE、CSV 和 FEDERATED 等其他存储引擎不支持分区。
MySQL主要有两种分区形式：
1.水平分区
这种分区根据我们的逻辑将表的行拆分为多个表。在水平分区中，每个表中的列数是相同的，但不需要保持相同的行数。它在物理上划分了表格，但在逻辑上被视为一个整体。目前，MySQL 仅支持这种分区。
2.垂直分区
此分区将表拆分为多个表，原始表中的列较少。它使用一个附加表来存储剩余的列。目前，MySQL 不提供对这种分区的支持。
二、分区的好处
以下是 MySQL 中分区的好处：
- 它优化了查询性能。当我们查询表时，它只扫描表中满足特定语句的部分。
- 可以将大量数据存储在一个表中，该表可以保存在单个磁盘或文件系统分区上。
- 它提供了更多控制来管理数据库中的数据。
三、我们如何在 MySQL 中对表进行分区？
我们可以使用CREATE TABLE或ALTER TABLE 语句在 MySQL 中创建一个分区。下面是使用 CREATE TABLE 命令创建分区的语法：
CREATE TABLE [IF NOT EXISTS] table_name
(column_definitions)
[table_options]
[partition_options]
partition_options: It provides control on the table partition.
PARTITION BY
{ [LINEAR] HASH(exp)
| [LINEAR] KEY [ALGORITHM={1 | 2}] (colm_list)
| RANGE{(exp) | COLUMNS(colm_list)}
| LIST{(exp) | COLUMNS(colm_list)} }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(exp)
| [LINEAR] KEY [ALGORITHM={1 | 2}] (colm_list) }
[SUBPARTITIONS num]
]
[(partition_definition [, partition_definition] ...)]
partition_definition: It defines each partition individually.
PARTITION part_name
[VALUES
{LESS THAN {(exp | val_list) | MAXVALUE}
|
IN (val_list)}]
[[STORAGE] ENGINE = engine_name]
[COMMENT = 'string' ]
[DATA DIRECTORY = 'data_dir']
[INDEX DIRECTORY = 'index_dir']
[MAX_ROWS = max_number_of_rows]
[MIN_ROWS = min_number_of_rows]
[TABLESPACE = tablespace_name]
[(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition: It is optional.
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
以下是使用 ALTER TABLE 命令创建分区的语法：
ALTER TABLE [IF EXISTS] tab_name
(colm_definitions)
[tab_options]
[partition_options]
三、MySQL 分区的类型
MySQL主要有六种分区类型，下面给出：
- 范围分区
- LIST 分区
- 列分区
- 哈希分区
- Key分区
- 子分区
让我们详细讨论每个分区。
MySQL 范围分区
这种分区允许我们根据指定范围内的列值对表的行进行分区。给定范围始终采用连续形式，但不应相互重叠，并且还使用VALUES LESS THAN运算符来定义范围。
在以下示例中，我们将创建一个名为“Sales”的表，其中包含五列 cust_id、name、store_id、bill_no、bill_date 和 amount。接下来，我们将根据需要以多种方式使用范围对这张表进行分区。在这里，我们将使用bill_date列进行分区，然后使用 PARTITION BY RANGE 子句以四种方式对表的数据进行分区：
CREATE TABLE Sales ( cust_id INT NOT NULL, name VARCHAR(40),
store_id VARCHAR(20) NOT NULL, bill_no INT NOT NULL,
bill_date DATE PRIMARY KEY NOT NULL, amount DECIMAL(8,2) NOT NULL)
PARTITION BY RANGE (year(bill_date))(
PARTITION p0 VALUES LESS THAN (2016),
PARTITION p1 VALUES LESS THAN (2017),
PARTITION p2 VALUES LESS THAN (2018),
PARTITION p3 VALUES LESS THAN (2020));
接下来，我们需要将记录插入到表中，如下所示：
INSERT INTO Sales VALUES
(1, 'Mike', 'S001', 101, '2015-01-02', 125.56),
(2, 'Robert', 'S003', 103, '2015-01-25', 476.50),
(3, 'Peter', 'S012', 122, '2016-02-15', 335.00),
(4, 'Joseph', 'S345', 121, '2016-03-26', 787.00),
(5, 'Harry', 'S234', 132, '2017-04-19', 678.00),
(6, 'Stephen', 'S743', 111, '2017-05-31', 864.00),
(7, 'Jacson', 'S234', 115, '2018-06-11', 762.00),
(8, 'Smith', 'S012', 125, '2019-07-24', 300.00),
(9, 'Adam', 'S456', 119, '2019-08-02', 492.20);
为了验证记录，我们将执行以下语句：
SELECT * FROM Sales;
我们可以看到记录已成功插入到 Sales 表中。
我们可以使用以下查询查看由 CREATE TABLE 语句创建的分区：
SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'myemployeedb' AND TABLE_NAME = 'Sales';
我们将在成功创建四个分区的情况下获得以下输出：
删除 MySQL 分区
有时我们的表包含分区表中无用的数据。在这种情况下，我们可以根据需要删除单个或多个分区。以下语句用于从表 Sales的分区p0中删除所有行：
ALTER TABLE Sales TRUNCATE PARTITION p0;
执行成功后，我们可以看到两行都从表中删除了。
我们可以使用以下查询来验证分区表：
SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'myemployeedb' AND TABLE_NAME = 'Sales';
在输出中，我们可以看到分区 p0 不包含任何行。
MySQL LIST 分区
它与范围分区相同。这里，分区是根据与一组离散值列表中的一个而不是一组连续的值范围匹配的列来定义和选择的。它由PARTITION BY LIST(exp)子句执行。exp 是返回整数值的表达式或列值。VALUES IN(value_lists) 语句将用于定义每个分区。
在下面的示例中，假设我们有 12 家商店，根据地区分布在四个特许经营店中。该表更清楚地解释了它：
| 地区 | 店铺编号 |
|---|---|
| 东方 | 101, 103, 105 |
| 西方 | 102、104、106 |
| 北 | 107、109、111 |
| 南 | 108、110、112 |
我们可以对上表进行分区，其中存储属于同一区域的行将存储在同一分区中。以下语句使用 LIST 分区来排列同一区域中的商店，如下所示：
CREATE TABLE Stores (
cust_name VARCHAR(40),
bill_no VARCHAR(20) NOT NULL,
store_id INT PRIMARY KEY NOT NULL,
bill_date DATE NOT NULL,
amount DECIMAL(8,2) NOT NULL
)
PARTITION BY LIST(store_id) (
PARTITION pEast VALUES IN (101, 103, 105),
PARTITION pWest VALUES IN (102, 104, 106),
PARTITION pNorth VALUES IN (107, 109, 111),
PARTITION pSouth VALUES IN (108, 110, 112));
执行成功后，会给出如下输出；
MySQL 哈希分区
此分区用于根据预定义的分区数量分配数据。换句话说，它按照用户定义的表达式返回的值拆分表。主要用于将数据均匀分布到分区中。它使用 PARTITION BY HASH(expr) 子句执行。在这里，我们可以根据要散列的 column_name 和表被划分的分区数来指定列值。
此语句用于使用 CREATE TABLE 命令创建表Store ，并在store_id列上使用散列，将其分为四个分区：
CREATE TABLE Stores (
cust_name VARCHAR(40),
bill_no VARCHAR(20) NOT NULL,
store_id INT PRIMARY KEY NOT NULL,
bill_date DATE NOT NULL,
amount DECIMAL(8,2) NOT NULL
)
PARTITION BY HASH(store_id)
PARTITIONS 4;
注意：如果不使用 PARTITIONS 子句，则默认情况下分区数为 1。如果不使用 PARTITIONS 关键字指定数字，则会抛出错误。
MySQL 列分区
这种分区允许我们在分区键中使用多个列。这些列的目的是将行放在分区中，并确定将验证哪个分区以匹配行。主要分为两种：
- RANGE 列分区
- LIST 列分区
它们支持使用非整数列来定义范围或值列表。它们支持以下数据类型：
- 所有整数类型： TINYINT、SMALLINT、MEDIUMINT、INT (INTEGER) 和 BIGINT。
- 字符串类型： CHAR、VARCHAR、BINARY 和 VARBINARY。
- DATE 和 DATETIME 数据类型。
Range Column Partitioning：它类似于范围分区，但有一个区别。它使用基于各种列的范围作为分区键来定义分区。定义的范围是除整数类型之外的列类型。
以下是范围列分区的语法。
CREATE TABLE tab_name
PARTITIONED BY RANGE COLUMNS(colm_list) (
PARTITION part_name VALUES LESS THAN (val_list)[,
PARTITION parti_name VALUES LESS THAN (val_list)][,
...]
)
colm_list: It is a list of one or more columns.
colm_name[, colm_name][, ...]
val_list: It is a list of values that supplied for each partition definition and have the same number of values as of columns.
val[, val][, ...]
让我们通过下面的例子来理解它。
CREATE TABLE test_part (A INT, B CHAR(5), C INT, D INT)
PARTITION BY RANGE COLUMNS(A, B, C)
(PARTITION p0 VALUES LESS THAN (50, 'test1', 100),
PARTITION p1 VALUES LESS THAN (100, 'test2', 200),
PARTITION p2 VALUES LESS THAN (150, 'test3', 300),
PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE));
在此示例中，表“test_part”包含 A、B、C 和 D 四列。我们在分区中使用了前三列，按 A、B、C 的顺序进行。并且，每个列表值用于定义包含三个值的分区，其顺序与 INT、CHAR 和 INT 相同。执行后，我们将得到如下输出并通过SELECT 语句验证成功地。
List Columns Partitioning：它将单个或多个列的列表作为分区键。它使我们能够使用除整数类型以外的各种类型的列作为分区列。在此分区中，我们可以使用 String 数据类型、DATE 和 DATETIME 列。
下面的例子解释得更清楚。假设一家公司出于营销目的在三个城市有许多代理商。我们可以如下组织：
| 城市 | 营销代理 |
|---|---|
| 纽约 | A1、A2、A3 |
| 德克萨斯州 | B1、B2、B3 |
| 加利福尼亚 | C1、C2、C3 |
以下语句使用 List Columns Partitioning 来组织代理：
CREATE TABLE AgentDetail (
agent_id VARCHAR(10),
agent_name VARCHAR(40),
city VARCHAR(10))
PARTITION BY LIST COLUMNS(agent_id) (
PARTITION pNewyork VALUES IN('A1', 'A2', 'A3'),
PARTITION pTexas VALUES IN('B1', 'B2', 'B3'),
PARTITION pCalifornia VALUES IN ('C1', 'C2', 'C3'));
执行成功后，我们会得到如下输出：
MySQL KEY 分区
它类似于 HASH 分区，其中哈希分区使用用户指定的表达式，并且 MySQL 服务器为 key 提供了哈希函数。如果我们使用其他存储引擎，MySQL 服务器会使用自己的内部散列函数，该函数通过使用 PARTITION BY KEY 子句执行。在这里，我们将使用 KEY 而不是 HASH，它只能接受零个或多个列名的列表。
如果表包含PRIMARY KEY并且我们没有为分区指定任何列，则使用主键作为分区键。下面的例子更清楚地解释了它：
CREATE TABLE AgentDetail (
agent_id INT NOT NULL PRIMARY KEY,
agent_name VARCHAR(40)
)
PARTITION BY KEY()
PARTITIONS 2;
如果表有唯一键但不包含主键，则为 UNIQUE KEY用作分区键。
CREATE TABLE AgentDetail (
agent_id INT NOT NULL UNIQUE KEY,
agent_name VARCHAR(40)
)
PARTITION BY KEY()
PARTITIONS 2;
子分区
它是一种复合分区，将分区表中的每个分区进一步拆分。下面的例子有助于我们更清楚地理解它：
CREATE TABLE Person (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(40),
purchased DATE
)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (2015),
PARTITION p1 VALUES LESS THAN (2020),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
执行以下语句来验证子分区：
SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'myemployeedb' AND TABLE_NAME = 'Person';
它将给出如下输出：
热门文章
优秀文章
