Mysql最全笔记包含脑图

获取脑图方式请看最下面！

Mysql

1.mysql语句执行流程

查询语句

• 1.通信协议

  • 1.通信类型

    • 1.同步通信：依赖于被调用方，受限与被调用方的性能，也就是说线程会阻塞，等待数据库的返回。
    • 2.异步通信：可以避免应用阻塞等待，但是不能节省SQL执行时间；如果并发过高，每个SQL执行都要建立一个连接，会给服务器造成很大压力，如果要异步，必须要考虑连接池。

  • 2.连接方式

    • 1.长连接（默认）：
    • 2.短连接：

  • 3.查询连接相关配置

    • 1.wait_timeout 非交互式超时时间，如JDBC

    • 2.interactive_timeout 交互式超时时间，如数据库工具

    • 3.查看当前有多少个连接 show global status like ‘Thread%’;

      • 1.Threads_cached:缓存中的线程连接数
      • 2.Threads_connected:当前打开的连接数。
      • 3.Threads_created:为处理连接创建的线程数。
      • 4.Threads_running:非睡眠状态的连接数，通常指并发连接数。

    • 4.查看SQL的执行状态， SHOW PROCESSLIST;

      • State

        • Sleep：线程正在等待客户端，以向他发送一个新语句
        • Query：线程正在执行查询或往客户端发送请求
        • Locked：该查询被其他查询锁定
        • Copying to tmp table on disk：临时结果集合大于tmp_table_size 线程把内存格式持久化到磁盘
        • Sending data：线程正在为Select语句处理行，同时正在向客户端发送数据
        • Sorting for group/order：线程正在分类，以满足group/order要求

      • Info：执行的语句

      • 以及一些数据库用户信息

    • 5.允许客户端最大连接151个，最大可设置成16384，show variables like ‘max_connections’;

  • 4.支持的通信协议

    • 1.Unix Socket：比如在服务器上，如果没有指定-h，它就用socket方式登陆（本地连接）
    • 2.TCP/IP：如果指定-h参数，就会用TCP/IP协议
    • 3.命名管道：
    • 4.内存共享：

  • 5.通信方式（采用半双工）

    • 1.单工：数据单向传输，比如遥控器
    • 2.半双工：数据双向传输，但不能同时传输，比如对讲机，
    • 3.全双工：数据双向传输，可以同时传输，比如打电话

• 2.查询缓存：mysql自带缓存模块，缓存默认是关闭的，8.0版本中被移除了

  • 为什么不推荐使用？

• 3.语法解析&预处理：基于SQL语法进行词法和语义的解析

  • 1.词法解析：把一个完整的SQL语句打碎成一个个单词
  • 2.语法解析：对SQL做一些语法检查，比如单引号没有闭合，然后根据MySQL定义的语法规则，根据SQL语句生成一个数据结构，这个数据结构为解析树
  • 3.预处理器：词法与语法解析只能知道SQL语句是否符合MySQL的语法规则，（而无法判断表是否存在、列名是否存在，检查名字和别名保证没有歧义），预处理后得到一个新的解析树。

• 4.查询优化器（Optimizer）&查询执行计划：

  • 查询优化器：Last_query_cost 可以查看开销。

    得到解析树之后，是不是就执行SQL语句了呢？
    这里有一个问题，一条SQL语句是不是只有一种执行方式，或者说数据库最终执行的SQL是不是就是为们发送的SQL？
    这个答案是否行的，一条SQL语句是可以有很多种执行方式的，最终返回相同的结果，他们是等价的，但是如果有这么多执行方式，这些方式是怎么得到的？最终选择哪一种去执行？根据什么判断标准去选择？

    • 1.什么是优化器？

      • 根据解析树生成不同的执行计划，然后选择最优的执行计划，MySQL是基于开销的优化器，哪种执行计划开销最小，就用哪种。

    • 2.优化器可以做什么？

      • 1.对多张表进行关联查询的时候，以哪个表的数据作为基准表
      • 2.多个索引可以使用的时候，选择哪个索引

    • 3.优化器是怎么得到执行计划的？

      • 1.开启优化器的追踪（默认关闭）SHOW VARIABLES LIKE ‘optimizer_trace’;

      • 2.执行一个SQL语句，优化器会生成执行计划

      • 3.优化器分析的过程已经记录到系统表里 set optimizer_trace=“enabled=off”;

        steps主要分为3部分
        1.准备阶段(join_preparation-SQL)
        2.优化阶段(join_optimization-SQL)
        3.执行阶段(join_execution-SQL)

    • 4.优化器得到的结果：优化器最终会把解析树变成一个查询执行计划，查询执行计划是一个数据结构

  • 执行计划

