Mysql 简明教程

MySQL - Horizontal Partitioning

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

The MySQL Partitioning is a technique that can be used to divide a database table into smaller tables i.e. partitions. These smaller tables are stored in different physical locations and are treated as separate tables. Thus, the data in these smaller tables can be accessed and managed individually.

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

But note that, even if the data smaller tables is managed separately, they are not independent tables; i.e., they are still a part of main table.

MySQL 中有两种分区形式： Horizontal Partitioning 和 Vertical Partitioning.

There are two forms of partitioning in MySQL: Horizontal Partitioning and Vertical Partitioning.

MySQL Horizontal Partitioning

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

The MySQL Horizontal partitioning is used to divide the table rows into multiple partitions. Since it divides the rows, all the columns will be present in each partition. All the partitions can be accessed individually or collectively.

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

There are several types of MySQL horizontal partitioning methods −

MySQL Range Partitioning

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

The MySQL RANGE partitioning is used to divide a table into partitions based on a specific range of column values. Each table partition contains rows with column values falling within that defined range.

Example

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

Let us create a table named CUSTOMERS and partition it by the AGE column into four partitions: P1, P2, P3, and P4 using the "PARTITION BY RANGE" clause −

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)
);

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

Here, we are inserting rows into the above created table −

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

Following is the CUSTOMERS table obtained −

现在我们已在表 CUSTOMERS 中有一些数据，我们可以显示分区状态，以使用以下查询查看数据在分区间的分布情况：

Now that we have some data in the CUSTOMERS table, we can display the partition status to see how the data is distributed among the partitions using the following query −

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

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

The above query will show us the number of rows in each partition. For example, P1 has 1 row, P2 has 2 rows, P3 has 2 rows, and P4 has 2 rows as shown below −

Displaying Partitions −

Displaying Partitions −

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

We can also display data from specific partitions using the PARTITION clause. For instance, to retrieve data from partition P1, we use the following query −

SELECT * FROM CUSTOMERS PARTITION (p1);

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

It will display all the records in partition P1 −

同样，我们还可以使用相同的语法来显示其他分区。

Similarly, we can display other partitions using the same syntax.

Handling Data Outside the Range −

Handling Data Outside the Range −

如果我们尝试将不属于任何已定义分区的值插入到 AGE 列，它将失败，并显示错误，如下所示：

If we attempt to insert a value into the AGE column that doesn’t fall within any of the defined partitions, it will fail with an error, as shown below −

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

以下是获得的错误 −

Following is the error obtained −

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

Truncating Partitions −

Truncating Partitions −

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

We can also manage partitions by truncating them if needed. For example, to empty partition P2, we can use the following query −

ALTER TABLE CUSTOMERS TRUNCATE PARTITION p2;

获得的输出如下所示 −

The output obtained is as shown below −

Query OK, 0 rows affected (0.03 sec)

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

This will remove all data from partition P2, making it empty as shown below −

SELECT * FROM CUSTOMERS PARTITION (p2);

下面是生成的输出−

Following is the output produced −

Empty set (0.00 sec)

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

We can verify the CUSTOMERS table using the following SELECT query −

SELECT * FROM CUSTOMERS;

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

We can see in the table below that the rows belonging to p2 partition are deleted −

MySQL List Partitioning

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

The MySQL List Partitioning is used to divide the table into partitions based on a discrete set of values for a specific column. Each partition contains rows that match a particular value within the defined set.

Example

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

In this example, we will create a table named STUDENTS and divide it into four partitions (P1, P2, P3, and P4) based on the "DEPARTMENT_ID" column using the "PARTITION BY LIST" clause −

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)
);

在此，我们正在向上述创建的表中插入行：

Here, we are inserting rows into the above-created table −

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

Following is the STUDENTS table obtained −

我们可以显示 STUDENTS 表的分区状态，以使用以下查询查看数据在分区间的分布情况：

We can display the partition status of the STUDENTS table to see how the data is distributed among partitions using the following query −

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

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

The output of this query will show the number of rows in each partition. For instance, P1 has 1 row, P2 has 2 rows, P3 has 1 row, and P4 has 3 rows −

MySQL Hash Partitioning

MySQL HASH 分区用于根据特定列上的哈希函数将表数据划分为分区。数据将平均分布在各个分区中。

The MySQL HASH partitioning is used to divide the table data into partitions using a hash function based on a specific column(s). The data will be evenly distributed among the partitions.

Example

在以下查询中，我们创建了一个名为 EMPLOYEES 的表，其中包含基于“id”列的四个分区，并使用了 PARTITION BY HASH 子句-

In the following query, we are creating a table with the name EMPLOYEES with four partitions based on the "id" column using the PARTITION BY HASH clause −

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;

在此，我们正在向上述创建的表中插入行：

Here, we are inserting rows into the above-created table −

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 表如下所示-

The EMPLOYEES table obtained is as follows −

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

The records are evenly distributed among four partitions based on the "id" column. You can verify the partition status using the following SELECT query −

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

获得的表如下 −

The table obtained is as follows −

Key Partitioning

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

The MySQL key partitioning is used to divide the table data into partitions based on the values of the primary key or a unique key.

Example

在以下查询中，我们创建了一个名为 PERSON 的表，其中包含“id”列上的密钥分区。我们已将该表划分为四个分区，并且主键是“id”-

In the following query, we are creating a table with the name PERSON with Key partitioning on the "id" column. We have divided the table into four partitions, and the primary key is "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;

在此，我们正在向上述创建的表中插入行：

Here, we are inserting rows into the above-created table −

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

Following is the PERSON table obtained −

同样，数据根据“id”列平均分布在分区中，您可以使用下面给出的查询验证分区状态-

Again, the data is evenly distributed among partitions based on the "id" column, and you can verify the partition status using the query given below −

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

获得的输出如下所示 −

The output obtained is as shown below −

MySQL Sub-partitioning

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

The MySQL subpartitioning is used to further divide partitions based on another column, often used in conjunction with other partitioning methods like RANGE or HASH.

Example

让我们创建一个包含“order_date”列上 RANGE 分区的 CUSTOMER_ORDERS 表，然后我们将通过对“order_date”的月份进行哈希运算来进行子分区-

Let us create a CUSTOMER_ORDERS table with RANGE partitioning on the "order_date" column, and then we will subpartition by hashing on the month of "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)
);

在此，我们正在向上述创建的表中插入行：

Here, we are inserting rows into the above-created table −

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

Following is the CUSTOMERS_ORDERS table obtained −

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

You can display the CUSTOMER_ORDERS table and verify the partition status using the following query −

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

以下是要获得的表：

Following is the table obtained −