Mysql进阶

Mysql概述

什么是关系型数据库？

关系型数据库（RDBMS，Relational Database Management System）就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。大部分关系型数据库都使用 SQL 来操作数据库中的数据。并且，大部分关系型数据库都支持事务的四大特性(ACID)。

有哪些常见的关系型数据库？

MySQL、PostgreSQL、Oracle、SQL Server、SQLite（微信本地的聊天记录的存储就是用的 SQLite）。

谈谈Mysql的优点？

MySQL 主要具有下面这些优点：

1. 成熟稳定，功能完善。
2. 开源免费。
3. 文档丰富，既有详细的官方文档，又有非常多优质文章可供参考学习。
4. 开箱即用，操作简单，维护成本低。
5. 兼容性好，支持常见的操作系统，支持多种开发语言。
6. 社区活跃，生态完善。
7. 事务支持优秀， InnoDB 存储引擎默认使用 REPEATABLE-READ 并不会有任何性能损失，并且，InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的。
8. 支持分库分表、读写分离、高可用。

高级数据类型

1.视图(view)

• 虚拟存在的表，不保存查询结果，只保存查询的SQL逻辑

• 简单、安全、数据独立

2.存储过程(PROCEDURE)

• 事先定义并存储在数据库中的一段SQL语句集合
• 减少网络交互，提高性能，封装重用
• 涉及知识点：变量、if、case、参数(in/out/inout)、while、repeat、loop、cursor、handler

3.存储函数(FUNCTION)

• 存储函数是有返回值的存储过程，参数类型只能是in类型
• 存储函数都可以被存储过程替代

4.触发器(TRIGGER)

• 可以在表数据进行INSERT、UPDATE、DELETE之前或之后触发
• 保存数据完整性、日志记录、数据校验

Mysql事务

什么是事务？

事务是逻辑上的一组操作，要么都执行，要么都不执行。

什么是数据库事务？

大多数情况下，我们在谈论事务的时候，如果没有特指分布式事务，往往指的就是数据库事务。

那数据库事务有什么作用呢？

简单来说，数据库事务可以保证多个对数据库的操作（也就是 SQL 语句）构成一个逻辑上的整体。构成这个逻辑上的整体的这些数据库操作遵循：要么全部执行成功,要么全部不执行 。

事务的四大特性

关系型数据库（例如：MySQL、SQL Server、Oracle 等）事务都有 ACID 特性：

1. 原子性（Atomicity） ： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

2. 一致性（Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；

3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

4. 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

此处要注意：只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的！

并发事务的问题

脏读

一个事务读取了另外一个事务还没有提交的数据

如图：事务一将数据A由20修改为19，在还每提交或回滚之前，事务2读取到了中间结果19，即为脏读

不可重复读

一个事务先后读取同一条记录，但两次读取的数据不一样，称为不可重复读

如图：事务2第一查询的结果未A = 20，但在这期间事务1对数据A进行了修改，导致事务2第二次读取到的数据和第一次不一样

幻读

一个事务第一次查询数据时，没有对应的数据行，但另一个事务在这期间插入了某些数据，导致第一个事务在第二次查询(这里的查询不能是普通的查询，否则仍然显示没有数据，这里也说明它是可以重复读的，需要是诸如count统计数量，或插入时报错id为某某的数据已存在)时发现好像多了某些数据一样，就像出现了幻觉

不可重复读 vs 幻读?

• 不可重复读的重点是内容修改或者记录减少比如多次读取一条记录发现其中某些记录的值被修改；
• 幻读的重点在于记录新增比如多次执行同一条查询语句（DQL）时，发现查到的记录增加了,但要注意这里的发现不是普通的查询，比如事务1查询id为3的数据不存在，查询count()的结果为2，那么事务2添加了id为3的数据并提交后，事务1继续查询id为3的数据仍然会显示不存在(如果是可重复读的话)，但是在它重新count()的时候得到的结果却是3，而且你要插入id为3的数据时会报错告诉你主键冲突，但你却查不到该数据。

幻读其实可以看作是不可重复读的一种特殊情况，单独把区分幻读的原因主要是解决幻读和不可重复读的方案不一样。

举个例子：执行 delete 和 update 操作的时候，可以直接对记录加锁，保证事务安全。而执行 insert 操作的时候，由于记录锁（Record Lock）只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁（Gap Lock）。也就是说执行 insert 操作的时候需要依赖 Next-Key Lock（Record Lock+Gap Lock） 进行加锁来保证不出现幻读。

事务隔离级别

• READ-UNCOMMITTED(读取未提交) ： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读
• READ-COMMITTED(读取已提交) ： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生
• REPEATABLE-READ(可重复读) ： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生(这里说的时可能，因为在可重复读下，InnoDB已经使用了临键锁，是可以解决幻读的，但是可能会因为各种原因，如事务主动用 for share 或者 for update 这样带锁的 select 语句，或者说较早开始的事务尝试insert或Update较晚开始但较早结束的事务2提交的新数据，那么仍然会发生幻读)
• SERIALIZABLE(可串行化) ： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读

存储引擎

基本介绍