• 5.存储引擎

  • 1.存储引擎介绍：MySQL支持多种存储引擎，他们是可以替换的，所以是插件式的存储引擎，存储引擎是以表为单位的，创建表后还可以修改存储引擎

  • 2.查看存储引擎 如何查看存储引擎呢？show table status from

    • Transactions 是否支持事务
    • XA 协议用来实现分布式事物（分为本地资源管理器/事务管理器）
    • Savepoints 实现子事务。创建一个savepoints后，事务可以回滚到这个点，不会影响到之前的操作。

  • 3.存储引擎的比较show engines ;

    • InnoDB

      mysql 5.7 中的默认存储引擎。InnoDB 是一个事务安全(与 ACID 兼容)的 MySQL 存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB 行级锁(不升级 为更粗粒度的锁)和 Oracle 风格的一致非锁读提高了多用户并发性和性能。InnoDB 将 用户数据存储在聚集索引中，以减少基于主键的常见查询的 I/O。为了保持数据完整性，
      InnoDB 还支持外键引用完整性约束。 特点:
      支持事务，支持外键，因此数据的完整性、一致性更高。 支持行级别的锁和表级别的锁。 支持读写并发，写不阻塞读(MVCC)。 特殊的索引存放方式，可以减少 IO，提升查询效率。 适合:经常更新的表，存在并发读写或者有事务处理的业务系统。

    • MyISAM

      特点:
      支持表级别的锁(插入和更新会锁表)。不支持事务。 拥有较高的插入(insert)和查询(select)速度。
      存储了表的行数(count 速度更快)。
      (怎么快速向数据库插入 100 万条数据?我们有一种先用 MyISAM 插入数据，然后
      修改存储引擎为 InnoDB 的操作。) 适合:只读之类的数据分析的项目。

    • Memory

      把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消 失。只适合做临时表。
      将表中的数据存储到内存中。

    • CSV

      它的表实际上是带有逗号分隔值的文本文件。csv 表允许以 csv 格式导入或转储数据， 以便与读写相同格式的脚本和应用程序交换数据。因为 csv 表没有索引，所以通常在正 常操作期间将数据保存在 innodb 表中，并且只在导入或导出阶段使用 csv 表。
      特点:不允许空行，不支持索引。格式通用，可以直接编辑，适合在不同数据库之 间导入导出。

    • Archive

      这些紧凑的未索引的表用于存储和检索大量很少引用的历史、存档或安全审计信息。 特点:不支持索引，不支持 update delete。

  • 如何选择存储引擎？

    • 如果对数据一致性要求高，需要事务支持，可以选择InnoDB
    • 如果数据查询多更新少，对查询性能要求比较高，可以选择 MyISAM。
    • 如果需要一个用于查询的临时表，可以选择 Memory。
    • 如果所有的存储引擎都不能满足你的需求,可以用C语言开发一个存储引擎。

• 6.执行引擎：使用执行计划操作存储引擎，它利用存储引擎提供的相应的API来完成操作。

更新语句：基本流程和查询流程是一致的，它也要经过解析器、优化器、执行器处理，区别在于拿到符合条件的数据之后的操作

简化过程：
1.事务开始，从内存或磁盘读取到这条数据，返回给Server的执行器
2.将查询到的行进行修改
3.将修改的结果更新到内存Buffer pool中
4.记录 redo log ，并将记录状态置为prepare状态
5.修改后，提交事务
6.server 层 写入bin log 中
7.执行 commit
8.将 redo log里的事务记录置为 commit 状态

