数据库分区、分表、分库，读写分离

最新推荐文章于 2022-10-17 21:25:09 发布

hello_world!

最新推荐文章于 2022-10-17 21:25:09 发布

阅读量273

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/djrm11/article/details/96470745

版权

sql 专栏收录该内容

61 篇文章 4 订阅

订阅专栏

https://blog.csdn.net/liangz/article/details/79352870

转载自：http://www.cnblogs.com/bluebluesky/articles/6475779.html、http://www.cnblogs.com/bluebluesky/articles/6475779.html、https://www.cnblogs.com/wade-luffy/p/6096578.html、http://blog.csdn.net/mchdba/article/details/52336203

分区

就是把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的。

分区的实现方式（简单）

mysql5 开始支持分区功能


   
   
     
     
      
      
     
     
     
     
      
      
       
       CREATE 
       
       TABLE sales (
      
      
     
     

     
     
      
      
     
     
     
     
      
          
       
       id 
       
       INT AUTO_INCREMENT,
      
      
     
     

     
     
      
      
     
     
     
     
      
      
       
           amount 
       
       DOUBLE 
       
       NOT 
       
       NULL,
      
      
     
     

     
     
      
      
     
     
     
     
      
      
       
           order_day DATETIME 
       
       NOT 
       
       NULL,
      
      
     
     

     
     
      
      
     
     
     
     
      
      
       
           PRIMARY 
       
       KEY(
       
       id, order_day)
      
      
     
     

     
     
      
      
     
     
     
     
      
      
       
       ) 
       
       ENGINE=
       
       Innodb 
      
      
     
     

     
     
      
      
     
     
     
     
      
      
       
       PARTITION 
       
       BY 
       
       RANGE(
       
       YEAR(order_day)) (
      
      
     
     

     
     
      
      
     
     
     
     
      
          
       
       PARTITION p_2010 
       
       VALUES 
       
       LESS 
       
       THAN (
       
       2010),
      
      
     
     

     
     
      
      
     
     
     
     
      
          
       
       PARTITION p_2011 
       
       VALUES 
       
       LESS 
       
       THAN (
       
       2011),
      
      
     
     

     
     
      
      
     
     
     
     
      
          
       
       PARTITION p_2012 
       
       VALUES 
       
       LESS 
       
       THAN (
       
       2012),
      
      
     
     

     
     
      
      
     
     
     
     
      
      
       
       PARTITION p_catchall 
       
       VALUES 
       
       LESS 
       
       THAN MAXVALUE);

分表

就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明，然后操作它。

分表的实现方式（复杂）

需要业务系统配合迁移升级，工作量较大

常见分区分表的规则策略（类似）

Range（范围）
Hash（哈希）
按照时间拆分
Hash之后按照分表个数取模
在认证库中保存数据库配置，就是建立一个DB，这个DB单独保存user_id到DB的映射关系

分库

一旦分表，一个库中的表会越来越多，这时需要分成多个数据库。

垂直拆分

将系统中不存在关联关系或者需要join的表可以放在不同的数据库不同的服务器中。

按照业务垂直划分。比如：可以按照业务分为资金、会员、订单三个数据库。

需要解决的问题：跨数据库的事务、jion查询等问题。

水平拆分

例如，大部分的站点。数据都是和用户有关，那么可以根据用户，将数据按照用户水平拆分。

按照规则划分，一般水平分库是在垂直分库之后的。比如每天处理的订单数量是海量的，可以按照一定的规则水平划分。需要解决的问题：数据路由、组装。

读写分离

对于时效性不高的数据，可以通过读写分离缓解数据库压力。需要解决的问题：在业务上区分哪些业务上是允许一定时间延迟的，以及数据同步问题。

思路

垂直分库-->水平分库-->读写分离

垂直分库带来的问题和解决思路

跨库join的问题

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

跨库Join的几种解决思路

全局表

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

字段冗余

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

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

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

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

数据同步

A库中的tab_a表和B库中tbl_b有关联，可以定时将指定的表做同步。当然，同步本来会对数据库带来一定的影响，需要性能影响和数据时效性中取得一个平衡。这样来避免复杂的跨库查询。

