面试篇：MySQL

一、如何定位慢查询

1、慢查询原因：

• 聚合查询
• 多表查询
• 表数据量过大查询
• 深度分页查询

表现：页面加载慢、接口无响应，或者响应时间过长（超过1s）

2、如何定位慢查询

** 3、面试官：MySQL中，如何定位慢查?**

• 嗯~，我们当时做压测的时候有的接口非常的慢，接口的响应时间超过了2秒以上，因为我们当时的系统部署了运维的监控系统Skywalking，在展示的报表中可以看到是哪一个接口比较慢，并且可以分析这个接口哪部分比较慢，这里可以看到SOL的具体的执行时间，所以可以定位是哪个sqll出了问题。 如果，项目中没有这种运维的监控系统，其实在MySQL中也提供了慢日志查询的功能，可以在MySQL的系统配置文件中开启这个慢日志的功能，并且也可以设置SQL执行超过多少时间来记录到一个日志文件中，我记得上一个项目配置的是2秒，只要SOL执行的时间超过了2秒就会记录到日志文件中，我们就可以在日志文件找到执行比较慢的SQL了。

二、sql语句执行很慢如何分析/优化

1、通过explain字段+查询语句查看索引使用情况

比如explain select * from user

• possible key 当前sql可能会使用到的索引
• key 当前sql实际命中的索引
• key len 索引占用的大小
• Extra 额外的优化建议

2、面试官：SQL语句执行很慢，如何分析？

• 如果一条sql执行很慢的话，我们通常会使用mysql自动的执行计划explain来去查看这条sql的执行情况，比如在这里面可以通过key和possible_keys检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了可以尝试添加索引或修改返回字段来修复 。

三、索引概念以及底层数据结构

1、什么是索引

• 索引(index)是帮助MySQL高效获取数据的数据结构(有序)
• 提高数据检索的效率，降低数据库的IO成本(不需要全表扫描)
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

2、索引的底层数据结构是什么？

MySQL的InnoDB引擎采用的B+树的数据结构来存储索引

• 阶数更多，路径更短
• 磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据
• B+树便于扫库和区间查询，叶子节点是一个双向链表

3、为什么选择B+树？

首先看二叉搜索树最好的情况性能是O(logn)，最坏情况是O(n)

然后来看红黑树，它的时间复杂度是O(logn)，但是在数据量特别大的时候，树会特别高，就会遍历很多层级，对性能影响较大

下来是B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉以一颗最大度数(max-degree)为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key。并且每个节点下面存储的是数据。比如20节点下方存储的是它的数据。

最后是B+树 ，B+Tree是在BTree基础上的一种优化，使其更话合实现外存储索引结构，InnoDB存储引警就是用B+Tree实现其索引结构。

与B树的区别就是非叶子节点只存储指针，不存储数据。只在叶子节点存储数据，非叶子节点作用只是导航找到叶子节点获取数据

B树与B+树对比:

• ①磁盘读写代价B+树更低：非叶子节点不存储数据，只存储指针。
• ②查询效率B+树更加稳定：数据都存储到叶子节点，都从根开始找到叶子节点，查找路径差不多，所以效率稳定。
• ③B+树便于扫库和区间查询：底层是双向指针的链表，进行范围查询直接从叶子节点进行查询，不需要每次从根节点开始获取数据。

4、面试官：了解过索引吗? (什么是索引)

• 嗯，索引在项目中还是比较常见的，它是帮助MySQL高效获取数据的数据结构，主要是用来提高数据检索的效率，降低数据库的I/O成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低了CPU的消耗。

5、面试官：索引的底层数据结构了解过嘛 ?

• MySQL的默认的存储引擎InnoDB采用的B+树的数据结构来存储索引，选择B+树的主要的原因是:第一阶数更多，路径更短，第二个磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据，第三是B+树便于扫库和区间查询，叶子节点是一个双向链表 。

6、面试官：B树和B+树的区别是什么呢?

• 第一:在B树中，非叶子节点和叶子节点都会存放数据，而B+树的所有的数据都会出现在叶子节点，在查询的时候，B+树查找效率更加稳定。
• 第二:在进行范围查询的时候，B+树效率更高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链表 。

四、聚簇索引、非聚簇索引、回表查询

1、什么是聚簇索引（聚集索引），什么是非聚簇索引（二级索引），什么是回表查询？

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一 (UNIQUE) 索作为聚集索引
• 如果表没有主键，或没有合适的唯一索引，则lnnoDB会自动生成一个rowid作为隐藏的聚集索引

