mysql经典知识总结

最新推荐文章于 2025-04-20 15:18:42 发布

原创最新推荐文章于 2025-04-20 15:18:42 发布 · 289 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

5 篇文章

订阅专栏

本文精选了MySQL面试题，涵盖索引、锁、事务、隔离级别等核心知识点，解析MySQL与Oracle的区别、数据类型选择、自增主键行为、存储引擎特性、索引原理与使用技巧，以及主从复制、备份策略、日志管理和性能调优等运维相关内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

精尽 MySQL 面试题

以下面试题，基于网络整理，和自己编辑。具体参考的文章，会在文末给出所有的链接。

如果胖友有自己的疑问，欢迎在星球提问，我们一起整理吊吊的 MySQL 面试题的大保健。

而题目的难度，尽量按照从容易到困难的顺序，逐步下去。

另外，MySQL 涉及的内容非常非常非常多，所以面试题也容易写的杂乱。当年，我们记着几个一定要掌握的重心：

重点的题目，已经在标题前，添加了【重点】前缀。

索引。
锁。
事务和隔离级别。

因为 MySQL 还会有部分内容和运维相关度比较高，所以本文我们分成两部分【开发】【运维】两部分。

对于【开发】部分，我们需要掌握。
对于【运维】部分，更多考验开发的知识储备情况，当然能回答出来是比较好的，特别是对于高级开发工程师、架构师等。

开发

为什么互联网公司一般选择 MySQL 而不是 Oracle?

免费、流行、够用。

? 当然，这个回答要稍微润色下。不过一般，很少问这个问题了。

数据库的三范式是什么？什么是反模式？

重点在于反模式的回答。实际在开发的过程中不会严格遵循三范式

胖友直接看《服务端指南数据存储篇 | MySQL（07）范式与反模式》。

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。具体可以看看《MySQL 数据类型》文档。

正确的使用数据类型，对数据库的优化是非常重要的。

? MySQL 中 varchar 与 char 的区别？varchar(50) 中的 50 代表的涵义？

1、varchar 与 char 的区别，char 是一种固定长度的类型，varchar 则是一种可变长度的类型。
2、varchar(50) 中 50 的涵义最多存放 50 个字符。varchar(50) 和 (200) 存储 hello 所占空间一样，但后者在排序时会消耗更多内存，因为 ORDER BY col 采用 fixed_length 计算 col 长度(memory引擎也一样)。

所以，实际场景下，选择合适的 varchar 长度还是有必要的。

? int(11) 中的 11 代表什么涵义？

int(11) 中的 11 ，不影响字段存储的范围，只影响展示效果。具体可以看看《MySQL 中 int 长度的意义》文章。

? 金额(金钱)相关的数据，选择什么数据类型？

方式一，使用 int 或者 bigint 类型。如果需要存储到分的维度，需要 *100 进行放大。
方式二，使用 decimal 类型，避免精度丢失。如果使用 Java 语言时，需要使用 BigDecimal 进行对应。

? 一张表，里面有 ID 自增主键，当 insert 了 17 条记录之后，删除了第 15,16,17 条记录，再把 MySQL 重启，再 insert 一条记录，这条记录的 ID 是 18 还是 15？

一般情况下，我们创建的表的类型是 InnoDB ，如果新增一条记录（不重启 MySQL 的情况下），这条记录的 ID 是18 ；但是如果重启 MySQL 的话，这条记录的 ID 是 15 。因为 InnoDB 表只把自增主键的最大 ID 记录到内存中，所以重启数据库或者对表 OPTIMIZE 操作，都会使最大 ID 丢失。
但是，如果我们使用表的类型是 MyISAM ，那么这条记录的 ID 就是 18 。因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里面，重启 MYSQL 后，自增主键的最大 ID 也不会丢失。

? 表中有大字段 X(例如：text 类型)，且字段 X 不会经常更新，以读为为主，请问您是选择拆成子表，还是继续放一起?写出您这样选择的理由

