使用shardingsphere实现mysql数据库分片（分库分表的实现方式）、读写分离、弹性伸缩的设计_mysql 分表中间件shardingsphere

1.对shardingsphere学习的建议，也是对所有技术学习的建议

shardingsphere和mycat类似，均是高可用数据库的解决方案中间件。这里采用shardingsphere。
1

如果大家对shardingsphere不了解，先找些文章或者视频看一下，入门一下，然后需要看官方文档，你在看别人的文章或者东西的时候是一个快餐，而你看官方文档的时候才是大餐，所以请一定不要错过大餐。shardingsphere官方文档：https://shardingsphere.apache.org/document/legacy/4.x/document/cn/overview/

2.背景介绍

1. 业务背景介绍：随着银行业务发展的迅速增长，每天锁产生的流水数据越来越多，对数据库的要求越来越高，特别是弹性伸缩这块儿的要求。
2. 这里讨论主要矛盾，流水的数据库治理。
3. 流水微服务主要处理流水表的增删改查操作，为其他微服务提供基础服务功能。
4. 配置微服务为整个系统的参数配置服务，主要提供一些基础系统参数配置，如字典表数据的配置。
5. 银行支行、柜员概念这里稍作说明：支行就是咱们业务的那个门面房，柜员就是办理业务的营业员。我们每办理一笔业务就会产生一笔（有可能是多条：借贷）流水。
6. 目前一般中小银行的流水量一天在20-30万笔。峰值在60万笔。
7. 每年增长之多10%，设计数据库满足业务3年的发展需要，同时兼顾业务突增时的数据库的弹性伸缩。
8. 3年后可以对数据进行历史数据迁移。
   
   我们的关注点近在上图匡红的地方应该怎设计？
   首先看下业务增长的情况

3.流水业务的增长情况

每天按峰值60万计算，每月1800万一年：2.16亿。
2020新增（亿）：2.16
2020年累计总量亿）：2.16
======================================
2021新增（亿）：2.3760000000000003
2021年累计总量亿）：4.5360000000000005
======================================
2022新增（亿）：2.6136000000000004
2022年累计总量亿）：7.149600000000001
======================================
2023新增（亿）：2.8749600000000006
2023年累计总量亿）：10.024560000000001
======================================
2024新增（亿）：3.162456000000001
2024年累计总量亿）：13.187016000000002
======================================
2025新增（亿）：3.4787016000000013
2025年累计总量亿）：16.665717600000004
======================================
2026新增（亿）：3.8265717600000015
2026年累计总量亿）：20.492289360000004
======================================
2027新增（亿）：4.209228936000002
2027年累计总量亿）：24.701518296000007
======================================
2028新增（亿）：4.630151829600003
2028年累计总量亿）：29.33167012560001
======================================
2029新增（亿）：5.093167012560003
2029年累计总量亿）：34.424837138160015
======================================
2030新增（亿）：5.602483713816004
2030年累计总量亿）：40.02732085197602
======================================
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

到达2022年累计数据达到7.14亿，如何提高数据库的想能。

4.设计

机构和分区的关系实例，用户纵向分区：

柜员和分区的关系实例，用户横向分区：

1. 共7个主节点，纵向切分：按机构所在的分区切分；横向切分按柜员所在的分区切分。
2. 每个主节点有两个slave节点，做读写分离
3. 达到的效果为，3面内每个节点的数据库量在329万左右。小于500万。足够完整业务发展的需要。且余量充足。
   
   config配置如下

schemaName: sharding_db

