MySQL 面试题分享，这么详细的题解还怕背不下来？！

MySQL 是一个小型关系型数据库管理系统，对于想要应聘软件开发、数据开发、测试、数据分析，甚至是产品经理等等岗位的同学来说，是不能忽视的一个学习部分。

当然，java 基础， Java 集合、Java 并发编程，JVM，Spring ，SpringBoot，微服务， Kafka ，分布式，Redis ，分布式事务，设计模式，算法，数据结构，MySQL ……这么多相关内容，面试的时候，都有可能问到，不过今天，我们先讲MySQL。

我们面试鸭整理了一些 MySQL 相关的详细题解，由于篇幅限制，一些拓展的知识点需要在网站的题解阅读，欢迎大家来找鸭鸭刷题！

1，MySQL 中数据排序的实现原理是什么？

排序过程中，如果排序字段命中索引，则利用索引排序。

反之，使用文件排序。

文件排序中，如果数据量少则在内存中排序，具体是使用单路排序或者双路排序。

如果数据大则利用磁盘文件进行外部排序，一般使用归并排序。

2，MySQL 的 Change Buffer 是什么？它有什么作用？

我们来看一下官网的一张图：

从上面的图我们可以看到， buffer pool 里面其实有一块内存是留给 change buffer 用的。

1）那 change buffer 具体是个什么东西呢？

假设我们就直接执行一条 update table set name = 'yes' where id = 1，如果此时 buffer pool 里没有 id 为 1 的这条数据，那怎么办？

难道把这条数据先加载到 buffer pool 中，然后再执行修改吗？

当然不是，这时候 change buffer 就上场了。

如果当前数据页不在 buffer pool 中，那么 innodb 会把更新操作缓存到 change buffer 中，当下次访问到这条数据后，会把数据页加载到 buffer pool 中，并且应用上 change buffer 里面的变更，这样就保证了数据的一致性。

2）所以 change buffer 有什么好处？

当二级索引页不在 buffer pool 中时，change buffer 可以避免立即从磁盘读取对应索引页导致的昂贵的随机I/O ，对应的更改可以在后面当二级索引页读入 buffer pool 时候被批量应用。

看到我加粗的字体没，二级索引页，没错 change buffer 只能用于二级索引的更改，不适用于主键索引，空间索引以及全文索引。

还有，唯一索引也不行，因为唯一索引需要读取数据然后检查数据的一致性。

3）看到这肯定又有小伙伴关心：更改先缓存在 change buffer 中，假如数据库挂了，更改不是丢了吗？

别怕，change buffer 也是要落盘存储的，从上图我们看到 change buffer 会落盘到系统表空间里面，然后 redo log 也会记录 chang buffer 的修改来保证数据一致性。

至此，想必你对 change buffer 已经有一定了解了吧。它主要用来避免于二级索引页修改产生的随机I/O。如果你的内存够大能装下所有数据，或者二级索引很少，或者你的磁盘是固态的对随机访问影响不大，其实可以关闭 change buffer，因为它也增加了复杂度，当然最终还是得看压测结果。

3，MySQL 中 InnoDB 存储引擎与 MyISAM 存储引擎的区别是什么？

回答重点

MyISAM ：

1）MyISAM 是基于 ISAM 引擎而来的，支持全文检索、数据压缩、空间函数，不支持事务和行级锁，只有表级别锁，它适用于 OLAP 场景，也就是分析类的，基本上都是读取，不会有什么写入动作的场景。

2）MyISAM 的索引也是 B+ 树，只是不像 InnoDB 那种叶子节点会存储完整的数据，MyISAM 的数据是独立于索引单独存储的，所以主键和非主键索引差别不大。

3）MyISAM 不支持崩溃后的安全恢复，而 InnoDB 有个 redolog 可以支持安全恢复。

4）MyISAM 写入性能差。因为锁的粒度太粗了，不支持行锁，只有表锁，所以写入的时候会对整张表加锁。不过有个并发插入的开关，开启之后当数据中间没有空洞的时候，也就是插入的新数据是从末尾插入时，读取数据是不会阻塞的。

