秋招后端开发面试题 - MySQL基础

---

---

MySQL基础

前言

已经找到工作了，分享秋招时的笔记。祝大家都能顺利找到自己心仪的工作。

---

面试题

MySQL 基础篇

Mysql 的基础架构？

• MySQL 分为 Server 层和存储引擎两部分
• Server 层包括连接器、查询缓存、分析器、优化器、执行器等
  • 连接器：管理连接和权限验证
  • 查询缓存：命中直接返回结果
  • 分析器：词法分析、语法分析
  • 优化器：执行计划生成、索引选择
  • 执行器：操作引擎、返回结果
• 存储引擎负责存储数据和提供读写 IO 接口，其架构是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎

MySQL 的长连接和短连接

• 长连接：连接成功后，如果客户端持续有请求，则一直使用同一个连接
• 短连接：每次执行完很少的几次查询就断开连接，下次查询再重新建立

长连接引起的异常重启问题？

• 引起异常的原因：MySQL 在执行过程中使用的内存是在连接对象里的，这些资源在连接断开的时候才会释放
• 解决方法：
  • 定期断开长连接
  • 重新初始化连接资源

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

连接器 → 查询缓存 → 分析器 → 优化器 → 执行器

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

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

• 优点是效率高，如果已经有缓存则会直接返回结果
• 但是，如果数据库表中数据和结构发生变化时（增删改、表结构调整），查询缓存将会失效，导致缓存命中率比较低

MySQL 的常用引擎都有哪些？

• MySQL 的常用引擎有 InnoDB、MyISAM[maiˈzæm]、Memory（所有数据放在内存中） 等
• InnoDB 是默认的存储引擎

InnoDB vs MyISAM

• 最大的区别是 InnoDB 支持事务，而 MyISAM 不支持事务
• InnoDB 支持崩溃后安全恢复，MyISAM 不支持崩溃后安全恢复
• InnoDB 支持行锁，MyISAM 不支持行锁，只支持表锁
• InnoDB 支持外键，MyISAM 不支持外键

InnoDB 有哪些特性？

• 插入缓冲： 对于非聚集索引页，**不是直接，提高性能
• 两次写：要进行写操作时，不直接写硬盘，而是先拷贝到内存中的两次写缓冲区，然后把两次写缓冲区的内容分两次写到磁盘上
• 自适应哈希索引：为了提高性能会自动建立一些 hash 索引，查找效率很高，直接通过 hash 值定位到数据

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

• MyISAM 存储引擎：ID 为 4，因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里面，重启 MySQL 后不会丢失
• InnoDB 存储引擎
  • MySQL 8 以前: ID 为 2，因为 InnoDB 表把自增主键的最大 ID 记录到内存中，所以重启数据库后会使最大 ID 丢失
  • MySQL 8.0 ：ID 为 4，InnoDB 每次更改时，当前最大计数器值写入重做日志 redo log

自增主键能不能被持久化的问题？

• MyISAM：自增主键会持久化到数据文件中
• InnoDB：
  • 8 之前：不会持久化
  • 从 8 开始：自增主键会持久化到 redo log

为什么 InnoDB 不把总条数记录下来，查询的时候直接返回呢？

• 因为 InnoDB 支持事务
• 即使是在同一时间进行查询，得到的结果也可能不相同

InnoDB 和 MyISAM 执行 select count(*) from t，哪个效率更高？为什么？

• MyISAM 效率最高
• 因为 MyISAM 内部维护了一个计数器，直接返回总条数
• 而 InnoDB 要逐行统计

在 InnoDB 引擎中 count(*)、count(1)、count(主键)、count(字段) 哪个性能最高？

• 对于 count(字段) 来说，遍历整张表，取这个字段，然后判断是否为 null，不为 null 则加 1
• 对于 count(主键 id) 来说，遍历整张表，取主键 id，每次加 1
• 对于 count(1) 来说，遍历整张表，不取值
• 对于 count(_) 来说，遍历整张表，不取值，直接按行累加

结果：count(字段)<count(主键 id)<count(1)≈count(*)

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

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

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

1. 唯一键冲突
2. 事务回滚

• 执行器执行插入，自增键 +1，但是插入时发现唯一键冲突，无法插入，导致的不连续
• 事务添加自增键 +1，但是由于事务回滚，数据被清除，导致的不连续

什么是覆盖索引？

覆盖索引是指，索引上的信息足够满足查询请求，不需要回表取数据

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

如果把主键删掉了，那么 InnoDB 会自己生成一个 rowid 作为主键

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

• 内存表，指的是使用 Memory 引擎的表，数据放在内存中，重启会被清空
• 临时表，指的是使用 InnoDB 引擎或者 MyISAM 引擎的表，数据放在磁盘上，重启不会清空

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

