MySQL知识点

MySQL知识点总结：索引、架构、锁、事物、隔离级别。

[1] 参考了CSDN上一个大佬关于MySQL的面经总结

[2] 参考了cyc2018大佬主页

[3] 参考了JavaGuide的数据库索引文章

说实话现在只会背背事务及隔离级别、各种锁的面经，但数据库底层的实现仍然是一无所知，Innodb引擎那本书也还没看到重点部分，权且先用大佬通俗的文章先理解一下，三个重点：

• 索引
• MySQL的基础架构 一条sql语句的执行过程
• 锁
• 事务和隔离级别
• 不同存储引擎的区别（面经）

1 索引

1.1 索引的定义、使用场景

什么是索引？

索引，是数据库管理系统中一个排好序的数据结构，类似于书籍的目录，想找到一本书的某个特定的主题，需要先找到书的目录，定位对应的页码。

MySQL 中存储引擎使用类似的方式进行查询，先去索引中查找对应的值，然后根据匹配的索引找到对应的数据行。

（where后面出现的列都是索引么？索引跟主键有什么区别和关系）

索引有什么好处？

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。

索引有什么坏处？

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
• 空间方面：索引需要占物理空间。

索引的使用场景？

• 1、对非常小的表，大部分情况下全表扫描效率更高。
• 2、对中大型表，索引非常有效。
• 3、特大型的表，建立和使用索引的代价随着增长，可以使用分区技术来解决。

表的两种切分方式：

• 水平切分Sharding ：它是将同一个表中的记录拆分到多个结构相同的表中。

  • 当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

• 垂直切分：垂直切分是将一张表按列切分成多个表

  • 通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

  • 在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。

实际场景下，MySQL 分区表很少使用，原因可以看看 《互联网公司为啥不使用 MySQL 分区表？》 文章。

对于特大型的表，更常用的是“分库分表”，目前解决方案有 Sharding Sphere、MyCAT 等等。

1.2 索引按应用层次分类

索引，都是实现在存储引擎层的。根据应用层次分为六种类型：

1、普通索引：最基本的索引，允许重复和为null。

2、唯一索引：与普通索引类似，允许为空，但具有唯一性约束。（主键索引是特殊的唯一索引，不允许有空值。）

4、复合索引：将多个列组合在一起创建索引，可以覆盖多个列。

5、外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作。

6、全文索引：MySQL 自带的全文索引只能用于 InnoDB、MyISAM ，并且只能对英文进行全文检索，一般使用全文索引引擎。

常用的全文索引引擎的解决方案有 Elasticsearch、Solr 等等。最为常用的是 Elasticsearch 。

具体的使用，可以看看 《服务端指南 数据存储篇 | MySQL（03） 如何设计索引》 。

==有一个问题：外键索引究竟是什么？前缀索引又是什么？最左匹配是什么？==

1.3 索引的底层实现（数据结构）

索引根据存储结构来分，索引分为 BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。

Hash索引 ：哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据。

B-Tree索引：B树的数据结构，数据分布在各个节点之上

B+Tree索引：B+树的数据结构，所有数据分布在叶子节点上、非终端节点看成是索引部分

回头看看大话数据结构的B树去了

hash索引

hash = hashfunc(key)
index = hash % array_size

既然哈希表查找这么快，为什么MySQL 没有使用其作为索引的数据结构呢？有两点

• 1 Hash 冲突问题（hash冲突不是主要的问题），hash冲突可以通过链地址法、再散列函数法等方法解决
• 2 Hash 索引不支持顺序和范围查询（最大的缺点），比如下面的的范围查找

SELECT * FROM tb1 WHERE id < 500;
# Hash 索引是根据 hash 算法来定位的，范围查找时难不成还要把 1 - 499 的数据，每个都进行一次 hash 计算来定位吗?

两种B树索引的区别

• B+树的检索更为稳定
  • B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
• B+树的叶子结点包含全部关键字信息更适合于顺序检索，范围检索
  • 1要排序遍历的话，直接从左到右遍历叶子节点
  • 2 适合带范围的查找：先从根节点出发找到最小值的位置，但后定位到叶子节点中，一直遍历找直到找到最大值

1.4 索引存储位置的分类

索引按索引表存放的位置分为聚集索引和非聚集索引

1.4.1 聚集索引