• Oracle、SqlServer 等数据库只有一种存储引擎，MySQL 提供了插件式的存储引擎架构，所以 MySQL 存在多种存储引擎 , 就会让数据库采取了不同的处理数据的方式和扩展功能
• 在关系型数据库中数据的存储是以表的形式存进行，存储引擎也是表级别的，所以存储引擎也称为表类型（存储和操作此表的类型）
• 通过选择不同的引擎，能够获取最佳的方案, 也能够获得额外的速度或者功能，提高程序的整体效果。

Mysql支持的存储引擎：

• MySQL 支持的引擎包括：InnoDB、MyISAM、MEMORY、Archive、Federate、CSV、BLACKHOLE 等
• MySQL5.5 之前的默认存储引擎是 MyISAM，5.5 之后就改为了 InnoDB

常见存储引擎

MyISAM 存储引擎：

• 特点：不支持事务和外键，读取速度快，节约资源
• 应用场景：适用于读多写少的场景，支持表锁, 对事务的完整性要求不高，比如一些日志，评论信息，数仓、离线数据、支付宝的年度总结之类的场景，业务进行只读操作，查询起来会更快，但逐渐被mongodb取代

InnoDB 存储引擎：(MySQL5.5 版本后默认的存储引擎)

• 特点：支持事务和外键操作，支持并发控制。对比 MyISAM 的存储引擎，InnoDB 写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引,支持行级锁
• 应用场景：对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作

MEMORY 存储引擎：

• 特点：每个 MEMORY 表实际对应一个磁盘文件 ，该文件中只存储表的结构，表数据保存在内存中，且默认使用 HASH 索引，所以数据默认就是无序的，但是在需要快速定位记录可以提供更快的访问，服务一旦关闭，表中的数据就会丢失，存储不安全
• 应用场景：缓存型存储引擎，通常用于更新不太频繁的小表，用以快速得到访问结果

MyISAM vs InooDB

• nnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。
• MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别。
• MyISAM 不支持外键，而 InnoDB 支持。
• MyISAM 不支持 MVVC，而 InnoDB 支持(MVCC 可以看作是行级锁的一个升级)。
• 虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。
• MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log
• InnoDB 的性能比 MyISAM 更强大

总结三点：事务、外键、行级锁

如何选择MyISAM InooDB?

大多数时候我们使用的都是 InnoDB 存储引擎，在某些读密集的情况下，使用 MyISAM 也是合适的。不过，前提是你的项目不介意 MyISAM 不支持事务、崩溃恢复等缺点

索引

介绍

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树 和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构。

索引的优缺点

优点 ：

• 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点 ：

• 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
• 索引需要使用物理文件存储，也会耗费一定空间。

但是，使用索引一定能提高查询性能吗?

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

B树 VS B+ 树

B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。

目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B 树& B+树两者有何异同呢？

• B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

索引分类

索引一般的分类如下：

• 功能分类

  • 主键索引：一种特殊的唯一索引，不允许有空值，一般在建表时同时创建主键索引
  • 单列索引：一个索引只包含单个列，一个表可以有多个单列索引（普通索引）
  • 联合索引：顾名思义，就是将单列索引进行组合
  • 唯一索引：索引列的值必须唯一，允许有空值，如果是联合索引，则列值组合必须唯一
    • NULL 值可以出现多次，因为两个 NULL 比较的结果既不相等，也不不等，结果仍然是未知
    • 可以声明不允许存储 NULL 值的非空唯一索引
  • 外键索引：只有 InnoDB 引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作

• 结构分类

  • BTree 索引：MySQL 使用最频繁的一个索引数据结构，是 InnoDB 和 MyISAM 存储引擎默认的索引类型，底层基于 B+Tree
  • Hash 索引：MySQL中 Memory 存储引擎默认支持的索引类型
  • R-tree 索引（空间索引）：空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型
  • Full-text 索引（全文索引）：快速匹配全部文档的方式。MyISAM 支持， InnoDB 不支持 FULLTEXT 类型的索引，但是 InnoDB 可以使用 sphinx 插件支持全文索引，MEMORY 引擎不支持

  索引	InnoDB	MyISAM	Memory
  BTREE	支持	支持	支持
  HASH	不支持	不支持	支持
  R-tree	不支持	支持	不支持
  Full-text	5.6 版本之后支持	支持	不支持

聚簇索引

索引对比

聚簇索引是一种数据存储方式，并不是一种单独的索引类型

• 聚簇索引的叶子节点存放的是主键值和数据行，支持覆盖索引

• 非聚簇索引的叶子节点存放的是主键值或指向数据行的指针（由存储引擎决定）

在 Innodb 下主键索引是聚簇索引，在 MyISAM 下主键索引是非聚簇索引

Innodb

聚簇索引

在 Innodb 存储引擎，B+ 树索引可以分为聚簇索引（也称聚集索引、clustered index）和辅助索引（也称非聚簇索引或二级索引、secondary index、non-clustered index）

InnoDB 中，聚簇索引是按照每张表的主键构造一颗 B+ 树，叶子节点中存放的就是整张表的数据，将聚簇索引的叶子节点称为数据页

• 这个特性决定了数据也是索引的一部分，所以一张表只能有一个聚簇索引
• 辅助索引的存在不影响聚簇索引中数据的组织，所以一张表可以有多个辅助索引

