MySQL的`Partitioning`：在`Range`与`List`分区中的应用

MySQL 分区：Range 与 List 分区深度解析与应用

大家好，今天我们来深入探讨 MySQL 分区技术中的两种重要类型：Range 分区和 List 分区。分区是 MySQL 中一种强大的性能优化和数据管理手段，它可以将一个大的表在物理上分割成多个更小的、更容易管理的分区。这对于处理大数据量、提高查询效率、简化维护操作都非常有帮助。

什么是 MySQL 分区？

简单来说，MySQL 分区就是将一个表的数据分散存储到多个物理文件中，但从逻辑上来看，它仍然是一个表。分区可以显著提升查询性能，尤其是在查询涉及特定数据范围或特定值时。此外，分区还能简化数据管理任务，例如数据备份、恢复和清理。

Range 分区

Range 分区基于列值的范围进行数据分割。每个分区都定义了一个值的范围，属于该范围的数据会被存储到对应的分区中。Range 分区最适合于处理具有时间序列数据、数值范围或字母顺序的数据。

Range 分区的语法

CREATE TABLE 表名 (
    列名 数据类型,
    ...
)
PARTITION BY RANGE (列名) (
    PARTITION 分区名 VALUES LESS THAN (上限值),
    PARTITION 分区名 VALUES LESS THAN (上限值),
    ...
    PARTITION 分区名 VALUES LESS THAN MAXVALUE
);

• PARTITION BY RANGE (列名)：指定使用 Range 分区，并指定用于分区的列。
• PARTITION 分区名 VALUES LESS THAN (上限值)：定义一个分区，指定该分区存储所有小于 上限值 的数据。
• PARTITION 分区名 VALUES LESS THAN MAXVALUE：定义一个分区，用于存储所有大于等于最后一个明确定义的上限值的数据。MAXVALUE 是一个特殊的值，表示最大可能的值。

Range 分区的例子

假设我们有一个 sales 表，用于存储销售记录，其中包含 sale_date 列，类型为 DATE。我们可以按照年份对数据进行 Range 分区：

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION sales_2020 VALUES LESS THAN (2021),
    PARTITION sales_2021 VALUES LESS THAN (2022),
    PARTITION sales_2022 VALUES LESS THAN (2023),
    PARTITION sales_future VALUES LESS THAN MAXVALUE
);

在这个例子中，我们创建了四个分区：

• sales_2020：存储 sale_date 年份为 2020 的数据。
• sales_2021：存储 sale_date 年份为 2021 的数据。
• sales_2022：存储 sale_date 年份为 2022 的数据。
• sales_future：存储 sale_date 年份大于等于 2023 的数据。

Range 分区的优点

• 查询优化： 可以根据查询条件只扫描相关的分区，提高查询效率。例如，如果查询 2021 年的销售数据，MySQL 只会扫描 sales_2021 分区。
• 数据维护： 可以单独对每个分区进行备份、恢复和清理，简化了数据管理任务。例如，如果需要删除 2020 年的销售数据，可以直接删除 sales_2020 分区。
• 数据归档： 可以将旧数据归档到单独的分区，并将其存储在成本更低的存储介质上。

Range 分区的缺点

• 范围定义： 需要预先定义好每个分区的范围，如果数据超出预定义的范围，可能会导致数据插入失败。
• 维护成本： 随着数据的增长，可能需要定期维护分区，例如添加新的分区或合并旧的分区。
• 热点问题： 如果某些范围的数据量远大于其他范围，可能会导致某些分区成为热点，影响性能。

Range 分区的应用场景

• 时间序列数据： 例如日志数据、交易数据、传感器数据等。
• 数值范围数据： 例如订单金额、用户年龄、商品价格等。
• 字母顺序数据： 例如用户姓名、商品名称等。

List 分区

List 分区基于列值的列表进行数据分割。每个分区都定义了一个值的列表，属于该列表的数据会被存储到对应的分区中。List 分区最适合于处理具有离散值的数据，例如国家、地区、产品类型等。

List 分区的语法

CREATE TABLE 表名 (
    列名 数据类型,
    ...
)
PARTITION BY LIST (列名) (
    PARTITION 分区名 VALUES IN (值1, 值2, ...),
    PARTITION 分区名 VALUES IN (值1, 值2, ...),
    ...
    PARTITION 分区名 VALUES IN (值1, 值2, ...)
);

