MySQL 拆分数据库和表（何时？如何？）

一开始我们只使用一个数据库。后来请求越来越多，我们就将数据库的写操作和读操作分开，使用多个数据库。数据库的从副本（Slaver Replication）负责读取，主数据库（Master）负责写入。从库同步更新主库数据，保持数据一致性。从架构上来说，这就是主从数据库同步。子库可以水平扩展，因此多个读取请求不是问题。

但是当用户级别提高了，写请求越来越多的时候怎么办？添加master并不能解决问题，因为数据必须一致，而且写操作需要两个master之间同步，相当于重复，比较复杂。

上一期我们了解到：在正式介绍分库分表之前，我们需要先了解数据库的性能瓶颈在哪里？

数据库瓶颈

无论是IO还是CPU瓶颈，最终都会导致数据库的活跃连接数不断增加，接近甚至达到数据库能够处理的活跃连接数的阈值。从业务服务的角度来看，

意味着可用的数据库连接很少或没有，您可以想象以下情况（并发性、吞吐量、崩溃）。

IO瓶颈

• 第一类：磁盘读IO瓶颈，热数据太多，数据库缓存放不下，每次查询都会产生大量IO，降低查询速度 -> 分库和竖表
• 第二类：网络IO瓶颈，请求数据过多，网络带宽不足 -> 子数据库

CPU瓶颈

• 第一类：SQL问题：如果SQL中含有join、join by、order by，查询字段条件没有索引等，添加CPU运算->SQL优化，建立合适的索引，在业务服务层进行业务计算。
• 第二种：单表数据量过大，查询时检查行数过多，SQL性能低，CPU运算增加。 ->水平子表。

何时考虑拆分数据库和表

可以的话就不要拆分

不一定要拆分所有表，主要取决于数据增长的水平。细分后，业务的复杂度有所增加。除非绝对必要，否则不要使用分库分表这个“大招”，以避免“过度规划”和“过早优化”。在对数据库和表进行分区之前，尽可能地进行优化：升级硬件、升级网络、读写分离、索引优化等。当数据量达到单表瓶颈时，可以考虑对数据库和表进行分区。

数据量过大，正常运维影响业务访问

这里所说的运维是指：

1）对于数据库备份，如果单个表过大，备份时所需的磁盘IO和网络IO量较大。例如，如果通过网络传输1T数据，占用50MB，则传输需要20,000秒。整个过程风险比较高

2）当大表上的DDL发生变化时，MySQL会锁住整张表。这个时间会很长。期间公司无法访问这张表，影响巨大。如果您使用 pt-online-schema-change，触发器和影子表是动态创建的，这也需要很长时间。在此操作期间，被视为风险时间。拆分数据表并减少总数可以帮助降低这种风险。

3）大表访问和更新频繁，更容易出现锁等待。划分数据，以空间换时间，暗中降低访问压力

随着公司的发展，有些字段需要进行垂直划分

例如项目一开始创建的用户表如下：

id                  bigint     #用户的ID
name                varchar    #用户的名字
last_login_time     datetime   #最近登录时间
personal_info       text       #私人信息
.....                          #其他信息字段

在项目初期，这样的设计满足了简单的业务需求，并且能够快速迭代开发。随着业务的快速增长，用户数量从10万增长到10亿，用户非常活跃。每次登录都会更新last_login_name字段，导致用户表不断更新，压力很大。其他字段：id、name、personal_info 不变或很少更新。从业务角度来看，应该拆分last_login_time，并创建新的user_time表。

personal_info属性更新和查询频率较低，文本字段占用空间太大。这时候就需要对user_ext表进行垂直拆分。

数据量急剧增加

随着业务的快速发展，一张表的数据量会不断增加。当容量接近瓶颈时，需要考虑水平分区，创建分库分表。这时候就需要选择合适的切分规则并提前预估数据容量。垂直细分是在业务层面进行的，将不相关企业的数据库分开。由于每个公司的数据范围和访问范围不同，不能因为一家公司影响数据库而牵连到其他公司。当数据库出现问题时应用水平切片不会影响100%的用户。每个数据库只承载一部分业务数据，因此可以提高整体可用性。

分库分表方式

横向数据库