InnoDB（MySQL 默认引擎）：

1）InnoDB 支持事务，实现了四种标准的隔离级别，利用 MVCC 来支持高并发，默认事务隔离级别为可重复读，支持行锁，利用行锁+间隙锁提供可重复读级别下防止幻读的能力，支持崩溃后的数据安全恢复。

2）支持外键，不过一般互联网项目都不会用外键的，性能太差，利用业务代码来实现约束即可。

3）由于 InnoDB 使用行级锁定和支持事务，因此在并发性能方面表现较好，特别是在多个用户同时对数据库进行读写操作时。

4）InnoDB 的主键索引称为聚簇索引，也就是数据和索引是放在一起的，这与 MyISAM 有所不同，并且它的辅助索引(非主键索引)只存储索引值与主键，因此当辅助索引不能覆盖查询的列时，需要通过找到的主键再去聚簇索引查询数据，这个过程称之为回表。

4，MySQL 的查询优化器如何选择执行计划？

回答重点

选择执行计划一般需要经过以下几个步骤：

1）将 SQL 语句解析为解析树。

2）预处理，包括语法检查、权限验证、查询重写（例如常量表达式计算、子查询展开等）。

3）生成多个执行计划，并选择成本最低的执行计划。

扩展：成本的计算方式

实际上优化器的内部实现方式比较复杂，我们仅需着重关心它的成本计算，这样能更好地理解 SQL 的执行计划是如何选择的。

MySQL 会根据成本来选择最终应用的索引，这里成本主要包括 IO 成本和 CPU 成本。

IO 成本

所谓的 IO 成本其实指的是把数据从磁盘加载到内存的成本。

MySQL 是以页的形式来读取数据的，即使你只要一条数据，但是实际读取的还是整页的数据，因为根据空间局部性原理，这条数据被读取，那么距离它空间近的数据，也有很大概率会被读取，因此相邻的数据也应该被加载到内存中，所以 MySQL 默认读取一页。

在优化器内，读取一页的成本记为 1。

CPU 成本

数据从磁盘读取到内存后，需要比较、排序等，这些操作需要占用 CPU 资源，因此优化器以扫描的行记为成本，一行的成本为 0.2。

因此最终的成本计算就是看 扫描行数 * 0.2 + 数据长度/16kb = 成本

对比所有索引的成本，最终选择最低成本的索引！

5，在 MySQL 中如何执行数据库备份和恢复操作？

备份数据库和恢复数据库是保障数据安全和业务连续性的重要手段。在 MySQL 中，进行数据库备份和恢复的方法主要有以下几种：

1）备份数据库：
使用 mysqldump 工具，这是最常用的 MySQL 数据库备份方法。命令如下：

mysqldump -u [username] -p[password] [database_name] > [backup_file.sql]

例如：

mysqldump -u root -p mydatabase > /path/to/backup/mydatabase_backup.sql

2）恢复数据库：
使用备份文件进行数据库恢复，也是使用 mysql 命令。命令如下：

mysql -u [username] -p[password] [database_name] < [backup_file.sql]

例如：

mysql -u root -p mydatabase < /path/to/backup/mydatabase_backup.sql

6，如何在 MySQL 中配置和使用慢查询日志？

慢查询日志（Slow Query Log）是 MySQL 提供的一个日志功能，用于记录执行时间超过指定阈值的 SQL 语句。它在数据库性能优化和诊断中非常有用。配置和使用慢查询日志主要分为如下几个步骤：

1）启用慢查询日志：
通过修改 MySQL 配置文件 my.cnf（或 my.ini）中的相关参数来启用慢查询日志。需要设置 slow_query_log 为 1 来启用日志功能。

2）设置阈值时间：
配置 long_query_time 参数来指定慢查询的时间阈值（默认是10秒）。任何执行时间超过该阈值的查询语句都会被记录到慢查询日志中。

3）指定日志文件位置：
可以通过设置 slow_query_log_file 参数来指定慢查询日志文件的存放位置和名称。

4）查看和分析日志：
启用后，慢查询日志会记录慢执行的 SQL 语句，存储相关查询信息。你可以通过 mysqldumpslow 命令或其他工具来查看和分析慢查询日志。

