深度好文：全面認識MySQL分庫分表篇

碼農老K

工具主管,科技領域創作者

1 傳統項目結構

2 數據庫性能瓶頸

① 數據庫連接 數據庫連接是非常稀少的資源,MySQL數據庫默認100個連接，單機最大1500連接。如果一個庫裏既有用戶相關的數據又有商品、訂單相關的數據，當海量用戶同時操作時,數據庫連接就很可能成為瓶頸。

② 數據量 MySQL單庫數據量在5000萬以內性能比較好，超過閾值後性能會隨著數據量的增大而變弱。MySQL單表的數據量是500w-1000w之間性能比較好，超過1000w性能也會下降。

③ 硬件問題 因為單個服務的磁盤空間是有限制的，如果並發壓力下所有的請求都訪問同一個節點，肯定會對磁盤IO造成非常大的影響。

3 數據庫性能優化

① 參數優化 ② 緩存、索引 ③ 讀寫分離 ④ 分庫分表 （最終方案）

4 分庫分表介紹

4.1 使用背景

當**表的數量**達到了幾百上千張表時，眾多的業務模塊都訪問這個數據庫，壓力會比較大，考慮對其進行分庫。 當**表的數據**達到了幾千萬級別，在做很多操作都比較吃力，考慮對其進行分庫或者分表

4.2 數據切分方案

數據的切分（Sharding）根據其切分規則的類型，可以分為兩種垂直切分和水平切分模式

4.2.1垂直切分

按照業務模塊進行切分，將不同模塊的表切分到不同的數據庫中。

4.2.1.1 分庫

4.2.1.2 分表

按照字段將大表拆分成小表，另當表中含有Blob、Clob（用於存頭像、小圖片等）等二進制類型的字段時，因其不能使用索引，考慮性能問題需將其拆分出來。

4.2.2 水平切分

將一張大表按照一定的切分規則，按照行切分成不同的表或者切分到不同的庫中

4.2.2.1 範圍式拆分

好處：增刪數據庫實例時數據遷移是部分遷移，擴展能力強。 壞處：熱點數據分布不均，訪問壓力不能負載均衡。

4.2.2.2 hash式拆分

好處：熱點數據分布均勻，訪問壓力能負載均衡。 壞處：增刪數據庫實例時數據都要遷移，擴展能力差。

4.2.2.3 水平切分規則

① 按照ID取模：對ID進行取模，餘數決定該行數據切分到哪個表或者庫中。 ② 按照日期：按照年月日，將數據切分到不同的表或者庫中。 ③ 按照範圍：可以對某一列按照範圍進行切分，不同的範圍切分到不同的表或者數據庫中。

4.2.3 切分原則

① 能不切分盡量不要切分。 ② 如果要切分一定要選擇合適的切分規則，提前規劃好。 ③ 數據切分盡量通過數據冗餘或表分組（Table Group）來降低跨庫 Join 的可能。

4.2.4 說明

垂直切分是程序員切分，水平切分是利用TDDL、Cobar、Mycat 、sharding-jdbc等進行切分。

4.3 分庫分表需要解決的問題

4.3.1 分布式事務問題

解決方案： ① 采用補償事務，例如TCC來解決分布式事務問題。 ② 用記錄日志等方式來解決分布式事務問題。

4.3.2 分布式主鍵ID沖突問題

解決方案： ① 利用Redis的incr命令生成主鍵。 ② 用UUID生成主鍵（不建議：字段比較長、不好排序）。 ③ 利用snowake算法生成主鍵。

4.3.3 跨庫join問題

解決方案： ① 將有E-R關系的表存儲到一個庫中。 ② 對於數據量少的表建成全局表，分布到各個庫中 ③ 對於必須跨庫join的，最多支持跨兩張表的跨庫join

4.4 案例分析

情況： 有用戶表user（uid、name、city、sex、age、timestamp），共5億條數據，機器為x86 64位系統，查詢維度比較單一

問題： 分幾張表？PartitionKey如何選擇？

分析： 根據分表原則，單行數據大於100字節則1千萬一張表，單行數據小於100字節則5千萬一張表，用戶表單行數據小於100字節，單張表可存5千條記錄，5億除以5千萬等於10，向上取整，共分為16張表。city、timestamp做為PartitionKey會造成熱點數據分布不均勻，故使用uid作為PartitionKey，算法為uid模以16

4.5 分庫分表實現技術

① 阿裏的TDDL、Cobar ② 基於阿裏Cobar開發的 ③ 當當網的sharding-jdbc

5 Sharding JDBC

5.1 Sharding JDBC 簡介

Apache Sharding Sphere(Incubator) 是一套開源的分布式數據庫中間件解決方案組成的生態圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（規劃中）這3款相互獨立，卻又能夠混合部署配合使用的產品組成。

5.1.1 Sharding JDBC 架構

5.1.2 Sharding JDBC 對多數據庫的支持

5.1.3 Sharding JDBC 核心概念

數據分片：將數據按照一定的規則進行切分得到數據分片，數據分片分為垂直分片和水平分片。

分片鍵：用於分片的數據庫字段，是將數據庫(表)水平拆分的關鍵字段。

一致性hash環：

