高性能 MySQL实战

概览

内容来自于周彦伟的《高性能MySQL实战》，课程有 10 个课时，本人对其内容进行了梳理、消化并形成笔记。

1. 高性能 MySQL 基础篇

1.1 体系结构

• Client Connectiors 层

  负责处理客户端的连接请求，与客户端创建连接

• MySQL Server 层

  • Connection Pool 负责处理和存储数据库与客户端创建的连接，一个线程负责管理一个连接，Connection Pool 还包括了用户认证模块，如登录、执行操作权限校验
  • Services & utilities 是管理服务 & 工具集，包括备份恢复、安全管理、集群管理服务和工具
  • SQL Interface 负责接收客户端发送的 SQL 语句
  • Paser 解析器 负责对 SQL 语句进行语法解析生成语法解析树
  • Optimizer 查询优化器会根据解析树生成执行计划，并选择适合的索引，然后按照执行计划执行 SQL 语句与存储引擎交互
  • Caches 缓存包括各个存储引擎的缓存部分，如 InnoDB 的 Buufer Pool、MyISAM 的 key buffer，以及一些权限、Session 级别的缓存

• 存储引擎层

  负责与文件系统交互，提供了多种存储引擎，存储引擎是基于文件访问层抽象接口定制的一种文件访问机制

1.2 存储引擎

1.2.1 InnoDB 存储引擎架构

InnoDB 存储引擎分为两部分，上半部分是位于计算机的实例层，下半部分是位于文件系统的物理层

1.2.1.1 实例层

• 实例层分为线程和内存，最重要的线程是 Master Thread，负责调度其他线程，优先级最高
• Master Thread 内部包含了主循环（loop）、后台循环（background loop）、刷新循环（flush loop）、暂停循环（susped loop），大部分操作都在主循环里完成，其中包含了 1s 操作和 10s 操作
  • 1s 操作
    • 日志缓冲刷新到磁盘（这个操作总是被执行，即使事务还没有提交）
    • 最多可能刷 100 个新脏页到磁盘
    • 执行并改变缓冲的操作
    • 若当前没有用户活动，可能切换到后台循环（background loop）等
  • 10s 操作
    • 最多可能刷新 100 个脏页到磁盘
    • 合并至多 5 个被改变的缓冲（总是）
    • 日志缓冲刷新到磁盘（总是）
    • 删除无用的 Undo 页（总是）
    • 刷新 100 个或者 10 个脏页到磁盘（总是）产生一个检查点（总是）等
    • buf_dump_thread 负责将 buffer pool 中的内容 dump 到物理文件中，以便再次启动 MySQL 时，可以快速加热数据
    • page_cleaner_thread 负责将 buffer pool 中的脏页刷新到磁盘，在 5.6 版本之前没有这个线程，刷新操作都是由主线程完成的，所以在刷新脏页时会非常影响 MySQL 的处理能力，在5.7 版本之后可以通过参数设置开启多个 page_cleaner_thread
    • purge_thread 负责将不再使用的 Undo 日志进行回收
    • read_thread 处理用户的读请求，并负责将数据页从磁盘上读取出来，可以通过参数设置线程数量
    • write_thread 负责将数据页从缓冲区写入磁盘，也可以通过参数设置线程数量，page_cleaner 线程发起刷脏页操作后 write_thread 就开始工作了
    • redo_log_thread 负责把日志缓冲中的内容刷新到 Redo log 文件中
    • insert_buffer_thread 负责把 Insert Buffer 中的内容刷新到磁盘。实例层的内存部分主要包含 InnoDB Buffer Pool，这里包含 InnoDB 最重要的缓存内容。数据和索引页、undo 页、insert buffer 页、自适应 Hash 索引页、数据字典页和锁信息等。additional memory pool 后续已不再使用。Redo buffer 里存储数据修改所产生的 Redo log。double write buffer 是 double write 所需的 buffer，主要解决由于宕机引起的物理写入操作中断，数据页不完整的问题。

1.2.1.2 物理层

在逻辑上分为系统表空间、用户表空间和 Redo日志

• 系统表空间里有 ibdata 文件和一些 Undo，ibdata 文件里有 insert buffer 段、double write段、回滚段、索引段、数据字典段和 Undo 信息段
• 用户表空间是指以 .ibd 为后缀的文件，文件中包含 insert buffer 的 bitmap 页、叶子页（这里存储真正的用户数据）、非叶子页。InnoDB 表是索引组织表，采用 B+ 树组织存储，数据都存储在叶子节点中，分支节点（即非叶子页）存储索引分支查找的数据值
• Redo 日志中包括多个 Redo 文件，这些文件循环使用，当达到一定存储阈值时会触发checkpoint 刷脏页操作，同时也会在 MySQL 实例异常宕机后重启，InnoDB 表数据自动还原恢复过程中使用。

1.2.1.3 内存和物理结构

• 用户读取或者写入的最新数据都存储在 Buffer Pool 中，采用 LRU 机制
• Buffer Pool 决定了 SQL 执行的速度，如果查询结果页都在内存中则返回结果速度很快，否则需要从磁盘读取
• Buffer Pool 的大小，单实例建议 60%~80%，多实例建议 <=80%，运行一段时间后可以通过 show global status like '%buffer_pool_wait%' 的值来看是否需要调整 Buffer Pool 的大小
• Redo Log 是一个循环利用的文件集，负责记录 InnoDB 中所有对 Buffer Pool 的物理修改日志
  • 当检查点位置 LSN 和最新写入的 LSN 差值（checkpoint_age）达到 Redo log 文件占文件总空间的 75% 时，InnoDB 会进行异步刷新操作，直到降至 75% 以下，并释放 Redo Log 的空间
  • 当达到 90% 时会触发同步刷新，此时 InnoDB 处于挂起状态无法操作
• Redo Log 的大小直接影响了数据库的处理能力
  • 如果设置太小，会导致强行 checkpoint 操作频繁刷新脏页
  • 如果设置过大，由于 Redo Log 只有两个 checkpoint 点，崩溃恢复时会很慢
• 事务提交时 Log Buffer（日志缓冲区）会刷新到 Redo Log 文件中
• 参数 innodb_file_per_table 表示用户建表时是否采用用户独立表空间
  • ON，用户建表时采用用户独立表空间，一个表对应一组物理文件 .frm 表定义文件和 .idb 表数据文件，删除数据后可以回收空间
  • OFF，用户建表时采用共享表空间，存储在 ibdata 文件中，但是会导致 ibdata 文件过大，且表删除后空间无法回收

1.2.2 MySQL 5.7 与 8.0 特性对比

• MySQL 5.7 版本新特性如下

  • 将 Undo 从共享表空间 ibdata 文件中分离出来，可以在安装 MySQL 时由用户自行指定文件大小和数量
  • 增加了 temporary 临时表空间，里面存储着临时表或临时查询结果集的数据
  • Buffer Pool 大小可以动态修改，无需重启数据库实例，这是 DBA 的福音。

• MySQL 8.0 版本新特性如下

  • 将 InnoDB 表的数据字典和 Undo 都从共享表空间 ibdata 中彻底分离出来了，以前需要ibdata 文件中数据字典与独立表空间 ibd 文件中数据字典一致才行，8.0 版本就不需要了
  • temporary 临时表空间也可以配置多个物理文件，而且均为 InnoDB 存储引擎并能创建索引，这样加快了处理的速度
  • 用户可以像 Oracle 数据库那样设置一些表空间，每个表空间对应多个物理文件，每个表空间可以给多个表使用，但一个表只能存储在一个表空间中。

1.2.3 InnoDB 和 MyISAM 对比

• 功能对比

• 性能对比

  
  • 读写混合模式下，随着 CPU 核数的增加，InnoDB 的读写能力呈线性增长
  • 在测试用例里，最高可达近 9000 的 TPS，但 MyISAM 因为读写不能并发，它的处理能力跟核数没关系，呈一条水平线，TPS 低于 500
  • 只读模式下，随着 CPU 核数的增加，InnoDB 的读写能力呈线性增长，最高可达近 14000 的 TPS，但 MyISAM 的处理能力不到 3000 TPS。

1.2.4 InnoDB 关键特性

• InnoDB 存储引擎的核心特性包括：锁、事务、表空间、ARIES、数据页、内存、线程以及状态查询

• ARIES 三原则，即 Write Ahead Logging（WAL）
  • 先写日志后写磁盘，日志成功写入后事务就不会丢失，后续由 checkpoint 机制保证磁盘物理文件与 Redo Log 一致
  • 利用 Undo Log 记录变更前的数据，即 Redo Log 记录事务数据变更前的值，用于回滚和其他事务多版本读
  • 利用 Redo Log 记录变更后的数据，即 Redo Log 记录事务数据变更后的值
• 使用 show engine innodb status 能输出 InnoDB 详细的运行信息

1.3 事务和锁机制

1.3.1 事务的特性

• 原子性：事务的所有操作，要么全部完成，要么全部不完成，不会结束在某个中间环节
• 持久性：事务一旦提交，在数据库数据的改变就应该是永久的，不会丢失
• 隔离性：一个事物的执行不能被其他事务干扰，锁和多版本控制就符合隔离性
• 一致性：事务开始之前和事务结束之后，数据库的完整性限制未被破坏，一致性包括约束一致性和数据一致性两类
  • 约束一致性：MySQL 支持唯一索引、外键约束，但不支持 check 约束
  • 数据一致性：一个综合性规定，它是由原子性、持久性、隔离性共同保证的结果

1.3.2 特性背后的技术原理

