分库分表知识总结

男娘i 2022-03-20 21:20 264阅读 0赞

### 分区表 ###

#### 原理 ####

分区表是由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个相同的索引，从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

#### 弊端 ####

事实上，这个方案也不错，它对用户屏蔽了sharding的细节，即使查询条件没有sharding column，它也能正常工作（只是这时候性能一般）。不过它的缺点很明显：很多的资源都受到单机的限制，例如连接数，网络吞吐等！虽然每个分区可以独立存储，但是分区表的总入口还是一个MySQL示例。从而导致它的并发能力非常一般，远远达不到互联网高并发的要求！

至于网上提到的一些其他缺点比如：无法使用外键，不支持全文索引。我认为这都不算缺点，21世纪的项目如果还是使用外键和数据库的全文索引，我都懒得吐槽了！

#### 场景 ####

所以，如果使用分区表，你的业务应该具备如下两个特点：

*  数据不是海量（分区数有限，存储能力就有限）；
 *  并发能力要求不高；

### 分库分表 ###

分库分表中间件全部可以归结为两大类型

*  Client模式（客户端代理-代理数据源）
 *  Proxy模式（服务端代理-代理数据库）

#### proxy模式 ####

我们独立部署一个代理服务，这个代理服务背后管理多个数据库实例。而在应用中，我们通过一个普通的数据源(c3p0、druid、dbcp等)与代理服务器建立连接，所有的sql操作语句都是发送给这个代理，由这个代理去操作底层数据库，得到结果并返回给应用。在这种方案下，分库分表和读写分离的逻辑对开发人员是完全透明的。

#### client模式 ####

应用程序需要使用一个特定的数据源，其作用是代理，内部管理了多个普通的数据源(c3p0、druid、dbcp等)，每个普通数据源各自与不同的库建立连接。应用程序产生的sql交给数据源代理进行处理，数据源内部对sql进行必要的操作，如sql改写等，然后交给各个普通的数据源去执行，将得到的结果进行合并，返回给应用。数据源代理通常也实现了JDBC规范定义的API，因此能够直接与orm框架整合。在这种方案下，用户的代码需要修改，使用这个代理的数据源，而不是直接使用c3p0、druid、dbcp这样的连接池。

具体如图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l2ZXJzb24yMDEwMTEyMjI4_size_16_color_FFFFFF_t_70]

#### 主流数据库中间件实现对比 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l2ZXJzb24yMDEwMTEyMjI4_size_16_color_FFFFFF_t_70 1]  
**数据库代理**

目前的实现方案有：阿里巴巴开源的cobar，mycat团队在cobar基础上开发的mycat，mysql官方提供的mysql-proxy，奇虎360在mysql-proxy基础开发的atlas。目前除了mycat，其他几个项目基本已经没有维护。

*  优点：多语言支持。也就是说，不论你用的php、java或是其他语言，都可以支持。原因在于数据库代理本身就实现了mysql的通信协议，你可以就将其看成一个mysql 服务器。mysql官方团队为不同语言提供了不同的客户端驱动，如java语言的mysql-connector-java，python语言的mysql-connector-python等等。因此不同语言的开发者都可以使用mysql官方提供的对应的驱动来与这个代理服务器建通信。
 *  缺点：实现复杂。因为代理服务器需要实现mysql服务端的通信协议，因此实现难度较大。

**数据源代理**

目前的实现方案有：阿里巴巴开源的tddl，大众点评开源的zebra，当当网开源的sharding-jdbc。需要注意的是tddl的开源版本只有读写分离功能，没有分库分表，且开源版本已经不再维护。大众点评的zebra开源版本代码已经很久更新，基本上处于停滞的状态。当当网的sharding-jdbc目前算是做的比较好的，代码时有更新，文档资料比较全。

*  优点：更加轻量，可以与任何orm框架整合。这种方案不需要实现mysql的通信协议，因为底层管理的普通数据源，可以直接通过mysql-connector-java驱动与mysql服务器进行通信，因此实现相对简单。
 *  缺点：仅支持某一种语言。例如tddl、zebra、sharding-jdbc都是使用java语言开发，因此对于使用其他语言的用户，就无法使用这些中间件。版本升级困难，因为应用使用数据源代理就是引入一个jar包的依赖，在有多个应用都对某个版本的jar包产生依赖时，一旦这个版本有bug，所有的应用都需要升级。而数据库代理升级则相对容易，因为服务是单独部署的，只要升级这个代理服务器，所有连接到这个代理的应用自然也就相当于都升级了。

**ORM框架代理**  
目前有hibernate提供的hibernate-shards，也可以通过mybatis插件的方式编写。相对于前面两种方案，这种方案可以说是只有缺点，没有优点。

#### 分库分表处理过程 ####

##### 举个栗子 #####

