# 分片规划
– 数据量估算：根据业务需求，估算数据量的增长趋势
– 分片策略：选择合适的分片策略，如范围分片、哈希分片、列表分片等
– 分片键选择：选择合适的分片键，确保数据分布均匀
– 分片数量：根据数据量和查询性能要求，确定分片数量
– 分片管理：选择合适的分片管理工具，如ProxySQL、ShardingSphere等
– 高可用设计：设计分片的高可用架构，确保数据安全和系统稳定
# 分片策略
– 范围分片：根据分片键的范围将数据分布到不同的分片
– 哈希分片：根据分片键的哈希值将数据分布到不同的分片
– 列表分片：根据分片键的具体值将数据分布到不同的分片
– 复合分片：结合多种分片策略，提高数据分布的均匀性
# 分片键选择
– 唯一性：分片键应该是唯一的，或者与其他字段组合后唯一
– 均匀性：分片键的值应该分布均匀，避免数据倾斜
– 查询频率：分片键应该是查询频率较高的字段，减少跨分片查询
– 稳定性：分片键的值应该相对稳定，避免频繁的数据迁移

# 分区规划
– 数据量估算：根据业务需求，估算数据量的增长趋势
– 分区策略：选择合适的分区策略，如范围分区、列表分区、哈希分区等
– 分区键选择：选择合适的分区键，确保数据分布均匀
– 分区数量：根据数据量和查询性能要求，确定分区数量
– 分区管理：制定分区的管理策略，如分区创建、合并、删除等
– 性能优化：优化分区的查询性能，如索引设计、查询优化等
# 分区策略
– 范围分区：根据分区键的范围将数据分布到不同的分区
– 列表分区：根据分区键的具体值将数据分布到不同的分区
– 哈希分区：根据分区键的哈希值将数据分布到不同的分区
– 复合分区：结合多种分区策略，提高数据分布的均匀性
# 分区键选择
– 查询频率：分区键应该是查询频率较高的字段，减少跨分区查询
– 数据分布：分区键的值应该分布均匀，避免分区大小差异过大
– 业务逻辑：分区键应该与业务逻辑相关，便于数据管理
– 性能考虑：分区键应该是能够提高查询性能的字段

# 实施策略
– 评估阶段：评估当前数据量和性能需求，确定是否需要分片或分区
– 设计阶段：设计分片或分区的架构，选择合适的策略和工具
– 测试阶段：在测试环境中进行分片或分区的测试，验证性能和可靠性
– 实施阶段：在生产环境中实施分片或分区，迁移数据
– 监控阶段：监控分片或分区的性能和状态，及时调整策略
# 数据迁移
– 在线迁移：在不影响业务的情况下，在线迁移数据
– 离线迁移：在业务低峰期，离线迁移数据
– 增量迁移：先迁移历史数据，再迁移增量数据
– 双写迁移：同时向旧系统和新系统写入数据，确保数据一致性
# 回滚策略
– 制定回滚计划，确保在实施过程中出现问题时能够快速回滚
– 保留旧系统的备份，以便在需要时恢复
– 进行充分的测试，减少回滚的可能性

