MySQL 高频面试题合集详解

❤ 作者主页：欢迎来到我的技术博客😎
❀ 个人介绍：大家好，本人热衷于Java后端开发，欢迎来交流学习哦！(￣▽￣)~*
🍊 如果文章对您有帮助，记得关注、点赞、收藏、评论⭐️⭐️⭐️
📣 您的支持将是我创作的动力，让我们一起加油进步吧！！！🎉🎉

1、什么是关系型数据库？

关系型数据库就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。

关系型数据库中，我们的数据都被存放在了各种表中（比如用户表），表中的每一行就存放着一条数据（比如一个用户的信息）。

大部分关系型数据库都使用 SQL 来操作数据库中的数据。并且，大部分关系型数据库都支持事务的四大特性(ACID)。

---

2、什么是 SQL？

SQL 是一种 结构化查询语言 (Structured Query Language)，专门用来与数据库打交道，目的是提供一种从数据库中读写数据的简单有效的方法。

几乎所有的主流关系数据库都支持 SQL ，适用性非常强。并且，一些非关系型数据库也兼容 SQL 或者使用的是类似于 SQL 的查询语言。

---

3、请说下你对 MySQL 架构的了解？

从上图可以看出， MySQL 主要由下面几部分构成：

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
• 查询缓存： 执行查询语句的时候，会先查询缓存。
• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
• 优化器： 按照 MySQL 认为最优的方案去执行。
• 执行器： 执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
• 插件式- 存储引擎 ： 主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。

---

4、一条 SQL 语句在数据库框架中的执行流程？

1. 应用程序把查询 SQL 语句发送给服务器端执行；
2. 查询缓存，如果查询缓存是打开的，服务器在接收到查询请求后，并不会直接去数据库查询，而是在数据库的查询缓存中找是否有相对应的查询数据，如果存在，则直接返回给客户端。只有缓存不存在时，才会进行下面的操作；
3. 查询优化处理，生成执行计划。这个阶段主要包括解析SQL、预处理、优化SQL执行计划；
4. MySQL 根据相应的执行计划完成整个查询；
5. 将查询结果返回客户端。

---

5、数据库的三范式是什么？

1. 第一范式： 强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项；
2. 第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在依赖主关键字一部分的属性；
3. 第三范式：任何非属性不依赖于其它非主属性。

---

6、char 和 varchar 的区别？

char(n): 固定长度类型，比如：char(10)，当你输入”abc”三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。char 优点：效率高；缺点：占用空间；适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。

varchar(n): 可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。

所以，从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

---

7、varchar(10) 和 varchar(20) 的区别？

varchar(10) 中 10 的含义最多存放 10 个字符，varchar(10) 和 varchar(20) 存储 hello 所占空间一样，但后者在 排序 时会消耗更多内存，因为 order by col 采用 固定长度 计算 col 长度。

---

8、说一说你对MySQL索引的理解？

索引是一个单独的、存储在磁盘上的数据库结构，用来提高数据的查询效率。

同样索引也会带来很多负面影响：创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

建立索引的原则：

1. 在最频繁使用的、用以缩小查询范围的字段上建立索引；
2. 在频繁使用的、需要排序的字段上建立索引。

不适合建立索引的情况：

1. 对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引；
2. 对于一些特殊的数据结构，不宜建立索引，比如：文本字段（text）等。

---

8、索引的底层使用的是什么数据结构？

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有 Hash 索引、B+树索引等。而我们经常使用的 InnoDB 存储引擎的默认索引实现为 B+ 树索引。

---

9、谈谈你对 B+ 树的理解？

1. B+ 树是基于B树和叶子节点顺序访问指针进行实现，它具有 B 树的平衡性，并且通过顺序访问指针来提高区间查询的性能；
2. 在 B+ 树中，一个节点中的 key 从左到右是递增的排序；
3. 进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data。
4. 插入、删除操作会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。

---

10、为什么 InnoDB 存储引擎选用 B+ 树而不是 B 树呢？

用 B+ 树不用 B 树考虑的是 IO 对性能的影响，B 树的每个节点都存储数据，而 B+ 树只有叶子节点才存储数据，所以查找相同数据量的情况下，B 树的高度更高，IO 更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。

---

11、谈谈对聚集索引和二级索引的理解？

聚集索引：

• 将数据存储和索引放到了一块，索引结构的叶子节点保存了 行数据；
• 一般情况下主键会默认创建聚集索引，且一张表只允许存在一个聚集索引；

二级索引：

• 将数据和索引分开存储，索引结构的叶子节点关联的是对应的 主键；
• 需要访问数据时，先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值获取数据，也就是需要进行 回表查询；

---

12、谈谈你对哈希索引的理解？

哈希索引能以 O(1) 时间进行查找，但是失去了有序性。无法用于排序与分组、只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫 “自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+ 树索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如：快速的哈希查找。

---

13、谈谈你对覆盖索引的认识？

覆盖索引 就是SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引之后再去查询数据。

