shardingsphere分库分表项目实践3-分库分表算法原理
整体说明:
ShardingSphere的数据库表分片算法原理主要基于分片字段、分片算法以及分片策略来实现。
分片算法是用于实现数据分片的具体逻辑。ShardingSphere支持多种分片算法,包括取模、哈希、范围、时间等常用算法。这些算法可以根据分片键的值来计算数据应该存储在哪个数据库或表中。支持自定义分片算法。
分片策略是在配置文件中配置的,就是分片键和分片算法的组合,它决定了数据分片的整体规则。在ShardingSphere中,可以通过配置分片策略来指定分片键、分片算法以及分片后的数据节点等信息。
分片策略配置详解:
分片策略配置文件主要包括2部分 dataSources 和 rules :
dataSources:
数据源配置部分,分库后需要配置多个数据源,数据源名称需要遵循自己设计的数据库分片规则,比如ds_0 中 ds_ 是前缀, 0 代表第一个数据库。
rules:
分片策略, 需要逐个表配置,以t_user表为例:
1. actualDataNodes: ds_${0..1}.t_user_${0..31}
这一行配置的意思是 t_user 一共分了32个表(t_user_0 到 t_user_31) , 分布在ds_0 ds_1 这两个库中,这两个库就是dataSources 配置的数据源名称。
2. databaseStrategy 分库路由策略:
解析 sql 中 t_user表的username这个字段的值,然后按照 user_database_hash_mod 算法分片。
user_database_hash_mod 算法类型是 CLASS_BASED, 是自己实现的算法,具体逻辑在CustomDbHashModShardingAlgorithm.java 代码中
CustomDbHashModShardingAlgorithm算法原理:
a. 将 username hash 成数值
b. 然后对32 取模,取模的值就是t_user表的后缀,对应实际的表
c. 取模的余数除以16,就是0或者1, 就对应具体的两个数据库了。
举例说明:
username=‘zhangsan’, 假设hash值为38, 那么 38%32=6 ,那么张三对应的表是 t_user_6,
对应的库是: 6/16=0 , ds_0 上。
3. tableStrategy :
shardingColumn: username shardingAlgorithmName: user_table_hash_mod
直接对username hash,然后对32取模, shardingsphere内置算法。
完整配置样例:
dataSources:
ds_0:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://127.0.0.1:3306/12306_user_0?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true&allowMultiQueries=true&serverTimezone=Asia/Shanghai
username: root
password: 123456
ds_1:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://127.0.0.1:3306/12306_user_1?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true&allowMultiQueries=true&serverTimezone=Asia/Shanghai
username: root
password: 123456
rules:
- !SHARDING
tables:
t_user:
actualDataNodes: ds_${0..1}.t_user_${0..31}
databaseStrategy:
standard:
shardingColumn: username
shardingAlgorithmName: user_database_hash_mod
tableStrategy:
standard:
shardingColumn: username
shardingAlgorithmName: user_table_hash_mod
t_passenger:
actualDataNodes: ds_${0..1}.t_passenger_${0..31}
databaseStrategy:
standard:
shardingColumn: username
shardingAlgorithmName: passenger_database_hash_mod
tableStrategy:
standard:
shardingColumn: username
shardingAlgorithmName: passenger_table_hash_mod
t_user_mail:
actualDataNodes: ds_${0..1}.t_user_mail_${0..31}
databaseStrategy:
standard:
shardingColumn: mail
shardingAlgorithmName: t_user_mail_database_hash_mod
tableStrategy:
standard:
shardingColumn: mail
shardingAlgorithmName: t_user_mail_table_hash_mod
t_user_phone:
actualDataNodes: ds_${0..1}.t_user_phone_${0..31}
databaseStrategy:
standard:
shardingColumn: phone
shardingAlgorithmName: t_user_phone_database_hash_mod
tableStrategy:
standard:
shardingColumn: phone
shardingAlgorithmName: t_user_phone_table_hash_mod
shardingAlgorithms:
user_database_hash_mod:
type: CLASS_BASED
props:
sharding-count: 32
table-sharding-count: 16
strategy: standard
algorithmClassName: org.opengoofy.index12306.framework.starter.database.algorithm.sharding.CustomDbHashModShardingAlgorithm
passenger_database_hash_mod:
type: CLASS_BASED
props:
sharding-count: 32
table-sharding-count: 16
strategy: standard
algorithmClassName: org.opengoofy.index12306.framework.starter.database.algorithm.sharding.CustomDbHashModShardingAlgorithm
t_user_mail_database_hash_mod:
type: CLASS_BASED
props:
sharding-count: 32
table-sharding-count: 16
strategy: standard
algorithmClassName: org.opengoofy.index12306.framework.starter.database.algorithm.sharding.CustomDbHashModShardingAlgorithm
t_user_phone_database_hash_mod:
type: CLASS_BASED
props:
sharding-count: 32
table-sharding-count: 16
strategy: standard
algorithmClassName: org.opengoofy.index12306.framework.starter.database.algorithm.sharding.CustomDbHashModShardingAlgorithm
passenger_table_hash_mod:
type: HASH_MOD
props:
sharding-count: 32
t_user_mail_table_hash_mod:
type: HASH_MOD
props:
sharding-count: 32
t_user_phone_table_hash_mod:
type: HASH_MOD
props:
sharding-count: 32
user_table_hash_mod:
type: HASH_MOD
props:
sharding-count: 32
- !ENCRYPT
tables:
t_user:
columns:
id_card:
cipherColumn: id_card
encryptorName: common_encryptor
phone:
cipherColumn: phone
encryptorName: common_encryptor
mail:
cipherColumn: mail
encryptorName: common_encryptor
address:
cipherColumn: address
encryptorName: common_encryptor
t_passenger:
columns:
id_card:
cipherColumn: id_card
encryptorName: common_encryptor
phone:
cipherColumn: phone
encryptorName: common_encryptor
queryWithCipherColumn: true
encryptors:
common_encryptor:
type: AES
props:
aes-key-value: d6oadClrrb9A3GWo
props:
sql-show: true