7，MySQL 中如何配置主从复制？

回答重点

在 MySQL 中实现主从复制的主要步骤如下：

1）配置主服务器（Master）

2）配置从服务器（Slave）

3）启动主从复制

主要工作原理：

1）主服务器将对数据库的更新操作记录到二进制日志（Binary Log）中。

2）从服务器从主服务器读取这些二进制日志，并将它们重新播放，即执行这些更新操作，从而使得从服务器的数据与主服务器保持一致。

具体步骤：

配置主服务器（Master）

1）修改 MySQL 配置文件（my.cnf或my.ini）：

[mysqld]
log-bin=mysql-bin
server-id=1

确保 log-bin 和 server-id 已正确设置，server-id 需要是一个唯一的整数。

2）重启 MySQL 服务：

systemctl restart mysqld   # 或者：service mysql restart

3）创建一个复制用户：

CREATE USER 'replica_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
FLUSH PRIVILEGES;

4）获取二进制日志文件的位置与二进制日志文件名：

SHOW MASTER STATUS;

配置从服务器（Slave）

1）修改 MySQL 配置文件：

[mysqld]
server-id=2

每个从服务器的 server-id 需要不同于其他服务器。

2）重启 MySQL 服务：

systemctl restart mysqld   # 或者：service mysql restart

3）在从服务器上执行以下命令，指定主服务器的地址、复制用户、密码、二进制日志文件名和位置：

CHANGE MASTER TO 
MASTER_HOST='master_host_ip',
MASTER_USER='replica_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.xxx',
MASTER_LOG_POS=xxx;

启动主从复制：

1）在从服务器上执行：

START SLAVE;

2）检查从服务器上的复制状态：

SHOW SLAVE STATUS\G;

确保 Slave_IO_Running 和 Slave_SQL_Running 都是 Yes。

8，如何配置和使用 MySQL 的 Performance Schema？

Performance Schema 是 MySQL 中用来监控服务器性能的一套工具。它帮助你分析数据库的性能瓶颈，优化查询和调优配置。我来简要地说明如何配置和使用这一工具。

1）启用 Performance Schema：
默认情况下，很多 MySQL 的版本都会开启 Performance Schema，但你仍然可以通过在 MySQL 配置文件 my.cnf（或 my.ini 在 Windows 系统上）中加入以下配置来确认和启用：

[mysqld]
performance_schema=ON

2）启动 MySQL 服务：
在你配置完 my.cnf 后，重新启动 MySQL 服务，使上述配置生效：

sudo systemctl restart mysql   # 适用于系统d管理的服务
# 或者
/etc/init.d/mysql restart       # 适用于传统的init.d脚本管理的服务

3）查询 Performance Schema 表：
Performance Schema 使用一组特殊的表来存储各种统计和监控数据，这些表位于数据库 performance_schema 中。你可以使用 SQL 语法对这些表进行查询：

USE performance_schema;
SELECT * FROM events_statements_summary_global_by_event_name;

4）配置 Instrumentation：
你可以配置 Performance Schema 的详细程度，选择性地启用或禁用某些监听项目（Instrumentation）。可以通过修改系统变量或在 performance_schema 数据库中直接操作。

UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/synch/mutex/sql/THD::LOCK_thd_data';

5）分析数据：
根据收集到的数据，你可以进行性能分析和调优。常见的做法包括分析等待事件、锁等待、查询性能等。

9，如何在 MySQL 中进行数据库的灾难恢复？

在 MySQL 中进行数据库的灾难恢复，最经典的方法是通过备份和还原，需要使用到 mysqldump、mysql 命令行工具以及一些策略性的步骤。总体步骤如下：

1）创建数据库备份：使用 mysqldump 工具将数据库导出到一个 SQL 文件中。可以在常规操作的闲暇时间定期制作备份，确保备份文件尽量最新。

mysqldump -u [username] -p [dbname] > [backupfile.sql]

2）存储备份文件：将生成的 .sql 文件安全存储在一个可靠的、异地的存储介质上（比如远程服务器或者云存储），以免数据丢失。

