大厂面试超高频MySQL题目（含答案）：基础+索引+事务+锁

MySQL基础篇

公众号：Java架构师联盟，每日更新技术好文

说一下 MySQL 执行一条查询语句的内部执行过程？

• 客户端先通过连接器连接到 MySQL 服务器。
• 连接器权限验证通过之后，先查询是否有查询缓存，如果有缓存（之前执行过此语句）则直接返回缓存数据，如果没有缓存则进入分析器。
• 分析器会对查询语句进行语法分析和词法分析，判断 SQL 语法是否正确，如果查询语法错误会直接返回给客户端错误信息，如果语法正确则进入优化器。
• 优化器是对查询语句进行优化处理，例如一个表里面有多个索引，优化器会判别哪个索引性能更好。
• 优化器执行完就进入执行器，执行器就开始执行语句进行查询比对了，直到查询到满足条件的所有数据，然后进行返回。

MySQL 提示“不存在此列”是执行到哪个节点报出的？

此错误是执行到分析器阶段报出的，因为 MySQL 会在分析器阶段检查 SQL 语句的正确性。

MySQL 查询缓存的功能有何优缺点？

MySQL 查询缓存功能是在连接器之后发生的，它的优点是效率高，如果已经有缓存则会直接返回结果。 查询缓存的缺点是失效太频繁导致缓存命中率比较低，任何更新表操作都会清空查询缓存，因此导致查询缓存非常容易失效。

如何关闭 MySQL 的查询缓存功能？

MySQL 查询缓存默认是开启的，配置 querycachetype 参数为 DEMAND（按需使用）关闭查询缓存，MySQL 8.0 之后直接删除了查询缓存的功能。

MySQL 的常用引擎都有哪些？

MySQL 的常用引擎有 InnoDB、MyISAM、Memory 等，从 MySQL 5.5.5 版本开始 InnoDB 就成为了默认的存储引擎。

MySQL 可以针对表级别设置数据库引擎吗？怎么设置？

可以针对不同的表设置不同的引擎。在 create table 语句中使用 engine=引擎名（比如Memory）来设置此表的存储引擎。完整代码如下：

create table student(
 id int primary key auto_increment,
 username varchar(120),
 age int
) ENGINE=Memory

常用的存储引擎 InnoDB 和 MyISAM 有什么区别？

InnoDB 和 MyISAM 最大的区别是 InnoDB 支持事务，而 MyISAM 不支持事务，它们主要区别如下：

• InnoDB 支持崩溃后安全恢复，MyISAM 不支持崩溃后安全恢复；
• InnoDB 支持行级锁，MyISAM 不支持行级锁，只支持到表锁；
• InnoDB 支持外键，MyISAM 不支持外键；
• MyISAM 性能比 InnoDB 高；
• MyISAM 支持 FULLTEXT 类型的全文索引，InnoDB 不支持 FULLTEXT 类型的全文索引，但是 InnoDB 可以使用 sphinx 插件支持全文索引，并且效果更好；
• InnoDB 主键查询性能高于 MyISAM。

InnoDB 有哪些特性？

1）插入缓冲(insert buffer)：对于非聚集索引的插入和更新，不是每一次直接插入索引页中，而是首先判断插入的非聚集索引页是否在缓冲池中，如果在，则直接插入，否则，先放入一个插入缓冲区中。好似欺骗数据库这个非聚集的索引已经插入到叶子节点了，然后再以一定的频率执行插入缓冲和非聚集索引页子节点的合并操作，这时通常能将多个插入合并到一个操作中，这就大大提高了对非聚集索引执行插入和修改操作的性能。

2）两次写(double write)：两次写给 InnoDB 带来的是可靠性，主要用来解决部分写失败(partial page write)。doublewrite 有两部分组成，一部分是内存中的 doublewrite buffer ，大小为 2M，另外一部分就是物理磁盘上的共享表空间中连续的 128 个页，即两个区，大小同样为 2M。当缓冲池的作业刷新时，并不直接写硬盘，而是通过 memcpy 函数将脏页先拷贝到内存中的 doublewrite buffer，之后通过 doublewrite buffer 再分两次写，每次写入 1M 到共享表空间的物理磁盘上，然后马上调用 fsync 函数，同步磁盘。如下图所示

3）自适应哈希索引(adaptive hash index)：由于 InnoDB 不支持 hash 索引，但在某些情况下 hash 索引的效率很高，于是出现了 adaptive hash index 功能， InnoDB 存储引擎会监控对表上索引的查找，如果观察到建立 hash 索引可以提高性能的时候，则自动建立 hash 索引。

一张自增表中有三条数据，删除了两条数据之后重启数据库，再新增一条数据，此时这条数据的 ID 是几？

如果这张表的引擎是 MyISAM，那么 ID=4，如果是 InnoDB 那么 ID=2（MySQL 8 之前的版本）。