• CHAR
• VARCHAR
• BLOB
• TEXT
• LONGTEXT
• VARBINARY
• BINARY

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

• VARCHAR 的长度可变，而 CHAR 是固定长度
• CHAR 比较适合长度较短的字段和固定长度的字段，如身份证号、手机号等
• 反之则适合使用 VARCHAR

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

MySQL 存储金额应该使用 decimal ，因为如果存储其他数据类型，比如 float 有导致小数点后数据丢失的风险

limit 3,2 的含义是什么？

去除前三条数据之后查询两条信息

删除表的数据有几种方式？它们有什么区别？

删除数据有两种方式：delete 和 truncate，它们的区别如下：

• delete 可以添加 where 条件删除部分数据，truncate 不能添加 where 条件，只能删除整张表
• delete 的删除信息会在 MySQL 的日志中记录，而 truncate 的删除信息不被记录， 因此 detele 的信息可以被找回，而 truncate 的信息无法被找回
• truncate 因为不记录日志所以执行效率比 delete 快

MySQL 中支持几种模糊查询？它们有什么区别？

MySQL 中支持两种模糊查询：regexp 和 like

• like 是模糊匹配
• regexp 支持正则表达式

count(column) 和 count(*) 有什么区别？

• count(column) 统计不会统计列值为 null 的数据
• count(*) 则会统计所有信息

MySQL 中内连接、左连接、右连接有什么区别？

• 内连（inner join）— 把匹配的关联数据显示出来
• 左连接（left join）— 把左边的表全部显示出来，右边的表显示出符合条件的数据
• 右连接（right join）— 把右边的表全部显示出来，左边的表显示出符合条件的数据

MySQL 日志篇

MySQL 有哪些重要的日志文件？

• 错误日志：记录 MySQL 服务器运行过程中的错误信息
• 常规查询日志：记录了数据库执行的所有命令
• 慢日志：记录查询超过指定时间的语句
• redo log 重做日志：一种缓存机制，先将数据写入内存中，再批量把内存中的数据统一刷回磁盘
• undo log 回滚日志：存储日志被修改前的值
• bin log 二进制日志：主要记录了对 MySQL 数据库执行更改的所有操作

redo log 和 binlog 有什么区别？

• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”
• binlog 是逻辑日志，记录的是这个语句的原始逻辑
• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用
• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的

什么是 crash-safe？

crash-safe 是指发生宕机等意外情况下，服务器重启后数据不会丢失的情况

什么是脏页和干净页？

MySQL 为了操作的性能优化，会把数据更新先放入内存中，之后再统一更新到磁盘。
当内存数据和磁盘数据内容不一致的时候，我们称这个内存页为脏页
内存数据写到磁盘后，内存的数据和磁盘上一致，我们称为“干净页”

什么情况下会引发 MySQL 刷脏页（flush）的操作？

• 内存写满了，这个时候就会引发 flush 操作，InnoDB 中就是 redo log 写满
• 系统的内存不足，当需要新的内存页的时候，就会淘汰一些内存页，如果淘汰的是脏页这个时候就会触发 flush 操作
• 系统空闲的时候，MySQL 会同步内存中的数据到磁盘也会触发 flush 操作
• MySQL 服务关闭的时候也会刷脏页，触发 flush 操作

MySQL 刷脏页的速度很慢可能是什么原因？

• 在 MySQL 中单独刷一个脏页的速度是很快的
• 如果发现刷脏页的速度很慢，说明触发了 MySQL 刷脏页的“连坐”机制
• MySQL 的“连坐”机制是指当 MySQL 刷脏页的时候如果发现相邻的数据页也是脏页也会一起刷写到磁盘

如何控制 MySQL 只刷新当前脏页？

在 InnoDB 中设置 innodb_flush_neighbors 这个参数的值为 0，来规定 MySQL 只刷当前脏页，MySQL 8 这个值默认是 0

MySQL 的 WAL 技术是解决什么问题的？

A.防止误删除，找回数据用的 B.容灾恢复，为了还原异常数据用的 C.事务处理，为了数据库的稳定性 D.为了降低 IO 成本
答：D
题目解析：WAL 技术的全称是 Write Ahead Logging（预写式日志），是先写日志，再写磁盘的方式，因为每次更新都写磁盘的话 IO 成本很高，所以才有了 WAL 技术

为什么有时候会感觉 MySQL 偶尔卡一下？

如果偶尔感觉 MySQL 卡一下，可能是 MySQL 正在刷脏页，正在把内存中的更新操作刷到磁盘中

redo log 和 binlog 是怎么关联的?

每个事务都有一个唯一的 XID，用于标识事务