聚集索引即索引结构和数据一起存放的索引。==主键索引属于聚集索引==。

聚集索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

聚集索引的优点：

• 聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

聚集索引的缺点：

• 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
• 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。

1.4.2 非聚集索引

非聚集索引即索引结构和数据分开存放的索引。 唯一索引，普通索引，前缀索引等索引都属于二级索引（辅助索引），二级索引属于非聚集索引。

二级索引的叶子节点存放的是主键，一般查到主键后再回表查数据。(大部分是，也不绝对)

非聚集索引的优点：

• 更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的

非聚集索引的缺点：

• 跟聚集索引一样，非聚集索引也依赖于有序的数据
• 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

1.4.3 非聚集索引一定要回表查询？

非聚集索引不一定回表查询。如果一个索引包含（或者说覆盖）所有需要查询的字段的值，那么非聚集索引也可以一次得到所需的字段，无需回表查询

试想一种情况，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。

SELECT name FROM table WHERE name='guang19';

那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

1.5 创建索引的注意事项

1.选择合适的字段创建索引：

• 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
• 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
• 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
• 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
• 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

2.被频繁更新的字段应该慎重建立索引。

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

3.尽可能的考虑建立联合索引而不是单列索引。

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

4.注意避免冗余索引 。

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

5.考虑在字符串类型的字段上使用前缀索引代替普通索引。

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

2 MySQL的基础架构

本节完全参考了一条SQL语句是如何执行的文章，作者：木木匠

本篇文章会分析下一个 SQL语句在 MySQL 中的执行流程，但在分析之前需要先了解 MySQL 的基础架构，知道了 MySQL 由那些组件组成以及这些组件的作用是什么，可以帮助我们理解SQL语句的执行过程。

2.1 MySQL 基本架构概览

下图是 MySQL 的一个简要架构图，从下图你可以很清晰的看到用户的 SQL 语句在 MySQL 内部是如何执行的。

• 连接器：身份认证和权限相关(登录 MySQL 的时候)。
• 查询缓存：执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
• 优化器：按照 MySQL 认为最优的方案去执行。
• 执行器：执行语句，然后从存储引擎返回数据。

简单来说 MySQL 主要分为 Server 层和存储引擎层：

• Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
• 存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

2.2 查询语句分析

select * from tb_student A where A.age='18' and A.name=' 张三 ';

结合上面的说明，我们分析下这个语句的执行流程：

• 1 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。

• 2 通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student，需要查询所有的列，查询条件是这个表的 id=’1’。然后判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。

• 3 接下来就是优化器进行确定执行方案，上面的 sql 语句，可以有两种执行方案：

  a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。
  b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。

  那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。

• 4 进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

2.3 更新语句分析

update tb_student A set A.age='19' where A.name=' 张三 ';

其实这条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志啦，这就会引入日志模块了，MySQL 自带的日志模块是 binlog（归档日志） ，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志），我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

• 1 先查询到张三这一条数据，如果有缓存，也是会用到缓存。
• 2 然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
• 3 执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
• 4 更新完成。

redo log 是 InnoDB 引擎特有的，因此InooDB也有了crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启，之前提交的记录都不会丢失)，binlog 日志只能用来归档。

redo log的两阶段提交的方式是为了保证一致性！！，为什么 redo log 要引入 prepare 预提交状态？这里我们用反证法来说明下为什么要这么做？

• 先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 bingog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
• 先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

如果采用 redo log 两阶段提交的方式就不一样了，写完 binglog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。

2.4 总结

• MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用，redolog 只有 InnoDB 有。
• 引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。
• 查询语句的执行流程如下：权限校验（如果命中缓存）—>查询缓存—>分析器—>优化器—>权限校验—>执行器—>引擎
• 更新语句执行流程如下：分析器—->权限校验—->执行器—>引擎—redo log(prepare 状态)—>binlog—>redo log(commit状态)

3 数据库的锁

完全参考了木木匠的开源项目中的文章为什么开发人员必须要了解数据库锁？

锁在数据库中其功能之一也是用来实现事务隔离性。而隔离性是为了解决脏读、不可重复读、和幻读几类问题。事务的隔离性这里就先不讲了。直接看InnoDB中有哪些锁吧

3.1 InnoDB锁的类型

