MySQL面试题大全

特点	InnoDB	MyISAM	Memory	Archive
事务支持	支持	不支持	不支持	不支持
索引支持	支持，聚簇索引	支持，非聚簇索引	支持	不支持
外键支持	支持	不支持	不支持	不支持
锁颗粒	行锁	表锁	表锁	行锁
优势	良好的事务处理、崩溃修复能力和并发控制	占用空间小，处理速度快	处理速度极快	压缩比非常高
劣势	读写效率较差，占用的数据空间相对较大	不支持事务的完整性和并发性	存储受内存限制，重启后数据丢失	不支持update和delete，不支持事务
适用场景	银行、售票等大部分业务系统	读多写少的场景	只需用内存存取	记录日志或历史信息等数据归档

Q：MySQL有哪些持久化文件？

A：

表结构定义文件：以.frm为后缀的文件，该文件记录了该表的表结构定义。

表空间文件：

参数innodb_file_per_table为OFF，只会有一个名为ibdata1的默认表空间文件，存放所有表的数据、索引等信息

参数innodb_file_per_table为ON，每个表都会有一个独立的表空间文件，文件名为表名.ibd，存放各自表的数据、索引等信息，其余信息还是放在默认表空间

重做日志文件：名为ib_logfile0和ib_logfile1的文件，记录了对于InnoDB存储引擎的事务日志,记录的是关于每个页(Page)更改的物理情况，主要用于保证事务的原子性，只用于InnoDB存储引擎

binlog文件：以事件形式记录了对MySQL数据库执行更改的所有操作，任何存储引擎都会生成该文件

表空间文件相关：

Q：表空间文件如何组织存储？

A：表空间文件由段、区、页、行组成，InnoDB通过B+树结构将页组织起来

段(Segment)：分为索引段，数据段，回滚段等。其中索引段就是非叶子结点部分，而数据段就是叶子结点部分，回滚段用于数据的回滚和多版本控制。一个段包含256个区(256M大小)

区(Extent)：是页的集合，一个区包含64个连续的页，默认大小为 1MB (64*16K)。B+树通过双向链表串联数据页，如果以页分配存储空间，链表相邻两页的物理位置并不连续，则磁盘查询时会产生大量随机IO，所以在分配存储空间时，按照区为单位进行分配，使得链表相邻的页物理位置也相邻，从而使用较快的顺序IO

页(Page)：是行的集合，默认页大小为16KB，InnoDB以页为单位将数据整体读入内存，B+树上的一个叶子节点就是一页，页与页之间通过双向链表串联起来，页通过页目录提升数据查询效率（页目录：InnonDB将页中所有记录划分多若干组，每组4~8条记录，页目录会存储每组最后一条记录与第一条记录的地址偏移量）。页分为数据页、Undo日志页、溢出页

行(Row)：一行就是数据库的一条记录，有Redundant、Compact、Dynamic和Compress四种方式存储

Redundant：最早的存储格式，已被废弃

Compact：存储格式为：变长字段长度列表、Null值列表、数据头、column01的值、column02的值、column0n的值

Dynamic：MySQL5.7版本后的默认行存储格式，格式与Compact一致，除了溢出页格式不同

Compress：基本功能与Dynamic一样，字段内容以压缩的形式存储在页中

Q：Null值如何存储？

A：行中的Null值列表记录可为Null的字段的情况

用bit位标识是否为Null，1为Null、0不为Null，多个Null字段在列表中按逆序存储

Q：varchar(n)的n最大多少？

A：行中的变长字段长度列表记录变长字段的长度

变长字段长度列表的长度最大不超过2字节，即变长字段长度最大是65525，减去Null值列表占用的1字节和变长字段长度列表占用的2字节，n最长为65532字节

如有多个变长字段，则Null值列表占用字节+变长字段长度列表占用字节+所有数据字段占用字节<=65535

Q：什么是行溢出？

A：数据页的默认大小是16KB，但是某些字段的大小可以远大于16KB，如varchar(N)，当一行数据超过16KB，会将数据存放在溢出页中

其中Compact格式：在页储存前768个字节，同时有一个20字节的指针，指向存储超了部分的溢出页

