07、ShardingJDBC实战：SQL执行-程序员快看-教程，程序员编程资料站

一序

之前整理了SQL的路由，SQL的改写，按照jdbc的过程，接下来就是SQL执行了。

SQL执行的源码在core模块下，下图所示。

下图是查询流程，绿框是SQL执行过程。

二 ExecutorEngine

2.1 ListeningExecutorService

SQL执行引擎，基于guava工具类提供的继承自 ExecutorService 的线程服务接口，提供创建 ListenableFuture 功能。ListenableFuture 接口，继承 Future 接口，好处是对比jdk自带的future简化了开发。

（这是一个通用的知识点，可以网上看下相关资料。）

 public ShardingDataSource(final ShardingRule shardingRule, final Map<String, Object> configMap, final Properties props) throws SQLException {
        super(shardingRule.getDataSourceMap().values());
        if (!configMap.isEmpty()) {
            ConfigMapContext.getInstance().getShardingConfig().putAll(configMap);
        }
        shardingProperties = new ShardingProperties(null == props ? new Properties() : props);
        int executorSize = shardingProperties.getValue(ShardingPropertiesConstant.EXECUTOR_SIZE);
        executorEngine = new ExecutorEngine(executorSize);
        boolean showSQL = shardingProperties.getValue(ShardingPropertiesConstant.SQL_SHOW);
        shardingContext = new ShardingContext(shardingRule, getDatabaseType(), executorEngine, showSQL);
    }

其中EXECUTOR_SIZE默认是系统可以用CPU核数。

EXECUTOR_SIZE("executor.size", String.valueOf(Runtime.getRuntime().availableProcessors()), int.class);

    public ExecutorEngine(final int executorSize) {
        executorService = MoreExecutors.listeningDecorator(new ThreadPoolExecutor(
                executorSize, executorSize, 0, TimeUnit.MILLISECONDS, new LinkedBlockingQueue<Runnable>(), new ThreadFactoryBuilder().setDaemon(true).setNameFormat("ShardingJDBC-%d").build()));
        MoreExecutors.addDelayedShutdownHook(executorService, 60, TimeUnit.SECONDS);
    }

1当然也就是创建的线程池大小。

2一个分片数据源( ShardingDataSource ) 独占一个 SQL执行引擎( ExecutorEngine )。

3 MoreExecutors.listeningDecorator() 创建 ListeningExecutorService，这样 #submit()，#invokeAll() 可以返回 ListenableFuture。

4 MoreExecutors#addDelayedShutdownHook()，应用关闭时，等待所有任务全部完成再关闭。默认配置等待时间为 60 秒

2.2 close

 @Override
    public void close() {
        executorService.shutdownNow();
        try {
            executorService.awaitTermination(5, TimeUnit.SECONDS);
        } catch (final InterruptedException ignored) {
        }
        if (!executorService.isTerminated()) {
            throw new ShardingJdbcException("ExecutorEngine can not been terminated");
        }
    }

真正的停止线程池还是调用了ExecutorService

   #MoreExecutors
  private final ExecutorService delegate;
      public List<Runnable> shutdownNow() {
      return delegate.shutdownNow();
    }

 public List<Runnable> shutdownNow() {
        List<Runnable> tasks;
        final ReentrantLock mainLock = this.mainLock;
        mainLock.lock();
        try {
            checkShutdownAccess();
            advanceRunState(STOP);
            interruptWorkers();
            tasks = drainQueue();
        } finally {
            mainLock.unlock();
        }
        tryTerminate();
        return tasks;
    }

shutdownNow() 底层实现是尝试使用 Thread.interrupt() 打断正在执行中的任务，未执行的任务不再执行。

因为 #shutdownNow() 打断不是立即结束，需要一个过程，因此这里等待了 5 秒，等待 5 秒后，线程池不一定已经关闭，此时抛出异常给上层。

2.3 执行SQL

ExecutorEngine 对外暴露 #executeStatement()，#executePreparedStatement()，#executeBatch()

三个方法分别提供给 StatementExecutor、PreparedStatementExecutor、BatchPreparedStatementExecutor 调用。而这三个方法，内部调用的都是 #execute() 私有方法。

	public <T> List<T> executeStatement(final SQLType sqlType, final Collection<StatementUnit> statementUnits, final ExecuteCallback<T> executeCallback) throws SQLException {
        return execute(sqlType, statementUnits, Collections.<List<Object>>emptyList(), executeCallback);
    }
    public <T> List<T> executePreparedStatement(
            final SQLType sqlType, final Collection<PreparedStatementUnit> preparedStatementUnits, final List<Object> parameters, final ExecuteCallback<T> executeCallback) throws SQLException {
        return execute(sqlType, preparedStatementUnits, Collections.singletonList(parameters), executeCallback);
    }
     public List<int[]> executeBatch(
            final SQLType sqlType, final Collection<BatchPreparedStatementUnit> batchPreparedStatementUnits, 
            final List<List<Object>> parameterSets, final ExecuteCallback<int[]> executeCallback) throws SQLException {
        return execute(sqlType, batchPreparedStatementUnits, parameterSets, executeCallback);
    }

