shardingsphere

apache-shardingsphere-5.0.0  分库分表配置

 

 

 

[root@ conf]# cat config-pangu.yaml | grep -Ev '^$|#'
schemaName: kbb
dataSourceCommon:
  username: kernel
  password: "$"
  connectionTimeoutMilliseconds: 30000
  idleTimeoutMilliseconds: 60000
  maxLifetimeMilliseconds: 1800000
  maxPoolSize: 50
  minPoolSize: 1
  maintenanceIntervalMilliseconds: 30000
dataSources:
  ds_0:
    url: jdbc:mysql://192.168.26.125:3306/zto_pangu_nhzq_0000?serverTimezone=UTC&useSSL=false
  ds_1:
    url: jdbc:mysql://192.168.26.125:3306/zto_pangu_nhzq_0001?serverTimezone=UTC&useSSL=false
rules:
- !SHARDING
  tables:
    pg_bill_operation_log:
      actualDataNodes: ds_0.pg_bill_operation_log_000${0..3},ds_1.pg_bill_operation_log_000${4..7}
      tableStrategy:
        standard:
          shardingColumn: bill_code_hash
          shardingAlgorithmName: t_order_inline
  shardingAlgorithms:
    t_order_inline:
      type: INLINE
      props:
        algorithm-expression: pg_bill_operation_log_000${bill_code_hash % 8}
  defaultDatabaseStrategy:
    none:
  defaultTableStrategy:
    none:

 

建表

 CREATE TABLE `pg_bill_operation_log` (
  `id` bigint(20) NOT NULL COMMENT 'PK',
  `bill_code` varchar(20) DEFAULT NULL COMMENT '',
  `bill_code_hash` int(11) DEFAULT NULL COMMENT ''
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

插入数据按8的整数倍取余数

insert into pg_bill_operation_log(id,bill_code,bill_code_hash) values (1,'213607954139',1);
insert into pg_bill_operation_log(id,bill_code,bill_code_hash) values (2,'213607954139',2);
insert into pg_bill_operation_log(id,bill_code,bill_code_hash) values (3,'213607954139',3);
insert into pg_bill_operation_log(id,bill_code,bill_code_hash) values (4,'213607954139',4);
insert into pg_bill_operation_log(id,bill_code,bill_code_hash) values (5,'213607954139',5);
insert into pg_bill_operation_log(id,bill_code,bill_code_hash) values (6,'213607954139',6);
insert into pg_bill_operation_log(id,bill_code,bill_code_hash) values (7,'213607954139',7);
insert into pg_bill_operation_log(id,bill_code,bill_code_hash) values (8,'213607954139',8);
insert into pg_bill_operation_log(id,bill_code,bill_code_hash) values (9,'213607954139',9);
insert into pg_bill_operation_log(id,bill_code,bill_code_hash) values (10,'213607954139',10);
insert into pg_bill_operation_log(id,bill_code,bill_code_hash) values (11,'213607954139',11);
insert into pg_bill_operation_log(id,bill_code,bill_code_hash) values (12,'213607954139',12);
insert into pg_bill_operation_log(id,bill_code,bill_code_hash) values (13,'213607954139',13);
insert into pg_bill_operation_log(id,bill_code,bill_code_hash) values (14,'213607954139',14);

 

代理默认端口是3307 

mysql> select  id,bill_code,bill_code_hash,table_name from pg_bill_operation_log limit 10 ;
+----+--------------+----------------+------------+
| id | bill_code    | bill_code_hash | table_name |
+----+--------------+----------------+------------+
|  8 | 213607954139 |              8 | NULL       |
|  1 | 213607954139 |              1 | NULL       |
|  9 | 213607954139 |              9 | NULL       |
|  2 | 213607954139 |              2 | NULL       |
| 10 | 213607954139 |             10 | NULL       |
|  3 | 213607954139 |              3 | NULL       |
| 11 | 213607954139 |             11 | NULL       |
|  4 | 213607954139 |              4 | NULL       |
| 12 | 213607954139 |             12 | NULL       |
|  5 | 213607954139 |              5 | NULL       |
+----+--------------+----------------+------------+
10 rows in set (0.00 sec)

 

mysql> select  id,bill_code,bill_code_hash,table_name from pg_bill_operation_log order by id limit 10 ;
+----+--------------+----------------+------------+
| id | bill_code    | bill_code_hash | table_name |
+----+--------------+----------------+------------+
|  1 | 213607954139 |              1 | NULL       |
|  2 | 213607954139 |              2 | NULL       |
|  3 | 213607954139 |              3 | NULL       |
|  4 | 213607954139 |              4 | NULL       |
|  5 | 213607954139 |              5 | NULL       |
|  6 | 213607954139 |              6 | NULL       |
|  7 | 213607954139 |              7 | NULL       |
|  8 | 213607954139 |              8 | NULL       |
|  9 | 213607954139 |              9 | NULL       |
| 10 | 213607954139 |             10 | NULL       |
+----+--------------+----------------+------------+
10 rows in set (0.00 sec)

 

[INFO ] 09:38:40.921 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual SQL: ds_0 ::: select  id,bill_code,bill_code_hash,table_name from pg_bill_operation_log_0000 limit 10
[INFO ] 09:38:40.921 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual SQL: ds_0 ::: select  id,bill_code,bill_code_hash,table_name from pg_bill_operation_log_0001 limit 10
[INFO ] 09:38:40.921 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual SQL: ds_0 ::: select  id,bill_code,bill_code_hash,table_name from pg_bill_operation_log_0002 limit 10
[INFO ] 09:38:40.921 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual SQL: ds_0 ::: select  id,bill_code,bill_code_hash,table_name from pg_bill_operation_log_0003 limit 10
[INFO ] 09:38:40.921 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual SQL: ds_1 ::: select  id,bill_code,bill_code_hash,table_name from pg_bill_operation_log_0004 limit 10
[INFO ] 09:38:40.921 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual SQL: ds_1 ::: select  id,bill_code,bill_code_hash,table_name from pg_bill_operation_log_0005 limit 10
[INFO ] 09:38:40.921 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual SQL: ds_1 ::: select  id,bill_code,bill_code_hash,table_name from pg_bill_operation_log_0006 limit 10
[INFO ] 09:38:40.921 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual SQL: ds_1 ::: select  id,bill_code,bill_code_hash,table_name from pg_bill_operation_log_0007 limit 10