MySQL笔记

目录

• MySQL的基本架构
• redo log
• bin log
• 两阶段提交
• 事务的隔离
• 索引下推
• 死锁
• 数据可见性

MySQL的基本架构

• Server层
  • 连接器：管理连接，权限验证
    • 建立连接时，用户名密码通过，连接器到权限表查询拥有的权限，赋予这个连接读写权限。在连接建立后，对该用户的权限修改不会影响已经存在的连接的权限。
    • 客户端长时间没有请求，连接器将自动断开连接。时长由wait_timeout控制，默认为8小时
    • MySQL执行过程中，临时使用的内存是管理在连接对象里的
    • 长连接高内存占用的处理：
      1. 定期断开长连接，再次使用时重新连接
      2. MySQL5.7以上，每次执行一个比较大的操作后，使用mysql_reset_connection重新初始化连接资源。不会重连和重新做权限验证，但是会将连接恢复到刚创建时候的状态、
  • 查询缓存：命中则直接返回结果
    • 只要有一个对表的更新操作，这个表上的查询缓存都会被清空
  • 分析器：词法分析，语法分析
  • 优化器：执行计划生成，索引选择
  • 执行器：操作引擎，返回结果
• 存储引擎层：存储数据，提供读写接口

redo log

• 当一条记录需要更新的时候，InnoDB引擎会先把记录写到redo log里，并更新缓存。InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘
• 这就是MySQL中的WAL技术，WAL的全称是Write-Ahead Logging，预写日志技术，先写日志再写磁盘
• redo log是InnoDB引擎独有的日志
• redo log是物理日志，记录的是“在某个数据页上做了什么修改”
• redo log是固定大小的，从头开始写，写到末尾就又回到开头循环写
• 有了redo log ，InnoDB即使数据库发生异常重启，之前提交的记录也不会丢失

bin log

• bin log是MySQL的server层实现的，所有引擎都可以使用
• bin log可以追加写入，文件写到一定大小后会切换到下一个，不会覆盖以前的日志
• bin log是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2的行上c字段加1”

两阶段提交

• redo log 和 bin log是两个独立的逻辑，日志需要“两阶段提交”来让两个日志状态逻辑上保持一致。

• 先写redo log后写binlog。假设在redo log 写完，binlog还没有写完的时候，MySQL进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1。

  但是由于binlog没写完就 crash了，这时候 binlog里面就没有记录这个语句。因此之后备份日志的时候，存起来的 binlog里面就没有这条语句。如果需要用这个 binlog来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。

• 先写binlog 后写redo log。如果在 binlog 写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是 binlog里面已经记录了“把c从О改成1”这个日志。所以，在之后用binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同。

事务的隔离

隔离级别

• 读未提交：一个事务还没提交时，它做的更改就能被其他事务看到
• 读已提交：一个事务提交之后，它做的变更才会被其他事务看到
  • 在实现上，数据库里会在每个SQL开始执行时创建一个视图，访问的时候以视图的逻辑结果为准。
• 可重复读：一个事务执行过程中看到的数据，总是更这个事务在启动时看到的数据是一致的。
  • 在实现上，数据库里会在事务启动时创建一个视图，访问的时候以视图的逻辑结果为准。
• 串行化：对同一行记录，写会加写锁，读会加读锁。

索引下推

• MySQL5.6引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不符合条件的记录，减少回表次数

死锁

MySQL中出现死锁的解决策略

1. 直接进入等待，直到超时。超时时间可以通过参数innodb_lock_wait_timeout来设置，默认为50s
2. 死锁检测，当发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。使用innodb_deadlock_detect设置为on，表示开启这个逻辑
   • 死锁检测需要消耗大量的CPU资源，解决方法
     • 控制并发度，使得同一行同一时刻只有部分线程更新。
     • 分段锁，减少资源冲突

数据可见性

• InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在活跃的所有事务ID。活跃指的是启动了还未提交
• 数组里面事务ID最小值为低水位，当前系统里面已经创建过的事务ID的最大值加1为高水位，视图数组和高水位，组成了当前事务的一致性视图
• 数据版本的可见性规则，就是基于数据的row trx_id和这个一致性视图的对比结果得到的
  
  对于当前事务启动瞬间，一个数据版本的row trx_id 会有以下几种可能

1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的。
2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的
3. 如果落在黄色部分，就有两种情况
   1. 若row trx_id在数组中，表示这个版本是由还没有提交的事务生成的，不可见
   2. 若row trx_id不在数组中，表示这个版本是已经提交的事务生成的，可见

事务更新操作时，执行当前读

除了update语句外，select语句如果加锁，也是执行当前读。

• 可重复读的核心是一致性读，当事务更新数据的时候，只能使用当前读，如果当前的记录行锁被其他事务占用，则进入锁等待