• 原子性

  每一个写事务都会修改 Buffer Pool，从而产生相应的 Redo Log，这些日志信息会被记录到 ib_logfiles 文件中。

  • 当事务已提交，Buffer Pool 未刷盘，通过 Redo Log 恢复数据
  • 当事务未提交，Buffer Pool 刷盘了，通过 Undo Log 回滚，Undo 又是通过 Redo 来保证的，所以最终原子性的保证还是靠 Redo 的 WAL 机制实现的

• 持久性

  
  • 逻辑上：事务的原子性保证事务要么执行要么不执行，从逻辑上保证用户看不到正在改的状态
  • 物理上：通过原子性，即使宕机，也可以从逻辑上将数据找回到再次写入物理空间

• 隔离性

  

  用 Read View 来表示一个事务的可见性

  • 对于隔离级别 RC，每次都生成一个新的readview

  • 对于隔离级别 RR，只在事务开启时，生成一个readview，直到事务提交，不发生变化，所以可以保证可重复读。

    注意：在 RR 级别下，长时间未提交的事务会影响数据库的 PURGE 操作，从而影响数据库的性能

• 一致性

  

  一致性可以归纳为数据的完整性，由原子性、持久性、隔离性共同保证

1.3.3 MySQL 并发控制机制

• 单版本控制：锁

  锁用独占的方式来保证只有一个版本的情况下事务之间相互隔离。

  在 MySQL 中锁的实现与隔离级别有关，如果是 RR 级别，使用的是 Gap 锁来解决幻读问题，但牺牲了并行度，容易引起死锁；如果是 RC 级别，可以使用 Row + RC 模式，可以提高并行度。

• 多版本控制：MVCC

  对数据进行多版本处理，通过事务的可见性来保证事务能看到自己应该看到的数据版本。

  多版本数据的生成：每次写事务都会在 Undo Log 记录当前修改记录的事务号以及修改前的存储地址（即 ROLL_PTR），以便回滚。如果此时有一个读事务查询当前记录，而最新的事务还未提交，根据原子性，读事务看不到最新数据，但可以去回滚段中找老版本数据，这样就生成了多个版本。

1.3.4 MVCC 实现原理

• MySQL 的 InnoDB 引擎的并发控制协议是基于多版本控制实现的，MVCC 最大的好处是读不加锁，读写不冲突。

• MVCC 分为快照读与当前读

  • 快照读：读取的记录是可见版本（有可能是历史版本），不用加锁
  • 当前读：读取的记录是最新版本，并且当前读返回的记录会加锁，保证其他事务不会再并发修改这条记录

• 分区快照读和当前读

  • 快照读：简单的 select
  • 当前读：特殊的读操作，insert、update、delete

• MVCC 实现原理

  

  • 行的组成：数据、隐含 ID、事务号、回滚指针

    • 隐含 ID（DB_ROW_ID），6 个字节，当由 InnoDB 自动产生聚集索引时，聚集索引包括这个值
    • 事务号（DB_TRX_ID），6 个字节，标记了最新更新这条行记录的 Transaction ID，每处理一个事务，值自动+1
    • 回滚指针（DB_ROLL_PT），7 个字节，指向当前记录项的 Rollback Segment 的 Undo Log 记录

  • 随着事务的增加，Undo Log 文件会越来越大，所以 InnoDB 有 purge 线程负责删除比现在最老事务还要早的 Undo Log

    

1.3.5 并发事务问题及解决方案

隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读已提交	不可能	可能	可能
可重复读	不可能	不可能	可能 （间歇锁解决）
串行化	不可能	不可能	不可能

不可重复读重点在于 UPDATA 和 DELETE，而幻读的重点在于 INSERT。它们之间最大的区别是如何通过锁机制来解决它们产生的问题。

1.3.6 锁的分类

• MySQL 有三种锁，不同存储引擎支持的锁有所不同

  • 表级锁
    • 开销小，加锁快
    • 不会出现死锁
    • 粒度最大，锁冲突概率最高，并发度最低
  • 行级锁
    • 开销大，加锁慢
    • 会出现死锁
    • 粒度最小，锁冲突概率最低，并发度最高
  • 页级锁
    • 开销和加锁时间介于表锁和行锁之间
    • 会出现死锁
    • 粒度介于表锁和行锁之间，并发度一般

• 各引擎支持的锁

  

• InnoDB 的锁分类

  • 行锁
    • 共享锁（S），多个事务可以一起读，共享锁之间不互斥，共享锁会阻塞排它锁
    • 排他锁（X），允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁
  • 表锁
    • 意向共享锁（IS），事务计划给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁
    • 意向排他锁（IX），事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁
    • 自增锁（AUTO-INC Locks），特殊表锁，自增长计数器通过该“锁”来获得子增长计数器最大的计数值

• InnoDB 自增锁

  自增锁在 insert 结束后立即释放，可以通过 show engine innodb status\G 查看自增锁的状态信息

  自增锁有一个核心参数 innodb_autoinc_lock_mode

  

• InnoDB 锁关系矩阵如下图所示，其中：+ 表示兼容，- 表示不兼容

  

• InndoDB 行锁的实现

  InnoDB 行锁是通过对索引数据页上的记录（Record）加锁实现的，主要实现算法有三种：

  • Record Lock：单行记录的锁
  • Gap Lock：间隙锁，锁定一个范围，但不包括记录本身，仅仅是锁住数据前面的 Gap
  • Next-key Lock：同时锁住数据本身和数据前面的 Gap

  获取锁时优先拿到 Next-key Lock，如果发现查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是范围

• 排查 InnoDB 锁问题的方法

  • 打开 innodb_lock_monitor 表（注意使用后记得关闭，否则影响性能）
  • MySQL 5.5+ 可以通过查看 information_schema 库下面的 innodb_locks、innodb_lock_waits、innodb_trx 三个视图排查锁问题

1.3.7 InnoDB 加锁行为

结合具体的隔离级别，按照索引类型分析，示例 SQL：update t1 set name=‘XX’ where id=10，id 是索引

• 主键 + RR

  只在主键索引记录上加 X 锁

• 唯一键 + RR

  先在唯一索引上加 X 锁，然后再到主键索引上加 X 锁，如果主键索引记录不存在，那么加 Gap 锁

• 非唯一键 + RR

  先根据 id = 10 定位到第一条记录，给该记录将 X 锁，而且要在记录前面加 Gap 锁，然后再主键索引记录上加上 X 锁

  接着根据 id = 10 定位第二条记录，重复第一条的加锁行为，直到没有其他满足的条件

  在最后一条定位的记录后面，也要加上一个 Gap 锁

• 无索引 + RR

  表里所有行和间隙都要加 X 锁

1.3.8 InnoDB 死锁

• InnoDB 会发生死锁，而 MyISAM 则不会，因为 InnoDB 是逐行加锁的，极容易产生死锁

• 产生死锁的四个条件

  • 互斥条件：一个资源每次只能被一个进程使用
  • 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放
  • 不剥夺条件：进程已获得的资源，在没使用完之前，不能强行剥夺
  • 循环等待条件：多个进程之间形成的一种互相循环等待资源的关系

• 避免产生死锁的建议

  • 加锁顺序一致
  • 尽量基于 primary 或 unique key 更新数据
  • 单次操作数据量不宜过多，涉及表尽量少
  • 减少表上索引，减少锁定资源
  • 使用相关工具：pt-deadlock-logger

• 死锁案例

  • 资源竞争导致死锁

    session1 和 session2 分别想拿到对方的持有的锁

    

  • 元数据锁导致死锁

    session1 和 session2 都想抢占元数据资源

    

  • 查看死锁信息 show engine innodb status\G，关注 “LATEST DETECTED DEADLOCK” 部分

  • 死锁优化

    • 更新 SQL 的 where 条件时尽量用索引
    • 加锁索引准确，缩小锁定范围
    • 减少范围更新，尤其非主键/非唯一索引上的范围更新
    • 控制事务大小，减少锁定数据量和锁定时间长度 （innodb_row_lock_time_avg）
    • 加锁顺序一致，尽可能一次性锁定所有所需的数据行

2. 高性能 MySQL 最佳实践

2.1 设计高性能数据库表

2.1.1 范式与反范式

• 三范式

  • 第一范式要求无重复的列，表的每一列都是不能继续拆分的基本数据项，强调列的原子性
  • 第二范式要求属性完全依赖于主键
  • 第三范式要求属性不传递依赖于其他非主属性

• 第二范式和第三范式的区别

  • 第二范式要求非主键列是否依赖主键
  • 第三范式要求非主键列是否直接依赖主键，而不是通过传递关系（如冗余了其他表的字段）

• 范式优缺点

  • 优点
    • 避免数据冗余，减少维护数据完整性的麻烦
    • 减少数据库的空间
    • 数据变更速度快
  • 缺点
    • 按照范式的规范设计的表，等级越高的范式设计出来的表数量越多
    • 获取数据时，表关联过多，性能较差

• 反范式

  反范式设计就是用空间换时间，通过冗余字段减少多表关联，提高业务场景的响应时间

2.1.2 MySQL 使用原则和设计规范

• 使用规则

  • 数据库只用于数据的存储，不进行数据的复杂计算，不承载业务逻辑，确保存储和计算分离
  • 尽量使用单表查询，减少多表和跨库查询
  • 杜绝大事务、大 SQL、大批量、大字段等一系列性能杀手