聚簇索引的优点：

• 数据访问更快，聚簇索引将索引和数据保存在同一个 B+ 树中，因此从聚簇索引中获取数据比非聚簇索引更快
• 聚簇索引对于主键的排序查找和范围查找速度非常快

聚簇索引的缺点：

• 插入速度严重依赖于插入顺序，按照主键的顺序（递增）插入是最快的方式，否则将会出现页分裂，严重影响性能，所以对于 InnoDB 表，一般都会定义一个自增的 ID 列为主键

• 更新主键的代价很高，将会导致被更新的行移动，所以对于 InnoDB 表，一般定义主键为不可更新

• 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

辅助索引

在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引等

辅助索引叶子节点存储的是主键值，而不是数据的物理地址，所以访问数据需要二次查找，推荐使用覆盖索引，可以减少回表查询

检索过程：辅助索引找到主键值，再通过聚簇索引（二分）找到数据页，最后通过数据页中的 Page Directory（二分）找到对应的数据分组，遍历组内所所有的数据找到数据行

补充：无索引走全表查询，查到数据页后和上述步骤一致

索引实现

InnoDB 使用 B+Tree 作为索引结构，并且 InnoDB 一定有索引

主键索引：

• 在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，这个索引的 key 是数据表的主键，叶子节点 data 域保存了完整的数据记录

• InnoDB 的表数据文件通过主键聚集数据，如果没有定义主键，会选择非空唯一索引代替，如果也没有这样的列，MySQL 会自动为 InnoDB 表生成一个隐含字段 row_id 作为主键，这个字段长度为 6 个字节，类型为长整形

辅助索引：

• InnoDB 的所有辅助索引（二级索引）都引用主键作为 data 域

• InnoDB 表是基于聚簇索引建立的，因此 InnoDB 的索引能提供一种非常快速的主键查找性能。不过辅助索引也会包含主键列，所以不建议使用过长的字段作为主键，过长的主索引会令辅助索引变得过大

MyISAM

非聚簇

MyISAM 的主键索引使用的是非聚簇索引，索引文件和数据文件是分离的，索引文件仅保存数据的地址

• 主键索引 B+ 树的节点存储了主键，辅助键索引 B+ 树存储了辅助键，表数据存储在独立的地方，这两颗 B+ 树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别
• 由于索引树是独立的，通过辅助索引检索无需回表查询访问主键的索引树

索引操作

索引在创建表的时候可以同时创建， 也可以随时增加新的索引

• 创建索引：如果一个表中有一列是主键，那么会默认为其创建主键索引（主键列不需要单独创建索引）

  CREATE [UNIQUE|FULLTEXT] INDEX 索引名称 [USING 索引类型] ON 表名(列名...);
  -- 索引类型默认是 B+TREE
  

• 查看索引

  SHOW INDEX FROM 表名;
  

• 添加索引

  -- 单列索引
  ALTER TABLE 表名 ADD INDEX 索引名称(列名);
  
  -- 组合索引
  ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);
  
  -- 主键索引
  ALTER TABLE 表名 ADD PRIMARY KEY(主键列名); 
  
  -- 外键索引(添加外键约束，就是外键索引)
  ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
  
  -- 唯一索引
  ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);
  
  -- 全文索引(mysql只支持文本类型)
  ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);
  

• 删除索引

  DROP INDEX 索引名称 ON 表名;
  

• 案例练习

  数据准备：student

  id	NAME	 age	score
  1	张三		23		99
  2	李四		24		95
  3	王五		25		98
  4	赵六		26		97
  

  索引操作：

  -- 为student表中姓名列创建一个普通索引
  CREATE INDEX idx_name ON student(NAME);
  
  -- 为student表中年龄列创建一个唯一索引
  CREATE UNIQUE INDEX idx_age ON student(age);
  

索引设计原则

引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率

创建索引时的原则：

• 对查询频次较高，且数据量比较大的表建立索引
• 使用唯一索引，区分度越高，使用索引的效率越高
• 索引字段的选择，最佳候选列应当从 where 子句的条件中提取，使用覆盖索引
• 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的 I/O 效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升 MySQL 访问索引的 I/O 效率
• 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价越高。对于插入、更新、删除等 DML 操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低 DML 操作的效率，增加相应操作的时间消耗；另外索引过多的话，MySQL 也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但提高了选择的代价

• MySQL 建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配

哪些情况不要建立索引：

• 记录太少的表
• 经常增删改的表
• 频繁更新的字段不适合创建索引
• where 条件里用不到的字段不创建索引

索引优化

覆盖索引

覆盖索引：包含所有满足查询需要的数据的索引（SELECT 后面的字段刚好是索引字段），可以利用该索引返回 SELECT 列表的字段，而不必根据索引去聚簇索引上读取数据文件

回表查询：要查找的字段不在非主键索引树上时，需要通过叶子节点的主键值去主键索引上获取对应的行数据

使用覆盖索引，防止回表查询：

• 表 user 主键为 id，普通索引为 age，查询语句：

  SELECT * FROM user WHERE age = 30;
  

  查询过程：先通过普通索引 age=30 定位到主键值 id=1，再通过聚集索引 id=1 定位到行记录数据，需要两次扫描 B+ 树

