面试总结之 《Mysql》

Mysql

一、innoDB跟myisam的区别

1.从索引来说：
两者底层实现都是B+树。

MyISAM的索引和行记录时分开的，叫做非聚集索引。其主键索引与普通索引没有本质区别。
有连续聚集的区域单独存储行记录。
主键索引的叶子节点，存储主键，与对应行记录的指针。
普通索引的叶子结点，存储索引列，与对应行记录的指针。
查询过程：
主键索引与普通索引是两棵独立的索引B+树，通过索引列查找时，先定位到B+树的叶子节点，再通过指针定位到行记录。

InnoDB的索引： 与行记录存放在一起，所以叫做聚集索引。
没有连续聚集的区域单独存储行记录。
主键索引的叶子节点存放的是主键，和与之对应的行数据（不包括主键）。
普通索引的叶子节点存放的是主键，
InnoDB索引必须要有聚集索引，且只能有一份
（1）如果表定义了PK，则PK就是聚集索引。
（2）如果表没有定义PK，则第一个非空unique列是聚集索引。
（3）如果都没有，InnoDB会创建一个隐藏的row-id作为聚集索引。
查询过程：
主键索引与普通索引是两棵独立的索引B+树，通过主键索引查找时，定位到B+树的叶子节点获得数据。普通索引查找列时，先定位到B+树的叶子节点获得主键，再通过主键索引定位到行记录。

其它索引相关的区别
对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。

2.从事务来说：
InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一。

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

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

5.从锁粒度来说
InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁，一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

二、InnoDB之索引

1.为什么需要索引，索引底层实现是什么，为什么选择它？

1） 为什么需要索引：
没有索引的查询是需要全表扫描非常的慢。所以需要通过索引缩小一张表中需要查询的记录/行的数目来加快搜索的速度。

2）索引底层实现：
索引底层实现是用B+树
(1)在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。
(2)叶子之间，增加了链表，获取所有节点，不再需要中序遍历；

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

Hash索引
不管是读请求，还是写请求，哈希类型的索引，都要比树型的索引更快一些，那为什么，索引结构要设计成树型呢？
对于排序查询的SQL需求：分组、排序、比较：
哈希型的索引**，时间复杂度会退化为O(n)**，而树型的“有序”特性，依然能够保持O(log(n)) 的高效率。

B-树：
当InnoDB的索引以树形结构为存储结构的时候，会以页（一页）为单位存储到一个节点中。我们根据主键查找数据就需要找到主键所在的节点，如果树的高度越高，遍历的节点就多，磁盘IO次数就越多，就会降低查询效率。B树中的节点存放主键和除了主键的行记录，使用B-树可以降低树的高度，减少磁盘IO次数，加快查询速度。

对于一棵 m 阶 B 树，需要满足以下条件：
(1)每个节点最多包含 m 个子节点。
(2)如果根节点包含子节点，则至少包含 2 个子节点；除根节点外，每个非叶节点至少包含 m/2 个子节点。
(3)拥有 k 个子节点的非叶节点将包含 k - 1 条记录。
(4)所有叶节点都在同一层中。

B树被作为实现索引的数据结构被创造出来，是因为它能够完美的利用“局部性原理”。
什么是局部性原理？
局部性原理的逻辑是这样的：
(1)内存读写块，磁盘读写慢，而且慢很多；
(2)磁盘预读：磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次加载更多的数据，如果未来要读取的数据就在这一页中，可以避免未来的磁盘IO，提高效率；
(3)局部性原理：软件设计要尽量遵循“数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO；

B树为何适合做索引？
(1)由于是m分叉的，高度能够大大降低；
(2)每个节点可以存储j个记录，如果将节点大小设置为页大小，例如4K，能够充分的利用预读的特性，极大减少磁盘IO；

B+树
在B树的基础上，做了一些改进：
(1)非叶子节点不再存储数据，数据只存储在同一层的叶子节点上；B+树中根到每一个节点的路径长度一样，而B树不是这样。
(2)叶子之间，增加了链表，获取所有节点，不再需要中序遍历；

这些改进让B+树比B树有更优的特性：
(1)范围查找，定位min与max之后，中间叶子节点，就是结果集，不用中序回溯；范围查询在SQL中用得很多，这是B+树比B树最大的优势。
(2)叶子节点存储实际记录行，记录行相对比较紧密的存储，适合大数据量磁盘存储；非叶子节点存储记录的PK，用于查询加速，适合内存存储；
(3)非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么在相同内存的情况下，B+树能够存储更多索引；

2. 覆盖索引

(1) 什么是覆盖索引
即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。
（2）如何实现
可以通过聚簇索引实现，让查询的值跟普通索引联合形成聚簇索引，这样这两个值都在节点上面了，可以通过索引节点获取数据，而不用根据行获取数据。避免了回表操作。

create index eamils on t_user(`email`,`username`); //创建联合索引
explain SELECT username,email FROM `t_user` WHERE email = '123' //分析索引

Extra中Using index表明我们成功使用了覆盖索引

3.前缀索引

MySQL 前缀索引通过确定索引的长度，能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。
如何确定索引长度
我们可以通过计算选择性来确定前缀索引的选择性，计算方法如下
全列选择性：
SELECT COUNT(DISTINCT column_name) / COUNT() FROM table_name;
某一长度前缀的选择性：
SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT() FROM table_name;
当前缀的选择性越接近全列选择性的时候，索引效果越好。

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.索引使用原则

哪些情况需要创建索引

主键自动建立唯一索引
频繁作为查询条件的字段
查询中与其他表关联的字段，外键关系建立索引
单键/组合索引的选择问题，高并发下倾向创建组合索引
查询中排序的字段，排序字段通过索引访问大幅提高排序速度
查询中统计或分组字段

哪些情况不要创建索引

表记录太少
经常增删改的表
数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）
频繁更新的字段不适合创建索引（会加重IO负担）
where条件里用不到的字段不创建索引

三、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之事务

1. 事务的四大特性

原子性（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都会被阻塞住。

参考文章

Mysql数据库面试题