数据库分片 database-sharding

数据库分片 概述 实现跨多个数据库服务器的水平数据分区。涵盖分片策略、一致性哈希、分片键选择和跨分片查询模式。

何时使用

• 数据库大小超出单服务器容量
• 读写吞吐量需要水平扩展
• 地理数据分布需求
• 多租户数据隔离
• 通过分布式架构优化成本
• 在数据库实例间进行负载均衡

分片策略

1. 基于范围的分片

PostgreSQL - 基于范围的分片实现：

-- 定义分片范围
-- 分片 0: user_id 0-999999
-- 分片 1: user_id 1000000-1999999
-- 分片 2: user_id 2000000-2999999

CREATE TABLE users_shard_0 (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id BIGINT NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  CONSTRAINT shard_0_range CHECK (user_id BETWEEN 0 AND 999999)
);

CREATE TABLE users_shard_1 (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id BIGINT NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  CONSTRAINT shard_1_range CHECK (user_id BETWEEN 1000000 AND 1999999)
);

-- 函数确定分片
CREATE OR REPLACE FUNCTION get_shard_id(p_user_id BIGINT)
RETURNS INT AS $$
BEGIN
  RETURN (p_user_id / 1000000)::INT;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

2. 基于哈希的分片

PostgreSQL - 一致性哈希分片：

-- 基于哈希的分布在4个分片上
CREATE OR REPLACE FUNCTION get_hash_shard(
  p_key VARCHAR,
  p_shard_count INT DEFAULT 4
) RETURNS INT AS $$
DECLARE
  hash_val BIGINT;
BEGIN
  -- 使用PostgreSQL的hashtext函数
  hash_val := abs(hashtext(p_key)::BIGINT);
  RETURN (hash_val % p_shard_count)::INT;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 创建分片表
CREATE TABLE users_shard_0 (
  id UUID PRIMARY KEY,
  user_key VARCHAR(255) NOT NULL,
  email VARCHAR(255),
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE users_shard_1 AS TABLE users_shard_0;
CREATE TABLE users_shard_2 AS TABLE users_shard_0;
CREATE TABLE users_shard_3 AS TABLE users_shard_0;

-- 插入带分片路由
INSERT INTO users_shard_0
SELECT * FROM users WHERE get_hash_shard(user_key, 4) = 0;

INSERT INTO users_shard_1
SELECT * FROM users WHERE get_hash_shard(user_key, 4) = 1;

一致性哈希以提高弹性：

-- 虚拟节点以更好地负载分布
CREATE TABLE shard_mapping (
  virtual_node_id INT PRIMARY KEY,
  actual_shard_id INT NOT NULL,
  shard_host VARCHAR(255),
  shard_port INT
);

INSERT INTO shard_mapping VALUES
(0, 0, 'shard0.example.com', 5432),
(1, 1, 'shard1.example.com', 5432),
(2, 2, 'shard2.example.com', 5432),
(3, 3, 'shard3.example.com', 5432),
(4, 1, 'shard1.example.com', 5432),  -- 虚拟节点
(5, 2, 'shard2.example.com', 5432);

-- 查找键的分片
CREATE OR REPLACE FUNCTION find_shard_host(p_key VARCHAR)
RETURNS TABLE (shard_id INT, host VARCHAR, port INT) AS $$
BEGIN
  RETURN QUERY
  SELECT sm.actual_shard_id, sm.shard_host, sm.shard_port
  FROM shard_mapping sm
  WHERE sm.virtual_node_id = (
    abs(hashtext(p_key)::BIGINT) %
    (SELECT COUNT(*) FROM shard_mapping)
  )::INT
  LIMIT 1;
END;
$$ LANGUAGE plpgsql;

3. 基于目录的分片

PostgreSQL - 查找表方法：

-- 创建分片目录
CREATE TABLE shard_directory (
  shard_key VARCHAR(255) PRIMARY KEY,
  shard_id INT NOT NULL,
  shard_host VARCHAR(255) NOT NULL,
  shard_port INT DEFAULT 5432,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_shard_id ON shard_directory(shard_id);

-- 插入分片配置
INSERT INTO shard_directory (shard_key, shard_id, shard_host) VALUES
('user_1', 0, 'shard0.example.com'),
('user_2', 1, 'shard1.example.com'),
('tenant_a', 2, 'shard2.example.com'),
('tenant_b', 3, 'shard3.example.com');

-- 从目录中获取分片信息的函数
CREATE OR REPLACE FUNCTION get_shard_info(p_key VARCHAR)
RETURNS TABLE (shard_id INT, host VARCHAR, port INT) AS $$
BEGIN
  RETURN QUERY
  SELECT sd.shard_id, sd.shard_host, sd.shard_port
  FROM shard_directory sd
  WHERE sd.shard_key = p_key;
END;
$$ LANGUAGE plpgsql;

分片键选择 好的分片键特性：

-- 示例：基于用户的分片
-- 分片键：user_id
-- 好因为：经常在查询中使用，稳定值

-- 所有查询都包含分片键
SELECT * FROM users WHERE user_id = 123;
SELECT * FROM orders WHERE user_id = 123 AND order_id = 456;

-- 多租户系统的复合分片键
-- 分片键：(tenant_id, user_id)
SELECT * FROM users
WHERE tenant_id = 'tenant_a' AND user_id = 123;

-- 分片键上的索引以提高性能
CREATE INDEX idx_users_shard_key ON users_shard_0(user_id);
CREATE INDEX idx_orders_shard_key ON orders_shard_0(user_id, order_id);

跨分片操作 PostgreSQL - 分布式查询模式：

-- 为每个分片创建外部服务器连接
CREATE EXTENSION postgres_fdw;

CREATE SERVER shard_0
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'shard0.example.com', dbname 'mydb');

CREATE SERVER shard_1
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'shard1.example.com', dbname 'mydb');

