Mysql 简明教程

MySQL - Vertical Partitioning

MySQL Partitioning 用于将大型表划分为较小的分区，这些分区存储在不同的物理位置并被视为单独的表。即使是较小的分区被单独管理，它们仍然是主表的一部分。

The MySQL Partitioning is used to divide large tables into smaller partitions that are stored in different physical locations and are treated as separate tables. Even though the smaller partitions are managed individually, they are still part of the main table.

在 MySQL 中有两种分区的形式： Horizontal Partitioning 和 Vertical Partitioning 。

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

The MySQL Vertical Partitioning

MySQL Vertical partitioning 根据列而非行将表划分为多个表。

The MySQL Vertical partitioning divides the table into multiple tables based on columns, rather than rows.

垂直分区在 MySQL 中有两种主要类型，每类服务特定的目的：

There are two main types of vertical partitioning in MySQL, each serving specific purposes −

  1. RANGE Columns Partitioning

  2. LIST Columns Partitioning

基于范围的列分区和基于列的列表分区都支持各种数据类型，包括整数类型（TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT）、字符串类型（CHAR、VARCHAR、BINARY 和 VARBINARY）以及 DATE 和 DATETIME 数据类型。

Both Range Columns Partitioning and List Columns Partitioning support various data types, including integer types (TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT), string types (CHAR, VARCHAR, BINARY, and VARBINARY), as well as DATE and DATETIME data types.

Range Columns Partitioning

MySQL 基于范围的列分区使用一个或多个列作为分区键，基于定义的列值范围将数据分为分区。

The MySQL Range Columns partitioning uses one or more columns as partition keys to divide the data into partitions based on a defined range of column values.

这些列中的值与预定义的范围进行比较，并且每行被分配给包含其列值范围的分区。

The values in these columns are compared to predefined ranges, and each row is assigned to the partition that encompasses the range containing its column values.

Example

在以下查询中，我们正在创建一个名为 INVENTORY 的表，并根据“product_quantity”和“product_price”列将其分为三个分区。在这些列中具有特定值的列存储在其相应的分区中：

In the following query, we are creating a table named INVENTORY and dividing it into three partitions based on "product_quantity" and "product_price" columns. Rows with specific values in these columns are stored in their corresponding partitions −

CREATE TABLE INVENTORY (
   id INT,
   product_name VARCHAR(50),
   product_quantity INT,
   product_price int
)
   PARTITION BY RANGE COLUMNS(product_quantity, product_price) (
   PARTITION P_low_stock VALUES LESS THAN (10, 100),
   PARTITION P_medium_stock VALUES LESS THAN (50, 500),
   PARTITION P_high_stock VALUES LESS THAN (200, 1200)
);

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

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

INSERT INTO INVENTORY VALUES
(1, 'Headphones', 5, 50),
(2, 'Mouse', 15, 200),
(3, 'Monitor', 30, 300),
(4, 'Keyboard', 60, 600),
(5, 'CPU', 100, 1000);

以下是获得的 INVENTORY 表：

Following is the INVENTORY table obtained −

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

Now that we have some data in the INVENTORY 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='inventory';

在下面的输出中你将看到，根据已定义的范围值，各个列被分配给各个分区−

You will see in the output below that the respective columns are assigned to their respective partitions based on the defined range values −

Displaying Partitions −

Displaying Partitions −

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

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

SELECT * FROM inventory PARTITION (P_high_stock);

该查询将显示分区 P_high_stock 中的所有记录−

It will display all the records in partition P_high_stock −

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

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

List Columns Partitioning

MySQL 列表列分区使用一列或多列作为分区键，并根据这些列中的特定值将记录分配给分区。当你要根据离散值或类别将数据分组到分区中时，这种方法非常有用。

The MySQL List columns partitioning uses one or more columns as partition keys and assigns records to partitions based on specific values in those columns. This method is handy when you want to group data into partitions based on discrete values or categories.

Example

让我们创建一个名为“EMPLOYEES”的表，并使用基于“department”列的 LIST COLUMNS 分区对它进行分区−

Let us create a table named "EMPLOYEES" and partition it using LIST COLUMNS partitioning based on the "department" column −

CREATE TABLE EMPLOYEES (
   id INT,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   hiring_date DATE,
   department VARCHAR(50)
)
   PARTITION BY LIST COLUMNS(department) (
   PARTITION p_sales VALUES IN ('Sales', 'Marketing'),
   PARTITION p_engineering VALUES IN ('Engineering', 'Research'),
   PARTITION p_operations VALUES IN ('Operations')
);

在此，我们要向上述所创建的表中插入记录−

Here, we are inserting records into above-created table −

INSERT INTO EMPLOYEES VALUES
(1, 'John', 'Doe', '2020-01-01', 'Sales'),
(2, 'Jane', 'Doe', '2020-02-01', 'Marketing'),
(3, 'Bob', 'Smith', '2020-03-01', 'Engineering'),
(4, 'Alice', 'Johnson', '2020-04-01', 'Research'),
(5, 'Mike', 'Brown', '2020-05-01', 'Operations');

以下是获取到的 EMPLOYEES 表−

Following is the EMPLOYEES table obtained −

我们可以显示 EMPLOYEES 表的分区状态，以使用以下查询查看数据如何分布在各分区中−

We can display the partition status of the EMPLOYEES 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='EMPLOYEES';

该查询将根据部门值显示分区和每个分区中的行数−

It will display the partitions and the number of rows in each partition based on the department values −