• 基本设计

  • 强制使用 InnoDB 存储引擎，MySQL 8.0 之后连数据字典都转换为 MyISAM，MyISAM 已是历史

  • 强制使用 utf8mb4，之前的 utf8 其实是 utf8mb3，没有包含特殊字符

  • 关闭区分大小写功能

    由于区分大小写的库表转换为不区分大小写的库表需要修改底层数据，步骤如下

    • MySQL dump 导出数据库
    • 修改参数 lower_case_tables_name=1
    • 导入备份数据时，必须停止数据库，停止业务，影响非常大
    • 开启 per-table 表空间，开启后，每张业务表会单独创建一个独立于系统表空间的表空间，便于空间的回收，数据的迁移

• 设计实践

  • 列不允许为 NULL，可以在列上添加 NOT NULL DEFAULT 缺省值

    • MySQL 难以优化 NULL 列
    • NULL 列加索引，需要额外空间
    • 含 NULL 复合索引无效

  • int(3) 和 int(5) 的区别

    • 正常显示没有区别
    • 3 和 5 仅是最小显示宽度而已
    • 有 zerofill 等扩展属性时则显示有区别

  • 浮点数(float、double) 和 定点数(decimal) 的区别

    • 浮点数存在误差问题
    • 尽量避免进行浮点数比较
    • 对货币等对精度敏感的数据，应该使用定点数

  • 字符类型中 N 的解释

    CHAR(N) 和 VARCHAR(N) 的长度 N 代表能保存的最大字符数，如 VARCHAR(40) 最多能存储 40 个字符，占用 120 个字节（utf8mb4 下）

  • Char 与 Varchar 类型的区别

    不同点	Char(N)	Varchar(N)
    保存的方式	如果插入的长度小于定义长度时，右边用空格填充至定义长度	如果插入的长度小于定义长度时，按实际长度存储 需要额外使用 1~2 个字节记录长度
    检索的方式	删除尾部空格	保留尾部空格
    最大长度	255 个字符，与编码无关	65535（2^16 - 1，两个字节） varchar的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532字节。

  • IP 处理

    IP 地址一般使用 Char(15) 进行存储，但是当进行查找和统计时，字符类型不是很高效，可以转成整形 int unsigned(4)，转化后数字是连续的，可以使用索引，占用空间小

    • SELECT INET_ATON(‘192.168.0.1’) => 3232235521
    • SELECT INET_NTOA(‘3232235521’) => 192.168.0.1

  • TIMESTAMP 处理

    将时间戳转成整形后，数据是连续的，可以使用索引，占用空间小

    • SELECT FROM_UNIXTIME(时间戳) => 时间
    • SELECT UNIX_TIMESTAMP(时间) => 时间戳

2.2 设计高性能索引

2.2.1 索引定义

索引是一种数据结构，以额外的写入和存储空间为代价来提高数据库表上数据检索操作的速度。

2.2.2 索引原理

2.2.2.1 Hash 索引

• 哈希索引的实现

  数据库中的哈希索引是基于哈希表实现的，对哈希索引列的值进行哈希算法计算，得到对应索引列的哈希码形成哈希表，由哈希码及哈希码指向的真实数据行的指针组成了哈希索引（键值对）。

  因为哈希索引只存储哈希值及对应的数据行指针，不存储实际字段值，所以其结构紧凑，查询速度非常快，在没有哈希冲突的情况下访问哈希索引一次即可命中。但是哈希索引的应用场景是仅适用于对哈希索引列的等值查询，不支持范围查询。

• 哈希冲突碰撞处理

  • 采用拉链法

    对于 Hash 碰撞通用的处理方法是使用链表，将哈希冲突碰撞的元素形成一个链表，发生冲突时在链表上进行二次遍历找到数据

  • 选择哈希算法的优先级

    FNV64 > CRC32 （大数据量下 Hash 冲突概率较大）> MD5 > SHA1

• MySQL 如何使用哈希

  • Memory 存储引擎原生支持的 Hash 索引

    建表时可以原生显式创建并且使用 Hash 索引

  • InnoDB 自适应哈希索引（AHI）

    InnoDB 存储引擎为了提升查询效率，会监控表上的各个索引页的查询，当某些索引值访问非常频繁时（满足三个条件），会在内存中基于 B+ Tree 索引再创建一个哈希索引（AHI）。

    AHI 的建立使得 InnoDB 存储引擎能为热点索引页建立哈希索引来加速访问。另外，用户只能选择开启或关闭功能，无法进行人工干涉。

  • 当满足下面三个条件时，InnoDB 为整个 block 构建 AHI 记录项

    • 分析使用自适应哈希索引（AHI）可以成功查询的次数是否超过 block 上记录数的1/16；
    • btr_search_info::n_hash_potential大于或等于BTR_SEARCH_BUILD_LIMIT (100)，表示为 SQL 查询能够连续 100 次成功使用 AHI；
    • 尚未为当前 block 构造索引或者当前 block 上已经构建了 AHI 索引且 block->n_hash_helps 大于 page 上记录数的两倍或者当前 block上 推荐的前缀索引列发生了变化 。

2.2.2.2 B+ Tree 索引

• 原理

  B+ Tree 索引能够快速访问数据，是因为存储引擎不需要通过全表扫描来获取数据，而是通过索引的根节点（通常在内存中）开始进行二分查找，根节点的槽中都存放了指向子节点的指针，存储根据这些指针能够快速遍历数据。

• 结构

  根页面存储了 <key+pageno>，pageno 是指向具体叶子节点的页面号。其他页面都是叶子节点，存放了具体的数据 <key+data>。相同深度的子页面之间可以通过顺序指针访问相邻的数据，提高区间访问的性能。例如，通过页面号为 20 的根节点可以快速得知 Key<10 的数据在 pageno 33 的页面，key在 [10,16) 范围的数据在 pageno 56 的页面。

• 数据存放的位置

  叶子节点存放的 <key+data> ，对于真正要存放哪些数据还得取决于该 B+Tree 是聚簇索引（Clustered Index）还是辅助索引（Secondary Index）。

2.2.2.3 聚簇索引和辅助索引

• 聚簇索引

  InnoDB 的聚簇索引按照主键索引构建 B+ Tree，B+ Tree 的叶子结点就是数据行，InnoDB 的主键索引就是数据表本身

• 辅助索引（非聚簇索引、二级索引）

  只是根据索引列构建 B+Tree，但在 B+Tree 的每一行都存了主键信息，加速回表操作

2.2.3 索引细节

2.2.3.1 基本概念

• 谓词：where 语句中的条件表达式，即过滤字段，一个过滤字段就是一个简单谓词，多个过滤字段组合在一起就是组合谓词
• 过滤因子：描述了谓词的选择性，过滤因子越小表示谓词能过滤更多的数据，选择性越强，字段越适合创建索引
  • 简单谓词的过滤因子 = 谓词结果集数量 / 表总行数
  • 组合谓词的过滤因子 = 谓词 1 的过滤因子 * 谓词 2 的过滤因子
• 基数（Cardinality）：某个键值去重后的行数，索引列不重复记录数量的预估值，MySQL 优化器会依赖于它
• 选择率：count(distinct 列 ) / count(*)，选择率越接近 1，说明越适合在这个字段上创建索引
• 回表：当无法通过索引扫描方位所有数据，需要回到主表进行数据扫描并返回

2.2.3.2 Cardinality 统计信息

Cardinality 能快速告知字段的选择性，有四种更新策略

• 查看统计

  执行 show index from table_name 会看到 Cardinality，同时也会触发 MySQL 数据库对 Cardinaltiy 值的统计

• 触发统计

  当 insert 和 update 时，表中超过 1/16 的数据发生变化或者 stat_modified_counter > 20亿

• 采样统计

  为了减少统计信息更新造成的资源损耗，数据库对 Cardinality 采样统计，每次随机获innodb_stats_persistent_sample_pages 页的数量进行 Cardinality 统计

• 手动统计

  执行 alter table table_name engine=innodb 或 analyze table table_name，当发现优化器选择错误的执行计划或没有走理想的索引时，执行 SQL 语句来手动统计信息有时是一种有效的方法

2.2.3.3 key_len 计算规则

索引字段的数据类型 + 表使用的字符集

如Varchr(10) 变长字段且允许 NULL：10 * (Character Set：utf8=3，gbk=2，latin1=1) + 1（标记是否为 NULL 需要 1 个字节）+ 2（变长字段存储长度信息最多需要 2 个字节）

2.2.3.4 最左前缀匹配原则

使用 B+Tree 联合索引检索数据时，MySQL 优化器会读取谓词并按照联合索引字段创建顺序从左到右匹配，直到遇到范围查询或非等值查询后停止匹配。

2.2.3.5 设计高性能索引步骤

1. 定位由于索引不合适或缺少索引而导致的慢查询

2. 设计合理的索引，如使用业务不相关的自增主键为主键

3. 创建索引策略

   优先为搜索列、排序列、分组列创建索引，必要时加入查询列创建覆盖索引

   计算字段列基数和选择率，选择率越接近于 1 越适合创建索引

   索引选用较小的数据类型（整型优于字符型），字符串可以考虑前缀索引

   不要建立过多索引，优先基于现有索引调整顺序

   参与比较的字段类型保持匹配并创建索引