• 使用覆盖索引：

  DROP INDEX idx_age ON user;
  CREATE INDEX idx_age_name ON user(age,name);
  SELECT id,age FROM user WHERE age = 30;
  

  在一棵索引树上就能获取查询所需的数据，无需回表速度更快

使用覆盖索引，要注意 SELECT 列表中只取出需要的列，不可用 SELECT *，所有字段一起做索引会导致索引文件过大，查询性能下降

---

索引下推

索引条件下推优化（Index Condition Pushdown，ICP）是 MySQL5.6 添加，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

索引下推充分利用了索引中的数据，在查询出整行数据之前过滤掉无效的数据，再去主键索引树上查找

• 不使用索引下推优化时存储引擎通过索引检索到数据，然后回表查询记录返回给 Server 层，服务器判断数据是否符合条件

  

• 使用索引下推优化时，如果存在某些被索引的列的判断条件时，由存储引擎在索引遍历的过程中判断数据是否符合传递的条件，将符合条件的数据进行回表，检索出来返回给服务器，由此减少 IO 次数

  

适用条件：

• 需要存储引擎将索引中的数据与条件进行判断（所以条件列必须都在同一个索引中），所以优化是基于存储引擎的，只有特定引擎可以使用，适用于 InnoDB 和 MyISAM
• 存储引擎没有调用跨存储引擎的能力，跨存储引擎的功能有存储过程、触发器、视图，所以调用这些功能的不可以进行索引下推优化
• 对于 InnoDB 引擎只适用于二级索引，InnoDB 的聚簇索引会将整行数据读到缓冲区，不再需要去回表查询了

工作过程：用户表 user，(name, age) 是联合索引

SELECT * FROM user WHERE name LIKE '张%' AND　age = 10;	-- 头部模糊匹配会造成索引失效

• 优化前：在非主键索引树上找到满足第一个条件的行，然后通过叶子节点记录的主键值再回到主键索引树上查找到对应的行数据，再对比 AND 后的条件是否符合，符合返回数据，需要 4 次回表

  

• 优化后：检查索引中存储的列信息是否符合索引条件，然后交由存储引擎用剩余的判断条件判断此行数据是否符合要求，不满足条件的不去读取表中的数据，满足下推条件的就根据主键值进行回表查询，2 次回表
  

当使用 EXPLAIN 进行分析时，如果使用了索引条件下推，Extra 会显示 Using index condition

---

前缀索引

当要索引的列字符很多时，比如长文本字段，索引会变大变慢，可以只索引列开始的部分字符串，节约索引空间，提高索引效率

注意：使用前缀索引就系统就忽略覆盖索引对查询性能的优化了

优化原则：降低重复的索引值

比如地区表：

area			gdp		code
chinaShanghai	100		aaa
chinaDalian		200		bbb
usaNewYork		300		ccc
chinaFuxin		400		ddd
chinaBeijing	500		eee

发现 area 字段很多都是以 china 开头的，那么如果以前 1-5 位字符做前缀索引就会出现大量索引值重复的情况，索引值重复性越低，查询效率也就越高，所以需要建立前 6 位字符的索引：

CREATE INDEX idx_area ON table_name(area(7));

场景：存储身份证

• 直接创建完整索引，这样可能比较占用空间
• 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引
• 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题（前 6 位相同的很多）
• 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描

---

索引合并

使用多个索引来完成一次查询的执行方法叫做索引合并 index merge

• Intersection 索引合并：

  SELECT * FROM table_test WHERE key1 = 'a' AND key3 = 'b'; # key1 和 key3 列都是单列索引、二级索引
  

  从不同索引中扫描到的记录的 id 值取交集（相同 id），然后执行回表操作，要求从每个二级索引获取到的记录都是按照主键值排序

• Union 索引合并：

  SELECT * FROM table_test WHERE key1 = 'a' OR key3 = 'b';
  

  从不同索引中扫描到的记录的 id 值取并集，然后执行回表操作，要求从每个二级索引获取到的记录都是按照主键值排序

• Sort-Union 索引合并

  SELECT * FROM table_test WHERE key1 < 'a' OR key3 > 'b';
  

  先将从不同索引中扫描到的记录的主键值进行排序，再按照 Union 索引合并的方式进行查询

索引合并算法的效率并不好，通过将其中的一个索引改成联合索引会优化效率

索引失效

语句错误

