52.mysql优化-分库分表

1.分库分表的时机

1.0为什么要进行分库分表

当MySQL单表数据量过大，比如超过5千万条的时候，读写性能变得很差。而且常规的优化手段已经不起作用了。

比如：SQL调优、添加索引、主从复制、读写分离。

数据库出现性能瓶颈，对外表现有几个方面：

• 大量请求阻塞

  在高并发场景下，大量请求都需要操作数据库，导致连接数不够了，请求处于阻塞状态。单实例连接数有限。

• SQL 操作变慢

  如果数据库中存在一张上亿数据量的表，一条 SQL 没有命中索引会全表扫描，这个查询耗时会非常久。

• 存储出现问题

  业务量剧增，单库数据量越来越大，给存储造成巨大压力。

从机器的角度看，性能瓶颈无非就是 CPU、内存、磁盘、网络这些。

要解决性能瓶颈最简单粗暴的办法就是提升机器性能，但是通过这种方法成本和收益投入比往往又太高了，不划算。

所以重点还是要从软件角度入手。

这时候就需要用到MySQL终极优化方案 — 分库分表。

1.1数据库相关优化方案

数据库优化方案很多，主要分为两大类：软件层面、硬件层面。

软件层面包括：SQL 调优、表结构优化、读写分离、数据库集群、分库分表等；

硬件层面主要是增加机器性能。

SQL 调优

SQL 调优往往是解决数据库问题的第一步，往往投入少部分精力就能获得较大的收益。

SQL 调优主要目的是尽可能地让那些慢 SQL 变快，手段其实也很简单，就是让 SQL 执行尽量命中索引。

开启慢 SQL 记录

如果你使用的是 Mysql，只需要在 Mysql 配置文件中配置几个参数即可。

slow_query_log=on long_query_time=1 slow_query_log_file=/path/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”表中有 userid、nickname 等字段，“order”表中有 orderid、user_id 等字段，如果想拿到用户昵称怎么办？一般情况是通过 join 关联表操作，在查询订单表时关联查询用户表，从而获取到用户昵称。

但是随着业务量增加，订单表和用户表肯定也是暴增，这时候通过两个表关联数据就比较费力了，为了取一个昵称字段而不得不关联查询几十上百万的用户表，其速度可想而知。

这个时候可以尝试将 nickname 这个字段加到 order 表中(orderid、userid、nickname)，这种做法通常叫做数据库表冗余字段。这样做的好处是展示订单列表时不需要再关联查询用户表了。

冗余字段的做法也有一个弊端，如果这个字段更新会同时涉及到多个表的更新，因此在选择冗余字段时要尽量选择不经常更新的字段。

架构优化

当单台数据库实例扛不住，我们可以增加实例组成集群对外服务。

当发现读请求明显多于写请求时，我们可以让主实例负责写，从实例对外提供读的能力；

如果读实例压力依然很大，可以在数据库前面加入缓存如 redis，让请求优先从缓存取数据减少数据库访问。

缓存分担了部分压力后，数据库依然是瓶颈，这个时候就可以考虑分库分表的方案了。

硬件优化

硬件成本非常高，一般来说不可能一遇到数据库性能瓶颈就去升级硬件。

在前期业务量比较小的时候，升级硬件数据库性能可以得到较大提升；

但是在后期，升级硬件得到的收益就不那么明显了,通过这种方法成本和收益投入比往往又太高了，不划算。

1.2分库还是分表

• 当数据库的QPS过高，数据库连接数不足的时候，就需要分库。(比如一个库的连接数支持2K，分成两个库就能支持4K)
• 微服务架构中，为了业务隔离，一般也做分库处理。
• 当单表数据量过大，读写性能较差，就需要分表。
• 当两者都有的时候，就需要分库分表。

1.3分库分表优先级

先分库还是先分表

建议先分表，如果分表能解决问题，就不需要分库了，毕竟需要单独服务器资源，成本更高。

先垂直还是先水平

分库分表的顺序应该是先垂直分,后水平分。

先垂直分库还是分表

先垂直分表，再垂直分库。

思考:分库分表是先垂直还是先水平 是先分库还是先分表

以微服务为例,是先分布式还是先集群呢？

答案显而易见,肯定是先分布式在集群,那就是先垂直在水平那么是先分库还是先分表呢？

当然是先分表在分库。

分表的意义在于冷热数据隔离,分库的意义在于提高连接数 减少单表数量过大的问题。

