MySQL进阶必须掌握的知识点有这些，我没说错吧。

痛定思痛。 2024-05-24 05:37 45阅读 0赞

### 1.InnoDB和MyISAM存储引擎的区别？ ###

MySQL 默认的存储引擎是 InnoDB，它采用 B+Tree 作为索引的数据结构。

在创建表时，InnoDB 存储引擎默认会创建一个主键索引，也就是聚簇索引，其它索引都属于二级索引。

MySQL 的 MyISAM 存储引擎支持多种索引数据结构，比如 B+ 树索引、R 树索引、Full-Text 索引。MyISAM 存储引擎在创建表时，创建的主键索引默认使用的是 B+ 树索引。

InnoDB存储引擎有2个文件：Frm文件和Ibd文件。Frm文件是表的定义文件，而Ibd文件是数据和索引存储文件（数据以主键进行聚集索引，把真正的数据保存在叶子节点中）。

MyISAM存储引擎有3个文件：Frm文件、MYD文件和MYI文件。Frm文件是表的定义文件，MYD文件是数据文件（所有的数据保存在这个文件中），MYI文件是索引文件。

综上所述，InnoDB 和 MyISAM 都支持 B+ 树索引，但是它们数据的存储结构实现方式不同。不同之处在于：

*  InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身，即数据和索引都放在一个文件中；
 *  MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址，即两个文件分开存储；

### 2.聚合索引和非聚合索引的区别，以及各自的优缺点？ ###

InnoDB 存储引擎根据索引类型不同，分为聚簇索引（上图就是聚簇索引）和二级索引。它们区别在于，**聚簇索引的叶子节点存放的是实际数据**，所有完整的用户数据都存放在聚簇索引的叶子节点，**而二级索引的叶子节点存放的是主键值，而不是实际数据**。

##### 聚簇索引的优点： #####

*  当你需要取出一定范围内的数据时，用聚簇索引比用非聚簇索引好
 *  数据访问更快，聚集索引将索引和数据保存在同一个B-Tree中，因此从聚集索引中获取数据通常比在非聚集索引中查找要快。
 *  当通过聚簇索引查找目标数据时理论上比非聚簇索引要快，因为非聚簇索引定位到对应主键时可能还要多一次回表操作
 *  使用覆盖索引扫描的查询可以直接使用叶节点中的主键值

##### 聚簇索引的缺点： #####

*  插入速度严重依赖于插入顺序
 *  更新主键的代价很高，因为将会导致被更新的行移动

##### 非聚簇索引的优点： #####

*  插入和更新数据时不需要移动其他数据行，因此性能较好。
 *  非聚簇索引能够加速数据查询，提高查询速度。

##### 非聚簇索引的缺点： #####

*  查询非索引列的时候，需要进行二次查找，因此相对于聚簇索引，查询速度较慢。
 *  非聚簇索引的叶子节点不存储数据行，因此对于需要查询全部列的查询语句，需要进行额外的I/O操作，降低查询效率。

### 3.索引失效情景？ ###

*  当我们使用左或者左右模糊匹配的时候，也就是 `like %xx` 或者 `like %xx%` 这两种方式都会造成索引失效。
 *  如果查询条件中对索引字段使用函数，就会导致索引失效。
 *  在查询条件中对索引进行表达式计算，也是无法走索引的。
 *  如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话会发生隐式类型转换，此时也是没法走索引的。
 *  如果不符合最左匹配原则，也就无法匹配上联合索引，联合索引就会失效。
 *  在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

### 4.说一说B+T是一个怎么样的数据结构？相对于平衡二叉树、红黑树、跳表，以及BT来说，为什么使用B+T？ ###

*  B+T一个节点有多个叶子节点，并且非叶子节点只存储索引，叶子节点存放实际数据；叶子节点间用双向链表维护，实现了高效的范围查询。

##### 为什么使用B+T #####

MySQL是基于磁盘的数据库，磁盘的性能瓶颈在于磁盘IO，我们知道磁盘是按照页进行存取的，每一页是固定大小，比如16KB，对于平衡二叉树和红黑树等，当数据量大的时候，它们的树通常是很高的，每次查询都只能取一个节点放入内存中查找，这样就会增加IO次数，查询效率低下。因此B树一族的优化思路就是不再限制一个节点就只能有 2 个子节点，而是允许 M 个子节点 (M>2)，从而降低树的高度。

B 树的每一个节点最多可以包括 M 个子节点，M 称为 B 树的阶，所以 B 树就是一个多叉树。每一次都取一批节点放入内存中查找，极大降低了磁盘IO。

相对于B树，B+树做了两方面优化，一方面是非叶子节点只存放索引，另一方面是叶子节点间使用双向链表维护。对于非叶子节点的优化，其实还是针对减少磁盘IO的进一步优化。磁盘IO每一页的大小是固定的，我们希望一页存取的元素个数越多，那么每个元素的大小就得越小，因此B+树的优化就是非叶子节点不再存放完整记录，这样节点的大小就极大减小了，磁盘IO次数也就减少了，进一步提升了查询效率。针对于叶子节点用双向链表维护，这种设计对范围查找非常有帮助。而 B 树没有将所有叶子节点用链表串联起来的结构，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

### 5.事务的隔离级别分别怎么实现的？ ###

*  对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
 *  对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
 *  对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 **Read View \**来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。\**「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View**。
    
    注意，执行「开始事务」命令，并不意味着启动了事务。在 MySQL 有两种开启事务的命令，分别是：
    
     *  第一种：begin/start transaction 命令；
     *  第二种：start transaction with consistent snapshot 命令；
    
    这两种开启事务的命令，事务的启动时机是不同的：
    
     *  执行了 begin/start transaction 命令后，并不代表事务启动了。只有在执行这个命令后，执行了第一条 select 语句，才是事务真正启动的时机；
     *  执行了 start transaction with consistent snapshot 命令，就会马上启动事务。

### 6.可重复读解决幻读问题了吗？ ###

首先说一下什么是幻读。当同一个查询在不同的时间产生不同的结果集时，事务中就会出现所谓的幻象问题。例如，如果 SELECT 执行了两次，但第二次返回了第一次没有返回的行，则该行是“幻像”行。

可重复读隔离级是由 MVCC（多版本并发控制）实现的，实现的方式是开始事务后（执行 begin 语句后），在执行第一个查询语句后，会创建一个 Read View，**后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的**，即使中途有其他事务插入了新纪录，是查询不出来这条数据的，所以就很好了避免幻读问题。

MySQL 里除了普通查询是快照读，其他都是**当前读**，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。

针对当前读是仍存在幻读问题的，因为行锁并不影响其他事务的插入操作。因此**Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题，就引出了间隙锁**，通过 记录锁+间隙锁形成next-key lock（临键锁）的方式解决了幻读。

但是可重复读隔离级别下仍然没彻底解决幻读问题，举两个例子：

*  对于快照读， MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录，那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读。
 *  对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读。

所以，**MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。**

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select ... for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

### 7.说一说意向锁？ ###

##### 首先为什么需要引入意向锁呢？ #####

我们在引入意向锁前有这样一个场景：其他事务对当前数据表进行了锁行操作（独占锁），而我们当前事务需要对该表加表级锁（独占表锁）。此时我们能加表锁的前提是当前表不存在独占锁，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

##### 那么什么是意向锁呢 #####

*  在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
 *  在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；

也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。

##### 意向锁带来的影响有哪些呢？（优点） #####

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（\*lock tables ... read\*）和独占表锁（\*lock tables ... write\*）发生冲突。因此引入了意向锁不仅解决了上述问题，而且并发性能也是很高的。