# 水平分片实施
# 创建分片数据库
$ kubectl exec -it mysql-6d6f58987b-7f5f8 -n middleware — mysql -u root -p
Enter password: root123
mysql> CREATE DATABASE fgedudb_shard1;
mysql> CREATE DATABASE fgedudb_shard2;
mysql> CREATE DATABASE fgedudb_shard3;
# 创建分片表
mysql> USE fgedudb_shard1;
mysql> CREATE TABLE fgedu_users (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(100),
-> email VARCHAR(100),
-> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-> );
mysql> USE fgedudb_shard2;
mysql> CREATE TABLE fgedu_users (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(100),
-> email VARCHAR(100),
-> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-> );
mysql> USE fgedudb_shard3;
mysql> CREATE TABLE fgedu_users (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(100),
-> email VARCHAR(100),
-> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-> );
# 配置分片规则，风哥提示：。
# 使用ShardingSphere-Proxy作为分片代理
$ cat > shardingsphere-proxy.yaml << 'EOF' apiVersion: apps/v1 kind: Deployment metadata: name: shardingsphere-proxy namespace: middleware spec: replicas: 1 selector: matchLabels: app: shardingsphere-proxy template: metadata: labels: app: shardingsphere-proxy spec: containers: - name: shardingsphere-proxy image: apache/shardingsphere-proxy:5.1.0 ports: - containerPort: 3307 volumeMounts: - name: config mountPath: /opt/shardingsphere-proxy/conf volumes: - name: config configMap: name: shardingsphere-proxy-config --- apiVersion: v1 kind: ConfigMap metadata: name: shardingsphere-proxy-config namespace: middleware data: server.yaml: | rules: - !SHARDING tables: fgedu_users: actualDataNodes: ds_${0..2}.fgedu_users tableStrategy: standard: shardingColumn: id shardingAlgorithmName: mod shardingAlgorithms: mod: type: MOD props: sharding-count: 3 dataSources: ds_0: url: jdbc:mysql://mysql:3306/fgedudb_shard1?serverTimezone=UTC username: root password: root123 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 ds_1: url: jdbc:mysql://mysql:3306/fgedudb_shard2?serverTimezone=UTC username: root password: root123 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 ds_2: url: jdbc:mysql://mysql:3306/fgedudb_shard3?serverTimezone=UTC username: root password: root123 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 EOF $ kubectl apply -f shardingsphere-proxy.yaml # 创建ShardingSphere-Proxy服务 $ cat > shardingsphere-proxy-service.yaml << 'EOF' apiVersion: v1 kind: Service metadata: name: shardingsphere-proxy namespace: middleware spec: selector: app: shardingsphere-proxy ports: - port: 3307 targetPort: 3307 type: ClusterIP EOF $ kubectl apply -f shardingsphere-proxy-service.yaml # 检查ShardingSphere-Proxy部署状态 $ kubectl get pods -n middleware NAME READY STATUS RESTARTS AGE mysql-6d6f58987b-7f5f8 1/1 Running 0 10m shardingsphere-proxy-6d6f58987b-7f5f8 1/1 Running 0 5m

，学习交流加群风哥微信: itpux-com。
# 垂直分片实施
# 创建垂直分片数据库
$ kubectl exec -it mysql-6d6f58987b-7f5f8 -n middleware — mysql -u root -p
Enter password: root123
mysql> CREATE DATABASE fgedudb_user;
mysql> CREATE DATABASE fgedudb_order;
mysql> CREATE DATABASE fgedudb_product;
# 创建用户表
mysql> USE fgedudb_user;
mysql> CREATE TABLE fgedu_users (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(100),
-> email VARCHAR(100),
-> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-> );
# 创建订单表
mysql> USE fgedudb_order;
mysql> CREATE TABLE fgedu_orders (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> user_id INT,
-> product_id INT,
-> amount DECIMAL(10,2),
-> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-> );
# 创建产品表
mysql> USE fgedudb_product;
mysql> CREATE TABLE fgedu_products (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(100),
-> price DECIMAL(10,2),
-> stock INT,
-> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-> );
# 配置应用连接多个数据库
$ cat > app-deployment.yaml << 'EOF' apiVersion: apps/v1 kind: Deployment metadata: name: app namespace: middleware spec: replicas: 1 selector: matchLabels: app: app template: metadata: labels: app: app spec: containers: - name: app image: nginx ports: - containerPort: 80 env: - name: DB_USER_HOST value: mysql - name: DB_USER_PORT value: "3306" - name: DB_USER_NAME value: fgedudb_user - name: DB_USER_USER value: fgedu - name: DB_USER_PASSWORD value: fgedu123 - name: DB_ORDER_HOST value: mysql - name: DB_ORDER_PORT value: "3306" - name: DB_ORDER_NAME value: fgedudb_order - name: DB_ORDER_USER value: fgedu - name: DB_ORDER_PASSWORD value: fgedu123 - name: DB_PRODUCT_HOST value: mysql - name: DB_PRODUCT_PORT value: "3306" - name: DB_PRODUCT_NAME value: fgedudb_product - name: DB_PRODUCT_USER value: fgedu - name: DB_PRODUCT_PASSWORD value: fgedu123 EOF $ kubectl apply -f app-deployment.yaml # 检查应用部署状态 $ kubectl get pods -n middleware NAME READY STATUS RESTARTS AGE mysql-6d6f58987b-7f5f8 1/1 Running 0 15m shardingsphere-proxy-6d6f58987b-7f5f8 1/1 Running 0 10m app-6d6f58987b-7f5f8 1/1 Running 0 5m