#execute() 执行过程大体流程如下图：

 private  <T> List<T> execute(
            final SQLType sqlType, final Collection<? extends BaseStatementUnit> baseStatementUnits, 
            final List<List<Object>> parameterSets, final ExecuteCallback<T> executeCallback) throws SQLException {
        if (baseStatementUnits.isEmpty()) {
            return Collections.emptyList();
        }
        //@1生成event
        OverallExecutionEvent event = new OverallExecutionEvent(sqlType, baseStatementUnits.size());
        //EventBus 发布 EventExecutionType.BEFORE_EXECUTE
        EventBusInstance.getInstance().post(event);
        // 第一个任务分离出来
        Iterator<? extends BaseStatementUnit> iterator = baseStatementUnits.iterator();
        BaseStatementUnit firstInput = iterator.next();
        // @2除第一个任务之外的任务异步执行
        ListenableFuture<List<T>> restFutures = asyncExecute(sqlType, Lists.newArrayList(iterator), parameterSets, executeCallback);
        T firstOutput;
        List<T> restOutputs;
        try {
              //@3 第一个任务同步执行
            firstOutput = syncExecute(sqlType, firstInput, parameterSets, executeCallback);
            // @4等待第二个任务开始所有 SQL任务完成
            restOutputs = restFutures.get();
            //CHECKSTYLE:OFF
        } catch (final Exception ex) {
            //CHECKSTYLE:ON
            event.setException(ex);
            event.setEventExecutionType(EventExecutionType.EXECUTE_FAILURE);
             //@5 EventBus 发布 EventExecutionType.EXECUTE_FAILURE
            EventBusInstance.getInstance().post(event);
            ExecutorExceptionHandler.handleException(ex);
            return null;
        }
        event.setEventExecutionType(EventExecutionType.EXECUTE_SUCCESS);
        //@6 EventBus 发布 EventExecutionType.EXECUTE_SUCCESS
        EventBusInstance.getInstance().post(event);
        List<T> result = Lists.newLinkedList(restOutputs);
        result.add(0, firstOutput);//*将第一个任务同步执行结果与其他任务异步执行结果合并就是最终的结果
        return result;
    }

@1，不管成不成功，先插入事务表，成功再删。
@2，除第一个任务之外的Guava的异步执行方法。
@3，第一个任务同步执行
@4，获取异步执行结果
@5，异常出现了，投递消息去重试了

@6，正常，投递消息，清除事务库

这块有两个知识点：

1就是guava的异步发布的eventbus.

2拆分任务为第一个同步执行，而其他任务异步执行的原因：优化那些只会路由到一张实际表的SQL为同步执行，减少线程开销—-因为分库分表后，承担绝大部分流量的API底层的SQL条件都会有sharding column，只会路由到一张实际表，这类SQL同步执行即可，不需要异步执行。

 private <T> ListenableFuture<List<T>> asyncExecute(
            final SQLType sqlType, final Collection<BaseStatementUnit> baseStatementUnits, final List<List<Object>> parameterSets, final ExecuteCallback<T> executeCallback) {
         //构造一个存放异步执行后的结果的list
        List<ListenableFuture<T>> result = new ArrayList<>(baseStatementUnits.size());
        final boolean isExceptionThrown = ExecutorExceptionHandler.isExceptionThrown();
        final Map<String, Object> dataMap = ExecutorDataMap.getDataMap();
        for (final BaseStatementUnit each : baseStatementUnits) {
             // 提交线程池【异步】执行SQL任务
            result.add(executorService.submit(new Callable<T>() {
                
                @Override
                public T call() throws Exception {
                    return executeInternal(sqlType, each, parameterSets, executeCallback, isExceptionThrown, dataMap);
                }
            }));
        }
        //guava的方法--将所有异步执行结果转为list类型
        return Futures.allAsList(result);
    }

看完异步的代码，在看上面的@4 restFutures.get(); 当所有任务都成功时，返回所有任务执行结果；当任何一个任务失败时，马上抛出异常，无需等待其他任务执行完成。就觉得guava真是简化开发的利器。好厉害。