4. 调优索引

   分析执行计划

   更新统计信息（Analyze Table）

   Hint优化，方便调优（FORCE INDEX、USE INDEX、IGNORE INDEX、STRAIGHT_JOIN）

   检查连接字段数据类型、字符集

   避免使用类型转换

   关注 optimizer_switch，重点关注索引优化特性 MRR（Multi-Range Read）和 ICP（Index Condition Pushdown）

   • MRR 优化是为了减少磁盘随机访问，将随机 IO 转化为顺序 IO 的数据访问，其方式是将查询得到辅助索引的键值放到内存中进行排序，通常是按照主键或 RowID 进行排序，当需要回表时直接根据主键或 RowID 排序顺序访问实际的数据文件，加速 SQL 查询。

   • ICP 优化同样也是对索引查询的优化特性，MySQL 根据索引查询到数据后会优先应用 where 条件进行数据过滤，即无法使用索引过滤的 where 子句，其过滤由之前 Server 层的数据过滤下推到了存储引擎层，可以减少上层对记录的检索，提高数据库的整体性能。

2.2.3.6 创建索引规范

• 联表查询时，JOIN 列的数据类型必须相同，并且要建⽴索引
• 不在低基数列上建⽴索引，例如“性别”
• 选择区分度（选择率）大的列建立索引。组合索引中，区分度（选择率）大的字段放在最前面。
• 对过长的 Varchar 段建立索引，建议优先考虑前缀索引，或添加 CRC32 或 MD5 伪列并建⽴索引
• 合理使用覆盖索引减少IO，避免排序

2.3 提高查询性能

2.3.1 MySQL 查询优化器

2.3.1.1 select 执行过程

1. 客户端通过通过协议与服务器建立连接，然后发送一条 select 语句给服务器
2. 服务器先检查查询缓存，如果命中缓存，则立刻返回结果，否则进入下一阶段
3. 服务器对 SQL 进行解析、预处理，生成解析树
4. 查询优化器根据解析树生成对应的执行计划
5. 查询执行引擎根据执行计划调用存储引擎的 api 来执行查询
6. 最后将查询结果返回客户端，同时也会放入查询缓存中

tips：查询缓存器由于命中率低难以发挥作用，8.0 后已经被移除

2.3.1.2 查询优化案例

• Range 执行效率差

  • 执行流程分为四个步骤

    • 根据查询条件计算所有的 possible keys

    • 计算全表扫描代价（cost_all）

    • 计算最小的索引范围访问代价（cost_range），有三步

      • 对于每一个 possible_key（可选索引），调用 records_in_ranges 函数计算范围中的 rows
      • 根据 rows 计算二级索引访问代价
      • 获取 cost 最小的二级索引访问代价

    • 选择执行最小化访问代价的执行计划，如果cost_range <= cost_all，则索引范围扫描，否则全表扫描

  • 问题

    possible_key 越多，随机 IO 代价越大，Range 查询效率越低

  • 解决

    可以用 pt-index-usage 工具来删除冗余索引，用 pt-duplicate-key-checker 工具来删除重复索引

• 强制优化器使用索引

  表存在一个联合索引 uniq_subId_globalId（sub_id，global_id），优化前 key_len 的长度只有 4，强制使用联合索引后，使用的key_len 长度变长了，查询速度也获得巨大提升

  

2.3.2 MySQL 执行计划

2.3.2.1 三个步骤

• 查询 SQL 执行计划

  explain 表名、desc 表名、show create table 表名

• 通过 porfile 定位 Query 代价消耗

  set profiling=1；

  执行 SQL；

  show profiles; 获取 Query_ID。

  show profile for query Query_ID; 查看详细的 profile 信息

• 通过 Optimizer Trace 表查看 SQL 执行计划树

  set session optimizer_trace=‘enabled=on’；

  执行 SQL；

  查询 information_schema.optimizer_trace 表，获取 SQL 查询计划树；

  set session optimizer_trace=‘enabled=off’；开启此项影响性能，记得用后关闭。

2.3.2.2 工具

• 使用慢查询文件

  将慢 SQL 记录到慢查询文件，通过 slow_query_log=on 开启

• 分析慢查询常用的工具

  explian、Mysqldumpslow（MySQL 官方）、pt-query-digest（Percona）

2.3.3 SQL优化建议

• 全表扫描还是索引扫描，对于小表来说，两者 IO 调用次数和返回时间差不多，对大表来说全表扫描查询返回的时间很长，需要加索引
• 在选择度高的列上创建索引，创建时避免冗余索引，如 index(a, b, c) 和 index(a)，并且尽可能拓展原有索引，但在特殊情况下，扩展已有的索引会导致其变得太大，这时就要创建新索引
• 多表关联的 SQL，在关联列要有索引且字段类型一致，这样 MySQL 在进行嵌套循环连接查找时可以使用索引，如果字段类型不一致会发生隐式转换导致无法使用索引
• 多表关联时尽量让结果集小的表作为驱动表，注意是结果集小的表而不是小表
• MySQL 的索引是 B+Tree 结构，全模糊查询无法索引，需要添加其他选择度高的列作为补充，或者使用 ES、solr 解决全模糊匹配
• 尽量不要使用子查询，对子查询产生的临时表再扫描时将无索引可查询，只能进行全表扫描
• MySQL 对于出现在 from 中的表无所谓顺序，对于 where 中也无所谓顺序，这也是可以优化 SQL 的地方
• order by / group by 的 SQL 涉及排序，尽量在索引中包含排序字段，并让排序字段的排序顺序与索引列中的顺序相同，这样可以避免排序或者减少排序的次数
• 不要总想着用一个SQL 解决所有事情，可以分步骤来进行，MySQL 也十分擅长处理短而简单的 SQL，总体耗时会更短

2.3.4 MySQL 自身对 SQL 的优化

• 重新定义表的关联顺序

  多表关联查询时，MySQL 优化器会自动选择驱动表以及表的连接顺序，基于 cost 规则极大减少 SQL 执行时间

• 使用等价变化原则

  MySQL 可以合并或减少一些比较，还可以移除一些恒成立或者恒不成立的判断

• 优化 count、min、max

  索引和列是否可为空通常可以帮助 MySQL 优化这类表达式，如查询最小值只需找到索引树最左边的第一条记录

2.3.5 SQL 优化案例

• 根据 Extra 列优化

  Using where 通常是全表或全索引扫描后通过 where 过滤条件进行数据筛选

  Using filesort 通常是没有可选的索引来避免排序

  Using temporary 通常是和 Using filesort 一起出现，数据需要用到临时表和 filesort

  以上都可以通过创建索引来避免排序和临时表开销

• 根据 Rows 列优化

  rows 行数过多时，可以对各查询条件扫描行数进行对比，找出最优的索引策略，然后添加索引减少扫描行

• group by 和 order by 的优化

  分组和排序都涉及排序，故尽量在索引中包含排序字段，并让排序字段的排序顺序与索引列中的顺序相同，这样可以避免排序或减少排序次数，如 where a = ? order by b ,c 就可以创建 index(a,b,c)

• limit 分页优化

  走主键，取符合条件的 N 条记录，减少不必要的 IO

• count 优化

  选择索引 key_len 最短的二级索引效率高，不要使用全表扫描（PK 聚族索引会全表扫描），因为索引 key_len 越短，读取页面越少，进而 IO_COST 越小。

• bad SQL 优化

  • select / update / delete 没有使用 where 或者 where 中无索引字段，造成全表扫描和加标记锁
  • where 的字段产生了隐式转换，无法使用索引，导致全表扫描
  • 关联的两个列的字符集不同，同样无法使用索引，如 column_utf8mb4 = column_utf8

2.4 突破单库性能瓶颈

2.4.1 硬件优化

2.4.1.1 CPU

• MySQL 支持多核，5.6 版本可以支持 64 核，后续版本能支持的核数更多

• MySQL 每个并发 Query 只能使用一个核，MySQL 引入了 Thread Pool 功能来提升性能

• 通过参数 thread_handing 可以查看线程池模型

  • one-connection-per-thread（MariaDB Thread Pool）

    • 5.6 之前的版本采用一个数据库连接对应一个独立的线程服务，处理请求后销毁线程
    • 当连接数暴涨时，Thread Pool 初始化的线程会很快耗尽，会退化为单线程并创建更多的线程，最终导致服务出现抖动

  • one-thread-per-connection（Percona Thread Pool，推荐）

    • 一个线程对应一个连接，线程处理的最小单位是 statement（SQL语句），所以一个线程可以处理多个连接请求

    • 工作原理

      

      1.创建多个 Group（由参数 thread_pool_size 指定，一般设置为 CPU core 数量），每个 Group 可以有多个 woker

      2.线程根据 connection id 被分配到 Group（生命周期内不变），woker 以 SQL 为单位进行处理，保证每个连接都能及时得到响应

      3.每个 Group 有两个任务队列，优先队列用于存放已开启事务的 SQL，保证事务优先被处理，尽早释放锁；优先队列为空时才处理普通队列；相比 MariaDB Thread Pool，优先队列可以避免调度上的死锁

      4.额外创建一个 Timer 线程，定期检查 Groups，若发现 woker 异常（堵塞/超时/woker 线程数不够），则及时唤醒

      5.若 Group 任务队列为空（客户端连接却不为空），为空闲连接设置一个超时期限，之后将连接的 socket fd 绑定到 Group 中的 epollfd，线程则调用 epoll_wait() 批量取任务

• 服务器 CPU 硬件优化总结

  • 系统配置选择 Performance Per Watt Optimized（DAPC），发挥最大功耗性能，而不是节能模式（高运算节点禁用），节能模式在低高频性能转换时易出现Bug
  • 选择高频 CPU 以提高运算能力，选择多核 CPU 以提升多线程并发处理能力
  • 开启最大性能和关闭 CPU CIE 和 C States。 高频加速 SQL 执行，多核解决并发

