MySQL面试题--最最最详细MySQL面试题和解答

MySQL各版本以及主流分支有何区别?

1 MySQL 5.5的特性

引入了半同步复制
MySQL 5.5之前的版本，都是采用的异步复制，主库的事务提交不会管从库的同步进度。如果主从复制有延迟的情况，主库宕机，那就会导致数据丢失。在5.5就引入了半同步复制，需要主库提交事务后，等待至少一个从库已经接收到并写入了该事务的日志，主库才返回给客户端。

Buffer Pool可拆分为多个Instances
每个Instance可以独立地管理自己的内存池，并且可以设定不同的大小和比例。这种方式可以更好地利用多核CPU和大内存，提高查询性能和效率。
元数据锁
MySQL在5.5版本中引入了Metadata Locks。当对一个表做增删改查操作的时候，加 MDL 读锁:当要对表做结构变更操作的时候，加 MDL 写锁
读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。
默认存储引擎改成了InnoDB
MySQL 5.5.5版本之后，默认存储引擎改成了InnoDB

2 MySQL 5.6的特性

支持GTID复制
更容易地跟踪和识别事务，知道事务在哪个节点上提交的
安全性更高
并且高可用切换的时候更方便，不用跟之前基于位点的复制，去找Binlog文件和位点。

MySQL 8.0相对于5.7的有哪些改进?

事务性数据字典

而8.0将元数据信息统一存储到数据字典中，更容易实现DDL变更的原子操作快速加列

MySQL 8.0 可以实现大表秒级加列

不可见索引

从MySQL 8.0开始，支持不可见索引，优化器不会主动使用这种索引。比如我们想知道增加和删除索引对查询性能的影响，就可以考虑使用不可见索引。这个在调试SQL的时候比较有用，

角色管理

MySQL 8.0引入了角色管理，非常方便用户不同，权限相同的场景

hash ioin

可以用来优化没有使用索引的关联查询

你使用过哪些版本的MySQL? 你觉得太在哪些地方需要改进?

这个根据自己的实际情况讲

比如使用了MySQL 5.6，尽管有基于库的并行复制，但是实例里面就一个业务库，实际也没啥效果，还是会延迟很高.MySQL 5.6，想要调大类似innodb _buffer_pool size等相关参数，还需要重启MySQL。8.0之前，大表添加字段，都很慢，并且直接执行，对数据库影响很大，比如元数据锁，只能通过pt-online-schema-change来执行。8.0之后，可以对大表秒级加列。

说下你知道的MySQL分支和他们的特点

Percona Server

支持线程池

extra port

基于Gelera 形成PXC

MariaDB

支持更多的存储引擎

比官方版本更早实现HashJoin、并行复制、虚拟列、Thread Pool

2-常见MySQL存储引擎有哪些? 区别是什么?

1 常见存储引擎

InnoDB

默认的存储引擎，InnoDB具有支持事务、行锁、MVCC等特点。

MyISAM

而MyISAM因为不支持事务，并且是表锁设定，在5.5之后的版本，基本不怎么使用了，也不太建议使用。不但并发能力差，也容易出现表损坏和数据丢失的情况

备份困难

比如使用mysgldump等逻辑备份工具，在备份某张表的期间，会对整个表进行锁定。即使在从库备份，也会导致主从延迟

TokuDB

TokuDB是一种高度可扩展、零维护停机时间的 MVSOL存引，目前是Percona在维护，不过从Percona Server for MySQL 8.0.28-19开始，不再支持 TokuDB 存储引擎。

TokuDB支持高压缩比，快速写入和删除等特点，可以用在海量数据场景，或者需要快速进行插入和删除操作的场景

Columnstore

MariaDB支持的存储引警

Columnstore因为采用的是列式存储、以及自动压缩，所以比较适用大数据场景

MEMORY

MEMORY存储擎因为数据全在内存中，所以读取块，但是重启MySQL数据就会丢失。并且不支持事务

所以类似临时表或者缓存表可以考虑使用MEMORY存储引擎，线上的业务数据，就不太建议MEMORY存储引擎了。

