【总结】互联网技术架构中常用的分库分表方案汇总

缺乏、安全感 2023-07-25 05:26 147阅读 0赞

# 问题背景 #

### 一、数据库瓶颈 ###

不管是IO瓶颈，还是CPU瓶颈，最终都会导致数据库的活跃连接数增加，进而逼近甚至达到数据库可承载活跃连接数的阈值。在业务Service来看，就是：可用数据库连接少甚至无连接可用。接下来就可以想象了：并发量、吞吐量、崩溃等

**1、IO瓶颈**

第一种：磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的IO，降低查询速度 -> *分库和垂直分表*

第二种：网络IO瓶颈，请求的数据太多，网络带宽不够 -> *分库*

**2、CPU瓶颈**

第一种：SQL问题，如SQL中包含join，group by，order by，非索引字段条件查询等，增加CPU运算的操作 -> *SQL优化，建立合适的索引，在业务Service层进行业务计算*

第二种：单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈 -> *水平分表*

# 解决方案 #

### 二、分库分表 ###

**1、水平分库**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70][]

**概念：**以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。

**结果：**

每个库的结构都一样；

每个库的数据都不一样，没有交集；

所有库的并集是全量数据；

**场景：**系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。

**分析：**库多了，io和cpu的压力自然可以成倍缓解。

**2、水平分表**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 1][]

**概念：**以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。

**结果：**

每个表的结构都一样；

每个表的数据都不一样，没有交集；

所有表的并集是全量数据；

**场景：**系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。推荐：[数据库优化][Link 1]

**分析：**表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。

**3、垂直分库**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 2][]

**概念：**以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。

**结果：**

每个库的结构都不一样；

每个库的数据也不一样，没有交集；

所有库的并集是全量数据；

**场景：**系统绝对并发量上来了，并且可以抽象出单独的业务模块。

**分析：**到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。

**4、垂直分表**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 3][]

**概念：**以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

**结果：**

每个表的结构都不一样；

每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；

所有表的并集是全量数据；

**场景：**系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读IO，产生IO瓶颈。

**分析：**可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。

**但记住：**千万别用join，因为join不仅会增加CPU负担并且会讲两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务Service层做文章，分别获取主表和扩展表数据，然后用关联字段关联得到全部数据。

### 三、分库分表工具 ###

*  [sharding-sphere][]：jar，前身是sharding-jdbc
 *  [TDDL][]：jar，Taobao Distribute Data Layer
 *  Mycat：中间件

### 四、分库分表步骤 ###

根据容量（当前容量和增长量）评估分库或分表个数 -> 选key（均匀）-> 分表规则（hash或range等）-> 执行（一般双写）-> 扩容问题（尽量减少数据的移动）

> 扩展：[MySQL：分库分表与分区的区别和思考][MySQL]

# 常见问题 #

### 五、分库分表问题 ###

**1、非partition key的查询问题**

基于水平分库分表，拆分策略为常用的hash法。

**端上除了partition key，只有一个非partition key作为条件查询**

a. 映射法

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 4][]

b. 基因法

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 5][]

> 注：写入时，基因法生成user\_id，如图  
> 关于xbit基因，例如要分8张表，23=8，故x取3，即3bit基因。根据user\_id查询时可直接取模路由到对应的分库或分表。  
> 根据user\_name查询时，先通过user\_name\_code生成函数生成user\_name\_code，再对其取模路由到对应的分库或分表。id生成常用snowflake算法。

**端上除了partition key不止一个非partition key作为条件查询**

a. 映射法

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 6][]

b. 冗余法

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 7][]

> 注：按照order\_id或buyer\_id查询时路由到db\_o\_buyer库中，按照seller\_id查询时路由到db\_o\_seller库中。感觉有点本末倒置！有其他好的办法吗？改变技术栈呢？

**后台除了partition key还有各种非partition key组合条件查询**

a. NoSQL法

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 8][]

b. 冗余法

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 9][]

**2、非partition key跨库跨表分页查询问题**

基于水平分库分表，拆分策略为常用的hash法

> 注：用NoSQL法解决（ES等）

**3、扩容问题**

基于水平分库分表，拆分策略为常用的hash法

**a. 水平扩容库（升级从库法）**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 10][]

> 注：扩容是成倍的

**b. 水平扩容表（双写迁移法）**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 11][]

1.  第一步：（同步双写）修改应用配置和代码，加上双写，部署；
2.  第二步：（同步双写）将老库中的老数据复制到新库中；
3.  第三步：（同步双写）以老库为准校对新库中的老数据；
4.  第四步：（同步双写）修改应用配置和代码，去掉双写，部署；

> 注：双写是通用方案

# 分库分表总结 #

*  分库分表，首先得知道瓶颈在哪里，然后才能合理地拆分（分库还是分表？水平还是垂直？分几个？），不可为了分库分表而拆分。
 *  选key很重要，既要考虑到拆分均匀，也要考虑到非partition key的查询。
 *  只要能满足需求，拆分规则越简单越好。

# 其他资料 #

*  [【总结】我的十二个架构设计原则][Link 2]
 *  [数据库优化专栏][Link 1]

\-------------------------------

[我的CSDN主页][CSDN]

[关于我][Link 3]（个人域名）

[我的开源项目集Github][Github]

期望和大家***一起学习，一起成长***，共勉，O(∩\_∩)O谢谢

欢迎交流问题，可加个人QQ 469580884，

或者，加我的群号 **751925591**，一起探讨交流问题

不讲虚的，只做实干家

Talk is cheap，show me the code

![20180411181545874][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70]: /images/20230528/9b366f580d1046a08037ef8b46aca4a7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 1]: /images/20230528/ff6fe723344642ee9b1566afb35d2998.png
[Link 1]: https://blog.csdn.net/hemin1003/category_8903100.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 2]: /images/20230528/41ce0d1ec279411793a8d1ba98a15e0d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 3]: /images/20230528/a17cb9819ec44da68047e4906b4faff0.png
[sharding-sphere]: https://shardingsphere.apache.org/index_zh.html
[TDDL]: https://github.com/alibaba/tb_tddl
[MySQL]: https://www.cnblogs.com/GrimMjx/p/11772033.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 4]: /images/20230528/3abca0bee6ef46ff86dbe9e52968ce5b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 5]: /images/20230528/37e84a0e03354d6f9ccf3858c4ff3851.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 6]: /images/20230528/18f778c46f3e4a5d84577e94797d16fc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 7]: /images/20230528/39cc685383774bd89cf1367a9783736e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 8]: /images/20230528/b0b091c5d1584ae7abf9eaf8dda4a16d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 9]: /images/20230528/169f854de77b4ddd88396afa5515659c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 10]: /images/20230528/83e72931829545f69d7f1b731e45a622.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbWluMTAwMw_size_16_color_FFFFFF_t_70 11]: /images/20230528/2ee8b10829fb4ed888b51a49dc22fb8f.png
[Link 2]: https://blog.csdn.net/hemin1003/article/details/103254040
[CSDN]: https://blog.csdn.net/hemin1003
[Link 3]: http://heminit.com/about/
[Github]: https://github.com/hemin1003
[20180411181545874]: /images/20230528/0c4646de72e34fe0988c3cf3c98d5cff.png