dataSources:
  master_ds_0:
    url: jdbc:mysql://***.***.***.***:****/db_seq?serverTimezone=UTC&useSSL=false
    username: test
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 200
  slave_ds_0:
    url: jdbc:mysql://***.***.***.***:****/db_seq?serverTimezone=UTC&useSSL=false
    username: test
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 200
  master_ds_1:
    url: jdbc:mysql://***.***.***.***:****/db_seq?serverTimezone=UTC&useSSL=false
    username: test
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 200
  slave_ds_1:
    url: jdbc:mysql://***.***.***.***:****/db_seq?serverTimezone=UTC&useSSL=false
    username: test
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 200
  master_ds_2:
    url: jdbc:mysql://***.***.***.***:****/db_seq?serverTimezone=UTC&useSSL=false
    username: test
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 200
  slave_ds_2:
    url: jdbc:mysql://***.***.***.***:****/db_seq?serverTimezone=UTC&useSSL=false
    username: test
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 200
  master_ds_3:
    url: jdbc:mysql://***.***.***.***:****/db_seq?serverTimezone=UTC&useSSL=false
    username: test
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 200
  slave_ds_3:
    url: jdbc:mysql://***.***.***.***:****/db_seq?serverTimezone=UTC&useSSL=false
    username: test
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 200
	master_ds_4:
    url: jdbc:mysql://***.***.***.***:****/db_seq?serverTimezone=UTC&useSSL=false
    username: test
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 200
  slave_ds_4:
    url: jdbc:mysql://***.***.***.***:****/db_seq?serverTimezone=UTC&useSSL=false
    username: test
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 200
	master_ds_5:
    url: jdbc:mysql://***.***.***.***:****/db_seq?serverTimezone=UTC&useSSL=false
    username: test
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 200
  slave_ds_5:
    url: jdbc:mysql://***.***.***.***:****/db_seq?serverTimezone=UTC&useSSL=false
    username: test
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 200
  master_ds_6:
    url: jdbc:mysql://***.***.***.***:****/db_seq?serverTimezone=UTC&useSSL=false
    username: test
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 200
  slave_ds_6:
    url: jdbc:mysql://***.***.***.***:****/db_seq?serverTimezone=UTC&useSSL=false
    username: test
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 200
shardingRule:
  tables:
    tb_seq:
      actualDataNodes: ms_ds_${0..6}.tb_seq${0..30}
      databaseStrategy:
        inline:
          shardingColumn: ds_zone # 流水表中数据库的分区字段 0,1,2,3,4,5,6-和支行关联配置
          algorithmExpression: ms_ds_${ds_zone}
      tableStrategy:
        inline:
          shardingColumn: tb_zone #表分区key值
          algorithmExpression: tb_seq${ds_zone}
      keyGenerator:
        type: SNOWFLAKE #5.0之后使用LEAF
        column: seq_id #主键
  bindingTables:
    - tb_seq
  defaultDataSourceName: master_ds_1
  defaultTableStrategy:
    none:
  masterSlaveRules:
    ms_ds_0:
      masterDataSourceName: master_ds_0
      slaveDataSourceNames:
        - slave_ds_0
      loadBalanceAlgorithmType: ROUND_ROBIN
    ms_ds_1:
      masterDataSourceName: master_ds_1
      slaveDataSourceNames:
        - slave_ds_1
      loadBalanceAlgorithmType: ROUND_ROBIN
    ms_ds_2:
      masterDataSourceName: master_ds_2
      slaveDataSourceNames:
        - slave_ds_2
      loadBalanceAlgorithmType: ROUND_ROBIN
    ms_ds_3:
      masterDataSourceName: master_ds_3
      slaveDataSourceNames:
        - slave_ds_3
      loadBalanceAlgorithmType: ROUND_ROBIN
    ms_ds_4:
      masterDataSourceName: master_ds_4
      slaveDataSourceNames:
        - slave_ds_4
      loadBalanceAlgorithmType: ROUND_ROBIN
    ms_ds_5:
      masterDataSourceName: master_ds_5
      slaveDataSourceNames:
        - slave_ds_5
      loadBalanceAlgorithmType: ROUND_ROBIN
    ms_ds_6:
      masterDataSourceName: master_ds_6
      slaveDataSourceNames:
        - slave_ds_6
      loadBalanceAlgorithmType: ROUND_ROBIN
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171

5.m主+n从升级

主节点如果宕机怎么办，可以使用m主+n从模式：

6.数据迁移

• 可以使用mysql 的Archive存储引擎，迁移后数据压缩70-80,仅支持查询和插入。为业务数据库腾出更多的空间。
• 针对历史数据的查询和现有数据的查询逻辑不变，这样即不影响现有业务的效率对，历史数据的查询也满足业务要求。

希望对大家理解mysql数据库分片和大批量数据导致数据库瓶颈问题解决有一定的参考意义。

6.动态水位调整

每晚定时任务：对库和表的水位进行自动调整。