Mysql数据库数据拆分之分库分表总结

最新推荐文章于 2024-05-28 10:06:31 发布

Jaemon

最新推荐文章于 2024-05-28 10:06:31 发布

阅读量1.7k

点赞数 1

分类专栏： MySQL 笔试面试经

本文链接：https://blog.csdn.net/u010979642/article/details/107408899

版权

笔试面试经同时被 2 个专栏收录

90 篇文章 5 订阅

订阅专栏

MySQL

30 篇文章 1 订阅

订阅专栏

Mysql数据库分库和分表方式，涉及到mysql数据库相关知识，对mysql数据库分库分表相关知识感兴趣的朋友一起学习吧

分库

场景：分表能够解决单表数据量过大带来的查询效率下降的问题，但是，却无法给数据库的并发处理能力带来质的提升。面对高并发的读写访问，当数据库master服务器无法承载写操作压力时，不管如何扩展slave服务器，此时都没有意义了。因此，我们必须换一种思路，对数据库进行拆分，从而提高数据库写入能力，这就是所谓的分库！与分表策略相似，分库可以采用通过一个关键字取模的方式，来对数据访问进行路由。

水平分库

水平分库： 把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。

场景：对用户表进行水平分库(DB1，DB2，DB3)，根据 用户ID%3 进行判断用户记录写在哪个库上。具体参考 分库分表 的案例

垂直分库

垂直分库： 按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用。

按照功能分库

按照功能进行分库。常见的分成6大库：

用户类库：用于保存了用户的相关信息。例如：db_user，db_system，db_company等。
业务类库：用于保存主要业务的信息。比如主要业务是笑话，用这个库保存笑话业务。例如：db_joke，db_temp_joke等。
内存类库：主要用Mysql的内存引擎。前台的数据从内存库中查找，速度快。例如：heap。
图片类库：主要保存图片的索引以及关联。例如：db_img_index，db_img_res。
日志类库：记录点击,刷新,登录等日志信息。例如：db_log_click，db_log_fresh，db_log_login。
统计类库：对业务的统计，比如点击量,刷新量等等。例如：db_stat。

安装城市站分库

如果业务遍布全国，在按照功能分库库，每一个城市复制一份一模一样的库，只是库后缀都是城市名称。

比如： db_log_click_bj，db_log_click_tj，db_log_click_sh；

分表

场景：对于大型的互联网应用来说，数据库单表的记录行数可能达到千万级甚至是亿级，并且数据库面临着极高的并发访问。采用Master-Slave复制模式的MySQL架构，只能够对数据库的读进行扩展，而对数据库的写入操作还是集中在Master上，并且单个Master挂载的Slave也不可能无限制多，Slave的数量受到Master能力和负载的限制。因此，需要对数据库的吞吐能力进行进一步的扩展，以满足高并发访问与海量数据存储的需要！

对于访问极为频繁且数据量巨大的单表来说，我们首先要做的就是减少单表的记录条数，以便减少数据查询所需要的时间，提高数据库的吞吐，这就是所谓的分表！

在分表之前，首先需要选择适当的分表策略，使得数据能够较为均衡地分不到多张表中，并且不影响正常的查询！

对于互联网企业来说，大部分数据都是与用户关联的，因此，用户id是最常用的分表字段。因为大部分查询都需要带上用户id，这样既不影响查询，又能够使数据较为均衡地分布到各个表中(当然，有的场景也可能会出现冷热数据分布不均衡的情况)

水平分表

水平分表： 在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。

按照用户或业务的编号分表

对与用户或业务可以按照编号%n，进行分成n表。

例如：笑话表。

tb_joke_01，tb_joke_02，tb_joke_03，tb_joke_04…

按照日期分表

对于日志或统计类等的表。可以按照年，月，日，周分表。

例如：点击量统计。

tb_click_stat_201601，tb_click_stat_201602，tb_click_stat_201603

垂直分表

垂直分表： 将一个表按照字段分成多表，每个表存储其中一部分字段。