• 1.缓冲池Buffer Pool

  InnooDB的数据都是放在磁盘上的，InnoDB 操作数据有一个最小的逻辑单 位，叫做页(索引页和数据页)，我们对于数据的操作不是直接操作磁盘，因为磁盘的速度慢，InnoDB使用了一种缓冲池的技术，也就是把磁盘读到的页放到一块内存区域里
  下次读取相同的页，先判断是不是在缓存池里面，如果是直接读取，不用再次访问磁盘。
  修改数据的时候，先修改缓冲池里的页。内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。InnoDB里面有专门的线程把Buffer Pool的数据写入到磁盘，每隔一段时间就一次性的把多个修改写入磁盘，这个动作叫做刷脏
  Buffer Pool 是 InnoDB 里面非常重要的一个结构，它的内部又分成几块区域

  • Buffer Pool，默认128M，采用LRU算法来管理缓冲池（链表实现，分成来young和old），经过淘汰的数据就是热点数据

    Buffer Pool 缓存的是页面信息，包括数据页、索引页。

  • Change Buffer 写缓冲区，如果这个数据页不是唯一索引，不存在数据重复的情况，可以先把修改记录在内存的缓冲池中，从而提升更新语句（insert、delete、update）执行速度

    5.5 之前叫 Insert Buffer 插入缓冲，现在也能支 持 delete 和 update。
    最后把 Change Buffer 记录到数据页的操作叫做 merge。什么时候发生 merge?
    有几种情况:在访问这个数据页的时候，或者通过后台线程、或者数据库 shut down、 redo log 写满时触发。
    如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，几句可以使用Change Buffer，写多读少的业务，可以调大这个值
    innodb_change_buffer_max_size 默认占比 25%

  • Adaptive Hash Index：自适应哈希索引，对于一些热点数据页，InnoDB会自动建立自适应hash索引，在B+Tree索引的基础上创建。

  • log buffer（redo log缓存区）：用来处理数据库宕机或者重启，这些数据丢失，如果写到一半，甚至可能会破坏数据文件导致数据库不可用。

    为了避免这个问题，InnoDB 把所有对页面的修改操作专门写入redo log，并且在数据库启动时从redo log进行恢复操作(实现 crash-safe)——用它来实现事务的持久性。
    默认2个文件，每个48M，这种日志和磁盘配合的整个过程就是WAL（Write-Ahead Logging），它的关键点就是先写日志，再写磁盘。

    • 为什么要先写日志再写磁盘？考虑 随机IO和顺序IO，写日志比较快

      我们先来了解一下随机 I/O 和顺序 I/O 的概念。 磁盘的最小组成单元是扇区，通常是 512 个字节。 操作系统和内存打交道，最小的单位是页 Page。 操作系统和磁盘打交道，读写磁盘，最小的单位是块 Block。
      刷盘是随机 I/O，而记录日志是顺序 I/O，顺序 I/O 效率更高。因此先把修改写入日志，可以延迟刷盘时机，进而提升系统吞吐。
      当然redo log也不是每一次都直接写入磁盘，Log buffer专门用来保存即将要写入日志的数据，默认16M，它一样可以接收磁盘IO

    • 什么时候写入redo log中？

      在我们写入数据到磁盘的时候，操作系统本身是有缓存的。flush 就是把操作系统缓 冲区写入到磁盘。log buffer 写入磁盘的时机，由一个参数控制，默认是 1。
      0（延迟写）：log buffer 将每秒一次的写入log 中，并且log 的flush操作同时进行，该模式下，在事务提交的时候，不会主动出发写入磁盘操作。
      1（默认，实时写，实时刷）:每次事务提交时log buffer的数据写入log中，并且刷到磁盘中
      2.(实时写，延迟刷)：每次事务提交会把log buffer的数据写入log中，但是flush 操作并不会同时进行，该模式下MySQL会每秒执行一次flush操作

  • 后台线程：刷新buffer内存池中的数据到磁盘

    • master thread： 负责刷新缓存数据到磁盘并协调调度其它后台进程。
    • IO thread：分为 insert buffer、log、read、write 进程。分别用来处理 insert buffer、 redo log、读写请求的 IO 回调。
    • purge thread：用来回收 undo 页
    • page cleaner thread：用来刷新脏页。

• 2.redo log

  • redo log的特点

    1.redo log是InnoDB存储引擎实现的，并不是所有存储引擎都有
    2.不是记录数据页更新之后的状态，而是记录这个页做了什么改动，属于物理日志。
    3.redo log大小是固定的，前面的内容会被覆盖，所有日志组成一个环形结构。
    当 write pos 和 check point相遇时说明写满，

2.MySQL体系结构

1.结构讲解

