写在前面

🔥我把后端Java面试题做了一个汇总，有兴趣大家可以看看！这里👉

⭐️在无数次的复习巩固中，我逐渐意识到一个问题：面对同样的面试题目，不同的资料来源往往给出了五花八门的解释，这不仅增加了学习的难度，还容易导致概念上的混淆。特别是当这些信息来自不同博主的文章或是视频教程时，它们之间可能存在的差异性使得原本清晰的概念变得模糊不清。更糟糕的是，许多总结性的面试经验谈要么过于繁复难以记忆，要么就是过于简略，对关键知识点一带而过，常常在提及某项技术时，又引出了更多未经解释的相关术语和实例，例如，在讨论ReentrantLock时，经常会提到这是一个可重入锁，并存在公平与非公平两种实现方式，但对于这两种锁机制背后的原理以及使用场景往往语焉不详。

⭐️正是基于这样的困扰与思考，我决定亲自上阵，撰写一份与众不同的面试指南。这份指南不仅仅是对现有资源的简单汇总，更重要的是，它融入了我的个人理解和解读。我力求回归技术书籍本身，以一种层层递进的方式剖析复杂的技术概念，让那些看似枯燥乏味的知识点变得生动起来，并在我的脑海中构建起一套完整的知识体系。我希望通过这种方式，不仅能帮助自己在未来的技术面试中更加从容不迫，也能为同行们提供一份有价值的参考资料，使大家都能在这个过程中有所收获。

MySQL相关面试题

面试官： 数据库的三大范式

候选人：

1. 每个列都不可拆分
2. 在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分
3. 在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键列

面试官： MyISAM和InnoDB区别

候选人：

1. 是否⽀持⾏级锁 : MyISAM 只有表级锁(table-level locking)，⽽InnoDB ⽀持⾏级锁(rowlevel locking)和表级锁,默认为⾏级锁。

补充：行级锁和表级锁？

行级锁是一种细粒度的锁机制，它只锁定对特定行的访问。这意味着当一个事务正在更新或删除一个表中的某一行时，其他事务不能同时更新或删除这一行，但它们仍然可以读取这一行或者修改表中的其他行。

• 这种锁的优点是它提供了更好的并发性能，因为多个事务可以在同一时间内操作表的不同部分。
• 缺点是它可能会导致更高的开销，因为系统需要跟踪哪些行已经被锁定。

表级锁是一种粗粒度的锁机制，它锁定整个表。当一个事务对某个表进行读写操作时，它可以阻止其他事务对该表的任何访问，直到锁被释放。

• 这种锁的优点是实现简单，管理成本相对较低。
• 缺点是由于锁住了整个表，所以并发能力较差，尤其是在需要频繁并发读写的情况下。

2. 是否⽀持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原⼦性,其执⾏速度⽐InnoDB类型更快，但是不提供事务⽀持。但是InnoDB 提供事务⽀持事务，外部键等⾼级数据库功能。 具有提交事务(commit)、回滚(rollback)和崩溃修复能⼒。

3. 是否⽀持外键： MyISAM不⽀持，⽽InnoDB⽀持。

4. 是否⽀持MVCC ：仅 InnoDB ⽀持。应对⾼并发事务, MVCC⽐单纯的加锁更⾼效；MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下⼯作；MVCC可以使⽤ 乐观锁 和 悲观锁来实现。

《MySQL高性能》上⾯有⼀句话这样写到：

不要轻易相信“MyISAM⽐InnoDB快”之类的经验之谈，这个结论往往不是绝对的。在很多我们已知场景中，InnoDB的速度都可以让MyISAM望尘莫及，尤其是⽤到了聚簇索引，或者需要访问的数据都可以放⼊内存的应⽤。

补充：乐观锁与悲观锁？

悲观锁 ：基于假设：当事务要对某个数据进⾏操作时，认为很可能会发⽣冲突。因此，在事务开始时就对数据进⾏锁定，阻⽌其他事务同时对其进⾏修改，直到当前事务完成。这种⽅式虽然能够保证数据的⼀致性和准确性，但由于锁的存在，可能会导致其他事务等待，增加了系统开销。

乐观锁 ：基于另⼀种假设：认为事务之间发⽣冲突的概率较低，因此在读取数据时不⽴即加锁，⽽是允许多个事务同时读取数据。当事务尝试提交更改时，会检查在此期间是否有其他事务修改过相同的数据。如果有冲突，则当前事务失败，可能需要回滚并重新开始。乐观锁通常通过版本号或时间戳来检测数据是否已被修改。

面试官： MySQL中，如何定位慢查询?

候选人：