小明首先了解一下Mysql中常见的锁类型有哪些:

2.3.1 S or X （行锁）

在InnoDb中实现了两个标准的行级锁，可以简单的看为两个读写锁:

• S-共享锁：又叫读锁，其他事务可以继续加共享锁，但是不能继续加排他锁。
• X-排他锁: 又叫写锁，一旦加了写锁之后，其他事务就不能加锁了。

兼容性:是指事务A获得一个某行某种锁之后，事务B同样的在这个行上尝试获取某种锁，如果能立即获取，则称锁兼容，反之叫冲突。

纵轴是代表已有的锁，横轴是代表尝试获取的锁。只有读锁和读锁同时兼容

已有锁\ 尝试锁	X	S
X	冲突	冲突
S	冲突	兼容

2.3.2 IS or IX（意向锁\表锁）

意向锁在InnoDB中是表级锁,和他的名字一样他是用来表达一个事务想要获取什么。其实就是先为表锁打一个标记！！意向锁分为:

• 意向共享锁 IS:表达一个事务想要获取一张表中某几行的共享锁。
• 意向排他锁 IX:表达一个事务想要获取一张表中某几行的排他锁。

这个锁有什么用呢？为什么需要这个锁呢？ 首先说一下如果没有这个锁，如果要给这个表加上表锁，一般的做法是去遍历每一行看看他是否有行锁，这样的话效率太低，而我们有意向锁，只需要判断是否有意向锁即可，不需要再去一行行的去扫描。

因此 IS 和 IX 的作用就是在上表级锁的时候，可以快速判断是否可以上锁，而不需要遍历表中的所有记录。

在InnoDB中由于支持的是行级的锁，因此InnboDB锁的兼容性可以扩展如下：

.	IX	IS	X	S
IX	兼容	兼容	冲突	冲突
IS	兼容	兼容	冲突	兼容
X	冲突	冲突	冲突	冲突
S	冲突	兼容	冲突	兼容

2.3.3 自增长锁（还是有点没看懂）

自增长锁是一种特殊的表锁机制，提升并发插入性能。对于这个锁有几个特点:

• 在sql执行完就释放锁，并不是事务执行完。
• 对于Insert…select大数据量插入会影响插入性能，因为会阻塞另外一个事务执行。
• 自增算法可以配置。

在MySQL5.1.2版本之后，有了很多优化，可以根据不同的模式来进行调整自增加锁的方式。小明看到了这里打开了自己的MySQL发现是5.7之后，于是便输入了下面的语句,获取到当前锁的模式:

mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 2     |
+--------------------------+-------+
1 row in set (0.01 sec)

在MySQL中innodb_autoinc_lock_mode有3种配置模式：0、1、2，分别对应”传统模式”, “连续模式”, “交错模式”。

• 0 传统模式:也就是我们最上面的使用表锁。

• 1 连续模式:对于插入的时候可以确定行数的使用互斥量，对于不能确定行数的使用表锁的模式。

• 2 交错模式:所有的都使用互斥量，为什么叫交错模式呢，有可能在批量插入时自增值不是连续的，当然一般来说如果不看重自增值连续一般选择这个模式，性能是最好的。

3.2 InnoDB锁算法

2.4.1 记录锁(Record-Lock)

记录锁是锁住记录的，这里要说明的是这里锁住的是索引记录，而不是我们真正的数据记录。

• 如果锁的是非主键索引，会在自己的索引上面加锁之后然后再去主键上面加锁锁住.
• 如果没有表上没有索引(包括没有主键)，则会使用隐藏的主键索引进行加锁。
• 如果要锁的列没有索引，则会进行全表记录加锁。

2.4.2 间隙锁（gap锁）

间隙锁顾名思义锁间隙，不锁记录。锁间隙的意思就是锁定某一个范围，间隙锁又叫gap锁，gap锁之间不会相互阻塞，但是会阻塞插入间隙锁，这也是用来防止幻读的关键。

要给此时还未存在的记录加锁怎么办？加间隙锁！！防止幻读！！

如下图：有两个间隙锁，相互之间不阻塞。但对间隙之中的元素起保护作用

3.2.3 next-key锁

