1. Suggestions for learning in shardingsphere are also suggestions for learning in all technologies

Shardingsphere, like MyCat, is a solution middleware for highly available databases. Shardingsphere is used here.Copy the code

If you are not familiar with Shardingsphere, you should first look for some articles or videos to get started, and then you need to read the official documents. When you read other people’s articles or things, it is a fast food, but when you read the official documents, it is a feast, so please do not miss the feast. Shardingsphere official documentation: https://shardingsphere.apache.org/document/legacy/4.x/document/cn/overview/

2. Background

  1. Introduction to business Background: With the rapid development of banking business, more and more data are generated by locking every day, and the requirements for database are getting higher and higher, especially the requirements for elastic expansion.
  2. Here we discuss the main contradiction, pipelining database governance.
  3. The flow micro service mainly deals with the operation of adding, deleting, modifying and checking the flow table, and provides basic service functions for other micro services.
  4. Configuration microservice provides the parameter configuration service for the entire system, mainly providing some basic system parameter configuration, such as dictionary table data configuration.
  5. Bank sub-branch, the concept of the teller here a little explanation: the sub-branch is our business of the facade, the teller is the business of the salesperson. For every transaction we make, we generate a flow (possibly multiple: a loan).
  6. At present, the flow volume of small and medium-sized banks is 200,000 to 300,000 a day. The peak was 600,000.
  7. With an annual growth of 10%, the database is designed to meet the development needs of the business for three years, while taking into account the elastic expansion of the database in case of a sudden business growth.
  8. Historical data migration can be performed after 3 years.



    How should we design the area near the above image?

    Let’s start with business growth

3. Growth of assembly line business

At a peak of 600,000 per day, 18 million per month per year: 216 million. 2020 new (million) : 2.16 cumulative total of 2020 million) : 2.16 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = 2021 new (million) : 2.3760000000000003 years accumulative total 2021) : 4.5360000000000005 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = 2022 new (million) : 2.6136000000000004 years accumulative total 2022) : 7.149600000000001 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = 2023 new (million) : 2.8749600000000006 years accumulative total 2023) : 10.024560000000001 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = 2024 new (million) : 3.162456000000001 years accumulative total 2024) : 13.187016000000002 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = 2025 new (million) : 3.4787016000000013 years accumulative total 2025) : 16.665717600000004 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = 2026 new (million) : 3.8265717600000015 years accumulative total 2026) : 20.492289360000004 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = 2027 new (million) : 4.209228936000002 years accumulative total 2027) : 24.701518296000007 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = 2028 new (million) : 4.630151829600003 years accumulative total 2028) : 29.33167012560001 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = 2029 new (million) : 5.093167012560003 years accumulative total 2029) : 34.424837138160015 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = 2030 new (million) : 5.602483713816004 years accumulative total 2030) : 40.02732085197602 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =Copy the code

How to improve the database capability when the accumulated data reaches 714 million by 2022?

Design of 4.

Example of relationship between organization and partition, user vertical partition:

Institutions, Library partition number
070667001 0
070667002 0
070667003 1
070667004 2
070667005 3
070667006 4
070667007 5
070667008 6
070667009 6
0706670010 6
070667011 6

Example of relationship between teller and partition, user horizontal partition:

Institutions, Table partition number
4001 0
4002 0
4003 1
4004 2
4005 3
4006 4
4007 5
4008 6
4009 6
… … … …
4010 31
… … … …
  1. There are 7 main nodes in total, which are segmented longitudinally: segmented according to the region where the mechanism is located; Horizontal segmentation according to the division where the teller is located.
  2. Each master node has two slave nodes for read/write separation
  3. The result is that the database quantity of each node in the three planes is about 3.29 million. Less than 5 million. Sufficient to complete business development needs. And the margin is sufficient.



    Config The configuration is as follows:
schemaName: sharding_db dataSources: master_ds_0: url: jdbc:mysql://***.***.***.***:****/db_seq? serverTimezone=UTC&useSSL=false
    username: testpassword: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 200 slave_ds_0: url: jdbc:mysql://***.***.***.***:****/db_seq? serverTimezone=UTC&useSSL=false
    username: testpassword: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 200 master_ds_1: url: jdbc:mysql://***.***.***.***:****/db_seq? serverTimezone=UTC&useSSL=false
    username: testpassword: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 200 slave_ds_1: url: jdbc:mysql://***.***.***.***:****/db_seq? serverTimezone=UTC&useSSL=false
    username: testpassword: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 200 master_ds_2: url: jdbc:mysql://***.***.***.***:****/db_seq? serverTimezone=UTC&useSSL=false
    username: testpassword: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 200 slave_ds_2: url: jdbc:mysql://***.***.***.***:****/db_seq? serverTimezone=UTC&useSSL=false
    username: testpassword: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 200 master_ds_3: url: jdbc:mysql://***.***.***.***:****/db_seq? serverTimezone=UTC&useSSL=false
    username: testpassword: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 200 slave_ds_3: url: jdbc:mysql://***.***.***.***:****/db_seq? serverTimezone=UTC&useSSL=false
    username: testpassword: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 200 master_ds_4: url: jdbc:mysql://***.***.***.***:****/db_seq? serverTimezone=UTC&useSSL=false
    username: testpassword: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 200 slave_ds_4: url: jdbc:mysql://***.***.***.***:****/db_seq? serverTimezone=UTC&useSSL=false
    username: testpassword: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 200 master_ds_5: url: jdbc:mysql://***.***.***.***:****/db_seq? serverTimezone=UTC&useSSL=false
    username: testpassword: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 200 slave_ds_5: url: jdbc:mysql://***.***.***.***:****/db_seq? serverTimezone=UTC&useSSL=false
    username: testpassword: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 200 master_ds_6: url: jdbc:mysql://***.***.***.***:****/db_seq? serverTimezone=UTC&useSSL=false
    username: testpassword: 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_The ${0.. 6}.tb_seqThe ${0.. 30}
      databaseStrategy:
        inline:
          shardingColumn: ds_zone 1, 1,2,3,4,5,6- associated configuration with the branch
          algorithmExpression: ms_ds_${ds_zone}
      tableStrategy:
        inline:
          shardingColumn: tb_zone Table partition key
          algorithmExpression: tb_seq${ds_zone}
      keyGenerator:
        type: SNOWFLAKE Use LEAF after #5.0
        column: seq_id # key
  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
Copy the code

5. M primary and N secondary upgrade

If the primary node is down, you can use m primary + N secondary mode:

6. Migrate data

  • You can use mysql’s Archive storage engine, which compresses data 70-80 after migration and supports only query and insert. Free up more space for the business database.
  • The query logic for historical data remains the same as that for existing data. In this way, the efficiency of existing services is not affected and the query for historical data meets service requirements.

Hope to understand the mysql database fragmentation and large quantities of data caused by the database bottleneck problems have a certain reference significance.

6. Dynamic water level adjustment

Nightly scheduled task: Automatically adjust the water level of the library and table.