拆带来的问题：连接消耗 + 存储拆分空间。

如果能容忍拆分带来的空间问题，拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区) 顺序 IO ，减少连接消耗，最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗。
不拆可能带来的问题：查询性能。

如果能容忍不拆分带来的查询性能损失的话，上面的方案在某个极致条件下肯定会出现问题，那么不拆就是最好的选择。

实际场景下，例如说商品表数据量比较大的情况下，会将商品描述单独存储到一个表中。即，使用拆的方案。

MySQL 有哪些存储引擎？

MySQL 提供了多种的存储引擎：

InnoDB
MyISAM
MRG_MYISAM
MEMORY
CSV
ARCHIVE
BLACKHOLE
PERFORMANCE_SCHEMA
FEDERATED

目前，MySQL 默认的存储引擎是 InnoDB ，并且也是最主流的选择。主要原因如下：

【最重要】支持事务。
支持行级锁和表级锁，能支持更多的并发量。
查询不加锁，完全不影响查询。
支持崩溃后恢复。

在 MySQL5.1 以及之前的版本，默认的存储引擎是 MyISAM ，但是目前已经不再更新，且它有几个比较关键的缺点：

不支持事务。
使用表级锁，如果数据量大，一个插入操作锁定表后，其他请求都将阻塞。

我们不需要花太多力气在 MyISAM 的学习上

请说明 InnoDB 和 MyISAM 的区别

	InnoDB	MyISAM
事务	支持	不支持
存储限制	64TB	无
锁粒度	行锁	表锁
崩溃后的恢复	支持	不支持
外键	支持	不支持
全文检索	5.7 版本后支持	支持

? 为什么 SELECT COUNT(*) FROM table 在 InnoDB 比 MyISAM 慢？

对于 SELECT COUNT(*) FROM table 语句，在没有 WHERE 条件的情况下，InnoDB 比 MyISAM 可能会慢很多，尤其在大表的情况下。因为，InnoDB 是去实时统计结果，会全表扫描；而 MyISAM 内部维持了一个计数器，预存了结果，所以直接返回即可。

【重点】什么是索引？

索引，类似于书籍的目录，想找到一本书的某个特定的主题，需要先找到书的目录，定位对应的页码。

MySQL 中存储引擎使用类似的方式进行查询，先去索引中查找对应的值，然后根据匹配的索引找到对应的数据行。

? 索引有什么好处？

提高数据的检索速度，降低数据库IO成本：使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
降低数据排序的成本，降低CPU消耗：索引之所以查的快，是因为先将数据排好序，若该字段正好需要排序，则正好降低了排序的成本。

? 索引有什么坏处？

占用存储空间：索引实际上也是一张表，记录了主键与索引字段，一般以索引文件的形式存储在磁盘上。
降低更新表的速度：表的数据发生了变化，对应的索引也需要一起变更，从而减低的更新速度。否则索引指向的物理数据可能不对，这也是索引失效的原因之一。

? 索引的使用场景？

1、对非常小的表，大部分情况下全表扫描效率更高。
2、对中大型表，索引非常有效。
3、特大型的表，建立和使用索引的代价随着增长，可以使用分区技术来解决(很少使用)。

对于特大型的表，更常用的是“分库分表”，目前解决方案有 Sharding Sphere、MyCAT 等等。

? 索引的类型？

索引，都是实现在存储引擎层的。主要有六种类型：

1、普通索引：最基本的索引，没有任何约束。
2、唯一索引：与普通索引类似，但具有唯一性约束。
3、主键索引：特殊的唯一索引，不允许有空值。
4、复合索引：将多个列组合在一起创建索引，可以覆盖多个列。
5、外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作。
6、全文索引：MySQL 自带的全文索引只能用于 InnoDB、MyISAM ，并且只能对英文进行全文检索，一般使用全文索引引擎。

常用的全文索引引擎的解决方案有 Elasticsearch、Solr 等等。最为常用的是 Elasticsearch 。