这个锁本质是记录锁加上gap锁。在RR隔离级别下(InnoDB默认)，Innodb对于行的扫描锁定都是使用此算法，但是如果查询扫描中有唯一索引会退化成只使用记录锁。为什么呢? 因为唯一索引能确定行数，而其他索引不能确定行数，有可能在其他事务中会再次添加这个索引的数据会造成幻读。

3.2.4 插入意向锁

插入意向锁是在插入数据的时候产生的。 如果持有插入意向锁。在多个事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

某个间隙被锁住之后，可以插入一个”插入意向锁“，表示在等待这个间隙被释放。

这里要说明的是如果有间隙锁了，插入意向锁会被阻塞。

3.3 加锁实验

因为事务之间是隔离的，可以通过两个事务来研究不同锁之间的冲突。

1 事务A查询（使用普通索引），事务B对不同行数据做插入操作

发现在事务A中给555加了Next-key锁，事务B插入的时候会首先进行插入意向锁的插入，于是得出下面结论:可以看见事务B由于间隙锁和插入意向锁的冲突，导致了阻塞。

2 事务A查询（使用唯一索引），事务B对不同行数据做插入操作

发现事务B并没有发生阻塞，，因为唯一索引会降级记录锁，这么做的理由是:非唯一索引加next-key锁由于不能确定明确的行数有可能其他事务在你查询的过程中，再次添加这个索引的数据，导致隔离性遭到破坏，也就是幻读。唯一索引由于明确了唯一的数据行，所以不需要添加间隙锁解决幻读。

3 事务A查询（没有使用索引），事务B对不同行数据做插入操作

小明一看哎哟我去，这个咋回事呢，咋不管是用实验1非间隙锁范围的数据，还是用间隙锁里面的数据都不行，难道是加了表锁吗？

的确，如果用没有索引的数据，其会对所有聚簇索引上都加上next-key锁。

所以大家平常开发的时候如果对查询条件没有索引的，一定进行一致性读，也就是加锁读，会导致全表加上索引，会导致其他事务全部阻塞，数据库基本会处于不可用状态。

3.4 解决项目中的死锁问题

undo较小的事务是啥？（就是回滚不重要的事务呗）

死锁:是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。说明有等待才会有死锁，解决死锁可以通过去掉等待，比如回滚事务。

解决死锁的两个办法:

1. 等待超时:当某一个事务等待超时之后回滚该事务，另外一个事务就可以执行了，但是这样做效率较低，会出现等待时间，还有个问题是如果这个事务所占的权重较大，已经更新了很多数据了，但是被回滚了，就会导致资源浪费。
2. 等待图(wait-for-graph): 等待图用来描述事务之间的等待关系，当这个图如果出现回路如下:就出现回滚，通常来说InnoDB会选择回滚权重较小的事务，也就是undo较小的事务。

4 事务和隔离级别

这里先用我之前幕布上的笔记

4.1 事务

事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

事务的ACID特性

• 原子性：一个事务中的操作要么都发生（全部提交成功），要么都不发生（全部回滚）。
• 一致性：数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对同一个数据的读取结果都是相同的。
• 隔离性： 多个事务并发访问时，事务之间是隔离的。就是说一个事务所做的修改在最终提交以前，对其它事务是不可见的。
• 持久性： 在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中。即使系统崩溃，事务执行的结果也不会丢失。
  • 系统发生崩溃可以通过重做日志（Redo Log）进行恢复，与回滚日志记录数据的逻辑修改不同，重做日志记录的是数据页的物理修改。
  • 回滚可以用回滚日志（Undo Log）来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

ACID特性之间的关系

• 只有满足一致性，事务的执行结果才是正确的。

• 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。

• 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。

• 事务满足持久化是为了能应对系统崩溃的情况。

总结：如何满足ACID特性

• 数据库管理系统采用日志来保证事务的原子性、持久性。日志记录了事务对数据库所做的更新，如果某个事务在执行过程中发生错误，就可以根据日志，撤销事务对数据库已做的更新，使数据库退回到执行事务前的初始状态。

• 数据库管理系统采用锁机制来实现事务的隔离性。当多个事务同时更新数 据库中相同的数据时，只允许持有锁的事务能更新该数据

• 原子性和隔离性满足之后保证了一致性：即保证执行结果正确；持久性用来应对系统的崩溃

4.2 事务日志

二进制日志只在事务提交的时候一次性写入(基于事务的innodb二进制日志)，提交前的每个二进制日志记录都先cache，提交时写入。

