数据库体系

---

一 、数据库设计与优化

指根据业务需求和数据特点，设计合理的数据库结构，包括表结构、索引、分区、分库分表等。一个好的数据库设计能够提高数据的存储效率、查询效率和数据的一致性。在数据库设计中，需要考虑数据的完整性、一致性、冗余度、范式等因素。同时，还需要根据业务需求进行合理的索引设计，以提高查询效率。

数据库优化指对数据库进行性能调优，以提高数据库的响应速度和吞吐量。

二、索引

B+树：

• 平衡性：一种自平衡的多路查询树，即使在数据删除/新增操作后，树的高度也能维持在对数级别，保证时间复杂度O(log n)
• 高效的磁盘I/O：内部节点只存储指针，所有数据存储在叶子节点。在查询过程中，只要到达叶子节点，就能找到对应的数据，无须遍历树的其它叶子节点数据。
• 支持范围查询和排序：节点间是互相链接，使用范围查询变得高效。同时，由于所有数据都存储在叶子节点，无需再引入外部排序。
• 减少锁竞争：行级锁，非叶子节点索引大多都是共享的，不需要加锁，使得查询变得高效。

设计原则：

• 根据查询条件设计：索引应该覆盖查询条件的字段，包括where/group by/order by中查询的字段
• 选择合适的列建索引：应建在基数变化大的字段上
• 使用短索引：对于字符串类型，可考虑使用前缀索引

三 、存储引擎

• InnoDB：提供了事务支持，行级锁定，外键约束和良好的崩溃恢复能力，适合需要高并发处理的数据完整性保证的应用场景
• MyISAM：提供快速的读取性能，不支持事务和行级锁定，适用于读操作多于写操作且对事务支持要求不高的应用
• MEMORY： 临时表及缓存，需快速读写操作场合，内存操作，不持久化

四、事务

数据库的核心组件，用于保证数据的一致性和完整性。在分布式系统，事务的重要性越明显。是一组对数据库的操作序列，这些操作要么全部完成，要么全部不完成。

4.1 特点

• 原子性，一组数据库操作序列，这些操作要么全部完成，要么全部不完成，即不会存在部分完成的情况。
• 一致性，事务操作的前后，数据库的状态是一致的，即满足所有约束。
• 隔离性，并发执行的事务之间是互不干扰，一个事务的中间状态对其它事务不可见。
• 持久性，一旦事务执行完成，其对数据库的修改是持久的

4.2 作用

• 保证数据的一致性和完整性，防止数据损坏
• 提升并发性，通过合理的事务隔离级别保证资源竞争
• 提高系统的可靠性，确保故障恢复时能恢复到正确的状态

4.3 事务隔离级别

MySQL中的事务隔离级别是数据库管理系统（DBMS）用来定义多个事务同时运行时如何相互隔离的机制。MySQL支持以下四种标准的事务隔离级别：

• 4.3.1 READ UNCOMMITTED（未提交读）
  • 这是最低的隔离级别。
  • 在这个级别下，一个事务可以读取到另一个事务未提交的数据。
  • 可能导致脏读（Dirty Reads）、不可重复读（Non-repeatable Reads）和幻读（Phantom Reads）等问题。
  • 由于其潜在的问题，这个级别很少被使用。
• 4.3.2 READ COMMITTED（提交读）
  • 在这个级别下，一个事务只能读取到另一个事务已经提交的数据。
  • 解决了脏读的问题，但仍然存在不可重复读和幻读的风险。
  • 在某些数据库系统中，如SQL Server，这是默认的隔离级别。
• 4.3.3 REPEATABLE READ（可重复读）
  • 这是MySQL的默认隔离级别。
  • 在这个级别下，一个事务在整个过程中可以多次读取同一数据行的结果是一致的，即使其他事务已经修改了这些数据并提交了它们。
  • 解决了脏读和不可重复读的问题，但仍然存在幻读的风险。
  • MySQL通过多版本并发控制（MVCC）技术来实现这个级别的隔离。