嗯~，我们当时做压测的时候有的接口非常的慢，接口的响应时间超过了2秒以上，因为我们当时的系统部署了运维的监控系统Skywalking ，在展示的报表中可以看到是哪一个接口比较慢，并且可以分析这个接口哪部分比较慢，这里可以看到SQL的具体的执行时间，所以可以定位是哪个sql出了问题。

如果，项目中没有这种运维的监控系统，其实在MySQL中也提供了慢日志查询的功能，可以在MySQL的系统配置文件中开启这个慢日志的功能，并且也可以设置SQL执行超过多少时间来记录到一个日志文件中，我记得上一个项目配置的是2秒，只要SQL执行的时间超过了2秒就会记录到日志文件中，我们就可以在日志文件找到执行比较慢的SQL了。

#  启用慢查询日志
slow_query_log = 1 
# 定义慢查询的时间阈值，默认为10秒
long_query_time = 2

面试官： 那这个SQL语句执行很慢, 如何分析呢？

候选人：如果一条sql执行很慢的话，我们通常会使用mysql自动的执行计划explain来去查看这条sql的执行情况。

• 比如在这里面可以通过 key(实际使用的索引) 和 key_len(使用的索引长度) 检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况。
• 第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描(INDEX)或全盘扫描(ALL)。
• 第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复。

SQL案例：

假设我们有一个数据库表 employees，结构如下：

CREATE TABLE employees (emp_no INT(11) NOT NULL,birth_date DATE NOT NULL,first_name VARCHAR(14) NOT NULL,last_name VARCHAR(16) NOT NULL,gender ENUM('M','F') NOT NULL,hire_date DATE NOT NULL,PRIMARY KEY (emp_no)
);

现在我们想分析下面这个查询的执行计划，我们可以使用 EXPLAIN 如下：

EXPLAIN SELECT * FROM employees WHERE first_name = 'John' AND last_name = 'Doe';

执行这条命令后，会得到一个结果集：

从这个例子中可以看出，因为 first_name 和 last_name 没有索引，所以MySQL需要对整个表进行全表扫描来查找符合条件的记录。这在数据量大时会非常低效。

面试官： 一条SQL语句执行得很慢的原因有哪些？

候选人： 分以下两种情况来讨论。

1、大多数情况是正常的，只是偶尔会出现很慢的情况。

2、在数据量不变的情况下，这条SQL语句一直以来都执行的很慢。

一、针对偶尔很慢的情况

一条 SQL 大多数情况正常，偶尔才能出现很慢的情况，针对这种情况，我觉得这条SQL语句的书写本身是没什么问题的，而是其他原因导致的，那会是什么原因呢？

1. 数据库在刷新脏页（flush）我也无奈啊

   当我们要往数据库插入一条数据、或者要更新一条数据的时候，我们知道数据库会在内存中把对应字段的数据更新了，但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，等到空闲的时候，在通过 redo log 里的日记把最新的数据同步到磁盘中去。

   当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

   刷脏页有下面4种场景（后两种不用太关注“性能”问题）：

   • redo log写满了： redo log 里的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候 redo log 很快就会被写满了，这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身心来把数据同步到磁盘中去的，而这个时候，就会导致我们平时正常的SQL语句突然执行的很慢，所以说，数据库在在同步数据到磁盘的时候，就有可能导致我们的SQL语句执行的很慢了。
   • 内存不够用了： 如果一次查询较多的数据，恰好碰到所查数据页不在内存中时，需要申请内存，而此时恰好内存不足的时候就需要淘汰一部分内存数据页，如果是干净页，就直接释放，如果恰好是脏页就需要刷脏页。
   • MySQL 认为系统“空闲”的时候： 这时系统没什么压力。
   • MySQL 正常关闭的时候： 这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

2. 拿不到锁我能怎么办。

   这个就比较容易想到了，我们要执行的这条语句，刚好这条语句涉及到的表，别人在用，并且加锁了，我们拿不到锁，只能慢慢等待别人释放锁了。或者表没有加锁，但要使用到的某个一行被加锁了，这个时候，我也没办法啊。

   如果要判断是否真的在等待锁，我们可以用 show processlist这个命令来查看当前的状态。

二、针对一直都这么慢的情况

如果在数据量一样大的情况下，这条 SQL 语句每次都执行的这么慢，那就就要好好考虑下你的 SQL 书写了，下面我们来分析下哪些原因会导致我们的 SQL 语句执行的很不理想。

我们先来假设我们有一个表，表里有下面两个字段,分别是主键 id，和两个普通字段 c 和 d。