• 1.概念：将一个数据库中的数据按照字段、按照特定的策略（哈希、范围等）划分到多个数据库中。
• 2。结果：
  • 各个库的结构相同
  • 各个库中的数据不同且没有交集
  • 所有库中的数据组合起来就是数据总量
  • 3。场景：当系统绝对并发量增大时，通过表分区很难基本解决问题，且企业没有明显的归属感对数据库进行垂直分区。
• 4。分析：有了多个库，IO和CPU压力自然可以成倍降低

水平表分区

• 1。概念：在字段的基础上，按照一定的策略（哈希值、范围等），我们所说的是将一张表中的数据分为几张表。
• 2。结果：
  • 每个表的结构相同
  • 每个表的数据不同，没有交集，所有表的并集是全数据。
• 3。场景：系统绝对并发度没有增加，但单表数据量太大，影响SQL性能，增加CPU负载，成为瓶颈。可以考虑对表格进行水平划分。
• 4。分析：每张表的数据量减少了，每条SQL执行的效率高，自然就减少了CPU的负载。

垂直分区数据库

• 1。概念：在表的基础上，根据公司不同的所有权，将不同的表划分到不同的数据库中。
• 2。结果：
  • 每个库的结构不同
  • 每个库的数据也不同，没有交集
  • 所有库的总和就是数据总量
• 3。场景：系统绝对并发量增加，可以抽象出单独的业务模块。
• 4。分析：此时基本可以面向服务了。例如：随着业务的发展，公共配置表、字典表等越来越多。目前，这些表可以拆分为单独的库，甚至可以面向服务。此外，随着公司的发展和业务模式的形成，相关的表可以分为单独的库，甚至面向服务。

表格的垂直分布

• 1。概念：以字段为基础，根据字段的活动性，将表中的字段划分为不同的表（主表和扩展表）。
• 2。结果：
  • 每个表的结构都不同。
  • 每个表的数据都不一样。一般来说，每个表的字段至少有一个列交集，通常是主键，用于链接数据。
  • 所有表的并集就是数据总量。
• 3。场景：系统的绝对并发度没有增加。表中记录不多，但字段较多，热点数据和非热点数据在一起。每行数据所需的存储空间较大，因此减少数据库缓存。数据行数减少，查询回读磁盘数据时，会产生大量随机读IO，造成IO瓶颈。 。
• 4。分析：可以使用列表页和详情页，更容易理解。垂直表分区的原理是将热点数据（可以经常查询的数据）分组为主表，将非热点数据集中在一起作为扩展表，这样就可以缓存更多的热点数据，从而减少随机读IO 。如果想要获取分区后的所有数据，则需要将两个表进行join来获取数据。

但切记不要使用join，因为join不仅会增加CPU负载，还会将两个表链接在一起（必须在一个数据库实例上）。关联数据应该在服务层实现，从主表或扩展表中获取数据，然后使用关联字段获取所有数据。

Sharding-jdbc（当当）

Sharding-jdbc（当当）

分库分表带来的问题

分库分表表可以有效缓解单机一条表带的问题。会造成瓶颈和性能压力，突破网络IO、硬件资源和连接数的瓶颈，导致一些问题。下面将描述这些问题和解决方案。

事务一致性问题

分布式事务

当更新的内容同时存在于不同的库中时，不可避免地会出现跨数据库事务问题。分片之间的交易也是分布式交易，没有简单的解决方案。一般来说，可以使用“XA协议”和“两阶段确认”来处理。

分布式事务可以最大程度保证数据库操作的原子性。但提交事务时，必须协调多个节点，这会延迟事务提交时间点，增加事务执行时间，增加事务访问共享资源时发生冲突或死锁的可能性。随着数据库节点数量的增加，这种趋势会越来越严重，从而成为系统在数据库层面水平扩展的障碍。

可能的一致性

对于那些性能要求高但一致性要求不高的系统，实时的系统一致性往往是不必要的。只要在允许的时间内最终合规，就可以申请交易补偿。与立即回滚方法不同，当事务执行过程中发生错误时，事务补偿是事后审查和纠正措施。一些常见的实现方法包括：数据一致性检查、基于日志的比较以及与标准数据源的定期比较。同步等7 问题比较困难。出于性能原因，尽量避免使用 Join 查询。一些解决方案：

全局表

