精华内容
下载资源
问答
  • 由于业务需要,需要对Mysql数据库进行分库分表,故而最近一直在整理分库分表的相关知识,现手上的工作也告一段落了,抽空将自己最近的学习结果转化为博文,分享给大家,本博文打算做成一个系列的,首先是分库分表的...

    由于业务需要,需要对Mysql数据库进行分库分表,故而最近一直在整理分库分表的相关知识,现手上的工作也告一段落了,抽空将自己最近的学习结果转化为博文,分享给大家,本博文打算做成一个系列的,首先是分库分表的理论知识的了解,其次是基于Java编程语言的分库分表的框架的开发,最后是分库分表的编制。让大家不仅仅从理论上了解mysql的分库分表,通过代码来更深层次的了解,理论是如何落地到实践的。最后非常感谢《可伸缩服务架构 框架与中间件》这本书的作者么,本博文的代码实现是参考此书,然后结合当前系统平台框架开发而成。

    坚持我写作的一贯风格,我们先需要带着问题来了解mysql的分库分表

    • 什么是分库分表,为什么我们需要分库分表
    • 如何进行分库分表,有什么优缺点
    • 对于分库分表有哪些架构设计,对于后期的扩容扩展怎么样
    • 目前行业内流行的解决方案有哪些?各自有什么特点
    • 自己设计一个数据库分库分表的框架,如何设计,需要考虑哪些因素

    为什么需要分库分表

    随着我们的系统运行,存储在关系型数据库的数据量会越来越大,系统的访问的压力也会随之增大,如果一个库中的表数据超过了一定的数量,比如说mysql中的表数据达到千万级别,就需要考虑进行分库分表;

    其次随着表数据的不断增大,会发现,查询也随着变得缓慢,如果添加索引的话,会发现影响到了新增和删除的性能,如果我们将数据库分散到不同的表上,单表的索引大小就得到了控制,对索引以及表结构的变更会变得很方便和高效;

    当数据库实例的吞吐量达到性能的瓶颈时,我们需要扩展数据库实例,让每个数据库实例承担其中一部分数据库的请求,分解总体的大请求量的压力;

    在数据库进行扩容的时候对应用层的配置改变最少, 就需要在每个数据库实例中预留足够的数据库数量

    以上的情况我们都可以使用分库分表,那么什么是分库分表呢?

    简而言之就是数据拆分:将一个表结构分为多个表,或者将一个表数据分片后放入多个表,这些表可以放在同一个数据库里,也可以放到不同的数据库中,甚至可以放到不同的数据库实例中

    数据拆分的方式

    数据拆分有两种方式:

    • 垂直拆分: 根据业务的维度,将原本一个库中的表拆分多个表,每个库中表与原有的结构不同
    • 水平拆分: 根据分片算法,将一个库拆分成多个库,每个库依旧保留原有的结构

    在实际的开发过程中,通常是先进行维度拆分形成微服务结构,然后再进行水平拆分

    分库分表

    比如我们有一张表,随着业务的不断进行,mysql中表中数据量达到了10亿,若是将数据存放在一张表中,则性能一定不会太好,根据我们使用的经验,mysql数据库一张表的数据记录极限一般在5000万左右,所以我们需要对进行分片存储(水平拆分),按照5000万一个单位来拆分的话,需要切片数量20个,也就是20个数据库表

    如果将20个相同业务表存放在同一个数据库中,那么单个数据库实例的网卡I/O、内存、CPU和磁盘性能是有限的,随着数据库访问频率的增加,会导致单个数据库实例和数据库达到性能瓶颈,因此我们需要将20个表分到多个数据库和多个数据库实例中,具体的评估如下:
    【TODO 对数据库实例和数据库表的数量的评估】

    image

    如何进行分库分表

    分库分表是对数据库拆分的一种解决方案,根据实施切片逻辑的层次不同,我们将分库分表方案大致分为三大类:客户端分片、代理分片和支持事务的分布式数据库

    • 客户端分片

    所谓的客户端分片即在使用数据库的应用层直接操作分片逻辑,分片规则需要在同一个应用的多个节点间进行同步,每个应用层嵌入一个操作切片的逻辑实现。

    image

    在客户端分片,目前主要有以下三种方式:

    1. 在应用层直接实现

    这是一种非常通用的解决方案,直接在应用层读取分片规则,解析分片规则,根据分片规则实现切分的路由逻辑,从应用层直接决定每次操作应该使用哪个数据库实例中的对应的数据库

    这种解决方案虽然有一定的代码侵入,但是实现起来比较简单,但是切片的逻辑是自己开发的, 如果生产上遇到了问题,能快速定位解决;

    当然这种方式也存在缺点:代码的耦合度比较高,其次这种实现方式会让数据库保持的链接比较多,这要看应用服务的节点数量,需要提前进行容量上的评估

    1. 通过定制JDBC协议实现

    这种解决方案主要是为了解决1中解决方案中的代码耦合,通过定制JDBC协议来实现(主要是针对业务逻辑层提供与JDBC一致的接口),让分库分表在JDBC的内部实现

    目前当当网开源的框架:Sharding JDBC 就是使用这种解决方案来实现的

    1. 通过定制ORM框架实现

    目前ORM框架非常流行,流行的JPA、Mybatis和Hibernate都是优秀的ORM框架,通过定制ORM框架来实现分库分表方案,常见的有基于Mybatis的分库分表方案的解决;

        <select id="selectUser" parameterType="java.util.Map" resultType="User">
            select user_id as userId,user_name as userName
            from user_#{index}
            where user_id = #{userId}
        </select>
    
    • 代理分片

    代理分片就是在应用层和数据库层之间添加一个代理层,把分片的路由规则配置在代理层,代理层对外提供与JDBC兼容的接口给应用层,在业务实现之后,在代理层配置路由规则即可;

    image

    这种方案的优点:让应用层的开发人员专注于业务逻辑的实现,把分库分表的配置留给代理层处理
    同样的业务存在缺点:增加了代理层,这样的话对每个数据库操作都增加了一层网络传输,这对性能是有影响的,同时需要维护增加的代理层,也有了硬件成本,线上生产环境出现了问题,不能迅速定位,需要有一定的技术专家来维护

    我们常见的 Mycat就是基于此种解决方案来实现的

    • 支持事务的分布式数据库

    支持分布式事务的框架,目前有OceanBase、TiDB框架,这些框架将可伸缩特定和分布式事务的实现包装到了分布式数据库内部实现,对使用者透明,使用者不需要直接控制这些特性,但是对事务的支持不如关系型数据,适合大数据日志系统、统计系统、查询系统、社交网站等

    分库分表的架构设计

    上面我们介绍过数据拆分的两种方式:垂直拆分和水平拆分;

    拆分方式优点缺点
    垂直拆分1. 拆分后业务清晰,拆分规则明确
    2. 系统之间进行整合或扩展容易
    3. 按照成本、应用等级、应用的类型等将表放到不同的机器上,便于管理
    4.便于实现动静分离、冷热分离的数据库表的设计模式
    5. 数据维护简单
    1. 部分业务表无法进行关联、只能通过接口的方式来解决,提高了系统的复杂度
    2. 受每种业务不同的限制,存在单库性能瓶颈,对数据扩展和性能提升不友好
    3. 事务处理复杂
    水平拆分1. 单裤单表的数据保持一定的量级,有助于性能的提高
    2. 切分的表的结构相同,应用层改造较少,只需要增加路由规则即可
    3. 提高了系统的稳定性和负载能力
    1. 切分后数据是分散的,很难利用数据库的关联查询,跨库查询性能较差
    2. 拆分规则难以抽象
    3. 分片数据的一致性难以解决
    4. 数据扩容的难度和维护量极大

    综上所述,我们发现垂直拆分和水平拆分具有共同点:

    1. 存在分布式事务问题
    2. 存在跨节点join的问题
    3. 存在跨节点合并排序、分页的问题
    4. 存在多数据源管理的问题

    垂直拆分更偏向于业务拆分的过程,在技术上我们更倾向于水平切分的方案;

    常见的分片策略:

    • 按照哈希切片

    对数据库的某个字段进行来求哈希,再除以分片总数后取模,取模后相同的数据为一个分片,这样将数据分成多个分片的方法叫做哈希分片

    我们大多数在数据没有时效性的情况下使用哈希分片,就是数据不管是什么时候产生的,系统都需要处理或者查询;

    优点缺点
    数据切片比较均匀,数据压力分散的效果好数据分散后,对于查询需求需要进行聚合处理
    • 按照时间切片

    按照时间的范围将数据分布到不同的分片上,比如我们可以将交易数据按照与进行切片,或者按照季度进行切片,由交易数据的多少来决定按照什么样的时间周期来进行切片

    这种切片方式适合明显时间特点的数据,常见的就是订单历史查询

    分布式事务

    本博文不进行分布式事务的分析和实践,后期我会更新一系列的分布式事务的博文,一起探讨分布式事务的原理、解决方案和代码实践等,本博文简单介绍了分布式事务的解决方案;

    上面说到的,不管是垂直拆分还是水平拆分,都有一个共同的问题:分布式事务

    我们将单表的数据切片后存储在多个数据库甚至是多个数据库实例中,所以依靠数据库本身的事务机制不能满足需要,这时就需要用到分布式事务来解决了

    三种解决方案

    • 两阶段提交协议

    两阶段提交协议中的两阶段是:准备阶段和提交阶段,两个阶段都是由事务管理器(协调者)发起,事务管理器能最大限度的保证跨数据库操作的事务的原子性。

    具体的交互逻辑如下:

    image

    优点缺点
    是分布式系统环境下最严格的事务实现防范,
    保证了数据一致性和操作原子性
    1. 难以进行水平伸缩,因为在提交事务过程中,事务管理器需要和每个参与者进行准备和提交的操作协调
    2.每个参与者之间的协调需要时间,参与者一多的话,则锁定资源和消费资源之间的时间差就边长
    3. 两阶段提交协议是阻塞协议,在极端情况下不能快速响应的话,会造成阻塞问题
    • 最大努力保证模式

    这是一种非常通用的保证分布式一致性的模式,适合对一致性要求不是十分严格的但是对性能要求比较高的场景

    最大努力保证模式:在更新多个资源时,将多个资源的提交尽量延后到最后一刻进行处理,这样的话,如果业务流程出现问题,则所有的资源更新都可以回滚,事务仍然保持一致。

    最大努力保证模式在发生系统问题,比如网络问题等会出现问题,造成数据一致性的问题 ,这是就需要进行实时补偿,将已提交的事务进行回滚

    一般情况下,使用消息中间件来完成消费者之间的事务协调,客户端从消息中间件的队列中消费消息,更新数据库,此时会涉及到两个操作,一是从消息中间件消费消息,二是更新数据库,具体的操作步骤如下:

    1. 开启消息事务
    2. 接收消息
    3. 开启数据库事务
    4. 更新数据库
    5. 提交数据库事务
    6. 提交消息事务

    上述步骤最关键的地方在5和6,如果5成功了,但是6出现了问题,导致消息中间件认为消息没有被成功消费,既有的机制会重新再消费消息,就会出现消息重复消费,这是需要幂等处理来避免消息的重新消费

    其次我们还需要注意消息消费的顺序性问题,以及消费过程中是否调用远程接口等耗时操作

    优点缺点
    性能较高1. 数据一致性不能完美保证,只能是最大保证
    2. 可能出现消息重复消费(幂等处理)
    3. 数据库事务可能存在远程操作嵌套,互相影响
    • 事务补偿机制

    以上提到的两种解决方案:两阶段提交协议对系统的性能影响较大,最大努力保证模式会是多个分布式操作互相嵌套,有可能互相影响,那么我们采用事务补偿机制:

    事务补偿即在事务链中的任何一个正向事务操作,都必须存在一个完全符合回滚规则的可逆事务。如果是一个完整的事务链,则必须事务链中的每一个业务服务或操作都有对应的可逆服务。对于Service服务本身无状态,也不容易实现前面讨论过的通过DTC或XA机制实现的跨应用和资源的事务管理,建立跨资源的事务上下文.

    我们通过跨银行转账来说明:

    首先调用取款服务,完全调用成功并返回,数据已经持久化。然后调用异地的存款服务,如果也调用成功,则本身无任何问题。如果调用失败,则需要调用本地注册的逆向服务(本地存款服务),如果本地存款服务调用失败,则必须考虑重试,如果约定重试次数仍然不成功,则必须log到完整的不一致信息。也可以是将本地存款服务作为消息发送到消息中间件,由消息中间件接管后续操作。

    最后添加的重试机制是最大程度的确保补偿服务执行,保持数据的一致性,如果重试之后还是失败,则将操作保存在消息中间件中,等待后续处理,这样就更多了一重保障

    image

    分库分表引起的问题

    由于将完整的数据分成若干份,在以下的场景中会产生多种问题

    • 扩容与迁移

    在分库分表中,如果涉及的分片已经达到了承载数据的最大值,就需要对集群进行扩容,通常包括以下的步骤

    1. 按照新旧分片规则,对新旧数据库进行双写
    2. 将双写前按照旧分片规则写入的历史数据,根据新分片规则迁移写入新的数据库
    3. 将按照旧的分片规则查询改为按照新的分片规则查询
    4. 将双写数据库逻辑从代码中下线,只按照新的分片规则写入数据
    5. 删除按照旧分片规则写入的历史数据

    2步骤中迁移数据时,数据量非常大,通常会导致不一致,因此需要先迁移旧的数据,洗完后再迁移到新规则的新数据库下,再做全量对比,对比评估在迁移过程中是否有数据的更新,如果有的话就再清洗、迁移,最后以对比没有差距为准

    • 分库分表维度导致的查询问题

    进行了分库分表以后,如果查询的标准是分片的主键,则可以通过分片规则再次路由并查询,但是对于其他主键的查询、范围查询、关联查询、查询结果排序等,并不是按照分库分表维度查询的;

    这样的话,解决方案有以下三种:

    1. 在多个分片表中查询后合并数据集,这种方式的效率最低
    2. 冗余记录多份数据,方便查询, 缺点是需要额外维护一份数据,浪费资源
    3. 通过搜索引擎解决,但如果实时性要求很高,就需要实现实时搜索,可以利用大数据相关特性来解决
    • 跨库事务难以实现

    同时操作多个库,则会出现数据不一致的情况,此时可以引用分布式事务来解决

    • 同组数据跨库问题

    要尽量把同一组数据放到同一数据库服务器上,不但在某些场景下可以利用本地事务的强一致性,还可以是这组数据自治

    主流的解决方案

    目前针对mysql的分库分表,行业内主流的解决方案有:ShardingJDBC、Mycat

    Mycat代理分片框架

    Mycat是一款面向企业级应用的开源数据库中间件产品,他目前支持数据库集群,分布式事务与ACID,被普遍视为基于Mysql技术的集群分布式数据库解决方案

    Mycat支持多种分片规则:

    • 枚举法
    • 固定分片的hash算法
    • 范围约定
    • 求模法
    • 日期列分区法
    • 通配取模
    • ASCII码求模通配
    • 编程指定
    • 截取数据哈希解析
    • 一致性Hash

    具体的Mycat使用方法,以后应该会有一博文来整理,敬请期待啊~~~

    展开全文
  • 数据库分库分表思路及案例分析

    千次阅读 2018-12-03 14:23:25
    当单表的数据量达到 1000W 或 100G 以后,由于查询维度较多,即使添加从、优化索引,做很多操作时性能仍下降严重。此时就要考虑对其进行切分了,切分的目的就在于减少数据库的负担,缩短查询时间。 数据库分布式...

    一. 数据切分

    关系型数据库本身比较容易成为系统瓶颈,单机存储容量、连接数、处理能力都有限。当单表的数据量达到 1000W 或 100G 以后,由于查询维度较多,即使添加从库、优化索引,做很多操作时性能仍下降严重。此时就要考虑对其进行切分了,切分的目的就在于减少数据库的负担,缩短查询时间。

    数据库分布式核心内容无非就是数据切分 (Sharding),以及切分后对数据的定位、整合。数据切分就是将数据分散存储到多个数据库中,使得单一数据库中的数据量变小,通过扩充主机的数量缓解单一数据库的性能问题,从而达到提升数据库操作性能的目的。

    数据切分根据其切分类型,可以分为两种方式:垂直 (纵向) 切分和水平 (横向) 切分

    1、垂直 (纵向) 切分

    垂直切分常见有垂直分库和垂直分表两种。

    垂直分库就是根据业务耦合性,将关联度低的不同表存储在不同的数据库。做法与大系统拆分为多个小系统类似,按业务分类进行独立划分。与 “微服务治理” 的做法相似,每个微服务使用单独的一个数据库。如图:
    在这里插入图片描述

    垂直分表是基于数据库中的 “列” 进行,某个表字段较多,可以新建一张扩展表,将不经常用或字段长度较大的字段拆分出去到扩展表中。在字段很多的情况下 (例如一个大表有 100 多个字段),通过 “大表拆小表”,更便于开发与维护,也能避免跨页问题,MySQL 底层是通过数据页存储的,一条记录占用空间过大会导致跨页,造成额外的性能开销。另外数据库以行为单位将数据加载到内存中,这样表中字段长度较短且访问频率较高,内存能加载更多的数据,命中率更高,减少了磁盘 IO,从而提升了数据库性能。
    在这里插入图片描述

    垂直切分的优点:

    解决业务系统层面的耦合,业务清晰

    与微服务的治理类似,也能对不同业务的数据进行分级管理、维护、监控、扩展等

    高并发场景下,垂直切分一定程度的提升 IO、数据库连接数、单机硬件资源的瓶颈

    缺点:

    部分表无法 join,只能通过接口聚合方式解决,提升了开发的复杂度

    分布式事务处理复杂

    依然存在单表数据量过大的问题 (需要水平切分)

    2、水平 (横向) 切分

    当一个应用难以再细粒度的垂直切分,或切分后数据量行数巨大,存在单库读写、存储性能瓶颈,这时候就需要进行水平切分了。

    水平切分分为库内分表和分库分表,是根据表内数据内在的逻辑关系,将同一个表按不同的条件分散到多个数据库或多个表中,每个表中只包含一部分数据,从而使得单个表的数据量变小,达到分布式的效果。如图所示:
    在这里插入图片描述

    库内分表只解决了单一表数据量过大的问题,但没有将表分布到不同机器的库上,因此对于减轻 MySQL 数据库的压力来说,帮助不是很大,大家还是竞争同一个物理机的 CPU、内存、网络 IO,最好通过分库分表来解决。

    水平切分的优点:

    不存在单库数据量过大、高并发的性能瓶颈,提升系统稳定性和负载能力

    应用端改造较小,不需要拆分业务模块

    缺点:

    跨分片的事务一致性难以保证

    跨库的 join 关联查询性能较差

    数据多次扩展难度和维护量极大

    水平切分后同一张表会出现在多个数据库 / 表中,每个库 / 表的内容不同。几种典型的数据分片规则为:

    1、根据数值范围

    按照时间区间或 ID 区间来切分。例如:按日期将不同月甚至是日的数据分散到不同的库中; 将 userId 为 1~9999 的记录分到第一个库,10000~20000 的分到第二个库,以此类推。某种意义上,某些系统中使用的 “冷热数据分离”,将一些使用较少的历史数据迁移到其他库中,业务功能上只提供热点数据的查询,也是类似的实践。

    这样的优点在于:

    单表大小可控

    天然便于水平扩展,后期如果想对整个分片集群扩容时,只需要添加节点即可,无需对其他分片的数据进行迁移

    使用分片字段进行范围查找时,连续分片可快速定位分片进行快速查询,有效避免跨分片查询的问题。

    缺点:

    热点数据成为性能瓶颈。连续分片可能存在数据热点,例如按时间字段分片,有些分片存储最近时间段内的数据,可能会被频繁的读写,而有些分片存储的历史数据,则很少被查询
    在这里插入图片描述

    2、根据数值取模

    一般采用 hash 取模 mod 的切分方式,例如:将 Customer 表根据 cusno 字段切分到 4 个库中,余数为 0 的放到第一个库,余数为 1 的放到第二个库,以此类推。这样同一个用户的数据会分散到同一个库中,如果查询条件带有 cusno 字段,则可明确定位到相应库去查询。

    优点:

    数据分片相对比较均匀,不容易出现热点和并发访问的瓶颈

    缺点:

    后期分片集群扩容时,需要迁移旧的数据 (使用一致性 hash 算法能较好的避免这个问题)

    容易面临跨分片查询的复杂问题。比如上例中,如果频繁用到的查询条件中不带 cusno 时,将会导致无法定位数据库,从而需要同时向 4 个库发起查询,再在内存中合并数据,取最小集返回给应用,分库反而成为拖累。
    在这里插入图片描述

    二. 分库分表带来的问题
    分库分表能有效的环节单机和单库带来的性能瓶颈和压力,突破网络 IO、硬件资源、连接数的瓶颈,同时也带来了一些问题。下面将描述这些技术挑战以及对应的解决思路。

    1、事务一致性问题

    分布式事务

    当更新内容同时分布在不同库中,不可避免会带来跨库事务问题。跨分片事务也是分布式事务,没有简单的方案,一般可使用 “XA 协议” 和 “两阶段提交” 处理。

    分布式事务能最大限度保证了数据库操作的原子性。但在提交事务时需要协调多个节点,推后了提交事务的时间点,延长了事务的执行时间。导致事务在访问共享资源时发生冲突或死锁的概率增高。随着数据库节点的增多,这种趋势会越来越严重,从而成为系统在数据库层面上水平扩展的枷锁。

    最终一致性

    对于那些性能要求很高,但对一致性要求不高的系统,往往不苛求系统的实时一致性,只要在允许的时间段内达到最终一致性即可,可采用事务补偿的方式。与事务在执行中发生错误后立即回滚的方式不同,事务补偿是一种事后检查补救的措施,一些常见的实现方法有:对数据进行对账检查,基于日志进行对比,定期同标准数据来源进行同步等等。事务补偿还要结合业务系统来考虑。

    2、跨节点关联查询 join 问题

    切分之前,系统中很多列表和详情页所需的数据可以通过 sql join 来完成。而切分之后,数据可能分布在不同的节点上,此时 join 带来的问题就比较麻烦了,考虑到性能,尽量避免使用 join 查询。

    解决这个问题的一些方法:

    1) 全局表

    全局表,也可看做是 “数据字典表”,就是系统中所有模块都可能依赖的一些表,为了避免跨库 join 查询,可以将这类表在每个数据库中都保存一份。这些数据通常很少会进行修改,所以也不担心一致性的问题。

    2) 字段冗余

    一种典型的反范式设计,利用空间换时间,为了性能而避免 join 查询。例如:订单表保存 userId 时候,也将 userName 冗余保存一份,这样查询订单详情时就不需要再去查询 “买家 user 表” 了。

    但这种方法适用场景也有限,比较适用于依赖字段比较少的情况。而冗余字段的数据一致性也较难保证,就像上面订单表的例子,买家修改了 userName 后,是否需要在历史订单中同步更新呢? 这也要结合实际业务场景进行考虑。

    3) 数据组装

    在系统层面,分两次查询,第一次查询的结果集中找出关联数据 id,然后根据 id 发起第二次请求得到关联数据。最后将获得到的数据进行字段拼装。

    4)ER 分片

    关系型数据库中,如果可以先确定表之间的关联关系,并将那些存在关联关系的表记录存放在同一个分片上,那么就能较好的避免跨分片 join 问题。在 1:1 或 1:n 的情况下,通常按照主表的 ID 主键切分。如下图所示:
    在这里插入图片描述

    这样一来,Data Node1 上面的 order 订单表与 orderdetail 订单详情表就可以通过 orderId 进行局部的关联查询了,Data Node2 上也一样。

    3、跨节点分页、排序、函数问题

    跨节点多库进行查询时,会出现 limit 分页、order by 排序等问题。分页需要按照指定字段进行排序,当排序字段就是分片字段时,通过分片规则就比较容易定位到指定的分片; 当排序字段非分片字段时,就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回,然后将不同分片返回的结果集进行汇总和再次排序,最终返回给用户。如图所示:
    在这里插入图片描述

    上图中只是取第一页的数据,对性能影响还不是很大。但是如果取得页数很大,情况则变得复杂很多,因为各分片节点中的数据可能是随机的,为了排序的准确性,需要将所有节点的前 N 页数据都排序好做合并,最后再进行整体的排序,这样的操作时很耗费 CPU 和内存资源的,所以页数越大,系统的性能也会越差。

    在使用 Max、Min、Sum、Count 之类的函数进行计算的时候,也需要先在每个分片上执行相应的函数,然后将各个分片的结果集进行汇总和再次计算,最终将结果返回。如图所示:
    在这里插入图片描述

    4、全局主键避重问题

    在分库分表环境中,由于表中数据同时存在不同数据库中,主键值平时使用的自增长将无用武之地,某个分区数据库自生成的 ID 无法保证全局唯一。因此需要单独设计全局主键,以避免跨库主键重复问题。有一些常见的主键生成策略:

    1)UUID

    UUID 标准形式包含 32 个 16 进制数字,分为 5 段,形式为 8-4-4-4-12 的 36 个字符,例如:550e8400-e29b-41d4-a716-446655440000

    UUID 是主键是最简单的方案,本地生成,性能高,没有网络耗时。但缺点也很明显,由于 UUID 非常长,会占用大量的存储空间; 另外,作为主键建立索引和基于索引进行查询时都会存在性能问题,在 InnoDB 下,UUID 的无序性会引起数据位置频繁变动,导致分页。

    2) 结合数据库维护主键 ID 表

    在数据库中建立 sequence 表:
    在这里插入图片描述

    stub 字段设置为唯一索引,同一 stub 值在 sequence 表中只有一条记录,可以同时为多张表生成全局 ID。sequence 表的内容,如下所示:
    在这里插入图片描述

    使用 MyISAM 存储引擎而不是 InnoDB,以获取更高的性能。MyISAM 使用的是表级别的锁,对表的读写是串行的,所以不用担心在并发时两次读取同一个 ID 值。

    当需要全局唯一的 64 位 ID 时,执行:

    REPLACE INTO sequence (stub) VALUES (‘a’);

    SELECT LAST_INSERT_ID();

    这两条语句是 Connection 级别的,select last_insert_id() 必须与 replace into 在同一数据库连接下才能得到刚刚插入的新 ID。

    使用 replace into 代替 insert into 好处是避免了表行数过大,不需要另外定期清理。

    此方案较为简单,但缺点也明显:存在单点问题,强依赖 DB,当 DB 异常时,整个系统都不可用。配置主从可以增加可用性,但当主库挂了,主从切换时,数据一致性在特殊情况下难以保证。另外性能瓶颈限制在单台 MySQL 的读写性能。

    flickr 团队使用的一种主键生成策略,与上面的 sequence 表方案类似,但更好的解决了单点和性能瓶颈的问题。

    这一方案的整体思想是:建立 2 个以上的全局 ID 生成的服务器,每个服务器上只部署一个数据库,每个库有一张 sequence 表用于记录当前全局 ID。表中 ID 增长的步长是库的数量,起始值依次错开,这样能将 ID 的生成散列到各个数据库上。如下图所示:
    在这里插入图片描述

    由两个数据库服务器生成 ID,设置不同的 auto_increment 值。第一台 sequence 的起始值为 1,每次步长增长 2,另一台的 sequence 起始值为 2,每次步长增长也是 2。结果第一台生成的 ID 都是奇数 (1, 3, 5, 7 …),第二台生成的 ID 都是偶数 (2, 4, 6, 8 …)。

    这种方案将生成 ID 的压力均匀分布在两台机器上。同时提供了系统容错,第一台出现了错误,可以自动切换到第二台机器上获取 ID。但有以下几个缺点:系统添加机器,水平扩展时较复杂; 每次获取 ID 都要读写一次 DB,DB 的压力还是很大,只能靠堆机器来提升性能。

    可以基于 flickr 的方案继续优化,使用批量的方式降低数据库的写压力,每次获取一段区间的 ID 号段,用完之后再去数据库获取,可以大大减轻数据库的压力。如下图所示:
    在这里插入图片描述

    还是使用两台 DB 保证可用性,数据库中只存储当前的最大 ID。ID 生成服务每次批量拉取 6 个 ID,先将 max_id 修改为 5,当应用访问 ID 生成服务时,就不需要访问数据库,从号段缓存中依次派发 0~5 的 ID。当这些 ID 发完后,再将 max_id 修改为 11,下次就能派发 6~11 的 ID。于是,数据库的压力降低为原来的 1/6。

    3)Snowflake 分布式自增 ID 算法

    Twitter 的 snowflake 算法解决了分布式系统生成全局 ID 的需求,生成 64 位的 Long 型数字,组成部分:

    第一位未使用

    接下来 41 位是毫秒级时间,41 位的长度可以表示 69 年的时间

    5 位 datacenterId,5 位 workerId。10 位的长度最多支持部署 1024 个节点

    最后 12 位是毫秒内的计数,12 位的计数顺序号支持每个节点每毫秒产生 4096 个 ID 序列
    在这里插入图片描述

    这样的好处是:毫秒数在高位,生成的 ID 整体上按时间趋势递增; 不依赖第三方系统,稳定性和效率较高,理论上 QPS 约为 409.6w/s(1000*2^12),并且整个分布式系统内不会产生 ID 碰撞; 可根据自身业务灵活分配 bit 位。

    不足就在于:强依赖机器时钟,如果时钟回拨,则可能导致生成 ID 重复。

    综上结合数据库和 snowflake 的唯一 ID 方案,可以参考业界较为成熟的解法:Leaf——美团点评分布式 ID 生成系统,并考虑到了高可用、容灾、分布式下时钟等问题。

    5、数据迁移、扩容问题

    当业务高速发展,面临性能和存储的瓶颈时,才会考虑分片设计,此时就不可避免的需要考虑历史数据迁移的问题。一般做法是先读出历史数据,然后按指定的分片规则再将数据写入到各个分片节点中。此外还需要根据当前的数据量和 QPS,以及业务发展的速度,进行容量规划,推算出大概需要多少分片 (一般建议单个分片上的单表数据量不超过 1000W)

    如果采用数值范围分片,只需要添加节点就可以进行扩容了,不需要对分片数据迁移。如果采用的是数值取模分片,则考虑后期的扩容问题就相对比较麻烦。

    三. 什么时候考虑切分 
    下面讲述一下什么时候需要考虑做数据切分。

    1、能不切分尽量不要切分

    并不是所有表都需要进行切分,主要还是看数据的增长速度。切分后会在某种程度上提升业务的复杂度,数据库除了承载数据的存储和查询外,协助业务更好的实现需求也是其重要工作之一。

    不到万不得已不用轻易使用分库分表这个大招,避免 “过度设计” 和 “过早优化”。分库分表之前,不要为分而分,先尽力去做力所能及的事情,例如:升级硬件、升级网络、读写分离、索引优化等等。当数据量达到单表的瓶颈时候,再考虑分库分表。

    2、数据量过大,正常运维影响业务访问

    这里说的运维,指:

    1) 对数据库备份,如果单表太大,备份时需要大量的磁盘 IO 和网络 IO。例如 1T 的数据,网络传输占 50MB 时候,需要 20000 秒才能传输完毕,整个过程的风险都是比较高的

    2) 对一个很大的表进行 DDL 修改时,MySQL 会锁住全表,这个时间会很长,这段时间业务不能访问此表,影响很大。如果使用 pt-online-schema-change,使用过程中会创建触发器和影子表,也需要很长的时间。在此操作过程中,都算为风险时间。将数据表拆分,总量减少,有助于降低这个风险。

    3) 大表会经常访问与更新,就更有可能出现锁等待。将数据切分,用空间换时间,变相降低访问压力

    3、随着业务发展,需要对某些字段垂直拆分

    举个例子,假如项目一开始设计的用户表如下:

    id bigint #用户的 ID

    name varchar# 用户的名字

    last_login_timedatetime #最近登录时间

    personal_infotext #私人信息

    … #其他信息字段

    在项目初始阶段,这种设计是满足简单的业务需求的,也方便快速迭代开发。而当业务快速发展时,用户量从 10w 激增到 10 亿,用户非常的活跃,每次登录会更新 last_login_name 字段,使得 user 表被不断 update,压力很大。而其他字段:id, name, personal_info 是不变的或很少更新的,此时在业务角度,就要将 last_login_time 拆分出去,新建一个 user_time 表。

    personal_info 属性是更新和查询频率较低的,并且 text 字段占据了太多的空间。这时候,就要对此垂直拆分出 user_ext 表了。

    4、数据量快速增长

    随着业务的快速发展,单表中的数据量会持续增长,当性能接近瓶颈时,就需要考虑水平切分,做分库分表了。此时一定要选择合适的切分规则,提前预估好数据容量

    5、安全性和可用性

    鸡蛋不要放在一个篮子里。在业务层面上垂直切分,将不相关的业务的数据库分隔,因为每个业务的数据量、访问量都不同,不能因为一个业务把数据库搞挂而牵连到其他业务。利用水平切分,当一个数据库出现问题时,不会影响到 100% 的用户,每个库只承担业务的一部分数据,这样整体的可用性就能提高。

    四. 案例分析

    1、用户中心业务场景

    用户中心是一个非常常见的业务,主要提供用户注册、登录、查询 / 修改等功能,其核心表为:

    User(uid, login_name, passwd, sex, age, nickname)

    uid 为用户 ID, 主键

    login_name, passwd, sex, age, nickname, 用户属性

    任何脱离业务的架构设计都是耍流氓,在进行分库分表前,需要对业务场景需求进行梳理:

    用户侧:前台访问,访问量较大,需要保证高可用和高一致性。主要有两类需求:

    用户登录:通过 login_name/phone/email 查询用户信息,1% 请求属于这种类型

    用户信息查询:登录之后,通过 uid 来查询用户信息,99% 请求属这种类型

    运营侧:后台访问,支持运营需求,按照年龄、性别、登陆时间、注册时间等进行分页的查询。是内部系统,访问量较低,对可用性、一致性的要求不高。

    2、水平切分方法

    当数据量越来越大时,需要对数据库进行水平切分,上文描述的切分方法有 “根据数值范围” 和 “根据数值取模”。

    “根据数值范围”:以主键 uid 为划分依据,按 uid 的范围将数据水平切分到多个数据库上。例如:user-db1 存储 uid 范围为 0~1000w 的数据,user-db2 存储 uid 范围为 1000w~2000wuid 数据。

    优点是:扩容简单,如果容量不够,只要增加新 db 即可。

    不足是:请求量不均匀,一般新注册的用户活跃度会比较高,所以新的 user-db2 会比 user-db1 负载高,导致服务器利用率不平衡

    “根据数值取模”:也是以主键 uid 为划分依据,按 uid 取模的值将数据水平切分到多个数据库上。例如:user-db1 存储 uid 取模得 1 的数据,user-db2 存储 uid 取模得 0 的 uid 数据。

    优点是:数据量和请求量分布均均匀

    不足是:扩容麻烦,当容量不够时,新增加 db,需要 rehash。需要考虑对数据进行平滑的迁移。

    3、非 uid 的查询方法

    水平切分后,对于按 uid 查询的需求能很好的满足,可以直接路由到具体数据库。而按非 uid 的查询,例如 login_name,就不知道具体该访问哪个库了,此时需要遍历所有库,性能会降低很多。

    对于用户侧,可以采用 “建立非 uid 属性到 uid 的映射关系” 的方案; 对于运营侧,可以采用 “前台与后台分离” 的方案。

    3.1、建立非 uid 属性到 uid 的映射关系

    1) 映射关系

    例如:login_name 不能直接定位到数据库,可以建立 login_name→uid 的映射关系,用索引表或缓存来存储。当访问 login_name 时,先通过映射表查询出 login_name 对应的 uid,再通过 uid 定位到具体的库。

    映射表只有两列,可以承载很多数据,当数据量过大时,也可以对映射表再做水平切分。这类 kv 格式的索引结构,可以很好的使用 cache 来优化查询性能,而且映射关系不会频繁变更,缓存命中率会很高。

    2) 基因法

    分库基因:假如通过 uid 分库,分为 8 个库,采用 uid%8 的方式进行路由,此时是由 uid 的最后 3bit 来决定这行 User 数据具体落到哪个库上,那么这 3bit 可以看为分库基因。

    上面的映射关系的方法需要额外存储映射表,按非 uid 字段查询时,还需要多一次数据库或 cache 的访问。如果想要消除多余的存储和查询,可以通过 f 函数取 login_name 的基因作为 uid 的分库基因。生成 uid 时,参考上文所述的分布式唯一 ID 生成方案,再加上最后 3 位 bit 值 = f(login_name)。当查询 login_name 时,只需计算 f(login_name)%8 的值,就可以定位到具体的库。不过这样需要提前做好容量规划,预估未来几年的数据量需要分多少库,要预留一定 bit 的分库基因。
    在这里插入图片描述

        3.2、前台与后台分离
    

    对于用户侧,主要需求是以单行查询为主,需要建立 login_name/phone/email 到 uid 的映射关系,可以解决这些字段的查询问题。

    而对于运营侧,很多批量分页且条件多样的查询,这类查询计算量大,返回数据量大,对数据库的性能消耗较高。此时,如果和用户侧公用同一批服务或数据库,可能因为后台的少量请求,占用大量数据库资源,而导致用户侧访问性能降低或超时。

    这类业务最好采用 “前台与后台分离” 的方案,运营侧后台业务抽取独立的 service 和 db,解决和前台业务系统的耦合。由于运营侧对可用性、一致性的要求不高,可以不访问实时库,而是通过 binlog 异步同步数据到运营库进行访问。在数据量很大的情况下,还可以使用 ES 搜索引擎或 Hive 来满足后台复杂的查询方式。

    五. 支持分库分表中间件
    站在巨人的肩膀上能省力很多,目前分库分表已经有一些较为成熟的开源解决方案:

    sharding-jdbc(当当)

    TSharding(蘑菇街)

    Atlas(奇虎 360)

    Cobar(阿里巴巴)

    MyCAT(基于 Cobar)

    Oceanus(58 同城)

    Vitess(谷歌)
    转:https://mp.weixin.qq.com/s?__biz=MzI0MDQ4MTM5NQ==&mid=2247487412&idx=2&sn=cd5e5005ae23a6df957a2e6b363b51f7&chksm=e91b6aa8de6ce3be96b86d693ebec563c7282c1ad6132797baae34d91cbad72dcc9f8747d228&scene=21#wechat_redirect

    展开全文
  • 别再问什么是数据库分库分表了,看这里!

    千次阅读 多人点赞 2019-06-16 17:20:49
    编者语:为了避免被误解为:「手里有把锤子,看什么都是钉子!」,说明一下不是什么业务都...本文主要是通过几道关于分库分表的常问面试题带你深入了解数据库分库分表,希望对大家能够有所帮助! 一:面试题 为什...

    编者语:为了避免被误解为:「手里有把锤子,看什么都是钉子!」,说明一下不是什么业务都适合分布式数据库,更不是用了分布式数据库性能就一定能得到扩展。

    其次:本文为纯干货,建议先转发、收藏再观看。

    分布式数据库已经流行好多年,产品非常众多,其中分布式数据库中间件使用场景最广。本文主要是通过几道关于分库分表的常问面试题带你深入了解数据库分库分表,希望对大家能够有所帮助!

    一:面试题

    • 为什么要分库分表?
    • 用过哪些分库分表中间件?
    • 不同的分库分表中间件都有什么优点和缺点?
    • 你们具体是如何对数据库如何进行垂直拆分或水平拆分的?

    二、面试官心理分析

    其实这块肯定是扯到高并发了,因为分库分表一定是为了支撑高并发、数据量大两个问题的。而且现在说实话,尤其是互联网类的公司面试,基本上都会来这么一下,分库分表如此普遍的技术问题,不问实在是不行,而如果你不知道那也实在是说不过去!

    三、面试题剖析

    3.1、为什么要分库分表?(设计高并发系统的时候,数据库层面该如何设计?)

    说白了,分库分表是两回事儿,大家可别搞混了,可能是光分库不分表,也可能是光分表不分库,都有可能。

    我先给大家抛出来一个场景。

    假如我们现在是一个小创业公司(或者是一个 BAT 公司刚兴起的一个新部门),现在注册用户就 20 万,每天活跃用户就 1 万,每天单表数据量就 1000,然后高峰期每秒钟并发请求最多就 10。天,就这种系统,随便找一个有几年工作经验的,然后带几个刚培训出来的,随便干干都可以。

    结果没想到我们运气居然这么好,碰上个 CEO 带着我们走上了康庄大道,业务发展迅猛,过了几个月,注册用户数达到了 2000 万!每天活跃用户数 100 万!每天单表数据量 10 万条!高峰期每秒最大请求达到 1000!同时公司还顺带着融资了两轮,进账了几个亿人民币啊!公司估值达到了惊人的几亿美金!这是小独角兽的节奏!

    好吧,没事,现在大家感觉压力已经有点大了,为啥呢?因为每天多 10 万条数据,一个月就多 300 万条数据,现在咱们单表已经几百万数据了,马上就破千万了。但是勉强还能撑着。高峰期请求现在是 1000,咱们线上部署了几台机器,负载均衡搞了一下,数据库撑 1000QPS 也还凑合。但是大家现在开始感觉有点担心了,接下来咋整呢......

    再接下来几个月,我的天,CEO 太牛逼了,公司用户数已经达到 1 亿,公司继续融资几十亿人民币啊!公司估值达到了惊人的几十亿美金,成为了国内今年最牛逼的明星创业公司!天,我们太幸运了。

    但是我们同时也是不幸的,因为此时每天活跃用户数上千万,每天单表新增数据多达 50 万,目前一个表总数据量都已经达到了两三千万了!扛不住啊!数据库磁盘容量不断消耗掉!高峰期并发达到惊人的 5000~8000!别开玩笑了,哥。我跟你保证,你的系统支撑不到现在,已经挂掉了!

    好吧,所以你看到这里差不多就理解分库分表是怎么回事儿了,实际上这是跟着你的公司业务发展走的,你公司业务发展越好,用户就越多,数据量越大,请求量越大,那你单个数据库一定扛不住。

    分表

    比如你单表都几千万数据了,你确定你能扛住么?绝对不行,单表数据量太大,会极大影响你的 sql 执行的性能,到了后面你的 sql 可能就跑的很慢了。一般来说,就以我的经验来看,单表到几百万的时候,性能就会相对差一些了,你就得分表了。

    分表是啥意思?就是把一个表的数据放到多个表中,然后查询的时候你就查一个表。比如按照用户 id 来分表,将一个用户的数据就放在一个表中。然后操作的时候你对一个用户就操作那个表就好了。这样可以控制每个表的数据量在可控的范围内,比如每个表就固定在 200 万以内。

    分库

    分库是啥意思?就是你一个库一般我们经验而言,最多支撑到并发 2000,一定要扩容了,而且一个健康的单库并发值你最好保持在每秒 1000 左右,不要太大。那么你可以将一个库的数据拆分到多个库中,访问的时候就访问一个库好了。

    这就是所谓的分库分表,为啥要分库分表?你明白了吧。

    3.2、用过哪些分库分表中间件?不同的分库分表中间件都有什么优点和缺点?

    这个其实就是看看你了解哪些分库分表的中间件,各个中间件的优缺点是啥?然后你用过哪些分库分表的中间件。

    比较常见的包括:

    • cobar
    • TDDL
    • atlas
    • sharding-jdbc
    • mycat

    3.2.1:cobar

    阿里 b2b 团队开发和开源的,属于 proxy 层方案,就是介于应用服务器和数据库服务器之间。应用程序通过 JDBC 驱动访问 cobar 集群,cobar 根据 SQL 和分库规则对 SQL 做分解,然后分发到 MySQL 集群不同的数据库实例上执行。早些年还可以用,但是最近几年都没更新了,基本没啥人用,差不多算是被抛弃的状态吧。而且不支持读写分离、存储过程、跨库 join 和分页等操作。

    3.2.2:TDDL

    淘宝团队开发的,属于 client 层方案。支持基本的 crud 语法和读写分离,但不支持 join、多表查询等语法。目前使用的也不多,因为还依赖淘宝的 diamond 配置管理系统。

    3.2.3:atlas

    360 开源的,属于 proxy 层方案,以前是有一些公司在用的,但是确实有一个很大的问题就是社区最新的维护都在 5 年前了。所以,现在用的公司基本也很少了。

    3.2.4:sharding-jdbc

    当当开源的,属于 client 层方案。确实之前用的还比较多一些,因为 SQL 语法支持也比较多,没有太多限制,而且目前推出到了 2.0 版本,支持分库分表、读写分离、分布式 id 生成、柔性事务(最大努力送达型事务、TCC 事务)。而且确实之前使用的公司会比较多一些(这个在官网有登记使用的公司,可以看到从 2017 年一直到现在,是有不少公司在用的),目前社区也还一直在开发和维护,还算是比较活跃,个人认为算是一个现在也可以选择的方案。

    3.2.5:mycat

    基于 cobar 改造的,属于 proxy 层方案,支持的功能非常完善,而且目前应该是非常火的而且不断流行的数据库中间件,社区很活跃,也有一些公司开始在用了。但是确实相比于 sharding jdbc 来说,年轻一些,经历的锤炼少一些。

    小结

    综上,现在其实建议考量的,就是 sharding-jdbc 和 mycat,这两个都可以去考虑使用。

    sharding-jdbc 这种 client 层方案的优点在于不用部署,运维成本低,不需要代理层的二次转发请求,性能很高,但是如果遇到升级啥的需要各个系统都重新升级版本再发布,各个系统都需要耦合 sharding-jdbc 的依赖;

    mycat 这种 proxy 层方案的缺点在于需要部署,自己运维一套中间件,运维成本高,但是好处在于对于各个项目是透明的,如果遇到升级之类的都是自己中间件那里搞就行了。

    通常来说,这两个方案其实都可以选用,但是我个人建议中小型公司选用 sharding-jdbc,client 层方案轻便,而且维护成本低,不需要额外增派人手,而且中小型公司系统复杂度会低一些,项目也没那么多;但是中大型公司最好还是选用 mycat 这类 proxy 层方案,因为可能大公司系统和项目非常多,团队很大,人员充足,那么最好是专门弄个人来研究和维护 mycat,然后大量项目直接透明使用即可。

    3.3、你们具体是如何对数据库如何进行垂直拆分或水平拆分的?

    水平拆分的意思,就是把一个表的数据给弄到多个库的多个表里去,但是每个库的表结构都一样,只不过每个库表放的数据是不同的,所有库表的数据加起来就是全部数据。水平拆分的意义,就是将数据均匀放更多的库里,然后用多个库来扛更高的并发,还有就是用多个库的存储容量来进行扩容。

     

    垂直拆分的意思,就是把一个有很多字段的表给拆分成多个表,或者是多个库上去。每个库表的结构都不一样,每个库表都包含部分字段。一般来说,会将较少的访问频率很高的字段放到一个表里去,然后将较多的访问频率很低的字段放到另外一个表里去。因为数据库是有缓存的,你访问频率高的行字段越少,就可以在缓存里缓存更多的行,性能就越好。这个一般在表层面做的较多一些。

     

    这个其实挺常见的,不一定我说,大家很多同学可能自己都做过,把一个大表拆开,订单表、订单支付表、订单商品表。

    还有表层面的拆分,就是分表,将一个表变成 N 个表,就是让每个表的数据量控制在一定范围内,保证 SQL 的性能。否则单表数据量越大,SQL 性能就越差。一般是 200 万行左右,不要太多,但是也得看具体你怎么操作,也可能是 500 万,或者是 100 万。你的SQL越复杂,就最好让单表行数越少。

    好了,无论分库还是分表,上面说的那些数据库中间件都是可以支持的。就是基本上那些中间件可以做到你分库分表之后,中间件可以根据你指定的某个字段值,比如说 userid,自动路由到对应的库上去,然后再自动路由到对应的表里去。

    你就得考虑一下,你的项目里该如何分库分表?一般来说,垂直拆分,你可以在表层面来做,对一些字段特别多的表做一下拆分;水平拆分,你可以说是并发承载不了,或者是数据量太大,容量承载不了,你给拆了,按什么字段来拆,你自己想好;分表,你考虑一下,你如果哪怕是拆到每个库里去,并发和容量都ok了,但是每个库的表还是太大了,那么你就分表,将这个表分开,保证每个表的数据量并不是很大。

    而且这儿还有两种分库分表的方式:

    • 一种是按照 range 来分,就是每个库一段连续的数据,这个一般是按比如时间范围来的,但是这种一般较少用,因为很容易产生热点问题,大量的流量都打在最新的数据上了。
    • 或者是按照某个字段 hash 一下均匀分散,这个较为常用。

    range 来分,好处在于说,扩容的时候很简单,因为你只要预备好,给每个月都准备一个库就可以了,到了一个新的月份的时候,自然而然,就会写新的库了;缺点,但是大部分的请求,都是访问最新的数据。实际生产用 range,要看场景。

    hash 分发,好处在于说,可以平均分配每个库的数据量和请求压力;坏处在于说扩容起来比较麻烦,会有一个数据迁移的过程,之前的数据需要重新计算 hash 值重新分配到不同的库或表。

    四: 总结

    分布式数据库(中间件)的特点就是分库分表,这个比较灵活和容易理解,使用场景最广。 SQL能否将该分布式数据库的性能能力都发挥出来,取决于SQL的写法,一般跟拆分键的行为有关。不同分布式数据库产品的功能有细节上的差别,但是分库分表的逻辑基本相同,所以上面的分析同样适用于其他分布式数据库(中间件)产品。

    个人总结,如有表达不当之处欢迎指正。

    温馨提示:部分内容转载自网络

    如果你喜欢本文,请转发,想要获得更多信息,请关注

     

    展开全文
  • 数据库分库分表架构选型

    千次阅读 2018-11-30 15:20:56
    随着用户量的增加和历史数据的不断积累,导致公司系统越来越卡,稍微复杂的查询都是分钟级,甚至有前端请求超时报错的情况(2分钟),所以这段时间一直在研究公司的数据库架构。 我是一个地道的java程序员,由于我们...

    随着用户量的增加和历史数据的不断积累,导致公司系统越来越卡,稍微复杂的查询都是分钟级,甚至有前端请求超时报错的情况(2分钟),所以这段时间一直在研究公司的数据库架构。
    我是一个地道的java程序员,由于我们公司没有DBA,所以只能我来研究,这也是公司交给我的一个重要的任务,我利用做完手头项目的空余时间分析并研究了目前市场上很多的数据库架构,进行一次总结、体会。
    请谨记:
    没有最好的数据库架构,只有适不适合的数据库架构。


    0 数据库架构调整背景:

    1、sql已经无法继续优化
    2、数据库表结构设计已经无法继续优化
    3、已经做了读写分离,但是性能还是低
    4、单日数据量50W左右,不超过100W(超过100W不建议使用本文的做法,后面会讲到)
    5、读压力远大于写压力
    6、对最近的1到2个月的数据操作频繁、对最近半年的较频繁
    7、偶尔会对很久的历史数据进行查询(历史数据不能删)
    8、无法避免会进行关联查询
    9、分页、排序等功能都必须正常使用
    10、没有专门的DBA,或者公司不想运维过于复杂的数据库架构

    如果你满足以上需要,那恭喜你,这篇文章应该值得你参考。
    为了不浪费大家宝贵的时间,我这篇文章采用倒叙的方法,第一章直接介绍架构调整后的终极版本,第二章开始介绍有哪些其他的架构都被pass掉了,尽量让大家更加认同“终极版本”。当然如果大家有其他想法或者意见都欢迎评论留言。

    1 终极版本

    终极版本
    首先,解释下上图的含义:

    • 图中的master是mysql的写库、slave是mysql的读库;cti是数据库的名字,fact_call表存最近2个月的数据,fact_call_6存最近6个月的数据,fact_call_all表存所有数据。
    • fact_call_6和fact_call_all的数据每天从fact_call表同步过来,同步完毕后需要删除fact_call表中超过2个月的数据,还需要删除fact_call_6表中超过6个月的数据.
    • 注意:需要限制查询最近的超过2个月的数据(如果要查询超过2个月的数据,则不能查今天的数据,因为今天的需要到晚上才能同步到fact_call_6表中)。
    • 当然也可以设置为1个月的、2个月的、6个月的、永久的。主要思路是:不分表不分库,做表的冗余存储

    其次,mysql的脚本:

    • mysql的存储过程脚本
    -- 创建同步的存储过程
    DELIMITER //
    USE `cti`//
    DROP PROCEDURE IF EXISTS pro_syn_data//
    CREATE PROCEDURE pro_syn_data ()
    BEGIN
    INSERT INTO `fact_call_6` SELECT * FROM `fact_call` WHERE DATE(report_time) >= DATE( DATE_SUB(NOW(), INTERVAL 1 DAY) ) ;
    INSERT INTO `fact_call_all` SELECT * FROM `fact_call` WHERE DATE(report_time) >= DATE( DATE_SUB(NOW(), INTERVAL 1 DAY) ) ;
    END//
    DELIMITER ;
    
    -- 创建删除的存储过程
    DELIMITER //
    USE `cti`//
    DROP PROCEDURE IF EXISTS pro_clear_data//
    CREATE PROCEDURE pro_clear_data ()
    BEGIN
    DELETE FROM `fact_call` WHERE DATE(report_time) <= DATE( DATE_SUB(NOW(), INTERVAL 3 MONTH) ) ;
    DELETE FROM `fact_call_6` WHERE DATE(report_time) <= DATE( DATE_SUB(NOW(), INTERVAL 6 MONTH) ) ;
    END//
    DELIMITER ;
    
    • mysql的定时器脚本
    -- 查询mysql事件是否开启
    show variables like 'event_scheduler';
    select @@event_scheduler;
    
    -- 开启mysql事件
    SET GLOBAL event_scheduler = 1;
    
    -- 创建定时同步的事件
    DROP EVENT IF EXISTS `e_pro_syn_data`;
    CREATE EVENT `e_pro_syn_data` 
    ON SCHEDULE EVERY 1 DAY STARTS '2018-11-12 00:00:01' 
    ON COMPLETION NOT PRESERVE ENABLE DO CALL pro_syn_data ();
    -- 创建定时删除的事件
    DROP EVENT IF EXISTS `e_pro_clear_data`;
    CREATE EVENT `e_pro_clear_data` 
    ON SCHEDULE EVERY 1 DAY STARTS '2018-11-12 02:00:00' 
    ON COMPLETION NOT PRESERVE ENABLE DO CALL pro_clear_data ();
    

    再次,读取分表数据的java的示例代码:
    主要思路:在查询fact_call等表前判断应该查哪个表查。
    核心代码:
    (1)通过时间范围确定表名

    	/**
    	 * 确定从哪张表中读取数据
    	 * @param decisionTime 这是sql中最小的的report_time
    	 *                     例如:select * from fact_call where report_time > '2018-10-06 17:32:59' and report_time < '2018-11-06 17:32:59'
    	 *                     或者:select * from fact_call where report_time between '2018-10-06 17:32:59' and '2018-11-06 17:32:59'
    	 *                     那么decisionTime应该是其中较小的值'2018-10-06 17:32:59'
    	 *                     注意:必须限制一次查询的最大时间跨度不超过3个月
    	 */
    	public String decisionTableName(String decisionTime) {
    		try {
    			if (null!=decisionTime && !"".equals(decisionTime)) {
    				long decision = sdf.parse(decisionTime).getTime();
    				Calendar calendar = Calendar.getInstance();
    				calendar.add(Calendar.MONTH, -3);
    				long before_3 = calendar.getTimeInMillis();
    				calendar.add(Calendar.MONTH, -3);
    				long before_6 = calendar.getTimeInMillis();
    				if (decision > before_3) {
    					return "fact_call";
    				} else if (decision > before_6) {
    					return "fact_call_6";
    				} else {
    					return "fact_call_all";
    				}
    			}
    		}catch (Exception e) {
    			e.printStackTrace();
    		}
    		return "fact_call_all";
    	}
    

    (2)mybatis的映射文件,对表名做判断:

        <select id="findAll" resultMap="base_result_map" parameterType="java.util.Map">
        SELECT
            *
        FROM
            <choose>
                <when test="tableName=='fact_call'">
                    fact_call f
                </when>
                <when test="tableName=='fact_call_6'">
                    fact_call_6 f
                </when>
                <otherwise>
                    fact_call_all f
                </otherwise>
            </choose>
        WHERE
            1=1
            <!-- and f.report_time &lt; #{begin_time} and f.report_time &gt; #{end_time}-->
            and f.report_time between #{begin_time} and #{end_time}
            limit #{index} , #{size}
        </select>
    

    最后,解释为什么这么做:
    优点:
    1、不需要分库(后面会介绍分库的架构)
    2、不依赖第三方程序(后面会介绍数据库中间件的架构)
    3、数据冗余尽量少(后面会介绍一主多从的架构)
    4、可靠性更高(后面会介绍使用mysql触发器做实时同步的架构)
    缺点:
    1、数据冗余为8个月数据(以空间换时间)
    2、需要开启mysql定时器功能(影响的性能很小,可忽略)
    3、对程序员不透明(但是程序员自己代码判断去哪个表中查,也很简单)
    4、单台数据库存在服务器io限制(我们公司的数据库查询慢的问题不在于服务器,在于单表过大)

    (PS:当然,也可以直接在mapper文件中,把表明当作变量,使用${}即可。由于表名参数不由前端传递,故不存在sql注入的风险)

    最终完美上线

    2 为什么不使用mycat、Kingshard、Sharding-JDBC

    1、为什么不使用mycat
    mycat
    (注:上图来源网络)
    mycat功能很强大,即支持分库也支持分表。支持取模、hash等不同的划分策略。但是存在三点问题:

    • 集群搭建过于复杂,运维成本高,如果不搭建mycat集群又会带来单点故障问题;
    • 我的需求对于最近数据和历史数据是不同的,历史数据可能1年也就查几次,慢点无所谓
    • 我必须要使用分页、排序等功能

    注:
    但是大后期,也就是过了很多年后,当我们公司有了自己的DBA,当我们的日数据量超过100W,应该还是会采用分库分表的办法。(为什么是100W呢?因为mysql innodb处理5000W数据量的单表速度勉强可以接受,5000W/60天,约等于100W/天)。我们公司还处于早中期阶段,没有DBA,公司想尽快提高数据库性能,又不想搞得太复杂。

    至于不使用Kingshard、Sharding-JDBC原因很多,一方面太麻烦了,Kingshard使用Go语言开发,难以维护。Sharding-JDBC对程序员不透明,我每个程序都要做一遍复杂的分库分表。
    另外,从需求的角度分析,我已经手动分表了,也不需要使用第三方来做读写分离。完全就没必要使用数据库中间件!

    3 为什么不使用一主多从

    一主多从的架构,如下图:
    在这里插入图片描述
    (上图的最近3月,改为最近2月。)
    上图是分库的,之前准备使用mybatis的多源数据库自动切换的办法,这样可以避免使用数据库中间件,也蛮简单的,在每次查询之前根据分库的key来切换数据源即可(感兴趣的可以自己搜索,我是已经实现过)。上图的好处是可以把数据库放在不同的服务器上,但是缺点是违反了主从原则,运维管理也很麻烦。后来被pass掉了。

    3 为什么不使用mysql触发器做实时同步

    mysql触发器实时同步的版本:
    在这里插入图片描述
    大家应该看出来了,终极版本中“需要限制查询最近的超过2个月的数据(如果要查询超过2个月的数据,则不能查今天的数据,因为今天的需要到晚上才能同步到fact_call_6表中)”,这个原因是因为,我做的不是实时同步,那么为什么不使用mysql触发器做实时同步,原因很简单,当数据库操作过于频繁时,mysql触发器不可靠!

    经过很久的while(true){分析、开会、讨论},最终得出来了我们的“终极版本”。最终版本虽说看起来很简单,也没有使用什么复杂的技术,但是能满足我们的需求,最快的提升数据库性能。就像:

    以前:
    客户:我想查下我今天的数据,为什么查不出来数据了,一查就报错。
    产品经理:没办法数据库量太大了。
    客户:什么?那也就是说我以后每天的数据都不能查了?
    产品经理说:那我把两年前的数据删了吧?
    客户:删数据?那怎么行,万一我们领导哪天要检查、核实、取证怎么办,不能删(其实他们永远不会去查很久以前的数据,他们只是一听到删他们的数据他们就很慌)。

    上线完毕,改动相对较小。只是多加了两张表、对于要查fact_call表的程序稍微做了修改。

    现在:
    客户:我今天的数据可以查,但是查以前的数据很慢,为什么?
    产品经理:谁叫你查以前的数据,查很久以前的数据,本来就慢…
    客户:哦,好吧…
    我:(哈哈哈哈)

    注意:mycat、Kingshard、Sharding-JDBC各有优势,都是非常优秀的开源产品,本文仅仅是个人看法。

    本文地址:https://blog.csdn.net/tiandixuanwuliang/article/details/84650061

    展开全文
  • 数据库如何分库分表

    万次阅读 2019-04-29 21:09:52
    刚开始我们只用单机数据库就够了,随后面对越来越多的请求,我们将数据库的写操作和读操作进行分离, 使用多个从副本(Slaver Replication)负责读,使用主库(Master)负责写, 从从主库同步更新数据,保持数据...
  • 数据库分库分表策略的具体实现方案

    万次阅读 多人点赞 2017-01-02 14:10:03
    1、 使用Spring AOP实现MySQL数据库读写分离案例分析 2、MySQL5.6 数据库主从(Master/Slave)同步安装与配置详解 3、MySQL主从复制的常见拓扑、原理分析以及如何提高主从复制的效率总结 4、使用mysqlreplicate...
  • 关于数据库的扩展主要包括:业务拆分、主从复制,数据库分库分表。这篇文章主要讲述数据库分库分表 (1)业务拆分 在 《大型网站应用之海量数据和高并发解决方案总结一二 》一篇文章中也具体讲述了为什么要对...
  • 怎么进行数据库分库分表

    千次阅读 2019-08-14 17:23:27
    当单表的数据量达到1000W或100G以后,由于查询维度较多,即使添加从、优化索引,做很多操作时性能仍下降严重。此时就要考虑对其进行切分了,切分的目的就在于减少数据库的负担,缩短查询时间。 数据库分布式核心...
  • 数据库分库分表学习整理

    千次阅读 2018-03-27 17:20:48
    关于数据库的学习,一个绕不过的东西就是数据库分库分表,还是有必要总结一下,以后工作的时候会用到,面试的时候也会用到的。 1. 什么是数据库分表 关于分库分表字面理解就把原本存储在一个库一个表中的数据...
  • 点击上方“朱小厮的博客”,选择“设为星标”后台回复”加群“加入公众号专属技术群来源:rrd.me/fpvFw一. 数据切分关系型数据库本身比较容易成为系统瓶颈,单机存储容量、连接数、处理...
  • 背景 关系型数据库本身比较容易成为系统瓶颈,单机存储容量、连接数、处理能力都有限。...垂直分表在日常开发和设计中比较常见,通俗的说法叫做“大表拆小表”,拆分是基于关系型数据库中的“列”(字段)进行的...
  • 水平切分分为库内分表和分库分表,是根据表内数据内在的逻辑关系,将同一个表按不同的条件分散到多个数据库或多个表中,每个表中只包含一部分数据,从而使得单个表的数据量变小,达到分布式的效果。如图所示: ...
  • 文章目录前言数据拆分垂直切分水平切分拆分阶段如何操作拆分的数据库(客户端分片)程序自定义规则实现通过ORM框架实现通过JDBC协议实现通过代理分片实现 ...此篇讲述如何通过分库分表设计解决以上问题。 一切技术都...
  • 点击▲关注 “数据和云” 给公众号标星置顶更多精彩 第一时间直达1.阿里巴巴分布式数据层发展和演变业务数据从原来的单单表模式变成了数据被拆分到多个数据库,甚至多个表...
  • 数据库分库分表方案

    2020-04-19 16:23:03
    数据库的拆分方式有两种:水平拆分和垂直拆分。水平拆分关注的单表数据量过大的问题,对于上千万过亿行数据的表来说,单表数据量过大,查询和变更的成本会变大,同时单的吞吐量也会达到瓶颈,会同时对业务造成影响...
  • 数据库分库分表)中间件对比 分区:对业务透明,分区只不过把存放数据的文件分成了许多小块,例如mysql中的一张表对应三个文件.MYD,MYI,frm。根据一定的规则把数据文件(MYD)和索引文件(MYI)进行了分割,分区后...
  • 在日常的工作中,关系型数据库本身比较容易成为系统的瓶颈点,虽然读写分离能分散数据库的读写压力,但并没有分散存储压力,当数据量达到千万甚至上亿时,单台数据库服务器的存储能力会成为系统的瓶颈,主要体现在...
  • 当单表的数据量达到1000W或100G以后,由于查询维度较多,即使添加从、优化索引,做很多操作时性能仍下降严重。此时就要考虑对其进行切分了,切分的目的就在于减少数据库的负担,缩短查询时间。 数据库分布式核心...
  • 浅谈数据库分库分表

    2020-07-09 18:15:46
    大多数关系型数据库采用B+数索引,在数据量查过阈值的情况下,索引深度增加也使得磁盘I/O次数增加,导致查询性能下降。 2、可用性 单一数据节点或者简单的主从结构已经难以满足大数据量请求的场景。 3、运维成本 ...
  • 一、分表解决的问题 分表后,单表的并发能力提高...二、常见分表分库常用策略: 1.平均进行分配hash(object)%N(适用于简单架构)。 2.按照权重进行分配且均匀轮询。 3.按照业务进行分配。 4.按照一致性hash算法进行...
  • 数据库分库分表思想1. 基本思想之什么是分库分表?从字面上简单理解,就是把原本存储于一个库的数据分块存储到多个库上,把原本存储于一个表的数据分块存储到多个表上。2. 基本思想之为什么要分库分表?数据库中的...
  • 数据库分库分表

    2017-09-07 14:20:22
    1. 数据库分库分表 1.1. 前言 1.1.1. 名词解释 1.2. 数据库架构演变1.3. 分库分表前的问题 1.3.1. 用户请求量太大1.3.2. 单库太大1.3.3. 单表太大 1.4. 分库分表的方式方法 1.4.1. 垂直拆分1.4.2. ...
  • 1、什么是分库分表 数据拆分是对数据进行分而治之的通用概念 垂直拆分:根据业务维度,将原本一个库(表)拆分为多个库(表),每个库(表)与原有的结构不同。例如将用户表和订单表分别存于两个不同的数据库中,...
  • 当单表的数据量达到1000W或100G以后,由于查询维度较多,即使添加从、优化索引,做很多操作时性能仍下降严重。此时就要考虑对其进行切分了,切分的目的就在于减少数据库的负担,缩短查询时间。 数据库分布式核心...
  • 数据库分库分表的面试连环炮 面试题 为什么要分库分表?(设计高并发系统的时候,数据库层面该如何设计) 用过哪些分库分表中间件? 不同的分库分表中间件都有什么优缺点? 你们具体是如何对数据库如何进行垂直拆分...
  • 不得已,分库分表提上日程,我们的目的很简单,减小数据库的压力,缩短表的操作时间。 二、如何进行数据切分 数据切分(Sharding),简单的来说,就是通过某种特定的条件,将存放在同一个数据库中的数据拆...
  • 数据库分库分表实现结构

    千次阅读 2016-11-25 17:17:21
    数据库分库分表实现结构,主要分为客户端和服务器端,文章简单介绍了各自的优缺点。

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 36,818
精华内容 14,727
关键字:

数据库分库分表管理