概念

數(shù)據(jù)分片

背景

傳統(tǒng)的將數(shù)據(jù)集中存儲至單一數(shù)據(jù)節(jié)點(diǎn)的解決方案，在性能、可用性和運(yùn)維成本這三方面已經(jīng)難于滿足 互聯(lián)網(wǎng)的海量數(shù)據(jù)場景。

從性能方面來說，由于關(guān)系型數(shù)據(jù)庫大多采用 B+ 樹類型的索引，在數(shù)據(jù)量超過閾值的情況下，索引深度 的增加也將使得磁盤訪問的 IO 次數(shù)增加，進(jìn)而導(dǎo)致查詢性能的下降;同時，高并發(fā)訪問請求也使得集中 式數(shù)據(jù)庫成為系統(tǒng)的最大瓶頸。

從可用性的方面來講，服務(wù)化的無狀態(tài)型，能夠達(dá)到較小成本的隨意擴(kuò)容，這必然導(dǎo)致系統(tǒng)的最終壓力 都落在數(shù)據(jù)庫之上。而單一的數(shù)據(jù)節(jié)點(diǎn)，或者簡單的主從架構(gòu)，已經(jīng)越來越難以承擔(dān)。數(shù)據(jù)庫的可用性， 已成為整個系統(tǒng)的關(guān)鍵。

從運(yùn)維成本方面考慮，當(dāng)一個數(shù)據(jù)庫實(shí)例中的數(shù)據(jù)達(dá)到閾值以上，對于 DBA 的運(yùn)維壓力就會增大。數(shù)據(jù) 備份和恢復(fù)的時間成本都將隨著數(shù)據(jù)量的大小而愈發(fā)不可控。一般來講，單一數(shù)據(jù)庫實(shí)例的數(shù)據(jù)的閾值 在 1TB 之內(nèi)，是比較合理的范圍。

在傳統(tǒng)的關(guān)系型數(shù)據(jù)庫無法滿足互聯(lián)網(wǎng)場景需要的情況下，將數(shù)據(jù)存儲至原生支持分布式的 NoSQL 的嘗 試越來越多。但 NoSQL 對 SQL 的不兼容性以及生態(tài)圈的不完善，使得它們在與關(guān)系型數(shù)據(jù)庫的博弈中始 終無法完成致命一擊，而關(guān)系型數(shù)據(jù)庫的地位卻依然不可撼動。

數(shù)據(jù)分片指按照某個維度將存放在單一數(shù)據(jù)庫中的數(shù)據(jù)分散地存放至多個數(shù)據(jù)庫或表中以達(dá)到提升性能 瓶頸以及可用性的效果。數(shù)據(jù)分片的有效手段是對關(guān)系型數(shù)據(jù)庫進(jìn)行分庫和分表。分庫和分表均可以有 效的避免由數(shù)據(jù)量超過可承受閾值而產(chǎn)生的查詢瓶頸。除此之外，分庫還能夠用于有效的分散對數(shù)據(jù)庫 單點(diǎn)的訪問量;分表雖然無法緩解數(shù)據(jù)庫壓力，但卻能夠提供盡量將分布式事務(wù)轉(zhuǎn)化為本地事務(wù)的可能， 一旦涉及到跨庫的更新操作，分布式事務(wù)往往會使問題變得復(fù)雜。使用多主多從的分片方式，可以有效 的避免數(shù)據(jù)單點(diǎn)，從而提升數(shù)據(jù)架構(gòu)的可用性。

通過分庫和分表進(jìn)行數(shù)據(jù)的拆分來使得各個表的數(shù)據(jù)量保持在閾值以下，以及對流量進(jìn)行疏導(dǎo)應(yīng)對高訪 問量，是應(yīng)對高并發(fā)和海量數(shù)據(jù)系統(tǒng)的有效手段。數(shù)據(jù)分片的拆分方式又分為垂直分片和水平分片。

垂直分片

按照業(yè)務(wù)拆分的方式稱為垂直分片，又稱為縱向拆分，它的核心理念是專庫專用。在拆分之前，一個數(shù) 據(jù)庫由多個數(shù)據(jù)表構(gòu)成，每個表對應(yīng)著不同的業(yè)務(wù)。而拆分之后，則是按照業(yè)務(wù)將表進(jìn)行歸類，分布到 不同的數(shù)據(jù)庫中，從而將壓力分散至不同的數(shù)據(jù)庫。下圖展示了根據(jù)業(yè)務(wù)需要，將用戶表和訂單表垂直 分片到不同的數(shù)據(jù)庫的方案。

截屏2021-01-19 下午11.05.48.png

]

水平分片

水平分片又稱為橫向拆分。相對于垂直分片，它不再將數(shù)據(jù)根據(jù)業(yè)務(wù)邏輯分類，而是通過某個字段(或 某幾個字段)，根據(jù)某種規(guī)則將數(shù)據(jù)分散至多個庫或表中，每個分片僅包含數(shù)據(jù)的一部分。例如:根據(jù)主 鍵分片，偶數(shù)主鍵的記錄放入 0 庫(或表)，奇數(shù)主鍵的記錄放入 1 庫(或表)，如下圖所示。

水平分片從理論上突破了單機(jī)數(shù)據(jù)量處理的瓶頸，并且擴(kuò)展相對自由，是分庫分表的標(biāo)準(zhǔn)解決方案

截屏2021-01-19 下午11.06.13.png

挑戰(zhàn)

雖然數(shù)據(jù)分片解決了性能、可用性以及單點(diǎn)備份恢復(fù)等問題，但分布式的架構(gòu)在獲得了收益的同時，也

引入了新的問題。

面對如此散亂的分庫分表之后的數(shù)據(jù)，應(yīng)用開發(fā)工程師和數(shù)據(jù)庫管理員對數(shù)據(jù)庫的操作變得異常繁重就 是其中的重要挑戰(zhàn)之一。他們需要知道數(shù)據(jù)需要從哪個具體的數(shù)據(jù)庫的分表中獲取。

另一個挑戰(zhàn)則是，能夠正確的運(yùn)行在單節(jié)點(diǎn)數(shù)據(jù)庫中的 SQL，在分片之后的數(shù)據(jù)庫中并不一定能夠正確 運(yùn)行。例如，分表導(dǎo)致表名稱的修改，或者分?、排序、聚合分組等操作的不正確處理。

跨庫事務(wù)也是分布式的數(shù)據(jù)庫集群要面對的棘手事情。合理采用分表，可以在降低單表數(shù)據(jù)量的情況下， 盡量使用本地事務(wù)，善于使用同庫不同表可有效避免分布式事務(wù)帶來的麻煩。在不能避免跨庫事務(wù)的場 景，有些業(yè)務(wù)仍然需要保持事務(wù)的一致性。而基于 XA 的分布式事務(wù)由于在并發(fā)度高的場景中性能無法滿 足需要，并未被互聯(lián)網(wǎng)巨頭大規(guī)模使用，他們大多采用最終一致性的柔性事務(wù)代替強(qiáng)一致事務(wù)。

核心概念

邏輯表

水平拆分的數(shù)據(jù)庫(表)的相同邏輯和數(shù)據(jù)結(jié)構(gòu)表的總稱。例:訂單數(shù)據(jù)根據(jù)主鍵尾數(shù)拆分為 10 張表，分 別是 t_order_0 到 t_order_9，他們的邏輯表名為 t_order。

真實(shí)表

在分片的數(shù)據(jù)庫中真實(shí)存在的物理表。即上個示例中的 t_order_0 到 t_order_9。

數(shù)據(jù)節(jié)點(diǎn)

數(shù)據(jù)分片的最小單元。由數(shù)據(jù)源名稱和數(shù)據(jù)表組成，例:ds_0.t_order_0。

綁定表

指分片規(guī)則一致的主表和子表。例如:t_order 表和 t_order_item 表，均按照 order_id 分片，則 此兩張表互為綁定表關(guān)系。綁定表之間的多表關(guān)聯(lián)查詢不會出現(xiàn)笛卡爾積關(guān)聯(lián)，關(guān)聯(lián)查詢效率將大大提 升。舉例說明，如果 SQL 為:

SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o. order_id in (10, 11);

在不配置綁定表關(guān)系時，假設(shè)分片鍵 order_id 將數(shù)值 10 路由至第 0 片，將數(shù)值 11 路由至第 1 片，那 么路由后的 SQL 應(yīng)該為 4 條，它們呈現(xiàn)為笛卡爾積:

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o. order_id in (10, 11);
SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o. order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o. order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o. order_id in (10, 11);

在配置綁定表關(guān)系后，路由的 SQL 應(yīng)該為 2 條:

其中 t_order 在 FROM 的最左側(cè)，ShardingSphere 將會以它作為整個綁定表的主表。所有路由計算將 會只使用主表的策略，那么 t_order_item 表的分片計算將會使用 t_order 的條件。故綁定表之間的 分區(qū)鍵要完全相同。

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o. order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o. order_id in (10, 11);

廣播表

指所有的分片數(shù)據(jù)源中都存在的表，表結(jié)構(gòu)和表中的數(shù)據(jù)在每個數(shù)據(jù)庫中均完全一致。適用于數(shù)據(jù)量不 大且需要與海量數(shù)據(jù)的表進(jìn)行關(guān)聯(lián)查詢的場景，例如:字典表。

分片

• 分片鍵

用于分片的數(shù)據(jù)庫字段，是將數(shù)據(jù)庫(表)水平拆分的關(guān)鍵字段。例:將訂單表中的訂單主鍵的尾數(shù)取模 分片，則訂單主鍵為分片字段。SQL 中如果無分片字段，將執(zhí)行全路由，性能較差。除了對單分片字段的 支持，Apache ShardingSphere 也支持根據(jù)多個字段進(jìn)行分片。

• 分片算法

  通過分片算法將數(shù)據(jù)分片，支持通過 =、>=、<=、>、<、BETWEEN 和 IN 分片。分片算法需要應(yīng)用方開 發(fā)者自行實(shí)現(xiàn)，可實(shí)現(xiàn)的靈活度非常高。

  目前提供 4 種分片算法。由于分片算法和業(yè)務(wù)實(shí)現(xiàn)緊密相關(guān)，因此并未提供內(nèi)置分片算法，而是通過分 片策略將各種場景提煉出來，提供更高層級的抽象，并提供接口讓應(yīng)用開發(fā)者自行實(shí)現(xiàn)分片算法。

  • 標(biāo)準(zhǔn)分片算法
    對應(yīng) StandardShardingAlgorithm，用于處理使用單一鍵作為分片鍵的 =、IN、BETWEEN AND、>、<、>=、

    <= 進(jìn)行分片的場景。需要配合 StandardShardingStrategy 使用。

  • 復(fù)合分片算法

    對應(yīng) ComplexKeysShardingAlgorithm，用于處理使用多鍵作為分片鍵進(jìn)行分片的場景，包含多個分片鍵 的邏輯較復(fù)雜，需要應(yīng)用開發(fā)者自行處理其中的復(fù)雜度。需要配合 ComplexShardingStrategy 使用。-

• Hint 分片算法
  對應(yīng) HintShardingAlgorithm，用于處理使用 Hint 行分片的場景。需要配合 HintShardingStrategy 使用。

• 分片策略

  包含分片鍵和分片算法，由于分片算法的獨(dú)立性，將其獨(dú)立抽離。真正可用于分片操作的是分片鍵 + 分 片算法，也就是分片策略。目前提供 5 種分片策略。

  • 標(biāo)準(zhǔn)分片策略

    對應(yīng) StandardShardingStrategy。提供對 SQ L 語句中的 =, >, <, >=, <=, IN 和 BETWEEN AND 的分片操作 支持。StandardShardingStrategy 只支持單分片鍵，提供 PreciseShardingAlgorithm 和 RangeShardingAl- gorithm 兩個分片算法。PreciseShardingAlgorithm 是必選的，用于處理 = 和 IN 的分片。RangeShardingAl- gorithm 是可選的，用于處理 BETWEEN AND, >, <, >=, <= 分片，如果不配置 RangeShardingAlgorithm， SQL 中的 BETWEEN AND 將按照全庫路由處理。

  • 復(fù)合分片策略

  對應(yīng) ComplexShardingStrategy。復(fù)合分片策略。提供對 SQL 語句中的 =, >, <, >=, <=, IN 和 BETWEEN AND 的分片操作支持。ComplexShardingStrategy 支持多分片鍵，由于多分片鍵之間的關(guān)系復(fù)雜，因此并 未進(jìn)行過多的封裝，而是直接將分片鍵值組合以及分片操作符透傳至分片算法，完全由應(yīng)用開發(fā)者實(shí)現(xiàn)， 提供最大的靈活度。

  • Hint 分片策略
    對應(yīng) HintShardingStrategy。通過 Hint 指定分片值而非從 SQL 中提取分片值的方式進(jìn)行分片的策略。

  • 不分片策略
    對應(yīng) NoneShardingStrategy。不分片的策略。

  SQL Hint

  對于分片字段非 SQL 決定，而由其他外置條件決定的場景，可使用 SQL Hint 靈活的注入分片字段。例:

  內(nèi)部系統(tǒng)，按照員工登錄主鍵分庫，而數(shù)據(jù)庫中并無此字段。SQL Hint 支持通過 Java API 和 SQL 注釋 (待實(shí)現(xiàn))兩種方式使用。

• 分布式主鍵

  • 實(shí)現(xiàn)動機(jī)

  傳統(tǒng)數(shù)據(jù)庫軟件開發(fā)中，主鍵自動生成技術(shù)是基本需求。而各個數(shù)據(jù)庫對于該需求也提供了相應(yīng)的支持， 比如 MySQL 的自增鍵，Oracle 的自增序列等。數(shù)據(jù)分片后，不同數(shù)據(jù)節(jié)點(diǎn)生成全局唯一主鍵是非常棘手 的問題。同一個邏輯表內(nèi)的不同實(shí)際表之間的自增鍵由于無法互相感知而產(chǎn)生重復(fù)主鍵。雖然可通過約 束自增主鍵初始值和步?的方式避免碰撞，但需引入額外的運(yùn)維規(guī)則，使解決方案缺乏完整性和可擴(kuò)展 性。

  目前有許多第三方解決方案可以完美解決這個問題，如 UUID 等依靠特定算法自生成不重復(fù)鍵，或者通過 引入主鍵生成服務(wù)等。為了方便用戶使用、滿足不同用戶不同使用場景的需求，Apache ShardingSphere 不僅提供了內(nèi)置的分布式主鍵生成器，例如 UUID、SNOWFLAKE，還抽離出分布式主鍵生成器的接口， 方便用戶自行實(shí)現(xiàn)自定義的自增主鍵生成器。

  • 內(nèi)置的主鍵生成器

  UUID

  SNOWFLAKE

• 強(qiáng)制分片路由

  • 實(shí)現(xiàn)動機(jī)

  通過解析 SQL 語句提取分片鍵列與值并進(jìn)行分片是 Apache ShardingSphere 對 SQL 零侵入的實(shí)現(xiàn)方式。 若 SQL 語句中沒有分片條件，則無法進(jìn)行分片，需要全路由。

  在一些應(yīng)用場景中，分片條件并不存在于 SQL，而存在于外部業(yè)務(wù)邏輯。因此需要提供一種通過外部指 定分片結(jié)果的方式，在 Apache ShardingSphere 中叫做 Hint。

  • 實(shí)現(xiàn)機(jī)制

  Apache ShardingSphere 使用 ThreadLocal 管理分片鍵值。可以通過編程的方式向 HintManager 中 添加分片條件，該分片條件僅在當(dāng)前線程內(nèi)生效。

  除了通過編程的方式使用強(qiáng)制分片路由，Apache ShardingSphere 還計劃通過 SQL 中的特殊注釋的方式 引用 Hint，使開發(fā)者可以采用更加透明的方式使用該功能。

  指定了強(qiáng)制分片路由的 SQL 將會無視原有的分片邏輯，直接路由至指定的真實(shí)數(shù)據(jù)節(jié)點(diǎn)。

