具体简介就不多逼逼,直接上代码。
private ShardingRuleConfiguration buildShardingRuleConfiguration(){
/**
* 配置表规则
*/
TableRuleConfiguration orderTableRuleConfiguration = new TableRuleConfiguration("t_order","db${0..2}.t_order_${1..2}");
// 配置分库策略(groovy表达式配置db规则)
orderTableRuleConfiguration.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id","db${user_id % 3}"));
// 配置分表策略(groovy表达式配置表规则)
orderTableRuleConfiguration.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id","t_order_${order_id % 2 + 1}"));
TableRuleConfiguration itemTableRuleConfiguration = new TableRuleConfiguration("t_item","db${0..2}.t_item_${1..2}");
itemTableRuleConfiguration.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id","db${user_id % 3}"));
itemTableRuleConfiguration.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("item_id","t_item_${item_id % 2 + 1}"));
List<TableRuleConfiguration> tableRuleConfigurationList = new ArrayList<>();
tableRuleConfigurationList.add(orderTableRuleConfiguration);
tableRuleConfigurationList.add(itemTableRuleConfiguration);
/**
* 分片规则
*/
ShardingRuleConfiguration shardingRuleConfiguration = new ShardingRuleConfiguration();
shardingRuleConfiguration.getTableRuleConfigs().addAll(tableRuleConfigurationList);
/**
* 数据脱敏策略
*/
shardingRuleConfiguration.setEncryptRuleConfig(buildEncryptRuleConfiguration());
log.info("分片策略构建完成");
return shardingRuleConfiguration;
}
分片策略的配置采用:Inline表达式分片策略 :InlineShardingStrategyConfiguration
groovy表达式配置db规则,照猫画虎你应该懂。
分表分库的常见问题及性能瓶颈:(解决问题的强度决定组件选型)
1. 多表之间关联查询,如何组合各类表的组合,可以优化查询?
2. 查询需要涉及多张表,如何进行排序、分页?
3. 数据量达到5000w级大量数据,如何优化?
常见解决方案:
1. 问题1,sharding-jdbc优化方式:尽量使用分片键(代码中order_id)作为查询条件,在关联组合之前,会先根据分片键分析得出分片键命中的路由表。这样可以避免出现笛卡尔积。
2. 问题2,使用雪花算法,自增式主键,配合业务可以达到一定的效果
3. 问题3,常用的是:使用索引查询目标主键,集合聚族索引的特点再查询主键所在记录中的其他field。(这里主要是避免回表,好像还是个有名的方案,方案名称忘了-.-)
public EncryptRuleConfiguration buildEncryptRuleConfiguration(){
//自带aes算法需要
Properties props = new Properties();
props.setProperty("aes.key.value", aeskey);
EncryptorRuleConfiguration encryptorConfig = new EncryptorRuleConfiguration("AES", props);
//自定义算法
//props.setProperty("qb.finance.aes.key.value", aeskey);
//EncryptorRuleConfiguration encryptorConfig = new EncryptorRuleConfiguration("QB-FINANCE-AES", props);
EncryptRuleConfiguration encryptRuleConfig = new EncryptRuleConfiguration();
encryptRuleConfig.getEncryptors().put("aes", encryptorConfig);
/**
* START: card_info 表的脱敏配置
*/
{
EncryptColumnRuleConfiguration columnConfig1 = new EncryptColumnRuleConfiguration("pay_no", "pay_name", "", "aes");
Map<String, EncryptColumnRuleConfiguration> columnConfigMaps = new HashMap<>();
columnConfigMaps.put("pay_name", columnConfig1);
EncryptTableRuleConfiguration tableConfig = new EncryptTableRuleConfiguration(columnConfigMaps);
encryptRuleConfig.getTables().put("t_item", tableConfig);
}
/**
* START: pay_order 表的脱敏配置
{
EncryptColumnRuleConfiguration columnConfig1 = new EncryptColumnRuleConfiguration("", "card_no", "", "aes");
Map<String, EncryptColumnRuleConfiguration> columnConfigMaps = new HashMap<>();
columnConfigMaps.put("card_no", columnConfig1);
EncryptTableRuleConfiguration tableConfig = new EncryptTableRuleConfiguration(columnConfigMaps);
encryptRuleConfig.getTables().put("pay_order", tableConfig);
}
*/
log.info("脱敏配置构建完成");
return encryptRuleConfig;
}
java代码形式配置数据脱敏策略
常用概念解释:
plainColumn:明文列
cipherColumn:密文列
assistedQueryColumn:辅助列(官网解释:辅助查询字段,针对ShardingQueryAssistedEncryptor类型的加解密器进行辅助查询)
简述使用:可能一些地方会提出 logicColumn:逻辑处理列 的概念,我的理解是密文列就充当此列。明文列的存在,一般是用于公司得一些骚操作(比如:说着脱敏,但后台却做着,导出用户信息,电话交流情感...)。为什么将数据脱敏在这里说呢?首先,这种场景是客观存在的。其次,官网4.x只提供了springboot-yml的方式配置,也看到许多误导配置。比如如何将这两种同时使用?
错误1:(出现sharding-jdbc加载链接地址中所有库的表信息)
@Bean(name = "shardingDataSource")
@Qualifier("shardingDataSource")
public DataSource getShardingDataSource(){
DataSource dataSource = null;
try{
/**
* 打印sql日志信息: ConfigurationPropertyKey
*/
Properties properties = new Properties();
properties.setProperty("sql.show","true");
properties.setProperty("sql.simple","true");
/**
* 1. 配置分片策略
* 2. 配置数据脱敏策略
*/
dataSource = ShardingDataSourceFactory.createDataSource(buildDataSourceMap(),buildShardingRuleConfiguration(),properties);
dataSource = EncryptDataSourceFactory.createDataSource(dataSource,buildEncryptRuleConfiguration(),new Properties());
}catch (Exception e){
e.printStackTrace();
}
return dataSource;
}
错误2:(提示缺少sqlSessionFactory类似信息)
@Bean(name = "shardingDataSource")
@Qualifier("shardingDataSource")
public DataSource getShardingDataSource(){
DataSource dataSource = null;
try{
/**
* 打印sql日志信息: ConfigurationPropertyKey
*/
Properties properties = new Properties();
properties.setProperty("sql.show","true");
properties.setProperty("sql.simple","true");
/**
* 1. 配置分片策略
* 2. 配置数据脱敏策略
*/
dataSource = ShardingDataSourceFactory.createDataSource(buildDataSourceMap(),buildShardingRuleConfiguration(),properties);
}catch (Exception e){
e.printStackTrace();
}
return dataSource;
}
@Bean(name = "druidDataSource")
public DataSource druidDataSource(){
DataSource dataSource = null;
try{
/**
* 打印sql日志信息: ConfigurationPropertyKey
*/
Properties properties = new Properties();
properties.setProperty("sql.show","true");
properties.setProperty("sql.simple","true");
/**
* 1. 配置分片策略
* 2. 配置数据脱敏策略
*/
dataSource = EncryptDataSourceFactory.createDataSource(getShardingDataSource(),buildEncryptRuleConfiguration(),new Properties());
}catch (Exception e){
e.printStackTrace();
}
return dataSource;
}
正确操作:(使用第一段代码中,在分片策略中指定数据加密测略 >.> 能找到吧)
private Map<String,DataSource> buildDataSourceMap(){
Map<String,DataSource> dataSourceMap = new HashMap<>(3);
/**
* 用一个库,模拟多个数据源
*/
final String urlPrefix = "jdbc:mysql://xxx:3306/";
final String uname = "xxx";
final String psw = "xxx";
DruidDataSource dataSource0 = createDruidDataSource(urlPrefix+"db0",uname,psw);
dataSourceMap.put("db0", dataSource0);
DruidDataSource dataSource1 = createDruidDataSource(urlPrefix+"db1",uname,psw);
dataSourceMap.put("db1", dataSource1);
DruidDataSource dataSource2 = createDruidDataSource(urlPrefix+"db2",uname,psw);
dataSourceMap.put("db2", dataSource2);
return dataSourceMap;
}
END:
1. 对于分库分表类常见问题解决方案,还往小伙伴们不吝赐教 +.+
2. 这里对于常用场景还缺少:读写分离、自定义加密等等,后续如果有时间一并奉上。
3. 4.x数据脱敏官网配置地址:https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-proxy/configuration/#%E6%95%B0%E6%8D%AE%E8%84%B1%E6%95%8F
4. 分片策略详解:https://www.cnblogs.com/mr-yang-localhost/p/8313360.html#_lab2_1_2