关于数据库:Apache-ShardingSphere-510-执行引擎性能优化揭秘

46次阅读

共计 13649 个字符，预计需要花费 35 分钟才能阅读完成。

端正强，SphereEx 高级中间件开发工程师，Apache ShardingSphere Committer。

2018 年开始接触 Apache ShardingSphere 中间件，曾主导公司外部海量数据的分库分表，有着丰盛的实践经验；酷爱开源，乐于分享，目前专一于 Apache ShardingSphere 内核模块开发。

前言
在 Apache ShardingSphere 5.1.0 前几篇技术解读文章中，咱们理解了解析引擎提供的 SQL 格式化性能，以及最新优化的高可用性能。除了一直为用户提供实用的新个性外，Apache ShardingSphere 社区始终在致力晋升性能。

以单库 10 分片的 t_order 表为例，max-connections-size-per-query 应用默认配置 1，如果用户执行 SELECT * FROM t_order 语句则会导致全路由。因为每个查问只容许在同一个数据库上创立一个数据库连贯，因而底层实在执行的 SQL 后果会被提前加载至内存进行解决，该场景限度了数据库连贯资源的耗费，然而会占用更多的内存资源。如果用户将 max-connections-size-per-query 调整为 10，则能够在执行实在 SQL 时，同时创立 10 个数据库连贯，因为数据库连贯可能持有后果集，因而该场景不会额定占用内存资源，然而很显然会耗费更多的数据库连贯资源。

为了更好地解决数据库连贯资源和内存资源占用的问题，在刚刚公布的 5.1.0 版本中，咱们对 SQL 执行引擎的性能进行了优化，通过 SQL 改写引擎进行优化性改写，将同一个数据源上的多条实在 SQL，应用 UNION ALL 语句进行合并，从而无效升高了执行引擎对数据库连贯资源的耗费，同时缩小了内存归并的产生，大幅度晋升了 OLTP 场景下 SQL 查问性能。上面咱们将联合具体的 SQL 实例，为大家具体解读执行引擎性能优化的细节。

执行引擎原理
在解读执行引擎性能优化之前，让咱们先来回顾下 Apache ShardingSphere 微内核及内核流程中执行引擎的原理。如下图所示，Apache ShardingSphere 微内核蕴含了 SQL 解析、SQL 路由、SQL 改写、SQL 执行和后果归并等外围流程。

SQL 解析引擎负责对用户输出的 SQL 语句进行解析，并生成蕴含上下文信息的 SQLStatement。SQL 路由引擎则依据解析上下文提取出分片条件，再联合用户配置的分片规定，计算出实在 SQL 须要执行的数据源并生成路由后果。SQL 改写引擎依据 SQL 路由引擎返回的后果，对原始 SQL 进行改写，具体包含了正确性改写和优化性改写。SQL 执行引擎则负责将 SQL 路由和改写引擎返回的实在 SQL 平安且高效地发送到底层数据源执行，执行的后果集最终会由归并引擎进行解决，生成对立的后果集返回给用户。

从整个微内核的执行流程能够看出，SQL 执行引擎间接与底层数据库交互，并负责持有执行的后果集，能够说执行引擎的性能和资源耗费，间接关系到整个 Apache ShardingSphere 的性能和资源耗费，因而 Apache ShardingSphere 外部采纳了一套自动化的 SQL 执行引擎，负责在执行性能和资源耗费间进行衡量。

从执行性能的角度来看，为每个分片的执行语句调配一个独立的数据库连贯，能够充分利用多线程来晋升执行性能，也能够将 I/O 所产生的耗费并行处理。此外，为每个分片调配一个独立的数据库连贯，还可能防止过早的将查问后果集加载至内存，独立的数据库连贯，可能持有查问后果集游标位置的援用，在须要获取相应数据时挪动游标即可。

从资源管制的角度来看，该当对业务拜访数据库的连贯数量进行限度，防止某一业务占用过多的数据库连贯资源，影响其余业务的失常拜访。特地是在一个数据库实例中存在较多分表的状况下，一条不蕴含分片键的逻辑 SQL 将产生落在同库不同表的大量实在 SQL，如果每条实在 SQL 都占用一个独立的连贯，那么一次查问无疑将会占用过多的资源。