同步执行核心代码：

  private <T> T syncExecute(final SQLType sqlType, final BaseStatementUnit baseStatementUnit, final List<List<Object>> parameterSets, final ExecuteCallback<T> executeCallback) throws Exception {
        return executeInternal(sqlType, baseStatementUnit, parameterSets, executeCallback, ExecutorExceptionHandler.isExceptionThrown(), ExecutorDataMap.getDataMap());
    }

可见同步，异步都是底层调用了executeInternal。

private <T> T executeInternal(final SQLType sqlType, final BaseStatementUnit baseStatementUnit, final List<List<Object>> parameterSets, final ExecuteCallback<T> executeCallback, 
                          final boolean isExceptionThrown, final Map<String, Object> dataMap) throws Exception {
        synchronized (baseStatementUnit.getStatement().getConnection()) {
            T result;
            ExecutorExceptionHandler.setExceptionThrown(isExceptionThrown);
            ExecutorDataMap.setDataMap(dataMap);
            List<AbstractExecutionEvent> events = new LinkedList<>();
            if (parameterSets.isEmpty()) {
                   // 生成 构造无参SQL的事件（事件类型为BEFORE_EXECUTE）
                events.add(getExecutionEvent(sqlType, baseStatementUnit, Collections.emptyList()));
            }
            for (List<Object> each : parameterSets) {
                  // 构造有参SQL的事件（事件类型为BEFORE_EXECUTE）
                events.add(getExecutionEvent(sqlType, baseStatementUnit, each));
            }
            for (AbstractExecutionEvent event : events) {
                  // EventBus 发布 EventExecutionType.BEFORE_EXECUTE
                EventBusInstance.getInstance().post(event);
            }
            try {
                // 执行回调函数
                result = executeCallback.execute(baseStatementUnit);
            } catch (final SQLException ex) {
                for (AbstractExecutionEvent each : events) {
                    each.setEventExecutionType(EventExecutionType.EXECUTE_FAILURE);
                    each.setException(ex);
                     // EventBus 发布 EventExecutionType.EXECUTE_FAILURE
                    EventBusInstance.getInstance().post(each);
                    ExecutorExceptionHandler.handleException(ex);
                }
                return null;
            }
            for (AbstractExecutionEvent each : events) {
                each.setEventExecutionType(EventExecutionType.EXECUTE_SUCCESS);
                   // EventBus 发布 EventExecutionType.EXECUTE_SUCCESS
                EventBusInstance.getInstance().post(each);
            }
            return result;
        }
    }

result = executeCallback.execute(baseStatementUnit); 执行回调函数。StatementExecutor，PreparedStatementExecutor，BatchPreparedStatementExecutor 通过传递执行回调函数( ExecuteCallback )实现给 ExecutorEngine 实现并行执行。