具体的使用，可以看看《服务端指南数据存储篇 | MySQL（03）如何设计索引》。

? MySQL 索引的“创建”原则？

注意，是“创建”噢。

1、最适合索引的列是出现在 WHERE 子句中的列，或连接子句中的列，而不是出现在 SELECT 关键字后的列。
2、索引列的基数越大，索引效果越好。
具体为什么，可以看看如下两篇文章：
- 《MySQL 索引基数》理解相对简单
- 《低基数索引为什么会对性能产生负面影响》写的更原理，所以较为难懂(么看懂)。

3、根据情况创建复合索引，复合索引可以提高查询效率。

因为复合索引的基数会更大。
4、避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率。
5、主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率。
6、对字符串进行索引，应该定制一个前缀长度，可以节省大量的索引空间

? MySQL 索引的“使用”注意事项？

注意，是“使用”噢。

1、应尽量避免在 WHERE 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

注意，column IS NULL 也是不可以使用索引的。
2、应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：SELECT id FROM t WHERE num = 10 OR num = 20 。
3、应尽量避免在 WHERE 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
4、应尽量避免在 WHERE 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
5、不要在 WHERE 子句中的 = 左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
6、复合索引遵循前缀原则。
7、如果 MySQL 评估使用索引比全表扫描更慢，会放弃使用索引。如果此时想要索引，可以在语句中添加强制索引。
8、列类型是字符串类型，查询时一定要给值加引号，否则索引失效。
9、LIKE 查询，% 不能在前，因为无法使用索引。如果需要模糊匹配，可以使用全文索引。

关于这块，可以看看《服务端指南数据存储篇 | MySQL（04）索引使用的注意事项》文章，写的更加细致。

? 想知道一个查询用到了哪个索引，如何查看?

EXPLAIN 显示了 MYSQL 如何使用索引来处理 SELECT 语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。

使用方法，在 SELECT 语句前加上 EXPLAIN 就可以了。感兴趣的胖友，可以详细看看《mysql explain详细执行计划解释》。

重点】MySQL 索引的原理？

解释 MySQL 索引的原理，篇幅会比较长，并且网络上已经有靠谱的资料可以看，所以艿艿这里整理了几篇，胖友可以对照着看。

下面，艿艿对关键知识做下整理，方便胖友回顾。

? MySQL 有哪些索引方法？

艿艿：这个问题是索引方法 Index Method ，上面的索引类型 Index Type 。

在 MySQL 中，我们可以看到两种索引方式：

B-Tree 索引。
Hash 索引。

实际场景下，我们基本仅仅使用 B-Tree 索引。详细的对比可以看看《MySQL BTree 索引和 hash 索引的区别》。

对于 Hash 索引，我们了解即可，面试重点是掌握 B-Tree 索引的原理。

? 什么是 B-Tree 索引？

B-Tree 是为磁盘等外存储设备设计的一种平衡查找树。因此在讲 B-Tree 之前先了解下磁盘的相关知识。

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为 16 KB，可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K ，在 MySQL 中可通过如下命令查看页的大小：
mysql> show variables like 'innodb_page_size';
而系统一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB 。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘 I/O 次数，提高查询效率。

B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组 [key, data] ，key 为记录的键值，对应表中的主键值，data 为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

一棵 m 阶的 B-Tree 有如下特性：

艿艿：如果胖友对算法了解不对，可能对下面的各种数字关系不太能理解。最起码，要弄懂层级关系，以及每种节点存储的数据。

每个节点最多有 m 个孩子。
- 除了根节点和叶子节点外，其它每个节点至少有 Ceil(m/2) 个孩子。
- 若根节点不是叶子节点，则至少有 2 个孩子。
所有叶子节点都在同一层，且不包含其它关键字信息。
每个非叶子节点包含 n 个关键字信息（P0,P1,…Pn, k1,…kn）
- 关键字的个数 n 满足：ceil(m/2)-1 <= n <= m-1
- ki(i=1,…n) 为关键字，且关键字升序排序。
- Pi(i=0,…n) 为指向子树根节点的指针。P(i-1) 指向的子树的所有节点关键字均小于 ki ，但都大于 k(i-1) 。

