MySQL - 水平分区

• MySQL 水平分区
• MySQL 范围分区
• MySQL 列表分区
• MySQL 哈希分区
• 键分区
• MySQL 子分区

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表 -

现在 CUSTOMERS 表中已经有了一些数据，我们可以使用以下查询显示分区状态，以了解数据在各个分区之间的分布情况 -

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

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

显示分区 -

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

SELECT * FROM CUSTOMERS PARTITION (p1);

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

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

处理超出范围的数据 -

如果我们尝试将一个不属于任何已定义分区的值插入到 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 分区的行已被删除 -

MySQL 列表分区

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

示例

在此示例中，我们将创建一个名为 STUDENTS 的表，并使用"PARTITION BY LIST"子句，根据"DEPARTMENT_ID"列将其划分为四个分区(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表 -

我们可以使用以下查询显示 STUDENTS 表的分区状态，以了解数据在各个分区之间的分布情况 -

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

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

MySQL 哈希分区

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

示例

在以下查询中，我们将创建一个名为 EMPLOYEES 的表，该表使用 PARTITION BY HASH 子句，基于"id"列创建四个分区 -

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"列均匀分布在四个分区中。您可以使用以下 SELECT 查询验证分区状态 -

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

得到的表如下 -

键分区

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

示例

在以下查询中，我们将创建一个名为 PERSON 的表，并根据"id"列进行键分区。我们将该表划分为四个分区，主键为"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', 'Krishna@tutorialspoint.com', 'Ayodhya'),
(2, 'Kasyap', 'Kasyap@tutorialspoint.com', 'Ayodhya'),
(3, 'Radha', 'Radha@tutorialspoint.com', 'Ayodhya'),
(4, 'Sarah', 'Sarah@tutorialspoint.com', 'Sri Lanka'),
(5, 'Sita', 'Sita@tutorialspoint.com', 'Sri Lanka'),
(6, 'Arjun', 'Arjun@tutorialspoint.com', 'India'),
(7, 'Hanuman', 'Hanuman@tutorialspoint.com', 'Sri Lanka'),
(8, 'Lakshman', 'Lakshman@tutorialspoint.com', 'Sri Lanka');

以下是获得的PERSON表 -

同样，数据根据"id"列均匀分布在各个分区之间，您可以使用以下查询验证分区状态 -

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

获得的输出如下所示 -

MySQL 子分区

MySQL 子分区用于根据其他列进一步划分分区，通常与其他分区方法(如 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表 -

您可以使用以下查询显示 CUSTOMER_ORDERS 表并验证分区状态 -

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

以下是获取的表 -