架构设计之数据库设计

背景

• IO瓶颈
  • 第一种：磁盘读 IO 瓶颈，热点数据太多，数据库缓存放不下，每次查询会产生大量的 IO，降低查询速度→分库和垂直分表。
  • 第二种：网络 IO 瓶颈，请求的数据太多，网络带宽不够→分库。
• CPU瓶颈
  • 第一种：SQL 问题：如 SQL 中包含 join，group by，order by，非索引字段条件查询等，增加 CPU 运算的操作→SQL 优化，建立合适的索引，在业务 Service 层进行业务计算。
  • 第二种：单表数据量太大，查询时扫描的行太多，SQL 效率低，增加 CPU 运算的操作→水平分表。

1. SQL优化

2. 表结构建设

3. 分库分表

什么时候考虑分库分表

• 能不分就不分

  并不是所有表都需要切分，主要还是看数据的增长速度。切分后在某种程度上提升了业务的复杂程度。不到万不得已不要轻易使用分库分表这个“大招”，避免“过度设计”和“过早优化”。

  分库分表之前，先尽力做力所能及的优化：升级硬件、升级网络、读写分离、索引优化等。当数据量达到单表瓶颈后，在考虑分库分表。在系统设计阶段就应该根据业务耦合松紧来确定垂直分库，垂直分表方案，在数据量及访问压力不是特别大的情况，首先考虑缓冲、读写分离、索引技术等方案。若数据量极大，且持续增长，再考虑水平分库水平分表方案。

• 数据量过大，正常运维影响业务访问，这里的运维是指：

  • 对数据库备份，如果单表太大，备份时需要大量的磁盘 IO 和网络 IO。
  • 对一个很大的表做 DDL，数据库会锁住整个表，这个时间会很长，这段时间业务不能访问此表，影响很大。
  • 大表经常访问和更新，就更有可能出现锁等待。

• 随着业务发展，需要对某些字段垂直拆分

  在实际业务中都可能会碰到，有些不经常访问或者更新频率低的字段应该从大表中分离出去。

• 数据量快速增长

  随着业务的快速发展，单表中的数据量会持续增长，当性能接近瓶颈时，就需要考虑水平切分，做分库分表了。

3.1 分库

3.1.1 读写分离（一主多从）

3.1.2 水平分库

以字段为依据，按照一定策略（hash、range等（参考二）），将一个库中的数据拆分到多个库中。

• 结果：

  • 每个库的结构都一样
  • 每个库中的数据不一样，没有交集
  • 所有库的数据并集是全量数据

• 适用场景

  系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库的情况下。

  库多了，IO 和 CPU 的压力自然可以成倍缓解。

3.1.3 垂直分库

以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。 达到专库专用的作用。

• 结果：

  • 每个库的结构都不一样。
  • 每个库的数据也不一样，没有交集。
  • 所有库的并集是全量数据。

• 适用场景

  系统绝对并发量上来了，并且可以抽象出单独的业务模块的情况下。

  到这一步，基本上就可以服务化(专库专用，接口调用方式)了。例如：随着业务的发展，一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。

  再者，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。

3.2 分表

3.2.1 垂直分表

以字段为依据，按照字段的活跃性，将表中字段拆到不同的表中（主表和扩展表）。 （参考一）

• 结果

  • 每个表的结构不一样。
  • 每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据。
  • 所有表的并集是全量数据。

• 适用场景

  系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大，以至于数据库缓存的数据行减少，查询时回去读磁盘数据产生大量随机读 IO，产生 IO 瓶颈。

  可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能经常会查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表，这样更多的热点数据就能被缓存下来，进而减少了随机读 IO。

  拆了之后，要想获取全部数据就需要关联两个表来取数据。但记住千万别用 Join，因为 Join 不仅会增加 CPU 负担并且会将两个表耦合在一起（必须在一个数据库实例上）。

  关联数据应该在 Service 层进行，分别获取主表和扩展表的数据，然后用关联字段关联得到全部数据。

3.2.2 水平分表

以字段为依据，按照一定策略（hash、range 等），将一个表中的数据拆分到多个表中。 单表数据控制在500万。

• 结果：

  • 每个表的结构都一样。
  • 每个表的数据不一样，没有交集，所有表的并集是全量数据。