为了解决执行性能和资源管制的抵触问题，Apache ShardingSphere 提出了连贯模式的概念，上面是 Apache ShardingSphere 源码对于连贯模式的定义。

/**

  • Connection Mode.
    */

public enum ConnectionMode {

MEMORY_STRICTLY, CONNECTION_STRICTLY

}
从 ConnectionMode 枚举类中成员的命名能够看出，SQL 执行引擎将数据库连贯划分为 MEMORY_STRICTLY 和 CONNECTION_STRICTLY。

MEMORY_STRICTLY 代表内存限度模式，当采纳内存限度模式时，对于同一个数据源，如果逻辑表对应了 10 个实在表，那么 SQL 执行引擎会创立 10 个连贯并行地执行，因为每个分片的后果集都有对应的连贯进行持有，因而无需将后果集提前加载到内存中，从而无效地升高了内存占用；

CONNECTION_STRICTLY 代表连贯限度模式，当采纳连贯限度模式时，SQL 执行引擎只会在同一个数据源上创立一个连贯，严格控制对数据库连贯资源的耗费，在实在 SQL 执行之后立刻将后果集加载至内存，因而会占用局部内存空间。

那么，Apache ShardingSphere SQL 执行引擎是如何帮忙用户抉择连贯模式的呢？SQL 执行引擎抉择连贯模式的逻辑能够参考下图：

用户通过配置 maxConnectionSizePerQuery 参数，能够指定每条语句在同一个数据源上最大容许的连接数。通过下面的计算公式，当每个数据库连贯需执行的 SQL 数量小于等于 1 时，阐明以后能够满足每条实在执行的 SQL 都调配一个独立的数据库连贯，此时会抉择内存限度模式，同一个数据源容许创立多个数据库连贯进行并行执行。反之则会抉择连贯限度模式，同一个数据源只容许创立一个数据库连贯进行执行，而后将后果集加载进内存后果集，再提供给归并引擎应用。

执行引擎优化
在相熟了 Apache ShardingSphere SQL 执行引擎的外部原理之后，咱们发现应用内存限度模式时，会耗费更多的数据库连贯，然而可能通过并发执行取得更好的性能，应用连贯限度模式可能无效管制连贯资源的应用，然而会占用过多的内存，执行的性能也会受到影响。

那么，有没有可能应用尽可能少的数据库连贯，同时占用较少内存的执行形式呢？依据前文对 SQL 执行引擎的剖析，执行模式的抉择次要是依据同一个数据源上路由后果的数量，因而最间接的优化思路，就是对同一个数据源上的路由后果进行合并。SQL 语句人造反对通过 UNION ALL 对多条查问语句进行合并，因而咱们采纳 UNION ALL 计划，对同一个数据源中的多条实在 SQL 进行优化性改写，从而将多条实在 SQL 改写为一条 SQL，这样可能大大减少数据库连贯的获取，同时也能够将内存后果集转换为流式后果集，缩小内存的占用。

思考到不同数据库方言对于 UNION ALL 语句的应用存在限度，咱们调研了 MySQL、PostgreSQL、Oracle 以及 SQL Server 的官网文档，梳理之后失去了如下信息。

MySQL UNION ALL 应用标准：

UNION 之后的列名应用第一个 SELECT 语句中的列名；

UNION 中蕴含 ORDER BY 和 LIMIT 时，须要应用括号将各个查问语句括起来，UNION 无奈保障最终的后果集有序，如果须要对 UNION 后果集进行排序，须要在 UNION 语句最初增加 ORDER BY LIMIT 子句；

无奈保障 UNION 后果集有序

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

保障 UNION 后果集有序

(SELECT a FROM t1 WHERE a=10 AND B=1) UNION (SELECT a FROM t2 WHERE a=11 AND B=2) ORDER BY a LIMIT 10;
UNION 不反对 SELECT HIGH_PRIORITY 语句和 SELECT INTO file 语句