• 4.3.4 SERIALIZABLE（可串行化）
  • 这是最高的隔离级别。
  • 在这个级别下，事务被串行执行，即一个事务执行完成后，另一个事务才开始执行。
  • 这完全避免了脏读、不可重复读和幻读的问题。
  • 但由于事务的串行执行，这个级别下的性能可能会显著降低。数据库会加锁来确保事务的串行执行，这可能会导致锁竞争和死锁的问题。

总结：选择合适的事务隔离级别是平衡并发性和数据一致性之间的重要考虑。

• 较低的隔离级别可以提高性能，但可能导致数据一致性问题。
• 较高的隔离级别能提供更好的数据一致性，但可能降低性能。
• MySQL的默认隔离级别是REPEATABLE READ，它在大多数情况下提供了足够的数据一致性保护，同时避免了过度的性能损失。然而，在需要绝对数据一致性的场景下，可能需要考虑使用SERIALIZABLE级别。

4.4 分布式事务**

4.4.1 Saga事务

• 核心思想: 将一个长事务拆分成多个短事务，每个短事务都在本地服务中执行，并且每个短事务都能够独立提交或回滚。如果整个流程中的某个短事务失败，那么会触发一系列补偿操作来撤销之前成功的短事务的影响，从而确保整个流程的一致性。
• 组成部分:
  • 协调者：协调者负责管理整个Saga事务的执行流程，包括启动、跟踪、终止Saga事务。
  • 参与者：每个参与者都是一个服务，负责执行一个或多个本地事务。
  • 补偿操作：当某个参与者失败时，协调者会触发相应的补偿操作来撤销之前成功的操作。
• 工作流程:
  • 初始化：协调者开始一个Saga事务，并通知第一个参与者执行本地事务。
  • 执行本地事务：参与者执行本地事务，并通知协调者事务是否成功。
  • 后续操作：如果所有参与者都成功完成本地事务，则整个Saga事务成功。如果任何一个参与者失败，则协调者启动补偿操作。
  • 补偿操作：协调者通知之前的参与者执行补偿操作，撤销已完成的操作。
• 缺点: Saga事务保证的是最终一致性，而非强一致性。

4.4.2 两阶段提交（2PC）

• 工作流程:
  • 准备阶段（Prepare Phase）：协调者询问所有参与者是否准备好提交事务。
  • 提交阶段（Commit Phase）：如果所有参与者都同意提交，则协调者向所有参与者发送提交命令。如果有任何参与者不同意提交，则协调者向所有参与者发送回滚命令。
• 优点: 确保所有参与者要么全部提交，要么全部回滚，提供强一致性保证。
• 缺点:
  • 性能问题：需要多次网络往返通信，导致较高的延迟。
  • 阻塞性：在准备阶段，参与者必须锁定资源直到事务结束，这可能导致资源长时间锁定。
  • 单点故障：协调者成为单点故障，一旦协调者失败，事务可能陷入不确定状态。

4.4.3 三阶段提交（3PC - Three-phase Commit）

• 工作流程：
  在两阶段提交的基础上进行了改进，增加了一个预提交阶段，以减少阻塞的可能性。
  分为询问阶段、预提交阶段和提交阶段。在询问阶段，协调者询问参与者是否可以执行事务；在预提交阶段，参与者进行一些准备工作并回复协调者；如果所有参与者都回复可以提交，那么在提交阶段，协调者通知所有参与者提交事务。
• 优点：
  相比两阶段提交，降低了阻塞的可能性，提高了系统的可用性。
  在某些情况下可以更快地检测到故障并进行回滚。
• 缺点：
  仍然存在性能开销较大的问题，因为需要进行多次通信。
  实现相对复杂，增加了系统的复杂性和开发成本。
• 适用场景：
  对可用性要求较高，同时对数据一致性也有一定要求的场景。

4.4.4 补偿事务（TCC - Try-Confirm-Cancel）

• 概述：
  TCC 是一种应用层面的分布式事务解决方案。它要求业务系统实现三个方法：Try 方法用于尝试执行事务，Confirm 方法用于确认事务，Cancel 方法用于在事务出现问题时进行回滚。
  通过业务代码来实现事务的控制，而不是依赖于数据库的事务机制。
• 优点：
  性能较好，因为事务的控制在应用层面进行，可以根据业务需求进行优化。
  不会出现阻塞情况，因为每个阶段都是由业务代码主动执行的。
  可以更好地适应复杂的业务场景，对事务的控制更加灵活。
