A-Zero

# 基本介绍

分库分表可以提升系统的稳定性跟负载能力，不存在单库/单表大数据。没有高并发的性能瓶颈，增加系统可用性。缺点是分库表无法join，只能通过接口方式解决，提高了系统复杂度。

# shardingsphere 数据库中间件

  • Sharding-JDBC最早是当当网内部使用的分库分表框架，2017年对外开源现已更名为ShardingSphere。
  • ShardingSphere是一套开源的分布式数据库中间件，由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar3款相互独立的产品组成。
  • 他们均提供标准化的数据分片、读写分离、多数据副本、数据加密、影子库压测、分布式事务和数据库治理等功能，可适用于如Java同构、异构语言、容器、云原生等各种多样化的应用场景。

# shardingsphere 特点

  • 适用于任何基于Java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC
  • 基于任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等
  • 支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer和PostgreSQL

# 主要的配置类

sharding-sphere主要的配置有三类：

MasterSlaveRuleConfiguration // 主备数据源配置
ShardingRuleConfiguration // 分片规则配置
TableRuleConfiguration // 数据表配置

# 主备数据源配置

从配置上看，符合一主多从的结构，主数据源只有一个，从数据源有多个

public final class MasterSlaveRuleConfiguration {
    //名称
    private final String name;
    //主数据源
    private final String masterDataSourceName;
     //从数据源
    private final Collection<String> slaveDataSourceNames;
     //负载均衡算法
    private MasterSlaveLoadBalanceAlgorithm loadBalanceAlgorithm;
}

MasterSlaveLoadBalanceAlgorithm算法有两种实现：

public interface MasterSlaveLoadBalanceAlgorithm {
    String getDataSource(String name, 
	                     String masterDataSourceName, 
						 List<String> slaveDataSourceNames);
}
//随机算法
public final class RandomMasterSlaveLoadBalanceAlgorithm 
                          implements MasterSlaveLoadBalanceAlgorithm {
    
    @Override
    public String getDataSource(final String name, 
	                            final String masterDataSourceName, 
								final List<String> slaveDataSourceNames) {
        return slaveDataSourceNames.get(new Random().nextInt(slaveDataSourceNames.size()));
    }
}
//轮询算法
public final class RoundRobinMasterSlaveLoadBalanceAlgorithm 
                            implements MasterSlaveLoadBalanceAlgorithm {
    
    private static final ConcurrentHashMap<String, AtomicInteger> COUNT_MAP = 
	                                                              new ConcurrentHashMap<>();
    
    @Override
    public String getDataSource(final String name, 
	                            final String masterDataSourceName, 
								final List<String> slaveDataSourceNames) {
        AtomicInteger count = COUNT_MAP.containsKey(name) ?  
		                      COUNT_MAP.get(name) : new AtomicInteger(0);
        COUNT_MAP.putIfAbsent(name, count);
        count.compareAndSet(slaveDataSourceNames.size(), 0);
        return slaveDataSourceNames.get(count.getAndIncrement() 
		                                % slaveDataSourceNames.size());
    }
}

# 分片规则配置

public final class ShardingRuleConfiguration {
    //默认数据源名称
    private String defaultDataSourceName;
    //表规则配置
    private Collection<TableRuleConfiguration> tableRuleConfigs = new LinkedList<>();
    //相同表分片规则的组，如果表分片规则相同，则可以放在一个组里。
    private Collection<String> bindingTableGroups = new LinkedList<>();
    //默认数据库的分片算法配置
    private ShardingStrategyConfiguration defaultDatabaseShardingStrategyConfig;
    //默认表的分片算法配置
    private ShardingStrategyConfiguration defaultTableShardingStrategyConfig;
    //默认键的生成工具类
    private KeyGenerator defaultKeyGenerator;
    //主备配置信息
    private Collection<MasterSlaveRuleConfiguration> masterSlaveRuleConfigs = 
	                                                            new LinkedList<>();
}