关于synchronized (baseStatementUnit.getStatement().getConnection()) {。网上看到大神的解释： MySQL、Oracle 的 Connection 实现是线程安全的。数据库连接池实现的 Connection 不一定是线程安全，例如 Druid 的线程池 Connection 非线程安全。考虑到mysql驱动在执行statement时对同一个connection是线程安全的。也就是说同一个数据库链接的会话是串行执行的。故在shardingjdbc的executor对于多线程执行的情况也进行了针对数据库链接级别的同步。故该方案不会降低shardingjdbc的性能。 https://github.com/dangdangdotcom/sharding-jdbc/issues/166

ExecutorExceptionHandler、ExecutorDataMap 和柔性事务 ( AbstractSoftTransaction )。就是这里只是发布，消费部分以后在柔性事务再整理。

三 Executor

Executor，执行器，目前一共有三个执行器。不同的执行器对应不同的执行单元 (BaseStatementUnit)。

执行器类	执行器名	执行单元
StatementExecutor	多线程执行静态语句对象请求的执行器	StatementUnit
PreparedStatementExecutor	多线程执行预编译语句对象请求的执行器.	PreparedStatementUnit
BatchPreparedStatementExecutor	批量预编译语句对象请求的执行器	BatchPreparedStatementUnit

执行单元继承自 BaseStatementUnit

3.1 StatementExecutor

StatementExecutor，多线程执行静态语句对象请求的执行器，一共有三类方法：

 public List<ResultSet> executeQuery() throws SQLException {
        return executorEngine.executeStatement(sqlType, statementUnits, new ExecuteCallback<ResultSet>() {
            
            @Override
            public ResultSet execute(final BaseStatementUnit baseStatementUnit) throws Exception {
                return baseStatementUnit.getStatement().executeQuery(baseStatementUnit.getSqlExecutionUnit().getSql());
            }
        });
    }

executeUpdate() 因为有四个不同情况的#executeUpdate()，所以抽象了 Updater 接口，从而达到逻辑重用。

public int executeUpdate() throws SQLException {
        return executeUpdate(new Updater() {
            
            @Override
            public int executeUpdate(final Statement statement, final String sql) throws SQLException {
                return statement.executeUpdate(sql);
            }
        });
    }。。。

 private int executeUpdate(final Updater updater) throws SQLException {
        List<Integer> results = executorEngine.executeStatement(sqlType, statementUnits, new ExecuteCallback<Integer>() {
            
            @Override
            public Integer execute(final BaseStatementUnit baseStatementUnit) throws Exception {
                return updater.executeUpdate(baseStatementUnit.getStatement(), baseStatementUnit.getSqlExecutionUnit().getSql());
            }
        });
        return accumulate(results);
    }

#execute() 因为有四个不同情况的#execute()，所以抽象了 Executor 接口，从而达到逻辑重用。

public boolean execute(final String[] columnNames) throws SQLException {
        return execute(new Executor() {
            
            @Override
            public boolean execute(final Statement statement, final String sql) throws SQLException {
                return statement.execute(sql, columnNames);
            }
        });
    }

3.2 PreparedStatementExecutor

public List<ResultSet> executeQuery() throws SQLException {
* * * * return executorEngine.executePreparedStatement(sqlType, preparedStatementUnits, parameters, new ExecuteCallback<ResultSet>() {
* * * * * **
* * * * * * @Override
* * * * * * public ResultSet execute(final BaseStatementUnit baseStatementUnit) throws Exception {
* * * * * * * * return ((PreparedStatement) baseStatementUnit.getStatement()).executeQuery();
* * * * * * }
* * * * });
* * }* **
* * public int executeUpdate() throws SQLException {
* * * * List<Integer> results = executorEngine.executePreparedStatement(sqlType, preparedStatementUnits, parameters, new ExecuteCallback<Integer>() {
* * * * * **
* * * * * * @Override
* * * * * * public Integer execute(final BaseStatementUnit baseStatementUnit) throws Exception {
* * * * * * * * return ((PreparedStatement) baseStatementUnit.getStatement()).executeUpdate();
* * * * * * }
* * * * });
* * * * return accumulate(results);
* * }* **
* * private int accumulate(final List<Integer> results) {
* * * * int result = 0;
* * * * for (Integer each : results) {
* * * * * * result += null == each ? 0 : each;
* * * * }
* * * * return result;
* * }* **
* * public boolean execute() throws SQLException {
* * * * List<Boolean> result = executorEngine.executePreparedStatement(sqlType, preparedStatementUnits, parameters, new ExecuteCallback<Boolean>() {
* * * * * **
* * * * * * @Override
* * * * * * public Boolean execute(final BaseStatementUnit baseStatementUnit) throws Exception {
* * * * * * * * return ((PreparedStatement) baseStatementUnit.getStatement()).execute();
* * * * * * }
* * * * });
* * * * if (null == result || result.isEmpty() || null == result.get(0)) {
* * * * * * return false;
* * * * }
* * * * return result.get(0);
* * }

3.3 BatchPreparedStatementExecutor

 public int[] executeBatch() throws SQLException {
        return accumulate(executorEngine.executeBatch(sqlType, batchPreparedStatementUnits, parameterSets, new ExecuteCallback<int[]>() {
            
            @Override
            public int[] execute(final BaseStatementUnit baseStatementUnit) throws Exception {
                return baseStatementUnit.getStatement().executeBatch();
            }
        }));
    }    
    private int[] accumulate(final List<int[]> results) {
        int[] result = new int[parameterSets.size()];
        int count = 0;
        for (BatchPreparedStatementUnit each : batchPreparedStatementUnits) {
            for (Map.Entry<Integer, Integer> entry : each.getJdbcAndActualAddBatchCallTimesMap().entrySet()) {
                int value = null == results.get(count) ? 0 : results.get(count)[entry.getValue()];
                if (DatabaseType.Oracle == dbType) {
                    result[entry.getKey()] = value;
                } else {
                    result[entry.getKey()] += value;
                }
            }
            count++;
        }
        return result;
    }

accumulate 需要累加的每个分片SQL影响的行数

4 ExecutionEvent

AbstractExecutionEvent 是抽象接口。有一个抽象子类AbstractSQLExecutionEvent。

public abstract class AbstractSQLExecutionEvent extends AbstractExecutionEvent {
    
    private final String dataSource;
    
    private final String sql;
    
    private final List<Object> parameters;

一个子类：

public final class OverallExecutionEvent extends AbstractExecutionEvent {
    
    private final SQLType sqlType;
    
    private final int statementUnitSize;
}

AbstractSQLExecutionEvent 有两个子类：DMLExecutionEvent、DQLExecutionEvent

EventExecutionType，事件触发类型。
BEFORE_EXECUTE：执行前
EXECUTE_SUCCESS：执行成功
EXECUTE_FAILURE：执行失败

4.1 eventbus

Sharding-JDBC 使用 Guava、的 EventBus 实现了事件的发布和订阅。从上文 ExecutorEngine#executeInternal() 我们可以看到每个分片 SQL 执行的过程中会发布相应事件。那么订阅在哪里呢？

搜一下代码：SoftTransactionManager

 public void init() throws SQLException {
        EventBusInstance.getInstance().register(new BestEffortsDeliveryListener());
        if (TransactionLogDataSourceType.RDB == transactionConfig.getStorageType()) {
            Preconditions.checkNotNull(transactionConfig.getTransactionLogDataSource());
            createTable();
        }
        if (transactionConfig.getBestEffortsDeliveryJobConfiguration().isPresent()) {
            new NestedBestEffortsDeliveryJobFactory(transactionConfig).init();
        }
    }

  @Subscribe
    @AllowConcurrentEvents
    public void listen(final DMLExecutionEvent event) {
        if (!isProcessContinuously()) {
            return;
        }
        SoftTransactionConfiguration transactionConfig = SoftTransactionManager.getCurrentTransactionConfiguration().get();
        TransactionLogStorage transactionLogStorage = TransactionLogStorageFactory.createTransactionLogStorage(transactionConfig.buildTransactionLogDataSource());
        BEDSoftTransaction bedSoftTransaction = (BEDSoftTransaction) SoftTransactionManager.getCurrentTransaction().get();
        switch (event.getEventExecutionType()) {
            case BEFORE_EXECUTE:
                //TODO for batch SQL need split to 2-level records
                transactionLogStorage.add(new TransactionLog(event.getId(), bedSoftTransaction.getTransactionId(), bedSoftTransaction.getTransactionType(), 
                        event.getDataSource(), event.getSql(), event.getParameters(), System.currentTimeMillis(), 0));
                return;
            case EXECUTE_SUCCESS: 
                transactionLogStorage.remove(event.getId());
                return;
            case EXECUTE_FAILURE: 
                boolean deliverySuccess = false;
                for (int i = 0; i < transactionConfig.getSyncMaxDeliveryTryTimes(); i++) {
                    if (deliverySuccess) {
                        return;
                    }
                    boolean isNewConnection = false;
                    Connection conn = null;
                    PreparedStatement preparedStatement = null;
                    try {
                        conn = bedSoftTransaction.getConnection().getConnection(event.getDataSource(), SQLType.DML);
                        if (!isValidConnection(conn)) {
                            bedSoftTransaction.getConnection().release(conn);
                            conn = bedSoftTransaction.getConnection().getConnection(event.getDataSource(), SQLType.DML);
                            isNewConnection = true;
                        }
                        preparedStatement = conn.prepareStatement(event.getSql());
                        //TODO for batch event need split to 2-level records
                        for (int parameterIndex = 0; parameterIndex < event.getParameters().size(); parameterIndex++) {
                            preparedStatement.setObject(parameterIndex + 1, event.getParameters().get(parameterIndex));
                        }
                        preparedStatement.executeUpdate();
                        deliverySuccess = true;
                        transactionLogStorage.remove(event.getId());
                    } catch (final SQLException ex) {
                        log.error(String.format("Delivery times %s error, max try times is %s", i + 1, transactionConfig.getSyncMaxDeliveryTryTimes()), ex);
                    } finally {
                        close(isNewConnection, conn, preparedStatement);
                    }
                }
                return;
            default: 
                throw new UnsupportedOperationException(event.getEventExecutionType().toString());
        }
    }

@Subscribe 注解在方法上，实现对事件的订阅
@AllowConcurrentEvents 注解在方法上，表示线程安全，允许并发执行
方法上的参数对应的类即是订阅的事件。例如，#listen() 订阅了 DMLExecutionEvent 事件

其他在柔性事务展开，这里就是作为订阅者的例子。

参考：

http://www.iocoder.cn/Sharding-JDBC/sql-execute/

07、ShardingJDBC实战：SQL执行

一 序