Mysql 简明教程

MySQL - Vertical Partitioning

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

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

The MySQL Vertical Partitioning

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

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

  1. RANGE Columns Partitioning

  2. LIST Columns Partitioning

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

Range Columns Partitioning

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

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

Example

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

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

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

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

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

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

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

Displaying Partitions −

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

SELECT * FROM inventory PARTITION (P_high_stock);

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

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

List Columns Partitioning

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

Example

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

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

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

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

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

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

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