极客笔记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主要有六种分区类型，如下所示：
- 范围分区
- 列表分区
- 列分区
- 哈希分区
- 键分区
- 子分区
让我们详细地讨论每种分区。
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;
我们可以看到记录已成功插入到销售表中。
我们可以使用以下查询语句来查看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列表分区
它与范围分区相同。在这里，分区是根据与一组离散值列表匹配的列来定义和选择的，而不是一组连续的值范围。它由 PARTITION BY LIST（exp） 子句执行。exp是返回整数值的表达式或列值。VALUES IN（value_lists）语句将用于定义每个分区。
在下面的示例中，假设我们有12家商店分布在四个基于地区的特许经销商之间。该表更清楚地解释了这一点：
| Region | Store ID Number |
|---|---|
| East | 101, 103, 105 |
| West | 102, 104, 106 |
| North | 107, 109, 111 |
| South | 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 的语句，将其分成四个分区：
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子句，默认情况下将只有一个分区。如果您没有使用PARTITIONS关键字指定数量，将会抛出错误。
MySQL列分区
此分区允许我们在分区键中使用多个列。这些列的目的是将行放置在分区中，并确定要匹配行的验证分区。它主要分为两种类型：
- RANGE列分区
- LIST列分区
它们支持使用非整数列定义范围或值列表。它们支持以下数据类型：
- 所有整数类型： TINYINT，SMALLINT，MEDIUMINT，INT（INTEGER）和BIGINT。
- 字符串类型： CHAR，VARCHAR，BINARY和VARBINARY。
- DATE和DATETIME数据类型。
范围列分区： 它类似于范围分区，只有一个区别。它根据各种列定义基于范围的分区作为分区键。定义的范围是列类型与整数类型不同的列。
以下是范围列分区的语法：
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语句成功验证。
列分区： 它采用单个或多个列的列表作为分区键。它使我们能够使用除了整数类型以外的各种列作为分区列。在这种分区中，我们可以使用字符串数据类型，日期和日期时间列。
以下示例更清晰地解释了它。假设一个公司在三个城市有很多代理商用于营销目的。我们可以按以下方式组织它：
| City | Marketing Agents |
|---|---|
| New York | A1, A2, A3 |
| Texas | B1, B2, B3 |
| California | C1, C2, C3 |
以下语句使用了List Columns分区来组织代理人：
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分区
它与哈希分区类似，哈希分区使用用户指定的表达式，而MySQL服务器提供了用于键的哈希函数。如果我们使用其他存储引擎，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;
如果表具有唯一键但不包含主键，则将唯一键用作分区键。
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';
它将输出如下：