內(nèi)核剖析

ShardingSphere 的 3 個產(chǎn)品的數(shù)據(jù)分片主要流程是完全一致的。核心由 SQL 解析 => 執(zhí)行器優(yōu)化 => SQL 路由 => SQL 改寫 => SQL 執(zhí)行 => 結(jié)果歸并的流程組成。

SQL 解析

分為詞法解析和語法解析。先通過詞法解析器將 SQL 拆分為一個個不可再分的單詞。再使用語法解析器 對 SQL 進(jìn)行理解，并最終提煉出解析上下文。解析上下文包括表、選擇項(xiàng)、排序項(xiàng)、分組項(xiàng)、聚合函數(shù)、 分?信息、查詢條件以及可能需要修改的占位符的標(biāo)記。

執(zhí)行器優(yōu)化

合并和優(yōu)化分片條件，如 OR 等。 SQL 路由

根據(jù)解析上下文匹配用戶配置的分片策略，并生成路由路徑。目前支持分片路由和廣播路由。

SQL 改寫
將 SQL 改寫為在真實(shí)數(shù)據(jù)庫中可以正確執(zhí)行的語句。SQL 改寫分為正確性改寫和優(yōu)化改寫。

SQL 執(zhí)行 通過多線程執(zhí)行器異步執(zhí)行。

結(jié)果歸并

將多個執(zhí)行結(jié)果集歸并以便于通過統(tǒng)一的 JDBC 接口輸出。結(jié)果歸并包括流式歸并、內(nèi)存歸并和使用裝飾 者模式的追加歸并這幾種方式。

解析引擎

相對于其他編程語言，SQL 是比較簡單的。不過，它依然是一?完善的編程語言，因此對 SQL 的語法進(jìn) 行解析，與解析其他編程語言(如:Java 語言、C 語言、Go 語言等)并無本質(zhì)區(qū)別。

抽象語法樹

解析過程分為詞法解析和語法解析。詞法解析器用于將 SQL 拆解為不可再分的原子符號，稱為 Token。并 根據(jù)不同數(shù)據(jù)庫方言所提供的字典，將其歸類為關(guān)鍵字，表達(dá)式，字面量和操作符。再使用語法解析器 將詞法解析器的輸出轉(zhuǎn)換為抽象語法樹。

例如，以下 SQL:

 SELECT id, name FROM t_user WHERE status = 'ACTIVE' AND age > 18

截屏2021-01-19 下午11.07.10.png

為了便于理解，抽象語法樹中的關(guān)鍵字的 Token 用綠色表示，變量的 Token 用紅色表示，灰色表示需要 進(jìn)一步拆分。

最后，通過 visitor 對抽象語法樹遍歷構(gòu)造域模型，通過域模型 (SQLStatement) 去提煉分片所需的 上下文，并標(biāo)記有可能需要改寫的位置。供分片使用的解析上下文包含查詢選擇項(xiàng)(Select Items)、表信 息(Table)、分片條件(Sharding Condition)、自增主鍵信息(Auto increment Primary Key)、排序信 息(Order By)、分組信息(Group By)以及分?信息(Limit、Rownum、Top)。SQL 的一次解析過程 是不可逆的，一個個 Token 按 SQL 原本的順序依次進(jìn)行解析，性能很高?？紤]到各種數(shù)據(jù)庫 SQL 方言的 異同，在解析模塊提供了各類數(shù)據(jù)庫的 SQL 方言字典。

SQL 解析引擎

• 歷史

SQL 解析作為分庫分表類產(chǎn)品的核心，其性能和兼容性是最重要的衡量指標(biāo)。ShardingSphere 的 SQL 解 析器經(jīng)歷了 3 代產(chǎn)品的更新迭代。

第一代 SQL 解析器為了追求性能與快速實(shí)現(xiàn)，在 1.4.x 之前的版本使用 Druid 作為 SQL 解析器。經(jīng)實(shí)際 測試，它的性能遠(yuǎn)超其它解析器。

第二代 SQL 解析器從 1.5.x 版本開始，ShardingSphere 采用完全自研的 SQL 解析引擎。由于目的不同， ShardingSphere 并不需要將 SQL 轉(zhuǎn)為一顆完全的抽象語法樹，也無需通過訪問器模式進(jìn)行二次遍歷。它 采用對 SQL 半理解的方式，僅提煉數(shù)據(jù)分片需要關(guān)注的上下文，因此 SQL 解析的性能和兼容性得到了進(jìn) 一步的提高。

第三代 SQL 解析器從 3.0.x 版本開始，嘗試使用 ANTLR 作為 SQL 解析引擎的生成器，并采用 Visit 的方 式從 AST 中獲取 SQL Statement。從 5.0.x 版本開始，解析引擎的架構(gòu)已完成重構(gòu)調(diào)整，同時通過將第一 次解析的得到的 AST 放入緩存，方便下次直接獲取相同 SQL 的解析結(jié)果，來提高解析效率。因此我們建 議用戶采用 PreparedStatement 這種 SQL 預(yù)編譯的方式來提升性能。

功能點(diǎn)

? 提供獨(dú)立的 SQL 解析功能

? 可以非常方便的對語法規(guī)則進(jìn)行擴(kuò)充和修改 (使用了 ANTLR)

? 支持多種方言的 SQL 解析

路由引擎

根據(jù)解析上下文匹配數(shù)據(jù)庫和表的分片策略，并生成路由路徑。對于攜帶分片鍵的 SQL，根據(jù)分片鍵的 不同可以劃分為單片路由 (分片鍵的操作符是等號)、多片路由 (分片鍵的操作符是 IN) 和范圍路由 (分片 鍵的操作符是 BETWEEN)。不攜帶分片鍵的 SQL 則采用廣播路由。

分片策略通?？梢圆捎糜蓴?shù)據(jù)庫內(nèi)置或由用戶方配置。數(shù)據(jù)庫內(nèi)置的方案較為簡單，內(nèi)置的分片策略大 致可分為尾數(shù)取模、哈希、范圍、標(biāo)簽、時間等。由用戶方配置的分片策略則更加靈活，可以根據(jù)使用方 需求定制復(fù)合分片策略。

分片路由

用于根據(jù)分片鍵進(jìn)行路由的場景，又細(xì)分為直接路由、標(biāo)準(zhǔn)路由和笛卡爾積路由這 3 種類型。

直接路由

滿足直接路由的條件相對苛刻，它需要通過 Hint(使用 HintAPI 直接指定路由至庫表)方式分片，并且 是只分庫不分表的前提下，則可以避免 SQL 解析和之后的結(jié)果歸并。因此它的兼容性最好，可以執(zhí)行包 括子查詢、自定義函數(shù)等復(fù)雜情況的任意 SQL。直接路由還可以用于分片鍵不在 SQL 中的場景。例如，設(shè) 置用于數(shù)據(jù)庫分片的鍵為 3

hintManager.setDatabaseShardingValue(3);

假如路由算法為 value % 2，當(dāng)一個邏輯庫 t_order 對應(yīng) 2 個真實(shí)庫 t_order_0 和 t_order_1 時， 路由后 SQL 將在 t_order_1 上執(zhí)行。下方是使用 API 的代碼樣例:

String sql = "SELECT * FROM t_order"; try (
HintManager hintManager = HintManager.getInstance(); Connection conn = dataSource.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
hintManager.setDatabaseShardingValue(3); try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) { //...
} }
}

標(biāo)準(zhǔn)路由

標(biāo)準(zhǔn)路由是 ShardingSphere 最為推薦使用的分片方式，它的適用范圍是不包含關(guān)聯(lián)查詢或僅包含綁定表 之間關(guān)聯(lián)查詢的 SQL。當(dāng)分片運(yùn)算符是等于號時，路由結(jié)果將落入單庫(表)，當(dāng)分片運(yùn)算符是 BETWEEN 或 IN 時，則路由結(jié)果不一定落入唯一的庫(表)，因此一條邏輯 SQL 最終可能被拆分為多條用于執(zhí)行的 真實(shí) SQL。舉例說明，如果按照 order_id 的奇數(shù)和偶數(shù)進(jìn)行數(shù)據(jù)分片，一個單表查詢的 SQL 如下:

SELECT * FROM t_order WHERE order_id IN (1, 2);

那么路由的結(jié)果應(yīng)為:

SELECT * FROM t_order_0 WHERE order_id IN (1, 2); 
SELECT * FROM t_order_1 WHERE order_id IN (1, 2);

綁定表的關(guān)聯(lián)查詢與單表查詢復(fù)雜度和性能相當(dāng)。舉例說明，如果一個包含綁定表的關(guān)聯(lián)查詢的 SQL 如 下:

 SELECT * FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE order_ id IN (1, 2);

那么路由的結(jié)果應(yīng)為:

SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE order_id IN (1, 2);
SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE order_id IN (1, 2);

可以看到，SQL 拆分的數(shù)目與單表是一致的。

笛卡爾路由

笛卡爾路由是最復(fù)雜的情況，它無法根據(jù)綁定表的關(guān)系定位分片規(guī)則，因此非綁定表之間的關(guān)聯(lián)查詢需 要拆解為笛卡爾積組合執(zhí)行。如果上個示例中的 SQL 并未配置綁定表關(guān)系，那么路由的結(jié)果應(yīng)為:

SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE order_id IN (1, 2);
SELECT * FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE order_id IN (1, 2);
SELECT * FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE order_id IN (1, 2);
SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE order_id IN (1, 2);

笛卡爾路由查詢性能較低，需謹(jǐn)慎使用。

廣播路由

對于不攜帶分片鍵的 SQL，則采取廣播路由的方式。根據(jù) SQL 類型又可以劃分為全庫表路由、全庫路由、 全實(shí)例路由、單播路由和阻斷路由這 5 種類型。

全庫表路由

全庫表路由用于處理對數(shù)據(jù)庫中與其邏輯表相關(guān)的所有真實(shí)表的操作，主要包括不帶分片鍵的 DQL 和 DML，以及 DDL 等。例如:

SELECT * FROM t_order WHERE good_prority IN (1, 10);

則會遍歷所有數(shù)據(jù)庫中的所有表，逐一匹配邏輯表和真實(shí)表名，能夠匹配得上則執(zhí)行。路由后成為

SELECT * FROM t_order_0 WHERE good_prority IN (1, 10);
SELECT * FROM t_order_1 WHERE good_prority IN (1, 10); 
SELECT * FROM t_order_2 WHERE good_prority IN (1, 10); 
SELECT * FROM t_order_3 WHERE good_prority IN (1, 10);

全庫路由:

全庫路由用于處理對數(shù)據(jù)庫的操作，包括用于庫設(shè)置的 SET 類型的數(shù)據(jù)庫管理命令，以及 TCL 這樣的事 務(wù)控制語句。在這種情況下，會根據(jù)邏輯庫的名字遍歷所有符合名字匹配的真實(shí)庫，并在真實(shí)庫中執(zhí)行 該命令，例如:

SET autocommit=0;

在 t_order 中執(zhí)行，t_order 有 2 個真實(shí)庫。則實(shí)際會在 t_order_0 和 t_order_1 上都執(zhí)行這個命

令。

全實(shí)例路由

全實(shí)例路由用于 DCL 操作，授權(quán)語句針對的是數(shù)據(jù)庫的實(shí)例。無論一個實(shí)例中包含多少個 Schema，每 個數(shù)據(jù)庫的實(shí)例只執(zhí)行一次。例如:

CREATE USER customer@127.0.0.1 identified BY '123';

單播路由

單播路由用于獲取某一真實(shí)表信息的場景，它僅需要從任意庫中的任意真實(shí)表中獲取數(shù)據(jù)即可。例如:

DESCRIBE t_order;

t_order 的兩個真實(shí)表 t_order_0，t_order_1 的描述結(jié)構(gòu)相同，所以這個命令在任意真實(shí)表上選擇執(zhí)行一

次。

阻斷路由

阻斷路由用于屏蔽 SQL 對數(shù)據(jù)庫的操作，例如:

USE order_db;

這個命令不會在真實(shí)數(shù)據(jù)庫中執(zhí)行，因?yàn)?ShardingSphere 采用的是邏輯 Schema 的方式，無需將切換數(shù) 據(jù)庫 Schema 的命令發(fā)送至數(shù)據(jù)庫中。

改寫引擎

工程師面向邏輯庫與邏輯表書寫的 SQL，并不能夠直接在真實(shí)的數(shù)據(jù)庫中執(zhí)行，SQL 改寫用于將邏輯 SQL 改寫為在真實(shí)數(shù)據(jù)庫中可以正確執(zhí)行的 SQL。它包括正確性改寫和優(yōu)化改寫兩部分。

正確性改寫

在包含分表的場景中，需要將分表配置中的邏輯表名稱改寫為路由之后所獲取的真實(shí)表名稱。僅分庫則 不需要表名稱的改寫。除此之外，還包括補(bǔ)列和分?信息修正等內(nèi)容。

標(biāo)識符改寫

需要改寫的標(biāo)識符包括表名稱、索引名稱以及 Schema 名稱。
表名稱改寫是指將找到邏輯表在原始 SQL 中的位置，并將其改寫為真實(shí)表的過程。表名稱改寫是一個典

型的需要對 SQL 進(jìn)行解析的場景。從一個最簡單的例子開始，若邏輯 SQL 為:

SELECT order_id FROM t_order WHERE order_id=1;

假設(shè)該 SQL 配置分片鍵 order_id，并且 order_id=1 的情況，將路由至分片表 1。那么改寫之后的 SQL 應(yīng) 該為:

SELECT order_id FROM t_order_1 WHERE order_id=1;

在這種最簡單的 SQL 場景中，是否將 SQL 解析為抽象語法樹似乎無關(guān)緊要，只要通過字符串查找和替換

就可以達(dá)到SQL改寫的效果。但是下面的場景，就無法僅僅通過字符串的查找替換來正確的改寫SQL了：

SELECT order_id FROM t_order WHERE order_id=1 AND remarks=' t_order xxx';

正確改寫的 SQL 應(yīng)該是:

SELECT order_id FROM t_order_1 WHERE order_id=1 AND remarks=' t_order xxx';

而非:

SELECT order_id FROM t_order_1 WHERE order_id=1 AND remarks=' t_order_1 xxx';

由于表名之外可能含有表名稱的類似字符，因此不能通過簡單的字符串替換的方式去改寫 SQL。

下面再來看一個更加復(fù)雜的 SQL 改寫場景:

 SELECT t_order.order_id FROM t_order WHERE t_order.order_id=1 AND remarks=' t_order xxx';

正確改寫的 SQL 應(yīng)該是:

SELECT order_id FROM t_order_1 WHERE order_id=1 AND remarks=' t_order xxx';

而非:

SELECT order_id FROM t_order_1 WHERE order_id=1 AND remarks=' t_order_1 xxx';

由于表名之外可能含有表名稱的類似字符，因此不能通過簡單的字符串替換的方式去改寫 SQL。

下面再來看一個更加復(fù)雜的 SQL 改寫場景:

  SELECT t_order.order_id FROM t_order WHERE t_order.order_id=1 AND remarks=' t_order xxx';

上面的 SQL 將表名作為字段的標(biāo)識符，因此在 SQL 改寫時需要一并修改:

 SELECT t_order_1.order_id FROM t_order_1 WHERE t_order_1.order_id=1 AND remarks=' t_order xxx';

而如果 SQL 中定義了表的別名，則無需連同別名一起修改，即使別名與表名相同亦是如此。例如:

  SELECT t_order.order_id FROM t_order AS t_order WHERE t_order.order_id=1 AND remarks=' t_order xxx';

SQL 改寫則僅需要改寫表名稱就可以了:

 SELECT t_order.order_id FROM t_order_1 AS t_order WHERE t_order.order_id=1 AND remarks=' t_order xxx';

補(bǔ)列

