面试复习 - MySQL总结

MySQL

资料参考：[https://blog.csdn.net/qq_37909141/article/details/107769695?spm=1001.2014.3001.5501]

一、InnoDB与MyISAM的区别

1. 从事务上来说：InnoDB支持事务，可以使用commit和rollback语句；MyISAM不支持事务。

2. 从数据存储来说：
   MyISAM的索引和数据是分开存储的，叫做非聚集索引。其主键索引和普通索引没有任何区别。有连续聚集的区域单独存储行记录。
   主键索引的叶子结点，存储的是主键，与对应数据行的地址。(也就是对应行记录的指针)
   普通索引的叶子结点，存储的是索引列，与对应数据行的地址。
   MyISAM的数据查询过程：
   主键索引与普通索引是两棵独立的索引B+树，通过索引列查找时，先在索引B+树中找到对应的叶子结点，读取出数据行的地址。再到数据文件中根据地址找出数据。
   InnoDB的索引和数据是一起存储的，叫做聚集索引。其叶子结点存储的是整个数据行，而不是数据行的地址。
   主键索引的叶子节点存放的是主键，和与之对应的行数据（不包括主键）。
   普通索引的叶子节点存放的是主键的值。
   InnoDB索引必须要有聚集索引，且只能有一份。
   查询过程：
   主键索引与普通索引是两棵独立的索引B+树，通过主键索引查找时，定位到B+树的叶子节点获得数据。普通索引查找列时，先定位到B+树的叶子节点获得主键，再通过主键索引定位到行记录。

3. 从并发上来说：
   MyISAM采用的是表级锁(并发阻塞经常受限)，而InnoDB默认采用的是行级锁，也支持表级锁。
   InnoDB在进行索引查找的时候，如果查找失败，会将行级锁自动转换为表级锁。

4. 从外键来说：InnoDB支持外键，MyISAM不支持外键。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；

5. 从数据备份上来说：InnoDB支持真正的在线热备份，MySQL的其他存储引擎都不支持在线热备份。对于在需要读取一致性视图时需要停止对其他所有表的写入。

6. 从count(*)来说：
   InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

7. 其他特性：
   MyISAM 支持压缩表和空间数据索引。InnoDB使用MVCC(多版本并发控制)来支持高并发。

二、InnoDB中的索引

1. 为什么需要索引，索引的底层是什么，为什么选择索引？
1） 为什么需要索引：
没有索引的查询是需要全表扫描非常的慢。所以需要通过索引缩小一张表中需要查询的记录/行的数目来加快搜索的速度。
2）索引底层实现：
索引底层实现是用B+树
B+ 树有两种类型的节点：内部节点（也称索引节点）和叶子节点，内部节点就是非叶子节点，内部节点不存储数据，只存储索引，数据都存在叶子节点。

内部节点中的 key 都按照从小到大的顺序排列，对于内部节点中的一个 key，左子树中的所有 key 都小于它，右子树中的 key 都大于等于它，叶子节点的记录也是按照从小到大排列的。

每个叶子节点都存有相邻叶子节点的指针。

3）为什么选择B+树：
InnoDB 存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。mysql从磁盘读取数据的时候都是以页为单位，读取整页中的数据。如果我们能快速的确定数据所在的页，就能提高查询速度。

Hash索引
不管是读请求，还是写请求，哈希类型的索引，都要比树型的索引更快一些，那为什么，索引结构要设计成树型呢？
对于排序查询的SQL需求：分组、排序、比较：
哈希型的索引**，时间复杂度会退化为O(n)**，而树型的“有序”特性，依然能够保持O(log(n)) 的高效率。
而且InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。
B + 树与 B 树的比较
B+ 树的磁盘 IO 更低
B+ 树的内部节点并没有指向关键字具体信息的指针。因此其内部节点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

B+ 树的查询效率更加稳定
由于非叶子结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

B+ 树元素遍历效率高
B 树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而 B 树不支持这样的操作（或者说效率太低）。

2. 覆盖索引
(1) 什么是覆盖索引
即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。
（2）如何实现
可以通过聚簇索引实现，让查询的值跟普通索引联合形成聚簇索引，这样这两个值都在节点上面了，可以通过索引节点获取数据，而不用根据行获取数据。避免了回表操作。
3.前缀索引
前缀索引
对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。

前缀长度的选取需要根据索引选择性来确定。
4.联合索引
最左匹配

1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配了，比如 a=3 and b=4 and c>5 and d=6 如果建立(abcd)顺序的索引,d就用不到索引了，如果建立(abdc)的索引则都可以用到索引，其中abd的顺序可以任意调整，因为查询优化器会重新编排（即使是c>5 and b=4 and d=6 and a=3也会全部用到 abdc索引 ）
   2.=和in可以乱序，比如a=1 and b=2 and c=3 建立(abc)索引可以任意顺序，mysql查询优化器会优化顺序
   ps: 比如abc索引 那么只要查询条件有a即可用到abc索引（如abc ab ac a）， 没有a就用不到。

5.索引使用原则

对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；
对于中到大型的表，索引就非常有效；
但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

为什么对于非常小的表，大部分情况下简单的全表扫描比建立索引更高效？

如果一个表比较小，那么显然直接遍历表比走索引要快（因为需要回表）。

注：首先，要注意这个答案隐含的条件是查询的数据不是索引的构成部分，否也不需要回表操作。其次，查询条件也不是主键，否则可以直接从聚簇索引中拿到数据。

三、MVCC

1.什么是数据多版本
MVCC就是通过“读取旧版本数据”来降低并发事务的锁冲突，提高任务的并发度

数据多版本是一种能够进一步提高并发的方法，它的核心原理是：
（1）MySQL 中 InnoDB 引擎支持 MVCC
（2）应对高并发事务, MVCC 比单纯的加行锁更有效, 开销更小
（3）MVCC 在读已提交（Read Committed）和可重复读（Repeatable Read）隔离级别下起作用
（4）MVCC 既可以基于乐观锁又可以基于悲观锁来实现
2.数据多版本实现方式
（1）写任务发生时，将数据克隆一份，以版本号区分；
（2）写任务操作新克隆的数据，直至提交；
（3）并发读任务可以继续读取旧版本的数据，不至于阻塞；
3.redo, undo,回滚段
（1）为什么要有redo日志
数据库事务提交后，必须将更新后的数据刷到磁盘上，以保证ACID特性。磁盘随机写性能较低，如果每次都刷盘，会极大影响数据库的吞吐量。

优化方式是，将修改行为先写到redo日志里（此时变成了顺序写），再定期将数据刷到磁盘上，这样能极大提高性能。

假如某一时刻，数据库崩溃，还没来得及刷盘的数据，在数据库重启后，会重做redo日志里的内容，以保证已提交事务对数据产生的影响都刷到磁盘上。

一句话，redo日志用于保障，已提交事务的ACID特性。

（2）为什么要有undo日志
数据库事务未提交时，会将事务修改数据的镜像（即修改前的旧版本）存放到undo日志里，当事务回滚时，或者数据库奔溃时，可以利用undo日志，即旧版本数据，撤销未提交事务对数据库产生的影响。

画外音：更细节的，
对于insert操作，undo日志记录新数据的PK(ROW_ID)，回滚时直接删除；
对于delete/update操作，undo日志记录旧数据row，回滚时直接恢复；
他们分别存放在不同的buffer里。

一句话，undo日志用于保障，未提交事务不会对数据库的ACID特性产生影响。

（3）什么是回滚段
存储undo日志的地方，是回滚段。

4.InnoDB为何能够做到这么高的并发
回滚段里的数据，其实是历史数据的快照（snapshot），这些数据是不会被修改，select可以肆无忌惮的并发读取他们。
快照读（Snapshot Read），这种一致性不加锁的读（Consistent Nonlocking Read），就是InnoDB并发如此之高的核心原因之一。
这里的一致性是指，事务读取到的数据，要么是事务开始前就已经存在的数据（当然，是其他已提交事务产生的），要么是事务自身插入或者修改的数据。

IInnoDB所有普通select都是快照读；

5.InnoDB，快照读，在RR和RC下有何差异
事务总能够读取到，自己写入(update /insert /delete)的行记录

RC下，快照读总是能读到最新的行数据快照，当然，必须是已提交事务写入的。

RR下，某个事务首次read记录的时间为T，未来不会读取到T时间之后已提交事务写入的记录，以保证连续相同的read读到相同的结果集。

四、InnoDB之锁

1.自增锁（Auto-inc Locks）
自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

2.共享/排它锁(Shared and Exclusive Locks)
在InnoDB里当然也实现了标准的行级锁(row-level locking)，共享/排它锁：
(1)事务拿到某一行记录的共享S锁，才可以读取这一行；
(2)事务拿到某一行记录的排它X锁，才可以修改或者删除这一行；

其兼容互斥表如下：

       S          X

S      兼容      互斥

X      互斥      互斥

即：
(1)多个事务可以拿到一把S锁，读读可以并行；
(2)而只有一个事务可以拿到X锁，写写/读写必须互斥；

3.意向锁(Intention Locks)
InnoDB支持多粒度锁(multiple granularity locking)，它允许行级锁与表级锁共存，实际应用中，InnoDB使用的是意向锁。

意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。

意向锁有这样一些特点：
(1)首先，意向锁，是一个表级别的锁(table-level locking)；

(2)意向锁分为：
意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁
意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁

举个例子：
select … lock in share mode，要设置IS锁；
select … for update，要设置IX锁；

(3)意向锁协议(intention locking protocol)并不复杂：
事务要获得某些行的S锁，必须先获得表的IS锁
事务要获得某些行的X锁，必须先获得表的IX锁

(4)由于意向锁仅仅表明意向，它其实是比较弱的锁，意向锁之间并不相互互斥，而是可以并行，其兼容互斥表如下：

        IS        IX

IS      兼容      兼容

IX      兼容      兼容

(5)既然意向锁之间都相互兼容，那其意义在哪里呢？它会与共享锁/排它锁互斥，其兼容互斥表如下：

        S          X

IS      兼容      互斥

IX      互斥      互斥

在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。

意向锁在原来的 X/S 锁之上引入了 IX/IS，IX/IS 都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定：

一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。
通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败。

4.插入意向锁(Insert Intention Locks)
对已有数据行的修改与删除，必须加强互斥锁X锁，那对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？插入意向锁，孕育而生.

插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。

画外音：有点尴尬，间隙锁下一篇文章才会介绍，暂且理解为，它是一种实施在索引上，锁定索引某个区间范围的锁。

它的玩法是：
多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

5.记录锁(Record Locks)
记录锁，它封锁索引记录，例如：
select * from t where id=1 for update;
它会在id=1的索引记录上加锁，以阻止其他事务插入，更新，删除id=1的这一行。

需要说明的是：
select * from t where id=1;
则是快照读(SnapShot Read)，它并不加锁，

6.间隙锁(Gap Locks)
间隙锁，它封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”。

如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效。

7.临键锁(Next-Key Locks)
临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。

更具体的，临键锁会封锁索引记录本身，以及索引记录之前的区间。
如果一个会话占有了索引记录R的共享/排他锁，其他会话不能立刻在R之前的区间插入新的索引记录。

临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

在 InnoDB 存储引擎中，SELECT 操作的不可重复读问题通过 MVCC 得到了解决，而 UPDATE、DELETE
的不可重复读问题通过 Record Lock 解决，INSERT 的不可重复读问题是通过 Next-Key Lock（Record Lock + Gap Lock）解决的。

五、InnoDB之事务

1. 事务的四大特性（ACID）

Atomicity：原子性
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
Consistency：一致性
事务前后数据的完整性必须保持一致。
Isolation：隔离性
多个事务并发执行时，一个事务的执行不应影响其他事务的执行
Durability：持久性
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

2. 并发一致性问题
（1）丢失修改
丢失修改指一个事务的更新操作被另外一个事务的更新操作替换。

（2）读脏数据
读脏数据指在不同的事务下，当前事务可以读到另外事务未提交的数据。

例如：T1 修改一个数据但未提交，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。

（3）不可重复读
不可重复读指在一个事务内多次读取同一数据集合。在这一事务还未结束前，另一事务也访问了该同一数据集合并做了修改，由于第二个事务的修改，第一次事务的两次读取的数据可能不一致。

例如：T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

（4）幻影读
幻读本质上也属于不可重复读的情况，T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

3.隔离级别：
（1）未提交读（READ UNCOMMITTED）
事务中的修改，即使没有提交，对其它事务也是可见的。
会出现脏读，不可重复读，幻读

（2）提交读（READ COMMITTED）
一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
会出现不可重复读，幻读

（3）可重复读（REPEATABLE READ）
保证在同一个事务中多次读取同一数据的结果是一样的。
会出现幻读(但在Mysql实现的Repeatable read配合gap锁不会出现幻读！)

（4）可串行化（SERIALIZABLE）
强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。
该隔离级别需要加锁实现，因为要使用加锁机制保证同一时间只有一个事务执行，也就是保证事务串行执行。
串行，避免以上的情况！

4. InnoDB的四种事务的隔离级别的实现方式
（1）未提交读（READ UNCOMMITTED）
这种事务隔离级别下，select语句不加锁。
此时，可能读取到不一致的数据，即“读脏”。这是并发最高，一致性最差的隔离级别。

（2）提交读（READ COMMITTED）
在RC下：
(1)普通读是快照读；
(2)加锁的select, update, delete等语句，除了在外键约束检查(foreign-key constraint checking)以及重复键检查(duplicate-key checking)时会封锁区间，其他时刻都只使用记录锁；

此时，其他事务的插入依然可以执行，就可能导致，读取到幻影记录。

（3）可重复读（REPEATABLE READ）
这是InnoDB默认的隔离级别，在RR下：

(1)普通的select使用快照读(snapshot read)，这是一种不加锁的一致性读(Consistent Nonlocking Read)，底层使用MVCC来实现。
(2)加锁的select(select … in share mode / select … for update), update, delete等语句，它们的锁，依赖于它们是否在唯一索引(unique index)上使用了唯一的查询条件(unique search condition)，或者范围查询条件(range-type search condition)：

在唯一索引上使用唯一的查询条件，会使用记录锁(record lock)，而不会封锁记录之间的间隔，即不会使用间隙锁(gap lock)与临键锁(next-key lock)

范围查询条件，会使用间隙锁与临键锁，锁住索引记录之间的范围，避免范围间插入记录，以避免产生幻影行记录，以及避免不可重复的读

（4）可串行化（SERIALIZABLE）
这种事务的隔离级别下，所有select语句都会被隐式的转化为select … in share mode.
这可能导致，如果有未提交的事务正在修改某些行，所有读取这些行的select都会被阻塞住。