2.4.1.2 内存

• 单机单实例建议分配机器的 50% ~ 80% 物理内存，单机多实例建议按实际热点数据量的 30% 进行规划

• 关闭 Query Cache（8.0 后已移除）

• 内存的优化建议

  • 内存频率选择 Maximum Performance（最佳性能)
  • 大内存，renice mysql pid 避免 OOM 时 MySQL 被强杀
  • 内存设置菜单中，启用 Node Interleaving 避免 NUMA 问题，同时建议修改系统配置关闭NUMA

2.4.1.3 磁盘

• 数据库是 IO 型负载的软件，优先使用 SSD、PCIe SSD，最后才是高转速机械硬盘
• IO 类型
  • MySQL Undo Log 是顺序写随机读， Redo Log 和 Binlog 是顺序写顺序读
  • OLTP 场景以随机 IO 为主，OLAP 场景以顺序 IO 为主
• MySQL 对磁盘优化原则
  • RAID 策略选择： Sata SSD、PCIe SSD 无需 RAID，机械硬盘优先选择 RAID10，其次是 RAID5
  • RAID CACHE & BBU 选择：购置阵列卡同时配备 CACHE 及 BBU 模块，可提升机械盘 IOPS，定期检查或监控 CACHE 及 BBU 模块的健康状况，确保意外时不至于丢失数据
  • 磁盘类型选择：优先选择 SSD 或 PCIe SSD，机械盘使用高速硬盘
  • 读写策略选择：有阵列卡时设置阵列写策略为 WB 或 Force WB with no battery，严禁 WT。同时关闭陈列预读策略，只用作写缓存

2.4.2 参数优化

2.4.2.1 系统全局内存参数（SGA）

• Innodb_buffer_pool_size，用于缓存数据、索引数据，以及事务所和自适应哈希等，单机单实例建议分配机器的 50% ~ 80% 物理内存，单机多实例建议按实际热点数据量的 30% 进行规划
• innodb_buffer_pool_instances，开启多个内存缓冲池，把数据 hash 到不同的缓冲池，可以并行读写
• innodb_additional_mem_pool_size，用于缓存所有数据字典
• innodb_log_buffer_size ，InnoDB Redo日志缓冲，提高Redo日志写入效率
• key_buffer_size，MyISAM 表索引高速缓冲，提高 MyISAM 表索引读写效率
• query_cache_size，查询缓存，缓存相同SQL查询结果，提高查询结果返回效率，建议禁用
• table_cache && table_definiton_cache，表空间文件描述符缓存和表定义文件描述符缓存，提供数据表打开效率

2.4.2.2 线程全局内存参数（PGA）

• innodb_flush_log_at_trx_commit，控制 Redo Log 刷盘策略

  

  不同取值时对应与日志缓冲区、OS cache、日志文件（ib_logfile）之间的关系

  

• sync_binlog，控制 Binlog 刷盘策略

  sync_log	写入机制	数据库宕机	OS 宕机
  0	写 Binlog 文件（OS Cache），文件系统控制刷盘策略	Binlog 不会丢失	Binglog cache 的 Binlog 丢失
  1	事务提交则 Binlog 写文件并刷盘	无数据丢失	无数据丢失
  N	每 N 次事务提交则 Binlog 写文件并刷盘	可能事务丢失	可能事务丢失

• 针对写入压力大的 OLTP 场景，扩容 Redo Log 有助于提升写入性能，涉及log 的相关参数

  • innodb_flush_log_at_timeout：每隔 N 秒写入并刷新日志，默认为 1 即每秒 flush一次,可选 [1-2700]。该参数值允许增加 flush 之间的间隔以减少刷新，避免影响二进制日志组提交的性能
  • innodb_log_file_size：日志文件大小，建议设置1～2GB
  • innodb_log_files_in_group：日志文件组个数

2.4.2.3 MySQL参数优化总结

2.4.3 架构优化 - 复制

2.4.3.1 MySQL 复制原理

• 复制过程分为三个步骤
  • 主库 Master 将数据库的变更操作记录在二进制日志 Binary Log 中
  • 备库 Slave 读取主库上的日志并写入到本地中继日志 Relay Log 中
  • 备库读取中继日志 Relay Log 中的 Event 事件在备库上进行重放 Replay

2.4.3.2 分类

• 异步复制

  • 主从同步时，主库事务写入分为 4 个步骤

    1. InnoDB Redo File Write (Prepare Write)

    2. Binlog File Flush & Sync to Binlog File

    3. InnoDB Redo File Commit（Commit Write）

    4. Send Binlog to Slave

  • 根据 Master 是否关注 Save 以及何时关注 Save 的 ACK，异步复制可以分为三种

    • 当 Master 不需要关注 Slave 是否接受到 Binlog Event 时，即为传统的主从复制
    • 当 Master 需要在第三步等待 Slave 返回 ACK 时，即为 after-commit
    • 当 Master 需要在第二步等待 Slave 返回 ACK 时，即为 after-sync

• 同步复制

  • Galera Cluster
  • MySQL Group Replication

2.4.3.3 主从复制

• 整个主从复制过程涉及三个 Thread，分别是 Master 的 Binlog Dump Thred，和 Slave 的 I/O Thread 和 SQL Thread

  • Master 服务器上对数据库的变更操作记录在 Binlog 中
  • Master 的 Binlog Dump Thread 接到写入请求后读取 Binlog 推送给 Slave I/O Thread
  • Slave I/O Thread 将读取的 Binlog 写入到本地 relay log 文件
  • Slave SQL thread 检测到 relay log 的变更请求，解析 relay log 并在从库上进行应用

• 主从复制也叫异步复制，存在数据延迟

  Master 数据变更后记录 Binlog，只是通知 Binlog Dump Thread 有数据写入后就告诉存储引擎提交事务，并不会关注 Slave 是否接受并落地 Binlog Event。下面是异步复制的时序图

  

2.4.3.4 after-commit

• 定义

  MySQL Master 将事务写入 Binlog（sync_binlog=1）并推送给 Slave 后主库将事务提交到存储引擎（此时未返回客户端但是其他会话可以访问到事务提交信息），Slave I/O Thread 将读取的 Binlog 写入到本地 relay log 文件（sync_relay=1）后向 Master 返回 ACK 消息，当主库 Master 等到 Slave 返回的 ACK 消息后 Master 将事务提交成功的结果返回给客户端。

• 对于当前会话的客户端进行事务提交后，主库等待 ACK 的过程中有两种情况

  • 事务还没发送到从库，主库 crash 并发起切换，从库为新主库。客户端收到事务提交失败的信息，需要重新提交该事务。（如果 原主库 crash 后无法重启，那么会导致数据丢失）

  • 事务已经发送到从库，主库 crash 并发起切换，从库为新主库。从库已经应用该事务并写入数据，但客户端连接重置同样会收到事务提交失败的信息，重新提交该事务时会报错数据已存在（如订单已提交成功）。

2.4.3.5 after-sync（无损复制）

• 为了提升数据的安全性，MySQL 5.7 引入了增强半同步 after_sync（无损复制），并将其设置为默认的半同步方式来解决数据丢失的问题

• after-sync 是将 Master 等待 ACK 消息放到了 Binlog File Flush & Sync to Binlog File 之后，Engine Commit 之前，这样就可以保证数据不会丢失，因为 Slave 接受到event 并写入自身 relay log

• 运维过程中有个关键点需要注意：当半同步等待 ACK 超时时，半同步复制会退化为异步复制（主从复制）

• 下图是 MySQL 官方对于半同步复制的时序图，主库等待从库写入 relay log 并返回 ACK 后才进行 Engine Commit

  

2.4.3.6 Galera Cluster

Galera Cluster是一个强一致性集群，当集群节点有数据写入时，Group communication 会向组内所有成员广播写集（初步可简单理解为写入的Binlog），所有节点验证通过之后写节点开始提交，其他节点执行写集应用和提交，当出现数据冲突时则写节点执行回滚，其他节点丢弃该写集。

Galera Cluster 的实现有两个

• 一个是 MariaDB 实现的 MariaDB Galera Cluster - MGC
• 一个是 Percona 实现的 Percona XtraDB Cluster - PXC

2.4.3.7 MySQL Group Replication

MGR 同样是一个支持多点写入的多主复制架构，它基于原生 MySQL 主从复制的基础上构建组通信层，由 Group Replication 提供一组原子消息并且按照全局顺序进行消息传递，集群任何节点均可写入，但所有写入事务只有在获得复制组认证通过（多数派协议 Paxos）后才能进行提交。

如下是 MySQL Group Replication /Galera Cluster 的时序图：由 3 个节点组成一个复制组，Consensus 层为一致性协议层，在事务提交过程中，发生组间通信，由 2 个节点决议（certify）通过这个事务，事务才能够最终得以提交并响应。

2.4.4 经典架构和适用场景

2.4.4.1 主从复制

• 应用程序读写直接访问 Master 或者配置 Master 和 Slave 的数据源进行人为的读写分离，当 Master 出现故障时无法自动切换，需要人工维护介入，因此通常会基于此架构加上 VIP/DNS + Keepalived 及双主复制来做一个简单的高可用切换

• 适合轻量级程序、高可用要求不高的业务场景

  

2.4.4.2 双主复制

• 一个 Master 提供线上服务，另一个 Master 作为 Standby 供高可用切换，Master 下游挂载 Slave 承担读请求，配置 VIP/DNS+Keepalived 或使用业内早期的 MMM 架构

