当前位置：首页 > news >正文

数据库分区与分表详解

news 2025/5/13 6:59:45

数据库分区与分表详解

一、数据库分区 (Partitioning)

分区是将一个大表在物理上分割成多个更小的、更易管理的部分，但在逻辑上仍然表现为一个表。

1. 分区类型及SQL实现

(1) RANGE 分区 (按范围)

CREATE TABLE sales (id INT NOT NULL,sale_date DATE NOT NULL,amount DECIMAL(10,2),region VARCHAR(50)
) PARTITION BY RANGE (YEAR(sale_date)) (PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022),PARTITION p2022 VALUES LESS THAN (2023),PARTITION pmax VALUES LESS THAN MAXVALUE
);

(2) LIST 分区 (按列表)

CREATE TABLE employees (id INT NOT NULL,name VARCHAR(50),store_id INT
) PARTITION BY LIST (store_id) (PARTITION pNorth VALUES IN (1, 3, 5),PARTITION pSouth VALUES IN (2, 4, 6),PARTITION pOther VALUES IN (DEFAULT)
);

(3) HASH 分区 (哈希分布)

CREATE TABLE users (id INT NOT NULL,username VARCHAR(50),created_at DATETIME
) PARTITION BY HASH (id)
PARTITIONS 4;

(4) KEY 分区 (类似HASH但MySQL处理键)

CREATE TABLE logs (id INT NOT NULL,log_time DATETIME,content TEXT
) PARTITION BY KEY (id)
PARTITIONS 5;

(5) COLUMNS 分区 (支持多列)

CREATE TABLE rc3 (a INT,b INT
) PARTITION BY RANGE COLUMNS(a,b) (PARTITION p0 VALUES LESS THAN (10,10),PARTITION p1 VALUES LESS THAN (20,20),PARTITION p2 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

2. 分区管理操作

-- 添加分区
ALTER TABLE sales ADD PARTITION (PARTITION p2023 VALUES LESS THAN (2024)
);-- 删除分区
ALTER TABLE sales DROP PARTITION p2020;-- 重组分区
ALTER TABLE sales REORGANIZE PARTITION pmax INTO (PARTITION p2023 VALUES LESS THAN (2024),PARTITION pmax VALUES LESS THAN MAXVALUE
);-- 查看分区信息
SELECT * FROM information_schema.PARTITIONS 
WHERE TABLE_NAME = 'sales';

二、数据库分表 (Sharding)

分表是将数据分散到多个物理表中，每个表都是独立的，需要应用层或中间件管理。

1. 分表实现方式

(1) 应用层分表

-- 用户表按ID范围分表
CREATE TABLE users_0 (id BIGINT PRIMARY KEY,name VARCHAR(100),CHECK (id >= 0 AND id < 1000000)
);CREATE TABLE users_1 (id BIGINT PRIMARY KEY,name VARCHAR(100),CHECK (id >= 1000000 AND id < 2000000)
);-- 查询时需要指定表
SELECT * FROM users_0 WHERE id = 123;
SELECT * FROM users_1 WHERE id = 1500000;

(2) 使用视图统一访问

CREATE VIEW users AS
SELECT * FROM users_0
UNION ALL
SELECT * FROM users_1;

(3) 使用存储过程路由

DELIMITER //
CREATE PROCEDURE insert_user(IN p_id INT, IN p_name VARCHAR(100))
BEGINDECLARE table_suffix INT;SET table_suffix = FLOOR(p_id / 1000000);SET @sql = CONCAT('INSERT INTO users_', table_suffix, ' VALUES (?, ?)');SET @param1 = p_id;SET @param2 = p_name;PREPARE stmt FROM @sql;EXECUTE stmt USING @param1, @param2;DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

2. 分表查询策略

(1) 并行查询各分表

-- 查询所有分表中符合条件的记录
(SELECT * FROM users_0 WHERE name LIKE '张%')
UNION ALL
(SELECT * FROM users_1 WHERE name LIKE '张%');

(2) 按分片键精准查询

-- 根据ID计算应该查询哪个表
SET @table_num = FLOOR(1500000 / 1000000);
SET @sql = CONCAT('SELECT * FROM users_', @table_num, ' WHERE id = 1500000');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

三、分区与分表对比

特性	分区	分表
管理方式	数据库自动管理	需要应用层或中间件管理
透明性	对应用完全透明	应用需要知道分表规则
跨分区查询	数据库自动处理	需要手动UNION或分布式查询
维护成本	低	高
扩展性	有限(同一数据库实例)	强(可跨服务器)
适用场景	单机大表	超大规模数据、分布式系统

四、最佳实践建议

分区适用场景：
- 单表数据量超过千万级
- 有明显的范围或列表查询特征
- 需要定期删除历史数据(直接删除分区效率高)
分表适用场景：
- 单机性能无法满足需求
- 需要水平扩展到多台服务器
- 不同分表可以有不同结构

混合使用方案：

-- 先分表再分区
CREATE TABLE orders_0 (id BIGINT,user_id INT,order_date DATE,amount DECIMAL(10,2)
) PARTITION BY RANGE (TO_DAYS(order_date)) (PARTITION p202201 VALUES LESS THAN (TO_DAYS('2022-02-01')),PARTITION p202202 VALUES LESS THAN (TO_DAYS('2022-03-01')),PARTITION pFuture VALUES LESS THAN MAXVALUE
);