全局表也可以认为是“数据字典表”，它是系统中所有模块都可以依赖的一些表。为了避免库连接查询，您可以将此类表在每个数据库中存储一个副本。这些数据通常很少变化，因此无需担心一致性问题。

字段冗余

典型的反范式设计，以空间换时间，避免分组查询以提高效率。例如，当orders表存储userId时，它还存储userName的冗余副本。这样通过查询订单明细表就可以找到用户名userName，而不需要查询客户的user表。但该方法的应用场景也受到限制。比较适合依赖字段比较少，而且冗余字段的一致性也很难保证的情况。

数据的编译

在系统服务的业务层面，有两个查询。第一个查询的结果集找到相关数据ID，然后根据ID发起者通过第二次请求检索相关数据。最后将得到的结果进行现场安装。这是比较常用的方法。

ER 分片

如果在关系型数据库中定义了表之间的关系（例如订单表和订单明细表），并且具有相关关系的表记录存储在同一个分片上，则跨分片可以更好地避免分片问题，并且可以在单个分片内进行合并。在1:1或1:n的情况下，主键分段通常是根据主表的ID来进行的。 这样数据节点1上的订单明细表和订单明细表就可以通过订单ID与查询部分关联起来，数据节点2上也是如此。

分页、排序、跨节点功能问题

跨多个节点、多个数据库查询可能会导致分页限制、排序顺序等问题。分页应按指定字段排序。如果排序字段是分页字段，则使用分片规则更容易找到给定的分片；当排序字段不是分片字段时，就变得比较复杂。

首先要在不同的分片节点中对数据进行排序返回，然后将不同分片返回的结果集重新汇总排序，最后返回给用户如下图： 上图仅包含第一页的数据。对性能的影响尚不显着。然而，如果检索的页面数量非常大，情况就会变得更加复杂，因为粒子每个节点中的数据可能是随机的。为了排序的准确性，必须对所有节点的前N个数据页进行排序并合并。最后，整个排序就完成了。该操作会消耗CPU和内存资源，因此页面数越高，系统性能越差。

使用Max、Min、Sum、Count等函数进行计算时，也必须先对各段执行相应的函数，然后将各段的结果集相加，重新计算。

全局主键规避问题

在分库分表环境下，由于表中的数据同时存在于不同的数据库中，因此主键值使用的自增将无用武之地，并且某个分区数据库无法自动创建ID。确保全球唯一性。因此，应该单独设计全局主键，避免数据库中出现重复的主键。以下是一些策略：

UUID

标准的UUID格式是32个十六进制数字，分为5段，36个字符，形式为8-4-4-4-12。

UUID 是最简单的解决方案。它是本地创建的，性能高，不需要网络时间。但它有明显的缺点，占用大量存储空间。此外，建立索引作为主键并针对索引进行查询会存在性能问题，尤其是在 InnoDb 引擎下。 ，UUID扰动会导致索引位置频繁变化，导致分页。

与数据库结合维护主键ID表

在数据库中创建序列表：

CREATE TABLE `sequence` (  
      `id` bigint(20) unsigned NOT NULL auto_increment,  
      `stub` char(1) NOT NULL default '',  
      PRIMARY KEY  (`id`),  
      UNIQUE KEY `stub` (`stub`)  
    ) ENGINE=MyISAM;

存根字段设置为唯一索引。相同的存根值在序列表中只有一条记录，可以同时作为多个表的全局ID。序列表的内容如下：

+-------------------+------+ 
| id               | stub |  
+-------------------+------+ 
| 72157623227190423 |    a |  
+-------------------+------+

使用MyISAM机制代替InnoDb可以提供更好的性能。 MyISAM使用表锁，表的读写是顺序的，所以不必担心并发时两次读取同一个ID。当需要全局唯一ID时，实现：

REPLACE INTO sequence (stub) VALUES ('a');  
    SELECT 1561439;  

这种方案比较简单，但缺点也很明显：存在单点问题，数据库依赖性强。当DB异常时，整个系统不可用。配置master和slave可以提高可用性。另外，性能瓶颈仅限于单个MySQL的读写性能。

还有一种类似于序列表解决方案的主键生成策略，可以更好地解决单点和性能瓶颈问题。该方案的总体思路是，设置两台以上的服务器来生成全局ID，每台服务器上只部署一个数据库，并且每个数据库都有一个序列表来记录当前的全局ID。

