MySQL核心技术点连环问

MySQL的存储引擎架构是什么？

MySQL 存储引擎采用的是 插件式架构 ，支持多种存储引擎。他可以针对不同的数据表，选用不同的存储引擎，用于解决不同的业务场景。常见的存储引擎有：MyISAM，InnoDB，Memory，Hash等。

 

MyISAM 和 InnoDB 的区别

MyISAM其实是早期MySQL自研的一款存储引擎，其主要特点是支持全文索引、压缩、空间函数等功能。MyISAM刚推出的时候挺受大家的欢迎。但是使用MyISAM会存在一些弊端，例如：无法支持事务操作，无法支持行锁，另外出现崩溃后无法安全恢复。所以在大多数互联网公司中，MyISAM均不受待见。

 

InnoDB其实早期是一家不知名的技术公司所研发的，但是后来却被MySQL的开发团队看中后给并入的。InnoDB底层支持事务特性，以及行锁，同时由于采用了两阶段提交写操作，所以即使是服务崩溃了也可以保证安全恢复。

 

下边是一张比对MyISAM和InnoDB两种存储引擎下数据库的读写性能。

由于MyISAM不支持多并发读/写，因为涉及到了锁表操作，所以随着机器CPU核数的增加，MyISAM的性能并没有太高的变化。

而由于InnoDB支持多并发读特性，所以会随着CPU核数的增加，读写性能也增加。

 

如果你想更多的了解下MySQL其他存储引擎之间的特性比对，这里我找了一张图帮你深入认识下。

 

说说你是如何理解索引的？

其实索引就是提前排序好的文件，他可以辅助我们在众多的数据中，迅速的找到所需数据。

 

索引的优缺点有哪些？

优点

• 建立索引可以提升表中数据检索的速率（减少数据的检索量）。
• 可以通过建立唯一索引来保证数据的唯一性。

缺点

• 需要额外建立一棵b+树进行维护，所有的增删改都需要同步维护这颗额外的索引树，所以当表的索引树较多的时候，整体的写性能会受到一定的影响。
• 给表建立索引的时候，需要有额外的物理空间。我曾经见过一张日志表，实际内容只有4gb左右大小，但是辅助索引空间占用了20gb。

 

使用索引就可以提升性能了吗？

其实未必，有的时候我们会发现，明明表里面有建立索引，但是sql执行的时候却没有选择采用索引。这是因为MySQL底层在进行SQL执行之前，会有一个叫做“性能评估”的阶段，它会根据评估结果，决定是否要走索引查询。

 

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

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

 

聚簇索引和非聚簇索引

 

MyISAM

如果是MyISAM存储引擎的话，MyISAM的B+树设计中，不论是聚簇索引还是非聚簇索引，其叶子结点记录的值，都是实际行记录的地址，如下图所示：

 

 

MyISAM聚簇索引结构图

 

 

MyISAM非聚簇索引结构图

 

这样的设计里，根据辅助索引检索到具体的叶子节点之后，可以快速的根据实际地址去定位到具体的行记录。看起来效率很高效，但是当主数据的地址发生变化的时候，MyISAM其他索引树中的地址也要发生变化，所以这种设计，在数据更新频繁的情况下，维护成本也不低。

 

当我们在MyISAM存储引擎中创建一个表时，这个表会相应生成三个文件

​ 1、.frm文件，这是表定义文件。

​ 2、.myi文件，这个表存储了数据的索引。

​ 3、.md文件，这个表存储的是数据。

 

InnoDB

而如果是在InnoDB中，聚簇索引其实就是主键索引，其特点是将数据内容存放在了叶子结点，如果需要进行根据主键查询的话，就需要从根节点开始往下层节点树开始遍历。MySQL的每张表都会有一个聚簇索引（主键），但是非聚簇索引不一定会有。

 

所以当我们选择了InnoDB作为存储引擎的话，如果你没有设置主键的话，其实默认MySQL底层会给你创建一列字段用作主键。

 

在InnoDB中，其数据和索引都是存放在一份文件中（.ibd后缀）。不过这里要注意的是，InnoDB存储引擎中中设计的表，其主键索引的叶子节点就是实际的数据信息。而非聚簇索引的叶子节点是主键的值。（注意这里不再是地址了）

具体如下图所示：

 

InnoDB主键索引结构图

 

InnoDB非聚簇索引结构图

 

使用非聚簇索引有哪些优势？

使用非聚簇索引的优势其实最明显的体现在最左匹配原则中。当我们需要检索的字段完全可以在辅助索引中获取的时候，那么这个时候，查询可以不进行回表操作，从而提升查询的性能。

 

MySQL使用规范有哪些？

• 建议所有数据表统一使用InnoDB存储引擎
• 建议默认数据库隔离级别采用read commited，如果有需要可以使用Spring的声明式事务注解去指定其他隔离级别。
• 每个表都一定要有一个自增且唯一的主键id，按照业务评估是采用int还是bigint类型，禁止使用varchar类型建立主键。
• 表的列数目不建议超过30列，如果超过需要考虑做垂直拆分。
• 统一字符集为utf8mb4
• 原则上不允许使用uuid作为主键，容易在插入新数据的时候提升叶子节点裂变的概率，影响性能。
• 唯一索引命名前缀udx_,普通索引命名前缀idx_
• 表字段建议有默认值，不要是NULL，并且需要有字段备注和表备注。
• SQL执行的时候，如果需要走索引，需要注意最左匹配原则，尽量将区分度高的索引列放在前边，但是要结合业务场景去定义。
• 金融类业务的字段，使用int类型存储，单位转换为分，如有特殊业务需要使用小数，建议使用decimal类型。
• 数字类型字段，如果确定为非负数，可以标记为unsigned类型

 

