MySQL - 水平分区

Table of content
上一页
下一页

MySQL 的分区是一种可用于将数据库表划分为较小表（即分区）的技术。这些较小的表存储在不同的物理位置，并被视为单独的表。因此，可以单独访问和管理这些较小表中的数据。

但请注意，即使单独管理较小表的数据，它们也不是独立的表；也就是说，它们仍然是主表的一部分。

MySQL 中有两种分区形式：水平分区和垂直分区。

MySQL 水平分区

MySQL 的水平分区用于将表行划分为多个分区。由于它划分的是行，因此每个分区中都会存在所有列。可以单独或集体访问所有分区。

MySQL 水平分区方法有几种类型：

MySQL 范围分区

MySQL RANGE 分区用于根据特定列值的特定范围将表划分为分区。每个表分区包含列值落在该定义范围内的行。

示例

让我们创建一个名为 CUSTOMERS 的表，并使用“PARTITION BY RANGE”子句根据 AGE 列将其划分为四个分区：P1、P2、P3 和 P4：

CREATE TABLE CUSTOMERS(
   ID int not null,
   NAME varchar(40) not null,
   AGE int not null,
   ADDRESS char(25) not null,
   SALARY decimal(18, 2)
   )
   PARTITION BY RANGE (AGE) (
   PARTITION P1 VALUES LESS THAN (20),
   PARTITION P2 VALUES LESS THAN (30),
   PARTITION P3 VALUES LESS THAN (40),
   PARTITION P4 VALUES LESS THAN (50)
);

在这里，我们将行插入到上面创建的表中：

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 19, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 31, 'Mumbai', 6500.00 ),
(5, 'Hardik', 35, 'Bhopal', 8500.00 ),
(6, 'Komal', 47, 'MP', 4500.00 ),
(7, 'Muffy', 43, 'Indore', 10000.00 );

以下是获得的CUSTOMERS 表：

ID 姓名 年龄 地址 工资
1 Ramesh 19 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 31 Mumbai 6500.00
5 Hardik 35 Bhopal 8500.00
6 Komal 47 MP 4500.00
7 Muffy 43 Indore 10000.00

现在，我们在 CUSTOMERS 表中有一些数据后，我们可以使用以下查询显示分区状态，以查看数据如何在分区之间分布：

SELECT PARTITION_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME='CUSTOMERS';

上述查询将向我们显示每个分区中的行数。例如，P1 有 1 行，P2 有 2 行，P3 有 2 行，P4 有 2 行，如下所示：

分区名称 表行数
P1 1
P2 2
P3 2
P4 2

显示分区：

我们还可以使用 PARTITION 子句显示特定分区中的数据。例如，要从分区 P1 检索数据，我们使用以下查询：

SELECT * FROM CUSTOMERS PARTITION (p1);

它将显示分区 P1 中的所有记录：

ID 姓名 年龄 地址 工资
1 Ramesh 19 Ahmedabad 2000.00

类似地，我们可以使用相同的语法显示其他分区。

处理范围之外的数据：

如果我们尝试将不在任何已定义分区范围内的值插入 AGE 列，则会失败并出现错误，如下所示：

INSERT INTO CUSTOMERS VALUES 
(8, 'Brahmi', 70, 'Hyderabad', 19000.00 );

以下是获得的错误：

ERROR 1526 (HY000): Table has no partition for value 70

截断分区：

如果需要，我们还可以通过截断分区来管理它们。例如，要清空分区 P2，我们可以使用以下查询：

ALTER TABLE CUSTOMERS TRUNCATE PARTITION p2;

获得的输出如下所示：

Query OK, 0 rows affected (0.03 sec)

这将删除分区 P2 中的所有数据，使其为空，如下所示：

SELECT * FROM CUSTOMERS PARTITION (p2);

以下是生成的输出：

Empty set (0.00 sec)

我们可以使用以下 SELECT 查询验证 CUSTOMERS 表：

SELECT * FROM CUSTOMERS;

我们可以在下表中看到属于 p2 分区的行已被删除：