• 在 redo log 中，事务的开始和结束会被记录，这些记录包括 XID
• 如果在 redo log 中发现只有 prepare 而没有 commit 的记录，MySQL 会使用 XID 去 binlog 中找到相应的事务，以确定该事务的最终状态，然后提交或回滚事务

MySQL 怎么知道 binlog 是完整的?

• statement 格式的 binlog，完整的标识是最后有 COMMIT 关键字
• row 格式的 binlog，完整的标识是最后会有一个 XID 关键字

MySQL 中可不可以只要 binlog，不要 redo log？

不可以，binlog 没有崩溃恢复的能力

MySQL 中可不可以只要 redo log，不要 binlog？

不可以，原因有以下两个：

• redo log 是循环写，不能保证所有的历史数据，这些历史数据只能在 binlog 中找到
• binlog 是高可用的基础，高可用的实现原理就是 binlog 复制

为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的？

• 因为 binlog 是不能被打断的，一个事务的 binlog 必须连续写，因此要整个事务完成后，再一起写到文件里
• redo log 并没有这个要求，中间有生成的日志可以写到 redo log buffer 中，在其他事务提交的时候可以被一起写到磁盘中

事务执行期间，还未提交，如果发生 crash，redo log 丢失，会导致主备不一致呢？

不会，因为这时候 binlog 也还在 binlog cache 里，没发给备库，crash 以后 redo log 和 binlog 都没有了，从业务角度看这个事务也没有提交，所以数据是一致的

在 MySQL 中用什么机制来优化随机读/写磁盘对 IO 的消耗？

• redo log 是用来节省随机写磁盘的 IO 消耗
• change buffer 主要是节省随机读磁盘的 IO 消耗

以下说法错误的是？

A.redo log 是 InnoDB 引擎特有的，它的固定大小的
B.redo log 日志是不全的，只有最新的一些日志，这和它的内存大小有关
C.redo log 可以保证数据库异常重启之后，数据不丢失
D.binlog 是 MySQL 自带的日志，它能保证数据库异常重启之后，数据不丢失 答：D
题目解析：binlog 是 MySQL 自带的日志，但它并不能保证数据库异常重启之后数据不丢失

以下说法正确的是？

A.redo log 日志是追加写的，后面的日志并不会覆盖前面的日志
B.binlog 日志是追加写的，后面的日志并不会覆盖前面的日志
C.redo log 和 binlog 日志都是追加写的，后面的日志并不会覆盖前面的日志
D.以上说法都正确
答：B
题目解析：binlog 日志是追加写的，后面的日志并不会覆盖前面的日志，redo log 日志是固定大小的，后面的日志会覆盖前面的日志

有没有办法把 MySQL 的数据恢复到过去某个指定的时间节点？怎么恢复？

• 可以恢复，只要你备份了这段时间的binlog，同时做了数据库的定期备份
• 可以把之前备份的数据库先还原到测试库
• 从备份的时间点开始，将备份的 binlog 依次取出来，重放到要恢复数据的那个时刻，这个时候就完成了数据到指定节点的恢复

MySQL 性能优化

MySQL 性能指标都有哪些？

• TPS：每秒事务数
• QPS：每秒请求次数
• IOPS：每秒处理的 IO 请求次数

什么是慢查询？

慢查询是 MySQL 中提供的一种慢查询日志，它用来记录在 MySQL 中响应时间超过阀值的语句

如何定位慢查询？

使用 MySQL 中的 explain 分析执行语句

explain select * from t where id=5

MySQL 的优化手段都有哪些？

• 查询优化
• 优化索引的使用
• 表结构设计优化
• 表拆分
• 读写分离

查询优化

• 避免 select *
• 小表驱动大表
• 使用连接代替子查询

优化索引的使用

• 尽量使用主键查询
• 不做列运算
• 查询语句尽可能简单
• 不使用 select * 查询
• 不用函数和触发器
• 避免% 在前的查询
• 避免在 where 子句中对字段进行表达式操作
• 尽量使用数字型字段

表结构设计优化

• 使用简单的数据类型，int 要比 varchar 类型在 MySQL 处理简单
• 尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int
• 尽可能使用 not null 定义字段，因为 null 占用 4 字节空间
• 尽量少用 text 类型，非用不可时最好考虑分表
• 尽量使用 timestamp，而非 datetime
• 单表不要有太多字段，建议在 20 个字段以内

表拆分

当数据库中的数据非常大时，查询优化方案也不能解决查询速度慢的问题时，可以考虑拆分表，让每张表的数据量变小，从而提高查询效率

• 垂直拆分：数据表列的拆分
• 水平拆分：表的行数过大时，拆分成多张表

读写分离