• 适用场景

  系统绝对并发量没有上来，只是单表的数据量太多，影响了 SQL 效率，加重了 CPU 负担，以至于成为瓶颈，可以考虑水平分表。

  单表的数据量少了，单次执行 SQL 执行效率高了，自然减轻了 CPU 的负担。

• 常用分表维度

  • 自增ID
  • 省份维度
  • 年份维度：当前年度数据为一张表，最近三年的数据为一张表，归档数据为一张表。

3.3 常用分库分表工具

常用的分库分表工具如下： 各种工具的利弊自查

• Sharding-JDBC（当当）
• TSharding（蘑菇街）
• Atlas（奇虎 360）
• Cobar（阿里巴巴）
• MyCAT（基于 Cobar）
• Oceanus（58 同城）
• Vitess（谷歌）

3.4 分库分表引发的问题

• 分页查询
• 计算总数、分组函数等其他函数的使用

3.4.1 事务一致性问题

• 分布式事务

  当更新内容同时存在于不同库找那个，不可避免会带来跨库事务问题。跨分片事务也是分布式事务，一般可使用“XA 协议”和“两阶段提交”处理。

  分布式事务能最大限度保证了数据库操作的原子性。但在提交事务时需要协调多个节点，推后了提交事务的时间点，延长了事务的执行时间，导致事务在访问共享资源时发生冲突或死锁的概率增高。

  随着数据库节点的增多，这种趋势会越来越严重，从而成为系统在数据库层面上水平扩展的枷锁。

• 最终一致性

  对于那些性能要求很高，但对一致性要求不高的系统，往往不苛求系统的实时一致性，只要在允许的时间段内达到最终一致性即可，可采用事务补偿的方式。

  与事务在执行中发生错误立刻回滚的方式不同，事务补偿是一种事后检查补救的措施，一些常见的实现方法有：对数据进行对账检查，基于日志进行对比，定期同标准数据来源进行同步等。

3.4.2 关联查询问题

• 使用全局表（或者建立读库）

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

  案例：用中间表（ 关系数据写ES、Hbase更好 ）（参考三）

  假设订单表有32个数据库，每个库有32个表；

  1. 每个订单指定唯一的订单号，订单号哈希除以32取余，存到相应的库和表；
  2. 用户端，建立user_order中间表，根据用户ID分表，此表字段：用户ID，订单号，时间。用户查询时，在此表查询分页排序，再根据订单号去相应的订单表查询详情；
  3. 商户端，建立merchant_order中间表，字段：商家ID，订单号，时间。商家查询时，查此表分页排序，获取订单号再去相应的订单表查询；
  4. 站点运营人员，如果想根据商户查，就用3，如果像根据用户查，就用2，如果想要全部订单按照时间排序，也根据时间建立中间表time_order，字段：时间，订单号，在这里查好后再根据订单号去查订单详情。

• 字段冗余

  一种典型的反范式设计，利用空间换时间，为了性能而避免 Join 查询。

  例如，订单表在保存 userId 的时候，也将 userName 也冗余的保存一份，这样查询订单详情顺表就可以查到用户名 userName，就不用查询买家 user 表了。

  但这种方法适用场景也有限，比较适用依赖字段比较少的情况，而冗余字段的一致性也较难保证。

• 数据组装（比较常用的方法）

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

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

• 分页需要按照指定字段进行排序，当排序字段就是分片字段时，通过分片规则就比较容易定位到指定的分片；

• 当排序字段非分片字段时 ，通过二次查询的方式进行查询（子集查询）

  先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序。

  取第一页的数据，对性能影响还不是很大。但是如果取得页数很大，情况就变得复杂的多。

  因为各分片节点中的数据可能是随机的，为了排序的准确性，需要将所有节点的前N页数据都排序好做合并，最后再进行整体排序，这样的操作很耗费 CPU 和内存资源，所以页数越大，系统性能就会越差。

  在使用 Max、Min、Sum、Count 之类的函数进行计算的时候，也需要先在每个分片上执行相应的函数，然后将各个分片的结果集进行汇总再次计算。

3.4.4 全局主键避重问题

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