• 全值匹配：对索引中所有列都指定具体值，这种情况索引生效，执行效率高

  EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND status='1' AND address='西安市';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GBUQu2qN-1676805734982)(https://seazean.oss-cn-beijing.aliyuncs.com/img/DB/MySQL-优化SQL使用索引1.png)]

• 最左前缀法则：联合索引遵守最左前缀法则

  匹配最左前缀法则，走索引：

  EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技';
  EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND status='1';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DWfFORNn-1676805734983)(https://seazean.oss-cn-beijing.aliyuncs.com/img/DB/MySQL-优化SQL使用索引2.png)]

  违法最左前缀法则 ， 索引失效：

  EXPLAIN SELECT * FROM tb_seller WHERE status='1';
  EXPLAIN SELECT * FROM tb_seller WHERE status='1' AND address='西安市';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qIjNww8M-1676805734983)(https://seazean.oss-cn-beijing.aliyuncs.com/img/DB/MySQL-优化SQL使用索引3.png)]

  如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

  EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND address='西安市';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HLkXqGvw-1676805734984)(https://seazean.oss-cn-beijing.aliyuncs.com/img/DB/MySQL-优化SQL使用索引4.png)]

  虽然索引列失效，但是系统会使用了索引下推进行了优化(减少回表查询)

• 范围查询右边的列，不能使用索引：

  EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND status>'1' AND address='西安市';
  

  根据前面的两个字段 name ， status 查询是走索引的， 但是最后一个条件 address 没有用到索引，使用了索引下推

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0B8hYOPf-1676805734984)(https://seazean.oss-cn-beijing.aliyuncs.com/img/DB/MySQL-优化SQL使用索引5.png)]

• 在索引列上函数或者运算（+ - 数值）操作， 索引将失效：会破坏索引值的有序性

  EXPLAIN SELECT * FROM tb_seller WHERE SUBSTRING(name,3,2) = '科技';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ESB9zUHv-1676805734984)(https://seazean.oss-cn-beijing.aliyuncs.com/img/DB/MySQL-优化SQL使用索引6.png)]

• 字符串不加单引号，造成索引失效：隐式类型转换，当字符串和数字比较时会把字符串转化为数字

  没有对字符串加单引号，查询优化器会调用 CAST 函数将 status 转换为 int 进行比较，造成索引失效

  EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND status = 1;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pKNFB1Zo-1676805734986)(https://seazean.oss-cn-beijing.aliyuncs.com/img/DB/MySQL-优化SQL使用索引7.png)]

  如果 status 是 int 类型，SQL 为 SELECT * FROM tb_seller WHERE status = '1' 并不会造成索引失效，因为会将 '1' 转换为 1，并不会对索引列产生操作

• 多表连接查询时，如果两张表的字符集不同，会造成索引失效，因为会进行类型转换

  解决方法：CONVERT 函数是加在输入参数上、修改表的字符集

• 用 OR 分割条件，索引失效，导致全表查询：

  OR 前的条件中的列有索引而后面的列中没有索引或 OR 前后两个列是同一个复合索引，都造成索引失效

  EXPLAIN SELECT * FROM tb_seller WHERE name='阿里巴巴' OR createtime = '2088-01-01 12:00:00';
  EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' OR status='1';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-01fMLNcz-1676805734987)(https://seazean.oss-cn-beijing.aliyuncs.com/img/DB/MySQL-优化SQL使用索引10.png)]

  AND 分割的条件不影响：

  EXPLAIN SELECT * FROM tb_seller WHERE name='阿里巴巴' AND createtime = '2088-01-01 12:00:00';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-H2Il0Zax-1676805734987)(https://seazean.oss-cn-beijing.aliyuncs.com/img/DB/MySQL-优化SQL使用索引11.png)]

• 以 % 开头的 LIKE 模糊查询，索引失效：

  如果是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效

  EXPLAIN SELECT * FROM tb_seller WHERE name like '%科技%';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JuV1Dtnj-1676805734988)(https://seazean.oss-cn-beijing.aliyuncs.com/img/DB/MySQL-优化SQL使用索引12.png)]

  解决方案：通过覆盖索引来解决

  EXPLAIN SELECT sellerid,name,status FROM tb_seller WHERE name like '%科技%';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-skzhQl2Z-1676805734988)(https://seazean.oss-cn-beijing.aliyuncs.com/img/DB/MySQL-优化SQL使用索引13.png)]

  原因：在覆盖索引的这棵 B+ 数上只需要进行 like 的匹配，或者是基于覆盖索引查询再进行 WHERE 的判断就可以获得结果

---

系统优化

系统优化为全表扫描：

• 如果 MySQL 评估使用索引比全表更慢，则不使用索引，索引失效：

  CREATE INDEX idx_address ON tb_seller(address);
  EXPLAIN SELECT * FROM tb_seller WHERE address='西安市';
  EXPLAIN SELECT * FROM tb_seller WHERE address='北京市';
  

  北京市的键值占 9/10（区分度低），所以优化为全表扫描，type = ALL

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GlNnf4jP-1676805734989)(https://seazean.oss-cn-beijing.aliyuncs.com/img/DB/MySQL-优化SQL使用索引14.png)]

• IS NULL、IS NOT NULL 有时索引失效：

  EXPLAIN SELECT * FROM tb_seller WHERE name IS NULL;
  EXPLAIN SELECT * FROM tb_seller WHERE name IS NOT NULL;
  

  NOT NULL 失效的原因是 name 列全部不是 null，优化为全表扫描，当 NULL 过多时，IS NULL 失效

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-I45C6Vpp-1676805734989)(https://seazean.oss-cn-beijing.aliyuncs.com/img/DB/MySQL-优化SQL使用索引15.png)]

• IN 肯定会走索引，但是当 IN 的取值范围较大时会导致索引失效，走全表扫描：

  EXPLAIN SELECT * FROM tb_seller WHERE sellerId IN ('alibaba','huawei');-- 都走索引
  EXPLAIN SELECT * FROM tb_seller WHERE sellerId NOT IN ('alibaba','huawei');
  

锁

锁分类

• 按操作分类：
  • 共享锁：也叫读锁。对同一份数据，多个事务读操作可以同时加锁而不互相影响 ，但不能修改数据
  • 排他锁：也叫写锁。当前的操作没有完成前，会阻断其他操作的读取和写入
• 按粒度分类：
  • 表级锁：会锁定整个表，开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低，偏向 MyISAM
  • 行级锁：会锁定当前操作行，开销大，加锁慢；会出现死锁；锁定力度小，发生锁冲突概率低，并发度高，偏向 InnoDB
  • 页级锁：锁的力度、发生冲突的概率和加锁开销介于表锁和行锁之间，会出现死锁，并发性能一般
  • 全局锁：对整个数据库的所有表加锁，常用于数据备份
• 按使用方式分类：
  • 悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁
  • 乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据

表锁 vs 行级锁

• 表级锁： MySQL 中锁定粒度最大的一种锁（全局锁除外），是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。
• 行级锁： MySQL 中锁定粒度最小的一种锁，是 针对索引字段加的锁 ，只针对当前操作的行记录进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的。

聊聊InnoDB中的锁

InnoDB 有哪几类行锁？

InnoDB 行锁是通过对索引数据页上的记录加锁实现的，MySQL InnoDB 支持三种行锁定方式：

• 记录锁（Record Lock） ：也被称为记录锁，属于单个行记录上的锁。
• 间隙锁（Gap Lock） ：锁定一个范围，不包括记录本身。
• 临键锁（Next-Key Lock） ：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

共享锁和排他锁

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

• 共享锁（S 锁） ：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
• 排他锁（X 锁） ：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

	S 锁	X 锁
S 锁	不冲突	冲突
X 锁	冲突	冲突

由于 MVCC 的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁。不过， 你可以通过以下语句显式加共享锁或排他锁。

# 共享锁
SELECT ... LOCK IN SHARE MODE;
# 排他锁
SELECT ... FOR UPDATE;

意向锁

如果需要用到表锁的话，如何判断表中的记录没有行锁呢，一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁的东东来快速判断是否可以对某个表使用表锁。

意向锁是表级锁，共有两种：

• 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

意向锁之间是互相兼容的。

	IS 锁	IX 锁
IS 锁	兼容	兼容
IX 锁	兼容	兼容

意向锁和共享锁和排它锁互斥（这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥）。

	IS 锁	IX 锁
S 锁	兼容	互斥
X 锁	互斥	互斥

SQL优化

SQL性能分析

SQL执行频次

通过show [session|global] status命令可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次，我们主要针对查询为主的数据表进行索引上的性能优化

慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位：秒，默认10秒)的所有SQL语句的日志。Mysql的慢查询日志默认没有开启，需要在Mysql的配置文件(/etc/my.cnf)中配置如下信息