B-Tree 中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个 3 阶的 B-Tree：

B-Tree 的结构

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的 key 和三个指向子树根节点的 point ，point 存储的是子节点所在磁盘块的地址。两个 key 划分成的三个范围域，对应三个 point 指向的子树的数据的范围域。
以根节点为例，key 为 17 和 35 ，P1 指针指向的子树的数据范围为小于 17 ，P2 指针指向的子树的数据范围为 [17~35] ，P3 指针指向的子树的数据范围为大于 35 。

模拟查找 key 为 29 的过程：

1、根据根节点找到磁盘块 1 ，读入内存。【磁盘I/O操作第1次】
2、比较 key 29 在区间（17,35），找到磁盘块 1 的指针 P2 。
3、根据 P2 指针找到磁盘块 3 ，读入内存。【磁盘I/O操作第2次】
4、比较 key 29 在区间（26,30），找到磁盘块3的指针P2。
5、根据 P2 指针找到磁盘块 8 ，读入内存。【磁盘I/O操作第3次】
6、在磁盘块 8 中的 key 列表中找到 eky 29 。

分析上面过程，发现需要 3 次磁盘 I/O 操作，和 3 次内存查找操作。由于内存中的 key 是一个有序表结构，可以利用二分法查找提高效率。而 3 次磁盘 I/O 操作是影响整个 B-Tree 查找效率的决定因素。B-Tree 相对于 AVLTree 缩减了节点个数，使每次磁盘 I/O 取到内存的数据都发挥了作用，从而提高了查询效率。

? 什么是 B+Tree 索引？

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用 B+Tree 实现其索引结构。

下面这一段，面试非常关键。

从上一节中的 B-Tree 结构图中可以看到，每个节点中不仅包含数据的 key 值，还有 data 值。而每一个页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小，当存储的数据量很大时同样会导致 B-Tree 的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率。在 B+Tree 中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 值数量，降低 B+Tree 的高度。

B+Tree 相对于 B-Tree 有几点不同：

非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。

将上一节中的 B-Tree 优化，由于 B+Tree 的非叶子节点只存储键值信息，假设每个磁盘块能存储 4 个键值及指针信息，则变成 B+Tree 后其结构如下图所示：

B+Tree 的结构

通常在 B+Tree 上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

可能上面例子中只有 22 条数据记录，看不出 B+Tree 的优点，下面做一个推算：

InnoDB 存储引擎中页的大小为 16KB，一般表的主键类型为 INT（占用4个字节）或 BIGINT（占用8个字节），指针类型也一般为 4 或 8 个字节，也就是说一个页（B+Tree 中的一个节点）中大概存储 16KB/(8B+8B)=1K 个键值（因为是估值，为方便计算，这里的 K 取值为〖10〗^3）。也就是说一个深度为 3 的 B+Tree 索引可以维护10^3 10^3 10^3 = 10亿条记录。
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在 2~4 层。MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要 1~3 次磁盘 I/O 操作。

? 关于熟悉 MySQL 的锁机制？

mysql锁机制

【重要】MySQL 查询执行顺序？

(1)     SELECT
(2)     DISTINCT <select_list>
(3)     FROM <left_table>
(4)     <join_type> JOIN <right_table>
(5)     ON <join_condition>
(6)     WHERE <where_condition>
(7)     GROUP BY <group_by_list>
(8)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

? 关于熟悉 MySQL 查询为什么会变慢

mysql 查询为什么会变慢

编写 SQL 查询语句的考题合集

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

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

如果此时是 IO 压力比较大，可以使用 iostat 命令，定位是哪个进程占用了磁盘 IO 。

