Mysql 45讲总结

• 1. 理论篇
  • 1.1. 1.基础架构
  • 1.2. 2.日志系统
  • 1.3. 3.事务隔离
  • 1.4. 4.索引
  • 1.5. 5.锁机制
• 2. 实战篇
  • 2.1. 普通索引和唯一索引如何选择

1. 理论篇

1.1. 1.基础架构

1.2. 2.日志系统

• redo log

  • 功能：当记录进行更新操作的时候，InnoDB先写入redo log（内存中的log buffer，减少IO），并将修改更新到内存，等到以后系统空闲时后台将log刷到磁盘（commit和abort刷盘时间由参数决定）。

    • 具体刷盘时间

      • 首先要明白数据库有个Log Buffer，可以先刷到操作系统OS Buffer中，然后再刷到磁盘上

      • 0延迟写

        • 提交事务后，不会立即刷到OS Buffer中，而是等一秒后刷新到OS Buffer并调用fsync()写入Redo Log FIle，可能会丢失一秒钟的数据。

      • 1实时写

        • 每次提交事务，都会刷新到OS Buffer并调用fsync()写到Redo Log FIle，性能较差

      • 2延迟刷新

        • 每次提交事务只刷新到OS Buffer，一秒后再调用fsync()写入Redo Log FIle。

  • 通过redo log实现crash safe，即服务崩溃后仍能把事务的变更持久化到磁盘文件上

  • InnoDB引擎特有

  • 物理日志，记录的是某个数据页上做了什么

  • 循环写，空间固定会用完（write pos和checkpoint之间即为空闲空间，checkpoint记录的是写入到了磁盘的位置，write pos是当前正在写的位置）

• bin log

  • 作用：数据备份和主从同步

  • Server层实现的

  • 逻辑日志

  • 追加

• undo log

  • undo log记录的是相反操作的逻辑日志

  • 作用是用来实现事务的回滚操作，以及实现MVCC（复用undo log的trx id和历史record内容，具体见事务隔离章节）

• 两阶段提交

  • 涉及到redo log和undo log和提交事务

  • 为什么要两阶段提交？

    • 一条记录更新操作必须要同时有bin log和redo log，如果少了其中的一个都要么在crash恢复或者备份恢复阶段少了其中一个记录的更新。但是如果不二阶段提交，总可能少其中一个（只是用在提交事务语句上）

  • 做法：

    • 先写入redo log，设为prepare状态，等到bin log也写入之后再把redo log中的prepare状态改为commit状态。当故障恢复的时候，判断redo log是否是commmit状态或者是否是prepare状态且完整

1.3. 3.事务隔离

• 三种问题

  • 脏读

    • 事务A、B，B更新a的值，A读取到，但是B没有提交而是回滚了。则A读到了一个不存在的值。

  • 不可重复读

    • 事务A、B，B更新a的值，提交了，A读到了。B再次更新a的值，提交了，A又读到了。造成这种问题的原因就是实现方式是每次select都开启一个Read View视图导致的。

  • 幻读

• 四种隔离级别

  • 读未提交（脏读）

    • 还没有提交的事务的修改对其他事务是可见的

    • 实现方式

      • 直接读取版本中最新的记录

  • 读已提交（不可重复读）

    • 已提交的事务的修改对其他未提交的事务是可见的

    • 图示

    • 实现方式

      • 使用版本链的方式

  • 可重复读（幻读）

    • 读到已提交事务的更改

    • 图示

    • 实现方式

      • select查询用得到是快照读

        • 使用MVCC一定程度上避免了幻读

        • 出现幻读的情况

          • 本质原因：事务B对该记录的修改的trix id是事务B的id所以事务A是看不到的，但是事务A执行了一个更新操作以后trix id就变成事务A的id了，就可以可见了

      • select … for update用的是当前读

        • 使用间隙锁+记录锁一定程度上避免了幻读

        • 出现幻读的情况

          • T1 时刻：事务 A 先执行「快照读语句」：select * from t_test where id > 100 得到了 3 条记录。

          • T2 时刻：事务 B 往插入一个 id= 200 的记录并提交；

          • T3 时刻：事务 A 再执行「当前读语句」 select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象。

      • 总结

        • 一种是MVCC机制本身的幻读，一种是没加临键锁的快照读和当前读引起的

        • 网址

          • 美团面试官：MySQL可重复读如何解决幻读问题？ - 知乎 (zhihu.com)

  • 可串行化

    • 实现方式

      • 使用加锁互斥的方式实现

