Apache ShardingSphere 5.1.0 執行引擎效能優化揭祕

SphereEx發表於2022-03-24
Apache優化

端正強，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 語句不支援 for updateclause；

  • UNION 語句不支援 select 子句中包含 order byclause，只能在 UNION 語句最後新增 order byclause；

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，這種場景下仍然需要執行歸併邏輯。

@Overridepublic 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_shardingdataSources:  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: 1rules:- !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 組取平均值，最終效能測試結果如下。

Apache ShardingSphere 5.1.0 執行引擎效能優化揭祕

Apache ShardingSphere 5.1.0 執行引擎效能優化揭祕

CASE 1 與 CASE 2 都是基於 100 萬資料量下的 sysbench 表結構進行測試，由於測試表分片數較多，整體效能提升了 4 倍左右，理論上隨著分片數的增加，效能提升的效果會更加明顯。

結語

Apache ShardingSphere 5.1.0 進行了大量的效能優化，針對協議層和核心層進行了全面的優化提升，本文限於篇幅只對 SQL 執行引擎進行了解讀，後續的系列文章還會帶來更加專業和全面的效能優化指南，希望感興趣的同學繼續關注。同時，也歡迎社群的同學積極參與進來，共同提升 Apache ShardingSphere 的效能，為社群提供更好的使用體驗。

參考文件

1、ShardingSphere 執行引擎

2、ShardingSphere 社群關於執行引擎連線模式的討論

3、MySQL UNION 官方文件

4、PostgreSQL UNION 官方文件

5、Oracle UNION 官方文件

6、SQL Server UNION 官方文件


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70001955/viewspace-2883828/，如需轉載，請註明出處，否則將追究法律責任。

相關文章