需要在查詢語句中補(bǔ)列通常由兩種情況導(dǎo)致。第一種情況是 ShardingSphere 需要在結(jié)果歸并時獲取相應(yīng) 數(shù)據(jù)，但該數(shù)據(jù)并未能通過查詢的 SQL 返回。這種情況主要是針對 GROUP BY 和 ORDER BY。結(jié)果歸并 時，需要根據(jù) GROUP BY 和 ORDER BY 的字段項(xiàng)進(jìn)行分組和排序，但如果原始 SQL 的選擇項(xiàng)中若并未包 含分組項(xiàng)或排序項(xiàng)，則需要對原始 SQL 進(jìn)行改寫。先看一下原始 SQL 中帶有結(jié)果歸并所需信息的場景:

SELECT order_id, user_id FROM t_order ORDER BY user_id;

由于使用 user_id 進(jìn)行排序，在結(jié)果歸并中需要能夠獲取到 user_id 的數(shù)據(jù)，而上面的 SQL 是能夠獲取

到 user_id 數(shù)據(jù)的，因此無需補(bǔ)列。 如果選擇項(xiàng)中不包含結(jié)果歸并時所需的列，則需要進(jìn)行補(bǔ)列，如以下 SQL:

SELECT order_id FROM t_order ORDER BY user_id;

由于原始 SQL 中并不包含需要在結(jié)果歸并中需要獲取的 user_id，因此需要對 SQL 進(jìn)行補(bǔ)列改寫。補(bǔ)列

之后的 SQL 是:

SELECT order_id, user_id AS ORDER_BY_DERIVED_0 FROM t_order ORDER BY user_id;

值得一提的是，補(bǔ)列只會補(bǔ)充缺失的列，不會全部補(bǔ)充，而且，在 SELECT 語句中包含 * 的 SQL，也會根 據(jù)表的元數(shù)據(jù)信息選擇性補(bǔ)列。下面是一個較為復(fù)雜的 SQL 補(bǔ)列場景:

 SELECT o.* FROM t_order o, t_order_item i WHERE o.order_id=i.order_id ORDER BY user_id, order_item_id;

我們假設(shè)只有 t_order_item 表中包含 order_item_id 列，那么根據(jù)表的元數(shù)據(jù)信息可知，在結(jié)果歸并時， 排序項(xiàng)中的 user_id 是存在于 t_order 表中的，無需補(bǔ)列;order_item_id 并不在 t_order 中，因此需要補(bǔ) 列。補(bǔ)列之后的 SQL 是:

 SELECT o.*, order_item_id AS ORDER_BY_DERIVED_0 FROM t_order o, t_order_item i WHERE o.order_id=i.order_id ORDER BY user_id, order_item_id;

補(bǔ)列的另一種情況是使用 AVG 聚合函數(shù)。在分布式的場景中，使用 avg1 + avg2 + avg3 / 3 計算平均值并 不正確，需要改寫為 (sum1 + sum2 + sum3) / (count1 + count2 + count3)。這就需要將包含 AVG 的 SQL 改寫為 SUM 和 COUNT，并在結(jié)果歸并時重新計算平均值。例如以下 SQL:

SELECT AVG(price) FROM t_order WHERE user_id=1;

需要改寫為:

 SELECT COUNT(price) AS AVG_DERIVED_COUNT_0, SUM(price) AS AVG_DERIVED_SUM_0 FROM t_ order WHERE user_id=1;

然后才能夠通過結(jié)果歸并正確的計算平均值。

最后一種補(bǔ)列是在執(zhí)行 INSERT 的 SQL 語句時，如果使用數(shù)據(jù)庫自增主鍵，是無需寫入主鍵字段的。但 數(shù)據(jù)庫的自增主鍵是無法滿足分布式場景下的主鍵唯一的，因此 ShardingSphere 提供了分布式自增主鍵 的生成策略，并且可以通過補(bǔ)列，讓使用方無需改動現(xiàn)有代碼，即可將分布式自增主鍵透明的替換數(shù)據(jù)庫 現(xiàn)有的自增主鍵。分布式自增主鍵的生成策略將在下文中詳述，這里只闡述與 SQL 改寫相關(guān)的內(nèi)容。舉 例說明，假設(shè)表 t_order 的主鍵是 order_id，原始的 SQL 為:

INSERT INTO t_order (`field1`, `field2`) VALUES (10, 1);

可以看到，上述 SQL 中并未包含自增主鍵，是需要數(shù)據(jù)庫自行填充的。ShardingSphere 配置自增主鍵后，

SQL 將改寫為:

INSERT INTO t_order (`field1`, `field2`, order_id) VALUES (10, 1, xxxxx);

改寫后的 SQL 將在 INSERT FIELD 和 INSERT VALUE 的最后部分增加主鍵列名稱以及自動生成的自增主 鍵值。上述 SQL 中的 xxxxx 表示自動生成的自增主鍵值。

如果 INSERT 的 SQL 中并未包含表的列名稱，ShardingSphere 也可以根據(jù)判斷參數(shù)個數(shù)以及表元信息中 的列數(shù)量對比，并自動生成自增主鍵。例如，原始的 SQL 為:

INSERT INTO t_order VALUES (10, 1);

改寫的 SQL 將只在主鍵所在的列順序處增加自增主鍵即可:

INSERT INTO t_order VALUES (xxxxx, 10, 1);

分?修正

從多個數(shù)據(jù)庫獲取分?數(shù)據(jù)與單數(shù)據(jù)庫的場景是不同的。假設(shè)每 10 條數(shù)據(jù)為一?，取第 2 ?數(shù)據(jù)。在分 片環(huán)境下獲取 LIMIT 10, 10，歸并之后再根據(jù)排序條件取出前 10 條數(shù)據(jù)是不正確的。舉例說明，若 SQL 為:

SELECT score FROM t_score ORDER BY score DESC LIMIT 1, 2;

截屏2021-01-19 下午11.09.03.png

截屏2021-01-19 下午11.09.32.png

越獲取偏移量位置靠后數(shù)據(jù)，使用 LIMIT 分?方式的效率就越低。有很多方法可以避免使用 LIMIT 進(jìn)行 分?。比如構(gòu)建行記錄數(shù)量與行偏移量的二級索引，或使用上次分?數(shù)據(jù)結(jié)尾 ID 作為下次查詢條件的分 ?方式等。

分?信息修正時，如果使用占位符的方式書寫 SQL，則只需要改寫參數(shù)列表即可，無需改寫 SQL 本身。

批量拆分

在使用批量插入的 SQL 時，如果插入的數(shù)據(jù)是跨分片的，那么需要對 SQL 進(jìn)行改寫來防止將多余的數(shù)據(jù) 寫入到數(shù)據(jù)庫中。插入操作與查詢操作的不同之處在于，查詢語句中即使用了不存在于當(dāng)前分片的分片 鍵，也不會對數(shù)據(jù)產(chǎn)生影響;而插入操作則必須將多余的分片鍵刪除。舉例說明，如下 SQL:

INSERT INTO t_order (order_id, xxx) VALUES (1, 'xxx'), (2, 'xxx'), (3, 'xxx');

假設(shè)數(shù)據(jù)庫仍然是按照 order_id 的奇偶值分為兩片的，僅將這條 SQL 中的表名進(jìn)行修改，然后發(fā)送至數(shù) 據(jù)庫完成 SQL 的執(zhí)行，則兩個分片都會寫入相同的記錄。雖然只有符合分片查詢條件的數(shù)據(jù)才能夠被查 詢語句取出，但存在冗余數(shù)據(jù)的實(shí)現(xiàn)方案并不合理。因此需要將 SQL 改寫為:

 INSERT INTO t_order_0 (order_id, xxx) VALUES (2, 'xxx');
INSERT INTO t_order_1 (order_id, xxx) VALUES (1, 'xxx'), (3, 'xxx');

優(yōu)化改寫

優(yōu)化改寫的目的是在不影響查詢正確性的情況下，對性能進(jìn)行提升的有效手段。它分為單節(jié)點(diǎn)優(yōu)化和流 式歸并優(yōu)化。

單節(jié)點(diǎn)優(yōu)化