• Connector：用来支持各种语言和SQL的交互
• Management Serveices & Utilities：系统管理和控制工具，包括备份恢复、MySQL复制、集群等等
• Connection Pool：连接池，管理需要缓冲的资源，包括用户密码、权限、线程等
• SQL Interface：用来接收用户的SQL命令，返回用户需要的查询结果
• Parser：用来解析SQL语句
• Optimizer：查询优化器
• Cache and Buffer：查询存储，除了行记录的缓存之外，还有表缓存，Key缓存，权限缓存等
• Pluggable Storage Engines：插件式存储引擎，它提供API给服务层使用

2.架构分层

• 连接层：管理连接，权限验证

  客户端要连接到MySQL服务器的3306端口，必须要跟服务端建立连接，那么管理所有的连接，验证客户端的身份和权限，这些功能就在连接层完成

• 服务层：语句分析->执行执行计划->执行引擎->返回结果

  连接层会把 SQL 语句交给服务层，这里面又包含一系列的流程:
  比如查询缓存的判断、根据 SQL 调用相应的接口，对我们的 SQL 语句进行词法和语 法的解析(比如关键字怎么识别，别名怎么识别，语法有没有错误等等)。
  然后就是优化器，MySQL 底层会根据一定的规则对我们的 SQL 语句进行优化，最 后再交给执行器去执行。

• 存储引擎层：存储数据，提供读写接口

  存储引擎就是我们的数据真正存放的地方，在 MySQL 里面支持不同的存储引擎。 再往下就是内存或者磁盘。

3.表空间分类

• 系统表空间：所有表共享系统表空间

  • 数据字典：由内部系统表组成，存储表和索引的元数据

  • 双写缓冲区：我们不是有 redo log 吗?但是有个问题，如果这个页本身已经损坏了，用它来做崩溃恢复是没有意义的。所以在对于应用 redo log 之前，需要一个页的副本。如果出现了写入失效，就用页的副本来还原这个页，然后再应用 redo log。这个页的副本就是double write，InnoDB 的双写技术。通过它实现了数据页的可靠性。

    InnoDB 的页和操作系统的页大小不一致，InnoDB 页大小一般为 16K，操作系统页 大小为 4K，InnoDB 的页写入到磁盘时，一个页需要分 4 次写，如果存储引擎正在写入页的数据到磁盘时发生宕机，可能出现页只写了一部分的情况，这种情况是部分写失效，可能会导致数据丢失。

    • 内存的double写
    • 磁盘的double写

  • Change Buffer

  • undo Logs：默认存放在系统表空间里

• 独占表空间：每张表会开辟一个表空间，这个文件就是数据目录下的 ibd 文件，存放表的索引和数据

• 通用表空间

• 临时表空间：存储临时表的数据，包括用户创建的临时表，和磁盘的内部临时表

• undo log（撤销日志/回滚日志）：记录了数据发生之前的数据状态，如果修改时出现异常，可以用undo log 来实现回滚操作。

  在执行undo 的时候，仅仅将数据从逻辑上恢复至事务之前的状态，而不是从物理页面的操作实现的，数据逻辑日志。
  redo log和undo log与事务相关，统称为事务日志。
  undo log的数据默认在系统表空间 ibdata1中，因为共享表空间不会自动收缩，也可以单独创建一个undo的表空间

  • 如何组织Undo Log 链

    详细请参考https://blog.csdn.net/m0_37645820/article/details/89814582

4.数据库完整性约束

• 1.域完整性：限制此单元格的数据正确，不对照此列的其他单元格比较（非空约束、默认值约束）

  • 1.非空约束
  • 2.默认值约束

• 2.实体完整性

  • 主键约束
  • 唯一约束
  • 自动增长列

• 3.参照完整性：表与表的关系，插入、更新、删除等操作都要与另外一张表对照，组织不正确的操作

3.Binlog：记录了所有的 DDL 和 DML 语句

binlog 以事件的形式记录了所有的 DDL 和 DML 语句(因为它记录的是操作而不是 数据值，属于逻辑日志)，可以用来做主从复制和数据恢复
跟redo log 不一样，它的文件内容是追加的，没有大小限制。

1.导出成sql语句，把所有操作重新执行，实现数据恢复

2.实现主从复制，从主服务器读取bin log ，然后执行一遍

4.索引

1.什么是索引：数据库中的一个排序的数据结构，可以快速的查询。

2.索引类型

• 普通索引：没有任何限制

• 唯一索引：要求键值不能重复，主键是特殊的唯一索引

• 全文索引:针对比较大的数据，只有文本类型的字段可以创建全文索引，比如char/varchar/text

  select * from table where match(content) against(‘content’ IN NATURAL LANGUAGE MODE);