# 数据库分区实施
# 创建分区表
$ kubectl exec -it mysql-6d6f58987b-7f5f8 -n middleware — mysql -u root -p，学习交流加群风哥QQ113257174。
Enter password: root123
mysql> USE fgedudb;
mysql> CREATE TABLE fgedu_orders (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> user_id INT,
-> product_id INT,
-> amount DECIMAL(10,2),
-> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-> INDEX idx_created_at (created_at)
-> ) PARTITION BY RANGE (YEAR(created_at)) (
-> PARTITION p2023 VALUES LESS THAN (2024),
-> PARTITION p2024 VALUES LESS THAN (2025),
-> PARTITION p2025 VALUES LESS THAN (2026),
-> PARTITION pfuture VALUES LESS THAN MAXVALUE
-> );
# 插入测试数据
mysql> INSERT INTO fgedu_orders (user_id, product_id, amount, created_at) VALUES
-> (1, 1, 100.00, ‘2023-01-01’),
-> (2, 2, 200.00, ‘2023-06-01’),
-> (3, 3, 300.00, ‘2024-01-01’),
-> (4, 4, 400.00, ‘2024-06-01’),
-> (5, 5, 500.00, ‘2025-01-01’);
# 查询分区数据
mysql> SELECT * FROM fgedu_orders PARTITION (p2023);
+—-+———+————+——–+———————+
| id | user_id | product_id | amount | created_at |
+—-+———+————+——–+———————+
| 1 | 1 | 1 | 100.00 | 2023-01-01 00:00:00 |
| 2 | 2 | 2 | 200.00 | 2023-06-01 00:00:00 |
+—-+———+————+——–+———————+
mysql> SELECT * FROM fgedu_orders PARTITION (p2024);
+—-+———+————+——–+———————+
| id | user_id | product_id | amount | created_at |
+—-+———+————+——–+———————+
| 3 | 3 | 3 | 300.00 | 2024-01-01 00:00:00 |
| 4 | 4 | 4 | 400.00 | 2024-06-01 00:00:00 |
+—-+———+————+——–+———————+
mysql> SELECT * FROM fgedu_orders PARTITION (p2025);
+—-+———+————+——–+———————+
| id | user_id | product_id | amount | created_at |
+—-+———+————+——–+———————+
| 5 | 5 | 5 | 500.00 | 2025-01-01 00:00:00 |
+—-+———+————+——–+———————+
# 管理分区
# 添加新分区
mysql> ALTER TABLE fgedu_orders REORGANIZE PARTITION pfuture INTO (
-> PARTITION p2026 VALUES LESS THAN (2027),
-> PARTITION pfuture VALUES LESS THAN MAXVALUE
-> );
# 删除旧分区
mysql> ALTER TABLE fgedu_orders DROP PARTITION p2023;

