MySQL常见面试题整理（含概率标注）及面试常问数据库点

2022年MySQL最新面试题目录

前言

最近在整理一份关于 MySQL 常见面试题的内容，并且会依据自身的经验，标注出一些出现的概率，其中出现概率最高的为 5 颗。例如：

百万级别或以上的数据如何删除

出现概率:

在面试中，一般来说，关于数据库的面试题中，频率较高的几个关键词有 SQL 优化、索引、存储引擎、事务、死锁、乐观锁、悲观锁，以及关系型数据库与非关系数据库的对比等。问完这些点，大概需要 10 到 20 分钟（通常一轮面试约 1 小时），基本上这些能让面试官对您的数据库知识有一定的了解。

如果你有线上运维经验，通常会问到一些问题，比如数据库扩容的相关事宜，怎样给大表添加索引，在业务高峰时期如何给一个大表添加字段等，同时你也可以查看我的在线博客。

欢迎关注我的公众号“漫步 coding”，它是一个专注于编程、算法、数据库的博主。可以一起交流，在 coding 的世界里漫步。若回复“mysql”，便能免费获取 mysql 最新、最全的面试题（含答案）电子书。

希望这篇文章能对大家有所帮助，同时也希望大家都能找到称心如意的好工作。

- 平时在 MySQL 方面，主要使用的是哪个版本。

出现概率:

你可以说一说自己所使用的 MySQL 版本，例如我使用的是 5.7 版本。接着，你可以简单地谈论一下这个版本的一些特点。

1)、安全性

在MySQL 5.7中，有不少安全性相关的改进。包括：

MySQL 数据库初始化结束后，会生成一个 root@localhost 用户。从 MySQL 5.7 起，root 用户的密码不再是空白的，而是随机生成了一个密码。这使得用户在安装 5.7 版本时，发现了与 5.6 版本相比一个较大的不同之处。

MySQL 官方已将 test 数据库删除。默认安装完成后，不存在 test 数据库。即便用户创建了 test 库，也能够对 test 库进行权限控制。

MySQL 5.7 版本的一个特点是提供了较为简单的 SSL 安全访问配置。同时，它默认的连接方式采用的是 SSL 的加密方式。

能够为用户设定密码过期的相关策略，在经过特定的一段时间之后，会强制要求用户对密码进行修改。

2)、灵活性

MySQL 5.7 具有两个全新的功能，一个是 JSON，另一个是 generate column。

CREATE TABLE t1 (jdoc JSON);
向 t1 表中插入值，这个值是一个包含"key1"对应"value1"以及"key2"对应"value2"的 JSON 数据。

3)、可用性

在线设置复制的过滤规则，无需重启 MySQL，只需停止 SQL thread，修改完成后启动 SQL thread

主要谈谈你自己的理解，也可依据自身理解进行延展，以提升面试的加分。

2、数据库三大范式是什么

出现概率:

第一范式：每个列都不可以再拆分。

第二范式的要求是：在第一范式的基础之上，非主键列必须完全依赖于主键，不能仅仅依赖于主键的一部分。

第三范式是在第二范式的基础之上的。在第三范式中，非主键列仅仅依赖于主键，而不会依赖于其他的非主键。

设计数据库结构时，应尽量遵循三范式。若不遵守，就必须有足够的理由，例如性能方面。实际上，我们常常会因为性能而对数据库的设计做出妥协。

3、MySQL有关权限的表都有哪几个

出现概率:

MySQL 服务器凭借权限表来管控用户对数据库的访问，权限表被存放在 mysql 数据库当中，并且是由 mysql_install_db 脚本进行初始化的。这些权限表包含 user 表、db 表、table_priv 表、columns_priv 表以及 host 表。接下来分别对这些表的结构和内容进行介绍：

语言结构：语言功能部分记录用户帐号信息，权限部分说明其为全局级。

db权限表：记录各个帐号在各个数据库上的操作权限。

table_priv权限表：记录数据表级的操作权限。

columns_priv权限表：记录数据列级的操作权限。

host 权限表用于对给定主机上数据库级操作权限进行更细致的控制，它与 db 权限表相配合。并且这个权限表不会受到 GRANT 和 REVOKE 语句的影响。

MySQL 的 binlog 有哪些录入格式呢？这些录入格式之间又有怎样的区别呢？

出现概率:

MySQL 的 binlog 存在三种格式，分别是 statement 格式、row 格式以及 mixed 格式。

在 statement 模式下，每一条会修改数据的 sql 都会被记录在 binlog 中。它不需要记录每一行的变化，这样就减少了 binlog 日志量，节约了 IO，从而提高了性能。因为 sql 的执行是有上下文的，所以在保存的时候需要保存相关的信息。同时，还有一些使用了函数之类的语句是无法被记录复制的。