以订单表为例,先做垂直分表,将订单表拆分成order 和 order_detail。

随着业务发展,订单表有1亿数据,虽然现在拆分成order 和 order_detail，但是再怎么拆分这2个表的基础数据都是1个亿。

原因在于当单表数量很大,即使再怎么分表也没办法解决单表膨胀的问题，此时就要做垂直分库了。

先水平分库还是分表

再水平分库，最后水平分表。

1.4分库分表拆分方案

分库分表有垂直拆分和水平拆分，垂直拆分又有垂直分库、垂直分表。

• 垂直分库：不同的业务拆分到不同的数据库(比如微服务分库)。
• 水平分库：单张表QPS过高，把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。解决了单库大数据，高并发的性能瓶颈
• 垂直分表：把长度较大或者访问频次较低的字段，拆分到扩展表中。
• 水平分表：单表数据量过大时，按照订单ID拆分到多张表中。

总结一下水平拆分和垂直拆分的特点：

• 垂直拆分：基于表或字段划分，表结构不同。
• 水平拆分：基于数据划分，表结构相同，数据不同。

垂直就是把1个分成N个,水平是把1个复制成N个。垂直对应分布式,水平对应集群。

在水平方向(即数据方向)上，分库和分表的作用，其实是有区别的，不能混为一谈。

• 水平分库：是为了解决数据库连接资源不足问题，和磁盘IO的性能瓶颈问题。
• 水平分表：是为了解决单表数据量太大，sql语句查询数据时，即使走了索引也非常耗时问题。此外还可以解决消耗cpu资源问题。
• 分库分表：可以解决 数据库连接资源不足、磁盘IO的性能瓶颈、检索数据耗时 和 消耗cpu资源等问题。

在垂直方向(即数据方向)上，分库和分表的作用，其实是有区别的，不能混为一谈。

• 垂直分库：是为了解决数据库连接资源不足问题，和磁盘IO的性能瓶颈问题。
• 垂直分表：冷热数据隔离

比如[用户表]包含了几十甚至上百个字段，管理起来有点混乱。这时候该怎么办呢？

答：将用户表拆分为：用户基本信息表 和 用户扩展表。

用户基本信息表中存的是用户最主要的信息，比如：用户名、密码、别名、手机号、邮箱、年龄、性别等核心数据。

这些信息跟用户息息相关，查询的频次非常高。

而用户扩展表中存的是用户的扩展信息，比如：所属单位、户口所在地、所在城市等等，非核心数据。

这些信息只有在特定的业务场景才需要查询，而绝大数业务场景是不需要的。

所以通过分表把核心数据和非核心数据分开，让表的结构更清晰，职责更单一，更便于维护。

除了按实际业务分表之外，我们还有一个常用的分表原则是：把调用频次高的放在一张表，调用频次低的放在另一张表。

有个非常经典的例子就是：订单表和订单详情表。

作者：苏三说技术 链接：https://www.zhihu.com/question/504073089/answer/2683960619

2.分库分表的缺点及解决方案

2.0 优点

垂直拆分优点： 1.跟随业务进行分割，和最近流行的微服务概念相似，方便解耦之后的管理及扩展。 2.高并发的场景下，垂直拆分使用多台服务器的 CPU、I/O、内存能提升性能，同时对单机数据库连接数、一些资源限制也得到了提升。 3.能实现冷热数据的分离。

水平拆分的优点 水平扩展能无线扩展。不存在某个库某个表过大的情况。 能够较好的应对高并发，同时可以将热点数据打散。 应用侧的改动较小，不需要根据业务来拆分。

2.1 缺点

• 垂直分库： 不同库多表之间无法join关联查询，只能通过接口聚合(MySQL有另外一种方式：MySQL跨库查询)，复杂度直线上升。 横跨多个数据库导致无法使用本地事务，数据强一致性就别想了，只能引入更为复杂的分布式事务，勉强实现数据的最终一致性，可用性直线下降。
• 垂直分表： 本来一张表能查出来的数据，现在需要多张表join关联查询，增加复杂度。
• 水平分表： 多张表关联查询时，无法实现分页、排序功能。

2.2 解决方案

2.2.1跨库查询问题

在单库单表的情况下，联合查询是非常容易的。但是，随着分库与分表的演变，联合查询就遇到跨库关联的问题。粗略的解决方法：ER 分片：子表的记录与所关联的父表记录存放在同一个数据分片上。

全局表：基础数据，所有库都拷贝一份。

