MySQL - 垂直分区

• MySQL 垂直分区
• 范围列分区
• 列表列分区

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

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

MySQL 垂直分区

MySQL 垂直分区根据列(而不是行)将表划分为多个表。

MySQL 中有两种主要的垂直分区类型，每种类型都有特定的用途 -

• 范围列分区
• 列表列分区

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

范围列分区

MySQL 范围列分区使用一个或多个列作为分区键，根据定义的列值范围将数据划分到不同的分区中。

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

示例

在下面的查询中，我们创建一个名为 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';

您将在下面的输出中看到，相应的列已根据定义的范围值分配到各自的分区 -

显示分区 -

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

SELECT * FROM inventory PARTITION (P_high_stock);

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

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

列表列分区

MySQL 列表列分区使用一个或多个列作为分区键，并根据这些列中的特定值将记录分配到分区。当您希望根据离散值或类别将数据分组到分区时，此方法非常方便。

示例

让我们创建一个名为"EMPLOYEES"的表，并使用基于"department"列的列表列分区对其进行分区 -

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

它将根据部门值显示分区以及每个分区中的行数 -