在 row 级别下，不会记录 sql 语句的上下文相关信息，只会保存哪条记录被修改。记录的单元是每一行的改动，基本上是可以全部记录下来的。然而，由于很多操作（比如 alter table），会导致大量行的改动，所以这种模式的文件保存的信息会很多，日志量也会很大。

混合方案是一种折中的方式。普通操作采用 statement 进行记录，而在无法使用 statement 的时候则使用 row。

新版 MySQL 对 row 级别进行了一些优化。在表结构发生变化时，它会记录语句，而非逐行记录。

你平时会用到哪些关系型数据库呢？可以谈谈你对它们的理解吗？你平时又会用到哪些非关系数据库呢？也可以谈谈你对它们的理解吗？

出现概率:

主要来讲你曾使用过的关系型数据库，例如 MySQL。同时也讲讲非关系数据库（NoSql 数据库），像 Redis 和 MongoDB 等。

1)、事务方面

关系型数据库的一个重要特点是事务具有一致性。因此，对于订单模型这类对一致性要求较高的情况，还是建议使用 MySQL。

2)、关系数据库的另一个特点就是其具有固定的表结构

在业务模型里，表结构固定是一件好事。没有约束的模型，更容易出现问题。

3)、复杂SQL，特别是多表关联查询

NoSql是不支持JOIN 这种查询的。

4)、索引方式

关系型数据库：B树、哈希等

NoSql：键值索引

5)、并发支持

关系型数据库在支持并发时，依靠事务和锁。在高并发的情形下，其执行效率相对较低。

所以 NoSql 的并发性能高。

自己可以多去延伸着看一看。这个面试出现的概率是蛮高的。

5、可以简单说说你对MySQL的逻辑架构了解吗？

出现概率:

第一层是服务器层，主要提供连接处理、授权认证、安全等功能。

第二层实现了 MySQL 的核心服务功能。其中包含查询解析、分析、优化以及缓存等。同时也涵盖了日期和时间等所有内置函数。所有跨存储引擎的功能都在这一层得以实现，比如存储过程、触发器、视图等。

第三层为存储引擎层。存储引擎的职责是负责 MySQL 中数据的存储以及提取。服务器借助 API 与存储引擎进行通信，这些接口将不同存储引擎的差异进行了屏蔽，从而使得这种差异在上层查询过程中呈现出透明的状态。除了会对 外键定义进行解析的 InnoDB 之外，存储引擎不会对 SQL 进行解析，并且不同存储引擎之间也不会相互通信，仅仅是简单地响应上层服务器的请求。

6、了解MySQL中的MVCC是什么?

出现概率:

MVCC 是多版本并发控制。在很多情况下，它能够避免加锁。它大都实现了非阻塞的读操作。并且写操作也只锁定必要的行。

InnoDB 的 MVCC 是通过在每行记录的后面保存两个隐藏的列来达成的。其中一个列保存了行的创建时间，另一个列保存了行的过期时间。然而，存储的并非实际的时间值，而是系统版本号。每开启一个新的事务，系统版本号都会自动增加。事务开始时刻的系统版本号会被当作事务的版本号，用于和查询到的每行记录的版本号进行对比。

MVCC 仅能在 READ COMMITTED 隔离级别下工作，也仅能在 REPEATABLE READ 隔离级别下工作。因为 READ UNCOMMITTED 始终读取最新的数据行，而非符合当前事务版本的数据行。同时，SERIALIZABLE 会对所有读取的行都加锁。

7、PostgreSQL相对于MySQL的优势

出现概率:

8、PostgreSQL和MySQL的一些区别

出现概率:

这个其实出现的概率还比较高， 自己可以说几点就行了

MySQL不支持地理数据类型。

9.2 起，PG 对 json 数据类型予以支持。与 MySQL 相比，PG 对 json 的支持较为先进。它具备一些专门针对 json 的操作符和函数，能让搜索 json 文本极为高效。从 9.4 开始，能够以二进制格式存储 json 数据，还支持在该列上创建全文索引（GIN 索引），以此在 json 文档中实现快速搜索。

从 5.7 起，MySQL 具备了支持 json 数据类型的能力，这比 PG 要晚。同时还能够在 json 列上构建索引。不过，对于 json 相关的函数的支持存在一定局限性。并且不支持在 json 列上进行全文索引。鉴于 MySQL 对 SQL 支持的诸多限制，在存储和处理 json 数据方面，MySQL 并非是一个理想的选择。

二、索引 0、概要 1、索引有哪些使用场景

出现概率:

1)、应该创建索引的场景

2)、不应创建索引的场景

索引的选择性是指索引列中不同值的数目与表中记录总数的比。

索引的选择性越接近于1，创建索引的价值就越高。反之就越低。

2、索引的数据结构（B+树，hash）

出现概率:

从存储结构方面划分：有 BTree 索引（B-Tree 或 B+Tree 索引）、Hash 索引、full-index 全文索引、R-Tree 索引。所描述的是索引存储时保存的形式，MySQL 通常采用的是 B+Tree，下面主要来讲讲 B+树的特点：