表中的增长步长是库的数量，初始值按顺序排列，这样可以在每个库中浓缩ID的生成！ 该方案将ID生成压力均匀分布在两台机器上。该机器还提供系统容错功能。如果第一台机器出现错误，它可以自动切换到第二台机器来检索ID。但也有几个缺点：系统中添加机器时横向扩展比较复杂；每次获取ID都要读取DB，对DB的压力还是很大的。只有使用堆机才能提高效率。 ？掉下来的41位是毫秒级的时间，41位的长度可以代表69年的时间

数据迁移和扩散问题

当业务快速增长并面临运营和存储瓶颈时，需要考虑分布式设计。这时候就不可避免的要考虑历史数据的迁移。一般的做法是先读取历史数据，然后按照一定的分布规则将数据写入分片的各个节点。另外，需要根据当前的数据量、QPS、业务发展速度进行容量规划，计算出大概需要的分片数量（一般建议一个分片一张表的数据量不超过1000W） ）。 ？设计全破了。分库分表之前，必须要安排好业务场景的需求：

• 用户侧：前端访问，访问量大，必须保证高可用性和一致性。请求主要有两类：
  • 用户登录：通过登录名/电话/邮箱查询用户数据，1%的请求属于此类
  • 用户数据查询：登录后通过uid查询用户数据，99%请求就属于这种类型。此类
• 操作页面：后台访问，支持操作需求，根据年龄、性别、登录时间、注册时间等进行分页查询。它是一个内部系统，访问量较小，可用性和一致性要求较低。

水平切分方法

随着数据量越来越大，数据库需要进行水平切分。上述分割方法包括“基于数值范围”和“基于数值模数”。

“基于数值范围”：基于主键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的查询方法

水平切分后，可以很好地满足uid查询的需求，可以直接定向到特定的数据库。对于像login_name这样的非基于uid的查询，不知道要访问哪个库。在这种情况下，必须扫描所有库，性能将大大降低。

对于用户侧，可以采用“建立非uid属性到uid的映射关系”的方案；对于操作端，可以采用“前后分离”的解决方案。

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

1）映射关系

例如：login_name无法直接在数据库中找到。您可以使用索引表或存储缓存建立映射关系login_name→uid 。访问login_name时，首先通过映射表查找login_name对应的uid，然后通过uid找到具体的库。

映射表只有两列，可以包含大量数据。当数据量太大时，还可以对映射表进行水平分割。这种kv格式的索引结构可以利用缓存来优化查询性能，而且映射比不会频繁变化，缓存命中率会很高。

2）基因法

基因破坏：如果按uid将文库分为8个文库，则使用uid%8进行路由。目前，uid 的最后 3 位指定该行的特定用户数据。无论它属于哪个库，那么我们都可以将这3位视为子库基因。

上述映射关系方法需要额外的映射表存储。查询非 uid 字段需要额外的数据库或缓存访问权限。要删除多余的存储和查询，可以使用f函数将login_name gen作为uid子库gen。生成uid时，参考上面介绍的分布式唯一ID生成方案，最后3位值=f(login_name)。查询login_name时，只需计算f(login_name)%8的值即可找到具体的库。但这需要提前做好容量规划，预估未来几年需要将数据量拆分成多少个数据库，并预留一定数量的数据库基因位。

前后端分离

对用户页面的主要要求是专注于单行查询。需要建立一个从login_name/phone/email到uid的映射关系，可以解决这些字段的查询问题。

在性能方面，有很多带有批量分页和各种条件的查询。此类查询需要计算量大、返回数据量大、消耗数据库容量高。如果与用户侧共享同一个服务包或数据库，少量的后台请求就会占用大量的数据库资源，导致用户侧访问性能下降或超时。

对于此类业务，最好采用“前后端分离”的方案。运营侧后端业务抽象出独立的服务和DB，解决与前端业务系统的集成。由于运营方没有高可用性和一致性要求，因此不需要实时访问库。相反，它可以通过访问binlog将数据异步同步到操作库。当数据量较大时，还可以使用ES搜索引擎或Hive在后台完成复杂的查询方法。 来源：cnblogs.com/butterfly100/p/9034281.html