• PARTITION BY LIST (列名)：指定使用 List 分区，并指定用于分区的列。
• PARTITION 分区名 VALUES IN (值1, 值2, ...)：定义一个分区，指定该分区存储所有列值为 值1、值2、… 的数据。

List 分区的例子

假设我们有一个 customers 表，用于存储客户信息，其中包含 country 列，类型为 VARCHAR。我们可以按照国家对数据进行 List 分区：

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255),
    country VARCHAR(255)
)
PARTITION BY LIST (country) (
    PARTITION customers_usa VALUES IN ('USA', 'United States'),
    PARTITION customers_china VALUES IN ('China', '中国'),
    PARTITION customers_uk VALUES IN ('UK', 'United Kingdom'),
    PARTITION customers_other VALUES IN (DEFAULT)
);

在这个例子中，我们创建了四个分区：

• customers_usa：存储 country 为 USA 或 United States 的数据。
• customers_china：存储 country 为 China 或 中国 的数据。
• customers_uk：存储 country 为 UK 或 United Kingdom 的数据。
• customers_other：存储 country 为 其他值的数据。这里使用了 DEFAULT，表示如果插入的数据 country 列的值没有在前面的分区中定义，那么数据会被存储到这个默认分区。 需要注意的是，如果使用了 DEFAULT，就必须显式地将 NULL 值放入某个分区，否则插入 country 为 NULL 的数据会报错。例如，如果我们希望将 NULL 值的 country 也放入 customers_other 分区，可以修改 customers_other 分区的定义为：

PARTITION customers_other VALUES IN (DEFAULT, NULL);

List 分区的优点

• 查询优化： 可以根据查询条件只扫描相关的分区，提高查询效率。例如，如果查询来自 China 的客户，MySQL 只会扫描 customers_china 分区。
• 数据维护： 可以单独对每个分区进行备份、恢复和清理，简化了数据管理任务。例如，如果需要删除来自 USA 的客户数据，可以直接删除 customers_usa 分区。
• 数据组织： 可以根据业务逻辑将数据组织到不同的分区中，提高数据的可读性和可维护性。

List 分区的缺点

• 列表定义： 需要预先定义好每个分区的列表，如果数据超出预定义的列表，可能会导致数据插入失败。
• 维护成本： 随着数据的增长，可能需要定期维护分区，例如添加新的分区或修改现有分区的列表。
• NULL 值处理： 需要显式地将 NULL 值放入某个分区，否则插入 NULL 值的数据会报错。

List 分区的应用场景

• 国家/地区数据： 例如客户的国家、商品的产地等。
• 产品类型数据： 例如商品的类别、服务的类型等。
• 状态数据： 例如订单的状态、用户的状态等。

Range Columns 与 List Columns 分区

除了基于单个列进行 Range 和 List 分区外，MySQL 还支持基于多个列进行分区，分别称为 Range Columns 分区和 List Columns 分区。

Range Columns 分区

Range Columns 分区允许你基于多个列的组合值范围进行数据分割。例如，你可以基于年份和月份的组合值进行分区。

Range Columns 分区的语法

CREATE TABLE 表名 (
    列名1 数据类型,
    列名2 数据类型,
    ...
)
PARTITION BY RANGE COLUMNS (列名1, 列名2, ...) (
    PARTITION 分区名 VALUES LESS THAN (值1, 值2, ...),
    PARTITION 分区名 VALUES LESS THAN (值1, 值2, ...),
    ...
    PARTITION 分区名 VALUES LESS THAN (MAXVALUE, MAXVALUE, ...)
);

• PARTITION BY RANGE COLUMNS (列名1, 列名2, ...)：指定使用 Range Columns 分区，并指定用于分区的列。
• PARTITION 分区名 VALUES LESS THAN (值1, 值2, ...)：定义一个分区，指定该分区存储所有列值组合小于 (值1, 值2, ...) 的数据。列值组合的比较是按照从左到右的顺序进行的。

Range Columns 分区的例子

