Basic concept

  1. Master library, the database for writing, ShardingSphere currently only supports single master libraries.
  2. Slave library, the database that the user queries, supports multiple slave libraries, and supports load balancing to spread the read pressure.
  3. Master/slave synchronization: The binlog of the master library is synchronized to the slave library through the IO thread to ensure the consistency of data between the master and slave libraries.

Reading and writing separation

No more BB, first look at the configuration file.

spring:
  shardingsphere:
    datasource:
      names: master,slave
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://xxxx:3306/master? useUnicode=true&characterEncoding=utf8
        username: xxxx
        password: xxxx
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://xxxx:3306/master? useUnicode=true&characterEncoding=utf8
        username: xxxx
        password: xxxx
      masterslave:
        load-balance-algorithm-type: round_robin              # Load balancing algorithm,
        name: ms
        master-data-source-name: master                        The primary database data source name
        slave-data-source-names: slave                    The name of the slave data source
      props:
        sql:
          show: true                                          # print SQL
Copy the code

Two databases, master and slave, represent the write library and the read library respectively. Then you can automatically configure it in Masterslave.

test

Write two controllers yourself, read them and write them, because in

      props:
        sql:
          show: true                                          # print SQL
Copy the code

Print logging is configured so that you can see whether you are using a read or write library at execution time.

The principle of

That’s what I love about development. In mybatis, an SQL query is executed in the following way: 1.

Connection conn = dataSource.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.executeQuery();
Copy the code

The ShardingSphere does its processing before execute.

MasterSlaveDataRouter.

@RequiredArgsConstructor
public final class MasterSlaveDataSourceRouter {
    
    private final MasterSlaveRule masterSlaveRule;
    
    /**
     * Route.
     * 
     * @param sqlStatement SQL statement
     * @return data source name
     */
    public String route(final SQLStatement sqlStatement) {
        if (isMasterRoute(sqlStatement)) {
            MasterVisitedManager.setMasterVisited();
            return masterSlaveRule.getMasterDataSourceName();
        }
        return masterSlaveRule.getLoadBalanceAlgorithm().getDataSource(
                masterSlaveRule.getName(), masterSlaveRule.getMasterDataSourceName(), new ArrayList<>(masterSlaveRule.getSlaveDataSourceNames()));
    }
    
    private boolean isMasterRoute(final SQLStatement sqlStatement) {
        returncontainsLockSegment(sqlStatement) || ! (sqlStatementinstanceof SelectStatement) || MasterVisitedManager.isMasterVisited() || HintManager.isMasterRouteOnly();
    }
    
    private boolean containsLockSegment(final SQLStatement sqlStatement) {
        return sqlStatement instanceofSelectStatement && ((SelectStatement) sqlStatement).getLock().isPresent(); }}Copy the code

Get the Connection source code.

public Connection getConnection(final String dataSourceName, final SQLType sqlType) throws SQLException {
        if (getCachedConnections().containsKey(dataSourceName)) {
            return getCachedConnections().get(dataSourceName);
        }
        DataSource dataSource = shardingContext.getShardingRule().getDataSourceRule().getDataSource(dataSourceName);
        Preconditions.checkState(null! = dataSource,"Missing the rule of %s in DataSourceRule", dataSourceName);
        String realDataSourceName;
        if (dataSource instanceof MasterSlaveDataSource) {
            NamedDataSource namedDataSource = ((MasterSlaveDataSource) dataSource).getDataSource(sqlType);
            realDataSourceName = namedDataSource.getName();
            if (getCachedConnections().containsKey(realDataSourceName)) {
                return getCachedConnections().get(realDataSourceName);
            }
            dataSource = namedDataSource.getDataSource();
        } else {
            realDataSourceName = dataSourceName;
        }
        Connection result = dataSource.getConnection();
        getCachedConnections().put(realDataSourceName, result);
        replayMethodsInvocation(result);
        return result;
    }
Copy the code

If the MasterSlaveDataSource type is entered.

public NamedDataSource getDataSource(final SQLType sqlType) {
    if (isMasterRoute(sqlType)) {
        DML_FLAG.set(true);
        return new NamedDataSource(masterDataSourceName, masterDataSource);
    }
    String selectedSourceName = masterSlaveLoadBalanceStrategy.getDataSource(name, masterDataSourceName, new ArrayList<>(slaveDataSources.keySet()));
    DataSource selectedSource = selectedSourceName.equals(masterDataSourceName) ? masterDataSource : slaveDataSources.get(selectedSourceName);
    Preconditions.checkNotNull(selectedSource, "");
    return new NamedDataSource(selectedSourceName, selectedSource);
}
private static boolean isMasterRoute(final SQLType sqlType) {
    returnSQLType.DQL ! = sqlType || DML_FLAG.get() || HintManagerHolder.isMasterRouteOnly(); }Copy the code

One interesting point is that if there is a write operation in a thread, then all subsequent SQL will go to the write library to prevent data inconsistency.

design

According to the source ideas, how to make a set of SAO operation? Will use the knowledge point, 1 AOP, 2 annotation. The idea is to determine whether the method is read or write before executing the SQL statement. Change the corresponding datasource. Annotations are used to explicitly identify the database used by the SQL in a method or class.

  1. Let’s make two annotations, let’s call them Master and Slave. For classes or methods that are marked, the specified database is used.
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE, ElementType.METHOD})
@Inherited
@Documented
public @interface Master {
}
Copy the code
  1. Use an interceptor to intercept the Dao before it executes SQL, using annotations and methods to determine whether it is a walk or write library.
@Pointcut("execution(* com.xx.xx.dao.. *. * (..) )"
public void pointcut(a) {}@Before("pointcut()")
public void before(JoinPoint joinPoint) {
    MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature();
    Class class = methodSignature.getDeclaringType();
    Method method = methodSignature.getMethod();

    if(method. IsAnnotationPresent (Master class)) {go main library}else if(method. IsAnnotationPresent (Slave class) from library} {walkif(class. IsAnnotationPresent (Master class)) {go main library}else if(class. IsAnnotationPresent (Slave. Class)) {walk from the library} String name = method. The getName ();if (name.contains("select") || name.contains("query") || name.contains("find") {go to the main library}else{go from library}}Copy the code

About this idea, students with ideas, please leave a comment in the comment section to discuss.