3）灾讯恢复：在灾难发生后，要恢复数据库，可以使用 mysql 工具将之前备份的 .sql 文件重新导入数据库。

mysql -u [username] -p [dbname] < [backupfile.sql]

10，如何在 MySQL 中使用 pt-query-digest 工具来分析查询性能？

使用 pt-query-digest 工具分析 MySQL 查询性能可以分为以下几个步骤：

1）安装和配置：
下载并安装 Percona Toolkit，这是一个开源的工具包，其中包含了 pt-query-digest。

2）收集慢查询日志：
确保你的 MySQL 配置里开启了慢查询日志，通过修改 my.cnf 文件中的相关配置进行设置。

3）运行 pt-query-digest：
使用命令行运行 pt-query-digest，指定慢查询日志文件，工具会解析日志并生成一个分析报告。

4）分析输出结果：
理解 pt-query-digest 生成的报告，从中发现哪些查询性能较差，针对这些查询进行优化。

下面我会详细解释每一步。

1）安装和配置

你首先需要从 Percona 官网或者通过包管理工具安装 Percona Toolkit。例如，在 Ubuntu 下可以用：

sudo apt-get install percona-toolkit

安装完成后，通过运行 pt-query-digest --help 来检查安装是否成功。

2）收集慢查询日志

要开始收集慢查询日志，确保在 MySQL 配置文件 my.cnf 中开启了以下配置：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1  # 记录执行时间超过 1 秒的查询

修改配置文件后，重启 MySQL 服务以使配置生效。

3）运行 pt-query-digest

使用以下命令来分析慢查询日志：

pt-query-digest /var/log/mysql/mysql-slow.log > digest_report.txt

该命令会将分析结果输出到 digest_report.txt 文件中。你也可以通过 --output 参数指定其他输出格式，例如 JSON 或者 XML。

4）分析输出结果

生成的报告通常包含以下几个部分：

• Overall Summary：总体概述，显示查询的总数、查询时间和总的执行时间等信息。
• Detailed Query Information：详细的查询信息，按执行时间、次数等对查询进行排序。
• Recommendations：一些优化建议，例如创建索引、修改查询等。

通过这些信息，你可以找到执行时间长、频率高的查询，针对这些查询进行优化。例如：

• 索引优化：为经常被查询的表字段创建合适的索引。
• 查询重构：将复杂查询拆分为更简单的多步查询。

11，MySQL 中的查询缓存是什么？如何配置查询缓存？

查询缓存（Query Cache）是 MySQL 中用于提升查询效率的一个重要特性。当你执行一条 SELECT 语句时，MySQL 会将查询结果缓存在内存中，下次执行相同的查询时，如果数据没有发生变化（即表的相关数据没有更新），MySQL 就会直接返回缓存中的结果，而无需再次执行查询，这样可以大幅提升查询速度。

配置查询缓存的方法主要包括以下几个步骤：

1）在 my.cnf 文件中添加或修改查询缓存的配置参数。例如，我们可以添加以下配置：

[mysqld]
query_cache_type = 1       -- 启用查询缓存
query_cache_size = 64M     -- 设置查询缓存的大小

2）确保服务器在重启后生效这些配置参数。
3）在运行中的 MySQL 实例中，您还可以使用 SQL 命令动态修改这些设置，例如：

SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_size = 67108864;  -- 64M

12，如何在 MySQL 中配置 Galera Cluster 以实现多主复制？

在 MySQL 中配置 Galera Cluster 实现多主复制，关键步骤主要包括以下几个方面：

1）安装 MySQL 和 Galera 支持的版本：
需要确保所有节点的 MySQL 版本一致，并支持 Galera。通常使用的版本是 MySQL 或 MariaDB，这是由于它们内置了 Galera 支持。

2）安装 Galera 的相关组件：
在各个节点上安装 Galera 的相关组件，如 galera 库、galera-arbitrator 和 mysql-wsrep 补丁等。MariaDB 可以集成这些组件，安装会更简单。