假设我们有一个 orders 表，用于存储订单信息，其中包含 order_year 和 order_month 列，类型都为 INT。我们可以基于年份和月份的组合值对数据进行 Range Columns 分区：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_year INT,
    order_month INT,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE COLUMNS (order_year, order_month) (
    PARTITION orders_2021_01 VALUES LESS THAN (2021, 2),
    PARTITION orders_2021_02 VALUES LESS THAN (2021, 3),
    PARTITION orders_2021_03 VALUES LESS THAN (2021, 4),
    PARTITION orders_2021_04 VALUES LESS THAN (2021, 5),
    PARTITION orders_2021_05 VALUES LESS THAN (2021, 6),
    PARTITION orders_2021_06 VALUES LESS THAN (2021, 7),
    PARTITION orders_2021_07 VALUES LESS THAN (2021, 8),
    PARTITION orders_2021_08 VALUES LESS THAN (2021, 9),
    PARTITION orders_2021_09 VALUES LESS THAN (2021, 10),
    PARTITION orders_2021_10 VALUES LESS THAN (2021, 11),
    PARTITION orders_2021_11 VALUES LESS THAN (2021, 12),
    PARTITION orders_2021_12 VALUES LESS THAN (2022, 1),
    PARTITION orders_2022_01 VALUES LESS THAN (2022, 2),
    PARTITION orders_future VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

List Columns 分区

List Columns 分区允许你基于多个列的组合值列表进行数据分割。例如，你可以基于国家和地区的组合值进行分区。

List Columns 分区的语法

CREATE TABLE 表名 (
    列名1 数据类型,
    列名2 数据类型,
    ...
)
PARTITION BY LIST COLUMNS (列名1, 列名2, ...) (
    PARTITION 分区名 VALUES IN ((值1, 值2, ...), (值1, 值2, ...), ...),
    PARTITION 分区名 VALUES IN ((值1, 值2, ...), (值1, 值2, ...), ...),
    ...
    PARTITION 分区名 VALUES IN ((值1, 值2, ...), (值1, 值2, ...), ...)
);

• PARTITION BY LIST COLUMNS (列名1, 列名2, ...)：指定使用 List Columns 分区，并指定用于分区的列。
• PARTITION 分区名 VALUES IN ((值1, 值2, ...), (值1, 值2, ...), ...)：定义一个分区，指定该分区存储所有列值组合为 (值1, 值2, ...) 的数据。

List Columns 分区的例子

假设我们有一个 locations 表，用于存储位置信息，其中包含 country 和 city 列，类型都为 VARCHAR。我们可以基于国家和城市的组合值对数据进行 List Columns 分区：

CREATE TABLE locations (
    location_id INT PRIMARY KEY,
    country VARCHAR(255),
    city VARCHAR(255)
)
PARTITION BY LIST COLUMNS (country, city) (
    PARTITION locations_usa_ny VALUES IN (('USA', 'New York'), ('USA', 'Buffalo')),
    PARTITION locations_china_bj VALUES IN (('China', 'Beijing'), ('China', 'Shanghai')),
    PARTITION locations_uk_ld VALUES IN (('UK', 'London'), ('UK', 'Manchester')),
    PARTITION locations_other VALUES IN (DEFAULT)
);

如何选择 Range 分区还是 List 分区？

选择 Range 分区还是 List 分区取决于数据的特点和业务需求。

特性	Range 分区	List 分区
分区依据	列值的范围	列值的列表
适用数据类型	时间序列数据、数值范围数据、字母顺序数据	离散值数据，例如国家、地区、产品类型
优点	查询优化、数据维护、数据归档	查询优化、数据维护、数据组织
缺点	需要预先定义范围、维护成本、可能存在热点问题	需要预先定义列表、维护成本、需要处理 NULL 值
应用场景	日志数据、交易数据、订单金额、用户年龄、商品价格等	客户的国家、商品的产地、商品的类别、服务的类型、订单的状态等

一般来说，如果数据具有连续性，例如时间序列数据或数值范围数据，那么 Range 分区更适合。如果数据具有离散性，例如国家或产品类型，那么 List 分区更适合。

分区表的维护

分区表需要定期进行维护，以保证其性能和数据的完整性。常见的维护操作包括：

• 添加新的分区： 随着数据的增长，可能需要添加新的分区来存储新的数据。
• 删除旧的分区： 可以删除旧的分区来清理过时的数据。
• 合并分区： 可以将多个小的分区合并成一个大的分区，以减少分区的数量。
• 拆分分区： 可以将一个大的分区拆分成多个小的分区，以提高查询效率。
• 重建分区： 可以重建分区来优化数据的存储结构。

可以使用 ALTER TABLE 语句来执行这些维护操作。例如，要添加一个新的分区，可以使用以下语句：

ALTER TABLE sales ADD PARTITION (PARTITION sales_2023 VALUES LESS THAN (2024));

要删除一个旧的分区，可以使用以下语句：

ALTER TABLE sales DROP PARTITION sales_2020;

代码示例：一个完整的 Range 分区和 List 分区示例

-- 创建 Range 分区表
CREATE TABLE orders_range (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION orders_2020 VALUES LESS THAN (2021),
    PARTITION orders_2021 VALUES LESS THAN (2022),
    PARTITION orders_2022 VALUES LESS THAN (2023),
    PARTITION orders_future VALUES LESS THAN MAXVALUE
);

-- 插入数据到 Range 分区表
INSERT INTO orders_range (order_id, customer_id, order_date, amount) VALUES
(1, 101, '2020-12-31', 100.00),
(2, 102, '2021-01-15', 200.00),
(3, 103, '2021-12-31', 300.00),
(4, 104, '2022-06-30', 400.00),
(5, 105, '2023-01-01', 500.00);

-- 创建 List 分区表
CREATE TABLE products_list (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    category VARCHAR(255),
    price DECIMAL(10, 2)
)
PARTITION BY LIST (category) (
    PARTITION products_electronics VALUES IN ('Electronics', 'Mobile'),
    PARTITION products_clothing VALUES IN ('Clothing', 'Shoes'),
    PARTITION products_books VALUES IN ('Books'),
    PARTITION products_other VALUES IN (DEFAULT)
);

-- 插入数据到 List 分区表
INSERT INTO products_list (product_id, product_name, category, price) VALUES
(1, 'Smartphone', 'Electronics', 800.00),
(2, 'T-Shirt', 'Clothing', 25.00),
(3, 'Database Book', 'Books', 50.00),
(4, 'Laptop', 'Electronics', 1200.00),
(5, 'Running Shoes', 'Shoes', 80.00),
(6, 'Coffee Maker', 'Appliances', 150.00);

-- 查询 Range 分区表
SELECT * FROM orders_range PARTITION (orders_2021);

-- 查询 List 分区表
SELECT * FROM products_list PARTITION (products_electronics);

-- 添加 Range 分区
ALTER TABLE orders_range ADD PARTITION (PARTITION orders_2023 VALUES LESS THAN (2024));

-- 删除 List 分区
ALTER TABLE products_list DROP PARTITION products_other;

-- 创建 Range Columns 分区表
CREATE TABLE sales_range_columns (
    sale_id INT PRIMARY KEY,
    sale_year INT,
    sale_month INT,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE COLUMNS (sale_year, sale_month) (
    PARTITION sales_2022_01 VALUES LESS THAN (2022, 2),
    PARTITION sales_2022_02 VALUES LESS THAN (2022, 3),
    PARTITION sales_2022_03 VALUES LESS THAN (2022, 4),
    PARTITION sales_future VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

INSERT INTO sales_range_columns (sale_id, sale_year, sale_month, amount) VALUES
(1, 2022, 1, 100.00),
(2, 2022, 2, 200.00),
(3, 2022, 3, 300.00),
(4, 2022, 4, 400.00);

-- 创建 List Columns 分区表
CREATE TABLE users_list_columns (
    user_id INT PRIMARY KEY,
    country VARCHAR(255),
    city VARCHAR(255),
    age INT
)
PARTITION BY LIST COLUMNS (country, city) (
    PARTITION users_usa_ny VALUES IN (('USA', 'New York'), ('USA', 'Los Angeles')),
    PARTITION users_china_bj VALUES IN (('China', 'Beijing'), ('China', 'Shanghai')),
    PARTITION users_other VALUES IN (DEFAULT)
);

INSERT INTO users_list_columns (user_id, country, city, age) VALUES
(1, 'USA', 'New York', 30),
(2, 'China', 'Beijing', 25),
(3, 'USA', 'Chicago', 40),
(4, 'China', 'Guangzhou', 35);

分区技术的价值所在

MySQL 分区技术，尤其是 Range 和 List 分区，通过将表数据分割成更小、更易管理的部分，显著提高了查询效率、简化了维护操作，并优化了数据组织。理解和掌握这些分区策略对于处理大数据量、构建高性能的 MySQL 应用至关重要。