MySQL 中什么情况会导致自增主键不能连续？

以下情况会导致 MySQL 自增主键不能连续：

• 唯一主键冲突会导致自增主键不连续；
• 事务回滚也会导致自增主键不连续。

InnoDB 中自增主键能不能被持久化？

自增主键能不能被持久化，说的是 MySQL 重启之后 InnoDB 能不能恢复重启之前的自增列，InnoDB 在 8.0 之前是没有持久化能力的，但 MySQL 8.0 之后就把自增主键保存到 redo log（一种日志类型，下文会详细讲）中，当 MySQL 重启之后就会从 redo log 日志中恢复。

什么是独立表空间和共享表空间？它们的区别是什么？

共享表空间：指的是数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在 data 目录下。 独立表空间：每一个表都将会生成以独立的文件方式来进行存储。 共享表空间和独立表空间最大的区别是如果把表放再共享表空间，即使表删除了空间也不会删除，所以表依然很大，而独立表空间如果删除表就会清除空间。

如何设置独立表空间？

独立表空间是由参数 innodbfileper_table 控制的，把它设置成 ON 就是独立表空间了，从 MySQL 5.6.6 版本之后，这个值就默认是 ON 了。

如何进行表空间收缩？

使用重建表的方式可以收缩表空间，重建表有以下三种方式：

• alter table t engine=InnoDB
• optmize table t
• truncate table t

说一下重建表的执行流程？

• 建立一个临时文件，扫描表 t 主键的所有数据页；
• 用数据页中表 t 的记录生成 B+ 树，存储到临时文件中；
• 生成临时文件的过程中，将所有对 t 的操作记录在一个日志文件（row log）中；
• 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 t相同的数据文件；
• 用临时文件替换表 t 的数据文件。

表的结构信息存在哪里？

表结构定义占有的存储空间比较小，在 MySQL 8 之前，表结构的定义信息存在以 .frm 为后缀的文件里，在 MySQL 8 之后，则允许把表结构的定义信息存在系统数据表之中。

什么是覆盖索引？

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键上去取数据。

如果把一个 InnoDB 表的主键删掉，是不是就没有主键，就没办法进行回表查询了？

可以回表查询，如果把主键删掉了，那么 InnoDB 会自己生成一个长度为 6 字节的 rowid 作为主键。

执行一个 update 语句以后，我再去执行 hexdump 命令直接查看 ibd 文件内容，为什么没有看到数据有改变呢？

可能是因为 update 语句执行完成后，InnoDB 只保证写完了 redo log、内存，可能还没来得及将数据写到磁盘。

内存表和临时表有什么区别？

• 内存表，指的是使用 Memory 引擎的表，建表语法是 create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。除了这两个特性看上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表。
• 而临时表，可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，写数据的时候是写到磁盘上的。

并发事务会带来哪些问题？

• 脏读
• 修改丢失
• 不可重复读
• 幻读

什么是脏读和幻读？

脏读是一个事务在处理过程中读取了另外一个事务未提交的数据；幻读是指同一个事务内多次查询返回的结果集不一样（比如增加了或者减少了行记录）。

为什么会出现幻读？幻读会带来什么问题？

因为行锁只能锁定存在的行，针对新插入的操作没有限定，所以就有可能产生幻读。 幻读带来的问题如下：

• 对行锁语义的破坏；
• 破坏了数据一致性。

如何避免幻读？

使用间隙锁的方式来避免出现幻读。间隙锁，是专门用于解决幻读这种问题的锁，它锁的了行与行之间的间隙，能够阻塞新插入的操作 间隙锁的引入也带来了一些新的问题，比如：降低并发度，可能导致死锁。

如何查看 MySQL 的空闲连接？

在 MySQL 的命令行中使用 show processlist; 查看所有连接，其中 Command 列显示为 Sleep 的表示空闲连接，如下图所示：

MySQL 中的字符串类型都有哪些？

MySQL 的字符串类型和取值如下：

类型取值范围CHAR(N)0_{255VARCHAR(N)0}65536TINYBLOB0_255BLOB065535MEDUIMBLOB0_{167772150LONGBLOB0}4294967295TINYTEXT0_255TEXT065535MEDIUMTEXT0_{167772150LONGTEXT0}4294967295VARBINARY(N)0_{N个字节的变长字节字符集BINARY(N)0}N个字节的定长字节字符集

VARCHAR 和 CHAR 的区别是什么？分别适用的场景有哪些？

VARCHAR 和 CHAR 最大区别就是，VARCHAR 的长度是可变的，而 CHAR 是固定长度，CHAR 的取值范围为1-255，因此 VARCHAR 可能会造成存储碎片。由于它们的特性决定了 CHAR 比较适合长度较短的字段和固定长度的字段，如身份证号、手机号等，反之则适合使用 VARCHAR。

MySQL 存储金额应该使用哪种数据类型？为什么？