01-05

01 | 基础架构：一条SQL查询语句是如何执行的？

• MySQL 的逻辑架构图
  
• MySQL 可以分为 Server层 和 存储引擎层 两部分
  • Server层: 包括连接器、查询缓存、分析器、优化其、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有内置函数(日期、时间、数学和加密函数等)，所有跨存储引擎的功能都在这一层实现(存储过程、触发器、视图等)
  • 存储引擎层: 负责数据的存储和提取，其架构模式是插件式的，支持 InnoDB、MyISAM、Memory等多个存储引擎。最常用的是 InnoDB，从 MySQL 5.5.5 版本成为默认存储引擎

执行流程

1. 连接器
   • 用户名密码认证通过后，连接器会到权限表中查出用户拥有的权限，后续在这个连接里的权限判断逻辑，都将依赖于此时读到的权限
   • show processlist: 查看当前的客户端连接
   • wait_timeout: 控制连接的超时时间
   • 长连接和短连接:
     • 长连接: 指连接成功后，如果客户端持续有请求，则一直使用同一个连接
     • 短连接: 指每次执行完很少的几次查询就断开连接，下次查询再重新创建连接
     • 建立连接的过程比较复杂，使用中尽量减少建立连接的动作，即尽量使用长连接
   • 长连接存在的问题:
     • 全部使用长连接后，可能存在 MySQL 占用内存快速增长的问题，因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里的，这些资源只有在连接断开时才释放，一旦长连接数量过多，极有可能导致内存占用过大，发生 OOM(系统杀掉进程)，MySQL异常重启
     • 解决方案:
       (1) 定期断开长连接。使用一段时间后，或者执行过一个占用内存的大查询后，断开连接
       (2) 如果 MySQL 版本在 5.7及以上，可以在每次执行一个较大操作后，通过执行 mysql_reset_connection 重新初始化连接资源，这个过程会将连接恢复到刚创建完成的状态，但并不需要重新连接并进行权限验证
2. 查询缓存(MySQL 8.0 后完全删除)
   • MySQL拿到查询请求后，会先从查询缓存中判断是否有缓存，之前执行过的语句和结构通常会以 key-value 的形式被直接缓存在内存中，有则直接返回结果，如果没有则继续执行，并将结果缓存起来
   • 查询缓存的过期: 查询缓存的失效非常频繁，只有有对一个表的更新，这个表上的所有查询缓存都会被清空，对于更新压力大的数据库来说，查询缓存的命中率极低
   • 查询缓存的按需使用: 设置参数 query_cache_type 为 DEMAND，对于默认的SQL语句都不使用查询缓存，对确定要使用查询缓存的语句使用 SQL_CACHE 进行显式执行，例如 select SQL_CACHE * from T where id = 1;
3. 分析器
   • 词法分析 -> 语法分析
4. 优化器
   • 在表里有多个索引的时候，决定使用哪个索引，或在一个语句有多表关联(join)的时候决定各个表的连接顺序
5. 执行器
   • 判断对表的权限 -> 打开表继续执行(此时会根据表的引擎定义调用对应接口)

• 当 SQL语句 查询了表中不存在的列时会出现报错，这个错误是在分析器阶段报出来的，原因是分析器会在分析阶段判断语句是否正确，包括表是否存在、列是否存在

02 | 日志系统：一条SQL更新语句是如何执行的？

• 更新流程与查询流程基本相同，但是还涉及到两个重要的日志模块: redo log(重做日志) 和 binlog(归档日志)
• innodb_flush_log_at_trx_commit: 设置为1表示每次事务的 redo log 都直接持久化到磁盘，推荐使用，可以保证异常重启后不丢失数据
• sync_binlog: 设置为1表示每次事务的 binlog 都持久化到磁盘，建议设置，可以保证异常重启后 binlog 不丢失

redo log

• InnoDB引擎特有，属于存储引擎层
• WAL技术: 全称 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。当有一条记录需要更新的时候，InnoDB引擎 会先把记录写到 redo log 里面并更新内存，此时等于更新完成，InnoDB引擎 会在适当的时候将这个操作记录更新到磁盘上，这个更新往往是在系统比较空闲的时候做的
• InnoDB引擎 的 redo log 是固定大小的，比如可以配置为 一组4个文件，每个文件大小为 1GB，总共可以记录 4GB 的操作，从头开始写，写到末尾就又回到开头循环写
  
  • write pos: 当前所在位置，边写边后移，写到第3号文件末尾后回到0号文件开头
  • checkpoint: 当前要擦除的位置，在擦除记录前要把记录更新到数据文件(磁盘上)
  • write pos 和 checkpoint 之间用来记录新的操作，当 write pos 追上 checkpoint 时表示空间已满，此时不能再执行新的更新，需要先停止下来擦除一些记录，推进 checkpoint
• 有了 redo log，InnoDB 可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe

binlog(Server层)

