《Mysql45讲》（一）基础篇关键点整理

1. mysql 架构

1.1. 逻辑架构

1.2. InnoDB 引擎架构

1.3. InnoDB 主从细节(右键看大图)

2. 贯穿：一条 SQL 语句是如何执行的？

1. Server 层：
   • 连接器(连接池)：密码校验、权限校验和读取(和连接相关)、客户端断连(wait_timeout设置)、长连接存在 OOM 可能(mysql_reset_connection重置连接)
   • 查询缓存：KV，mysql8 中移除，少用，表更新记录失效
   • 分析器：词法、语法分析、表字段不存在也是该阶段检测出来的、mysql8 错误码
   • 优化器：SQL 调优、多个索引决定使用哪个
   • 执行器：校对权限按优化器的执行计划执行
2. 引擎层，多款存储引擎组件：Myisam、InnoDB、Memory、CSV、Archive 归档、BlackHole 等

3. 日志：一条更新语句是如何执行的？

3.1. redolog 和 binlog

1. 更新涉及重要日志：
   • redolog - 物理日志(重做日志-引擎层)：
     • InnoDB 特有，在引擎层，记录的是物理日志(操作数据页)
     • 配置固定大小，例如 4 个文件，每个文件 1G，则总计 4G 日志
     • 日志循环记录，擦除[writepos, checkpoint]直接的内容，同时推进checkpoint
   • binlog - 逻辑日志(归档日志 - 服务层)
     • 所有引擎共有，在服务层，记录的是逻辑 SQL(SQL 语句)
     • 最开始 InnoDB 是第三方插件，而 mysql 仅支持 Myisam 引擎，后来融合
     • 同时仅有binlog，无法保证Crash Safe，需要依赖redolog
     • 日志追加记录
     • 两种格式：statement 记录源 sql 语句，row 格式记录记录行内容
2. WAL(Write-Ahead-Logging)技术，先写redolog日志，同时更新内存，再写磁盘；
3. Crash Safe能力：基于redolog

3.2. 两阶段提交

执行update T set c=c+1 where ID=2;，会存在两阶段执行过程：prepare+commit，即先写 redolog 再写 binlog

1. 执行器找InnoDB引擎，ID 为主键(聚簇索引），引擎查索引树，从磁盘读取得到数据页（若已在内存，直接返回）；
2. 执行器从数据页得到行记录，在内存中将c=c+1操作，然后调用InnoDB引擎写接口更新；
3. InnoDB引擎将行记录更新到内存，同时将更新操作记录到redolog，此时redolog为prepare状态，返回执行器，告知随时可以提交事务；
4. 执行器生成binlog写入磁盘
5. 执行器调用引擎事务提交接口，InnoDB引擎更改redolog为commit状态，整体操作完成

3.3. 数据备份

• 备份：定期全量：N 天、M 小时，低峰时间段备份
• 恢复：找故障前一天全量+从故障点后的binlog增量
• RTO：Disaster Recovery灾难恢复的目标时间，资源成本的考量

4. 事务隔离

4.1. ACID 特性

• A(Atomicity)原子性：事务是原子的(不可分割），要么全部完成，要么失败，没有局部完全的情况；
• C(Consistent)一致性：事务前后，数据库的完整性没有被破坏；
• I(Isolation)隔离性：多事务并发情况下，由于事务交叉可能导致的数据不一致的情况；
• D(Durability)持久性：事务提交后，数据修改就是永久的，即便系统故障也不会丢失；

4.2. 隔离级别

• RU：read uncommited，事务可以读到其他事务未提交的内容
• RC：read commited，事务可以读到其他事务已提交的内容
• RR：repeatable read，事务仅能读到自己启动事务时刻的数据
• S：serializable，串行化事务读写操作，加读、写锁

不同隔离级别，对并发性能有影响，从上到下隔离越严格，并发能力越低。 设定事务隔离级别：SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

4.3. MVCC

• 如果是可重复读隔离级别RR下，事务 T 启动时候，会创建一个READ-VIEW，但具体创建会有两种差异
  • 默认情况：BEGIN/START STANSACTION，这种情况是在执行第一个DML语句时候创建的！
  • 一致性快照启动：START TRANSACTION WITH CONSISTENT SNAPSHOT, 这种情况是在执行完成上述语句，就会启动一个一致性的视图，而不用等到第一个DML才生成！
• 多版本视图并发控制，导致了形成事务的不同隔离级别
• 数据表的一行记录存在多个版本，每个版本都有自己的row trx_id，基于undolog回滚日志，层层回滚。
• InnoDB 通过一个数组存放事务启动的瞬间相关的活跃事务 ID(未提交事务的状态)，组成了已提交-未提交-未开始三个事务集合的区间。对于当前启动的事务，若存在trx_id在未提交区间内，则不可见，反之在已提交区间则可见。
• 当前读，即读取已提交事务的最新值：
  • 特别注意，更新数据都是基于当前读current read，即读取最新的数据
  • 另外select ... for update或select ... lock in share mode加锁情况下，也是当前读
  • 不同事务，在更新同一行情况下会存在锁互斥，等到其他事务提交后，更新会基于当前读，读取取到最新的值
• tips: 避免长事务，长事务需要保存多个版本的回滚日志，消耗磁盘资源，同时还占用锁资源

可重复读(RR)核心就是基于 MVCC 实现在事务启动时候形成一致性读(Consistent Read)，而事务更新数据的时候，只能用当前读；如果当前的记录行锁被其他事务持有的话，就需要进入锁等待！ 读提交（RC)，查询仅承认在语句启动前就已提交完成的数据（不用等到第一条 DML)；