索引效果展示：

• 聚集索引：对于每个叶子节点，对应的数据就是当前聚集索引id的这一行的数据。
• 二级索引：对于每个叶子节点，对应的数据就是当前二级索引字段的id值。
• 回表查询：根据二级索引找到主键值，然后再聚集索引查询主键值叶子节点的那一行数据

2、面试官:什么是聚簇索引什么是非聚簇索引?

• 聚簇索引主要是指数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个,一般情况下主键在作为聚簇索引的。
• 非聚簇索引值的是数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个，一般我们自已定义的索引都是非聚簇索引。

3、面试官:知道什么是回表查询嘛 ?

• 嗯，其实跟刚才介绍的聚簇索引和非聚簇索引是有关系的，回表的意思就是通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表。
• [备注:如果面试官直接问回表，则需要先介绍聚簇索引和非聚簇索引]

五、覆盖索引，超大索引优化

1、什么是覆盖索引？

覆盖索引是指查询使用了索引，返回的列，必须在索引中全部能够找到

• 使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高
• 如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select

2、 超大分页查询

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

我们一起来看看执行limit分页查询耗时对比:

因为，当在进行分页查询时，如果执行 limit 9000000,10，此时需要MySQL排序前9000010 记录，仅仅返回9000000-9000010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路：优化思路: 一般分页查询时，通过创建** 覆盖索引** 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

优化前速度是11s多，这是优化后的效果

3、面试官:知道什么叫覆盖索引嘛 ?

• 覆盖索引是指select查询语句使用了索引，在返回的列，必须在索引中全部能够找到，如果我们使用id查询，它会直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
• 如果按照二级索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select*，尽量在返回的列中都包含添加索引的字段。

4、面试官: MYSQL超大分页怎么处理?

• 超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决。
• 先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了。
• 因为查询id的时候，走的覆盖索引，所以效率可以提升很多 。

六、索引创建原则

1、原则

• 针对于数据量较大，且查询比较频繁的表建立索引。单表超过10万数据(增加用户体验)
• 针对于常作为查询条件 (where)、排序 (order by)分组 (group by) 操作的字段建立索引。
• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
• 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免,回表，提高查询效率
• 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
• 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

2、概括：

• 数据量较大，且查询比较频繁的表
• 常作为查询条件、排序、分组的字段
• 字段内容区分度高
• 内容较长，使用前缀索引
• 尽量联合索引
• 要控制索引的数量
• 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它

3、面试官:索引创建原则有哪些?

• 这个情况有很多，不过都有一个大前提，就是表中的数据要超过10万以上，我们才会创建索引，并且添加索引的字段是查询比较频繁的字段，一般也是像作为查询条件，排序字段或分组的字段这些。
• 还有就是，我们通常创建索引的时候都是使用复合索引来创建，一条sql的返回值，尽量使用覆盖索引，如果字段的区分度不高的话，我们也会把它放在组合索引后面的字段。
• 如果某一个字段的内容较长，我们会考虑使用前缀索引来使用，当然并不是所有的字段都要添加索引，这个索引的数量也要控制，因为添加索引也会导致新增改的速度变慢。

七、什么情况下索引会失效

1、什么情况下索引会失效

①违反最左前级法则

• 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

② 范围查询右边的列，不能使用索引

③不要在索引列上进行运算操作，索引将失效

④字符串不加单引号，造成索引失效

⑤类型转换会造成索引失效

⑥以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效

2、概括

3、面试官:什么情况下索引会失效 ?

• 嗯，这个情况比较多，我说一些自己的经验，以前遇到过的。
• 比如，索引在使用的时候没有遵循最左匹配法则，第二个是，模糊查询，如果%号在前面也会导致索引失效。如果在添加索引的字段上进行了运算操作或者类型转换也都会导致索引失效。
• 我们之前还遇到过一个就是，如果使用了复合索引，中间使用了范围查询，右边的条件索引也会失效。
• 所以，通常情况下，想要判断出这条sql是否有索引失效的情况，可以使用explain执行计划来分析 。

八、谈谈你对索引优化的经验

1、表的设计优化

• 比如设置合适的数值 (tinyint int bigint)，要根据实际情况选择
• 比如设置合适的字符串类型 (char和varchar) char定长效率高，varchar可变长度，效率稍低