CREATE TABLE `t` (`id` int(11) NOT NULL,`c` int(11) DEFAULT NULL,`d` int(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB;

1. 没用到索引

   （1）、字段没有索引

   刚好 c 字段上没有索引，只能走全表扫描了，体验不会索引带来的乐趣了，所以，这会导致这条查询语句很慢。

   select * from t where 100 <c and c < 100000;
   

   （2）、字段有索引，但却没有用索引

   好吧，这个时候你给 c 这个字段加上了索引，然后又查询了一条语句:

   select * from t where c - 1 = 1000;
   

   但是这样子在查询的时候会用索引查询吗？

   答是不会，如果我们在字段的左边做了运算，那么很抱歉，在查询的时候，就不会用上索引了，所以呢，大家要注意这种字段上有索引，但由于自己的疏忽，导致系统没有使用索引的情况了。

   正确的查询应该如下：

   select * from t where c = 1000 + 1;
   

   有人可能会说，右边有运算就能用上索引？难道数据库就不会自动帮我们优化一下，自动把 c - 1=1000 自动转换为 c = 1000+1。

   不好意思，确实不会帮你，所以，你要注意了。

   （3）、函数操作导致没有用上索引

   如果我们在查询的时候，对字段进行了函数操作，也是会导致没有用上索引的，例如

   select * from t where pow(c,2) = 1000;
   

   这里我只是做一个例子，假设函数 pow 是求 c 的 n 次方，实际上可能并没有 pow(c,2)这个函数。其实这个和上面在左边做运算也是很类似的。

2. 数据库自己选错索引了

   我们在进行查询操作的时候，例如:

   select * from t where 100 < c and c < 100000;
   

   我们知道，主键索引和非主键索引是有区别的，主键索引存放的值是整行字段的数据，而非主键索引上存放的值不是整行字段的数据，而且存放主键字段的值。

   也就是说，我们如果走 c 这个字段的索引的话，最后会查询到对应主键的值，然后，再根据主键的值走主键索引，查询到整行数据返回。

   所以就算你在 c 字段上有索引，系统也并不一定会走 c 这个字段上的索引，而是有可能会直接扫描扫描全表，找出所有符合 100 < c and c < 100000 的数据。

   为什么会这样呢？

   其实是这样的，系统在执行这条语句的时候，会进行预测：究竟是走 c 索引扫描的行数少，还是直接扫描全表扫描的行数少呢？显然，扫描行数越少当然越好了，因为扫描行数越少，意味着I/O操作的次数越少。

   如果是扫描全表的话，那么扫描的次数就是这个表的总行数了，假设为 n；而如果走索引 c 的话，我们通过索引 c 找到主键之后，还得再通过主键索引来找我们整行的数据，也就是说，需要走两次索引。而且，我们也不知道符合 100 c < and c < 10000 这个条件的数据有多少行，万一这个表是全部数据都符合呢？这个时候意味着，走 c 索引不仅扫描的行数是 n，同时还得每行数据走两次索引。

   系统是有可能走全表扫描而不走索引的。那系统是怎么判断呢？

   判断来源于系统的预测，也就是说，如果要走 c 字段索引的话，系统会预测走 c 字段索引大概需要扫描多少行。如果预测到要扫描的行数很多，它可能就不走索引而直接扫描全表了。

   那么问题来了，系统是怎么预测判断的呢？

   系统是通过索引的区分度来判断的，一个索引上不同的值越多，意味着出现相同数值的索引越少，意味着索引的区分度越高。我们也把区分度称之为基数，即区分度越高，基数越大。所以呢，基数越大，意味着符合 100 < c and c < 10000 这个条件的行数越少。

   所以呢，一个索引的基数越大，意味着走索引查询越有优势。

   那么问题来了，怎么知道这个索引的基数呢？

   系统当然是不会遍历全部来获得一个索引的基数的，代价太大了，索引系统是通过遍历部分数据，也就是通过采样的方式，来预测索引的基数的。

   重点的来了，居然是采样，那就有可能出现失误的情况，也就是说，c 这个索引的基数实际上是很大的，但是采样的时候，却很不幸，把这个索引的基数预测成很小。例如你采样的那一部分数据刚好基数很小，然后就误以为索引的基数很小。然后系统就不走 c 索引了，直接走全部扫描了。

所以呢，说了这么多，得出结论：由于统计的失误，导致系统没有走索引，而是走了全表扫描，而这，也是导致我们 SQL 语句执行的很慢的原因。

不过呢，我们有时候也可以通过强制走索引的方式来查询，例如:

select * from t force index(a) where c < 100 and c < 100000;

我们也可以通过:

show index from t;

来查询索引的基数和实际是否符合，如果和实际很不符合的话，我们可以重新来统计索引的基数，可以用这条命令:

analyze table t;

来重新统计分析。

既然会预测错索引的基数，这也意味着，当我们的查询语句有多个索引的时候，系统有可能也会选错索引，这也可能是 SQL 执行的很慢的一个原因。

​

面试官： MySQL中 SQL 查询和更新语句的执行流程？

候选人：

一、 MySQL 基础架构分析

下图是 MySQL 的一个简要架构图，从下图你可以很清晰的看到用户的 SQL 语句在 MySQL 内部是如何执行的。

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。

• 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做⼀次缓存操作，失效后还要销毁。）

• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。

• 优化器： 按照 MySQL 认为最优的方案去执行。

• 执行器： 执行具体的SQL语句之前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

简单来说 MySQL 主要分为 Server 层和存储引擎层：

• Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块。
• 存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自带的redo log日志模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5 版本开始就被当做默认存储引擎了。

二、语句分析

1. 查询语句（SELECT）

   我们先分析下查询语句，语句如下：

   select * from tb_student  A where A.age='18' and A.name=' 张三 ';
   

   结合上面的说明，我们分析下这个语句的执行流程：

   • 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，如果有直接返回，如果没有，执行下一步。
   • 通过分析器进行词法分析，提取 SQL 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student，需要查询所有的列，查询条件是这个表的 id=‘1’。然后判断这个 SQL 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。
   • 接下来就是优化器进行确定执行方案，上面的 SQL 语句，可以有两种执行方案：a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。
   • 进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用存储引擎接口，返回引擎的执行结果。

2. 更新语句（UPDATE、INSERT、DELETE）

   接下来我们看看一条更新语句如何执行的呢？SQL 语句如下：

   update tb_student A set A.age='19' where A.name=' 张三 ';
   

   其实这条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志，这就会引入日志模块了，MySQL 自带的日志模块是 binlog（归档日志） ，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志），我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

   • 先查询到张三这一条数据，不会走查询缓存，因为更新语句会导致与该表相关的查询缓存失效。
   • 然后拿到查询的语句，把 age 改为 19，然后调用存储引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare（预提交） 状态，然后告诉执行器，执行完成了，随时可以提交。
   • 执行器收到通知后记录 binlog，然后调用存储引擎接口，提交 redo log 为提交状态。
   • 更新完成。

   这里肯定有同学会问，为什么要用两个日志模块，用一个日志模块不行吗?

   并不是说只用一个日志模块不可以，只是 InnoDB 引擎就是通过 redo log 来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行，为什么 redo log 要引入 prepare 预提交状态？这里我们用反证法来说明下为什么要这么做？

   • 先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
   • 先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于redo log没有记录，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

   如果采用 redo log 两阶段提交的方式就不一样了，写完 binlog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。

   那么问题来了，有没有一个极端的情况呢？假设 redo log 处于预提交状态，binlog 也已经写完了，这个时候发生了异常重启会怎么样呢？这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

   • 判断 redo log 是否完整，如果判断是完整的，就立即提交。
   • 如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。

   这样就解决了数据一致性的问题。

面试官： 了解过索引吗？（什么是索引）

候选人： 嗯，索引是帮助MySQL高效获取数据的数据结构，主要是用来提高数据检索的效率，降低数据库的IO成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低了CPU的消耗。

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

候选人：MySQL的默认的存储引擎InnoDB采用的B+树的数据结构来存储索引，选择B+树的主要的原因是：

• 第一阶数更多，路径更短，这减少了树的高度，从而缩短了从根到叶子节点的路径长度，从而提高了检索效率。

• 第二个磁盘读写代价B+树更低，只有叶子节点存储实际的数据记录，而所有非叶子节点保存索引键值和指针信息。

• 第三是B+树便于扫库和区间查询，所有的叶子节点都通过双向链表连接在一起。

面试官： B树和B+树的区别是什么呢？

候选人：

第一：在B树中，非叶子节点和叶子节点都会存放数据，而B+树的所有的数据都会出现在叶子节点，在查询的时候，B+树查找效率更加稳定。

第二：在进行范围查询的时候，B+树效率更高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链表。

面试官： 什么是聚簇索引？什么是非聚簇索引（辅助索引） ?

候选人：

• 聚簇索引主要是指数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个，这意味着表中的数据行按照索引键的顺序存放，通常这个索引是主键索引。

• 非聚簇索引（辅助索引）主要是数据与索引分开存储，B+树的叶子节点保存对应的主键值，可以有多个，当用户定义额外的索引时（如唯一索引、普通索引等），这些索引就是非聚簇索引。

面试官： 知道什么是回表查询嘛 ?

候选人： 嗯，其实跟刚才介绍的聚簇索引和非聚簇索引是有关系的，回表的意思就是通过辅助索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表。

【备注：如果面试官直接问回表，则需要先介绍聚簇索引和非聚簇索引】

面试官： MySQL的主键索引为什么用自增ID，不使用UUID？

候选人：

一般情况下，MySQL推荐使用自增ID，因为在MySQL的InnoDB存储引擎中，主键索引就是聚簇索引，主键索引的B+树的叶子结点按顺序存储主键值和数据，如果主键索引是自增ID，只需按顺序往后排列即可；如果是UUID，ID是随机生成的，在数据插入时会造成大量的数据移动，产生大量碎片。

面试官： 知道什么叫覆盖索引嘛 ?

候选人：

如果一个索引包含了查询语句中所需要的所有列（索引全部命中了），那么这个索引就是一个覆盖索引。如果我们使用id查询，它会直接走覆盖索引查询，一次索引扫描，直接返回数据，性能高。

如果按照辅助索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *，尽量在返回的列中都包含添加索引的字段。

面试官： MySQL超大分页怎么处理 ?

候选人： 超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低。例如我们执行limit 900000，10，此时MySQL需排序前900010条记录，仅仅返回900000-900010的记录，其他记录要丢弃，这时查询排序的代价就非常大了。

不过我们可以采用覆盖索引和子查询来解决。先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了。因为查询id的时候，走的覆盖索引，所以效率可以提升很多。

面试官： 索引创建原则有哪些？

候选人： 这个情况有很多，不过都有一个大前提，就是表中的数据要超过10万以上，我们才会创建索引，并且添加索引的字段是查询比较频繁的字段，一般也是像作为查询条件，排序字段或分组的字段这些。

还有就是，我们通常创建索引的时候都是使用复合索引来创建，一条sql的返回值，尽量使用覆盖索引，如果字段的区分度不高的话，我们也会把它放在组合索引后面的字段。

如果某一个字段的内容较长，我们会考虑使用前缀索引来使用，当然并不是所有的字段都要添加索引，这个索引的数量也要控制，因为添加索引也会导致新增改的速度变慢，维护成本大。

面试官： 什么情况下索引会失效 ?

候选人： 嗯，这个情况比较多，我说一些自己的经验，以前遇到过的，比如：

• 索引在使用的时候没有遵循最左匹配法则。

  假设有一个复合索引 idx(a, b, c)，其中 a 是最左边的列。

  查询语句如下：

  SELECT * FROM table WHERE c = 'value';
  

  在这种情况下，虽然有复合索引 idx(a, b, c)，但是因为查询条件没有从索引的最左侧开始，所以无法利用这个索引。

• 模糊查询，如果%号在前面也会导致索引失效。

  假设有一个索引 idx(column_a)。

  查询语句如下：

  SELECT * FROM table WHERE column_a LIKE '%abc';
  

  这里 % 放在了模式字符串的开头，意味着匹配任意数量的字符后跟着 abc。由于 % 放在了开头，数据库无法利用索引来加速查询，因为任何字符都可能匹配，所以只能进行全面扫描。

• 如果在添加索引的字段上进行了运算操作或者类型转换也都会导致索引失效。

  假设有一个索引 idx(column_b)。

  查询语句如下：

  SELECT * FROM table WHERE column_b + 1 = 5;
  

  这里对 column_b 进行了加法运算，导致索引 idx(column_b) 无法被使用。同样，如果对 column_b 进行类型转换，例如 WHERE CAST(column_b AS CHAR) = '5'，也会导致索引失效。

• 如果使用了复合索引，中间使用了范围查询，右边的条件索引也会失效

  假设有一个复合索引 idx(a, b, c)。

  查询语句如下：

  SELECT * FROM table WHERE a = 1 AND b BETWEEN 10 AND 20 AND c = 'value';
  

  在这个查询中，虽然 a 和 c 的条件可以使用索引，但是由于 b 使用了范围查询 BETWEEN，导致 c 的条件无法使用索引加速查询。因此，尽管 a 和 b 的条件仍然可以使用部分索引，但是 c 的索引效果将被忽略。

通常情况下，想要判断出这条sql是否有索引失效的情况，可以使用explain执行计划来分析。

面试官： sql的优化的经验

候选人： 嗯，这个在项目还是挺常见的，当然如果直说sql优化的话，我们会从这几方面考虑，比如

建表的时候、使用索引、sql语句的编写、主从复制，读写分离，还有一个是如果量比较大的话，可以考虑分库分表。

面试官： 创建表的时候，你们是如何优化的呢？

候选人：

• 对于数值型字段，我们会根据预期的值域大小选择适当的类型，如 TINYINT、SMALLINT、INT 或 BIGINT。这样既可以节省存储空间，也能提高查询效率。
• 对于字符型字段，我们会在 CHAR 和 VARCHAR 之间作出区分。CHAR 适合固定长度的字符串，而 VARCHAR 更适用于长度可变的文本。对于非常大的文本字段，我们会考虑使用 TEXT 类型及其变体。

面试官： 那在使用索引的时候，是如何优化呢？

候选人：【参考索引创建原则 进行描述】

面试官： 你平时对sql语句做了哪些优化呢？

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

补充：union all 和 union

UNION ALL：它会返回查询结果的所有行，包括重复的行。因此，它的执行速度通常更快，因为它不需要进行去重处理。

UNION：它会去除查询结果中的重复行，然后返回最终结果。这意味着 UNION 在返回结果之前会进行去重处理，这一步骤可能会消耗更多的计算资源，因此执行速度较慢。

补充：以小表为驱动怎么理解？

数据库在执行join操作时，通常会对驱动表进行扫描，并为驱动表中的每一行查找另一表中的匹配行。如果驱动表很大，那么这种操作可能会非常耗时。

面试官： 事务的特性是什么？可以详细说一下吗？

候选人： ACID，分别指的是：原子性、一致性、隔离性、持久性；我举个例子：

A向B转账500，转账成功，A扣除500元，B增加500元，原子操作体现在要么都成功，要么都失败。

在转账的过程中，数据要一致，A扣除了500，B必须增加500。

在转账的过程中，隔离性体现在A向B转账，不能受其他事务干扰。

在转账的过程中，持久性体现在事务提交后，要把数据持久化（可以说是落盘操作）。

面试官：并发事务带来哪些问题？

候选人：

我们在项目开发中，多个事务并发进行是经常发生的，并发也是必然的，有可能导致一些问题：

1. 第一是脏读：当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。（一个事务读到另一个事务还未提交的数据）

2. 第二是不可重复读：比如在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。（一个事务先后读取同一条记录，但两次读取的数据不同）

3. 第三是幻读（Phantom read）：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。（一个事务按照条件查询时，没有对应的数据行，再次查询时发现多了一行数据）

补充：不可重复读和幻读的区别

不可重复读的重点是修改；幻读的重点是新增或者删除。

面试官：怎么解决这些问题呢？MySQL的默认隔离级别是？

候选人：解决方案是对事务进行隔离。

MySQL支持四种隔离级别，分别有：

第一个是未提交读（read uncommitted） 它解决不了刚才提出的所有问题，一般项目中也不用这个。

第二个是读已提交（read committed） 它能解决脏读的问题的，但是解决不了不可重复读和幻读。

第三个是可重复读（repeatable read） 它能解决脏读和不可重复读，但是解决不了幻读，这个也是mysql默认的隔离级别。

第四个是串行化（serializable） 它可以解决刚才提出来的所有问题，但是由于让是事务串行执行的，性能比较低。

所以，我们一般使用的都是MySQL默认的隔离级别为可重复读。

面试官：undo log和redo log的区别

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

redo log保证了事务的持久性，undo log保证了事务的原子性和一致性。

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

候选人：事务的隔离性是由锁和MVCC实现的。

其中MVCC的意思是多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，它的底层实现主要是分为了三个部分，第一个是隐藏字段，第二个是undo log日志，第三个是readView读视图。

隐藏字段是指：在mysql中给每个表都设置了隐藏字段，有一个是trx_id(事务id)，记录每一次操作的事务id，是自增的；另一个字段是roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址。

undo log主要的作用是记录回滚日志，存储老版本数据，在内部会形成一个版本链，在多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表。

readView解决的是一个事务查询选择版本的问题，在内部定义了一些匹配规则和当前的一些事务id判断该访问那个版本的数据，不同的隔离级别快照读是不一样的，最终的访问的结果不一样。如果是rc隔离级别，每一次执行快照读时生成readView，如果是rr隔离级别仅在事务中第一次执行快照读时生成ReadView，后续复用。

面试官： 说下三种缓存读写策略（熟练使用缓存）？

候选人：

Cache Aside Pattern（旁路缓存模式）

Cache Aside Pattern 是我们平时使用比较多的一个缓存读写模式，比较适合读请求比较多的场景。

Cache Aside Pattern 中服务端需要同时维系 DB 和 cache，并且是以 DB 的结果为准。

写 ：

• 先更新 DB
• 然后直接删除 cache 。

读 :

• 从 cache 中读取数据，读取到就直接返回
• cache 中读取不到的话，就从 DB 中读取数据返回
• 再把数据放到 cache 中。

“在写数据的过程中，可以先删除 cache ，后更新 DB 么？”

答案： 那肯定是不行的！因为这样可能会造成数据库（DB）和缓存（Cache）数据不一致的问题。为什么呢？比如说现在有两个请求，请求一和请求二。请求一写入数据先删除cache，然后去更新DB；请求二去读取数据时发现没有在cache中找到（因为被请求一删掉了），这个时候请求二就会读取DB中的数据。恰好这时，**请求一正在更新DB的过程中，请求二读取到了旧数据，并且返回旧数据到cache当中。**这样就导致了DB（新）和cache（旧）不一致的问题。

“在写数据的过程中，先更新 DB，后删除 cache 就没有问题了么？”

答案：理论上来说还是可能会出现数据不一致性的问题，不过概率非常小，因为缓存的写入速度是比数据库的写入速度快很多！在并发场景下，如果请求1更新数据库后未能成功删除缓存中的数据A，而请求2在此期间尝试读取数据A，可能会发生以下情况：

• 请求1更新数据库成功，但删除缓存失败。
• 请求2尝试读取数据A，发现缓存中有旧数据A。
• 请求2返回旧数据A，而不是最新的数据A。

现在我们再来分析一下 Cache Aside Pattern 的缺陷。

缺陷 1：首次请求数据一定不存在 cache 的问题

解决办法：可以将热点数据可以提前放入 cache 中。

缺陷 2：写操作比较频繁的话导致 cache 中的数据会被频繁被删除，这样会影响缓存命中率 。

解决办法：

• 数据库和缓存数据强一致场景 ：更新 DB 的时候同样更新 cache，不过我们需要加一个锁/分布式锁来保证更新 cache 的时候不存在线程安全问题。
• 可以短暂地允许数据库和缓存数据不一致的场景 ：更新 DB 的时候同样更新 cache，但是给缓存加一个比较短的过期时间，这样的话就可以保证即使数据不一致的话影响也比较小。

Read/Write Through Pattern（读写穿透）

Read/Write Through Pattern 中服务端把 cache 视为主要数据存储，从中读取数据并将数据写入其中。cache 服务负责将此数据读取和写入 DB，从而减轻了应用程序的职责。

这种缓存读写策略小伙伴们应该也发现了在平时在开发过程中非常少见。抛去性能方面的影响，大概率是因为我们经常使用的分布式缓存 Redis 并没有提供 cache 将数据写入 DB 的功能。

Read-Through Pattern 实际只是在 Cache-Aside Pattern 之上进行了封装。在 Cache-Aside Pattern 下，发生读请求的时候，如果 cache 中不存在对应的数据，是由客户端自己负责把数据写入 cache，而 Read Through Pattern 则是 cache 服务自己来写入缓存的，这对客户端是透明的。

和 Cache Aside Pattern 一样， Read-Through Pattern 也有首次请求数据一定不存在 cache 的问题，对于热点数据可以提前放入缓存中。

Write Behind Pattern（异步缓存写入）

Write Behind Pattern 和 Read/Write Through Pattern 很相似，两者都是由 cache 服务来负责 cache 和 DB 的读写。

但是，两个又有很大的不同：Read/Write Through 是同步更新 cache 和 DB，而 Write Behind Caching 则是只更新缓存，不直接更新 DB，而是改为异步批量的方式来更新 DB。

很明显，这种方式对数据一致性带来了更大的挑战，比如 cache 数据可能还没异步更新 DB 的话，cache 服务可能就挂掉了。

这种策略在我们平时开发过程中也非常少见，但是不代表它的应用场景少，比如消息队列中消息的异步写入磁盘、MySQL 的 InnoDB Buffer Pool 机制都用到了这种策略。

Write Behind Pattern 下 DB 的写性能非常高，非常适合一些数据经常变化又对数据一致性要求没那么高的场景，比如浏览量、点赞量。

面试官： 如何保证缓存和数据库数据的一致性？

下面单独对 Cache Aside Pattern（旁路缓存模式） 来聊聊。

Cache Aside Pattern 中遇到写请求是这样的：更新 DB，然后直接删除 cache 。

如果更新数据库成功，而删除缓存这一步失败的情况的话，简单说两个解决方案：

1. 缓存失效时间变短（不推荐，治标不治本） ：我们让缓存数据的过期时间变短，这样的话缓存就会从数据库中加载数据。另外，这种解决办法对于先操作缓存后操作数据库的场景不适用。
2. 增加 cache 更新重试机制（常用）：如果 cache 服务当前不可用导致缓存删除失败的话，我们就隔一段时间进行重试，重试次数可以自己定。如果多次重试还是失败的话，我们可以把当前更新失败的 key 存入队列中，等缓存服务可用之后，再将缓存中对应的 key 删除即可。

面试官：MySQL主从同步原理

候选人：MySQL主从复制的核心就是二进制日志(DDL（数据定义语言）语句和 DML（数据操纵语言）语句)，它的步骤是这样的：

第一：主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。

第二：从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。

第三：从库重做中继日志中的事件，将改变反映它自己的数据。

思考：为什么不让从库直接读取主库的binlog文件呢？中继日志的作用是什么？

如果多个从库直接连接到主库并读取其binlog，这会增加主库的网络负载和磁盘IO压力。

如果从库失败，可通过中继日志快速恢复到失败前的状态，有助于加速从库的恢复过程。

由于中继日志是本地存储的，因此读取速度通常更快。

面试官： 谈谈你对读写分离和分库分表的理解

候选人:

读写分离

何为读写分离？

见名思意，根据读写分离的名字，我们就可以知道：读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。 这样的话，就能够小幅提升写性能，大幅提升读性能。

一般情况下，我们都会选择一主多从，也就是一台主数据库负责写，其他的从数据库负责读。主库和从库之间会进行数据同步，以保证从库中数据的准确性。

读写分离会带来什么问题？如何解决？

读写分离对于提升数据库的并发非常有效，但是，同时也会引来一个问题：主库和从库的数据存在延迟，比如你写完主库之后，主库的数据同步到从库是需要时间的，这个时间差就导致了主库和从库的数据不一致性问题。这也就是我们经常说的 主从同步延迟 。

解决方案：

1.强制将读请求路由到主库处理。

既然你从库的数据过期了，那我就直接从主库读取嘛！这种方案虽然会增加主库的压力，但是，实现起来比较简单，也是我了解到的使用最多的一种方式。

2.延迟读取。

还有一些朋友肯定会想既然主从同步存在延迟，那我就在延迟之后读取啊，比如主从同步延迟 0.5s,那我就 1s 之后再读取数据。这样多方便啊！方便是方便，但是也很扯淡。

不过，如果你是这样设计业务流程就会好很多：对于一些对数据比较敏感的场景，你可以在完成写请求之后，避免立即进行请求操作。比如你支付成功之后，跳转到一个支付成功的页面，当你点击返回之后才返回自己的账户。

如何实现读写分离？

不论是使用哪一种读写分离具体的实现方案，想要实现读写分离一般包含如下几步：

1. 部署多台数据库，选择一种的一台作为主数据库，其他的一台或者多台作为从数据库。
2. 保证主数据库和从数据库之间的数据是实时同步的。
3. 系统将写请求交给主数据库处理，读请求交给从数据库处理。

落实到项目本身的话，常用的方式有两种：

我们可以在应用和数据中间加了一个代理层。应用程序所有的数据请求都交给代理层处理，代理层负责分离读写请求，将它们路由到对应的数据库中。

提供类似功能的中间件MyCat。

分库分表

读写分离主要应对的是数据库读并发，没有解决数据库存储问题。试想一下：如果 MySQL 一张表的数据量过大怎么办?

换言之，我们该如何解决 MySQL 的存储压力呢？

答案之一就是 分库分表。

什么情况下需要分库分表？

遇到下面几种场景可以考虑分库分表：

• 单表的数据达到千万级别以上，数据库读写速度比较缓慢（分表）。
• 数据库中的数据占用的空间越来越大，备份时间越来越长（分库）。
• 应用的并发量太大（分库）。

分库分表会带来什么问题呢？

引入分库分表之后，会给系统带来什么挑战呢？

• join 操作 ：同一个数据库中的表分布在了不同的数据库中，导致无法使用 join 操作。这样就导致我们需要手动进行数据的封装，比如你在一个数据库中查询到一个数据之后，再根据这个数据去另外一个数据库中找对应的数据。
• 事务问题 ：同一个数据库中的表分布在不同的数据库中，如果单个操作涉及到多个数据库，那么数据库自带的事务就无法满足我们的要求了。
• 分布式 id ：分库之后， 数据遍布在不同服务器上的数据库，数据库的自增主键已经没办法满足生成的主键唯一了。我们如何为不同的数据节点生成全局唯一主键呢？这个时候，我们就需要为我们的系统引入分布式 id 了。

推荐阅读：揭秘！MySQL索引背后的秘密武器：B+树为何力压跳表，独领风骚？