• 最初 MySQL 中没有 InnoDB引擎，MySQL 自带引擎为 MyISAM引擎，没有 crash-safe 能力，binlog日志 只能用于归档，而 InnoDB引擎 是另一家公司以插件形式引入MySQL中的

日志对比

1. redo log 是 InnoDB引擎 特有的；binlog 是 MySQL 的 Server层实现的，所有引擎都可以使用
2. redo log 是物理日志，记录的是 在某个数据页上做了什么修改；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如: 给 ID=2 这一行的C字段加1
3. redo log 是循环写的，空间固定；binlog 是可追加写入的。追加写 指 binlog 文件写到一定大小后会切换到下一个，不会覆盖以前的日志

两阶段提交

• update 语句的执行流程图: 浅色框表示在 InnoDB 内部执行，深色框表示在执行器中执行
  
• 两阶段提交: redo log 的写入拆分成了两个步骤，prepare 和 commit，目的是为了让两份日志之间的逻辑一致
• 数据表误删的数据恢复:
  系统会定时做整库备份，备份系统中会保存所有 binlog
  步骤1: 找到最近一次全量备份，从这个备份恢复到临时库
  步骤2: 从备份的时间点开始，将备份的 binlog 以此取出来，重放到误删表之前的那个时刻
  这样临时库就跟误删之前的线上库一样了，可以把表数据从临时库中取出，按需要恢复到线上库
• redo log 和 binlog 是两个独立的逻辑，如果没有 两阶段提交，就是使用先写完 redo log 再写 binlog的方式，或者使用相反的顺序
  例子: update T set c=c+1 where ID=2;
  1. 先写 redo log 后写 binlog: 在 redo log 写完后，binlog写完前，MySQL 异常重启，由于 binlog 没写完就发生了 crash，binlog 中没有记录这条语句，之后备份的时候，存起来的 binlog 中就缺少了这条语句，在需要使用这个 binlog 来恢复临时库时，就会缺少这一次更新，导致恢复出来这一行c的值为0,与原库不同
  2. 先写 binlog 后写 redo log: binlog 写完发生 crash，redo log 还未写入，崩溃恢复后这个事务无效，所以这一行c的值是0，但是 binlog 中已经记录了 c -> 1 这个日志，因此之后使用 binlog 恢复就会多出一个事务，恢复出来的这一喊c的值为1,与原库不同
• 不使用 两阶段提交，数据库的状态就有可能和用它的日志恢复出来的临时库状态不一致，redo log 和 binlog 都可以用于表示事务的提交状态，两阶段提交的作用就是让这两个状态保持逻辑上的一致
• 不仅在误操作后需要使用这个过程进行数据恢复，在需要扩容(增加备库提高系统读能力)时，常见做法也是使用全量备份+应用binlog 来实现的

03 | 事务隔离：为什么你改了我还看不见？

隔离性和隔离级别

• 隔离级别：
  读未提交： 一个事务还未提交时，它做出的更改就能被别的事务看到
  读已提交： 一个事务提交之后，它所做的变更才会被其他事务看到
  可重复读： 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一样的，在这个隔离级别下，未提交变更的事务对其他事务也是不可见的
  串行化： 对同一行记录，写加写锁，读加读锁，当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成才能继续执行

04 | 深入浅出索引（上）

索引的常见模型

• 哈希表： 只适用于等值查询，比如 Memcached 以及其他一些 NoSQL 引擎
• 有序数组： 在等值查询和范围查询场景中的性能都非常优秀，但是在更新数据时，插入中间一个记录就得挪动后面的所有记录，因此只适用于静态存储引擎
• 搜索树

InnoDB 的索引模型

• InnoDB 中，表都是根据主键顺序以索引形式存放的，这种存储形式的表称为索引组织表，InnoDB 又使用了 B+ 树索引模型，所有数据都存储在 B+ 树中
• InnoDB 中主键索引的叶子节点存的是整行数据，在 InnoDB 中，主键索引也被称为聚簇索引
• 非主键索引的叶子节点内容是主键的值，在 InnoDB 中非主键索引也被称为二级索引

索引维护

• 页分裂: B+树为了维护索引有序性，在插入新值前需要做必要的维护，当被插入的数据页已经满了，根据 B+树 的算法，需要申请一个新的数据页，然后挪动部分数据过去，这个过程称为页分裂。这种情况下性能就会受到影响，同时还影响数据页的利用率
• 页合并：当相邻的两个数据页由于删除了数据，利用率很低时，就会将两个数据页做合并
• 自增主键: 自增主键的插入数据模式，是递增插入的场景，每次插入一条新纪录都是追加操作，不涉及挪动其他纪录，因此也不会触发叶子节点的分裂。同时主键的长度越小，普通索引的叶子节点也就越小，占用的空间也就越小

05 | 深入浅出索引（下）

• 回表: 回到主键索引树搜索的过程
• 索引覆盖: 索引覆盖可以减少树的搜索次数，显著提升查询性能，是一个常用的性能优化手段
• 最左前缀原则: 当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了，因此第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的
• 索引下推: 在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的纪录，减少回表次数