# 案例：MySQL水平分片
# 连接ShardingSphere-Proxy
$ kubectl exec -it shardingsphere-proxy-6d6f58987b-7f5f8 -n middleware — mysql -u root -p -P 3307
Enter password: root123
# 插入测试数据
mysql> INSERT INTO fgedu_users (name, email) VALUES
-> (‘User1’, ‘user1@example.com’),
-> (‘User2’, ‘user2@example.com’),
-> (‘User3’, ‘user3@example.com’),
-> (‘User4’, ‘user4@example.com’),
-> (‘User5’, ‘user5@example.com’),
-> (‘User6’, ‘user6@example.com’);
# 查询数据
mysql> SELECT * FROM fgedu_users;
+—-+——-+——————+———————+
| id | name | email | created_at |
+—-+——-+——————+———————+
| 1 | User1 | user1@example.com | 2024-01-01 00:00:00 |
| 2 | User2 | user2@example.com | 2024-01-01 00:00:00 |
| 3 | User3 | user3@example.com | 2024-01-01 00:00:00 |
| 4 | User4 | user4@example.com | 2024-01-01 00:00:00 |
| 5 | User5 | user5@example.com | 2024-01-01 00:00:00 |
| 6 | User6 | user6@example.com | 2024-01-01 00:00:00 |
+—-+——-+——————+———————+
# 查看各分片的数据
$ kubectl exec -it mysql-6d6f58987b-7f5f8 -n middleware — mysql -u root -p -e “SELECT * FROM fgedudb_shard1.fgedu_users;”
Enter password: root123
+—-+——-+——————+———————+
| id | name | email | created_at |
+—-+——-+——————+———————+
| 3 | User3 | user3@example.com | 2024-01-01 00:00:00 |
| 6 | User6 | user6@example.com | 2024-01-01 00:00:00 |
+—-+——-+——————+———————+
$ kubectl exec -it mysql-6d6f58987b-7f5f8 -n middleware — mysql -u root -p -e “SELECT * FROM fgedudb_shard2.fgedu_users;”
Enter password: root123
+—-+——-+——————+———————+
| id | name | email | created_at |
+—-+——-+——————+———————+
| 1 | User1 | user1@example.com | 2024-01-01 00:00:00 |
| 4 | User4 | user4@example.com | 2024-01-01 00:00:00 |
+—-+——-+——————+———————+
$ kubectl exec -it mysql-6d6f58987b-7f5f8 -n middleware — mysql -u root -p -e “SELECT * FROM fgedudb_shard3.fgedu_users;”
Enter password: root123
+—-+——-+——————+———————+
| id | name | email | created_at |，更多视频教程www.fgedu.net.cn。
+—-+——-+——————+———————+
| 2 | User2 | user2@example.com | 2024-01-01 00:00:00 |
| 5 | User5 | user5@example.com | 2024-01-01 00:00:00 |
+—-+——-+——————+———————+