Dynamic格式：只会在页存储一个20字节的指针，指向存储大字段所有内容的溢出页，该种方式查询效率更高

索引相关：

Q：为什么需要索引？

A：索引最重要的作用就是加快数据的检索速度，但是在对表中数据进行增删改时，需动态维护索引，从而降低增删改执行效率，并增加物理空间的占用

Q：什么是聚簇索引、非聚簇索引、回表、覆盖索引？

A：

聚簇索引：B+树的叶子节点存放整行数据，索引与数据是存放在一起的，我们称为聚簇索引，通常InnoDB的主键索引便是聚簇索引
非聚簇索引：B+树的叶子节点存放数据位置或ID，索引与数据分开存放，我们称为非聚簇索引，通常InnoDB的普通索引和MyISAM的索引都是非聚簇索引
我们通过聚簇索引查找数据时，可直接查找到对应行数据返回。而通过非聚簇索引查找数据时，则需先通过非聚簇索引树查找到对应的主键Id，再通过主键Id在聚簇索引树查找到行数据返回。对于InnoDB，即使你没创建主键索引，也会默认隐式创建一个主键索引。
回表：当通过非聚簇索引查询行记录，在非聚簇索引中查到主键ID后，还需通过聚簇索引查询，这便是回表，如查询语句已全部命中索引，则无需回表，如select age from user where age > 10
覆盖索引：当查询信息在非聚簇索引中均可取得，此时便无需查询聚簇索引

Q：索引为什么使用B+树？

A：

红黑树和二叉树的树高随数据量增大而增加，IO代价高

B+树相比B树的特点

B+树的数据全部存放在叶子节点，叶子节点之间通过双向链表链接，可以快速的进行范围查询，B树的数据存放在所有节点，不方便范围查询
B+树的非叶子节点不存放数据，只记录索引值，故一个节点可存放更多记录，降低树的高度，从而减少系统IO次数

我们还可以选用Hash实现，实现方式类似Java的HashMap，使用数组+单链表的形式实现，Hash索引没办法实现范围查找。

Q：索引为什么会失效？

A：用了索引条件，但是却走了全表查询，一般原因如下：

字符串 like 时左边是通配符。类似于’%aaa’
列参与了数学运算或者函数
存在隐式转换
使用不等于查询
当优化器分析全表扫描比使用索引快的时候，一般由于条件查询出的数据非常多
当使用联合索引，未使用第一个条件

Buffer Pool相关：

Q：什么是Buffer Pool？

A：为了减少磁盘IO次数，MySQL引入缓冲池概念，以页为单位，采用链表数据结构管理，将最热的数据页和索引页进行缓存，从而提高访问效率。

Buffer Pool默认大小是128M，有三个链表结构：FREE链表、LRU链表和FLUSH链表，InnonDB通过这三个链表控制页的更新和淘汰

FREE链表：用于帮助找到空闲的缓存页，是一个双向链表

LRU链表：当数据从磁盘读取到内存后，就会从FREE链表获取一个空闲页，放入LRU链表中，LRU链表通过LRU算法，将访问少的数据进行淘汰腾出空间

FLUSH链表：当数据被修改时，会先更新Buffer Pool中的缓存页，此时该页会被放入FLUSH链表，该链表用于定位被更新需要刷盘的缓存页

Q：LRU算法存在什么问题？MySQL怎么解决?

A：LRU算法存在【预读失效】、【Buffer Pool污染】两大问题

预读失效：

预读：磁盘数据读取到内存，是按页读取数据，因为数据访问通常遵循“集中读写”原则，读取数据后，大概率会继续读取附近的数据，按页读取数据可以减少磁盘IO，提高效率

但预读进来的数据一直没有被访问，即预读失效，预读的数据会放到LRU链表头部，却将需要被使用的数据挤到链表尾部，从而被淘汰，那缓存的命中率就会大大降低

预读失效解决方案：

划分old和young两个区域，预读的页会加入到old区域头部，当页被真正访问时，才将页插入young区域的头部，如果预读的页一直没有被访问，会一直存在old区域，直到被移除，不会影响young区域中的热点数据

Buffer Pool污染：