如果是 mysqld 造成的，使用 show processlist 命令，看看里面跑的 Session 情况，是不是有消耗资源的 SQL 在运行。找出消耗高的 SQL ，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。一般来说，肯定要 kill 掉这些线程(同时观察 CPU 使用率是否下降)，等进行相应的调整(比如说加索引、改 SQL 、改内存参数)之后，再重新跑这些 SQL。

也可以查看 MySQL 慢查询日志，看是否有慢 SQL 。

也有可能是每个 SQL 消耗资源并不多，但是突然之间，有大量的 Session 连进来导致 CPU 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

MySQL 主从复制的流程是怎么样的？

主从复制图解原理

MySQL 的主从复制是基于如下 3 个线程的交互（多线程复制里面应该是 4 类线程）：

1、Master 上面的 binlog dump 线程，该线程负责将 master 的 binlog event 传到 slave 。
2、Slave 上面的 IO 线程，该线程负责接收 Master 传过来的 binlog，并写入 relay log 。
3、Slave 上面的 SQL 线程，该线程负责读取 relay log 并执行。
4、如果是多线程复制，无论是 5.6 库级别的假多线程还是 MariaDB 或者 5.7 的真正的多线程复制， SQL 线程只做 coordinator ，只负责把 relay log 中的 binlog 读出来然后交给 worker 线程， woker 线程负责具体 binlog event 的执行

? MySQL 如何解决主从复制的延时性？

5.5 是单线程复制，5.6 是多库复制（对于单库或者单表的并发操作是没用的），5.7 是真正意义的多线程复制，它的原理是基于 group commit，只要 master 上面的事务是 group commit 的，那 slave 上面也可以通过多个 worker线程去并发执行。和 MairaDB10.0.0.5 引入多线程复制的原理基本一样。

? 工作遇到的复制 bug 的解决方法？

5.6 的多库复制有时候自己会停止，我们写了一个脚本重新 start slave 。

? 你是否做过主从一致性校验，如果有，怎么做的，如果没有，你打算怎么做？

主从一致性校验有多种工具例如 checksum、mysqldiff、pt-table-checksum 等。

聊聊 MySQL 备份方式？备份策略是怎么样的？

具体的，胖友可以看看《MySQL 高级备份策略》。主要有几个知识点：

数据的备份类型
- 【常用】完全备份
  
  这是大多数人常用的方式，它可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。但它需要花费更多的时间和空间，所以，一般推荐一周做一次完全备份。
- 增量备份
  
  它是只备份数据库一部分的另一种方法，它不使用事务日志，相反，它使用整个数据库的一种新映象。它比最初的完全备份小，因为它只包含自上次完全备份以来所改变的数据库。它的优点是存储和恢复速度快。推荐每天做一次差异备份。
- 【常用】事务日志备份
  
  事务日志是一个单独的文件，它记录数据库的改变，备份的时候只需要复制自上次备份以来对数据库所做的改变，所以只需要很少的时间。为了使数据库具有鲁棒性，推荐每小时甚至更频繁的备份事务日志。
- 文件备份
  
  数据库可以由硬盘上的许多文件构成。如果这个数据库非常大，并且一个晚上也不能将它备份完，那么可以使用文件备份每晚备份数据库的一部分。由于一般情况下数据库不会大到必须使用多个文件存储，所以这种备份不是很常用。

备份工具

cp
mysqldump
xtrabackup
lvm2 快照

MySQL 有哪些日志？

错误日志：记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。
二进制文件：记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，不包括数据查询语句。语句以“事件”的形式保存，它描述了数据的更改过程。（定期删除日志，默认关闭）。

就是我们上面看到的 MySQL binlog 日志。
查询日志：记录了客户端的所有语句，格式为纯文本格式，可以直接进行读取。（log 日志中记录了所有数据库的操作，对于访问频繁的系统，此日志对系统性能的影响较大，建议关闭，默认关闭）。
慢查询日志：慢查询日志记录了包含所有执行时间超过参数long_query_time（单位：秒）所设置值的 SQL 语句的日志。（纯文本格式）