# 案例：PostgreSQL分区
# 部署PostgreSQL
$ cat > postgres-deployment.yaml << 'EOF' apiVersion: apps/v1 kind: Deployment metadata: name: postgres namespace: middleware spec: replicas: 1 selector: matchLabels: app: postgres template: metadata: labels: app: postgres spec: containers: - name: postgres image: postgres:14 ports: - containerPort: 5432 env: - name: POSTGRES_DB value: fgedudb - name: POSTGRES_USER value: fgedu - name: POSTGRES_PASSWORD value: fgedu123 volumeMounts: - name: postgres-storage mountPath: /var/lib/postgresql/data volumes: - name: postgres-storage emptyDir: {} EOF $ kubectl apply -f postgres-deployment.yaml # 创建PostgreSQL服务 $ cat > postgres-service.yaml << 'EOF' apiVersion: v1 kind: Service metadata: name: postgres namespace: middleware spec: selector: app: postgres ports: - port: 5432 targetPort: 5432 type: ClusterIP EOF $ kubectl apply -f postgres-service.yaml # 检查PostgreSQL部署状态 $ kubectl get pods -n middleware NAME READY STATUS RESTARTS AGE mysql-6d6f58987b-7f5f8 1/1 Running 0 20m shardingsphere-proxy-6d6f58987b-7f5f8 1/1 Running 0 15m app-6d6f58987b-7f5f8 1/1 Running 0 10m postgres-6d6f58987b-9f5g7 1/1 Running 0 5m # 连接PostgreSQL $ kubectl exec -it postgres-6d6f58987b-9f5g7 -n middleware -- psql -U fgedu -d fgedudb # 创建分区表 fgedudb=> CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
user_id INT,
product_id INT,
amount DECIMAL(10,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (created_at);
# 创建分区
fgedudb=> CREATE TABLE fgedu_orders_2023 PARTITION OF fgedu_orders
FOR VALUES FROM (‘2023-01-01’) TO (‘2024-01-01’);
fgedudb=> CREATE TABLE fgedu_orders_2024 PARTITION OF fgedu_orders
FOR VALUES FROM (‘2024-01-01’) TO (‘2025-01-01’);
fgedudb=> CREATE TABLE fgedu_orders_2025 PARTITION OF fgedu_orders
FOR VALUES FROM (‘2025-01-01’) TO (‘2026-01-01’);
# 插入测试数据
fgedudb=> INSERT INTO fgedu_orders (user_id, product_id, amount, created_at) VALUES
(1, 1, 100.00, ‘2023-01-01’),
(2, 2, 200.00, ‘2023-06-01’),
(3, 3, 300.00, ‘2024-01-01’),
(4, 4, 400.00, ‘2024-06-01’),
(5, 5, 500.00, ‘2025-01-01’);
# 查询分区数据
fgedudb=> SELECT * FROM fgedu_orders_2023;
id | user_id | product_id | amount | created_at
—-+———+————+——–+———————+
1 | 1 | 1 | 100.00 | 2023-01-01 00:00:00
2 | 2 | 2 | 200.00 | 2023-06-01 00:00:00
(2 rows)
fgedudb=> SELECT * FROM fgedu_orders_2024;
id | user_id | product_id | amount | created_at
—-+———+————+——–+———————+，更多学习教程公众号风哥教程itpux_com。
3 | 3 | 3 | 300.00 | 2024-01-01 00:00:00
4 | 4 | 4 | 400.00 | 2024-06-01 00:00:00
(2 rows)
fgedudb=> SELECT * FROM fgedu_orders_2025;
id | user_id | product_id | amount | created_at
—-+———+————+——–+———————+
5 | 5 | 5 | 500.00 | 2025-01-01 00:00:00
(1 row)

# 案例：使用ProxySQL作为分片代理
# 部署ProxySQL
$ cat > proxysql-deployment.yaml << 'EOF' apiVersion: apps/v1 kind: Deployment metadata: name: proxysql namespace: middleware spec: replicas: 1 selector: matchLabels: app: proxysql template: metadata: labels: app: proxysql spec: containers: - name: proxysql image: proxysql/proxysql:2.3.2 ports: - containerPort: 6033 - containerPort: 6032 volumeMounts: - name: config mountPath: /etc/proxysql volumes: - name: config configMap: name: proxysql-config --- apiVersion: v1 kind: ConfigMap metadata: name: proxysql-config namespace: middleware data: proxysql.cnf: | datadir="/var/lib/proxysql" admin_variables={ admin_credentials="admin:admin" mysql_ifaces="0.0.0.0:6032" refresh_interval=2000 } mysql_variables={ threads=4 max_connections=2048 default_query_delay=0 default_query_timeout=36000000 have_compress=true poll_timeout=2000 interfaces="0.0.0.0:6033" default_schema="information_schema" stacksize=1048576 server_version="5.7.33" connect_timeout_server=3000 monitor_username="monitor" monitor_password="monitor" monitor_history=600000 monitor_connect_interval=60000 monitor_ping_interval=10000 monitor_read_only_interval=1500 monitor_read_only_timeout=500 ping_interval_server_msec=120000 ping_timeout_server=500 commands_stats=true sessions_sort=true connect_retries_on_failure=10 } mysql_servers = ( { address="mysql" port=3306 hostgroup=0 max_connections=1000 } ) mysql_users = ( { username="fgedu" password="fgedu123" default_hostgroup=0 active=1 } ) EOF $ kubectl apply -f proxysql-deployment.yaml # 创建ProxySQL服务 $ cat > proxysql-service.yaml << 'EOF'，from K8S+DB视频:www.itpux.com。 apiVersion: v1 kind: Service metadata: name: proxysql namespace: middleware spec: selector: app: proxysql ports: - port: 6033 targetPort: 6033。 - port: 6032 targetPort: 6032 type: ClusterIP EOF $ kubectl apply -f proxysql-service.yaml # 检查ProxySQL部署状态 $ kubectl get pods -n middleware NAME READY STATUS RESTARTS AGE mysql-6d6f58987b-7f5f8 1/1 Running 0 25m shardingsphere-proxy-6d6f58987b-7f5f8 1/1 Running 0 20m app-6d6f58987b-7f5f8 1/1 Running 0 15m postgres-6d6f58987b-9f5g7 1/1 Running 0 10m proxysql-6d6f58987b-7f5f8 1/1 Running 0 5m # 连接ProxySQL $ kubectl exec -it proxysql-6d6f58987b-7f5f8 -n middleware -- mysql -u fgedu -p -h 127.0.0.1 -P 6033 Enter password: fgedu123 # 查询数据 mysql> USE fgedudb;
mysql> SELECT * FROM fgedu_users;
+—-+——-+——————+———————+
| id | name | email | created_at |
+—-+——-+——————+———————+
| 1 | User1 | user1@example.com | 2024-01-01 00:00:00 |
| 2 | User2 | user2@example.com | 2024-01-01 00:00:00 |
| 3 | User3 | user3@example.com | 2024-01-01 00:00:00 |
+—-+——-+——————+———————+