ShareJoin：是一个简单的跨分片 join，目前支持 2 个表的 join,原理就是解析 SQL 语句，拆分成单表的 SQL 语句执行，然后把各个节点的数据汇集。

字段冗余：这样有些字段就不用 join 去查询了。

采用字段冗余方案，比如订单表存储店铺ID、店铺名称，就不需要再查询商户数据库了。

不过这种方案要求冗余字段要很少变动，就算变动后，也能容忍返回旧数据。

比如订单里冗余商户名称,商户名称即使从A变成了B,也不影响订单详情的展示。

2.2.2多表分页查询问题

一般情况下，列表分页时需要按照指定字段进行排序。在单库单表的情况下，分页和排序也是非常容易的。但是，随着分库与分表的演变，也会遇到跨库排序和跨表排序问题。为了最终结果的准确性，需要在不同的分表中将数据进行排序并返回，并将不同分表返回的结果集进行汇总和再次排序，最后再返回给用户。

这个处理起来就很需要技术含量了，比如：订单表按照订单ID分片，(order_id % 128)，分成了128张表。同一个用户的订单散落在不同的表，用户想查询自己的订单，根本无法做到分页查询。难道一次全部查询该用户的所有订单，然后做内存分页，多大的机器内存都让你搞挂。

想要实现用户订单分页查询，可以采用按照用户ID分片，(user_id % 128)，这样同一个用户的订单只会存储在一张表中，咋分页展示都行。

2.2.3商户分页查看店铺的订单

那就把订单再冗余存储一份，按照店铺ID分片，(shop_id % 128)。不过由于商户数量较少，可以搞个异步线程往商户订单分片表同步。

2.2.4订单按userId分片,数据倾斜

因为不同用户的订单量是不同的，一个爱好购物的小姐姐的订单量抵得上几十个老爷们，导致一张表数据几百条，另一张表数据量千万级，这该咋整？

做冷热数据分离，基础库只存储3个月内的订单，其他的移动到历史订单库。这个要跟产品商量好，3个月前的订单需要单独的查询页面。

2.2.5切分原则

由于数据切分后数据 Join 的难度，在此也分享一下数据切分的经验：

第一原则：能不切分尽量不要切分。

第二原则：如果要切分一定要选择合适的切分规则，提前规划好。

第三原则：数据切分尽量通过数据冗余或表分组(Table Group)来降低跨库 Join 的可能。

第四原则：由于数据库中间件对数据 Join 实现的优劣难以把握，而且实现高性能难度极大，业务读取尽量少使用多表 Join。

参考地址:

https://blog.csdn.net/YoungJ_Zhou/article/details/125599169

https://blog.csdn.net/weixin_41645135/article/details/122748614

3.分库分表规则

• RANGE：从0到10000一个表，10001到20000一个表；
• HASH取模：一个商场系统，一般都是将用户，订单作为主表，然后将和它们相关的作为附表，这样不会造成跨库事务之类的问题。 取用户id，然后hash取模，分配到不同的数据库上。
• 地理区域：比如按照华东，华南，华北这样来区分业务
• 时间：按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。

4.多套数据源

https://mp.weixin.qq.com/s/0J-FLYScYtEMnj0vZToX7g

https://juejin.cn/post/6844903661655572494

优点

• 简单、直接：一个库对应一套处理方式，很好理解。
• 符合开闭原则( OCP )：开发的设计模式告诉我们，对扩展开放，对修改关闭，添加多一个数据库，原来的那一套不需要改动，只添加即可。

缺点

• 资源浪费：针对每一个数据源写一套操作，连接数据库的资源也是独立的，分别占用同样多的资源。SqlSessionFactory 是一个工厂，建议是使用单例，完全可以重用，不需要建立多个，只需要更改数据源即可，跟多线程，使用线程池减少资源消耗是同一道理。
• 代码冗余：在前面的多数据源配置中可以看出，其实 master 和 slave 的很多操作是一样的，只是改个名称而已，因此会造成代码冗余。
• 缺乏灵活：所有需要使用的地方都需要引入对应的 mapper，对于很多操作，只是选择数据源的不一样，代码逻辑是一致的。另外，对于一主多从的情况，若需要对多个从库进行负载均衡，相对比较麻烦。
• 共配系统300+库使用多套数据源实现是不现实的

正因为有上述的缺点，所以还有改进的空间。于是就有了动态数据源，至于动态数据源如何实现，下回分解。