分库分表分库分表

jupiter_888

已于 2024-08-16 10:10:53 修改

阅读量325

点赞数 3

文章标签：分库分表

于 2024-08-01 16:06:07 首次发布

本文链接：https://blog.csdn.net/jupiter_888/article/details/140850435

版权

分库分表

如果用ShardingSphere-jdbc来分库分表，那么需要开发者手动在创建1024张表结构吗

《分库分表ShardingSphere原理与实战》

sharding-proxy的安装和测试

sharding-proxy就像一个mysql服务一样，将所有分库分表汇总在一个库，一张表中，方便运维。

sharding-proxy还可以创建表、修改表，会根据分库分表规则在所有分库中执行DD
L的。

先分库再分表，分库用user_id,分表用order_id, 否则如果表字段不含user_id无法分库，
报错
ERROR 1997 (C1997): Runtime exception: [Insert statement does not support sharding table routing to multiple data nodes.]


schemaName: sharding_db

dataSources:
 ds_0:
   url: jdbc:mysql://localhost:3306/db0?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
   username: root
   password: password
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
   minPoolSize: 1
 ds_1:
   url: jdbc:mysql://localhost:3306/db1?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
   username: root
   password: password
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
   minPoolSize: 1

rules:
- !SHARDING
 tables:
   t_order:
     actualDataNodes: ds_${0..1}.t_order_${0..1}
     tableStrategy:
       standard:
         shardingColumn: order_id
         shardingAlgorithmName: t_order_inline
     keyGenerateStrategy:
         column: order_id
         keyGeneratorName: snowflake
   t_order_item:
     actualDataNodes: ds_${0..1}.t_order_item_${0..1}
     tableStrategy:
       standard:
         shardingColumn: order_id
         shardingAlgorithmName: t_order_item_inline
     keyGenerateStrategy:
       column: order_item_id
       keyGeneratorName: snowflake
 bindingTables:
   - t_order,t_order_item
 defaultDatabaseStrategy:
   standard:
     shardingColumn: user_id
     shardingAlgorithmName: database_inline
 defaultTableStrategy:
   none:
 
 shardingAlgorithms:
   database_inline:
     type: INLINE
     props:
       algorithm-expression: ds_${user_id % 2}
   t_order_inline:
     type: INLINE
     props:
       algorithm-expression: t_order_${order_id % 2}
   t_order_item_inline:
     type: INLINE
     props:
       algorithm-expression: t_order_item_${order_id % 2}

 keyGenerators:
   snowflake:
     type: SNOWFLAKE