MYSQL自带的日志有bin二进制日志、查询日志、慢查询日志

• bin log日志 记录所有更改数据的语句，可用于数据复制。
• 错误日志（error log）记录MySQL服务进程mysqld在启动/关闭或运行过程中遇到的错误信息
• 普通查询日志（general query log）：记录建立的客户端连接和执行的语句。
• 慢查询日志（slow query log）：记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。

所谓的事务日志是指InnoDB引擎的redo log重做日志和undo log回滚日志。 redo用来保证事务的持久性，通过记录物理修改来恢复数据。 undo保证事务的原子、用来回滚行记录到某个特定的版本。

4.2.1 redo日志

我这里只需要理解redo是什么就好了，至于如何实现和底层原理、我还不懂

参考了这篇文章

redo log通常是物理日志，记录的是也得物理修改操作。

redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。

mysql数据库同步数据到磁盘的过程！！

mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Buffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步。而buffer pool是在内存里的，如果还没来的同步的时候宕机或断电了，就会导致丢失部分已提交事务的修改信息。

所以引入了redo log来记录已成功提交事务的修改信息，宕机之后，系统重启后读取redo log恢复最新数据。虽然redo log也有内存buffer缓冲的部分，如果要严格保证数据不丢失，就要在事务提交前做一次磁盘写入，但是这种IO操作相比于buffer pool这种以页（16kb）为管理单位的随机写入，它做的是几个字节的顺序写入，效率要高得多。（就是说虽然redo也是需要从内存写入磁盘，但它是顺序写入、比直接同步事务要快得多）

比如下面的操作，从银行卡账户转账到理财账户表：

redo buffer比 buffer pool快，但redo buffer就不可能丢失事务么？

当一条 SQL 更新完 Buffer Pool 中的缓存页后，将修改先写入redo log buffer 的缓存中，然后在某一个合适的时间点，将这条 redo log buffer中的修改刷入到磁盘redo log中。(其中还涉及到redo log buffer 内部是划分为许多 redo log block，每次刷新的是redo log block。这里不再详细表述了)

合适的时间点：

这个合适的时间点究竟是什么时候呢？

• MySQL 正常关闭的时候；

• MySQL 的后台线程每隔一段时间定时的将 redo log buffer 刷入到磁盘，默认是每隔 1s 刷一次；

• 当 redo log buffer 中的日志写入量超过 redo log buffer 内存的一半时，即超过 8MB 时，会触发 redo log buffer 的刷盘；

• 当事务提交时，根据配置的参数 innodb_flush_log_at_trx_commit 来决定是否刷盘。

  • 如果innodb_flush_log_at_trx_commit 参数配置为 0，表示事务提交时，不进行 redo log buffer 的刷盘操作；
  • 如果配置为 1，表示事务提交时，会将此时事务所对应的 redo log 所在的 redo log block 从内存写入到磁盘，同时调用 fysnc，确保数据落入到磁盘；
  • 如果配置为 2，表示只是将日志写入到操作系统的缓存，而不进行 fysnc 操作。（进程在向磁盘写入数据时，是先将数据写入到操作系统的缓存中：os cache，再调用 fsync 方法，才会将数据从 os cache 中刷新到磁盘上

实际上要严格保证数据不丢失，必须得保证 innodb_flush_log_at_trx_commit 配置为 1。

innodb_flush_log_at_trx_commit为0，表示事务提交时不需要写入重做日志，而是由master thread来定期完成。
innodb_flush_log_at_trx_commit为2，表示事务提交时将重做日志写入重做日志缓存中。这个情况下，MySQL发生宕机但是操作系统没有宕机的情况下，重做是仍然可以进行的

下面介绍将日志同步到磁盘中的最佳实践：两阶段提交！!

如何通过日志实现持久性？

一言以蔽之：即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的文件中进行持久化。（包括redo log file和undo log file）。持久化又是一个需要经过系统调用的过程。

因为MySQL是工作在用户空间的，MySQL的log buffer处于用户空间的内存中。要写入到磁盘上的log file中(redo:ib_logfileN文件,undo:share tablespace或.ibd文件)，中间还要经过操作系统内核空间的os buffer，所以必须经过系统调用fsync()操作，才能完成。

更新数据、写入日志的过程：