• UUID

  UUID 是最简单的方案，本地生成，性能高，没有网络耗时，但是缺点明显，占用存储空间多。

  另外作为主键建立索引和基于索引进行查询都存在性能问题，尤其是 InnoDb 引擎下，UUID 的无序性会导致索引位置频繁变动，导致分页。

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

  在数据库中建立 sequence 表：

  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;
  

  stub 字段设置为唯一索引，同一 stub 值在 sequence 表中只有一条记录，可以同时为多张表生辰全局 ID。

  使用 MyISAM 引擎而不是 InnoDb，已获得更高的性能。MyISAM 使用的是表锁，对表的读写是串行的，所以不用担心并发时两次读取同一个 ID。

  当需要全局唯一的 ID 时，执行：

  REPLACE INTO sequence (stub) VALUES (‘a‘);  
  SELECT LAST_INSERT_ID();  
  

  此方案较为简单，但缺点较为明显：存在单点问题，强依赖 DB，当 DB 异常时，整个系统不可用。配置主从可以增加可用性。另外性能瓶颈限制在单台 MySQL 的读写性能。

• 结合数据库维护主键ID表（升级版）

  建立 2 个以上的全局 ID 生成的服务器，每个服务器上只部署一个数据库，每个库有一张 sequence 表用于记录当前全局 ID。

  表中增长的步长是库的数量，起始值依次错开，这样就能将 ID 的生成散列到各个数据库上（例：两台机器，第一台从1开始，第二台从2开始，增长步长为2）。

  这种方案将生成 ID 的压力均匀分布在两台机器上，同时提供了系统容错，第一台出现了错误，可以自动切换到第二台获取 ID。

  但有几个缺点：系统添加机器，水平扩展较复杂；每次获取 ID 都要读取一次 DB，DB 的压力还是很大，只能通过堆机器来提升性能。

• Snowflake 分布式自增 ID 算法

  Twitter 的 Snowfalke 算法解决了分布式系统生成全局 ID 的需求，生成 64 位 Long 型数字。

  组成部分如下：

  • 第一位未使用。
  • 接下来的 41 位是毫秒级时间，41 位的长度可以表示 69 年的时间。
  • 5 位 datacenterId，5 位 workerId。10 位长度最多支持部署 1024 个节点。
  • 最后 12 位是毫秒内计数，12 位的计数顺序号支持每个节点每毫秒产生 4096 个 ID 序列。

• 使用Redis的incr操作

3.4.5 数据迁移、扩容问题

3.4.6 小结

• 垂直分表 ：可以把一个宽表的字段按访问频次，是否是大字段的原则拆分为多个表，这样既能使业务清晰，还能提升部分性能。拆分后，尽量从业务角度避免联查，否则性能方面将得不偿失。
• 垂直分库 ：可以把多个表按业务耦合松紧归类，分别存放在不同的库，这些库可以分布在不同服务器，从而使访问压力被能服务器负载，大大提升性能，同时能提高整体架构的业务清晰度，不同的业务库可根据自身情况定制优化方案。但是它需要解决跨库带来的所有复杂问题。
• 水平分库 ：可以把一个表的数据（按数据行）分到多个不同的库，每个库只有这个表的部分数据，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能。它不仅需要解决跨库带来的所有复杂问题，还要解决数据路由的问题。
• 水平分表 ：可以把一个表的数据（按数据行）分到多个同一个数据库的多张表中，每个表只有这个表的部分数据。

3.5 百亿级数据分表后怎么分页查询

3.5.1 分表

分表，借助唯一主键，查询的时候根据唯一主键的规则，可以指定到那一张表查询，常见的分布式生成唯一ID的方式很多，最常见的雪花算法Snowflake、滴滴Tinyid、美团Leaf。以雪花算法举例来说，一毫秒可以生成4194304多个ID。

3.5.2 借助ES

Elasticsearch 将全文检索、数据分析以及分布式技术，合并在了一起，使用 lucene（全文检索） ；

分布式的搜索引擎和数据分析引擎、处理海量数据、全文检索，结构化检索，数据分析 ；

3.5.3 提前计算

如果需要的是统计数据，如季报、年报等数据，可以进行提前计算，或者提前一天计算，但是会有一天的差值。

4. 创建分区、索引

参考资料

• 分库分表
• 水平分库分表方法（range、hash）
• 分库后关联查询案例
• 百亿级数据分表后怎么分页查询？

原文：https://www.cnblogs.com/renxiuxing/p/15265387.html