# 开启Mysql慢查询日志
show_query_log=1
   
# 设置慢查询日志的时间为2s,SQL语句执行时间超过2秒，就会被视为慢查询，记录在慢查询日志中
long_query_time = 2

profile查看SQL耗时

show profiles能够在做SQL优化时帮助我们了解事件都耗费到哪里去了。通过having_profiling能够看到当前Mysql是否支持profile操作:

SELECT @@have_profiling;

# 默认profiling是关闭的，可以手动开启
SET profiling=1;

使用语法：

# 查看每一条SQL的耗时基本情况
show profiles;

#查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query_query_id;

#查看指定query_id的SQL语句CPU使用情况
show profile cpu for query query_id;

EXPLAIN

执行计划

通过 EXPLAIN 命令获取执行 SQL 语句的信息，包括在 SELECT 语句执行过程中如何连接和连接的顺序，执行计划在优化器优化完成后、执行器之前生成，然后执行器会调用存储引擎检索数据

查询 SQL 语句的执行计划：

EXPLAIN SELECT * FROM table_1 WHERE id = 1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bFFarP7i-1676805734989)(https://seazean.oss-cn-beijing.aliyuncs.com/img/DB/MySQL-explain查询SQL语句的执行计划.png)]

字段	含义
id	SELECT 的序列号
select_type	表示 SELECT 的类型
table	访问数据库中表名称，有时可能是简称或者临时表名称（<table_name>）
type	表示表的连接类型
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
ref	表示与索引列进行等值匹配的对象，常数、某个列、函数等，type 必须在（range, const] 之间，左闭右开
rows	扫描出的行数，表示 MySQL 根据表统计信息及索引选用情况，估算的找到所需的记录扫描的行数
filtered	条件过滤的行百分比，单表查询没意义，用于连接查询中对驱动表的扇出进行过滤，查询优化器预测所有扇出值满足剩余查询条件的百分比，相乘以后表示多表查询中还要对被驱动执行查询的次数
extra	执行情况的说明和描述

id

id 代表 SQL 执行的顺序的标识，每个 SELECT 关键字对应一个唯一 id，所以在同一个 SELECT 关键字中的表的 id 都是相同的。SELECT 后的 FROM 可以跟随多个表，每个表都会对应一条记录，这些记录的 id 都是相同的，