1. MySQL Server 层的执行器调用 InnoDB 存储引擎的数据更新接口；
2. 存储引擎更新 Buffer Pool 中的缓存页，
3. 同时存储引擎记录一条 redo log 到 redo log buffer 中，并将该条 redo log 的状态标记为 prepare 状态；
4. 接着存储引擎告诉执行器，可以提交事务了。执行器接到通知后，会写 binlog 日志，然后提交事务；
5. 存储引擎接到提交事务的通知后，将 redo log 的日志状态标记为 commit 状态；
6. 接着根据 innodb_flush_log_at_commit 参数的配置，决定是否将 redo log buffer 中的日志刷入到磁盘。

将 redo log 日志标记为 prepare 状态和 commit 状态，这种做法称之为两阶段事务提交，它能保证事务在提交后，数据不丢失。为什么呢？redo log 在进行数据重做时，只有读到了 commit 标识，才会认为这条 redo log 日志是完整的，才会进行数据重做，否则会认为这个 redo log 日志不完整，不会进行数据重做。

4.2.2 undo日志

undo 称为回滚日志，是一种逻辑日志，是为了保证事务的原子性。

• undo log主要记录的是数据的逻辑变化，因此只是将数据库逻辑地恢复到原 来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。

• 当 InnoDB存储引擎回滚时，它实际上做的是与先前相反的丁作。对于每个INSERT, InnoDB存储引擎会完成一个DELETE; 对于每个 DELETE, InnoDB存储引擎会执行一个INSERT; 对于每个UPDATE, InnoDB存储引擎会执行一个相反的UPDATE, 将修改前的行放回去。

比如一个插入的更新操作：

用户执行了一个INSERT 10W条记录的事务，这个事务会导致分配新的段，即表空间会增大。在用户执行ROLLBACK时，会将插入的事务进行回滚（即对插入的行进行全部的删除操作），但是表空间的大小事实上还是变大了，只是数据还是那些数据。

4.2.3 总结

既然生产环境一般建议将 innodb_flush_log_at_trx_commit 设置为 1，也就是说每次更新数据时，redo log写入磁盘也要发生一次磁盘IO，为什么无直接将数据的修改写入磁盘中，何必引入 redo log 这一机制呢？

• 首先 redo log 日志的刷盘时将修改追加到文件末尾，虽然也是一次磁盘 IO，但是这是顺序写操作（不需要移动磁头）；而对于直接将数据更新到磁盘，这个操作发生的是随机写操作（需要移动磁头做寻址）。
• 从另一方面来讲，通常一次更新操作，我们往往只会涉及到修改几个字节的数据，而如果因为仅仅修改几个字节的数据，就将整个数据页写入到磁盘（无论是磁盘还是 buffer pool，他们管理数据的单位都是以页为单位），这个代价未免也太了（每个数据页默认是 16KB），而一条 redo log 日志的大小可能就只有几个字节，因此每次磁盘 IO 写入的数据量更小，那么耗时也会更短。

redo日志和bin日志的区别！！？

4.3 事务并发控制带来的问题

• 在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题，主要有以下四种：
  • 丢失更新：两个事务在并发下同时进行更新，后一个事务的更新覆盖了前一个事务的更新。
  • 脏读： 脏读意味着一个事务T2读取了另一个事务T1未提交的数据，（当T1撤销了这次修改、那么T2读的就是脏数据）
  • 不可重复读：同一个事务中，多次读取到的数据不一致（原因是其他事务T2进来对本事务T1的一个数据做了修改、导致T1两次读取结果不一样）
  • 幻读：一个事务读取数据时，另外一个事务进行更新，导致第一个事务读取到了没有更新的数据（T2想要读取T1的数据，但是T2读取数据之后，T1又对该数据进行了更新导致T2读取的是更新之前的数据）
• 产生并发不一致性问题的主要原因是破坏了事务的隔离性，解决方法是通过并发控制来保证隔离性。
  • 并发控制可以通过封锁来实现，但是封锁操作需要用户自己控制，相当复杂。
  • 数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式处理并发一致性问题。

4.4 事务的隔离级别

数据库事务的隔离级别有4个，由低到高依次为 读未提交、读已提交、可重复读、序列化