CSV存储引擎数据文件就存储为CSV格式，并且能直接查看，这个常用在数据导出或者临时存储的场景。2 拓展面试题

对比InnoDB和MyISAM存储引擎的区别和适用场景

InnoDB支持事务、MyISAM不支持事务

InnoDB是行锁，MyISAM是表锁

InnoDB具有崩溃恢复能力，MyISAM不具备崩溃恢复能力InnoDB:适合于高并发的 OLTP (在线事务处理)应用，对于大量写入和更新操作的应用性能更好，MyISAM: 适合读密集型应用，例如数据仓库或者只读的报表生成。当然，现在，类似数仓或者只读报表，可能其他的一些数据库更适合比如ClickHouse。

为什么从MySQL5.5开始，MySQL默认的存储引擎改成了InnoDB?

这个也是直接讲下InnoDB存储引擎的一些优势就行，跟前面这个问题有点类似，比如:因为InnoDB存储引擎支持事务，具备崩溃恢复的能力，更新数据的时候，锁的范围更小。

假如你管理的业务线，有多张MyISAM表，你会怎么处理?

我会考虑把这些MyISAM表全都转成InnoDB

当然要说服研发，说出MyISAM的不足，比如表锁，极端情况可能丢数据，不支持事务并且让他们了解，不需要调整任何代码，只要我们这边直接把存储引擎改下就行用法是:

ALTER TABLE table name ENGINE=InnoDB;

Explain需要注意哪些项?如何进行SQL分析?

根据某个条件查询一行数据，但是type列为all，应该怎样优化?

通常表示这个条件字段没索引，这个时候就需要添加索引当然，有时候，也有可能这个字段有索引，而SQL没有正常使用索引导致的,比如:

对条件字段进行了函数操作或者计算操作

隐式转换

等情况。

执行计划中，Extra显示Using filesort，表示什么?

通常是排序字段没索引，将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。

关联查询中被驱动表的关联字段没索引，通常在执行计划中会显示什么?

通常Explain中，key列显示为空，表示使用不到任何索引

rows这列，分别为这两张表的总行数

Extra，这一列，如果是MySQL 8.0.20 之前的版本:

会显示Using where; Using join buffer (Block Nested Loop)

根据某个条件查询一行数据，但是type列为all，应该怎样优化?通常表示这个条件字段没索引，这个时候就需要添加索引当然，有时候，也有可能这个字段有索引，而SQL没有正常使用索引导致的,比如:

对条件字段进行了函数操作或者计算操作

隐式转换

等情况。

执行计划中，Extra显示Using filesort，表示什么?

通常是排序字段没索引，将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序被驱动表的关联字段没索引，通常在执行计划中会显示什么?关联查询中，

通常Explain中，key列显示为空，表示使用不到任何索引

rows这列，分别为这两张表的总行数

Extra，这一列，如果是MySQL 8.0.20 之前的版本:会显示Using where; Using join buffer (Block Nested Loop)

根据某个条件查询一行数据，但是type列为all，应该怎样优化?通常表示这个条件字段没索引，这个时候就需要添加索引当然，有时候，也有可能这个字段有索引，而SQL没有正常使用索引导致的,比如:

对条件字段进行了函数操作或者计算操作

隐式转换

等情况。

执行计划中，Extra显示Using filesort，表示什么?

通常是排序字段没索引，将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序被驱动表的关联字段没索引，通常在执行计划中会显示什么?关联查询中，

通常Explain中，key列显示为空，表示使用不到任何索引

rows这列，分别为这两张表的总行数

Extra，这一列，如果是MySQL 8.0.20 之前的版本:

会显示Using where; Using join buffer (Block Nested Loop)

如果是MySQL 8.0.20及之后的版本:

会显示Using where; Using join buffer (Hash join)

主从复制

30.4-主从复制的原理? 主从复制常遇到的错误? 多线程复制的演进是怎样的?

1 主从复制的原理