2、SQL语句优化

• SELECT语句务必指明字段名称 (避免直接使用select * )
• SQL语句要避免造成索引失效的写法
• 尽量用union all代替union union会多一次过滤，效率低
• 避免在where子句中对字段进行表达式操作
• Join优化能用innerjoin 就不用left join right join，如必须使用一定要以小表为驱动内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序

3、主从复制、读写分离

• 如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构。读写分离解决的是，数据库的写入，影响了查询的效率

4、概括

• 表的设计优化，数据类型的选择
• 索引优化，索引创建原则
• sql语句优化，避免索引失效，避免使用select *......
• 主从复制、读写分离，不让数据的写入，影响读操作
• 分库分表

5、面试官: sql的优化的经验

• 嗯，这个在项目还是挺常见的，当然如果直说sql优化的话，我们会从这几方面考虑，比如建表的时候、使用索引、sql语句的编写、主从复制，读写分离，还有一个是如果量比较大的话，可以考虑分库分表

6、面试官:创建表的时候，你们是如何优化的呢?

• 在定义字段的时候需要结合字段的内容来选择合适的类型，如果是数值的话，像tinyint、int、bigint这些类型，要根据实际情况选择。如果是字符串类型，也是结合存储的内容来选择char和varchar或者text类型。

7、那在使用索引的时候，是如何优化呢?

• 数据量较大，且查询比较频繁的表
• 常作为查询条件、排序、分组的字段
• 字段内容区分度高
• 内容较长，使用前缀索引
• 尽量联合索引
• 要控制索引的数量
• 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它

8、面试官:你平时对sql语句做了哪些优化呢?

• 嗯，这个也有很多，比如SELECT语句务必指明字段名称，不要直接使用select*，还有就是要注意SQL语句避免造成索引失效的写法: 如果是聚合查询，尽量用union all代替union，union会多一次过滤，效率比较低;如果是表关联的话，尽量使用inmerioin，不要使用用left join right join，如必须使一定要以小表为驱动。

九、事物的特性

1、事务概念

• 事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

2、事务的特性（ACID）