PostgreSQL UNION ALL 应用标准：

UNION 之后的列名应用第一个 SELECT 语句中的列名；

UNION 中蕴含 ORDER BY 和 LIMIT 时，须要应用括号将各个查问语句括起来，最初一个 UNION 子句能够不应用括号，不应用括号，则 ORDER BY LIMIT 子句利用于整个 UNION 后果。

UNION 语句不反对 FOR NO KEY UPDATE、FOR UPDATE、FOR SHARE 和 FOR KEY SHARE；

Oracle UNION ALL 应用标准：

UNION 语句不反对 BLOB, CLOB, BFILE, VARRAY, LONG 类型或者 nested table；

UNION 语句不反对 forupdateclause；

UNION 语句不反对 select 子句中蕴含 orderbyclause，只能在 UNION 语句最初增加 orderbyclause；

SELECT product_id FROM order_items UNION SELECT product_id FROM inventories ORDER BY product_id;
UNION 语句不反对 SELECT 语句中蕴含 TABLE collection expressions；
SQL Server UNION ALL 应用标准：

UNION 语句中应用 ORDER BY 子句时，必须放在最初一个 select 子句之上，对 UNION 后果进行排序；
综合以上梳理的信息来看，不同的数据库方言都可能反对简略的 SELECT FROM table WHERE 语句，对于 ORDER BY LIMIT 也能通过语法调整进行反对，只是应用上存在一些语法差别，而对于更加简单的分组查问、子查问及关联查问，官网文档上并未进行详细描述。思考到 SQL 优化性改写须要保障 SQL 兼容性，Apache ShardingSphere 5.1.0 只抉择了简略的 SELECT FROM table WHERE 语句进行改写，旨在疾速晋升 OLTP 场景下的查问性能。

上面展现了 RouteSQLRewriteEngine 改写引擎的最新逻辑，Apache ShardingSphere 5.1.0 中增加了对于 SELECT FROM table WHERE 语句的优化性改写逻辑，首先通过 isNeedAggregateRewrite 进行判断，只有当同一个数据源中的路由后果大于 1，并且实在执行的 SQL 满足 SELECT FROM table WHERE 构造时，才会进行 UNION ALL 改写。

/**

  • Rewrite SQL and parameters.
    *
  • @param sqlRewriteContext SQL rewrite context
  • @param routeContext route context
  • @return SQL rewrite result
    */

public RouteSQLRewriteResult rewrite(final SQLRewriteContext sqlRewriteContext, final RouteContext routeContext) {

Map<RouteUnit, SQLRewriteUnit> result = new LinkedHashMap<>(routeContext.getRouteUnits().size(), 1);
for (Entry<String, Collection<RouteUnit>> entry : aggregateRouteUnitGroups(routeContext.getRouteUnits()).entrySet()) {Collection<RouteUnit> routeUnits = entry.getValue();
    if (isNeedAggregateRewrite(sqlRewriteContext.getSqlStatementContext(), routeUnits)) {result.put(routeUnits.iterator().next(), createSQLRewriteUnit(sqlRewriteContext, routeContext, routeUnits));
    } else {result.putAll(createSQLRewriteUnits(sqlRewriteContext, routeContext, routeUnits));
    }
}
return new RouteSQLRewriteResult(result);

}
因为应用了 UNION ALL 改写，归并引擎中对于 queryResults 的判断逻辑也须要同步进行调整，原先多个 queryResults 可能被 UNION ALL 合并为一个 queryResults，这种场景下依然须要执行归并逻辑。