当主库有写操作时(比如insert、update，delete) ，会记录到主库的Binlog中从库通过I0线程读取主库的Binlog里面的内容，传给从库的Relay Log (中继日志)从库的sql线程负责读取它的relay log里的信息并应用到数据库中

2 主从复制常遇到的错误

server id重复，解决办法: 主从设置不同的server id

Slave l0_Running的值是Connecting，解决办法，打通主从机器的网络，并保证从库能访问主库的3306端口从库延迟一直增加，执行stop slave的时候，会出现等待原因，可能从库磁盘空间满了，这个时候，删除一些不要的文件或者文件夹，释放出空间就行

主库要新增的内容，从库有了

解决办法:从库在复制中跳过这条记录

主库要更新的记录从库没有

关闭同步，找到原记录，在从库补数据，在开启同步

找不到主库Binlog位点

如果有其他正常的从库，可以把报错的从库接到这个从库，复制缺失的事务

如果没有其他从库，那建议是重建复制。

3 多线程复制的演进是怎样的?

在 MySQL 5.6 之前，MySQL 只支持单线程复制。

从 MySQL 5.6 版本开始，支持并行复制策略，但是只支持库级别的。如果表都集中在一个 DB 里，或者热点表集中在一个库中，那就没有什么效果了。

MySQL 5.7的并行复制

由参数: slave-parallel-type 控制并行复制策略

配置为 DATABASE，表示使用 MySQL 5.6 版本的按库并行策略

配置为 LOGICAL CLOCK，同时处于 prepare 状态的事务，在备库执行时是可以并行的:处于 prepare 状态的事务，与处于 commit 状态的事务之间，在备库执行时也是可以并行的。

MySQL 5.7.22的并行复制

MySQL 5.7.22 版本里，MySQL 增加了一个新的并行复制策略，基于 WRITESET 的并行复制相应地，新增了一个参数 binlog-transaction-dependency-tracking，用来控制是否启用这个新策略。这个参数的可选值有以下三种。

COMMIT ORDER，表示的就是前面介绍的，根据同时进入 prepare 和 commit 来判断是否可以并行的策略

WRITESET，表示的是对于事务涉及更新的每一行，计算出这一行的 hash 值，组成集合writeset。如果两个事务没有操作相同的行，也就是说它们的 writeset 没有交集，就可以并行。

WRITESET SESSION，是在 WRITESET 的基础上多了一个约束，即在库上同一个线程先后执行的两人事务，在备库执行的时候，要保证相同的先后顺序。

5 哪些情况可能会导致复制延迟?

主库增删改并发大

大表在做DDL

从库备份导致延迟

大事务

从库配置差

6 怎样判断复制是否有延迟?

Seconds_Behind_Master

种常规的方法就是 show slave status 查看 Seconds_Behind-Master，这个参数表示从库延迟的秒数。

如果是0，表示可能没有延迟。这里为什么是可能呢?

当从库正在主动外理更新时，此字段显示从库上的当前时间截与从库上当前正在处理事件对应的主库上记录的原始时间戳之间的差异。

当副本上当前没有处理任何事件时，该值为 0

在某些情况下，Seconds_Behind_Master 并不一定准确。比如网络中断时，Seconds_Behind_Master =0，并不能代表主从无延迟。

因此，有比这个更准确的一种方法: 对比位点或 GTID。

对比位点

如果是基于位点的复制，则判断 Master Log File 跟 Relay_Master_Log_File 是否相等，如果 Relay_Master_Log_File 落后 Master_Log_File，则表示主从存在延迟

其中

Master_Log_File 表示IO 线程正在读取的主库 binlog 文件名

Relay_Master_Log_File 表示SQL 线程最近执行的事务对应的主库 binlog 文件名

或者判断 Read_Master_Log_Pos 跟 Exec_Master_Log_Pos 是否相等，如果后者落后前者很多，则表示延迟比较高

其中

Read_Master_Log_Pos 表示IO 线程正在读取的主库 binlog 文件中的位点

Exec_Master_Log_Pos 表示 SQL线程最近读取和执行的事务对应