• 原子性(Atomicity) :事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性(Consistency): 事务完成时，必须使所有的数据都保持一致状态。
• 隔离性 (Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性(Durability): 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

3、面试官:事务的特性是什么? 可以详细说一下吗?

• 嗯，这个比较清楚，ACID，分别指的是:原子性、一致性、隔离性、持久性;我举个例子，A向B转账500，转账成功，A扣除500元，B增加500元，原子操作体现在要么都成功，要么都失败在转账的过程中，数据要一致，A扣除了500，B必须增加500在转账的过程中，隔离性体现在A向B转账，不能受其他事务干扰在转账的过程中，持久性体现在事务提交后，要把数据持久化(可以说是落盘操作)。

十、并发事务问题、隔离问题

1、并发事务问题

• 脏读：指一个事务读取了另一个事务未提交的数据，如果另一个事务在此后回滚了操作，那么第一个事务所读取到的数据就是无效的。
• 不可重复读：指在一个事务内，多次读取同一数据，由于其他事务的修改，每次读取的结果都不同。
• 幻读：幻读就是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

不可重复读和脏读的区别是：脏读是读到未提交的数据，而不可重复读读到的却是已经提交的数据，但是其违反了事务一致性的要求。

不可重复读和幻读区别：不可重复读主要是针对数据的更新（即事务的两次读取结果值不一样），而幻读主要是针对数据的增加或减少（即事务的两次读取结果返回的数量不一样）。

2、隔离级别

3、面试官:并发事务带来哪些问题?

• 我们在项目开发中，多个事务并发进行是经常发生的，并发也是必然的，有可能导致一些问题
• 第一是脏读，当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
• 第二是不可重复读:比如在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
• 第三是幻读(Phantom read): 幻读与不可重复读类似。它发生在一个事务(T1) 读取了几行数据接着另一个并发事务(T2)插入了一些数据时。在随后的查询中，第一个事务(T1)就会发现多了些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

4、面试官: 怎么解决这些问题呢? MySQL的默认隔离级别是?

• 解决方案是对事务进行隔离。
• MySQL支持四种隔离级别，分别有: - 第一个是，未提交读(read uncommitted) 它解决不了刚才提出的所有问题，一般项目中也不用这个。- 第二个是读已提交(read committed)它能解决脏读的问题的，但是解决不了不可重复读和幻读- 第三个是可重复读(repeatable read)它能解决脏读和不可重复读，但是解决不了幻读，这个也是mysql默认的隔离级别。- 第四个是串行化 (serializable) 它可以解决刚才提出来的所有问题，但是由于让是事务串行执行的，性能比较低。所以，我们一般使用的都是mysql默认的隔离级别:可重复读。

十一、undo log 和redo log的区别

1、先引入缓冲池和数据页的概念

• 缓冲池(buffer pool):主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据(若缓冲池没有数据，则从磁盘加载并缓存》，以一定频率刷新到磁盘，从而减少磁盘I/O，加快处理速度。存储在内存结构
• 数据页(page):是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。页中存储的是行数据。存储再磁盘结构

2、redo log

• 重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性
• 该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo og fle),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时，进行数据恢复使用。

3、undo log

回滚日志，用于记录数据被修改前的信息，作用包含两个:提供回滚和 MVVC(多版本并发控制)。undo log和redo og记录物理日志不一样，它是逻辑日志。

• 可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然
• 当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

undo log可以实现事务的一致性和原子性

4、undo log和redo log的区别

• redo log: 记录的是数据页的物理变化，服务宕机可用来同步数据
• undo log : 记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据
• redo log保证了事务的持久性，undo log保证了事务的原子性和一致性

5、面试官: undo log和redo log的区别

• 好的，其中redo log日志记录的是数据页的物理变化，服务宕机可用来同步数据，而undo log不同，它主要记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据，比如我们删除一条数据的时候，就会在undo log日志文件中新增一条insert语句，如果发生回滚就执行逆操作;
• redo log保证了事务的持久性，undo log保证了事务的原子性和一致性

十二、解释下MVVC

1、事务中的隔离性是如何保证的呢?

• 锁:排他锁(如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁)
• mvcc:多版本并发控制

2、解释下什么是MVVC？

全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突MVCC的具体实现，主要依赖于数据库记录中的隐式字段、undo log日志、readView（读视图）。

3、隐藏字段

4、undo log

• 回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志
• 当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除
• 而update、delete的时候，产生的undo log日志不仅在回滚时需要，mvcc版本访问也需要，不会立即被删除.

** 4.1、undo log的版本链**

这是四个事务，对应着四次不同的修改

此时对应表中的数据如下，其中DB_TRX_ID为事务id，默认为1，DB_ROLL_PTR为回滚指针，目前未指向其他地址。当发生写操作就会在undo log生成记录，记录修改之前的数据，并把DB_ROLL_PTR指针指向修改前记录的地址，且事务ID自增1。

①此时经历事务2的操作，将id为30的age修改为3.那么在记录中的age改为3，DB_TRX_ID自增1为2，并且DB_ROLL_PTR改为上个版本的地址

②事务3修改了id为30的name为A3，则记录中的name改为A3，并且DB_TRX_ID自增1为3，DB_ROLL_PTR修改回滚指针地址为上个事务id为2的地址。并且把事务id为2的回滚指针地址指向事务id为1的地址。

③事务4修改了id为30的age为10，则记录中的age为10，DB_TRX_ID自增1为4，DB_ROLL_PTR指向事务id为3记录的地址。并把事务ID为3的回滚指针指向事务ID为2的记录的地址。

④不同事务或相同事务对同一条记录进行修改，会导致该记录的undo log生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

5、readview

ReadView(读视图)是快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(末提交的) id。

5.1、当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如:select... lock in share mode(共享锁)，select ...for update、 update、 insert、 delete(排他锁)都是一种当前读。

5.2、快照读

简单的select(不加锁)就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

• Read Committed: 每次select，都生成一个快照读。
• Repeatable Read: 开启事务后第一个select语句才是快照读的地方

** 5.3、ReadView包含的4个核心字段**

• 对于m_ids对应事务3、4、5。因为在事务5第一次读的时候，事务3、4、5还没有提交，处于活跃状态。
• 对于min_trx_id对应事务3，因为事务3是当前活跃事务的最小版本号。
• 对于max_trx_id对应事务6，因为当前最大活跃事务是事务5，所以当前最大事务ID+1为6。
• 对于creator_trx_id 对应事务5，因为事务5就是开启快照读的事务。

5.4、版本控制链访问规则

其中trx_id代表当前事务ID，也可能是历史事务ID

不同的隔离级别，生成ReadView的时机不同：

• READ COMMITTED（读已提交） : 在事务中每一次执行快照读时生成ReadView.
• REPEATABLE READ（可重复读）: 仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

5.5 、在READ COMMITTED（读已提交）的隔离级别下，事务中每一次执行快照读时生成ReadView

** 5.5.1、对于第一个读视图根据以下规则可以进行判断**

事务ID当前为4

• 对比规则①，4!=5，所以不可以访问当前版本
• 对比规则②，4!<3，所以不可以访问当前版本
• 对比规则③，4!>6,所以不可以访问该版本
• 对比规则④，3=<4<=6，但是4不能在m_ids集合中，即4不能是3、4、5中任一，所以不可以访问当前版本

事务ID当前为3

• 对比规则①，3!=5，所以 不可以访问当前版本
• 对比规则②，3!<3，所以不可以访问当前版本
• 对比规则③，3!>6,所以不可以访问该版本
• 对比规则④，3=<3<=6，但是3不能在m_ids集合中，即3不能是3、4、5中任一，所以不可以访问当前版本

事务ID当前为2

• 对比规则①，2!=5，所以 不可以访问当前版本
• 对比规则②，2<3，所以可以访问当前版本
• 对比规则③，2!>6,所以不可以访问该版本
• 对比规则④，3!=<2<=6，但是2不能在m_ids集合中，即2不能是3、4、5中任一，所以不可以访问当前版本

** 因此当前读视图可以访问的版本链是事务ID为2的记录**

5.5.2、对于第二个读视图

事务ID当前为4

• 对比规则①，4!=5，所以不可以访问当前版本
• 对比规则②，4!<4，所以不可以访问当前版本
• 对比规则③，4!>6,所以不可以访问该版本
• 对比规则④，4=<4<=6，但是4不能在m_ids集合中，即4不能是4、5中任一，所以不可以访问当前版本

事务ID当前为3

• 对比规则①，3!=5，所以 不可以访问当前版本
• 对比规则②，3<4，所以可以访问当前版本
• 对比规则③，3!>4,所以不可以访问该版本
• 对比规则④，4!=<3<=6，但是3不能在m_ids集合中，即3不能是4、5中任一，所以不可以访问当前版本

** 因此当前读视图可以访问的版本链是事务ID为3的记录**

**5.6、在REPEATABLE READ（可重复读）的隔离级别下，事务中每一次执行快照读时生成ReadView **

RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

操作与读已提交相同

6、事务中的隔离性是如何保证的呢?(你解释一下MVCC)

MySQL中的多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突

①隐藏字段:

• trx_id(事务id)，记录每一次操作的事务id，是自增的
• roll_pointer(回滚指)，指向上一个版本的事务版本记录地址

② undo log:

• 回滚日志，存储老版本数据
• 版本链:多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll pointer指针形成一个链表

③readView解决的是一个事务查询选择版本的问题

• 根据readView的匹配规则和当前的一些事务id判断该访问那个版本的数据
• 不同的隔离级别快照读是不一样的，最终的访问的结果不一样 - RC(读已提交):每一次执行快照读时生成ReadView- RR(可重复读):在事务中第一次执行快照读时生成ReadView，后续复用

7、 面试官：事务中的隔离性是如何保证的呢?(你解释一下MVCC)

• 事务的隔离性是由锁和mvcc实现的。
• 其中mvcc的意思是多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，它的底层实现主要是分为了三个部分，第一个是隐藏字段，第二个是undo log日志，第三个是readView读视图。
• 隐藏字段是指:在mysql中给每个表都设置了隐藏字段，有一个是trx_id(事务id)，记录每一次操作的事务id，是自增的;另一个字段是roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址。最后一个字段是隐藏主键，如果没有主键的时候这个字段就会派上用场。
• undo log主要的作用是记录回滚日志，存储老版本数据，在内部会形成一个版本链，在多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表。
• readView解决的是一个事务查询选择版本的问题，在内部定义了一些匹配规则和当前的一些事务id判断该访问那个版本的数据，不同的隔离级别快照读是不一样的，最终的访问的结果不一样。如果是rc隔离级别，每一次执行快照读时生成ReadView，如果是rr隔离级别仅在事务中第一次执行快照读时生成ReadView，后续复用。

十三、主从同步原理

一个java应用首先要去连接数据库的中间件，中间件至少连接两个库一个是主库，一个是从库。主库负责写数据，从库负责读数据。当主库写数据的时候，就把数据同步到从库中。所以主要解决的是如何同步的，同步的原理。

1、主从同步原理

二进志(BINLOG)记录了所有的 DDL(数据定义语言)语和 DML(数据操纵语言)语句，但不包括数据查询(SELECT、SHOW)语句。

复制分成三步：

• Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog中。
• 从库读取主库的二进制日志文件 Binlog，写入到从库的中继日志 Relay Log。
• slave重做中继日志中的事件，将改变反映它自己的数据。

2、概括

主从同步原理

MySQL主从复制的核心就是二进制日志binlog(DDL(数据定语言)语和 DML(数据操纵语言)语句)

• 主库在事务提交时，会把数据变更记录在二进制日志文件 Binog 中。
• 从库读取主库的二进制日志文件 Binlog，写入到从库的中继日志 Relay Log。
• 从库重做中继日志中的事件，将改变反映它自己的数据 。

3、 面试官:说一下主从同步的原理?

MySOL主从复制的核心就是二进制日志，二进制日志记录了所有的 DDL语句和 DML语句
具体的主从同步过程大概的流程是这样的:

• Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
• 从库读取主库的二进制日志文件 Binlog，写入到从库的中继日志 RelayLog。
• slave重做中继日志中的事件，将改变反映它自己的数据。

4、排疑：mysql主从复制的时候为什么不直接将bin log直接读取并执行到从库，而是先保存到relay log再保存到从库？

• MySQL主从复制默认是异步的模式。MySQL增删改操作会全部记录在Binlog中，当slave节点连接master时，会主动从master处获取最新的Binlog文件。并把Binlog存储到本地的relay log中，然后去执行relay log的更新内容。这样做的好处是，如果slave节点在执行过程中出现问题，可以重新从relay log中读取数据，而不需要再次从master处获取数据。

十四、分库分表

分库分表的时机:

• 前提，项目业务数据逐渐增多，或业务发展比较迅速（单表的数据量达1000W或20G以后）
• 优化已解决不了性能问题 (主从读写分离、查询索引...)
• IO瓶颈(磁盘l0、网络lO)、CPU瓶颈 (聚合查询、连接数太多)

1、拆分策略

1.1、垂直分库

一个数据库有多个表，且都为热点数据表，为了避免单个数据库访问压力过大，将表分开存储到不同的数据库中。

垂直分库：以表为依据，根据业务将不同表拆分到不同库中

特点：

• 按业务对数据分级管理、维护、监控、扩展
• 在高并发下，提高磁盘1O和数据量连接数

1.2、垂直分表

一个表中有多个字段，该表的某些字段访问压力过大但某些字段访问压力小，可以将表的字段根据访问量拆分不同的表保存到数据库中。

垂直分表:以字段为依据，根据字段属性将不同字段拆分到不同表中 。

特点:

• 冷热数据分离
• 减少IO过渡争抢，两表互不影响

拆分规则:

• 把不常用的字段单独放在一张表
• 把text，blob等大字段拆分出来放在附表中

** 1.3、水平分库**

某些数据库的总体访问压力过大，为了解决这种情况，可以将数据拆分到不同的数据库中，但是会造成不同数据库的数据是不同的，解决方法是使用路由规则来解决。

水平分库： 将一个库的数据拆分到多个库中。

但是每个库的数据不一定都相同，为了解决这种问题，可以根据以下规则来解决

路由规则：

• 根据id节点取模
• 按id也就是范围路由，节点1(1-100万)节点2(100万-200万）

特点:

• 解决了单库大数量，高并发的性能瓶颈问题
• 提高了系统的稳定性和可用性

** 1.4、水平分表**

某些表的总体访问压力过大，为了解决这种情况，可以将表中的数据拆分到多个表中，但是会造成多个表的数据是分散的，解决方法是使用路由规则来解决。

水平分表:将一个表的数据拆分到多个表中(可以在同一个库内)

特点:

• 优化单一表数据量过大而产生的性能问题
• 避免1O争抢并减少锁表的几率:

2、分库分表产生的问题

分库之后产生的问题：

• 分布式事务一致问题
• 跨节点关联查询
• 跨节点分页、排序函数
• 主键避重

使用中间件即可解决以上问题

• sharding-sphere
• mycat

3、概括

业务介绍

• 根据自己简历上的项目，想一个数据量较大业务(请求数多或业务累积大)
• 达到了什么样的量级(单表1000万或超过20G)

具体拆分策略

• 水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题（较多）
• 水平分表，解决单表存储和性能的问题（较少）
• 垂直分库，根据业务进行拆分，高并发下提高磁盘IO和网络连接数（最多）
• 垂直分表，冷热数据分离，多表互不影响（较多）

十五、说一说MyISAM索引的实现原理

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址，MyISAM索引的原理图如下。

这里假设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示。同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

十六、innodb为什么要用自增id作为主键？

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置。

如果使用随机数字作为索引，比如身份证号等数字，那么对于添加一条数据，就会可能出现重构索引，会造成频繁的移动，对性能造成了巨大的损耗。

十七、MySQL常见的存储引擎InnoDB、MyISAM的区别？

1、事务

• MyISAM不支持
• InnoDB支持

2、锁级别

• MyISAM 表级锁 - MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。就是说对MyISAM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作；而对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。
• InnoDB 行级锁及外键约束 - InnoDB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁！行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。在InnoDB两个事务发生死锁的时候，会计算出每个事务影响的行数，然后回滚行数少的那个事务。当锁定的场景中不涉及Innodb的时候，InnoDB是检测不到的。只能依靠锁定超时来解决。）

3、行数

• MyISAM存储表的总行数
• InnoDB不存储总行数

4、索引

• MyISAM采用非聚集索引，B+树叶子存储指向数据文件的指针 - MyISAM适合：插入不频繁，查询非常频繁，如果执行大量的SELECT，MyISAM是更好的选择， 没有事务。
• InnoDB主键索引采用聚集索引，B+树叶子存储数据 - 可靠性要求比较高，或者要求事务；表更新和查询都相当的频繁， 大量的写操作

十八、数据库三范式，根据某个场景设计数据表？优缺点是什么？

1、第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

2、第二范式(确保表中的每列都和主键相关)

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说 在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

3、第三范式(确保每列都和主键列直接相关,而不是间接相关)

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。

优点：可以尽量得减少数据冗余 缺点:对于查询需要多个表进行关联，更难进行索引优化 反范式化: 优点:可以减少表得关联 缺点:数据冗余以及数据异常。

十九、数据库悲观锁和乐观锁的原理和应用场景？

1、悲观锁

悲观锁，先获取锁，再进行业务操作，一般就是利用类似 SELECT … FOR UPDATE 这样的语句，对数据加锁，避免其他事务意外修改数据。

当数据库执行

select … for update

时会获取被select中的数据行的行锁，

select for update

获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。

2、乐观锁

乐观锁则与悲观锁相反，先进行业务操作，只在最后实际更新数据时进行检查数据是否被更新过。Java 并发包中的 AtomicFieldUpdater 类似，也是利用 CAS 机制，并不会对数据加锁，而是通过对比数据的时间戳或者版本号，来实现乐观锁需要的版本判断。

二十、Myql中的事务回滚机制,持久性，隔离级别的实现

1、事务回滚机制

undo log实现事务的回滚机制，所有事务进行的修改都会先记录到这个回滚日志中，然后在对数据库中的对应行进行写入。当事务已经被提交之后，就无法再次回滚了回滚日志作用：

• 能够在发生错误或者用户执行 ROLLBACK 时提供回滚相关的信息
• 在整个系统发生崩溃、数据库进程直接被杀死后，当用户再次启动数据库进程时，还能够立刻通过查询回滚日志将之前未完成的事务进行回滚，这也就需要回滚日志必须先于数据持久化到磁盘上，是我们需要先写日志后写数据库的主要原因。

2、持久性

redo log 是实现事务的持久性。当数据修改时候，就会将已修改的数据写入缓冲池的redo log，不管是否提交。只要缓冲池的redo log出现变化的时候，磁盘中的redo log就会将缓冲池的redo log同步到磁盘中的redo log中。当出现宕机的时候，数据库就会从磁盘中的redo log进行事务回滚，保证事务的持久化。

3、隔离级别

MVVC+锁实现数据库的隔离级别对在同一时间执行的事务进行控制，限制不同的事务对于同一资源的访问和更新，锁分为共享锁和互斥锁

二十一、说一说drop、delete与truncate的区别？

1、drop

Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。

2、truncate

Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小。

3、delete

Delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除。会触发这个表上所有的delete触发器

二十二、索引是什么？

在 MySQL 中，索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录，索引中包含着对数据库所有记录的引用指针。

二十三、索引的优缺点？

1、优点

通过使用索引可以大大加快数据的查询速度（使用索引最主要的原因）

2、缺点

• 时间方面来看，创建维护索引需要耗费时间，具体表现为在对表中数据进行增删改的时候，索引也需要动态维护，降低增删改的执行效率
• 空间方面来看，索引的存储也需要占据物理空间

二十四、MySQL 索引类型都有什么？

1、按存储结构来划分

• B-Tree索引：B-Tree 结构里每个节点包含了索引值和表记录的信息，数据分布在各个节点之中，可以加快访问速度，不需要扫描全表获取数据
• B+Tree 索引：B-Tree 的改进，是 MySQL 使用的索引存储结构。数据都存储在叶子节点上，在叶子节点间增加了顺序访问指针
• Hash 索引：基于哈希表实现，只有精确匹配索引的所有列查询才有效。对于每行数据，存储引擎都会对其计算一个哈希码（hash code），并且 Hash 索引将所有的哈希码存储在索引中，同时在索引表中保持指向每个数据行的指针
• 全文索引：通过建立倒排索引，极大的提升检索效率，解决判断 字段是否包含 的问题

2、从应用层次来划分

• 普通索引：一个索引只包含单个列，一个数据库可包含多个单列索引
• 唯一索引：索引值必须唯一（允许有 null 值）
• 复合索引：多个值组成一个索引，用于组合搜索，效率高于索引合并

3、从数据的存储方式划分，根据数据的物理顺序与键值的逻辑关系

• 聚簇索引：不是一种单独存在的索引类型，是一种数据存储方式。细节取决于不同的实现，以 InnoDB 数据库为例，其聚簇索引就是在同一个结构中中保持 B-Tree 索引和数据行
• 非聚簇索引：不是聚簇索引的就是非聚簇索引

二十五、索引的底层实现？

1、Hash 索引

基于哈希表实现，只有精确匹配索引的所有列查询才有效。对于每行数据，存储引擎都会对其计算一个哈希码（hash code），并且 Hash 索引将所有的哈希码存储在索引中，同时在索引表中保持指向每个数据行的指针。

2、B-Tree 索引

B-Tree 结构里每个节点包含了索引值和表记录的信息，数据分布在各个节点之中，可以加快访问速度，不需要扫描全表获取数据。

3、B+Tree 索引

B-Tree 的改进，是 MySQL 使用的索引存储结构。数据都存储在叶子节点上，在叶子节点间增加了顺序访问指针，相较于 B-Tree 每次都需要从根节点开始查找，B+Tree 存储的结构范围查找效率更高

4、B+Tree 性质：

• 叶子节点保存全部关键字信息以及指向这些关键字记录的指针，叶子节点本身依照关键字自小而大顺序连接。（这种结构会在上层非叶子节点存储一部分冗余数据，但是这样的缺点是可以容忍的，因为冗余的都是索引数据，不会对内存造成大的负担）
• 非叶子节点都是索引部分，仅含子树的最大最小关键字
• B+Tree 中含两个头指针，一个是树的根节点，一个是最小关键字的叶节点

二十六、为什么索引结构默认使用 B+Tree？

1、B+Tree：

• B+树的磁盘读 写代价更低： - B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B(B-)树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
• B+树更加适合区间查询： - 由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，必须从根节点开始进行一次中序遍历，所以B+树更加适合区间查询的情况，所以通常B+树用于数据库索引。

2、Hash

• Hash索引虽然可以快速定位，但是没有顺序，IO复杂度高；
• 适合等值查询，如=、in()、<=>，Hash索引在查询等值时非常快 ，但是不支持范围查询；
• 因为不是按照索引值顺序存储的，就不能像B+Tree索引一样利用索引完成排序 ；
• 因为Hash索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找 ；
• 如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

3、二叉树

树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高

4、红黑树

树的高度随着数据量增加而增加，IO代价高

二十七、SQL语句在MySQL中的执行过程

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

查询语句的执行流程如下：

• 权限校验（如果命中缓存）--->查询缓存--->分析器--->优化器--->权限校验--->执行器--->引擎

更新语句执行流程如下：

• 分析器---->权限校验---->执行器--->引擎---redo log(prepare 状态)--->binlog--->redo log(commit 状态)