-- 创建外部表
CREATE FOREIGN TABLE users_remote_0 (
  id UUID, user_id BIGINT, email VARCHAR, created_at TIMESTAMP
) SERVER shard_0 OPTIONS (table_name 'users');

CREATE FOREIGN TABLE users_remote_1 (
  id UUID, user_id BIGINT, email VARCHAR, created_at TIMESTAMP
) SERVER shard_1 OPTIONS (table_name 'users');

-- 跨分片的分布式查询
SELECT * FROM users_remote_0
UNION ALL
SELECT * FROM users_remote_1
WHERE created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 100;

跨分片聚合：

-- 从所有分片聚合数据
CREATE OR REPLACE FUNCTION aggregate_user_orders()
RETURNS TABLE (user_id BIGINT, total_orders BIGINT, total_spent DECIMAL) AS $$
BEGIN
  RETURN QUERY
  SELECT
    so.user_id,
    COUNT(so.id)::BIGINT,
    SUM(so.total)::DECIMAL
  FROM (
    SELECT user_id, id, total FROM orders_shard_0
    UNION ALL
    SELECT user_id, id, total FROM orders_shard_1
    UNION ALL
    SELECT user_id, id, total FROM orders_shard_2
    UNION ALL
    SELECT user_id, id, total FROM orders_shard_3
  ) so
  GROUP BY so.user_id;
END;
$$ LANGUAGE plpgsql;

分片重平衡 PostgreSQL - 添加新分片：

-- 1. 创建新的分片表
CREATE TABLE users_shard_4 (
  id UUID PRIMARY KEY,
  user_id BIGINT NOT NULL,
  email VARCHAR(255),
  created_at TIMESTAMP
);

-- 2. 使用哈希函数迁移数据
INSERT INTO users_shard_4
SELECT * FROM (
  SELECT * FROM users_shard_0 UNION ALL
  SELECT * FROM users_shard_1 UNION ALL
  SELECT * FROM users_shard_2 UNION ALL
  SELECT * FROM users_shard_3
) all_users
WHERE get_hash_shard(user_id::VARCHAR, 5) = 4;

-- 3. 从旧分片中删除迁移的数据
DELETE FROM users_shard_0 WHERE get_hash_shard(user_id::VARCHAR, 5) = 4;
DELETE FROM users_shard_1 WHERE get_hash_shard(user_id::VARCHAR, 5) = 4;
DELETE FROM users_shard_2 WHERE get_hash_shard(user_id::VARCHAR, 5) = 4;
DELETE FROM users_shard_3 WHERE get_hash_shard(user_id::VARCHAR, 5) = 4;

-- 4. 在配置中更新分片计数
-- 更新应用程序配置：shard_count = 5

分片监控 PostgreSQL - 监控分片平衡：

-- 检查分片分布
CREATE OR REPLACE FUNCTION monitor_shard_distribution()
RETURNS TABLE (shard_id INT, record_count BIGINT, avg_records BIGINT) AS $$
DECLARE
  total_records BIGINT;
BEGIN
  SELECT COUNT(*) INTO total_records FROM (
    SELECT 0 as shard_id, COUNT(*) FROM users_shard_0
    UNION ALL
    SELECT 1, COUNT(*) FROM users_shard_1
    UNION ALL
    SELECT 2, COUNT(*) FROM users_shard_2
    UNION ALL
    SELECT 3, COUNT(*) FROM users_shard_3
  ) counts;

  RETURN QUERY
  SELECT * FROM (
    SELECT 0::INT, COUNT(*)::BIGINT, (total_records / 4)::BIGINT FROM users_shard_0
    UNION ALL
    SELECT 1, COUNT(*), (total_records / 4) FROM users_shard_1
    UNION ALL
    SELECT 2, COUNT(*), (total_records / 4) FROM users_shard_2
    UNION ALL
    SELECT 3, COUNT(*), (total_records / 4) FROM users_shard_3
  );
END;
$$ LANGUAGE plpgsql;

SELECT * FROM monitor_shard_distribution();

监控分片访问：

-- 跟踪哪个分片被访问
CREATE TABLE shard_access_log (
  shard_id INT,
  operation VARCHAR(10),
  record_count INT,
  duration_ms INT,
  accessed_at TIMESTAMP DEFAULT NOW()
);

-- 日志记录分片访问模式
SELECT shard_id, operation, COUNT(*) as access_count
FROM shard_access_log
WHERE accessed_at > NOW() - INTERVAL '1 hour'
GROUP BY shard_id, operation
ORDER BY shard_id;

常见分片错误 ❌ 不要使用不稳定的值作为分片键 ❌ 不要忘记在所有查询中包含分片键 ❌ 不要忽视跨分片查询的复杂性 ❌ 不要忽略不均匀的分片分布 ❌ 不要错过分布式事务的挑战 ✅ 插入时验证分片键 ✅ 定期监控分片平衡 ✅ 计划分片重平衡 ✅ 彻底测试跨分片操作 ✅ 清晰地记录分片映射

分片策略比较

策略	优点	缺点
基于范围的	简单易实现	范围内的热点
基于哈希的	均匀分布	复杂的重平衡
基于目录的	灵活，动态	额外的查找开销

资源

• PostgreSQL 分区
• Citus - PostgreSQL 分片
• Vitess - MySQL 中间件
• ShardingSphere - 分片框架