路由至單節(jié)點(diǎn)的 SQL，則無需優(yōu)化改寫。當(dāng)獲得一次查詢的路由結(jié)果后，如果是路由至唯一的數(shù)據(jù)節(jié)點(diǎn)， 則無需涉及到結(jié)果歸并。因此補(bǔ)列和分?信息等改寫都沒有必要進(jìn)行。尤其是分?信息的改寫，無需將 數(shù)據(jù)從第 1 條開始取，大量的降低了對數(shù)據(jù)庫的壓力，并且節(jié)省了網(wǎng)絡(luò)帶寬的無謂消耗。

流式歸并優(yōu)化

它僅為包含 GROUP BY 的 SQL 增加 ORDER BY 以及和分組項(xiàng)相同的排序項(xiàng)和排序順序，用于將內(nèi)存歸 并轉(zhuǎn)化為流式歸并。在結(jié)果歸并的部分中，將對流式歸并和內(nèi)存歸并進(jìn)行詳細(xì)說明。

連接模式

從資源控制的?度看，業(yè)務(wù)方訪問數(shù)據(jù)庫的連接數(shù)量應(yīng)當(dāng)有所限制。它能夠有效地防止某一業(yè)務(wù)操作過 多的占用資源，從而將數(shù)據(jù)庫連接的資源耗盡，以致于影響其他業(yè)務(wù)的正常訪問。特別是在一個數(shù)據(jù)庫實(shí) 例中存在較多分表的情況下，一條不包含分片鍵的邏輯 SQL 將產(chǎn)生落在同庫不同表的大量真實(shí) SQL ，如 果每條真實(shí) SQL 都占用一個獨(dú)立的連接，那么一次查詢無疑將會占用過多的資源。

從執(zhí)行效率的?度看，為每個分片查詢維持一個獨(dú)立的數(shù)據(jù)庫連接，可以更加有效的利用多線程來提升 執(zhí)行效率。為每個數(shù)據(jù)庫連接開啟獨(dú)立的線程，可以將 I/O 所產(chǎn)生的消耗并行處理。為每個分片維持一個 獨(dú)立的數(shù)據(jù)庫連接，還能夠避免過早的將查詢結(jié)果數(shù)據(jù)加載至內(nèi)存。獨(dú)立的數(shù)據(jù)庫連接，能夠持有查詢 結(jié)果集游標(biāo)位置的引用，在需要獲取相應(yīng)數(shù)據(jù)時移動游標(biāo)即可。

以結(jié)果集游標(biāo)下移進(jìn)行結(jié)果歸并的方式，稱之為流式歸并，它無需將結(jié)果數(shù)據(jù)全數(shù)加載至內(nèi)存，可以有效 的節(jié)省內(nèi)存資源，進(jìn)而減少垃圾回收的頻次。當(dāng)無法保證每個分片查詢持有一個獨(dú)立數(shù)據(jù)庫連接時，則 需要在復(fù)用該數(shù)據(jù)庫連接獲取下一張分表的查詢結(jié)果集之前，將當(dāng)前的查詢結(jié)果集全數(shù)加載至內(nèi)存。因 此，即使可以采用流式歸并，在此場景下也將退化為內(nèi)存歸并。

一方面是對數(shù)據(jù)庫連接資源的控制保護(hù)，一方面是采用更優(yōu)的歸并模式達(dá)到對中間件內(nèi)存資源的節(jié)省，如 何處理好兩者之間的關(guān)系，是 ShardingSphere 執(zhí)行引擎需要解決的問題。具體來說，如果一條 SQL 在經(jīng) 過 ShardingSphere 的分片后，需要操作某數(shù)據(jù)庫實(shí)例下的 200 張表。那么，是選擇創(chuàng)建 200 個連接并行 執(zhí)行，還是選擇創(chuàng)建一個連接串行執(zhí)行呢?效率與資源控制又應(yīng)該如何抉擇呢?

針對上述場景，ShardingSphere 提供了一種解決思路。它提出了連接模式(Connection Mode)的概念， 將其劃分為內(nèi)存限制模式(MEMORY_STRICTLY)和連接限制模式(CONNECTION_STRICTLY)這兩種 類型。

內(nèi)存限制模式

使用此模式的前提是，ShardingSphere 對一次操作所耗費(fèi)的數(shù)據(jù)庫連接數(shù)量不做限制。如果實(shí)際執(zhí)行的 SQL 需要對某數(shù)據(jù)庫實(shí)例中的 200 張表做操作，則對每張表創(chuàng)建一個新的數(shù)據(jù)庫連接，并通過多線程的 方式并發(fā)處理，以達(dá)成執(zhí)行效率最大化。并且在 SQL 滿足條件情況下，優(yōu)先選擇流式歸并，以防止出現(xiàn) 內(nèi)存溢出或避免頻繁垃圾回收情況。

連接限制模式

使用此模式的前提是，ShardingSphere 嚴(yán)格控制對一次操作所耗費(fèi)的數(shù)據(jù)庫連接數(shù)量。如果實(shí)際執(zhí)行的 SQL 需要對某數(shù)據(jù)庫實(shí)例中的 200 張表做操作，那么只會創(chuàng)建唯一的數(shù)據(jù)庫連接，并對其 200 張表串行 處理。如果一次操作中的分片散落在不同的數(shù)據(jù)庫，仍然采用多線程處理對不同庫的操作，但每個庫的 每次操作仍然只創(chuàng)建一個唯一的數(shù)據(jù)庫連接。這樣即可以防止對一次請求對數(shù)據(jù)庫連接占用過多所帶來 的問題。該模式始終選擇內(nèi)存歸并。

內(nèi)存限制模式適用于 OLAP 操作，可以通過放寬對數(shù)據(jù)庫連接的限制提升系統(tǒng)吞吐量;連接限制模式適 用于 OLTP 操作，OLTP 通常帶有分片鍵，會路由到單一的分片，因此嚴(yán)格控制數(shù)據(jù)庫連接，以保證在線 系統(tǒng)數(shù)據(jù)庫資源能夠被更多的應(yīng)用所使用，是明智的選擇。

自動化執(zhí)行引擎

ShardingSphere 最初將使用何種模式的決定權(quán)交由用戶配置，讓開發(fā)者依據(jù)自己業(yè)務(wù)的實(shí)際場景需求選 擇使用內(nèi)存限制模式或連接限制模式。

這種解決方案將兩難的選擇的決定權(quán)交由用戶，使得用戶必須要了解這兩種模式的利弊，并依據(jù)業(yè)務(wù)場 景需求進(jìn)行選擇。這無疑增加了用戶對 ShardingSphere 的學(xué)習(xí)和使用的成本，并非最優(yōu)方案。

這種一分為二的處理方案，將兩種模式的切換交由靜態(tài)的初始化配置，是缺乏靈活應(yīng)對能力的。在實(shí)際的 使用場景中，面對不同 SQL 以及占位符參數(shù)，每次的路由結(jié)果是不同的。這就意味著某些操作可能需要使 用內(nèi)存歸并，而某些操作則可能選擇流式歸并更優(yōu)，具體采用哪種方式不應(yīng)該由用戶在 ShardingSphere 啟動之前配置好，而是應(yīng)該根據(jù) SQL 和占位符參數(shù)的場景，來動態(tài)的決定連接模式。

為了降低用戶的使用成本以及連接模式動態(tài)化這兩個問題，ShardingSphere 提煉出自動化執(zhí)行引擎的思 路，在其內(nèi)部消化了連接模式概念。用戶無需了解所謂的內(nèi)存限制模式和連接限制模式是什么，而是交 由執(zhí)行引擎根據(jù)當(dāng)前場景自動選擇最優(yōu)的執(zhí)行方案。

自動化執(zhí)行引擎將連接模式的選擇粒度細(xì)化至每一次 SQL 的操作。針對每次 SQL 請求，自動化執(zhí)行引擎 都將根據(jù)其路由結(jié)果，進(jìn)行實(shí)時的演算和權(quán)衡，并自主地采用恰當(dāng)?shù)倪B接模式執(zhí)行，以達(dá)到資源控制和 效率的最優(yōu)平衡。針對自動化的執(zhí)行引擎，用戶只需配置 maxConnectionSizePerQuery 即可，該參數(shù)表 示一次查詢時每個數(shù)據(jù)庫所允許使用的最大連接數(shù)。