@Override
public MergedResult merge(final List<QueryResult> queryResults, final SQLStatementContext<?> sqlStatementContext, final ShardingSphereSchema schema) throws SQLException {

if (1 == queryResults.size() && !isNeedAggregateRewrite(sqlStatementContext)) {return new IteratorStreamMergedResult(queryResults);
}
Map<String, Integer> columnLabelIndexMap = getColumnLabelIndexMap(queryResults.get(0));
SelectStatementContext selectStatementContext = (SelectStatementContext) sqlStatementContext;
selectStatementContext.setIndexes(columnLabelIndexMap);
MergedResult mergedResult = build(queryResults, selectStatementContext, columnLabelIndexMap, schema);
return decorate(queryResults, selectStatementContext, mergedResult);

}
为了不便大家了解优化前后的逻辑，咱们应用如下分片配置，通过 SELECT * FROM t_order 来具体阐明下优化的成果，示例中 max-connections-size-per-query 参数应用默认值 1。

rules:

  • !SHARDING
    tables:
    t_order:

    actualDataNodes: ds_${0..1}.t_order_${0..1}
tableStrategy:
  standard:
    shardingColumn: order_id
    shardingAlgorithmName: t_order_inline
databaseStrategy:
  standard:
    shardingColumn: user_id
    shardingAlgorithmName: database_inline

    shardingAlgorithms:
    database_inline:

    type: INLINE
props:
  algorithm-expression: ds_${user_id % 2}

    t_order_inline:

    type: INLINE
props:
  algorithm-expression: t_order_${order_id % 2}

    在 5.0.0 版本中，咱们执行 SELECT * FROM t_order 语句后，能够失去如下路由后果，后果中蕴含 ds_0 和 ds_1 两个数据源，并且各自蕴含了两个路由后果，因为 max-connections-size-per-query 设置为 1，此时无奈满足每个实在执行 SQL 都有一个数据库连贯，因而会抉择连贯限度模式。

同时因为应用了连贯限度模式，在并行执行后会将后果集加载至内存中，应用 JDBCMemoryQueryResult 进行存储，当用户后果集较大时，会占用较多的内存。内存后果集的应用也会导致归并时只能应用内存归并，而无奈应用流式归并。

private QueryResult createQueryResult(final ResultSet resultSet, final ConnectionMode connectionMode) throws SQLException {

return ConnectionMode.MEMORY_STRICTLY == connectionMode ? new JDBCStreamQueryResult(resultSet) : new JDBCMemoryQueryResult(resultSet);

}
在 5.1.0 版本中，咱们应用了 UNION ALL 对执行的 SQL 进行优化，同一个数据源中多个路由后果会被合并为一条 SQL 执行。因为可能满足一个数据库连贯持有一个后果集，因而会抉择内存限度模式。在内存限度模式下，会应用流式后果集 JDBCStreamQueryResult 对象持有后果集，在须要应用数据时，能够依照流式查问的形式查问数据。

性能优化测试
从后面大节的示例中，咱们能够看出应用 UNION ALL 进行优化性改写，能够无效缩小对数据库连贯的耗费，也可能将内存后果集转换为流式后果集，从而防止过多地占用内存。为了更加具体阐明优化对于性能的晋升，咱们针对优化前后的逻辑进行了压测，压测所采纳的软件版本如下，应用 5.0.1-SNAPSHOT 版本的 ShardingSphere-Proxy 以及 5.7.26 版本的 MySQL。

压测环境对应的机器配置如下：

咱们参考 sysbench 表构造，创立了 sbtest1~sbtest10 等 10 张分片表，每个分片表又分为 5 库，每个库分为 10 张表，具体的 config-sharding.yaml 配置文件如下。

schemaName: sbtest_sharding
dataSources:
ds_0:

url: jdbc:mysql://127.0.0.1:3306/sbtest?useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=8192&prepStmtCacheSqlLimit=1024
username: root
password: 123456
connectionTimeoutMilliseconds: 10000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1

ds_1:

url: jdbc:mysql://127.0.0.1:3306/sbtest?useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=8192&prepStmtCacheSqlLimit=1024
username: root
password: 123456
connectionTimeoutMilliseconds: 10000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1

ds_2:

url: jdbc:mysql://127.0.0.1:3306/sbtest?useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=8192&prepStmtCacheSqlLimit=1024
username: root
password: 123456
connectionTimeoutMilliseconds: 10000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1

ds_3:

url: jdbc:mysql://127.0.0.1:3306/sbtest?useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=8192&prepStmtCacheSqlLimit=1024
username: root
password: 123456
connectionTimeoutMilliseconds: 10000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1