具有以下的优点：

1. 索引通常小于数据行的大小，只读取索引能大大减少数据访问量。
2. 一些存储引擎在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
3. 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

---

14、索引的分类？

• 从数据结构角度

  1. 树索引
  2. Hash索引
      

• 从物理存储的角度

  1. 聚集索引
  2. 二级索引（辅助索引）
      

• 从逻辑角度

  1. 主键索引
  2. 唯一索引
  3. 常规索引
  4. 全文索引

---

15、谈谈你对最左前缀法则的理解？

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

注意 ： 最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。

---

16、怎么知道创建的索引有没有被使用到？

使用 Explain 命令来查看语句的执行计划，MySQL 在执行某个语句之前，会将该语句过一遍查询优化器，之后会拿到对语句的分析，也就是执行计划，其中包含了许多信息。可以通过其中和索引有关的信息来分析是否命中了索引，例如：possilbe_key、key、key_len 等字段，分别说明了此语句可能会使用的索引、实际使用的索引以及使用的索引长度。

---

17、什么情况下索引会失效？

1. 索引列参与表达式计算

   SELECT 'sname' FROM 'stu' WHERE 'age' + 10 = 30;
   

2. 函数运算

   SELECT 'sname' FROM 'stu' WHERE LEFT('date',4) < 1990;
   

3. 模糊查询：如果仅仅是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效。

   SELECT * FROM 'manong' WHERE `uname` LIKE '码农%' -- 走索引 
   
   SELECT * FROM 'manong' WHERE `uname` LIKE '%码农%' -- 不走索引 
   

4. 字符串不加引号

   select * from tb_user where profession = '软件工程' and age = 31 and status = '0'; --走索引
   
   select * from tb_user where profession = '软件工程' and age = 31 and status = 0; --不走索引
   

5. 查询条件中有 or ，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引。

   select * from dept where dname='xxx' or loc='xx' or deptno = 45;
   

6. 正则表达式不使用索引。

7. MySQL 估计使用全表扫描要比使用索引快，则不使用索引。

---

18、查询性能的优化方法？

减少请求的数据量

1. 只返回必要的列：最好不要使用 SELECT * 语句；
2. 只返回必要的行：使用 LIMIT 语句来限制返回的数据；
3. 缓存重复查询的数据： 使用缓存可以避免在数据库中进行查询。

减少服务器端扫描的行数

1. 最有效的方式是使用索引来覆盖查询。

---

19、InnoDB 和 MyISAM 的比较？