Buffer Pool的大小是有限的，如果加载大量数据，比如全表扫描，就会将Buffer Pool里所有页都替换出去，导致原本的热数据被淘汰，下次访问又要重新读取磁盘，导致性能下降

Buffer Pool污染解决方案：

缓冲池加入了一个“老生代停留时间窗口”的机制，插入old区域头部的页，即使立刻被访问，并不会立刻放入young区域头部；短时间内被大量加载的页，并不会立刻插入young区域头部，而是优先淘汰那些短期内仅仅访问了一次的页。只有满足“被访问”并且“在old区域停留时间”大于T，才会被放入young区域头部；

Q：一条SQL语句的完整执行流程？

A：

1.客户端请求

2.连接器（验证用户身份，给予权限）

3.查询缓存（存在缓存则直接返回，不存在则执行后续操作）

4.分析器（对SQL进行词法分析和语法分析操作）

5.优化器（主要对执行的sql优化选择最优的执行方案方法）

6.执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）

7.去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

Q：MySQL数据存取的过程？

A：MySQL会去引擎层存取数据

取数过程：

1、检查Buffer Pool中是否有相关数据页被缓存

2、如缓存不存在相关页，则从磁盘获取数据页，并存至Buffer Pool中

3、如缓存存在相关页，则从磁盘中获取数据页

存数过程：

1、检查Buffer Pool中是否有相关数据页被缓存

2、如缓存不存在相关页，使用写缓冲（change buffer）来做更新操作

3、如缓存存在相关页，则更新Buffer Pool的缓冲页

4、将更新操作顺序写入Redo Log

5、线程将脏页更新至磁盘

Q：如何确定数据页是否被缓存？

A：

Q：InnonDB怎么定位查询的是哪笔数据？

A：

Q：为什么需要写缓存？

A：

事务与锁相关：

Q：MySQL事务实现原理

A：

事务是通过原子性、隔离性、持久性来保证一致性

InnoDB通过undo实现原子性、MVVC实现隔离性、redo实现持久性

Q：MySQL事务的隔离级别？

A：

read uncommited(读取未提交内容)：在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。读取未提交的数据，也被称之为脏读（Dirty Read）
read committed(读取提交内容)：这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。一个事务只能看见已经提交事务所做的改变。可解决脏读
repeatable read(可重读)：这是MySQL的默认事务隔离级别，同一事务的多个实例在并发读取数据时，会看到同样的数据。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。可解决脏读、不可重复读
serializable(可串行化) ：这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。可解决脏读、不可重复读、幻读。

Q：MVVC如何实现隔离性？

A：

高可用相关：

Q：MySQL的读写分离如何实现？

A：

Q：读写分离如何避免过期读？

A：

强制走主库方案
sleep 方案
判断主备无延迟方案
配合 semi-sync 方案
等主库位点方案
GTID 方案。
实际生产中，先客户端对请求做分类，区分哪些请求可以接受过期读，而哪些请求完全不能接受过期读；然后，对于不能接受过期读的语句，再使用等 GTID 或等位点的方案。

Q：MySQL如何实现高可用？

A：MySQL单机部署时，可能因为机器宕机导致整个数据库层不可用

解决方案：binlog复制

Q：MySQL集群方案？

A：

Q：MySQL数据库如何进行恢复？

A：

MySQL使用过程中可能会被误删，此时就要对数据库的数据进行恢复

解决方案：MySQL数据库备份恢复方案

优化相关：

Q：MySQL性能优化方案？

A：通过合理安排资源，调整系统参数使MySQL运行更快、更节省资源

解决方案：MySQL性能优化方案

Q：如何识别慢SQL？如何进行优化？

A：

原因：从大到小可分为四种情况

MySQL 数据库本身被堵住了，比如：系统或网络资源不够。
SQL 语句被堵住了，比如：表锁，行锁等，导致存储引擎不执行对应的 SQL 语句。
确实是索引使用不当，没有走索引。
表中数据的特点导致的，走了索引，但回表次数庞大。
解决：

考虑采用 force index 强行选择一个索引
考虑修改语句，引导 MySQL 使用我们期望的索引。比如把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。
第三种方法是，在有些场景下，可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。
如果确定是索引根本没必要，可以考虑删除索引。