• 缺点：
  开发成本较高，需要业务系统实现三个方法，并且需要考虑各种异常情况的处理。
  对业务的侵入性较大，需要修改业务代码来实现事务控制。
• 适用场景：
  对性能要求较高，业务逻辑较为复杂的场景。

4.4.5 消息队列实现最终一致性

• 概述：
  通过消息队列来实现分布式事务的最终一致性。业务系统在执行本地事务的同时，将事务的操作信息发送到消息队列中，其他系统从消息队列中获取消息并进行相应的处理。
  如果事务出现问题，可以通过重试机制或者人工干预来保证最终的一致性。
• 优点：
  性能较好，因为事务的处理是异步进行的，可以提高系统的吞吐量。
  对业务的侵入性较小，只需要在业务系统中发送和接收消息即可。
  可以更好地适应分布式系统的特点，提高系统的可用性和可扩展性。
• 缺点：
  只能保证最终一致性，而不是强一致性。在某些情况下，可能需要一定的时间才能达到一致状态。
  实现相对复杂，需要考虑消息的可靠传输、重复消费等问题。
• 适用场景：
  对性能要求较高，对数据一致性要求不是非常严格的场景，如电商系统中的订单处理、物流跟踪等。

综上所述，选择哪种分布式事务解决方案需要根据具体的业务需求来决定。如果对数据一致性要求非常高，可以选择两阶段提交或三阶段提交；如果对性能要求较高，业务逻辑较为复杂，可以选择补偿事务；如果对性能和可用性要求较高，对数据一致性要求不是非常严格，可以选择消息队列实现最终一致性。

五、锁

用于管理多个事务对数据的并发访问，保证数据的一致性和完整性。

• 共享锁（S锁）：用于多个事务持有共享锁并读取同一数据，但阻止其它事务修改这些数据
• 排他锁（X锁）：用于一个事务持有排他锁并进行数据操作，但阻止其它事务同时获取共享锁或排他锁
• 表级锁：锁定整个表，适用于对整个表操作或只读操作，阻止其它事务同时对数据进行写操作，保证数据的完整性
• 行级锁：锁定表的某行记录，大大减少数据库操作的冲突，但锁的开销较大
• 意图锁：用于提示事务意图在表中的某些行上施加共享锁或排他锁

六、update机制

• 6.1 解析SQL语句是否正确，表和列是否存在
• 6.2 根据查询和索引条件生成执行计划
• 6.3 先查询缓存池区是否存在匹配的数据，若不存在，会从硬盘将数据页刷新到缓存池
• 6.4 对查询到的记录进行加锁，一般是共享锁，防止在更新操作过程中其它事务对记录进行修改
• 6.5 将更新前的数据保存到undo日记中，以便在必要时进行回滚。然后更新内存中的数据页，这些数据页称为脏页
• 6.6 innodb会将更新操作记录到redo log缓存区, 并在适当的时机将记录刷新到磁盘redo log, 确保即使在系统崩溃之前，更新操作也能持久化
• 6.7 事务提交前，innodb会将redo log操作记录的状态更新为已提交。如果触发了脏页刷新，innodb会将更新的binlog（归档日志）刷新到磁盘
• 6.8 事务一旦提交完成，innodo就释放对记录的锁，这样其它事务就可以对记录进行操作了

七、备份与恢复

7.1 备份策略: MySQL数据库的备份策略主要包括以下几种方式：

• mysqldump

  • 逻辑备份：mysqldump是MySQL自带的逻辑备份工具，通过生成包含SQL语句的文件来备份数据库。这些SQL语句可以在需要时重新执行以恢复数据库。
  • 优点：简单易用，备份文件可读性强，支持跨平台。
  • 缺点：备份和恢复速度相对较慢，对于大型数据库可能不太适用。
  • 使用场景：适用于数据量不是很大的场景，或者需要备份数据库结构时。

• 二进制日志（binlog）

  • 增量备份：MySQL的二进制日志记录了所有的DDL（数据定义语言）和DML（数据操纵语言）语句（不包括SELECT和SHOW这类操作），可以用于增量备份和恢复。
  • 优点：可以记录数据库的变更历史，支持时间点恢复，备份效率高。
  • 缺点：恢复过程相对复杂，需要结合全备进行。
  • 使用场景：适用于需要频繁备份且数据量大的场景，如金融、电商等。