4.4. 症状

• 脏读：读到其他事务还没有提交的内容，读到的数据是脏的，可能没有被提交！ – RU级别存在脏读问题
• 不可重复读：读的的下一次数据，可能与上次一数据一致！– RU、RC级别都存在不可重复读问题
• 幻读：两次相同条件，读到不一样的的内容(其他事务插入或更新了相同范围内数据)，自己像幻觉一样！ – RU、RC、RR都可能发生

5. 索引

5.1. 索引类型/数据结构

• Hash：
  • 基于数组，利用 hash 函数，将 key 转化成数组位置，值存放在数据中该位置
  • 索引冲突：拉链法、再哈希
  • 适应于等值查询，无法查询区间范围
• 有序数组：适应于静态存储引擎，因为动态插入会移动大量有序数组的元素
• 二叉树：查询性能很高，O(logN)时间复杂度，但需要保持树的平衡；另外，二叉树索引结构，对机械磁盘读取数据页不友好，因为存在多次磁盘的 IO 操作，故 InnoDB 采用B+树结构，降低了磁盘访问频率！

5.2. B+索引树模型

• 主键索引：表基于索引树存放数据（聚簇索引）+（普通索引），新建一个索引就相当于新建一棵B+树
• 普通索引：若要查找的数据都在普通索引中包含，则无需回表，否需要回表
• 索引维护：
  • 页分裂：在插入过程中，可能引发插入到数据页，同时导致该数据页后续元素都要移动到一个新的数据页中的情况
  • 页合并：数据库执行，通过重建页排序，将节点下的数据页空洞修复
  • 数据页空洞，可能需要重建索引
• 索引长度问题：主键索引通常使用自增 ID 设定，NOT NULL PRIMARY KEY AUTO_INCREMENT，采用整型则只需要 4 个字节，长整型只需要 8 个字节，相比其他字段做主键，占用空间较小！

5.3. 覆盖索引

若要查找的数据都在普通索引中包含，则无需回表，否需要回表。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

5.4. 最左前缀规则

最左前缀规则是由B+树这种索引结构决定的，可以利用索引的“最左前缀”，来定位记录；

由于最左前缀规则存在，联合索引中字段的顺序尤为重要了，另外，联合索引还需要结合存储空间考虑；

5.5. 索引下推

满足索引的条件下，针对联合索引(name, age)，以下 SQL 查询：

1

select * from tuser where name like '张%' and age=10 and ismale=1;

检索名字为张的，但不满足age=10的都会被过滤掉，比如(张三, 11)，减少了回表次数。

原因是倘若符合张%的有 100 个，但age=10的仅有 10 个，则原来需要回表 100 次，现在通过索引下推只需要回表 10 次。

6. 锁

• 锁的范围：库、表、行
• DML(数据管理，增删改查)，DDL(数据定义，表结构修改定义)

6.1. 库级锁

• 应用：全库逻辑备份，不加全局锁的话，得到的不是一个一致性的视图（先备份余额表，再备份课程表；恢复的话则余额表有钱，课程表有课）！
• FTWRL(Flush tables with read lock): 在不支持事务的数据库引擎，比如Myisam上用到；
• 备份命令：mysqldump --single-transaction，基于MVCC支持，数据可以正常更新（备份由于事务启动，看到的是一个一致性视图），在支持事务的数据库引擎上，比如 InnoDB上用到！

6.2. 表级锁

• 表锁：LOCK TABLES ... READ/WRITE
  • 读锁和写锁互斥
  • 在无行锁情况下，并发支持靠的是表锁
• 表元数据锁：MDL(meta data lock) ，mysql5.5引入，当对表结构做变更或者是 DML 时候，都会加 MDL 锁：
  • 读锁间不互斥，多个线程支持并发 DML
  • 读锁、写锁互斥，多个线程同时更改表结构，需要等待到锁资源才可以执行
  • MDL在 DML 增删改查过程中是自动加DML读锁，同时在 DDL 修改数据表结构会加DML写锁，因此即使是高频小表，也可能被阻塞很久：读锁-读锁-写锁(block)-读锁(block)...
  • 如何安全 DDL: 低峰操作、申请 DML 锁，发现申请不到，先释放锁，后续再操作

6.3. 行级锁

• 行锁：行记录锁，两个线程同时更新一行，会导致后更新的 DML 被阻塞，直至innodb_lock_wait_timeout超时
  • mysql 行锁是由各个引擎自己实现的，有了行锁支持，提升了数据库的并发能力
• 两阶段锁协议：行锁在需要的时候才加上，需要待事务提交结束才会释放，因此需要缩短锁持有时间，将事务中多个行操作，考虑将影响并发的锁往后操作
• 锁配置：
  • 锁等待，直至超时：innodb_lock_wait_timeout=50
  • 死锁检测，耗 CPU：innodb_deadlock_detect=on，1000 个线程并发操作行，存在 1M 的检测量，期间消耗大量 CPU
• 死锁应对：
  • 在应用层排队或者在进入引擎之前排队
  • 将操作单行，分散到操作多行，降低锁冲突
  • 先记录内存锁操作，做数据排队聚合，再落盘
• 针对行锁其他注意：更新条件若没有加索引，会导致全表锁定

1
2
3
4

死锁相关参数：
// ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
| innodb_deadlock_detect    | ON  |
| innodb_lock_wait_timeout  | 50  |

7. 小结

快速对 mysql 的逻辑结构、日志、事务和事务隔离级别、锁类型、索引和索引结构等进行简要概述，对 mysql 的一些基本知识点进行了回顾和学习！