SQL数据库分区表原理是什么？range、hash、list分区实践怎么操作

SQL数据库分区表是通过特定规则将一张大表的数据拆分到多个物理存储单元的技术，数据库在查询时会根据分区规则自动定位到对应的存储单元，避免全表扫描，从而提升查询性能，同时也方便后续的数据归档、删除等维护操作。

分区表核心原理

分区表的逻辑表现仍然是一张完整的表，用户查询时不需要额外指定分区信息，数据库优化器会根据查询条件中的分区键自动判断需要访问哪些分区。分区键是创建分区时指定的字段，所有分区规则都基于这个字段的取值来划分数据。每个分区对应独立的物理存储文件，不同分区的数据互不干扰，当对某个分区执行删除、归档操作时，不会影响其他分区的正常使用。

range分区实践

range分区是按照分区键的取值范围来划分数据，适合按时间、数值范围存储的场景，比如按年份、订单金额区间拆分数据。

创建range分区表

以下示例创建一张订单表，按订单创建时间的年份做range分区：

-- 创建订单表，按创建时间年份分区
CREATE TABLE order_info (
    order_id INT PRIMARY KEY,
    order_amount DECIMAL(10,2),
    create_time DATE
) PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

插入数据验证分区

插入不同年份的订单数据，数据库会自动将数据存入对应的分区：

-- 插入2021年订单
INSERT INTO order_info VALUES (1, 199.99, '2021-05-10');
-- 插入2022年订单
INSERT INTO order_info VALUES (2, 299.99, '2022-08-15');
-- 插入2024年订单，会存入pmax分区
INSERT INTO order_info VALUES (3, 399.99, '2024-01-20');

查询分区数据

查询时可以直接按条件查询，数据库会自动定位到对应分区：

-- 查询2022年的订单，只会扫描p2022分区
SELECT * FROM order_info WHERE create_time >= '2022-01-01' AND create_time < '2023-01-01';

hash分区实践

hash分区是对分区键进行哈希计算，根据哈希结果将数据均匀分布到各个分区，适合分区键取值没有明显范围规律，需要均匀分散数据的场景。

创建hash分区表

以下示例创建用户表，按用户ID做hash分区，分为4个分区：

-- 创建用户表，按用户ID哈希分区，共4个分区
CREATE TABLE user_info (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(50),
    register_time DATE
) PARTITION BY HASH (user_id) PARTITIONS 4;

插入数据验证分区

插入用户数据后，数据库会自动计算哈希值分配分区：

INSERT INTO user_info VALUES (1, '张三', '2023-01-01');
INSERT INTO user_info VALUES (2, '李四', '2023-02-01');
INSERT INTO user_info VALUES (3, '王五', '2023-03-01');
INSERT INTO user_info VALUES (4, '赵六', '2023-04-01');

查看分区数据分布

可以通过系统表查看每个分区的数据量：

-- 查看用户表各分区的数据行数
SELECT PARTITION_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME = 'user_info';

list分区实践

list分区是按照分区键的离散取值列表来划分数据，适合分区键取值是固定枚举值的场景，比如按地区、状态分类存储数据。

创建list分区表

以下示例创建商品表，按商品所属地区做list分区：

-- 创建商品表，按所属地区枚举值分区
CREATE TABLE product_info (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    region VARCHAR(20)
) PARTITION BY LIST (region) (
    PARTITION p_east VALUES IN ('上海', '江苏', '浙江'),
    PARTITION p_south VALUES IN ('广东', '福建', '海南'),
    PARTITION p_other VALUES IN ('北京', '四川', '湖北')
);

插入数据验证分区

插入不同地区的商品数据，会自动匹配到对应的分区：

INSERT INTO product_info VALUES (1, '手机', '上海');
INSERT INTO product_info VALUES (2, '电脑', '广东');
INSERT INTO product_info VALUES (3, '平板', '北京');

查询指定分区数据

如果需要单独查询某个分区的数据，可以指定分区名：

-- 查询东部地区的商品，直接访问p_east分区
SELECT * FROM product_info PARTITION (p_east);

三种分区类型对比

不同分区类型适用场景不同，以下是三者的核心差异对比：

注意事项

• 分区键必须是主键或者唯一索引的一部分，否则创建分区表会失败。
• range分区的范围需要连续且不重叠，最后一个分区可以用MAXVALUE承接超出范围的数据。
• hash分区的分区数建议设置为2的幂次，方便后续扩展分区。
• list分区的枚举值不能重复，插入不在枚举列表中的数据会报错，除非有默认分区承接。

SQL分区表range分区hash分区list分区修改时间：2026-07-17 12:45:36