• 物理备份

  • 冷备：在数据库停止服务的情况下，直接复制数据文件进行备份。
  • 热备：在数据库运行状态下，使用专门的工具（如Percona XtraBackup）进行备份，不影响数据库的正常使用。
  • 优点：备份和恢复速度快，适用于大型数据库。
  • 缺点：热备工具可能需要额外购买或配置。
  • 使用场景：适用于对数据库性能要求较高，且不能容忍长时间停机备份的场景。

7.2 恢复流程:

• 全备恢复：
  • 使用mysqldump备份的文件，通过mysql命令导入数据库。
  • 适用于数据库完全损坏或需要迁移到新服务器的场景。
• 增量恢复：
  • 首先恢复最近的全备文件。
  • 然后使用mysqlbinlog工具将二进制日志中的变更应用到数据库中，直到需要的恢复点。
  • 适用于需要恢复到特定时间点的场景。

八、案例分析：SQL性能调优过程

8.1 问题发现

• 监控与日志：通过监控工具（如Zabbix、Prometheus）和数据库日志来发现性能瓶颈。
• 慢查询日志：开启MySQL的慢查询日志，记录执行时间超过设定阈值的查询语句。

8.2 查询优化

• 8.2.1 分析慢查询

  对慢查询日志中的查询语句进行分析，使用explain分析sql执行计划, 找出性能瓶颈。

• 8.2.2 查询改写

  • 避免使用select *, 仅查询所需的字段
  • 优化join查询，小表驱动大表，甚至尽可能减少多表查询
  • 分页查询，减少返回的数据量
  • 减少锁竞争, 通过合理的事务隔离级别和锁定机制减少锁等待时间

• 8.2.3 索引优化

  • 使用索引加快查询效率，确保在经常用于过滤，连接，排序的列上使用索引
  • 使用前缀索引减少索引的大小
  • 避免索引失效导致全表扫描的操作
    • 8.2.3.1 索引列不为空，且不使用is null or is not null，=null, !=, <>，or,in,not in判断
    • 8.2.3.2 不使用%like开头
    • 8.2.3.3 where子句不使用参数
    • 8.2.3.4 索引列上不进行任何操作（计算，函数，类型转换）
    • 8.2.3.5 索引字段是字符串类型，查询条件的值要加’’, 避免底层类型自动转换
    • 8.2.3.6 对于覆盖索引，应确保索引的第一个字段作为条件

• 8.2.4 表结构优化

  • 尽量使用数字型字段, 选用最小化存储空间的数据类型，减少I/O操作
  • 使用nvarchar/nchar替代varchar/char
  • 一个表内不超过20字段
  • 尽可能使用not null定义字段

• 8.2.5 分库分表

  通过垂直或水平分区，减少表的大小，提升查询性能和管理效率

• 8.2.6 读写分离

  通过主从复制等方式实现读写分离，减轻主库压力，提高查询性能。

• 8.2.7 归档历史与适时删除

• 8.2.8 软件配置优化

  • buffer_innode_pool_size用于缓存数据和索引, 适当增加可以提高缓存命中率
  • 使用慢查询分析和优化SQL语句

• 8.2.9 硬件优化

  • 确保有足够的内存，缓存索引和数据
  • 使用SSD硬盘，减少I/O等待

• 8.2.10 缓存策略

  使用Redis等缓存中间件来缓存热点数据，减少对数据库的访问压力。

• 8.2.11 负载均衡

  通过负载均衡器将请求分发到多个数据库实例上，实现负载均衡。

• 8.2.12 异步处理

  对于非实时性要求较高的查询，可以采用异步处理的方式，减少用户等待时间。

8.3 结果验证

• 性能测试：使用性能测试工具（如JMeter、LoadRunner）对优化后的查询进行性能测试，验证优化效果。
• 对比分析：对比优化前后的性能指标（如查询响应时间、吞吐量等），评估优化效果是否达到预期。

通过以上步骤，可以系统地发现和解决SQL查询中的性能问题，提高数据库的整体性能。