• MMM 架构提供了单点判断的 Monitor，由它来判断 Master 的存活并进行 VIP 的漂移，MMM 的优点是基于 MySQL 原生复制，其工具集功能强大，提供了一套 HA、Failover 的 tools 来帮助运维

• MMM 缺点是架构比较落伍且长期不更新（导致很多 MySQL 的新特性无法支持，例如 GTID），同时由于 MMM 是单点判断并没有 watch dog 守护进程，对于网络分区或网络抖动的场景会出现集群脑裂，当出现业务在两边数据同时写入时会出现写入冲突甚至数据错乱的问题。最大的问题是 MMM 备选主延迟过大会导致无法切换，不提供 binlog 补偿的功能。

• 该架构不适用于对数据一致性要求高的业务场景，适用于能够容忍网络抖动导致数据冲突和不可用、容忍数据丢失的应用场景，其架构扩展和读写分离需要应用程序联调配合。

  

2.4.4.3 树形复制（级联复制）

• 适用于数据访问策略分层，例如 MySQL Master 和 MySQL Slave 参与线上业务访问及高可用切换，MySQL Statistic 节点提供离线查询、报表慢查和非线上业务访问

  

2.4.4.4 环形复制

• 基于原生 MySQL Replication 构建的环形复制由于构建费力不讨好、运维复杂不友好等原因不在介绍范围内。环形复制重点在于 MySQL Group Replication 和 Galera Cluster。
• 这两种架构均可满足保证集群节点数据强一致性、多点写入的需求，适用于金融场景及对数据一致性要求高的业务场景

3. 高可用 MySQL 最佳实践

3.1 MySQL 高可用方案

3.1.1 什么是高可用性

3.1.1.1 计量指标

• 平均故障间隔时间（平均无故障时间） MTBF

• 平均恢复时间 MTTR

• 可用性 = MTBF / (MTBF+MTTR)

  

3.1.1.2 提升 MTBF

• 规范上线操作流程

  开发环境、测试环境及生产环境要严格区分开，禁止混合部署，禁止未经测试的功能或任意变更直接上线生产

• 严格审核线上变更操作

  数据库线上变更操作需要指定审核及执行流程

• 合理参数优化

• 定期巡检并修复问题

  建议开发巡检平台对数据库服务器和数据库进行深度巡检，如服务器磁盘空间是否富余、数据库表碎片率是否超过 50%

• 慢查询优化分析

  线上慢查询需要每日抓取分析，并发送优化建议给相关业务方，促进慢查询优化

• 加强监控告警

  完善操作系统的监控指标、数据库的监控指标

• 危险命令转义执行

  如\rm -rf

• 系统过载保护

  超时后记录查询 SQL 信息并使用 PT-KILL 工具自动进行 query kill，事后进行针对性慢查询优化

• 高可用架构的正确选型

  少使用基于Keepalive + VIP + 多主的架构或 MMM 架构，使用改良后的 MHA 或自研数据库高可用守护系统

• 严守开发及运维规范

• 完善工具集

• 引进数据库专家服务

3.1.1.3 降低 MTTR

• 拒绝单点
• 故障演练
• 高可用守护系统
• 自动化运维管理平台

3.1.2 避免单点失效

3.1.2.1 基础软硬件避免单点

• 数据库高可用集群至少一主一从

• 数据库集群节点不在同一机柜，不在同一交换机，不共用同一电源

• 避免光缆被挖，注意机房布线

3.1.2.2 MySQL 高可用架构选型

• MMM（Keepalived + VIP + MySQL 主从/双主）

  

  • 介绍

    数据库集群节点各绑定了一个 VIP，VIP 赋有读节点、写节点的逻辑概念，应用程序使用读写 VIP 来配置读写数据源，当写节点 Master db1 出现故障时，由 MMM Monitor 或 Keepalived 触发切换脚本，将 VIP 漂移到可用的 Master db2 上

  • 问题

    • 当出现网络抖动或网络分区时，MMM Monitor 会误判，严重时来回切换写 VIP 导致集群双写
    • 当数据复制延迟时，应用程序会出现数据错乱或数据冲突的故障
    • MMM Monitor 是单点判断的，存在单点故障的问题

  • 优缺点对比

    

• MHA

  

  • 介绍

    整个 MHA 架构分为 MHA Manager 节点和 MHA Node 节点，其中 MHA Manager 节点是单点部署，MHA Node 节点是部署在每个需要监控的 MySQL 集群节点上的。MHA Manager 会定时探测集群中的 Master 节点，当 Master 出现故障时，它可以自动将最新数据的 Standby Master 或 Slave 提升为新的 Master，然后将其他的 Slave 重新指向新的 Master。

  • MHA 对比 MMM

    • 支持延迟数据补偿（最大保证）
    • 支持版同步
    • 支持 GTID 新特性
    • 更强的工具集，能做健康检查、集群节点挂载等运维工作

  • 问题

    • MHA Manager 和 MMM Monitor 一样，都没有 watch dog 守护进程，存在单点故障的问题，应该使用多点判断

    • 集群脑裂

      

      当一部分应用程序 Client 和 Master 形成内部网络，而 Manager、Slave、Standby 及其他 Client 端组成内部网络时。首先集群的复制状态中断，两边数据不一致。其次 Manager 会认为 Master 已经 Crash，会发起故障切换，例如将 Standby Master 提升为新主库，将 Slave 自动 change master 挂载到 Standby Master 节点下形成新的数据库集群。

    • 数据丢失

      当出现 Master 和 Standby（最新的 Slave）节点所处的服务器Crash 或组成内部网时，Manager 只能提升为 Slave（数据非最新，存在延迟），并为新 Master 提供服务，这就导致数据丢失。虽然 MHA 可以使用半同步复制来保证数据安全，但是半同步复制在网络抖动时同样是会存在退化为异步复制的风险。

• MHA + Arksentinel

  用分布式哨兵集群解决 MHA Manager 单点判断问题

• QMHA（加强版的 MHA）

  

  • 介绍

    QMHA 集群能够满足数据一致性的需求，支持跨机房部署，但不支持多点写入。通过分布式哨兵选举投票来减少误切换，发起切换时会比对 GTID 来进行主从数据的快速比对，然后进行数据补齐。同时使用 Semi Sync 半同步复制提高数据安全性。

  • 执行流程

    1. 应用程序加载 DBA 提供的 Jar 包，配置读写数据源为全局 namespace。 每个 namespace对应一个后端 MySQL 集群。
    2. 应用程序通过 namepace 配置中心获取对应集群的读写节点信息后进行数据库集群读写访问。
    3. 分布式哨兵集群实时监控线上的 MySQL 集群，当线上 MySQL 集群发生切换时，分布式哨兵会更新配置中心的配置（例如原 Master 下线，新 Master下线)，同时通知 ZK 集群信息发生变更。
    4. ZK 获取信息变更时会通知 Java 客户端，应用程序会重新获取 namesapce 的信息，按照最新的读写节点信息重建连接池后进行数据库访问。

• PXC/MGR

  

  • 介绍

    对于 MySQL，PXC 集群和 MGR 集群同样可以使用分布式监控哨兵进行集群高可用性守护。相比传统主从复制，PXC 集群和 MGR 集群支持多点写入，更完美得满足了高可用切换的需求，集群自身保证数据强一致性，不需要额外进行数据补齐操作。

  • 执行流程

    1. 应用程序加载 DBA 提供的 Jar 包，即运行 DBA 提供的 java proxy 客户端。 同时通过全局namespace 查询配置中心获取集群最新读写节点的 IP 和 Port 后，业务正常读写访问MySQL 集群。
    2. 分布式哨兵实时监控线上 MySQL 集群，当集群出现故障发生切换后，分布式哨兵会更新配置中心的配置（例如原 Master 下线，新 Master 下线），同时修改 namesapce 的版本信息来通知 ZK 集群信息发生变更。
    3. ZK 获取到信息变更时会通知 Java 客户端，应用程序会重新获取 namesapce 的信息，按照最新的读写节点信息重建连接池后进行数据库访问。

  • 问题

    QMHA 和 PXC/MGR 这两种高可用方案的唯一缺点在于需要应用程序引入 Jar 包，面对非 Java 应用程序需要定制开发

  • 解决

    引入一款高度兼容 MySQL 语法和原生协议的数据库中间件，中间件只负责透明读写分离和负载均衡，它对应用程序屏蔽后端复杂的高可用集群架构，中间层对外提供统一的访问入口，无论数据库是 MS / MGR / PXC / MM 架构，对应用程序而言都像访问单机 MySQL 一样。

3.1.2.3 高可用守护系统

通过数据库中间件提供透明读写分离及负载均衡，分布式哨兵集群对故障节点进行切换处理后告知数据库中间件后端集群最新的节点上下线信息，中间件支持动态 reload 后端集群配置及支持连接池的特性，能够对前端应用程序的连接进行平滑的迁移。

3.1.2.4 共享存储

通过分布式存储拥有的数据多副本、快照备份及恢复等功能特性保证数据存储节点的高可用性。

3.1.3 故障转移

• 虚拟 IP 或 DNS 服务，对应 Keepalived +VIP/DNS 和 MMM 架构；
• 提升备库为主库，对应 MHA、QMHA、PXC 及高可用守护系统；
• 中间件层负载均衡，如 LVS 或 Arkproxy 中间层。

3.1.4 故障恢复

• 虚拟 IP 或 DNS 服务

  在虚拟 IP 运维过程中，有时会出现一个 VIP 绑定在多台服务器同时提供连接的问题。这时需要刷新 ARP，这也是为什么要避免使用 Keepalived+VIP 和 MMM 架构的原因之一，因为它处理不了这类问题而导致集群多点写入。