• 读未提交： 如果一个事务已经 开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。避免了丢失更新，却可能出现脏读。也就是说事务B读取到了事务A未提交的数据。
  • 不允许同时写，避免了丢失更新，有脏读
• 读已提交： 读取数据的事务允许其 他事务继续访问该行数据（写），但是未提交的写事务将会禁止其他事务访问该行。避免了脏读，但是却可能出现不可重复读。事务A事先读 取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。（一般默认的隔离级别）
  • 未提交的写禁止其他事物访问，避免了脏读
• 可重复读：无论是否有其他事务对数据做了修改，一个事务在启动和提交之间读到数据始终是一致的。通常是，读取数据的事务将 会禁止写事务（但允许读事务），写事务则禁止任何其他事务。。这样避免 了不可重复读和脏读，但是有时可能出现幻读。
  • 只能同时并发读，避免不可重复读，有幻读
• 序列化：要求事务序列化 执行，事务只能一个接着一个地执行，不能并发执行。序列化是最高的事务隔离级 别，同时代价也最高。。不仅可以避免脏读、不可重复读，还避免了幻读。

4.5 多版本并发控制MVCC

[1] 此小节参考了 yes的练级攻略 的文章关于MySQL的酸与MVCC和面试官小战三十回合 文章幽默有趣、讲的比较深入又十分易懂

4.5.1 什么是MVCC?

多版本并发控制（Multi-Version Concurrency Control ）。指的是一条记录会有多个版本，每次修改记录都会存储这条记录被修改之前的版本，多版本之间串联起来就形成了一条版本链。

这样不同时刻启动的事务可以无锁地获得不同版本的数据(普通读)。此时读(普通读)写操作不会阻塞，写操作可以继续写，无非就是多加了一个版本，历史版本记录可供已经启动的事务读取。

（为保持简短，简化了SQL语句，下文也同样简化）

4.5.2 MVCC有什么用？

MVCC 主要可以提高了事务的并发度，提升数据库的性能。

如果是读未提交的话，直接读最新版本的数据就行了，压根就不需要保存以前的版本。可串行化隔离级别事务都串行执行了，所以也不需要多版本，因此 MVCC 是用来实现读已提交和可重复读的。

• 对于读已提交：规定只有在写事务只有提交之后，才可以读，不然可能会出现脏读的问题。但是如果有MVCC保存多版本的数据的话、如果A事务在修改时，另一个事务B仍然可以读取旧版本的数据。这样读写就不会阻塞了，提高了事务的并发度。（如果没有的话事务B就会阻塞）

• 对于可重复读，MVCC可以和gap间隙锁来防止幻读。（没有MVCC在RR隔离级别下可能出现幻读）

4.5.3 MVCC的实现原理？

MVCC其实没有存多个版本的数据，索引上对应的记录只有一个版本，但是利用undo日志可以获得之前版本的数据，类似通过undo日志、形成了一个版本链，看起来是多个版本！！

举个栗子：三条命令：

1 一个事务ID为1的事务执行 insert （1，XX）语句执行，并提交

2 一个事务ID为5的事务执行 update NO where id 1 语句执行，并提交

3 一个事务ID为11 的事务执行 update Yes where id 1 这个语句，并提交

最后id=1的这行数据字段分别为（1，Yes)，同时还有 trx_id （当前事务ID）和 roll_pointer(指向undo日志的指针) 这两个隐藏字段。其版本链如下：

多个版本链还涉及到一个重要的问题，就是当前版本的数据应该对哪些事务可见?

一个原则就是 未提交的数据是不可见的，已提交还未修改的数据是可见的。

可见版本的判断是从最新版本开始沿着版本链逐渐寻找老的版本，如果遇到符合条件的版本就返回。（因为事务的id是随开始事务的时间而递增的）

readView 用来判断哪个版本对当前事务可见的，其判断涉及到四个字段值：

• creator_trx_id，当前事务ID。
• m_ids，生成 readView 时还活跃的事务ID集合，也就是已经启动但是还未提交的事务ID列表。
• min_trx_id，当前活跃ID之中的最小值。
• max_trx_id，生成 readView 时 InnoDB 将分配给下一个事务的 ID 的值（事务 ID 是递增分配的，越后面申请的事务ID越大）

判断条件如下：