系统层组装

在系统层面，通过调用不同模块的组件或者服务，获取到数据并进行字段拼装。说起来很容易，但实践起来可真没有这么简单，尤其是数据库设计上存在问题但又无法轻易调整的时候。具体情况通常会比较复杂。组装的时候要避免循环调用服务，循环RPC，循环查询数据库，最好一次性返回所有信息，在代码里做组装。

跨库事务（分布式事务）的问题

http://www.infoq.com/cn/articles/solution-of-distributed-system-transaction-consistency

水平分库带来的问题和解决思路

分布式全局唯一ID

我们往往直接使用数据库自增特性来生成主键ID，这样确实比较简单。而在分库分表的环境中，数据分布在不同的分片上，不能再借助数据库自增长特性直接生成，否则会造成不同分片上的数据表主键会重复。简单介绍几种ID生成算法。

Twitter的Snowflake（又名“雪花算法”）
UUID/GUID（一般应用程序和数据库均支持）
MongoDB ObjectID（类似UUID的方式）
Ticket Server（数据库生存方式，Flickr采用的就是这种方式）

其中，Twitter 的Snowflake算法生成的是64位唯一Id（由41位的timestamp+ 10位自定义的机器码+ 13位累加计数器组成）。

分片字段该如何选择

在开始分片之前，我们首先要确定分片字段（也可称为“片键”）。很多常见的例子和场景中是采用ID或者时间字段进行拆分。这也并不绝对的，我的建议是结合实际业务，通过对系统中执行的sql语句进行统计分析，选择出需要分片的那个表中最频繁被使用，或者最重要的字段来作为分片字段。

常见分片规则

常见的分片策略有随机分片和连续分片这两种，当需要使用分片字段进行范围查找时，连续分片可以快速定位分片进行高效查询，大多数情况下可以有效避免跨分片查询的问题。后期如果想对整个分片集群扩容时，只需要添加节点即可，无需对其他分片的数据进行迁移。但是，连续分片也有可能存在数据热点的问题，有些节点可能会被频繁查询压力较大，热数据节点就成为了整个集群的瓶颈。而有些节点可能存的是历史数据，很少需要被查询到。随机分片其实并不是随机的，也遵循一定规则。通常，我们会采用Hash取模的方式进行分片拆分，所以有些时候也被称为离散分片。随机分片的数据相对比较均匀，不容易出现热点和并发访问的瓶颈。但是，后期分片集群扩容起来需要迁移旧的数据。使用一致性Hash算法能够很大程度的避免这个问题，所以很多中间件的分片集群都会采用一致性Hash算法。离散分片也很容易面临跨分片查询的复杂问题。

数据迁移，容量规划，扩容等问题

很少有项目会在初期就开始考虑分片设计的，一般都是在业务高速发展面临性能和存储的瓶颈时才会提前准备。因此，不可避免的就需要考虑历史数据迁移的问题。一般做法就是通过程序先读出历史数据，然后按照指定的分片规则再将数据写入到各个分片节点中。此外，我们需要根据当前的数据量和QPS等进行容量规划，综合成本因素，推算出大概需要多少分片（一般建议单个分片上的单表数据量不要超过1000W）。如果是采用随机分片，则需要考虑后期的扩容问题，相对会比较麻烦。如果是采用的范围分片，只需要添加节点就可以自动扩容。

跨分片的排序分页

分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片，而当排序字段非分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户。

跨分片的函数处理

在使用Max、Min、Sum、Count之类的函数进行统计和计算的时候，需要先在每个分片数据源上执行相应的函数处理，然后再将各个结果集进行二次处理，最终再将处理结果返回。

跨分片join

Join是关系型数据库中最常用的特性，但是在分片集群中，join也变得非常复杂。应该尽量避免跨分片的join查询（这种场景，比上面的跨分片分页更加复杂，而且对性能的影响很大）。通常有以下几种方式来避免：

全局表

全局表的概念之前在“垂直分库”时提过。基本思想一致，就是把一些类似数据字典又可能会产生join查询的表信息放到各分片中，从而避免跨分片的join。