1. 事务：MyISAM不支持事务，InnoDB支持事务；
2. 全文索引：MyISAM 支持全文索引，InnoDB 5.6 之前不支持全文索引；
3. 关于count()：MyISAM会直接存储总行数，InnoDB 则不会，需要按行扫描。意思就是对于 select count(）from 如果数据量大，MyISAM 会瞬间返回，而 InnoDB 则会一行行扫描；
4. 外键：MyISAM 不支持外键，InnoDB 支持外键；
5. 锁：MyISAM 只支持表锁，InnoDB 可以支持行锁。

---

20、谈谈你对水平切分和垂直切分的理解？

• 水平切分
  水平切分是将同一个表中的记录拆分到多个结构相同的表中。当一个表的数据不断增多时，水平切分是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

• 垂直切分
  垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。例如：将原来的电商数据库垂直切分成商品数据库、用户数据库等。

---

21、主从复制中涉及到哪三个线程？

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

1. binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
2. I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的重放日志（Relay log）中。
3. SQL 线程 ：负责读取重放日志并重放其中的 SQL 语句。

---

22、主从同步的延迟原因及解决办法？

主从同步延迟的原因
假如一个服务器开放 Ｎ 个连接给客户端，这样会有大并发的更新操作, 但是从服务器的里面读取 binlog 的线程仅有一个， 当某个 SQL 在从服务器上执行的时间稍长或者由于某个 SQL 要进行锁表就会导致主服务器的 SQL 大量积压，未被同步到从服务器里。这就导致了主从不一致， 也就是主从延迟。

主从同步延迟的解决方案
实际上主从同步延迟根本没有什么一招制敌的办法， 因为所有的 SQL 必须都要在从服务器里面执行一遍，但是主服务器如果不断的有更新操作源源不断的写入，那么一旦有延迟产生，那么延迟加重的可能性就会原来越大。当然我们可以做一些缓解的措施。

1. 我们知道因为主服务器要负责更新操作， 它对安全性的要求比从服务器高，所有有些设置可以修改，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而 slave 则不需要这么高的数据安全，完全可以将 sync_binlog 设置为 0 或者关闭 binlog、innodb_flushlog、innodb_flush_log_at_trx_commit 也 可以设置为 0 来提高 SQL 的执行效率。
2. 增加从服务器，这个目的还是分散读的压力，从而降低服务器负载。

---

23、谈谈对数据库读写分离的理解？

读写分离常用代理方式来实现，代理服务器接受应用层传来的读写请求，然后决定转发到哪个服务器。主服务器处理写操作以及实时性要求比较高的操作，而从服务器处理读操作。

读写分离能提高性能的原因在于：

1. 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
2. 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
3. 增加冗余，提高可用性。

---

24、描述下事务的特性？

1. 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2. 一致性： 执行事务前后，数据库从一个一致性状态转换到另一个一致性状态；
3. 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4. 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不会对其有任何影响。

---

25、解释下什么是脏读、不可重复读和幻读？

脏读： 表示一个事务能够读取另一个事务中还未提交的数据。比如：某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。

不可重复读： 是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
不可重复读的重点是修改： 同样的条件，你读取过的数据，再次读取出来发现值不一样了。

幻读： 指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。
幻读的重点在于新增或者删除： 同样的条件，第1次和第2次读出来的记录数不一样。

---

26、谈谈你对事务隔离级别的理解？

1. READ_UNCOMMITTED（未提交读）: 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读；
2. READ_COMMITTED（提交读）: 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生；
3. REPEATABLE_READ（可重复读）: 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生；
4. SERIALIZABLE（序列化）: 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。

---

27、MySQL 默认的隔离级别是什么？

MySQL默认采用的 REPEATABLE_READ（可重复读）隔离级别。
Oracle 默认采用的 READ_COMMITTED（提交读） 隔离级别。

---

28、谈谈你对MVCC的了解？

数据库并发场景：

1. 读-读：不存在任何问题，也不需要并发控制；
2. 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读、不可重复读、幻读；
3. 写-写：有线程安全问题，可能会存在更新丢失问题。

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。

MVCC的优点：

1. 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能；
2. 同时还可以解决脏读、不可重复读、幻读等事务隔离问题，但不能解决更新丢失问题。

---

29、说一下 MySQL 的行锁和表锁？

MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

表级锁： 开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。

行级锁：开销大，加锁慢，会出现死锁。锁粒度小，发生锁冲突的概率小，并发量最高。

---

30、MySQL 问题排查都有哪些手段？

1. 使用 show processlist 命令查看当前所有连接信息；
2. 使用 Explain 命令查询 SQL 语句执行计划；
3. 开启慢查询日志，查看慢查询的 SQL；

---

31、MySQL 数据库 CPU 飙升到 500% 的话他怎么处理？

当 CPU 飙升到 500% 时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。

如果是 mysqld 造成的，通过 show processlist 查看正在运行的线程，是不是有消耗资源的 SQL 在运行，找出其中消耗高的SQL，看看执行计划是否准确， index 是否缺失，或者是数据量太大造成。

然后 kill 掉这些线程（同时观察 CPU 使用率是否下降），等进行相应的调整（比如说加索引、改 SQL、改内存参数）之后，再重新跑这些 SQL。

若每个 SQL 消耗资源都不多，只是同一时间大量的 session 连进来导致 CPU 飙升，这种情况就需要分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

---

32、MySQL 添加索引的原则？

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

---

33、UNION 与 UNION ALL 的区别？

UNION 用于把来自多个 SELECT 语句的结果组合到一个结果集合中，MySQL 会把结果集中 重复的记录删掉，而使用 UNION ALL，MySQL 会把所有的记录返回，且效率高于 UNION 。

---

34、DROP、DELETE 与 TRUNCATE 的区别

三种都表示删除，具体的区别如下：

-	DROP	DELETE	TRUNCATE
SQL 语句类型	DDL	DML	DDL
回滚	不可回滚	可回滚	不可回滚
删除内容	从数据库中删除表，所有的数据行、索引和权限也会被删除	表结构还在，删除表的全部或者一部分数据行	表结构还在，删除表中的所有数据
删除速度	删除速度最快	删除速度慢，需要逐行删除	删除速度快

因此，在不再需要一张表的时候，采用 DROP；在想删除部分数据行时候，用 DELETE；在保留表而删除所有数据的时候用 TRUNCATE。

---

35、为什么要分库分表？

随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下性能瓶颈：

• IO瓶颈： 热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。请求数据太多，带宽不够，网络IO瓶颈。
• CPU瓶颈： 排序、分组、连接查询、聚合统计登SQL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈。

为了解决上述问题，我们需要对数据库进行分库分表处理。

---

36、分库分表存在哪些问题？

事务问题: 分库分表后，就成了分布式事务。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

跨库跨表的 JOIN 问题: 在执行了分库分表之后，难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上，这时，表的关联操作将受到限制，我们无法 JOIN 位于不同分库的表，也无法 JOIN 分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。

额外的数据管理负担和数据运算压力： 额外的数据管理负担，最为常见的是数据的 定位问题 和数据的 增删改查 的重复执行问题，这些都可以通过应用程序来解决，但必然会引起额外的逻辑运算。

---

 
创作不易，如果有收获！！！别忘记点个赞，让更多人看到！！！


关注博主不迷路，内容持续更新中！！！