对于开发人员而言，虽然分库分表的，但是其还是希望能和单库单表那样的去操作数据库。例如我们要批量插入四条用户记录，并且希望根据用户的id字段，确定这条记录插入哪个库的哪张表。例如1号记录插入user\_1表，2号记录插入user\_2表，3号记录插入user\_3表，4号记录插入user\_0表，以此类推。sql如下所示：

insert into user(id,name) values (1,”tianshouzhi”),(2,”huhuamin”), (3,”wanghanao”),(4,”luyang”)

这样的sql明显是无法执行的，因为我们已经对库和表进行了拆分,这种sql语法只能操作mysql的单个库和单个表。所以必须将sql改成4条如下所示，然后分别到每个库上去执行。

insert into user_1(id,name) values (1,”tianshouzhi”)
    insert into user_2(id,name) values (2,”huhuamin”)
    insert into user_3(id,name) values (3,”wanghanao”)
    insert into user_0(id,name) values  (4,”luyang”)

具体流程可以用下图进行描述：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l2ZXJzb24yMDEwMTEyMjI4_size_16_color_FFFFFF_t_70 2]  
解释如下：

*  sql解析：首先对sql进行解析，得到需要插入的四条记录的id字段的值分别为1,2,3,4
 *  sql路由：sql路由包括库路由和表路由。库路由用于确定这条记录应该插入哪个库，表路由用于确定这条记录应该插入哪个表。
 *  sql改写：上述批量插入的语法将会在 每个库中都插入四条记录，明显是不合适的，因此需要对sql进行改写，每个库只插入一条记录。
 *  sql执行：一条sql经过改写后变成了多条sql，为了提升效率应该并发的到不同的库上去执行，而不是按照顺序逐一执行
 *  结果集合并：每个sql执行之后，都会有一个执行结果，我们需要对分库分表的结果集进行合并，从而得到一个完整的结果。

#### 多个纬度的水平分表 ####

如果表中多个字段的查询情况都非常多，比如订单表。用户需要按照userid纬度查询，商户需要按照customerid纬度查询。应该如何选择字段进行分表呢？如下图  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l2ZXJzb24yMDEwMTEyMjI4_size_16_color_FFFFFF_t_70 3]

#### 分表的切分策略 ####

### 参考资料 ###

[https://mp.weixin.qq.com/s?\_\_biz=MzU5ODUwNzY1Nw==&mid=2247484012&idx=1&sn=be574cb31f0c0a36034f4635294e55d0&chksm=fe426b8ac935e29c7aa313334446066eddff290121aa089b30698b7a1a9371e6d5e6d78d8c43&scene=21\#wechat\_redirect][https_mp.weixin.qq.com_s_biz_MzU5ODUwNzY1Nw_mid_2247484012_idx_1_sn_be574cb31f0c0a36034f4635294e55d0_chksm_fe426b8ac935e29c7aa313334446066eddff290121aa089b30698b7a1a9371e6d5e6d78d8c43_scene_21_wechat_redirect]

[https://tech.meituan.com/2016/11/18/dianping-order-db-sharding.html][https_tech.meituan.com_2016_11_18_dianping-order-db-sharding.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l2ZXJzb24yMDEwMTEyMjI4_size_16_color_FFFFFF_t_70]: /images/20220321/84b949c97d00420199d9f8d3f8cdff66.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l2ZXJzb24yMDEwMTEyMjI4_size_16_color_FFFFFF_t_70 1]: /images/20220321/8f34948f6fdd4c36a56002e3b312a1b5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l2ZXJzb24yMDEwMTEyMjI4_size_16_color_FFFFFF_t_70 2]: /images/20220321/31f3bd37ab814642af6f2e4d70b1e76d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l2ZXJzb24yMDEwMTEyMjI4_size_16_color_FFFFFF_t_70 3]: /images/20220321/43a40df2ea8c46238e87e773c65ef658.png
[https_mp.weixin.qq.com_s_biz_MzU5ODUwNzY1Nw_mid_2247484012_idx_1_sn_be574cb31f0c0a36034f4635294e55d0_chksm_fe426b8ac935e29c7aa313334446066eddff290121aa089b30698b7a1a9371e6d5e6d78d8c43_scene_21_wechat_redirect]: https://mp.weixin.qq.com/s?__biz=MzU5ODUwNzY1Nw==&mid=2247484012&idx=1&sn=be574cb31f0c0a36034f4635294e55d0&chksm=fe426b8ac935e29c7aa313334446066eddff290121aa089b30698b7a1a9371e6d5e6d78d8c43&scene=21#wechat_redirect
[https_tech.meituan.com_2016_11_18_dianping-order-db-sharding.html]: https://tech.meituan.com/2016/11/18/dianping-order-db-sharding.html