ER分片

在关系型数据库中，表之间往往存在一些关联的关系。如果我们可以先确定好关联关系，并将那些存在关联关系的表记录存放在同一个分片上，那么就能很好的避免跨分片join问题。在一对多关系的情况下，我们通常会选择按照数据较多的那一方进行拆分。

内存计算

随着spark内存计算的兴起，理论上来讲，很多跨数据源的操作问题看起来似乎都能够得到解决。可以将数据丢给spark集群进行内存计算，最后将计算结果返回。

存储演进

单库单表

单库单表是最常见的数据库设计，例如，有一张用户(user)表放在数据库db中，所有的用户都可以在db库中的user表中查到。

单库多表

随着用户数量的增加，user表的数据量会越来越大，当数据量达到一定程度的时候对user表的查询会渐渐的变慢，从而影响整个DB的性能。如果使用mysql, 还有一个更严重的问题是，当需要添加一列的时候，mysql会锁表，期间所有的读写操作只能等待。

可以通过某种方式将user进行水平的切分，产生两个表结构完全一样的user_0000,user_0001等表，user_0000 + user_0001 + …的数据刚好是一份完整的数据。

多库多表

随着数据量增加也许单台DB的存储空间不够，随着查询量的增加单台数据库服务器已经没办法支撑。这个时候可以再对数据库进行水平拆分。

总结

总的来说，优先考虑分区 --> 当分区不能满足需求时，开始考虑分表，合理的分表对效率的提升会优于分区 --> 最后才是分库。

MySQL 使用自增ID主键和UUID 作为主键的优劣比较详细过程（从百万到千万表记录测试）

（1）单实例或者单节点组：

经过500W、1000W的单机表测试，自增ID相对UUID来说，自增ID主键性能高于UUID，磁盘存储费用比UUID节省一半的钱。所以在单实例上或者单节点组上，使用自增ID作为首选主键。

（2）分布式架构场景：

20个节点组下的小型规模的分布式场景，为了快速实现部署，可以采用多花存储费用、牺牲部分性能而使用UUID主键快速部署；

20到200个节点组的中等规模的分布式场景，可以采用自增ID+步长的较快速方案。

200以上节点组的大数据下的分布式场景，可以借鉴类似twitter雪花算法构造的全局自增ID作为主键。

分区

就是把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的。

分区的实现方式（简单）

mysql5 开始支持分区功能


   
   
     
     
      
      
     
     
     
     
      
      
       
       CREATE 
       
       TABLE sales (
      
      
     
     

     
     
      
      
     
     
     
     
      
          
       
       id 
       
       INT AUTO_INCREMENT,
      
      
     
     

     
     
      
      
     
     
     
     
      
      
       
           amount 
       
       DOUBLE 
       
       NOT 
       
       NULL,
      
      
     
     

     
     
      
      
     
     
     
     
      
      
       
           order_day DATETIME 
       
       NOT 
       
       NULL,
      
      
     
     

     
     
      
      
     
     
     
     
      
      
       
           PRIMARY 
       
       KEY(
       
       id, order_day)
      
      
     
     

     
     
      
      
     
     
     
     
      
      
       
       ) 
       
       ENGINE=
       
       Innodb 
      
      
     
     

     
     
      
      
     
     
     
     
      
      
       
       PARTITION 
       
       BY 
       
       RANGE(
       
       YEAR(order_day)) (
      
      
     
     

     
     
      
      
     
     
     
     
      
          
       
       PARTITION p_2010 
       
       VALUES 
       
       LESS 
       
       THAN (
       
       2010),
      
      
     
     

     
     
      
      
     
     
     
     
      
          
       
       PARTITION p_2011 
       
       VALUES 
       
       LESS 
       
       THAN (
       
       2011),
      
      
     
     

     
     
      
      
     
     
     
     
      
          
       
       PARTITION p_2012 
       
       VALUES 
       
       LESS 
       
       THAN (
       
       2012),
      
      
     
     

     
     
      
      
     
     
     
     
      
      
       
       PARTITION p_catchall 
       
       VALUES 
       
       LESS 
       
       THAN MAXVALUE);