3）配置 MySQL（MariaDB）：
配置 my.cnf 文件，包括基本设置和Galera相关的配置项：

[mysqld]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://NODE1_IP,NODE2_IP,NODE3_IP  # 包含所有节点的IP地址
wsrep_node_address=THIS_NODE_IP  # 本节点的IP地址
wsrep_cluster_name=my_galera_cluster
wsrep_node_name=node1  # 本地节点的名称
wsrep_sst_method=rsync  # 文件同步方法，常使用rsync或mariabackup

4）启动和初始化集群：

• 在第一个节点上初始化集群：galera_new_cluster
• 启动其余的节点并加入集群：systemctl start mysql

5）检查集群状态：
确定节点是否都正确加入并同步。例如，使用 SHOW STATUS LIKE 'wsrep_cluster_size'; 检查集群中的节点数。

13，如何在 MySQL 中配置和管理 GTID（Global Transaction ID）以实现数据恢复？

在 MySQL 中，配置和管理 GTID（全局事务标识）用于数据恢复，主要需要完成以下关键步骤：

1）启用 GTID 模式：
你需要在 MySQL 配置文件 my.cnf 中启用 GTID 支持。添加如下设置即可：

[mysqld]
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=ON

这些设置确保 GTID 被启用，并且所有事务是一致的。重启 MySQL 服务使这些配置生效。

2）进行数据备份：
推荐使用 mysqldump 工具进行完全备份。在命令行中，执行以下命令：

mysqldump --all-databases --single-transaction --set-gtid-purged=ON -u root -p > full_backup.sql

这个命令会创建一个数据库快照，包括 GTID 信息。

3）数据恢复：
在需要恢复数据的 MySQL 实例上，执行以下命令导入备份：

mysql -u root -p < full_backup.sql

这样，数据库就会恢复到备份时的状态，包括所有 GTID 信息。

4）配置主从同步（如需要）：
如果你有一个主从架构，确保从库也启用了 GTID 模式并配置好主库连接。具体命令如下：

CHANGE MASTER TO MASTER_HOST='主库地址', MASTER_USER='复制用户名', MASTER_PASSWORD='复制用户密码', MASTER_AUTO_POSITION=1;
START SLAVE;

14，如何在 MySQL 中使用 Percona XtraBackup 工具进行热备份？

要使用 Percona XtraBackup 工具对 MySQL 数据进行热备份，可以按照以下步骤进行：

1）安装 Percona XtraBackup：
确保你的系统已经安装好 Percona XtraBackup 工具。可以通过包管理器（如 apt 或 yum）进行安装。

2）准备备份目录：
创建一个目录来存放备份数据，例如 /backup/mysql.

3）执行备份命令：
在终端中执行以下命令来进行热备份：

xtrabackup --backup --target-dir=/backup/mysql

这将会把 MySQL 数据库的数据备份到 /backup/mysql 目录中。

4）应用日志：
备份完成后，应用日志以确保数据的一致性：

xtrabackup --prepare --target-dir=/backup/mysql

5）验证备份：
可以检查备份目录 (/backup/mysql) 中的文件，以确保备份成功无误。

15，MySQL 中如何使用 mysqlbinlog 工具进行二进制日志恢复？

在 MySQL 中，你可以使用 mysqlbinlog 工具将二进制日志文件恢复到数据库。具体步骤如下：

1）首先，找到你需要恢复的二进制日志文件。通常，这些文件位于 MySQL 数据目录下，文件名格式类似于 mysql-bin.000001。
2）使用 mysqlbinlog 命令读取二进制日志文件并将其输出到一个 SQL 文件。命令如下：

mysqlbinlog /path/to/mysql-bin.000001 > /path/to/output.sql

3）接着，使用 mysql 命令行工具将生成的 SQL 文件恢复到数据库中。命令如下：

mysql -u username -p database_name < /path/to/output.sql

这将把二进制日志中的所有语句重新执行，恢复数据库到指定的时间点。

---

除了这15题外，面试鸭还有更多题目等你解锁！

目前小程序和网页双端都可以刷题，想刷随时都能刷！快来鸭鸭和大家一起刷题吧！