3.索引结构（B+树）

• 特点

  • 1.关键字的数量是跟树相等的
  • 2.根节点和枝节点中都不会存储数据，只有叶子节点才存储数据
  • 3.数据节点相连，形成一条有序的链表
  • 4.它时根据左闭右开[ )来检索数据的

• 计算一个Page存储多少数据

  假设一条记录是1K，一个叶子节点可以存储16条数据，非叶子节点可以存储多少哥指针？
  假设索引字段是bigint，长度为8字节，指针大小在innoDB 源码中为6字节，这样一共14字节。
  非叶子节点（1页）存储16384/14=1170个单元，代表1170个指针。
  树深度为2的时候，共有11701170个叶子节点
  可以存储的数据为11701170*16，在查询数据时，一次页的查找代表一次IO，也就是说，一张2000万左右的表查询最多需要3次IO。

  • 假设索引字段是bigint

    • 假设索引字段是bigint

4.索引存储选型树

• B+树存储的特点

  • 1.它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。B树解决了每个节点存储更多关键字;路数更多的问题
  • 2.扫库、扫表能力更强，遍历数据节点的指针就可以了
  • 3.磁盘读写能力相对于B 树来说更强，根节点和叶子节点不保存数据，所以一个节点可以存储更多的关键字，加载的关键字也多
  • 4.排序能力更强
  • 5.效率稳定，叶子节点存储数据，IO次数稳定

• 为什么不用红黑树？

  • 1.只有两路
  • 2.不够平衡

5.索引方式

• Hash

  • 特点

    它的时间复杂度是 O(1)，查询速度比较快。因为哈希索引里面的数据不是 按顺序存储的，所以不能用于排序。
    查询的数据会根据键值计算hash码，所以只能支持等值查询，不支持范围查询
    如果字段重复很多的时候，会有大量的哈希冲突（拉链表法解决），降低查询效率

  • 如何建立

    InnoDB 只支持显式创建 B+Tree 索引，对于一些热点数据页， InnoDB 会自动建立自适应 Hash 索引也就是在 B+Tree 索引基础上建立 Hash 索引， 这个过程对于客户端是不可控制的，隐式的。

• B+Tree只能显式创建

6.实现形式

• MySQL架构：MySQL是一个支持插件式存储的数据库，每个表都可以指定不用的存储引擎，最常用的是MyISAM，InnoDB

• 数据存储文件：MySQL数据是以文件的形式存储在磁盘中的，目录在 datadir参数可以查询到，每个数据库都有一个目录，每个表都有.frm文件(存储表结构)

  • MyISAM

    • .MYD 存放数据
    • .MYI 存放索引
    • 特点：辅助索引和主键索引存储和检索数据的方式没有任何区别，是索引文件里面找到磁盘地址，然后到数据文件里获取数据

  • innoDB

    • .ibd 存放数据与索引

    • 特点：以主键为索引来组织数据存储的，索引文件和数据文件是一个文件，数据存储在叶子节点上。辅助索引存储的值是主键的内容，可能需要回表二次检索，如果没有主键，那么会选择第一个不包含NULL值的唯一索引作为主键，如果没有则会选择内置6字节长的RowId作为隐藏的聚集索引，他会随着记录而主键子增

      为什么在辅助索引里面存储的是主键值而不是主键的磁盘地址呢?如果主键的数据 类型比较大，是不是比存地址更消耗空间呢?
      1.是因为有分裂和合并的操作，这个时候键值的地址会发生变化，还需要将辅助索引进行同步。
      2.InnoDB本身的以主键为主排序的表，对InnoDB来讲，其在磁盘的顺序已经由主键的顺序决定了，不能在按照其他顺序排序。

7.聚集索引：索引的逻辑顺序与表数据行的物理存储顺序一致

比如字典的目录 是按拼音排序的，内容也是按拼音排序的，按拼音排序的这种目录就叫聚集索引，按照部首的就不是聚集索引。
在 InnoDB 里面，它组织数据的方式叫做叫做(聚集)索引组织表，所以主键索引是聚集索引，非主键都是非聚集索引。

8.索引使用原则

• 1.列的离散度：列的全部不同值和所有数据行的比例，离散度越大越适合创建索引。

  如果在 B+Tree 里面的重复值太多，MySQL 的优化器发现走索引跟使用全表扫描差
  不了多少的时候，就算建了索引，也不一定会走索引。