執(zhí)行引擎分為準(zhǔn)備和執(zhí)行兩個階段。

準(zhǔn)備階段

顧名思義，此階段用于準(zhǔn)備執(zhí)行的數(shù)據(jù)。它分為結(jié)果集分組和執(zhí)行單元創(chuàng)建兩個步驟。

結(jié)果集分組是實(shí)現(xiàn)內(nèi)化連接模式概念的關(guān)鍵。執(zhí)行引擎根據(jù) maxConnectionSizePerQuery 配置項(xiàng)，結(jié)合 當(dāng)前路由結(jié)果，選擇恰當(dāng)?shù)倪B接模式。具體步驟如下:

1. 將 SQL 的路由結(jié)果按照數(shù)據(jù)源的名稱進(jìn)行分組。
2. 通過下圖的公式，可以獲得每個數(shù)據(jù)庫實(shí)例在maxConnectionSizePerQuery的允許范圍內(nèi)，每個連接需要執(zhí)行的 SQL 路由結(jié)果組，并計算出本次請求的最優(yōu)連接模式。

截屏2021-01-19 下午11.10.01.png

在 maxConnectionSizePerQuery 允許的范圍內(nèi)，當(dāng)一個連接需要執(zhí)行的請求數(shù)量大于 1 時，意味著當(dāng)前 的數(shù)據(jù)庫連接無法持有相應(yīng)的數(shù)據(jù)結(jié)果集，則必須采用內(nèi)存歸并;反之，當(dāng)一個連接需要執(zhí)行的請求數(shù) 量等于 1 時，意味著當(dāng)前的數(shù)據(jù)庫連接可以持有相應(yīng)的數(shù)據(jù)結(jié)果集，則可以采用流式歸并。

每一次的連接模式的選擇，是針對每一個物理數(shù)據(jù)庫的。也就是說，在同一次查詢中，如果路由至一個 以上的數(shù)據(jù)庫，每個數(shù)據(jù)庫的連接模式不一定一樣，它們可能是混合存在的形態(tài)。

通過上一步驟獲得的路由分組結(jié)果創(chuàng)建執(zhí)行的單元。當(dāng)數(shù)據(jù)源使用數(shù)據(jù)庫連接池等控制數(shù)據(jù)庫連接數(shù)量 的技術(shù)時，在獲取數(shù)據(jù)庫連接時，如果不妥善處理并發(fā)，則有一定幾率發(fā)生死鎖。在多個請求相互等待 對方釋放數(shù)據(jù)庫連接資源時，將會產(chǎn)生饑餓等待，造成交叉的死鎖問題。

舉例說明，假設(shè)一次查詢需要在某一數(shù)據(jù)源上獲取兩個數(shù)據(jù)庫連接，并路由至同一個數(shù)據(jù)庫的兩個分表 查詢。則有可能出現(xiàn)查詢 A 已獲取到該數(shù)據(jù)源的 1 個數(shù)據(jù)庫連接，并等待獲取另一個數(shù)據(jù)庫連接;而查 詢 B 也已經(jīng)在該數(shù)據(jù)源上獲取到的一個數(shù)據(jù)庫連接，并同樣等待另一個數(shù)據(jù)庫連接的獲取。如果數(shù)據(jù)庫 連接池的允許最大連接數(shù)是 2，那么這 2 個查詢請求將永久的等待下去。

聚合歸并

無論是流式分組歸并還是內(nèi)存分組歸并，對聚合函數(shù)的處理都是一致的。除了分組的 SQL 之外，不進(jìn)行 分組的 SQL 也可以使用聚合函數(shù)。因此，聚合歸并是在之前介紹的歸并類的之上追加的歸并能力，即裝 飾者模式。聚合函數(shù)可以歸類為比較、累加和求平均值這 3 種類型。

比較類型的聚合函數(shù)是指 MAX 和 MIN。它們需要對每一個同組的結(jié)果集數(shù)據(jù)進(jìn)行比較，并且直接返回其 最大或最小值即可。

累加類型的聚合函數(shù)是指 SUM 和 COUNT。它們需要將每一個同組的結(jié)果集數(shù)據(jù)進(jìn)行累加。 求平均值的聚合函數(shù)只有 AVG。它必須通過 SQL 改寫的 SUM 和 COUNT 進(jìn)行計算，相關(guān)內(nèi)容已在 SQL 改寫的內(nèi)容中涵蓋，不再贅述。

分?歸并

上文所述的所有歸并類型都可能進(jìn)行分?。分?也是追加在其他歸并類型之上的裝飾器，ShardingSphere 通過裝飾者模式來增加對數(shù)據(jù)結(jié)果集進(jìn)行分?的能力。分?歸并負(fù)責(zé)將無需獲取的數(shù)據(jù)過濾掉。

ShardingSphere 的分?功能比較容易讓使用者誤解，用戶通常認(rèn)為分?歸并會占用大量內(nèi)存。在分布式 的場景中，將 LIMIT 10000000, 10 改寫為 LIMIT 0, 10000010，才能保證其數(shù)據(jù)的正確性。用戶 非常容易產(chǎn)生 ShardingSphere 會將大量無意義的數(shù)據(jù)加載至內(nèi)存中，造成內(nèi)存溢出?險的錯覺。其實(shí)， 通過流式歸并的原理可知，會將數(shù)據(jù)全部加載到內(nèi)存中的只有內(nèi)存分組歸并這一種情況。而通常來說，進(jìn) 行 OLAP 的分組 SQL，不會產(chǎn)生大量的結(jié)果數(shù)據(jù)，它更多的用于大量的計算，以及少量結(jié)果產(chǎn)出的場景。 除了內(nèi)存分組歸并這種情況之外，其他情況都通過流式歸并獲取數(shù)據(jù)結(jié)果集，因此 ShardingSphere 會通 過結(jié)果集的 next 方法將無需取出的數(shù)據(jù)全部跳過，并不會將其存入內(nèi)存。

但同時需要注意的是，由于排序的需要，大量的數(shù)據(jù)仍然需要傳輸?shù)?ShardingSphere 的內(nèi)存空間。因此， 采用 LIMIT 這種方式分?，并非最佳實(shí)踐。由于 LIMIT 并不能通過索引查詢數(shù)據(jù)，因此如果可以保證 ID 的連續(xù)性，通過 ID 進(jìn)行分?是比較好的解決方案，例如:

SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id; 

或通過記錄上次查詢結(jié)果的最后一條記錄的 ID 進(jìn)行下一?的查詢，例如:

SELECT * FROM t_order WHERE id > 10000000 LIMIT 10;

使用規(guī)范

支持項(xiàng)

路由至單數(shù)據(jù)節(jié)點(diǎn)

? 100% 全兼容(目前僅 MySQL，其他數(shù)據(jù)庫完善中)。 路由至多數(shù)據(jù)節(jié)點(diǎn)

全面支持 DML、DDL、DCL、TCL 和部分 DAL。支持分?、去重、排序、分組、聚合、關(guān)聯(lián)查詢(不支 持跨庫關(guān)聯(lián))。以下用最為復(fù)雜的 DML 舉例:

? SELECT 主語句