非叶子节点不存储 data ，它只存储索引（冗余的），这样就能放置更多的索引。

2.叶子节点包含所有索引字段

叶子节点通过指针相连接，这样能提升区间访问的性能。它可以快速定位范围查询，比如查询大于 20 的数据，第一次进行 io 操作从根节点开始查询三次就能定位到 20 ，接着通过后面的指针去查询大于 20 的数据，无需再从根节点重新开始查询，从而提高了性能。并且叶子节点的开始节点和结束节点也是通过指针连接起来的。

3、创建索引的原则是什么?

出现概率:

1)、选择唯一性索引

2)、为经常需要排序、分组和联合操作的字段建立索引

3)、为常作为查询条件的字段建立索引

4)、限制索引的数目

索引的数目并非越多越好。每个索引都会占用磁盘空间，一旦索引增多，所需的磁盘空间也就越大。在修改表时，对索引进行重构和更新是很麻烦的事情。而越多的索引，会让更新表的过程变得很浪费时间。

5)、尽量使用数据量少的索引

如果索引的值比较长，那么查询的速度就会受到影响。比如，对一个属于 CHAR(100)类型的字段进行全文检索所需要的时间，肯定要比对一个属于 CHAR(10)类型的字段需要的时间多。

6)、尽量使用前缀来索引

如果索引字段的值比较长，那么最好利用值的前缀来进行索引。比如 TEXT 类型和 BLOG 类型的字段，在进行全文检索时会耗费大量时间。倘若只检索字段前面的若干个字符，便能够提升检索的速度。

7)、最左前缀匹配原则

8)、查询时使用计算，会导致索引失效

4、使用索引查询一定能提高查询的性能吗？为什么

出现概率:

不是所有的查询使用查询都能提高性能， 比如下面几个案例

5、索引有哪些优缺点？

出现概率:

1)、索引的优点

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

使用索引后，在查询过程中能够使用优化隐藏器，从而提高系统的性能。

2)、索引的缺点

在时间方面，创建索引以及维护索引都需要耗费时间。具体而言，当对表中的数据进行增加操作、删除操作和修改操作的时候，索引需要进行动态的维护，这会导致增/改/删的执行效率降低。

空间方面：索引需要占物理空间。

6、讲一讲聚簇索引与非聚簇索引？

出现概率:

InnoDB 中，索引 B+Tree 的叶子节点存储了整行数据的是主键索引。主键索引也被称作聚簇索引。它将数据存储与索引放在了一起，所以找到索引也就找到了数据。

索引 B+Tree 的叶子节点存储了主键的值，这种索引是非主键索引，也被称作非聚簇索引、二级索引。

聚簇索引与非聚簇索引的区别：

非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键（行号），这就是非聚集索引与聚集索引的区别。

对于 InnoDB 而言，若要查找数据，需依据主键到聚集索引中去查找。将此依据聚集索引查找数据的过程称作回表。首次索引通常为顺序 IO，回表操作属于随机 IO。回表次数越多，也就是随机 IO 次数越多，我们就越倾向于采用全表扫描。

一般来讲，主键索引（聚簇索引）的查询通常只需进行一次查询。而非主键索引（非聚簇索引）则需要进行多次回表查询。当然，如果是覆盖索引的情况，那么只需进行一次查询就可以了。

MyISAM 的主键索引和二级索引均为非聚簇索引。InnoDB 的主键索引是聚簇索引，其二级索引为非聚簇索引。我们自行创建的索引大多为非聚簇索引。

7、百万级别或以上的数据如何删除

出现概率:

方法一:

索引是一个单独的文件。在进行增删改操作时，如果存在索引，就会消耗额外的 io。删除的速度与索引的数量是成正比的。

这个方法存在一个明显的缺点。在正式环境中，如果这个表的访问频率比较高，那么删除索引后，会有大量的 SQL 查询，这些查询会致使数据库的 IO 和 CPU 变得特别高。

方法二:

之前我在正式环境的做法如下：先将删除任务拆分成每次删除 1 万条，接着把拆分后的删除任务重新压入异步任务队列之中。

8、什么是最左前缀原则？什么是最左匹配原则

出现概率:

在建立 mysql 的联合索引时会依照最左前缀匹配的原则，也就是最左边具有优先权。在检索数据的时候会从联合索引的最左边开始进行匹配。例如：

对列col1、列col2和列col3建一个联合索引

在 test(col1,col2,col3)上进行 KEY test_col1_col2_col3 操作；

联合索引 test_col1_col2_col3 分别建立了(col1)这个索引，建立了(col1,col2)这个索引，还建立了(col,col2,col3)这个索引。

从 test 表中选择所有数据，条件是 col1 等于“1”，并且 clo2 等于“2”，同时 clo4 等于“4”。

这个查询语句在执行时会依据最左前缀匹配原则，在检索过程中会运用索引(col1,col2)