ID 姓名 年龄 地址 工资
1 Ramesh 19 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
6 Komal 47 MP 4500.00
7 Muffy 43 Indore 10000.00

MySQL 列表分区

MySQL 列表分区用于根据特定列的离散值集将表划分为分区。每个分区包含与定义集内特定值匹配的行。

示例

在此示例中，我们将创建一个名为 STUDENTS 的表，并根据“DEPARTMENT_ID”列使用“PARTITION BY LIST”子句将其划分为四个分区 (P1、P2、P3 和 P4)：

CREATE TABLE STUDENTS(
   ID int,
   NAME varchar(50),
   DEPARTMENT varchar(50),
   DEPARTMENT_ID int
   )
   PARTITION BY LIST(DEPARTMENT_ID)(
   PARTITION P1 VALUES IN (3, 5, 6, 7, 9),
   PARTITION P2 VALUES IN (13, 15, 16, 17, 20),
   PARTITION P3 VALUES IN (23, 25, 26, 27, 30),
   PARTITION P4 VALUES IN (33, 35, 36, 37, 40)
);

在这里，我们将行插入到上面创建的表中：

INSERT INTO STUDENTS VALUES 
(1, 'Ramesh', "cse", 5),
(2, 'Khilan', "mech", 20),
(3, 'kaushik', "ece", 17),
(4, 'Chaitali', "eee", 33),
(5, 'Hardik', "IT", 36),
(6, 'Komal', "Hotel management", 40),
(7, 'Muffy', "Fashion", 23);

以下是获得的STUDENTS 表：

ID 姓名 系别 系别ID
1 Ramesh cse 5
2 Khilan 机械工程 20
3 Kaushik 电子工程 17
7 Muffy 时尚设计 23
4 Chaitali 电子电气工程 33
5 Hardik 信息技术 36
6 Komal 酒店管理 40

我们可以使用以下查询显示 STUDENTS 表的 partition 状态，查看数据如何在各个 partition 中分布：

SELECT PARTITION_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME='STUDENTS';

此查询的输出将显示每个 partition 中的行数。例如，P1 有 1 行，P2 有 2 行，P3 有 1 行，P4 有 3 行：

分区名称 表行数
P1 1
P2 2
P3 1
P4 3

MySQL 哈希分区

MySQL 的 HASH 分区是使用基于特定列的哈希函数将表数据划分为多个 partition。数据将在各个 partition 之间均匀分布。

示例

在以下查询中，我们使用 PARTITION BY HASH 子句创建名为 EMPLOYEES 的表，该表基于“id”列具有四个 partition：

CREATE TABLE EMPLOYEES (
   id INT NOT NULL,
   name VARCHAR(50) NOT NULL,
   department VARCHAR(50) NOT NULL,
   salary INT NOT NULL
 )
   PARTITION BY HASH(id)
   PARTITIONS 4;

在这里，我们将行插入到上面创建的表中：

INSERT INTO EMPLOYEES VALUES 
(1, 'Varun', 'Sales', 50000),
(2, 'Aarohi', 'Marketing', 60000),
(3, 'Paul', 'IT', 70000),
(4, 'Vaidhya', 'Finance', 80000),
(5, 'Nikhil', 'Sales', 55000),
(6, 'Sarah', 'Marketing', 65000),
(7, 'Tim', 'IT', 75000),
(8, 'Priya', 'Finance', 85000);

获得的 EMPLOYEES 表如下：

id 姓名 部门 薪资
4 Vaidhya 财务 80000
8 Priya 财务 85000
1 Varun 销售 50000
5 Nikhil 销售 55000
2 Aarohi 市场营销 60000
6 Sarah 市场营销 65000
3 Paul 信息技术 70000
7 Tim 信息技术 75000

记录根据“id”列在四个 partition 中均匀分布。您可以使用以下 SELECT 查询验证 partition 状态：

SELECT PARTITION_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME='EMPLOYEES';

获得的表如下：

分区名称 表行数
P0 2
P1 2
P2 2
P3 2

关键分区