分片规则

public final class ShardingRule {
    //分片规则配置
    private final ShardingRuleConfiguration shardingRuleConfig;
    //数据源信息
    private final ShardingDataSourceNames shardingDataSourceNames;
    //表规则
    private final Collection<TableRule> tableRules = new LinkedList<>();
    //相同分片规则的表规则
    private final Collection<BindingTableRule> bindingTableRules = new LinkedList<>();
     //数据库配置
    private final ShardingStrategy defaultDatabaseShardingStrategy;
    //默认表分片规则
    private final ShardingStrategy defaultTableShardingStrategy;
    //默认字段生成器
    private final KeyGenerator defaultKeyGenerator;
    //主备配置信息
    private final Collection<MasterSlaveRule> masterSlaveRules = new LinkedList<>();
}

# 表配置信息

public final class TableRuleConfiguration {
    //逻辑表名
    private String logicTable;
    //真实的数据节点名称
    private String actualDataNodes;
    //数据库分片算法配置
    private ShardingStrategyConfiguration databaseShardingStrategyConfig;
    //表分片算法配置
    private ShardingStrategyConfiguration tableShardingStrategyConfig;
    //自动生成键的名称
    private String keyGeneratorColumnName;
    //自动生成键的工具类
    private KeyGenerator keyGenerator;
    
    private String logicIndex;
}

表分片规则，logicTable为逻辑实体表的表名，DataNode为实际存储的节点表的信息

public final class TableRule {
    //逻辑实体表
    private final String logicTable;
    //实际存储的表节点数据
    private final List<DataNode> actualDataNodes;
    
    private final ShardingStrategy databaseShardingStrategy;
    
    private final ShardingStrategy tableShardingStrategy;
    //默认生成值的表字段名称
    private final String generateKeyColumn;
    //生成器
    private final KeyGenerator keyGenerator;
    
    private final String logicIndex;
}

# Sharding-JDBC相关使用

# 数据分配

  • 引入Maven依赖
<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>${sharding-sphere.version}</version>
</dependency>

<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>${sharding-sphere.version}</version>
</dependency>
  • 基于Java编码的规则配置

Sharding-JDBC的分库分表通过规则配置描述，以下例子是根据user_id取模分库, 且根据order_id取模分表的两库两表的配置。

// 配置真实数据源
Map<String, DataSource> dataSourceMap = new HashMap<>();

// 配置第一个数据源
BasicDataSource dataSource1 = new BasicDataSource();
dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
dataSource1.setUrl("jdbc:mysql://localhost:3306/ds0");
dataSource1.setUsername("root");
dataSource1.setPassword("");
dataSourceMap.put("ds0", dataSource1);

// 配置第二个数据源
BasicDataSource dataSource2 = new BasicDataSource();
dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
dataSource2.setUrl("jdbc:mysql://localhost:3306/ds1");
dataSource2.setUsername("root");
dataSource2.setPassword("");
dataSourceMap.put("ds1", dataSource2);

// 配置Order表规则
TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();
orderTableRuleConfig.setLogicTable("t_order");
orderTableRuleConfig.setActualDataNodes("ds${0..1}.t_order${0..1}");

// 配置分库策略
orderTableRuleConfig.setDatabaseShardingStrategyConfig(
                     new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}")
					 );
// 配置分表策略
orderTableRuleConfig.setTableShardingStrategyConfig(
          new InlineShardingStrategyConfiguration("order_id", "t_order${order_id % 2}")
		            );

// 配置分片规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

// 省略配置order_item表规则...
// ...

// 获取数据源对象
DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, 
                                                                   shardingRuleConfig, 
																   new ConcurrentHashMap(), 
																   new Properties());
  • 基于Yaml的规则配置
dataSources:
  ds0: !!org.apache.commons.dbcp.BasicDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ds0
    username: root
    password: 
  ds1: !!org.apache.commons.dbcp.BasicDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ds1
    username: root
    password: 

