MySQL：互联网公司常用分库分表方案汇总

系统管理员 2022-12-17 14:51 150阅读 0赞

##  ##

点击上方“java大数据修炼之道”,选择“设为星标”

# 优质文章和精品资源, 第一时间送达![format_png][] #

![format_png 1][]

###  ###

来源：cnblogs.com/littlecharacter/p/9342129.html

**好文回顾：**[Mybatis：颠覆你心中对事务的理解][Mybatis]

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

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

### 1、IO瓶颈 ###

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

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

### 2、CPU瓶颈 ###

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

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

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

### 1、水平分库 ###

![format_png 2][]

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

**结果：**

*  每个库的结构都一样；
 *  每个库的数据都不一样，没有交集；
 *  所有库的并集是全量数据；

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

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

### 2、水平分表 ###

![format_png 3][]

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

**结果：**

*  每个表的结构都一样；
 *  每个表的数据都不一样，没有交集；
 *  所有表的并集是全量数据；

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

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

### 3、垂直分库 ###

![format_png 4][]

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

**结果：**

*  每个库的结构都不一样；
 *  每个库的数据也不一样，没有交集；
 *  所有库的并集是全量数据；

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

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

### 4、垂直分表 ###

![format_png 5][]

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

**结果：**

*  每个表的结构都不一样；
 *  每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；
 *  所有表的并集是全量数据；

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

映射法

![format_png 6][]

基因法

![format_png 7][]

> 注：写入时，基因法生成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作为条件查询**

映射法

![format_png 8][]

冗余法

![format_png 9][]

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

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

NoSQL法

![format_png 10][]

冗余法

![format_png 11][]

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

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

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

### 3、扩容问题 ###

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

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

**![format_png 12][]**

> 注：扩容是成倍的。

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

**![format_png 13][]**

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

> 注：双写是通用方案。

## **六、分库分表总结** ##

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

## **七、分库分表示例** ##

> 示例GitHub地址：https://github.com/littlecharacter4s/study-sharding

#### ![format_png 14][]  **推荐阅读：** ####

#### 1、[必须收藏！SpringBoot注解大全][SpringBoot]     2、[项目中常用的19条MySQL优化][19_MySQL]  3、[分享几个开源的高仿项目， 需要的自取][Link 1]  4、[老板要求用java将word转为PDF，这么搞？][java_word_PDF]     5、[这8种常见的SQL错误用法，80%的人还在使用][8_SQL_80]  6、[程序员面试 10 大潜规则，千万不要踩坑！][10]     7、[卧槽，为什么volatile关键字保证不了线程安全啊][volatile]     8、  [单怼60道][60]  [多线程][60]  [面试题，你能答上几个?(附答案)][60]  9、[这21个刁钻的HashMap 面试题，我把阿里面试官吊打了！][21_HashMap]     10、[Spring Boot “内存泄漏”？看看美团大牛是如何排查的][Spring Boot] ####

**看完有收获?请转发分享给更多有需要的人**

关注 java大数据修炼之道

每天学习java技术，你想学的Java知识这里都有!**![format_png 15][]**

![format_png 16][]微信扫描二维码，关注我的公众号

写留言

*  
 *  ####  ####
    
    如果对你有帮助,请点个"在看 ![format_png 17][]",谢谢 ![format_png 18][]

*  ####  ####
    
    **嘿，你****在看****吗?**❤️

