Shulou(Shulou.com)06/02 Report--

This article mainly explains the "SpringBoot integration sharding-jdbc to achieve custom sub-library sub-table method is what", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in-depth, together to study and learn "SpringBoot integration sharding-jdbc to achieve custom sub-library sub-table method is what" it!

I. Preface

SpringBoot integrates sharding-jdbc to realize the separation of database and table and the separation of read and write

Introduction 1. Sharding key

Key fields for database / table split

Ex: the user table is split into different databases according to user_id modeling

2. Slicing algorithm

Please refer to: https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/configuration/built-in-algorithm/sharding

Accurate slicing algorithm

Range slicing algorithm

Compound slicing algorithm

Hint fragmentation algorithm

3. Sharding strategy (sharding key + sharding algorithm)

Line expression slicing strategy

Standard sharding strategy

Compound slicing strategy

Hint fragmentation strategy

Non-fragmentation strategy

You can view the source code org.apache.shardingsphere.core.yaml.config.sharding.YamlShardingStrategyConfiguration

Third, program realization

Warm Tip: for more information, please see the case demo source code

The complete application.yml configuration is posted here, and when each sharding policy is implemented, you can release the corresponding configuration.

# sharding-jdbc configuration spring: shardingsphere: # whether to enable SQL display props: sql: show: true # = = ↓ data source configuration ↓ = = datasource: names: ds-master-0,ds-slave-0-1 Ds-slave-1-2 # = = ↓ configuration the first master / slave library ↓ = # Master library 1 ds-master-0: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:3306/ds0?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL is in a higher version If you need to indicate whether to solve the SSL connection, add & useSSL=false username: root password: root # master library 1-slave library 1 ds-slave-0-1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:3307/ds0?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL in If the higher version needs to indicate whether to solve the SSL connection, add & useSSL=false username: root password: root # Master Library 1-Slave Library 2 ds-slave-0-2: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:3307/ds0?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL needs to indicate whether to solve the SSL connection in the higher version, then add & useSSL=false username: root password: root # = = ↓ configuration second master / slave library ↓ = # master library 2 ds-master-1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc: Mysql://127.0.0.1:3306/ds1?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL needs to indicate whether to solve the SSL connection in the higher version, then add & useSSL=false username: root password: root # Master Library 2-Slave Library 1 ds-slave-1-1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url : jdbc:mysql://127.0.0.1:3307/ds1?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL needs to indicate whether to solve SSL connection in the higher version, then add & useSSL=false username: root password: root # Master Library 2-Slave Library 2 ds-slave-1-2: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver Jdbc-url: jdbc:mysql://127.0.0.1:3307/ds1?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL needs to indicate whether to solve the SSL connection problem in the higher version, then add & useSSL=false username: root password: root sharding: # = ↓ read-write separation configuration ↓ = = master-slave-rules: ds-master-0: # Master library masterDataSourceName: ds-master-0 # slave library slaveDataSourceNames:-ds-slave-0-1-ds-slave-0-2 # load balancing algorithm for querying data from slave library currently there are two algorithms round_robin (polling) and random (random) # algorithm interface org.apache.shardingsphere.spi .masterslave.MasterSlaveLoadBalanceAlgorithm # implements class RandomMasterSlaveLoadBalanceAlgorithm and RoundRobinMasterSlaveLoadBalanceAlgorithm loadBalanceAlgorithmType: ROUND_ROBIN ds-master-1: masterDataSourceName: ds-master-1 slaveDataSourceNames:-ds-slave-1-1-ds-slave-1-2 loadBalanceAlgorithmType: ROUND_ROBIN # = = ↓ sub-library sub-table configuration ↓ = = tables: T_user: actual-data-nodes: ds-master-$- > {0.1} .t _ user$- > {0.1} # configuration attributes can be found in org.apache.shardingsphere.core.yaml.config.sharding.YamlShardingStrategyConfiguration # = ↓ line expression sharding policy ↓ = # using Groovy expressions in configuration Provides sharding operation support for the = and IN in the SQL statement. Only single sharding keys are supported. # # = ↓ sub-library ↓ = # database-strategy:# inline:# sharding-column: user_id # add data sub-library fields (according to which database to insert data into) # algorithm-expression: ds-master-$- > {user_id% 2} # according to User_id is split into different libraries # # = ↓ sub-table ↓ = # table-strategy:# inline:# sharding-column: sex # add data sub-table field (according to which table to insert data into) # algorithm-expression: tweak username-> {sex% 2 } # sharding algorithm expression = > split the model into different tables according to the user's gender # = ↓ standard sharding strategy ↓ = # precise slicing algorithm = > sql triggers computation logic when executing = and IN on the library / table key Otherwise, do not take sub-library / sub-table, full-database / full-table execution. # database-strategy:# standard:# sharding-column: user_id # keys used in sub-libraries # precise-algorithm-class-name: com.zhengqing.demo.config.sharding.precise.MyDbPreciseShardingAlgorithm # custom sub-library algorithm implementation class # table-strategy:# standard:# sharding-column: sex # additions According to the sub-table field (insert data into that table ex:sex according to the field) # precise-algorithm-class-name: com.zhengqing.demo.config.sharding.precise.MyTablePreciseShardingAlgorithm # custom sub-table algorithm to implement class # range slicing algorithm = > sql execute BETWEEN AND on the sub-library / sub-table key, >, =, > =, {user_id% 2} # split into different libraries according to user_id module # = ↓ sub-table ↓ = table-strategy: inline: sharding-column: sex # add data sub-table fields (according to which table the field inserts data into) algorithm-expression: tweak username-> {sex% 2} # sharding algorithm expression = > split into different tables according to the user's gender