• 2.联合索引最左匹配原则：建立联合索引一定把最常用的列放在左边

• 3.覆盖索引：使用辅助索引查询，查询数据列只用从索引中就能取得，不必从数据区中读取，这时候使用的索引就是覆盖索引，避免了回表操作

• 4.索引条件下推(ICP)

  索引的比较是在存储引擎进行的，数据记录的比较是在Server层进行的
  只适用于二级索引

  • 开启索引下推：set optimizer_switch=‘index_condition_pushdown=on’;

  • 使用的场景

    1.对于二级索引
    2.select的列不使用覆盖索引
    3.多条件查询（where中多条件，where + order by）+联合索引

    • 1.过滤like的模糊匹配
    • 2.进行联合索引的排序

• 5.索引上不能使用函数，会导致索引失效

9.索引的创建原则

• 1.用于 where 判断 order 排序和 join 的(on)字段上创建索引
• 2.索引的个数不要过多——浪费空间，更新变慢
• 3.区分度低的字段，例如性别，不要建索引——离散度太低，导致扫描行数过多。
• 4.频繁更新的值，不要作为主键或者索引——页分裂
• 5.组合索引把散列性高(区分度高)的值放在前面
• 6.创建复合索引，而不是修改单列索引。
• 7.过长的字段，创建前缀索引——前缀的离散度尽可能符合要求
• 8.不建议用无序的值作为聚集索引——会产生存储的碎片化

10.什么时候用不到索引

• 1.索引列上使用函数、表达式、计算（±*/）
• 2.查询时索引类型被隐式转换，比如索引是string类型，查询时用int类型
• 3.like条件中前面带%
• 4.负向查询，Not like 不能，<>和NOT IN在某些情况下可以
• 5.有些情况也不可以使用索引，由于是基于cost的，用不用索引最终由优化器说了算。

5.语句优化

1.主要是索引相关，尽量用到索引

2.in与exists区别

• in：优先查询子查询，再查询主查询，适用于子查询数据量小

  SELECT * FROM A WHERE id IN (SELECT id FROM B);
  以上in()中的查询只执行一次，它查询出B中的所有的id并缓存起来，然后检查A表中查询出的id在缓存中是否存在，如果存在则将A的查询数据加入到结果集中，直到遍历完A表中所有的结果集为止。

• exists：优先查询主查询，主查询的数据集去子查询exists中去寻找，如果存在添加到结果集

  SELECT * FROM a WHERE EXISTS(SELECT 1 FROM b WHERE B.id = A.id);
  EXISTS()查询会执行SELECT * FROM A查询，执行A.length次，并不会将EXISTS()查询结果结果进行缓存，因为EXISTS()查询返回一个布尔值true或flase，它只在乎EXISTS()的查询中是否有记录，与具体的结果集无关。
  EXISTS()查询是将主查询的结果集放到子查询中做验证，根据验证结果是true或false来决定主查询数据结果是否得以保存。

6.事务:数据库管理系统执行过程中的一个逻辑单位，由一个或者多个操作构成。

1.事务的特性

• 原子性：事务要么成功，要么失败（利用undo 日志来实现回滚操作）

  以转账的场景为例，一个账户的余额减少，对应一个账户的增加，这两个一 定是同时成功或者同时失败的。

• 一致性：事务前后数据的完整性必须保持一致

• 隔离性：多个用户并发访问库，每个用户开启一个事务，不能被其他事务的操作所干扰

• 持久性：事务一旦被提交，它对数据库中数据的改变就是永久性的，通过redo log 和 double write双写缓冲来实现的，double write实现了数据库崩溃，数据页本身不会被破坏

2.事务隔离级别

• 未提交读（read-uncommitted）脏读、不可重复读、幻读都不可避免
• 提交读（read-committed）不可重复读、幻读都不可避免
• 可重复读（repeatable-read）
• 串行化（serializable）

3.事务并发带来的问题

总结：无论是脏读、不可重复读、幻读，他们都是数据库的读一致性文帝，都是在一个事务里面前后两次读取结果出现不一致情况，目前有两种解决方案，LBCC （基于锁的并发控制）：仅仅通过锁来实现事务隔离，意味着不支持并发的读写操作，会极大影响操作数据的效率；MVCC（多版本并发控制）核心思想：可以查到这个事务开始之前已经存在的数据，即使后面修改或删除，当前事务查询结果都是一样的。在这个事务之后的新增数据，是查不到的。