[format_png]: /images/20221122/3525befc2f494798903a48453a0d5740.png
[format_png 1]: /images/20221122/9380a25135e247589dbbccc0d812b248.png
[Mybatis]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc22848ccb5a15e1e2d2adc63b2e7813b41acdfae07391a86bce775924b57436d7a8ed99218&idx=1&mid=2247493252&scene=21&sn=54638b598a57683c4b2b9efe3acbe03b#wechat_redirect
[format_png 2]: /images/20221122/b84b82a887ab42a8a912e49d5a72602c.png
[format_png 3]: /images/20221122/597e11e537c647f4afb0122026fc30a7.png
[SQL]: http://mp.weixin.qq.com/s?__biz=MzI4Njc5NjM1NQ%3D%3D&chksm=ebd621dddca1a8cb16f33497c45aeb44e95ca0d5289afee263e047a868cb67f34ada644418b0&idx=2&mid=2247491313&scene=21&sn=01d82309c459c7385a2ccf0018bb0d8a#wechat_redirect
[format_png 4]: /images/20221122/2cacc247087542e293ac9840ca216194.png
[format_png 5]: /images/20221122/fe5c797b9bc0458bbbcda74f07d0f338.png
[MySQL]: http://mp.weixin.qq.com/s?__biz=MzI4Njc5NjM1NQ%3D%3D&chksm=ebd621f5dca1a8e3f2cc4d782b012bd84e61f310959fb3de9f48ef3fa7c54861a66389e9e9ff&idx=1&mid=2247491289&scene=21&sn=0dc45c799ca4c14782455d9d2f161674#wechat_redirect
[format_png 6]: /images/20221122/206c6caa295e4554a7b8fc8327a66a5b.png
[format_png 7]: /images/20221122/5a3efc6774c643ca88204991eebd8d4b.png
[format_png 8]: /images/20221122/1bfa522f32514348a7a83cf81c0606c2.png
[format_png 9]: /images/20221122/70424d0923704b17a9e3c3f65b4f5f9d.png
[format_png 10]: /images/20221122/b66ee03a343c48df951388f13617e23b.png
[format_png 11]: /images/20221122/517c0ac585ca49d2bcb599312c081a3d.png
[format_png 12]: /images/20221122/46d6099ab8c440d88ac801f701ce786a.png
[format_png 13]: /images/20221122/44682fa757d542c28c12ca2f69647fdd.png
[format_png 14]: /images/20221122/c4b8090fa3f14daabb4b83b59cda3ca3.png
[SpringBoot]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc228beccb5a1a8eee97316c21121d8b19125491464c3f2fab82c35fb47ba80299421a9e54e&idx=1&mid=2247493234&scene=21&sn=2c1164a6660993e4c9d42cc972235885#wechat_redirect
[19_MySQL]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc22c17ccb5a501df41ddeaecf0f7e85e292b45d6c7522e4d6a4d9c4a93a389338ee175eae8&idx=1&mid=2247492315&scene=21&sn=37c6888e30892c7fae56d0769c35a571#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc228b6ccb5a1a02f8d036b13a85c7485c048d351afb87a949999c24e0e6863f860289c00a6&idx=1&mid=2247493242&scene=21&sn=ae4546b8447b8aef71cf68781003b226#wechat_redirect
[java_word_PDF]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc22dc7ccb5a4d1b94f2a9bbce26082c9f845fbbf7d588a5540d072bcef39b2281d0b705f91&idx=1&mid=2247492363&scene=21&sn=6975a1a5de92dc49d04654f21ffa7c21#wechat_redirect
[8_SQL_80]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc22897ccb5a1811b6f10e1c5de666e0d853a2808ad174e1b9adf9e30898cf3a9dbca810fa1&idx=1&mid=2247493211&scene=21&sn=0c7504ab32f5c8bf4930ed2d89753cd8#wechat_redirect
[10]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc22cc6ccb5a5d09ec368b85dfe087d1b68f6ccbf67d7275356f64f203d353dcba684b4012e&idx=1&mid=2247492106&scene=21&sn=e25c3eea649006f34fd6f5cb62ea78be#wechat_redirect
[volatile]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc22a05ccb5a31383ee13bb6c58f8c12dada691c16624e028f23f24c32d1e4201b4ebd1a354&idx=1&mid=2247492809&scene=21&sn=47c50b227f8063129ed09edc91449100#wechat_redirect
[60]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc1d557ccb65c41ec5d3b7dedd6c7a22949ddcb5de1406de482c3b702a1a4768c66b730b219&idx=1&mid=2247490459&scene=21&sn=66bc23ae41a8abcb9a6f00be754b2e47#wechat_redirect
[21_HashMap]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc1d429ccb65d3fc4be6ab008e4318d3beeccb1394801c72e33cbb9fd162868dce2b9b0c0f2&idx=1&mid=2247490277&scene=21&sn=63d44db7ae6930f25478119f423227d4#wechat_redirect
[Spring Boot]: http://mp.weixin.qq.com/s?__biz=MzU1NjczNTM5NQ%3D%3D&chksm=fbc22b09ccb5a21f763789682341712c81e967d31949e2ee4d9161f348839c69fbf9d5aab7f2&idx=1&mid=2247493061&scene=21&sn=059d8dcf396bb5a4627d47ff4caa2b4f#wechat_redirect
[format_png 15]: /images/20221122/be3b20eb1f6e4eee8687210b893e3357.png
[format_png 16]: /images/20221122/66157935a7c64a75a03d3681f8cae9cf.png
[format_png 17]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9DMXVETURxam4xOXd0dVFwSzNqbUpXM2JGR1dJOFl6NkZSMTd0bDFNRjhWZnFZeFB4OTkwa3YySjc0THZxd2liMjZLYXlIZE9YZDZlYnpycVlpYmJUand3LzY0MA?x-oss-process=image/format,png
[format_png 18]: /images/20221122/5b5415be73194e63b9a6a44057b4a95e.png