ds_4:

url: jdbc:mysql://127.0.0.1:3306/sbtest?useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=8192&prepStmtCacheSqlLimit=1024
username: root
password: 123456
connectionTimeoutMilliseconds: 10000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1

rules:

  • !SHARDING
    tables:
    sbtest1:

    actualDataNodes: ds_${0..4}.sbtest1_${0..9}
tableStrategy:
  standard:
    shardingColumn: id
    shardingAlgorithmName: table_inline_1
keyGenerateStrategy:
  column: id
  keyGeneratorName: snowflake

    sbtest2:

    actualDataNodes: ds_${0..4}.sbtest2_${0..9}
tableStrategy:
  standard:
    shardingColumn: id
    shardingAlgorithmName: table_inline_2
keyGenerateStrategy:
  column: id
  keyGeneratorName: snowflake

    sbtest3:

    actualDataNodes: ds_${0..4}.sbtest3_${0..9}
tableStrategy:
  standard:
    shardingColumn: id
    shardingAlgorithmName: table_inline_3
keyGenerateStrategy:
  column: id
  keyGeneratorName: snowflake

    sbtest4:

    actualDataNodes: ds_${0..4}.sbtest4_${0..9}
tableStrategy:
  standard:
    shardingColumn: id
    shardingAlgorithmName: table_inline_4
keyGenerateStrategy:
  column: id
  keyGeneratorName: snowflake

    sbtest5:

    actualDataNodes: ds_${0..4}.sbtest5_${0..9}
tableStrategy:
  standard:
    shardingColumn: id
    shardingAlgorithmName: table_inline_5
keyGenerateStrategy:
  column: id
  keyGeneratorName: snowflake

    sbtest6:

    actualDataNodes: ds_${0..4}.sbtest6_${0..9}
tableStrategy:
  standard:
    shardingColumn: id
    shardingAlgorithmName: table_inline_6
keyGenerateStrategy:
  column: id
  keyGeneratorName: snowflake

    sbtest7:

    actualDataNodes: ds_${0..4}.sbtest7_${0..9}
tableStrategy:
  standard:
    shardingColumn: id
    shardingAlgorithmName: table_inline_7
keyGenerateStrategy:
  column: id
  keyGeneratorName: snowflake

    sbtest8:

    actualDataNodes: ds_${0..4}.sbtest8_${0..9}
tableStrategy:
  standard:
    shardingColumn: id
    shardingAlgorithmName: table_inline_8
keyGenerateStrategy:
  column: id
  keyGeneratorName: snowflake

    sbtest9:

    actualDataNodes: ds_${0..4}.sbtest9_${0..9}
tableStrategy:
  standard:
    shardingColumn: id
    shardingAlgorithmName: table_inline_9
keyGenerateStrategy:
  column: id
  keyGeneratorName: snowflake

    sbtest10:

    actualDataNodes: ds_${0..4}.sbtest10_${0..9}
tableStrategy:
  standard:
    shardingColumn: id
    shardingAlgorithmName: table_inline_10
keyGenerateStrategy:
  column: id
  keyGeneratorName: snowflake

    defaultDatabaseStrategy:
    standard:

    shardingColumn: id
shardingAlgorithmName: database_inline

    shardingAlgorithms:
    database_inline:

    type: INLINE
props:
  algorithm-expression: ds_${id % 5}
  allow-range-query-with-inline-sharding: true

    table_inline_1:

    type: INLINE
props:
  algorithm-expression: sbtest1_${id % 10}
  allow-range-query-with-inline-sharding: true

    table_inline_2:

    type: INLINE
props:
  algorithm-expression: sbtest2_${id % 10}
  allow-range-query-with-inline-sharding: true

    table_inline_3:

    type: INLINE
props:
  algorithm-expression: sbtest3_${id % 10}
  allow-range-query-with-inline-sharding: true

    table_inline_4:

    type: INLINE