通常我们按以下原则进行垂直拆分：

把不常用的字段单独放在一张表；
把text，blob等大字段拆分出来放在附表中；
经常组合查询的列放在一张表中；

场景：用户在浏览商品列表时，只有对某商品感兴趣时才会查看该商品的详细描述。因此，商品信息中商品描述字段访问频次较低，且该字段存储占用空间较大，访问单个数据IO时间较长；商品信息中商品名称、商品图片、商品价格等其他字段数据访问频次较高。

由于这两种数据的特性不一样，因此他考虑将商品信息表拆分如下：

将访问频次低的商品描述信息单独存放在一张表中，访问频次较高的商品基本信息单独放在一张表中。

原有商品表

id	goods_name	goods_price	goods_img	goods_desc
主键id	商品名称	商品价格	商品图片	商品描述

经过垂直分表拆分后

商品表：ta_goods_info

id	goods_name	goods_price	goods_img
主键id	商品名称	商品价格	商品图片

商品描述表： ta_goods_describe

id	goods_id	goods_desc
主键id	商品id	商品描述

分库分表

场景：有时数据库可能既面临着高并发访问的压力，又需要面对海量数据的存储问题，这时需要对数据库既采用分表策略，又采用分库策略，以便同时扩展系统的并发处理能力，以及提升单表的查询性能，这就是所谓的分库分表。

分库分表的策略比前面的仅分库或者仅分表的策略要更为复杂，一种分库分表的路由策略如下：

中间变量 = user_id % (分库数量 * 每个库的表数量)
库 = 取整数 (中间变量 / 每个库的表数量)
表 = 中间变量 % 每个库的表数量

以上分库分表策略为 水平分库水平分表策略。采用user_id作为路由字段，首先使用user_id 对库数量*每个库表的数量取模，得到一个中间变量；然后使用中间变量除以每个库表的数量，取整，便得到对应的库；而中间变量对每个库表的数量取模，即得到对应的表。

分库分表策略详细过程如下：

假设将原来的单库单表order拆分成256个库，每个库包含1024个表，那么按照前面所提到的路由策略，对于user_id=262145 的访问，路由的计算过程如下：

中间变量 = 262145 % (256 * 1024) = 1
库 = 取整 (1/1024) = 0
表 = 1 % 1024 = 1

这就意味着，对于user_id=262145 的订单记录的查询和修改，将被路由到第0个库的第1个order_1表中执行！！！

跨库Join的几种解决思路

在拆分之前，系统中很多列表和详情页所需的数据是可以通过sql join来完成的。而拆分后，数据库可能是分布式在不同实例和不同的主机上，join将变得非常麻烦。而且基于架构规范，性能，安全性等方面考虑，一般是禁止跨库join的。那该怎么办呢？首先要考虑下垂直分库的设计问题，如果可以调整，那就优先调整。如果无法调整的情况，下面笔者将结合以往的实际经验，总结几种常见的解决思路，并分析其适用场景。

全局表

所谓全局表，就是有可能系统中所有模块都可能会依赖到的一些表。比较类似我们理解的“数据字典”。 为了避免跨库join查询，我们可以将这类表在其他每个数据库中均保存一份。同时，这类数据通常也很少发生修改（甚至几乎不会），所以也不用太担心“一致性”问题。

字段冗余

这是一种典型的反范式设计，在互联网行业中比较常见，通常是为了性能来避免join查询。

举个电商业务中很简单的场景：

“订单表”中保存“卖家Id”的同时，将卖家的“Name”字段也冗余，这样查询订单详情的时候就不需要再去查询“卖家用户表”。

字段冗余能带来便利，是一种“空间换时间”的体现。但其适用场景也比较有限，比较适合依赖字段较少的情况。最复杂的还是数据一致性问题，这点很难保证，可以借助数据库中的触发器或者在业务代码层面去保证。当然，也需要结合实际业务场景来看一致性的要求。就像上面例子，如果卖家修改了Name之后，是否需要在订单信息中同步更新呢？