• 提升备库为主库，需要关注以下几点

  • 尝试将原 Master 设置 read_only 为 on，避免集群多点写入。

  • 当出现数据延迟时，再提升 Slave 为新 Master 之前需要进行数据补齐，否则会丢失数据。

    • 可以借助 binlog server 保留 Master 的 Binlog；

    • 可以借助 Semi Sync 半同步复制最大限度保证数据的安全性

    • 可以借助 GTID 快速比对差异数据，相比 file、postion 的复制方式能加快数据补齐的速度。

  • 使用同步集群，Galera Cluster 和 MGR 集群多点写入及数据强一致性的特性，架构层次即可以天然解决前两个问题点。

3.2 搭建稳固的MySQL运维体系

3.2.1 监控要点

3.2.1.1 监控告警方向

• 监控策略

  每个监控项的告警阈值，例如 threads_running > 30 触发报警

• 监控趋势图

  对每个时间点，项目所采集数据的图形展现，基于历史数据的比对能够快速发现异常的监控项

• 报警方式

  按需配置，Email、电话或企业微信等均可

3.2.1.2 服务器监控要点

在采集服务器监控信息时，可以通过系统文件进行历史数据采集。

• 采集服务器的登录用户数，使用 who | wc -l 进行数据采集入库
• 监控 CPU 使用情况，通过系统文件 /proc/stat 计算获取监控项：user、idle、iowait、sys，获取监控项后进行数据采集入库
• 监控 load 信息，通过系统文件 /proc/loadavg 计算获取 load1、load5、load15
• 监控 IO 使用情况，通过系统文件 /proc/diskstats 计算获取 ioutil、iops、write_mb、read_mb
• 监控磁盘空间信息，通过 df–m 获取信息，其中 Avail：可用空间，Used：已使用空间，Size：总空间
• 监控内存使用情况，通过系统文件 /proc/meminfo 计算获取 used 、free、buffer、cache、total
• 监控虚拟内存信息，通过 free –m|grep swap 即可采集
• 监控 TCP 信息，通过系统文件 /proc/net/tcp 计算获取 close、connected、listen、syn
• 监控 Net 信息，通过系统文件 /proc/net/dev 计算获取，recv 接收数据速度，send 发送数据速度。

3.2.1.3 MySQL 监控要点

• MySQL 大部分的监控取值来源于 MySQL 运行状态值

  监控项	采集方式
  数据库连接情况	Threads_connected, Threads_cached, Threads_created, Threads_running
  数据库 QPS	show global status like ‘%Queries%’，QPS = △Queries / △Seconds
  数据库 TPS	TPS = (com_commit + com_rollback + com_xa_commit + com_xa_rollback) / Seconds
  数据库每秒操作命令的次数	采集数据库状态值 com_select, com_deleted, com_insert, com_update, com_update_multi, com_insert_select
  InnoDB 表操作行数	Innodb_rows_read, Innodb_rows_deleted, Innodb_rows_inserted, Innodb_rows_update
  server 发送 / 接收数据量	Bytes_sent：发送给客户端的数据量 Bytes_received：收到客户端的数据量
  表锁监控	Table_locks_immediate：申请时立刻获取表锁的次数 Table_locks_waited：申请表锁时等待的次数
  行锁监控	Innodb_row_lock_time：等待行锁的总时间 Innodb_row_lock_time_avg：平均等待行锁时间 Innodb_row_lock_time_max：等待行锁最长时间 Innodb_row_lock_waits：等待行锁的总次数 Innodb_row_lock_current_waits：当前等待行锁数量
  Buffer Pool 页信息监控	Innodb_buffer_pool_pages_misc：用于存储行锁、自适应哈希索引等信息的管理层的页数 Innodb_buffer_pool_pages_free：Buffer Pool 空闲的页数 Innodb_buffer_pool_pages_made_young：标记为 young 的页数 Innodb_buffer_pool_pages_made_not_yound：进入 Buffer Pool 后未被标记为 yound 的页数 Innodb_buffer_pool_pages_old：在 Buffer Pool LRU old 段的页数 Innodb_buffer_pool_pages_flushed：请求 flush pages 的次数 Innodb_buffer_pool_pages_LRU_flushed：？？ Innodb_buffer_pool_pages_total：Buffer Pool 总页数 Innodb_buffer_pool_pages_data：Buffer Pool 包含数据的页数（包括 drity 和 clean 页） Innodb_buffer_pool_pages_dirty：Buffer Pool 脏页数
  Redo Log 监控	Innodb_os_log_fsync：调用 fsync() writes 写 Redo Log 的次数 Innodb_os_log_written：写入 Redo Log 的字节数 Innodb_log_waits：log buffer 空闲空间不足，等待刷盘次数 Innodb_log_write_requests：写 Redo Log 的请求次数 Innodb_log_writes：写 Redo Log 的次数
  Replication 监控	Seconds_Behind_Master：复制延迟秒数 Read_Master_Log_Pos：读取主节点 binlog 的位置 Exec_Master_Log_Posdemo：执行到主节点 binlog 的位置
  semi-Replication 监控	Rpl_semi_sync_master_net_avg_wait_time：master 等待 salve 回复的平均等待时间 Rpl_semi_sync_master_tx_avg_wait_time：master 花在每个事务上的平均等待时间 Rpl_semi_sync_master_no_tx：msater 没有收到 slave 回复就提交的次数 Rpl_semi_sync_master_yes_tx：master 收到 slave 回复的次数 Rpl_semi_sync_master_clients：半同步复制从库个数 Rpl_semi_sync_master_wait_sessions：当前因为 slave 回复而造成等待的 session 个数 Rpl_semi_sync_master_no_times：master 关闭半同步复制的次数
  PXC 监控	wsrep_cluster_status：集群节点是否正常服务，OFF 表示为脑裂 wsrep_connected：节点是否连接到集群，ON / OFF wsrep_ready：集群节点是否正常可用 wsrep_local_status：Joining-1，Donor/Desynced-2，Joined-3，Synced-4 wsrep_replicated_bytes：节点传输给其他节点写集的字节数 wsrep_replicated：节点传输给其他节点的写集个数 wsrep_received_bytes：节点从其他节点接收写集的字节数 wsrep_received：节点从其他节点接收写集的个数 wsrep_cluster_size：集群节点个数 wsrep_%flow%：限流相关

3.2.1.4 监控系统

• 常用监控系统

  

• 自研监控告警系统

  

  推送模式在每个需要采集的服务器上部署 Agent 采集程序，Agent 采集程序定期将采集的监控数据推送给配置中心或元数据存储库进行持久化。

  拉取模式由中控机向远程服务器发送采集监控信息的命令，将采集信息拉取到配置中心或元数据存储库进行持久化。

  最后通过 Grafana 进行监控数据展示。

3.2.2 SQL 审核执行

3.2.2.1 人肉审批流程

传统的人肉 SQL 审核通常是这样的，借助于邮件，DBA 或运维基于个人的工作经验和"火眼金睛"对变更脚本的 SQL 进行人肉审核。通常需要审核如下内容：

• SQL 语句是否有语法错误
• SQL 语句是否符合开发规范和运维规则
• 执行前环境检查，确认变更的数据源
• 执行前备份
• 执行中出错，查找出错的地方
• 执行后检查是否变更成功

3.2.2.2 自动化审批流程

借助数据库自动化运维平台，如阿里云 DMS

3.2.3 备份恢复

3.2.3.1 常见问题

• 本地存储备份，磁盘空间 double，不仅造成资源浪费，而且放大了备份对线上业务的影响，关键是存在安全隐患，服务器 crash 若无法恢复，则数据库实例和备份集均无法使用。
• 备份手段很多，每个 DBA 有自己的喜好。如果没有集中化管理，而是多样化进行备份，维护困难。
• 备份文件校验，备份文件不做校验通常是最大问题也是最容易忽视的问题，以为备份完成就成功了，恢复时发现出现备份文件无法恢复的问题。
• 集群备份节点选择问题。备份或多或少对线上业务有影响，DBA 建议备份部署在 slave 或 statistic 节点上，那么当集群发生主从切换，如果备份节点没有动态进行切换，导致在写库上进行备份，使线上业务受备份影响。
• 流量和压缩。备份传输引发网卡流量限速和压缩对线上业务造成影响。

3.2.3.2 关注点

• 对于备份恢复策略，可以集中统一使用物理备份和逻辑备份相结合的方式
• 备份统一使用流备份进行远程备份，同时提前 apply log 进行备份文件校验
• 恢复策略可以支持通过备份集新建示例、通过备份集挂载线上集群 Slave、指定时间点恢复和 FlashBack
• 备份策略支持多通道，方便扩展
• 备份存储，建议是本地无备份、备份通道机存储最近 N 次备份、历史备份集压缩存储到备份池。备份池使用 MFS 是一个不错的选择，MFS 是一种容错的分布式文件系统，将数据切分成多个数据副本存储在不同的计算机节点里，备份文件的高可用性得到极大的保证
• 在备份过程中备份文件提前通过 apply log 进行了恢复，即达到了备份文件校验的同时又加快后期备份恢复的速度

4. 高拓展 MySQL 最佳实践

4.1 架构设计

4.1.1 架构设计方法论

• Shared Everything

  一般是针对单个系统，完全透明共享 CPU/MEMORY/IO，并行处理能力是最差的，典型的代表 SQL Server。