props:
  algorithm-expression: sbtest4_${id % 10}
  allow-range-query-with-inline-sharding: true

    table_inline_5:

    type: INLINE
props:
  algorithm-expression: sbtest5_${id % 10}
  allow-range-query-with-inline-sharding: true

    table_inline_6:

    type: INLINE
props:
  algorithm-expression: sbtest6_${id % 10}
  allow-range-query-with-inline-sharding: true

    table_inline_7:

    type: INLINE
props:
  algorithm-expression: sbtest7_${id % 10}
  allow-range-query-with-inline-sharding: true

    table_inline_8:

    type: INLINE
props:
  algorithm-expression: sbtest8_${id % 10}
  allow-range-query-with-inline-sharding: true

    table_inline_9:

    type: INLINE
props:
  algorithm-expression: sbtest9_${id % 10}
  allow-range-query-with-inline-sharding: true

    table_inline_10:

    type: INLINE
props:
  algorithm-expression: sbtest10_${id % 10}
  allow-range-query-with-inline-sharding: true

    keyGenerators:
    snowflake:

    type: SNOWFLAKE
props:
  worker-id: 123

    咱们应用如下 JMH 测试程序对不同 CASE 进行测试：

@State(Scope.Thread)
public class QueryOptimizationTest {

private PreparedStatement unionAllForCaseOneStatement;

private PreparedStatement unionAllForCaseTwoStatement;

@Setup(Level.Trial)
public void setup() throws Exception {Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3307/sharding_db?useSSL=false", "root", "123456");
    // CASE 1
    unionAllForCaseOneStatement = connection.prepareStatement("SELECT COUNT(k) AS countK FROM sbtest1 WHERE id < ?;");
    // CASE 2
    unionAllForCaseTwoStatement = connection.prepareStatement("SELECT SUM(k) AS sumK FROM sbtest1 WHERE id < ?;");
}

@Benchmark
public void testUnionAllForCaseOne() throws SQLException {unionAllForCaseOneStatement.setInt(1, 200);
    unionAllForCaseOneStatement.executeQuery();}

@Benchmark
public void testUnionAllForCaseTwo() throws SQLException {unionAllForCaseTwoStatement.setInt(1, 200);
    unionAllForCaseTwoStatement.executeQuery();}

}
性能测试会对每个 CASE 别离测试 3 组，而后取平均值，再切换到优化前的版本 aab226b72ba574061748d8f94c461ea469f9168f 进行编译打包，同样测试 3 组取平均值，最终性能测试后果如下。

CASE 1 与 CASE 2 都是基于 100 万数据量下的 sysbench 表构造进行测试，因为测试表分片数较多，整体性能晋升了 4 倍左右，实践上随着分片数的减少，性能晋升的成果会更加显著。

结语
Apache ShardingSphere 5.1.0 进行了大量的性能优化，针对协定层和内核层进行了全面的优化晋升，本文限于篇幅只对 SQL 执行引擎进行了解读，后续的系列文章还会带来更加业余和全面的性能优化指南，心愿感兴趣的同学持续关注。同时，也欢送社区的同学积极参与进来，独特晋升 Apache ShardingSphere 的性能，为社区提供更好的应用体验。

参考文档
1、ShardingSphere 执行引擎：https://shardingsphere.apache…

2、ShardingSphere 社区对于执行引擎连贯模式的探讨：https://github.com/apache/sha…

3、MySQL UNION 官网文档：https://dev.mysql.com/doc/ref…

4、PostgreSQL UNION 官网文档：https://www.postgresql.org/do…

5、Oracle UNION 官网文档：https://docs.oracle.com/en/da…

6、SQL Server UNION 官网文档：https://docs.microsoft.com/en…

正文完
数据库
发表至： 数据库
2022-03-25
 0
关于数据库:使用-KubeSphere-快速部署-Chaos-Mesh
关于数据库:mysql如何优雅删除大表-看这篇就够了
关于数据库:KaiwuDB-受邀亮相山东省数字化转型论坛
关于数据库:Tapdata-的-∞-实践实时数据赋能电商资源分配快速落地敏捷可复用的库存数据服务