• 采用数据库集群的方案
• 使用一个库作为主库，负责写入数据
• 其他库为从库，负责读取数据，这样可以缓解对数据库的访问压力

MySQL 常见读写分离方案有哪些？

• 应用层：通过应用层对数据源做理由来实现读写分离
• 中间件：通过 MySQL 的中间件做主从集群

MySQL 开放问题

有一个超级大表，如何优化分页查询？

• 数据库层面优化：利用子查询优化超多分页场景，利用子查询先快速定位需要获取的 id 段，然后再关联查询
• 程序层面优化：可以利用缓存把查询的结果缓存起来

查询长时间不返回可能是什么原因？应该如何处理？

• 查询字段没有索引或者没有触发索引查询
  • 以 % 开头的 like 查询
  • 数据类型出现隐式转换时不会使用索引
  • 不符合最左前缀原则；
  • 如果查询条件有 or 分割，如果有 or 的字段没有索引，会引起全表查询
  • 如果 MySQL 认为使用索引会比全表查询更慢，则不会使用索引
• IO 压力大，读取磁盘速度变慢
• 内存不足
• 网络速度慢
• 查询出的数据量过大
• 死锁

以上问题的解决方案如下：

• 正确创建和使用索引
• 把数据、日志、索引放到不同的 IO 设备上，减少主数据库的 IO 操作。更换 MySQL 的磁盘为固态硬盘，以提高磁盘的 IO 性能
• 升级内存，更换更大的内存
• 提升网速，升级带宽
• 查询时只返回需要的字段。
• 设置死锁的超时时间，限制和避免死锁消耗过多服务器的资源

MySQL 主从延迟的原因有哪些？

• 主库有大事务处理
• 主库做大量的增、删、改操作
• 主库对大表进行字段新增、修改或添加索引等操作
• 主库的从库太多，导致复制延迟
• 主库读写压力大，导致复制延迟
• 从库之间的网络延迟

如何保证数据不被误删？

• 权限控制与分配（数据库和服务器权限）
• 避免数据库账号信息泄露，业务代码不要使用明文保存数据库连接信息
• 部署延迟复制从库，万一误删除时用于数据回档，且从库设置为 read-only
• 确认备份制度及时有效
• 启用 sql_safe_updates 选项，不允许没 where 条件的更新/删除
• 将系统层的 rm 改为 mv
• 线上不进行物理删除，改为逻辑删除
• 开启 binlog，方便追溯数据

MySQL 服务器 CPU 飙升应该如何处理？

• 使用 show full processlist 查出慢查询
• 为了缓解数据库服务器压力，先使用 kill 命令杀掉慢查询的客户端
• 然后再去项目中找到执行慢的 SQL 语句进行修改和优化

MySQL 毫无规律的异常重启，可能产生的原因是什么？该如何解决？

可能是积累的长连接导致内存占用太多，被系统强行杀掉导致的异常重启

• 定期断开空闲的长连接
• 定期重新初始化连接资源

补充

查询语句不同元素（where、jion、limit、group by、having 等等）执行先后顺序？

• from: 需要从哪个数据表检索数据
• where: 过滤表中数据的条件
• group by: 如何将上面过滤出的数据分组
• having: 对上面已经分组的数据进行过滤的条件
• select: 查看结果集中的哪个列，或列的计算结果
• order by : 按照什么样的顺序来查看返回的数据

数据库三范式，根据某个场景设计数据表？

• 第一范式：所有字段值都是不可分解的原子值
• 第二范式：在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中
• 第三范式：数据表中的每一列数据都和主键直接相关，而不能间接相关

MySQL 高并发环境解决方案？

分库 分表 分布式 增加二级缓存

Explain 性能分析

• 查看执行计划：使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的
• 分析查询语句或是表结构的性能瓶颈

秋招后端开发面试题系列目录
一、Java
1.1 Java基础上
1.2 Java基础下
1.3 Java集合
1.4 JavaIO
1.5 Java多线程上
1.6Java多线程下
二、JVM
2.1 JVM底层原理
2.2 垃圾回收器
2.3 垃圾回收算法
2.4 类加载机制
2.5 运行时数据区
三、MySQL
3.1 MySQL基础
3.2 事务
3.3 索引
3.4 锁机制
3.5 MVCC
四、Redis
4.1 Redis基础
4.2 缓存原理
五、中间件
5.1 RabbitMQ
六、Spring开源框架
6.1 Spring
6.2 Spring MVC
6.3 Spring Boot
6.4 MyBatis
七、操作系统
八、计算机网络
九、设计模式
十、微服务架构
十一、Spring Cloud分布式
11.1 分布式基础
11.2 Spring Cloud
11.3 GateWay
11.4 Nacos
11.5 OpenFeign
11.6 Ribbon
十二、算法
十三、项目