• MVCC

  • 三个隐藏的字段

    • RowID

      • 自动创建的id（若没有主键才存在，MVCC不使用）

        • 隐藏的自增ID，当建表没有指定主键，InnoDB会使用该RowID创建一个聚簇索引。

    • DB_TRX_ID

      • 事务id

        • 最近修改（更新/删除/插入）该记录的事务ID。

    • DB_ROLL_PTR

      • 回滚指针

        • 回滚指针，指向这条记录的上一个版本。

  • Read View

    • up_trix_id

      • 最先开始的事务，该SQL启动时，当前事务链表中最小的事务id编号，也就是当前系统中创建最早但还未提交的事务

    • low_trix_id

      • 最后开始的事务，该SQL启动时，当前事务链表中最大的事务id编号，也就是最近创建的除自身以外最大事务编号

    • m_ids

      • 当前活跃事务ID列表，所有事务链表中事务的id集合

    • creator_trix_id（是否存在？）

      • 当前创建 ReadView 事务的 ID

  • 可见性判断

1.4. 4.索引

• 回表查询

• 联合索引

  • 为什么要使用联合索引

    • 减少开销

      • 建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

    • 覆盖索引

      • 对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。

    • 效率高

      • 索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知

        • 解释一下：使用单列索引时，查询计划使用的

• 覆盖索引

  • 可以使得不需要回表查询

• 最左匹配原则

1.5. 5.锁机制