*邏輯表*：水平拆分的數據庫（表）的相同邏輯和數據結構表的總稱。 *真實表*：在分片的數據庫中真實存在的物理表。 *數據節點*：數據分片的最小單元。由數據源名稱和數據表組成。 *綁定表*：指分片規則一致的主表和子表。例如： t_order 表和t_order_item 表，均按照order_id 分片，則此兩張表互為綁定表關系。綁定表之間的多表關聯查詢不會出現笛卡爾積關聯，關聯查詢效率將大大提升。 *廣播表*：指所有的分片數據源中都存在的表，表結構和表中的數據在每個數據庫中均完全一致。適用於數據量不 大且需要與海量數據的表進行關聯查詢的場景，例如：字典表。

5.2 分片算法與分片策略

5.2.1 分片算法

Sharding-JDBC的分片算法有精確分片算法、範圍分片算法、複合分片算法、Hint分片算法四種。

5.2.1.1 精確分片算法

用於處理使用單一鍵作為分片鍵的=與IN進行分片的場景。需要配合StandardShardingStrategy使用。

public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> { @Override public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) { for (String each : collection) { if(each.endsWith(Long.parseLong(preciseShardingValue.getValue().toString()) % 2+"")){ return each; } } throw new IllegalArgumentException(); }}

5.2.1.2 範圍分片算法

用於處理使用單一鍵作為分片鍵的BETWEEN AND進行分片的場景。需要配合StandardShardingStrategy使用。

public class MyRangeShardingAlgorithm implements RangeShardingAlgorithm<Long> { @Override public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) { log.info("Range collection:" + JSON.toJSONString(collection) + ",rangeShardingValue:" + JSON.toJSONString(rangeShardingValue)); Collection<String> collect = new ArrayList<>(); Range<Long> valueRange = rangeShardingValue.getValueRange(); for (Long i = valueRange.lowerEndpoint(); i <=valueRange.upperEndpoint(); i++) { for (String each : collection) { if (each.endsWith(i % collection.size() + )) { collect.add(each); } } } return collect; }}

5.2.1.3 複合分片算法

用於處理使用多鍵作為分片鍵進行分片的場景，包含多個分片鍵的邏輯較複雜，需要應用開發者自行處理其中的複雜度。需要配合ComplexShardingStrategy使用。

public class MyComplexShardingAlgorithm implements ComplexKeysShardingAlgorithm{ @Override public Collection<String> doSharding(Collection<String> collection, Collection<ShardingValue> shardingValues) { log.info("collection:" + JSON.toJSONString(collection) + ",shardingValues:" + JSON.toJSONString(shardingValues)); Collection<Long> orderIdValues = getShardingValue(shardingValues, "order_id"); Collection<Long> userIdValues = getShardingValue(shardingValues, "user_id"); List<String> shardingSuffix = new ArrayList<>(); /**例如：根據user_id + order_id 雙分片鍵來進行分表*/ //Set<List<Integer>> valueResult = Sets.cartesianProduct(userIdValues, orderIdValues); for (Long userIdVal : userIdValues) { for (Long orderIdVal : orderIdValues) { String suffix = userIdVal % 2 + "_" + orderIdVal % 2; collection.forEach(x -> {if (x.endsWith(suffix)) { shardingSuffix.add(x);}}); } } return shardingSuffix; } private Collection<Long> getShardingValue(Collection<ShardingValue> shardingValues, final String key) { Collection<Long> valueSet = new ArrayList<>(); Iterator<ShardingValue> iterator = shardingValues.iterator(); while (iterator.hasNext()) { ShardingValue next = iterator.next(); if (next instanceof ListShardingValue) { ListShardingValue value = (ListShardingValue) next; if (value.getColumnName().equals(key)) { return value.getValues(); } } } return valueSet; } }

5.2.1.4 Hint分片算法

用於處理使用Hint行分片的場景。需要配合HintShardingStrategy使用。

5.2.2 分片策略

Sharding-JDBC的分片策略有標准分片策略、複合分片策略、行表達式分片策略、Hint分片策略四種。

5.2.2.1 標准分片策略

提供對SQL語句中的=、IN和BETWEEN AND的分片操作支持。StandardShardingStrategy只支持單分片鍵，提供PreciseShardingAlgorithm和RangeShardingAlgorithm兩個分片算法。PreciseShardingAlgorithm是必選的，用於處理=和IN的分片。RangeShardingAlgorithm是可選的，用於處理BETWEEN AND分片。如果不配置RangeShardingAlgorithm，SQL中的BETWEEN AND將按照全庫路由處理。

5.2.2.2 複合分片策略

提供對SQL語句中的=、IN和BETWEEN AND的分片操作支持。ComplexShardingStrategy支持多分片鍵，由於多分片鍵之間的關系複雜，因此並未進行過多的封裝，而是直接將分片鍵值組合以及分片操作符透傳至分片算法，完全由應用開發者實現，提供最大的靈活度。

5.2.2.3 行表達式分片策略

使用Groovy的表達式，提供對SQL語句中的=和IN的分片操作支持，只支持單分片鍵。對於簡單的分片算法，可以通過簡單的配置使用，從而避免繁瑣的Java代碼開發。 行表達式的使用非常直觀，只需要在配置中使用

5.2.2.4 Hint分片策略

通過Hint而非SQL解析的方式分片的策略。對於分片字段非SQL決定，而由其他外置條件決定的場景，可使用SQL Hint靈活的注入分片字段。

5.3 Sharding JDBC核心組件

5.3.1 解析引擎