什么是分库分表？MySQL为什么要分库分表？

在文章开头先抛几个问题1什么时候才需要分库分表呢我们的评判标准是什么2一张表存储了多少数据的时候才需要考虑分库分表3数据增长速度很快每天产生多少数据才需要考虑做分库分表这些问题你都搞清楚了吗相信看完这篇文章会有答案。为什么要分库分表首先回答一下为什么要分库分表答案很简单数据库出现性能瓶颈。用大白话来说就是数据库快扛不住了。数据库出现性能瓶颈对外表现有几个方面大量请求阻塞在高并发场景下大量请求都需要操作数据库导致连接数不够了请求处于阻塞状态。SQL 操作变慢如果数据库中存在一张上亿数据量的表一条 SQL 没有命中索引会全表扫描这个查询耗时会非常久。存储出现问题业务量剧增单库数据量越来越大给存储造成巨大压力。从机器的角度看性能瓶颈无非就是CPU、内存、磁盘、网络这些要解决性能瓶颈最简单粗暴的办法就是提升机器性能但是通过这种方法成本和收益投入比往往又太高了不划算所以重点还是要从软件角度入手。数据库相关优化方案数据库优化方案很多主要分为两大类软件层面、硬件层面。软件层面包括SQL 调优、表结构优化、读写分离、数据库集群、分库分表等硬件层面主要是增加机器性能。SQL 调优SQL 调优往往是解决数据库问题的第一步往往投入少部分精力就能获得较大的收益。SQL 调优主要目的是尽可能的让那些慢 SQL 变快手段其实也很简单就是让 SQL 执行尽量命中索引。开启慢 SQL 记录如果你使用的是 Mysql需要在 Mysql 配置文件中配置几个参数即可。slow_query_logon long_query_time1 slow_query_log_file/path/to/log调优的工具常常会用到 explain 这个命令来查看 SQL 语句的执行计划通过观察执行结果很容易就知道该 SQL 语句是不是全表扫描、有没有命中索引。select id, age, gender from user where name 爱笑的架构师;返回有一列叫“type”常见取值有ALL、index、range、 ref、eq_ref、const、system、NULL从左到右性能从差到好ALL 代表这条 SQL 语句全表扫描了需要优化。一般来说需要达到range 级别及以上。表结构优化以一个场景举例说明“user”表中有 user_id、nickname 等字段“order”表中有order_id、user_id等字段如果想拿到用户昵称怎么办一般情况是通过 join 关联表操作在查询订单表时关联查询用户表从而获取导用户昵称。但是随着业务量增加订单表和用户表肯定也是暴增这时候通过两个表关联数据就比较费力了为了取一个昵称字段而不得不关联查询几十上百万的用户表其速度可想而知。这个时候可以尝试将 nickname 这个字段加到 order 表中order_id、user_id、nickname这种做法通常叫做数据库表冗余字段。这样做的好处展示订单列表时不需要再关联查询用户表了。冗余字段的做法也有一个弊端如果这个字段更新会同时涉及到多个表的更新因此在选择冗余字段时要尽量选择不经常更新的字段。架构优化当单台数据库实例扛不住我们可以增加实例组成集群对外服务。当发现读请求明显多于写请求时我们可以让主实例负责写从实例对外提供读的能力如果读实例压力依然很大可以在数据库前面加入缓存如 redis让请求优先从缓存取数据减少数据库访问。缓存分担了部分压力后数据库依然是瓶颈这个时候就可以考虑分库分表的方案了后面会详细介绍。硬件优化硬件成本非常高一般来说不可能遇到数据库性能瓶颈就去升级硬件。在前期业务量比较小的时候升级硬件数据库性能可以得到较大提升但是在后期升级硬件得到的收益就不那么明显了。分库分表详解下面我们以一个商城系统为例逐步讲解数据库是如何一步步演进。单应用单数据库在早期创业阶段想做一个商城系统基本就是一个系统包含多个基础功能模块最后打包成一个 war 包部署这就是典型的单体架构应用。商城项目使用单数据库如上图商城系统包括主页 Portal 模板、用户模块、订单模块、库存模块等所有的模块都共有一个数据库通常数据库中有非常多的表。因为用户量不大这样的架构在早期完全适用开发者可以拿着 demo到处找骗投资人。一旦拿到投资人的钱业务就要开始大规模推广同时系统架构也要匹配业务的快速发展。多应用单数据库在前期为了抢占市场这一套系统不停地迭代更新代码量越来越大架构也变得越来越臃肿现在随着系统访问压力逐渐增加系统拆分就势在必行了。为了保证业务平滑系统架构重构也是分了几个阶段进行。第一个阶段将商城系统单体架构按照功能模块拆分为子服务比如Portal 服务、用户服务、订单服务、库存服务等。多应用单数据库如上图多个服务共享一个数据库这样做的目的是底层数据库访问逻辑可以不用动将影响降到最低。多应用多数据库随着业务推广力度加大数据库终于成为了瓶颈这个时候多个服务共享一个数据库基本不可行了。我们需要将每个服务相关的表拆出来单独建立一个数据库这其实就是“分库”了。单数据库的能够支撑的并发量是有限的拆成多个库可以使服务间不用竞争提升服务的性能。多应用多数据库如上图从一个大的数据中分出多个小的数据库每个服务都对应一个数据库这就是系统发展到一定阶段必要要做的“分库”操作。现在非常火的微服务架构也是一样的如果只拆分应用不拆分数据库不能解决根本问题整个系统也很容易达到瓶颈。分表说完了分库那什么时候分表呢如果系统处于高速发展阶段拿商城系统来说一天下单量可能几十万那数据库中的订单表增长就特别快增长到一定阶段数据库查询效率就会出现明显下降。因此当单表数据增量过快业界流传是超过500万的数据量就要考虑分表了。当然500万只是一个经验值大家可以根据实际情况做出决策。那如何分表呢分表有几个维度一是水平切分和垂直切分二是单库内分表和多库内分表。水平拆分和垂直拆分就拿用户表user来说表中有7个字段id,name,age,sex,nickname,description如果 nickname 和 description 不常用我们可以将其拆分为另外一张表用户详细信息表这样就由一张用户表拆分为了用户基本信息表用户详细信息表两张表结构不一样相互独立。但是从这个角度来看垂直拆分并没有从根本上解决单表数据量过大的问题因此我们还是需要做一次水平拆分。拆分表还有一种拆分方法比如表中有一万条数据我们拆分为两张表id 为奇数的1357……放在 user1 id 为偶数的2468……放在 user2中这样的拆分办法就是水平拆分了。水平拆分的方式也很多除了上面说的按照 id 拆表还可以按照时间维度取拆分比如订单表可以按每日、每月等进行拆分。每日表只存储当天的数据。每月表可以起一个定时任务将前一天的数据全部迁移到当月表。历史表同样可以用定时任务把时间超过 30 天的数据迁移到 history表。总结一下水平拆分和垂直拆分的特点垂直切分基于表或字段划分表结构不同。水平切分基于数据划分表结构相同数据不同。单库内拆分和多库拆分拿水平拆分为例每张表都拆分为了多个子表多个子表存在于同一数据库中。比如下面用户表拆分为用户1表、用户2表。单库拆分在一个数据库中将一张表拆分为几个子表在一定程度上可以解决单表查询性能的问题但是也会遇到一个问题单数据库存储瓶颈。所以在业界用的更多的还是将子表拆分到多个数据库中。比如下图中用户表拆分为两个子表两个子表分别存在于不同的数据库中。多库拆分一句话总结分表主要是为了减少单张表的大小解决单表数据量带来的性能问题。分库分表带来的复杂性既然分库分表这么好那我们是不是在项目初期就应该采用这种方案呢不要激动冷静一下分库分表的确解决了很多问题但是也给系统带来了很多复杂性下面简要说一说。1跨库关联查询在单库未拆分表之前我们可以很方便使用 join 操作关联多张表查询数据但是经过分库分表后两张表可能都不在一个数据库中如何使用 join 呢有几种方案可以解决字段冗余把需要关联的字段放入主表中避免 join 操作数据抽象通过ETL等将数据汇合聚集生成新的表全局表比如一些基础表可以在每个数据库中都放一份应用层组装将基础数据查出来通过应用程序计算组装2分布式事务单数据库可以用本地事务搞定使用多数据库就只能通过分布式事务解决了。常用解决方案有基于可靠消息MQ的解决方案、两阶段事务提交、柔性事务等。3排序、分页、函数计算问题在使用 SQL 时 order by limit 等关键字需要特殊处理一般来说采用分片的思路先在每个分片上执行相应的函数然后将各个分片的结果集进行汇总和再次计算最终得到结果。4分布式 ID如果使用 Mysql 数据库在单库单表可以使用 id 自增作为主键分库分表了之后就不行了会出现id 重复。常用的分布式 ID 解决方案有UUID基于数据库自增单独维护一张 ID表号段模式Redis 缓存雪花算法Snowflake百度uid-generator美团Leaf滴滴Tinyid这些方案后面会写文章专门介绍这里不再展开。5多数据源分库分表之后可能会面临从多个数据库或多个子表中获取数据一般的解决思路有客户端适配和代理层适配。业界常用的中间件有shardingsphere前身 sharding-jdbcMycat总结如果出现数据库问题不要着急分库分表先看一下使用常规手段是否能够解决。分库分表会给系统带来巨大的复杂性不是万不得已建议不要提前使用。作为系统架构师可以让系统灵活性和可扩展性强但是不要过度设计和超前设计。在这一点上架构师一定要有前瞻性提前做好预判。大家学会了吗