• id 相同时，执行顺序由上至下。连接查询的执行计划，记录的 id 值都是相同的，出现在前面的表为驱动表，后面为被驱动表

  EXPLAIN SELECT * FROM t_role r, t_user u, user_role ur WHERE r.id = ur.role_id AND u.id = ur.user_id ;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TwteAveK-1676805734990)(https://seazean.oss-cn-beijing.aliyuncs.com/img/DB/MySQL-explain之id相同.png)]

• id 不同时，id 值越大优先级越高，越先被执行

  EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gBV6eeWp-1676805734990)(https://seazean.oss-cn-beijing.aliyuncs.com/img/DB/MySQL-explain之id不同.png)]

• id 有相同也有不同时，id 相同的可以认为是一组，从上往下顺序执行；在所有的组中，id 的值越大的组，优先级越高，越先执行

  EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id ; 
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KnM08sRb-1676805734991)(https://seazean.oss-cn-beijing.aliyuncs.com/img/DB/MySQL-explain之id相同和不同.png)]

• id 为 NULL 时代表的是临时表

---

select

表示查询中每个 select 子句的类型（简单 OR 复杂）

select_type	含义
SIMPLE	简单的 SELECT 查询，查询中不包含子查询或者 UNION
PRIMARY	查询中若包含任何复杂的子查询，最外层（也就是最左侧）查询标记为该标识
UNION	对于 UNION 或者 UNION ALL 的复杂查询，除了最左侧的查询，其余的小查询都是 UNION
UNION RESULT	UNION 需要使用临时表进行去重，临时表的是 UNION RESULT
DEPENDENT UNION	对于 UNION 或者 UNION ALL 的复杂查询，如果各个小查询都依赖外层查询，是相关子查询，除了最左侧的小查询为 DEPENDENT SUBQUERY，其余都是 DEPENDENT UNION
SUBQUERY	子查询不是相关子查询，该子查询第一个 SELECT 代表的查询就是这种类型，会进行物化（该子查询只需要执行一次）
DEPENDENT SUBQUERY	子查询是相关子查询，该子查询第一个 SELECT 代表的查询就是这种类型，不会物化（该子查询需要执行多次）
DERIVED	在 FROM 列表中包含的子查询，被标记为 DERIVED（衍生），也就是生成物化派生表的这个子查询
MATERIALIZED	将子查询物化后与与外层进行连接查询，生成物化表的子查询

子查询为 DERIVED：SELECT * FROM (SELECT key1 FROM t1) AS derived_1 WHERE key1 > 10

子查询为 MATERIALIZED：SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2)

---

type

对表的访问方式，表示 MySQL 在表中找到所需行的方式，又称访问类型

type	含义
ALL	全表扫描，如果是 InnoDB 引擎是扫描聚簇索引
index	可以使用覆盖索引，但需要扫描全部索引
range	索引范围扫描，常见于 between、<、> 等的查询
index_subquery	子查询可以普通索引，则子查询的 type 为 index_subquery
unique_subquery	子查询可以使用主键或唯一二级索引，则子查询的 type 为 index_subquery
index_merge	索引合并
ref_or_null	非唯一性索引（普通二级索引）并且可以存储 NULL，进行等值匹配
ref	非唯一性索引与常量等值匹配
eq_ref	唯一性索引（主键或不存储 NULL 的唯一二级索引）进行等值匹配，如果二级索引是联合索引，那么所有联合的列都要进行等值匹配
const	通过主键或者唯一二级索引与常量进行等值匹配
system	system 是 const 类型的特例，当查询的表只有一条记录的情况下，使用 system
NULL	MySQL 在优化过程中分解语句，执行时甚至不用访问表或索引

从上到下，性能从差到好，一般来说需要保证查询至少达到 range 级别， 最好达到 ref

---

key

possible_keys：

• 指出 MySQL 能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
• 如果该列是 NULL，则没有相关的索引

key：

• 显示 MySQL 在查询中实际使用的索引，若没有使用索引，显示为 NULL
• 查询中若使用了覆盖索引，则该索引可能出现在 key 列表，不出现在 possible_keys

key_len：

• 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
• key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的
• 在不损失精确性的前提下，长度越短越好

---

Extra

其他的额外的执行计划信息，在该列展示：

• No tables used：查询语句中使用 FROM dual 或者没有 FROM 语句
• Impossible WHERE：查询语句中的 WHERE 子句条件永远为 FALSE，会导致没有符合条件的行
• Using index：该值表示相应的 SELECT 操作中使用了覆盖索引（Covering Index）
• Using index condition：第一种情况是搜索条件中虽然出现了索引列，但是部分条件无法形成扫描区间（索引失效），会根据可用索引的条件先搜索一遍再匹配无法使用索引的条件，回表查询数据；第二种是使用了索引条件下推优化
• Using where：搜索的数据需要在 Server 层判断，无法使用索引下推
• Using join buffer：连接查询被驱动表无法利用索引，需要连接缓冲区来存储中间结果
• Using filesort：无法利用索引完成排序（优化方向），需要对数据使用外部排序算法，将取得的数据在内存或磁盘中进行排序
• Using temporary：表示 MySQL 需要使用临时表来存储结果集，常见于排序、去重（UNION）、分组等场景
• Select tables optimized away：说明仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
• No tables used：Query 语句中使用 from dual 或不含任何 from 子句

