一、簡要說明
以下配置實現了：
1、分庫分表
2、每一個分庫的讀寫分離
3、讀庫負載均衡演算法
4、雪花演算法，生成唯一id
5、欄位取模
二、配置項
# # Licensed to the Apache Software Foundation (ASF) under one or more # contributor license agreements. See the NOTICE file distributed with # this work for additional information regarding copyright ownership. # The ASF licenses this file to You under the Apache License, Version 2.0 # (the "License"); you may not use this file except in compliance with # the License. You may obtain a copy of the License at # # http://www.apache.org/licenses/LICENSE-2.0 # # Unless required by applicable law or agreed to in writing, software # distributed under the License is distributed on an "AS IS" BASIS, # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. # See the License for the specific language governing permissions and # limitations under the License. # ###################################################################################################### # # Here you can configure the rules for the proxy. # This example is configuration of sharding rule. # ###################################################################################################### # #schemaName: sharding_db # #dataSources: # ds_0: # url: jdbc:postgresql://127.0.0.1:5432/demo_ds_0 # username: postgres # password: postgres # connectionTimeoutMilliseconds: 30000 # idleTimeoutMilliseconds: 60000 # maxLifetimeMilliseconds: 1800000 # maxPoolSize: 50 # minPoolSize: 1 # ds_1: # url: jdbc:postgresql://127.0.0.1:5432/demo_ds_1 # username: postgres # password: postgres # 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 # props: # worker-id: 123 ###################################################################################################### # # If you want to connect to MySQL, you should manually copy MySQL driver to lib directory. # ###################################################################################################### # 連線mysql所使用的資料庫名 schemaName: MyDb dataSources: dsdatasources_0: url: jdbc:mysql://127.0.0.1:3306/MyDb_0?serverTimezone=UTC&useSSL=false username: root # 資料庫使用者名稱 password: mysql123 # 登入密碼 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 dsdatasources_0_read0: url: jdbc:mysql://192.168.140.132:3306/MyDb_0?serverTimezone=UTC&useSSL=false username: root # 資料庫使用者名稱 password: Xiaohemiao_123 # 登入密碼 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 dsdatasources_1: url: jdbc:mysql://127.0.0.1:3306/MyDb_1?serverTimezone=UTC&useSSL=false username: root # 資料庫使用者名稱 password: mysql123 # 登入密碼 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 dsdatasources_1_read1: url: jdbc:mysql://192.168.140.132:3306/MyDb_1?serverTimezone=UTC&useSSL=false username: root # 資料庫使用者名稱 password: Xiaohemiao_123 # 登入密碼 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 # ds_1: # url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false # username: root # password: # connectionTimeoutMilliseconds: 30000 # idleTimeoutMilliseconds: 60000 # maxLifetimeMilliseconds: 1800000 # maxPoolSize: 50 # minPoolSize: 1 # # 規則 rules: - !READWRITE_SPLITTING dataSources: pr_ds1: writeDataSourceName: dsdatasources_0 #主庫 readDataSourceNames: - dsdatasources_0_read0 # 從庫，如果有多個從庫，就在下面寫多個 loadBalancerName: loadBalancer_ROUND_ROBIN pr_ds2: writeDataSourceName: dsdatasources_1 #主庫 readDataSourceNames: - dsdatasources_1_read1 # 從庫，如果有多個從庫，就在下面寫多個 loadBalancerName: loadBalancer_ROUND_ROBIN loadBalancers: # 負載均衡演算法配置 loadBalancer_ROUND_ROBIN: # 負載均衡演算法名稱，自定義 type: ROUND_ROBIN # 負載均衡演算法，預設為輪詢演算法，還有加權演算法和隨機演算法，可參考官網 - !SHARDING tables: t_product: #需要進行分表的表名 actualDataNodes: dsdatasources_${0..1}.t_product_${0..1} # 表示式，將表分為t_product_0 , t_product_1 tableStrategy: #分表策略 standard: shardingColumn: product_id # 欄位名 shardingAlgorithmName: t_product_MOD databaseStrategy: # 分庫策略 standard: shardingColumn: product_id shardingAlgorithmName: t_product_MOD keyGenerateStrategy: column: 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: t_product_MOD: # 取模名稱，可自定義 type: MOD # 取模演算法 props: sharding-count: 2 #分片數量，因為分了兩個表，所以這裡是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 props: worker-id: 123
三、資料準備
-- 建立表 SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_product -- ---------------------------- DROP TABLE IF EXISTS `t_product`; CREATE TABLE `t_product` ( `id` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `product_id` int(11) NOT NULL, `product_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, PRIMARY KEY (`id`, `product_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1; -- 插入表資料 INSERT INTO t_product(product_id,product_name) VALUES(1,'one'); INSERT INTO t_product(product_id,product_name) VALUES(2,'two'); INSERT INTO t_product(product_id,product_name) VALUES(3,'three'); INSERT INTO t_product(product_id,product_name) VALUES(4,'four'); INSERT INTO t_product(product_id,product_name) VALUES(5,'five'); INSERT INTO t_product(product_id,product_name) VALUES(6,'six'); INSERT INTO t_product(product_id,product_name) VALUES(7,'seven');
四、檢視資料
1、檢視shardingsphere中介軟體t_product表資料
2、主庫192.168.140.131資料
2、從庫192.168.140.132資料