2. Standard slicing strategy

A: accurate slicing algorithm

# precise sharding algorithm = > sql triggers the calculation logic when executing = and IN on the sub-library / sub-table key, otherwise the whole database / table is executed without going to the sub-library / sub-table. Database-strategy: standard: sharding-column: user_id # key used in sub-library precise-algorithm-class-name: com.zhengqing.demo.config.sharding.precise.MyDbPreciseShardingAlgorithm # custom sub-library algorithm implementation class table-strategy: standard: sharding-column: sex # add data sub-table field (insert data into that table ex:sex according to the field) precise-algorithm-class-name: com.zhengqing.demo.config.sharding. Precise.MyTablePreciseShardingAlgorithm # custom sharding algorithm implementation class @ Slf4jpublic class MyDbPreciseShardingAlgorithm implements PreciseShardingAlgorithm {/ * sharding policy * * @ param dbNameList all data sources * @ param shardingValue SQL passed shard value * @ return data source name * / @ Override public String doSharding (Collection dbNameList) PreciseShardingValue shardingValue) {log.info ("[MyDbPreciseShardingAlgorithm] SQL passed in shard value: [{}]", shardingValue) / / split into different libraries according to user_id Long userId = shardingValue.getValue (); for (String dbNameItem: dbNameList) {if (dbNameItem.endsWith (userId% 2)) {return dbNameItem;}} return null } @ Slf4jpublic class MyTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm {/ * sharding policy * * @ param tableNameList all table names * @ param shardingValue SQL shard value passed in during execution * @ return table name * / @ Override public String doSharding (Collection tableNameList, PreciseShardingValue shardingValue) {log.info ("[MyTablePreciseShardingAlgorithm] shard value passed during SQL execution: [{}]", shardingValue) / / split into different tables according to the user's gender Byte sex = shardingValue.getValue (); for (String tableNameItem: tableNameList) {if (tableNameItem.endsWith (String.valueOf (sex% 2) {return tableNameItem;}} return null;}}

B: range slicing algorithm

# range slicing algorithm = > sql executes BETWEEN AND, >, =, > =, = tableSize) {return tableNameResultList;}} return tableNameResultList;} on the library / table key

4. Hint slicing strategy

# = ↓ hint sharding policy ↓ = # Personalized configuration through Hint API = > you can see com.zhengqing.demo.service.impl.UserServiceImpl.listPageForHintdatabase-strategy: hint: algorithm-class-name: com.zhengqing.demo.config.sharding.hint.MyDbHintShardingAlgorithmtable-strategy: hint: algorithm-class-name: com.zhengqing.demo.config.sharding.hint.MyTableHintShardingAlgorithm@Slf4jpublic class MyDbHintShardingAlgorithm implements HintShardingAlgorithm {@ Override public Collection doSharding (Collection dbNameList) HintShardingValue hintShardingValue) {log.info ("[MyDbHintShardingAlgorithm] hintShardingValue: [{}]", hintShardingValue) Collection dbResultList = new ArrayList (); int dbSize = dbNameList.size (); for (String dbNameItem: dbNameList) {for (Integer shardingValue: hintShardingValue.getValues ()) {if (dbNameItem.endsWith (String.valueOf (shardingValue% 2) {dbResultList.add (dbNameItem) } if (dbResultList.size () > = dbSize) {return dbResultList;} return dbResultList;}} @ Slf4jpublic class MyTableHintShardingAlgorithm implements HintShardingAlgorithm {@ Override public Collection doSharding (Collection tableNameList, HintShardingValue hintShardingValue) {log.info ("[MyTableHintShardingAlgorithm] hintShardingValue: [{}]", hintShardingValue) Collection tableResultList = new ArrayList (); int tableSize = tableNameList.size (); Collection hintShardingValueValueList = hintShardingValue.getValues (); for (String tableName: tableNameList) {for (Integer shardingValue: hintShardingValueValueList) {if (tableName.endsWith (String.valueOf (shardingValue% 2) {tableResultList.add (tableName) } if (tableResultList.size () > = tableSize) {return tableResultList;} return tableResultList;}}

When in use, the dynamic trigger is as follows:

Public IPage listPageForHint () {/ / clear the previous rule, otherwise an error will be reported HintManager.clear (); / / HintManager API tool class instance HintManager hintManager = HintManager.getInstance (); / / Library = > mainly transfers the value value to MyDbHintShardingAlgorithm for logical sub-library processing hintManager.addDatabaseShardingValue ("t_user", 100); hintManager.addDatabaseShardingValue ("t_user", 1000) / / specify the sharding key of the table = > specify to look up t_user0 hintManager.addTableShardingValue ("t_user", 0); / / hintManager.addTableShardingValue ("t_user", 1); / / read-write separation forces you to read the master library to avoid delay caused by master-slave replication hintManager.setMasterRouteOnly () / / query data Page result = this.userMapper.selectPage (new Page (1,10), new LambdaQueryWrapper () .eq (User::getSex, "0"). Between (User::getUserId, 1L, 1000L)); / / clear rule hintManager.close (); return result } Thank you for your reading, the above is the content of "what is the method of SpringBoot integrating sharding-jdbc to achieve custom sub-library sub-table". After the study of this article, I believe you have a deeper understanding of what is the method of SpringBoot integrating sharding-jdbc to achieve custom sub-library sub-table, and the specific use situation still needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.