tables:
  t_order: 
    actualDataNodes: ds${0..1}.t_order${0..1}
    databaseStrategy: 
      inline:
        shardingColumn: user_id
        algorithmInlineExpression: ds${user_id % 2}
    tableStrategy: 
      inline:
        shardingColumn: order_id
        algorithmInlineExpression: t_order${order_id % 2}
  t_order_item: 
    actualDataNodes: ds${0..1}.t_order_item${0..1}
    databaseStrategy: 
      inline:
        shardingColumn: user_id
        algorithmInlineExpression: ds${user_id % 2}
    tableStrategy: 
      inline:
        shardingColumn: order_id
        algorithmInlineExpression: t_order_item${order_id % 2}

# 读写分离

  • 引入Maven依赖
<!-- for spring boot -->
<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>${sharding-sphere.version}</version>
</dependency>

<!-- for spring namespace -->
<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>${sharding-sphere.version}</version>
</dependency>
  • 基于Java编码的规则配置
// 配置真实数据源
Map<String, DataSource> dataSourceMap = new HashMap<>();

// 配置主库
BasicDataSource masterDataSource = new BasicDataSource();
masterDataSource.setDriverClassName("com.mysql.jdbc.Driver");
masterDataSource.setUrl("jdbc:mysql://localhost:3306/ds_master");
masterDataSource.setUsername("root");
masterDataSource.setPassword("");
dataSourceMap.put("ds_master", masterDataSource);

// 配置第一个从库
BasicDataSource slaveDataSource1 = new BasicDataSource();
slaveDataSource1.setDriverClassName("com.mysql.jdbc.Driver");
slaveDataSource1.setUrl("jdbc:mysql://localhost:3306/ds_slave0");
slaveDataSource1.setUsername("root");
slaveDataSource1.setPassword("");
dataSourceMap.put("ds_slave0", slaveDataSource1);

// 配置第二个从库
BasicDataSource slaveDataSource2 = new BasicDataSource();
slaveDataSource2.setDriverClassName("com.mysql.jdbc.Driver");
slaveDataSource2.setUrl("jdbc:mysql://localhost:3306/ds_slave1");
slaveDataSource2.setUsername("root");
slaveDataSource2.setPassword("");
dataSourceMap.put("ds_slave1", slaveDataSource2);

// 配置读写分离规则
MasterSlaveRuleConfiguration masterSlaveRuleConfig = 
                new MasterSlaveRuleConfiguration("ds_master_slave", 
							                     "ds_master", 
												 Arrays.asList("ds_slave0", "ds_slave1"));

// 获取数据源对象
DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(createDataSourceMap() 
                                                                     ,masterSlaveRuleConfig
															 ,new HashMap<String, Object>()
																	      ,new Properties()
																	  );
  • 基于Yaml的规则配置
dataSources:
  ds_master: !!org.apache.commons.dbcp.BasicDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ds_master
    username: root
    password: 
  ds_slave0: !!org.apache.commons.dbcp.BasicDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ds_slave0
    username: root
    password:
  ds_slave1: !!org.apache.commons.dbcp.BasicDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ds_slave1
    username: root
    password: 

masterSlaveRule:
  name: ds_ms
  masterDataSourceName: ds_master
  slaveDataSourceNames: [ds_slave0, ds_slave1]
  
  props:
      sql.show: true
  configMap:
      key1: value1

# 强制路由

ShardingSphere使用ThreadLocal管理分片键值进行Hint强制路由。可以通过编程的方式向HintManager中添加分片条件，该分片条件仅在当前线程内生效。

  • 分片字段不存在SQL中、数据库表结构中，而存在于外部业务逻辑。因此，通过Hint实现外部指定分片结果进行数据操作
  • 强制在主库进行某些数据操作

← 授权认证 →