• 脏读

  一个事务里面，由于其他的时候修改了 数据并且没有提交，而导致了前后两次读取数据不一致的情况，这种事务并发的问题

• 不可重复读

  一个事务读取到了其他事务已提交的数据导致前后两次读取数据不一致的情况

• 幻读

  一个事务前后两次读取数据数据不一致，是由于其他事务插入数据造成的

4.事务并发带来问题的解决方案

• MVCC：undo log 实现的

  我可以查到在我这个事务开始之前已经存在的数据，即使它 在后面被修改或者删除了。在我这个事务之后新增的数据，我是查不到的。

  • 解决方案：InnoDB为每行记录都实现了两个隐藏的字段，DB_TRX_ID 6字节，事务编号，记录操作的事务ID；DB_ROLL_PTR 7字节 ，回滚指针 数据被删除活记录为旧数据的时候，记录当前事务ID

  • 案例

    https://www.processon.com/view/link/5d29999ee4b07917e2e09298 MVCC 演示图

    • 1.只能查找版本号小于等于当前事务版本的数据行，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
    • 2.行的删除版本要么未定义，要么大于当前事务版本号，这可以确保事务读取到的行，在事务开始之前未被删除

InnoDB 锁

锁的粒度

• 行锁：锁住一张表
• 表锁：锁住一行记录

锁的类型

• 共享锁（Shared Locks）

• 排它锁（Exclusive Locks）

  • 自动加排它锁，比如增删改都会加排它锁
  • 手动加排它锁，for update

• 意向锁

  • 如果一张表上面至少有一个意向共享锁，说明有其他的事务给其中的某些数据行加上了共享锁。
  • 如果一张表上面至少有一个意向排他锁，说明有其他的事务给其中的某些数据行加上了排他锁。

行锁的原理

• 没有索引的表、没有非NULL唯一键约束：会进行全表扫描，然后把每一个隐藏的聚集索引（ROW_ID，6字节）都锁住

• 有索引的表

  • 聚簇索引:直接通过索引锁定数据行
  • 其他索引:找到存储的主键id（显式ID与隐式ID），在通过主键锁定数据行

锁的算法

• 记录锁（Record Lock）：当我们对于唯一性的索引(包括唯一索引和主键索引)使用等值查询，精准匹配到一条记录的时候，这个时候使用的就是记录锁，比如where id=1

• 间隙锁（Gap Lock）：当我们查询的记录不存在，没有命中任何一个 record，无论是用等值 查询还是范围查询的时候，它使用的都是间隙锁。

  • 关闭间隙锁

    如果要关闭间隙锁，就是把事务隔离级别设置成 RC，并且innodb_locks_unsafe_for_binlog 设置为 ON。

  • 左开右开的区间范围

• 临键锁（Next-key Lock）：当我们使用了范围查询，不仅仅命中了 Record 记录，还包含了 Gap 间隙，在这种情况下我们使用的就是临键锁

  • 左开右闭的区间范围

锁的退化

• 1.唯一性索引等值查询匹配到一条记录的时候，退化成记录锁。
• 2.没有匹配到任何记录的时候，退化成间隙锁

锁与事务隔离级别的关系

• 事务隔离级别

  • 未提交读（read-uncommitted）（不加锁）

  • 提交读（read-committed）

    • 普通的select都是快照读，使用MVCC实现
    • 加锁的 select 都使用记录锁，因为基本不用 Gap Lock。
    • 外键约束检查以及重复键检查时会使用间隙锁（Gap Lock）

  • 可重复读（repeatable-read）

    • 普通的select都是快照读，使用MVCC实现
    • 加锁的select以及更新操作等语句使用当前读，底层使用记录锁、间隙所、临键锁

  • 串行化（serializable）

    • 所有的 select 语句都会被隐式的转化为in share mode,会和update、delete等操作互斥

ReadView

• 在RU隔离级别下，直接读取版本最新的记录。
• 在RR隔离级别下，每个事务开始时，会将当前系统中所有的活跃事务拷贝到一个列表生成ReadView
• 在RC隔离级别下，每个语句开始时，会将当前系统中的所有活跃事务拷贝到一个列表生成ReadView

关注公众号，每周都有新内容

获取脑图请回复公众号“mysql”获取PDF版本脑图，需要补充的知识点可以进行留言，逐步进行完善。

最后感谢大家的关注