• 加锁粒度

  • 全局锁

    • 使用

      • flush tables with read lock

        • 对数据的增删改和表的修改都被阻塞

      • unlock tables

    • 作用

      • 全库逻辑备份

    • 备注

      • InnoDB存储引擎默认是可重复读的隔离级别，备份的时候可以不用全局锁。MylSAM不支持可重复读隔离级别，备份的时候需要加全局锁

  • 表级锁

    • 表锁

      • 使用

        • lock tables t_student read 共享读锁

        • lock tables t_student write 共享写锁

        • unlock tables

      • 作用

        • 表锁可以锁住表的数据

    • 元数据锁（MDL）

      • 作用

        • 元数据锁可以锁住表的元数据

        • 对表结构的修改（例如给某个表删除某个列）会被阻塞

      • 使用

        • 当对表进行操作时，自动给表加上MDL。DML锁一旦加上则在整个事务期间都持有，直到事务结束才释放

        • 对一张表CRUD，加的是DML读锁

          • 当有线程在执行CRUD（加DML读锁），其他线程如果要对表结构进行变更，会被阻塞

        • 对一张表的结构CRUD，加的是DML写锁

          • 当有线程进行表结构的更改（加DML写锁），其他线程如果要进行CRUD操作，将会被阻塞

      • 申请方式

        • 锁请求队列，该队列有优先级，写锁请求阻塞读写请求

          • 一旦有一个写锁请求被阻塞，后面无法再进行表的CRUD操作

      • 备注

        • 在所有事务不对表进行结构改变的情况下，加的都是DML读锁，可以说互不影响，察觉不到DML锁的存在

    • 意向锁

      • 使用

        • 在使用InnoDB引擎的表里对某些记录加上共享行级锁之前，需要先给表加上表级意向共享锁

          • select … lock in share mode;

        • 在使用InnoDB引擎的表里对某些记录加上共享独占锁之前，需要先给表加上表级意向独占锁

          • select … for update;

      • 使用注意

        • 只有InnoDB才支持行级锁，所以只有InnoDB才有意向锁

        • 默认的select语句使用的是MVCC实现事务隔离，是没有行级锁的

        • 意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。

      • 作用

        • 快速判断表里是否有记录被加锁。

        • eg：表加了意向互斥，就不能再遍历了

    • AUTO-INC锁

      • 使用

        • 表锁，插入数据语句执行时自动加锁，插入语句执行完后（将AUTO_INCRE递增后）立即释放

      • 注意

        • AUTO-INC锁是重量级锁，插入大量数据会影响性能

        • MySQL 5.1.22之后，InnoDB使用一种轻量级锁来实现自增

          • 插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

        • innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。

  • 行锁（InnoDB特有）

    • 记录锁（Record Lock）

      • 共享锁S

      • 互斥锁X

    • 间隙锁（Gap Lock）

      • 使用

        • 只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

    • 临建锁（Next-key Lock）

      • 使用

        • 同间隙锁，是为了在可重复隔离级别下实现幻读（第二种幻读）的

        • 使用即select语句的where条件中的范围，后面for update就是给指定范围加临键锁

      • 注意

        • 一个间隙和间隙后面紧跟的key

    • InnoDB三种锁的选择规则

      • 无索引的情况

        • select…for update、insert、update、delete都会给全表加next-key lock

      • 唯一索引等值查询

        • 记录存在

          • 加表记X意向锁

          • 加的是针对该记录的X锁

        • 记录不存在

          • 表级X意向锁

          • 加的是间隙锁

            • id = 3

      • 唯一索引范围查询

        • 每扫描到一个索引加next-key锁，遇到下面的情况会退化为记录锁或者间隙锁

        • 情况一：大于等于的范围查询

          • 存在等值查询，如果等值查询的记录是存在于表中，next-key锁退化为记录锁

            • 也就是说等于的那个值前面的间隙是不需要的

        • 情况二：针对小于或者小于等于的范围查询

          • 条件值的记录存在于表中

            • 小于：退化为间隙锁

            • 小于等于：next-key锁

          • 条件值的记录不存在于表中

            • 不管是小于还是小于等于都会退化为间隙锁

              • 都只保留间隙，而不取key

      • 非唯一索引等值查询

        • 当我们用非唯一索引进行等值查询的时候，因为存在两个索引，一个是主键索引，一个是非唯一索引（二级索引），所以在加锁时，同时会对这两个索引都加锁，但是对主键索引加锁的时候，只有满足查询条件的记录才会对它们的主键索引加锁。

        • 情况一：查询的记录存在时

          • 直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。

            • 解析：二级索引加锁是很好想的（第一个不符合的变为间隙锁，除去该第一个值，保留前面的间隙），一级索引加的只是记录锁。

        • 情况二：查询的记录不存在时

          • 二级索引同不存在的情况，找到第一个不符合的退化间隙锁，但是主键索引不需要加锁

      • 非唯一索引范围查询

        • 相比于等值查询，二级索引全是加next-key锁

    • 二阶段锁规则以及性能优化

      • 二阶段锁

        • 所有事务都是在用到的时候加上锁，但是在事务commit的时候提交事务

        • 让冲突的行尽量放在事务的后面（保证事务正确的前提下），这样可以减少锁住的时间，提高并发度。（参考45讲06的例子）

      • 死锁检测

        • 两种手段

          • 等待直到超时

            • 这个超时时间可以通过参数innodb_lock_wait_timeout来设置。

          • 死锁检测

            • 发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

        • 业务上的问题（热点行更新）

          • innodb_lock_wait_timeout的默认值是50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过50s才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。

          • 主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是O(n)的操作。假设有1000个并发线程要同时更新同一行，那么死锁检测操作就是100万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的CPU资源。因此，你就会看到CPU利用率很高，但是每秒却执行不了几个事务。

        • 解决思路

          • 控制并发度

            • 中间件实现

            • 修改MySQL源码

            • 表的一行拆分成逻辑的多行

2. 实战篇

2.1. 普通索引和唯一索引如何选择

• ChangeBuffer

  • Buffer Pool内存结构图

  • 工作原理

    • 要修改的索引页在内存中

      • 直接在内存中更新索引页

    • 要修改的索引页不在内存中

      • 将对索引页的修改写到change buffer中

      • 下次查询索引页时，会将索引页读入Buffer Pool，然后将change buffer中的操作应用到对应的缓存页，得到最新结果，这个过程称为merge。

  • 使用条件（索引）

    • 记录在内存中

      • 对于唯一索引来说，找到对应的位置，判断到没有冲突，插入这个值，语句执行结束；

      • 对于普通索引来说，找到对应的位置，插入这个值，语句执行结束。

    • 记录不在内存中

      • 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；

      • 对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。

    • 结论

      • 对于记录在内存中，区别差不多。对于记录不在内存中，普通索引要减少随机IO次数，所以普通索引才适合使用ChangeBuffer

  • 业务场景

    • 写多读少类业务（是账单类、日志类）

      • 适合changeBuffer和普通索引配合使用

    • 写入后立马查询的业务

      • 不适合

  • 与redo log的工作流程

    • 1. Page 1在内存中，直接更新内存；
    • 2. Page 2没有在内存中，就在内存的change buffer区域，记录下“我要往Page 2插入一行”这个信息
    • 3. 将上述两个动作记入redo log中（图中3和4）。