SELECT select_expr [, select_expr ...] FROM table_reference [, table_reference ...] [WHERE predicates]
[GROUP BY {col_name | position} [ASC | DESC], ...]
[ORDER BY {col_name | position} [ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

? select_expr

*|
[DISTINCT] COLUMN_NAME [AS] [alias] |
(MAX | MIN | SUM | AVG)(COLUMN_NAME | alias) [AS] [alias] | COUNT(* | COLUMN_NAME | alias) [AS] [alias]

? table_reference

tbl_name [AS] alias] [index_hint_list]
| table_reference ([INNER] | {LEFT|RIGHT} [OUTER]) JOIN table_factor [JOIN ON conditional_expr | USING (column_list)]

不支持項(xiàng)

路由至多數(shù)據(jù)節(jié)點(diǎn)

部分支持 CASE WHEN * CASE WHEN 中包含子查詢不支持 * CASE WHEN 中使用邏輯表名不支持(請使 用表別名)不支持 HAVING、UNION (ALL)

部分支持子查詢 * 子查詢中使用 WHERE 條件時，必須包含分片鍵，當(dāng)外層查詢中也包含分片鍵時，子 查詢和外層查詢中的分片鍵必須保持一致

除了分?子查詢的支持之外 (詳情請參考分?)，也支持同等模式的子查詢。無論嵌套多少層，Sharding- Sphere 都可以解析至第一個包含數(shù)據(jù)表的子查詢，一旦在下層嵌套中再次找到包含數(shù)據(jù)表的子查詢將直 接拋出解析異常。

例如，以下子查詢可以支持:

SELECT COUNT(*) FROM (SELECT * FROM t_order) o;
SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE order_id = 1) o;
SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE order_id = 1) o WHERE o.order_id = 1;

以下子查詢不支持:

SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE product_id = 1) o;
SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE order_id = 1) o WHERE o.order_id = 2;

簡單來說，通過子查詢進(jìn)行非功能需求，在大部分情況下是可以支持的。比如分?、統(tǒng)計總數(shù)等;而通過 子查詢實(shí)現(xiàn)業(yè)務(wù)查詢當(dāng)前并不能支持。

由于歸并的限制，子查詢中包含聚合函數(shù)目前無法支持。
不支持包含 schema 的 SQL。因?yàn)?ShardingSphere 的理念是像使用一個數(shù)據(jù)源一樣使用多數(shù)據(jù)源，因此

對 SQL 的訪問都是在同一個邏輯 schema 之上。

對分片鍵進(jìn)行操作

運(yùn)算表達(dá)式和函數(shù)中的分片鍵會導(dǎo)致全路由。
假設(shè) create_time 為分片鍵，則無法精確路由形如 SQL:

SELECT * FROM t_order WHERE to_date(create_time, 'yyyy-mm-dd') = '2019-01-01'; 由于ShardingSphere只能通過SQL 字面提取用于分片的值，因此當(dāng)分片鍵處于運(yùn)算表達(dá)式或函數(shù)中時，

ShardingSphere 無法提前獲取分片鍵位于數(shù)據(jù)庫中的值，從而無法計算出真正的分片值。 當(dāng)出現(xiàn)此類分片鍵處于運(yùn)算表達(dá)式或函數(shù)中的 SQL 時，ShardingSphere 將采用全路由的形式獲取結(jié)果。

示例
支持的 SQL
不支持的 SQL
DISTINCT 支持情況詳細(xì)說明 支持的 SQL

SQL

SELECT DISTINCT * FROM tbl_name WHERE col1 = ?

SELECT DISTINCT col1 FROM tbl_name
SELECT DISTINCT col1, col2, col3 FROM tbl_name SELECT DISTINCT col1 FROM tbl_name ORDER BY col1 SELECT DISTINCT col1 FROM tbl_name ORDER BY col2 SELECT DISTINCT(col1) FROM tbl_name

SELECT AVG(DISTINCT col1) FROM tbl_name
SELECT SUM(DISTINCT col1) FROM tbl_name
SELECT COUNT(DISTINCT col1) FROM tbl_name
SELECT COUNT(DISTINCT col1) FROM tbl_name GROUP BY col1 SELECT COUNT(DISTINCT col1 + col2) FROM tbl_name

SELECT COUNT(DISTINCT col1), SUM(DISTINCT col1) FROM tbl_name SELECT COUNT(DISTINCT col1), col1 FROM tbl_name GROUP BY col1 SELECT col1, COUNT(DISTINCT col1) FROM tbl_name GROUP BY col1

不支持的 SQL 分?

完全支持 MySQL、PostgreSQL 和 Oracle 的分?查詢，SQLServer 由于分?查詢較為復(fù)雜，僅部分支持。 分?性能

性能瓶頸

查詢偏移量過大的分?會導(dǎo)致數(shù)據(jù)庫獲取數(shù)據(jù)性能低下，以 MySQL 為例: SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10

這句 SQL 會使得 MySQL 在無法利用索引的情況下跳過 1000000 條記錄后，再獲取 10 條記錄，其性能可 想而知。而在分庫分表的情況下(假設(shè)分為 2 個庫)，為了保證數(shù)據(jù)的正確性，SQL 會改寫為:

SELECT * FROM t_order ORDER BY id LIMIT 0, 1000010

即將偏移量前的記錄全部取出，并僅獲取排序后的最后 10 條記錄。這會在數(shù)據(jù)庫本身就執(zhí)行很慢的情 況下，進(jìn)一步加劇性能瓶頸。因?yàn)樵?SQL 僅需要傳輸 10 條記錄至客戶端，而改寫之后的 SQL 則會傳輸 1,000,010 * 2 的記錄至客戶端。

ShardingSphere 的優(yōu)化
ShardingSphere 進(jìn)行了 2 個方面的優(yōu)化。

首先，采用流式處理 + 歸并排序的方式來避免內(nèi)存的過量占用。由于 SQL 改寫不可避免的占用了額外的帶 寬，但并不會導(dǎo)致內(nèi)存暴漲。與直覺不同，大多數(shù)人認(rèn)為 ShardingSphere 會將 1,000,010 * 2 記錄全部 加載至內(nèi)存，進(jìn)而占用大量內(nèi)存而導(dǎo)致內(nèi)存溢出。但由于每個結(jié)果集的記錄是有序的，因此 ShardingSphere 每次比較僅獲取各個分片的當(dāng)前結(jié)果集記錄，駐留在內(nèi)存中的記錄僅為當(dāng)前路由到的分片的結(jié)果集的當(dāng) 前游標(biāo)指向而已。對于本身即有序的待排序?qū)ο?，歸并排序的時間復(fù)雜度僅為 O(n)，性能損耗很小。

其次，ShardingSphere 對僅落至單分片的查詢進(jìn)行進(jìn)一步優(yōu)化。落至單分片查詢的請求并不需要改寫 SQL 也可以保證記錄的正確性，因此在此種情況下，ShardingSphere 并未進(jìn)行 SQL 改寫，從而達(dá)到節(jié)省帶寬 的目的。

分?方案優(yōu)化

由于 LIMIT 并不能通過索引查詢數(shù)據(jù)，因此如果可以保證 ID 的連續(xù)性，通過 ID 進(jìn)行分?是比較好的解 決方案:

SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id 或通過記錄上次查詢結(jié)果的最后一條記錄的 ID 進(jìn)行下一?的查詢:

SELECT * FROM t_order WHERE id > 100000 LIMIT 10

分?子查詢

Oracle 和 SQLServer 的分?都需要通過子查詢來處理，ShardingSphere 支持分?相關(guān)的子查詢。 ? Oracle

支持使用 rownum 進(jìn)行分?:

目前不支持 rownum + BETWEEN 的分?方式。 ? SQLServer

支持使用 TOP + ROW_NUMBER() OVER 配合進(jìn)行分?:

支持 SQLServer 2012 之后的 OFFSET FETCH 的分?方式:
SELECT * FROM t_order o ORDER BY id OFFSET ? ROW FETCH NEXT ? ROWS ONLY

目前不支持使用 WITH xxx AS (SELECT ...) 的方式進(jìn)行分?。由于 Hibernate 自動生成的 SQLServer 分 ?語句使用了 WITH 語句，因此目前并不支持基于 Hibernate 的 SQLServer 分?。目前也不支持使用兩 個 TOP + 子查詢的方式實(shí)現(xiàn)分?。

? MySQL, PostgreSQL
MySQL 和 PostgreSQL 都支持 LIMIT 分?，無需子查詢:

SELECT * FROM t_order o ORDER BY id LIMIT ? OFFSET ?

解析器

ShardingSphere 使用不同解析器支持 SQL 多種方言。對于未實(shí)現(xiàn)解析器的特定 SQL 方言，默認(rèn)采用 SQL92 標(biāo)準(zhǔn)進(jìn)行解析。