讲讲你所了解的MySQL的Redo Log

MySQL的InnoDB在设计底层实现的时候，为了避免发生数据丢失的问题，当前事务数据库系统（并非 MySQL 所独有）普遍都采用了 WAL（Write Ahead Log，预写日志）策略：即当事务提交时，先写重做日志（redo log），再修改页（先修改缓冲池，再刷新到磁盘）；当由于发生宕机而导致数据丢失时，通过 redo log 来完成数据的恢复。这也是事务 ACID 中 D（Durability 持久性）的要求。

 

这里要注意Redo Log只是在InnoDB存储引擎中存在的，它的存在让MySQL拥有了数据崩溃恢复的功能。

 

有了 Redo Log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

 

当有写类型SQL提交的时候，数据首先会同步修改掉缓存池里面的内容，接着会往一个叫做Redo Log Buffer的位置写入。接着Redo Log Buffer的缓存会被Master Thread定时写入到磁盘Redo Log中。

 

而新的数据从Redo Log Buffer同步到实际磁盘中的Redo Log文件这一环节，其实是可以根据一个参数 innodb_flush_log_at_trx_commit 去调整的。

 

 

innodb_flush_log_at_trx_commit 参数不同值的含义：

0：写入到内存中后，每秒由master thread去调用fsync记录到系统磁盘中

1：每次提交事务都写入磁盘。

2：写入操作系统的文件缓存中，由操作系统每秒刷盘

 

Redo Log会被写满吗？

在事务操作中，每次写入数据，都会往Redo Log里面记录一份备用数据，那么久而久之是否会出现Redo Log空间过大，导致存储空间不足的问题呢？

 

其实设计Redo Log的人也有思考过这个问题，所以下边让我们来看看Redo Log底层的设计思想是如何的。

 

其实MySQL的Redo Log是一组文件的集合，其底层是往多个文件去做写入操作的。之所以是多个文件的组合是想把不同的文件放在不同的磁盘，分散掉写入的IO压力。就像下边的这张图所示，当InnoDB往Redo Log去写数据的时候，首先会写入 ib_logfile0，然后是ib_logfile1，接着是ib_logfile2，依次循环。

 

如果说三个文件都被写满了，要怎么处理？

其实在每份ib_logfile文件中，都有一个write pos标记和checkpoint标记。在write pos和CheckPoint之间的范围，用于表示可以写入的磁盘空间。

如果 write pos 追上 CheckPoint，就表示 redo log file 满了，这时候不能再执行新的更新，得停下来先覆盖（擦掉）一些 redo log，把 CheckPoint 推进一下。

 

综上所述，Checkpoint 所做的事情无外乎是将缓冲池中的脏页刷新到磁盘。不同之处在于每次刷新多少页到磁盘，每次从哪里取脏页，以及什么时间触发 Checkpoint。在 InnoDB 存储引擎内部，有两种 Checkpoint，分别为：

• Sharp Checkpoint：发生在数据库关闭时将所有的脏页都刷新回磁盘，这是默认的工作方式，参数 innodb_fast_shutdown=1
• Fuzzy Checkpoin：InnoDB 存储引擎内部使用这种模式，只刷新一部分脏页，而不是刷新所有的脏页回磁盘。关于 Fuzzy CheckPoint 具体的情况这里就不再赘述了。

 

 

 

MySQL中一条SQL的执行流程是怎样的？

这里我整理了一张底层执行的原理图和大家分享。

 

 

 

聊聊MySQL中的Undo Log

我们知道，MySQL在执行事务之前，会先将数据写入到undo log当中，如果事务执行出现了异常，那么就会通过undo log进行回滚，从而恢复数据。

undo log里面记录的数据有：事务id，主键id，表id，undo log的日志编号，undo log的日志类型，以及undo log的起始地址。

 

而undo log的底层，其实还存储了两列字段，分别是trx_id（当前的事务id）和roll_pointer（回滚指针），这个roll_pointer指向了不同版本的数据。

所以在多个事务并非执行的时候，同一条记录会有多个版本的存在，这些不同的版本是通过一个叫做undo log的版本链来进行关联的，整体存储结构如下图所示：

在一个事务执行的开始阶段，会创建一个ReadView对象，这个对象在创建的时候会记录下 哪些事务正在执行，以及自己目前可以看到的最大事务id号（tx_id）。

 

有了这些Undo Log版本链和Read View的关系维护，也就说同一行数据被多个事务更新的时候，不同的事务都会有自己的一个独立的视图，每个视图都会有自己独立的txid。那么在读这些数据的时候，只需要结合ReadView里面记录的事务id判断即可。而这就是MySQL的MVCC底层实现的本质原理了。

 

当事务执行结束之后，版本号低于当前最新版本号的那些Undo Log文件基本就是可以抛弃的了。在innoDB1.1之前，这部分的工作是master thread去负责回收多余空间的，后来出现了purge thread之后，就大大减轻了master thread的压力。早期innoDB1.2的时候，回收这部分的工作都是由单个purge thread处理的，但是在InnoDB1.2之后是多线程进行 undo 页的回收，提升了随机读写的效率