MySQL 的键分区是根据主键或唯一键的值将表数据划分为多个 partition。

示例

在以下查询中，我们创建名为 PERSON 的表，在“id”列上进行键分区。我们将表分为四个 partition，主键为“id”：

CREATE TABLE PERSON (
   id INT NOT NULL,
   name VARCHAR(50) NOT NULL,
   email VARCHAR(50) NOT NULL,
   address VARCHAR(100) NOT NULL,
   PRIMARY KEY (id)
   )
   PARTITION BY KEY(id)
   PARTITIONS 4;

在这里，我们将行插入到上面创建的表中：

INSERT INTO PERSON VALUES 
(1, 'Krishna', '[email protected]', 'Ayodhya'),
(2, 'Kasyap', '[email protected]', 'Ayodhya'),
(3, 'Radha', '[email protected]', 'Ayodhya'),
(4, 'Sarah', '[email protected]', 'Sri Lanka'),
(5, 'Sita', '[email protected]', 'Sri Lanka'),
(6, 'Arjun', '[email protected]', 'India'),
(7, 'Hanuman', '[email protected]', 'Sri Lanka'),
(8, 'Lakshman', '[email protected]', 'Sri Lanka');

获得的 PERSON 表如下：

id 姓名 邮箱 地址
1 Krishna [email protected] 阿约提亚
5 Sita [email protected] 斯里兰卡
4 Sarah [email protected] 斯里兰卡
8 Lakshman [email protected] 斯里兰卡
3 Radha [email protected] 阿约提亚
7 Hanuman [email protected] 斯里兰卡
2 Kasyap [email protected] 阿约提亚
6 Arjun [email protected] 印度

同样，数据根据“id”列在各个 partition 中均匀分布，您可以使用以下查询验证 partition 状态：

SELECT PARTITION_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME='PERSON';

获得的输出如下所示：

分区名称 表行数
P0 2
P1 2
P2 2
P3 2

MySQL 子分区

MySQL 的子分区用于根据另一列进一步划分 partition，通常与其他分区方法（如 RANGE 或 HASH）结合使用。

示例

让我们创建一个 CUSTOMER_ORDERS 表，在“order_date”列上进行 RANGE 分区，然后我们将根据“order_date”的月份进行哈希子分区：

CREATE TABLE CUSTOMER_ORDERS (
   order_id INT NOT NULL,
   customer_name VARCHAR(50) NOT NULL,
   order_date DATE NOT NULL,
   order_status VARCHAR(20) NOT NULL
   )
   PARTITION BY RANGE (YEAR(order_date))
   SUBPARTITION BY HASH(MONTH(order_date))
   SUBPARTITIONS 2(
   PARTITION p0 VALUES LESS THAN (2022),
   PARTITION p1 VALUES LESS THAN (2023),
   PARTITION p2 VALUES LESS THAN (2024)
);

在这里，我们将行插入到上面创建的表中：

INSERT INTO CUSTOMER_ORDERS VALUES 
(1, 'John', '2021-03-15', 'Shipped'),
(2, 'Bob', '2019-01-10', 'Delivered'),
(3, 'Johnson', '2023-01-10', 'Delivered'),
(4, 'Jake', '2020-01-10', 'Delivered'),
(5, 'Smith', '2022-05-01', 'Pending'),
(6, 'Rob', '2023-01-10', 'Delivered');

获得的 CUSTOMERS_ORDERS 表如下：

订单ID 客户姓名 订单日期 订单状态
1 John 2021-03-15 已发货
2 Bob 2019-01-10 已送达
4 Jake 2020-01-10 已送达
5 Smith 2022-05-01 待处理
3 Johnson 2023-01-10 已送达
6 Rob 2023-01-10 已送达

您可以使用以下查询显示 CUSTOMER_ORDERS 表并验证 partition 状态：

SELECT PARTITION_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME='CUSTOMER_ORDERS';

获得的表如下：

分区名称 表行数
P0 0
P0 3
P1 0
P1 1
P2 0
P2 2
打印页面
上一页
下一页
广告