• Shared Nothing

  系统中的各个处理单元都有私有的 CPU/MEMORY/IO 等，不存在共享资源，类似于 MPP（大规模并行处理）模式，各处理单元之间相互独立，各自处理自己的数据，它们之间通过协议通信，处理后的结果或向上层汇总或在节点间流转。这种方式的并行处理和扩展能力更好。典型的代表 DB2 DPF、Hadoop、GreenPlum 等。

• Shared Disk

  系统中的各个处理单元使用私有 CPU 和 MEMORY，共享磁盘系统。典型的代表 Oracle Rac、AWS Aurora 和极数云舟自主研发的 ArkDB 等，它们都是数据共享，可通过增加节点来提高并行处理的能力，做到了计算与存储分离，扩展能力较好。

4.1.2 基本方法

4.1.2.1 垂直扩展

• 增强单机硬件性能

  • 增加 CPU 核数提升并发处理能力
  • 选购主频较高的 CPU 来提高运算能力
  • 增加内存大小，缓存更多的数据，减少物理 IO
  • 提升网卡为万兆网卡，当然交换机也需匹配万兆交换机，如 InfiniBand
  • 扩展性能更好的存储介质，如 PCIe 或 NVME SSD 等，提升 IOPS

• 提升单机架构性能，主要是通过外部的架构优化来提升单机的处理性能或者是扩展单机的处理能力

  • 针对读多写少的场景，使用 Cache 减少 IO 次数，拓展读性能

    在数据库和应用的中间增加一层缓存 Cache，数据持久化在 DB 中，用 Memcached、Redis 等缓存热数据。

  • 针对读少写多的场景，使用消息队列提升吞吐量，拓展写性能

    在数据库和应用的中间增加一层消息队列，应用先写队列，然后用异步的方式更新到数据库中，消息队列可以是 Kafka、RabbitMQ、RocketMQ 等。

• 使用无锁数据结构提升性能

  非阻塞算法保证为共享资源竞争的线程，不会通过互斥让它们的执行无限期暂停

• 使用分区拓展数据存储和提高查询吞吐量

  
  • 使用分区的优点
    • 分区可以把数据打散存储到不同的文件系统上，和单个磁盘或者文件系统相比，可以存储更多的数据。
    • 优化查询。where 子句包含分区条件时，可以只扫描对应分区，缩小了查询范围。同时在涉及 count() 和 sum() 等聚合函数时，可以在多个分区上并行处理。
    • 对于已经过期或不需要的数据，可以通过删除分区快速删除。
    • 跨多个磁盘来分散数据查询，以获得更大的查询吞吐量。
  • 使用分区的限制
    • 一个表最多只能有 1024 个分区，MySQL 5.6 之后支持 8192 个分区。
    • 如果分区字段中有主键或者唯一索引列，那么所有主键列和唯一索引列都必须包含进来，如果表中有主键或唯一索引，那么分区键必须是主键或唯一索引。
    • MySQL 分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对一个表的部分数据进行分区。
    • 无论使用何种分区类型，不能使用主键/唯一键之外的其他字段进行分区。
    • 分区表中无法使用外键约束。
    • MySQL 数据库支持的分区类型为水平分区，并不支持垂直分区，因此，MySQL 数据库的分区中索引是局部分区索引，一个分区中既存放了数据又存放了索引，而全局分区是指数据库放在各个分区中，但是所有的数据的索引放在另外一个对象中。
    • 目前 MySQL 不支持对空间类型和临时表类型进行分区。不支持全文索引。

4.1.2.2 水平扩展

• 主从复制

  
  • 通过主从复制来扩展从库，从而提升读性能。
  • 不足：如果写的操作过多，导致 master 往往成为性能的瓶颈，从而使得 slaves 的数据同步延迟也变大，进而大大消耗 CPU 的资源，并导致数据的不一致从而影响用户的体验。

• 分库分表

  
  • 这个又可以称之为“数据分片（Sharding）”。例如：拆分实例、分库、分表，达到线性扩展的目的。
  • 缺陷
    • 加大了应用代码的复杂性，需要路由到正确的 shard。
    • 后期增加 shard 需要修改应用逻辑，并需要迁移数据，工作量比较大。
    • 查询和聚集（aggregation）不再简单，需要跨库联合操作。
    • 主数据和参照数据需要复制到所有 shard，以避免跨库操作。主数据和参照数据虽然偏静态，但一旦修改，可能会存在数据不一致性问题。
    • 跨库修改需要分布式交易处理，会限制可扩展性，因此应尽量避免。
    • 单个 shard 的失效可能会使整个系统不可用，因此通常需要为每个 shard再配置 HA 方案，如主从复制。
  • 分片和分区的区别
    • 分片（Sharding）是库外的
    • 分区（Partitioning）是库内的

• 数据库中间件

  

• 集群

  例如使用 MNC（MySQL NDB Cluster）、MGR（MySQL Group Replication）、PXC（Percona XtraDB Cluster）、MGC（MySQL Galera Cluster） 集群来弥补单机性能的不足。

4.1.2.3 分布式 Distributed

• 基于分布式事务的数据库，以 Google Cloud Spanner 和 PingCAP 的 TiDB 为代表。
• 基于分布式存储的数据库，以 AWS 的 Aurora 和极数云舟的 ArkDB 为代表。

4.2 从不同视角梳理架构

• 从业务视角

  需要去了解业务的特点、业务架构和访问模型、业务数据流向、业务技术栈、业务数据规模（QPS、TPS、并发情况），找出瓶颈和痛点

• 从资源视角

  需要去进行资源梳理（MySQL），掌握资源，针对存在的不足，加大并持续投入。

• 从运营视角

  化繁为简，大行其道，利用优势资源和条件去优化，去实践。

  对于数据运营建议要做到“八化”，即：平台化、自动化、工具化、服务化、流程化、可视化、容器化、智能化。

  此外还需要掌握一定的方法和工具。比如：扩容、降级、切流量、限流、过载保护、链路压测、故障演练和故障复盘等，做到事前有预案，事后有总结。

5. 整体架构

5.1 MySQL 典型数据库架构

5.1.1 主从架构

• 传统主从复制

  有时候也称为异步复制，优点是架构简单，部署方便。（ MySQL 中的各种存储引擎，要注意它们的特性）

  

• 基于 GTID 的主从复制

  从 MySQL 5.6 版本后，推荐使用这种方式的复制，原因是在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务。

  

• 主主复制

  这个还有不少传统企业仍在使用。

  

• 级连复制

  面试的时候特别容易问到关于复制的各种变换，用的就是级连复制，注意技巧，工作中也经常用。

  

• 多源复制

  MySQL 5.7 版本的一个特性，在某些特殊场景中会用到。

  

• 延迟复制

  备份中会用到，尤其是当数据量特别大的情况。

  

• 半同步复制

  对数据一致性要求比较高的业务场景，可以考虑用。面试中被问的也是最多的，它是通过插件的方式工作在数据库中。

  

  AFTER_COMMIT 和 AFTER_SYNC 模式的区别

  

5.1.2 集群架构

• MySQL Group Replication

  MGR 分单主模式和多主模式

  在单主模式下， 组复制具有自动选主功能，每次只有一个 server 成员接受读写。

  在多主模式下，所有的 server 成员都可以同时接受读写。

  

• Percona XtraDB Cluster

  具有高可用性，方便扩展。

  可以实现多个 MySQL 节点间的数据同步复制与读写，可保障数据库的服务高可用及数据强一致性。

  跟 MGR 一样，也分单主模式和多主模式。

  

• MySQL Galera Cluster

  Galera Cluster 解决的最核心问题是在三个实例节点之间，它们能以对等的，multi-master（多主）并存的方式存在，在多节点同时写入的时候，能够保证整个集群数据的一致性、完整性与正确性。

  具有支持多主架构、同步复制、并发复制、故障切换、热插拔、自动节点克隆和对应用透明的特点。

  

• MySQL NDB Cluster（ MySQL Cluster）

  MySQL NDB Cluster 是 MySQL 官方推出的一个适用于分布式计算环境的高可用性、高冗余的 MySQL 集群解决方案。

  主要由使用 NDB 引擎的 SQL 节点、数据存储节点和 NDB 管理节点三部分组成。

  NDB 是一个内存存储引擎，提供高可用的数据持久化功能，可以配置故障转移和负载均衡等策略，但管理复杂。

  

• MySQL + 共享存储方案

  MySQL 主从数据库共享同一份数据，但是在同一个集群中，只有一个库提供读写服务，其他的库提供只读服务，其性能也受限于分布式共享存储。

  在实际环境中基于 SAN 或 GlusterFS 的应用较多，但是前者成本昂贵，生产环境中使用的不多。

  

• MySQL + DRBD 方案

  DRBD 的全称是 Distributed ReplicatedBlock Device（分布式块设备复制），是一个用软件实现的、无共享的、服务器之间镜像块设备内容的存储复制解决方案。

  它的功能实现是由 Linux 系统中的 DRBD 内核模块和相关脚本构成的，用来构建存储高可用集群。跟上面共享存储方案有异曲同工之妙，只是实现方式有所不同。

  也是一写多读，生产中基本不用。

  

5.1.3 分布式架构

• 基于分布式事务的数据库，以 Google Cloud Spanner 和 PingCAP 的 TiDB 为代表。

  

• 基于分布式存储的数据库，以 AWS 的 Aurora 和极数云舟的 ArkDB 为代表。

  

5.2 数据库中间件

5.3 MySQL 主流数据库架构方案对比