---

InnoDB MVCC

三大日志

MySQL 日志 主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中，比较重要的还要属二进制日志 binlog（归档日志）和事务日志 redo log（重做日志）和 undo log（回滚日志）。

redo log

redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。

比如 MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。

MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。

后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。

更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。

然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

binlog

redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。

而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。

不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。

那 binlog 到底是用来干嘛的？

可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

undo log

我们知道如果想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。如果执行过程中遇到异常的话，我们直接利用 回滚日志 中的信息将数据回滚到修改之前的样子即可！并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

另外，MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改

Innodb对mvcc的实现

MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改

隐藏字段

在内部，InnoDB 存储引擎为每行数据添加了三个隐藏字段

• DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务 id。此外，delete 操作在内部被视为更新，只不过会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除
• DB_ROLL_PTR（7字节） 回滚指针，指向该行的 undo log 。如果该行未被更新，则为空
• DB_ROW_ID（6字节）：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索

ReadView

class ReadView {
  /* ... */
private:
  trx_id_t m_low_limit_id;      /* 大于等于这个 ID 的事务均不可见 */

  trx_id_t m_up_limit_id;       /* 小于这个 ID 的事务均可见 */

  trx_id_t m_creator_trx_id;    /* 创建该 Read View 的事务ID */

  trx_id_t m_low_limit_no;      /* 事务 Number, 小于该 Number 的 Undo Logs 均可以被 Purge */

  ids_t m_ids;                  /* 创建 Read View 时的活跃事务列表 */

  m_closed;                     /* 标记 Read View 是否 close */
}

ReadView主要是用来做可见性判断，里面保存了 “当前对本事务不可见的其他活跃事务”

主要有以下字段：

• m_low_limit_id：目前出现过的最大的事务 ID+1，即下一个将被分配的事务 ID。大于等于这个 ID 的数据版本均不可见
• m_up_limit_id：活跃事务列表 m_ids 中最小的事务 ID，如果 m_ids 为空，则 m_up_limit_id 为 m_low_limit_id。小于这个 ID 的数据版本均可见
• m_ids：Read View 创建时其他未提交的活跃事务 ID 列表。创建 Read View时，将当前未提交事务 ID 记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的。m_ids 不包括当前事务自己和已提交的事务（正在内存中）
• m_creator_trx_id：创建该 Read View 的事务 ID

undo-log

undo log 主要有两个作用：

• 当事务回滚时用于将数据恢复到修改前的样子

• 另一个作用是 MVCC ，当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现非锁定读

• 不同事务或者相同事务的对同一记录行的修改，会使该记录行的 undo log 成为一条链表，链首就是最新的记录，链尾就是最早的旧记录，称为undolog版本链，借助此链就可以寻找到最近的一条允许访问的数据记录

数据可见性算法

在 InnoDB 存储引擎中，创建一个新事务后，执行每个 select 语句前，都会创建一个快照（Read View），快照中保存了当前数据库系统中正处于活跃（没有 commit）的事务的 ID 号。其实简单的说保存的是系统中当前不应该被本事务看到的其他事务 ID 列表（即 m_ids）。当用户在这个事务中要读取某个记录行的时候，InnoDB 会将该记录行的 DB_TRX_ID 与 Read View 中的一些变量及当前事务 ID 进行比较，判断是否满足可见性条件

RC 和 RR 隔离级别下 MVCC 的差异

在事务隔离级别 RC 和 RR （InnoDB 存储引擎的默认事务隔离级别）下，InnoDB 存储引擎使用 MVCC（非锁定一致性读），但它们生成 Read View 的时机却不同

• 在 RC 隔离级别下的 每次select 查询前都生成一个Read View (m_ids 列表)
• 在 RR 隔离级别下只在事务开始后 第一次select 数据前生成一个Read View（m_ids 列表），本次事务的其余select操作则直接复用第一次生成的read view，这样就保证了在本次事务中的可重复读，因为沿着undolog版本链的查找结果必然相同

MVCC + Next-key-Lock 防止幻读

InnoDB存储引擎在 RR 级别下通过 MVCC和 Next-key Lock 来解决幻读问题：

1、执行普通 select，此时会以 MVCC 快照读的方式读取数据

在快照读的情况下，RR 隔离级别只会在事务开启后的第一次查询生成 Read View ，并使用至事务提交。所以在生成 Read View 之后其它事务所做的更新、插入记录版本对当前事务并不可见，实现了可重复读和防止快照读下的 “幻读”

2、执行 select…for update/lock in share mode、insert、update、delete 等当前读

在当前读下，读取的都是最新的数据，如果其它事务有插入新的记录，并且刚好在当前事务查询范围内，就会产生幻读！InnoDB 使用Next-key-Lock 来防止这种情况。当执行当前读时，会锁定读取到的记录的同时，锁定它们的间隙，防止其它事务在查询范围内插入数据。只要我不让你插入，就不会发生幻读