• 如果当前数据版本的 trx_id == creator_trx_id 说明修改这条数据的事务就是当前事务，所以可见。
• 如果当前数据版本的 trx_id < min_trx_id，说明修改这条数据的事务在当前事务生成 readView 的时候已提交，所以可见。
• 如果当前数据版本的 trx_id 在 m_ids 中，说明修改这条数据的事务此时还未提交，所以不可见。（还活跃的事务，即未提交，所以不可见）
• 如果当前数据版本的 trx_id >= max_trx_id，说明修改这条数据的事务在当前事务生成 readView 的时候还未启动，所以不可见(结合事务ID递增来看)。

4.5.4 可重复读下的MVCC

其实需要先讲一下：读已提交隔离级别下的MVCC，比较简单，一笔带过：

总是读取到最近的一个已提交的数据，如果两次读取中，有另一个事务提交了，那么第二次读取将会读取到新提交的数据。存在不可重复读的并发问题。

读已提交每次查询都会重新生成一个新的 readView ，而可重复读在第一次生成 readView 之后的所有查询都共用同一个 readView 。

也就是说可重复读只会在第一次 select 时候生成一个 readView ，所以一个事务里面不论有几次 select ，其实看到的都是同一个 readView 。

因为两次查询用的是同一个readView ，所以不管新事务有没有提交，仍认为它没有提交，查到的还是那个老版本的数据！！所以是可重复读的！！

undolog 算是热点资源，多个事务会争抢 undolog 。所以为了提高 undolog 的写入性能，每个事务都有属于自己的 undolog 页面链表

5 其他知识点

5.1 不同存储引擎的区别

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎，MySQL 5.5 版本后默认的存储引擎为 InnoDB。

InnoDB 实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

MyISAM 设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

InnoDB 和MyISAM 的对比

• 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
• 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
• 外键：InnoDB 支持外键。
• 备份：InnoDB 支持在线热备份。
• 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。（InnoDB 有redo日志！！）
• 其它特性：MyISAM 支持压缩表和空间数据索引。

5.2 关系型数据库和非关系型数据库的区别

• 关系型数据库：

  • 关系型数据库的最大特点就是事务的一致性：传统的关系型数据库读写操作都是事务的，具有ACID的特点

  • 关系型数据库为了维护一致性所付出的巨大代价就是其读写性能比较差

  • 关系数据库的另一个特点就是其具有固定的表结构，因此，其扩展性较差

• 非关系型数据库

  • 指非关系型的，分布式的，且一般不保证遵循ACID原则的数据存储系统

  • 面向高性能并发读写的key-value数据库

  • 面向可扩展性的分布式数据库

数据的持久存储，尤其是海量数据的持久存储，使用的更多的还是关系数据库

5.3 复合索引的最左前缀原则

最左前缀法则：带头大哥不能死、中间兄弟不能断；如果是全值匹配查询时，顺序无所谓；

参考了神文Mysql最左匹配原则 写的真是太好了

索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。
例子：假如创建一个（a,b)的联合索引，那么它的索引树是这样的

也就是说：索引a的值是有序的，而只有在a相等的情况下b才是有序的。如果单独查b，只能全表找b，没办法二分索引找到b。

最左匹配原则：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

1 全值匹配查询时–用到了索引

select * from table_name where a = '1' and b = '2' and c = '3' 
select * from table_name where b = '2' and a = '1' and c = '3' 
select * from table_name where c = '3' and b = '2' and a = '1' 
....

2 匹配左边的列时—只有从第一个开始才能用到索引

# 如果都从最左边开始连续匹配，用到了索引
select * from table_name where a = '1' 
select * from table_name where a = '1' and b = '2'  
select * from table_name where a = '1' and b = '2' and c = '3'
# 这些没有从最左边开始，最后查询没有用到索引，用的是全表扫描 
select * from table_name where  b = '2' 
select * from table_name where  c = '3'
select * from table_name where  b = '1' and c = '3' 
# 如果不连续时，只用到了a列的索引，b列和c列都没有用到 
select * from table_name where a = '1' and c = '3' 

3 匹配列前缀：如果a是字符类型，那么前缀匹配用的是索引，后缀和中缀只能全表扫描了

select * from table_name where a like 'As%'; //前缀都是排好序的，走索引查询
select * from table_name where  a like '%As'//全表查询
select * from table_name where  a like '%As%'//全表查询