MySQL常见面试题总结

存储引擎是数据库的底层软件组织，数据库管理系统使用存储引擎进行增删改查等操作，不同的存储引擎提供了不同的存储机制、索引技巧、锁定水平等功能。常用的存储引擎主要有 InnoDB 和 MyIASM。

MyIASM

存储结构：数据文件.myd，索引文件.myi，表结构文件。索引文件和数据文件是分离的，索引文件仅保存数据记录的地址（非聚集索引）。

特点：

• 执行读取操作数据块，且占用的内存和存储资源较少。
• 设计简单，数据以紧密格式存储。提供了大量的特性，包括压缩表、空间数据索引等。
• 但不支持事务，行级锁和外键，写操作时需要锁定整张表，效率较低。
• 缓冲池只缓存索引文件，而不缓存数据文件。
• 如果指定了 DELAY_KEY_WRITE 选项（延迟更新索引， MyISAM 独有），在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。崩溃后无法安全恢复。
• 经常把 MyISAM 用于slave层，供客户端去读取。
• MyISAM引擎保存了表的行数，于是当进行Select count(*) from table语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的。可以将MyIASM作为数据库引擎。

InnoDB

是 MySQL 默认的事务型存储引擎，为MySQL提供了 事务支持、回滚、崩溃修复能力、多版本并发控制（MVCC） 的操作，除此之外，InnoDB存储引擎还提供了 插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。

从图中可见，InnoDB存储引擎由内存池，后台线程和磁盘文件三大部分组成。

内存

InnoDB是基于磁盘存储的，并将记录按照页进行管理。为提高性能，加快读写，在内存中使用缓冲池技术。

具体来说，内存中包括缓冲池，额外内存池、重做日志缓冲等。缓冲池中缓存的数据页类型有：索引页、数据页、插入缓冲、自适应哈希索引、锁信息、数据字典等。

缓冲池：读取时，将从磁盘读到的页存放在缓冲池中。下一次再读相同的页时，首先判断该页是否在缓冲池中。若在，直接读取该页。修改时，首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。

重做日志缓冲：存储引擎首先将重做日志放于此，再以一定的频率刷新到磁盘上的重做日志文件。

额外内存池：在对一些数据结构本身的内存进行分配时，需要从额外内存池中进行申请。

缓冲池的管理：如何保证缓冲池中存放的是热点数据？通过 LRU （最近最少使用算法）来管理。即最频繁使用的页在 LRU 列表的前端，而最少使用的页在 LRU 列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放 LRU 列表中尾端的页。

Free List: LRU用来管理已经读取的页，但当数据库刚启动时， LRU 列表是空的，这时页都存放在Free列表中。当需要从缓冲池中分页时，首先从Free List查找是否有可用的空闲页，若有则将该页从FreeList中删除，放入到LRU列表中。

Flush List: 存放脏页列表。

后台线程

后台线程的主要作用是负责刷新内存池中的数据，此外将已修改的数据文件刷新到磁盘文件，同时保证在数据库发生异常的情况下 InnoDB 能恢复到正常状态。 InnoDB 存储引擎是多线程模型，后台有多个不同的后台线程，负责处理不同的任务。

Master Thread：负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲等。

IO Thread: InnoDB 大量使用了 AIO 来处理写IO请求。IO Thread负责这些IO请求的回调。

Purge Thread：回收已经使用并分配的undo页。

磁盘文件

InnoDB的主要的磁盘文件主要分为三大块：一是系统表空间，二是用户表空间，三是redo日志文件和归档文件。

系统表空间和用户表空间

InnoDB 系统表空间包含数据字典(元数据以及相关对象)并且 doublewrite buffer, change buffer, undo logs 的存储区域。系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。系统表空间是一个共享的表空间因为它是被多个表共享的。

设置 innodb_data_file_path 参数之后，所以基于InnoDB存储引擎的表的数据都会记录到该系统表空间中，如果设置了参数innodb_file_per_table，则用户可以将每个基于InnoDB存储引擎的表产生一个独立的用户表空间。用户表空间的命名规则为：表名.ibd。

通过这种方式，用户不用将所有数据都存放于默认的系统表空间中，但是用户表空只存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是存放在默认的表空间中。

重做日志文件和归档文件

InnoDB的重做日志文件(redo log file)，它记录了对于 InnoDB 存储引擎的事务日志。

当InnoDB的数据存储文件发生错误时，InnoDB存储引擎可以使用重做日志文件将数据恢复为正确状态，以此来保证数据的正确性和完整性。

为了得到更高的可靠性，用户可以设置多个镜像日志组，将不同的文件组放在不同的磁盘上，以此来提高重做日志的高可用性。

如果重做日志文件设置的太大，数据丢失时，恢复时可能需要很长的时间；另一方面，如果设置的太小，重做日志文件太小会导致依据checkpoint的检查需要频繁刷新脏页到磁盘中，导致性能的抖动。

特点

• 支持事务

实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻读。

• 数据存储

采用聚集的方式，每张表的存储都是按主键的顺序进行存放。主索引是聚集索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

• 插入缓冲

通常应用程序中，行记录的插入是按照主键递增的顺序进行插入的，因此，不需要磁盘的随机读取。

但对于表中的非聚集辅助索引，有可能是非顺序的、不唯一的（数据库中可能有两个人名相同，但不在一页内，因为是按ID顺序存放的），对于按辅助索引进行插入和更新的操作，是离散的。

对于非聚集且不是唯一的索引的插入或更新操作，不是每一次都直接插入到索引页中，而是先插入到内存中。如果该索引页在缓冲池中，直接插入；否则，先将其放入插入缓冲区中，再以一定的频率和索引页合并，这时，就可以将同一个索引页中的多个插入合并到一个IO操作中，大大提高写性能。

带来的问题：
1）可能导致数据库宕机后实例恢复时间变长。如果应用程序执行大量的插入和更新操作，且涉及非唯一的聚集索引，一旦出现宕机，这时就有大量内存中的插入缓冲区数据没有合并至索引页中，导致实例恢复时间会很长。
2）在写密集的情况下，插入缓冲会占用过多的缓冲池内存，默认情况下最大可以占用1/2，这在实际应用中会带来一定的问题。

• 两次写：提高可靠性

当存储引擎正在从内存向磁盘写一个数据页时，如果发生宕机，会产生部分写失效，导致数据丢失。此时无法通过重做日志恢复，因为重做日志记录的是对页的物理修改，如果页本身已经损坏，重做日志也无能为力。

为解决这个问题，在脏页刷新时，并不直接写磁盘，而是将脏页先复制到内存中的 double writer buffer 中，再分两次，每次1MB顺序写入共享表空间的磁盘，然后马上同步磁盘。如果写入过程中发生崩溃，存储引擎可以从共享表空间找到该页的副本，再应用重做日志。

• 自适应哈希索引

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希表可以带来速度提升，则建立哈希索引，称之为自适应哈希索引（AHI）。

• 刷新邻接页

当刷新一个脏页时，存储引擎会检测该页所在区的所有页，如果是脏页，那么一起刷新。

• 锁

默认行锁，也支持表锁。

• 查询缓存（8.0后移除）

两个查询在任何字符上的不同都会导致不命中。查询中包含用户自定义函数、用户变量、存储函数、临时表等，查询不会被缓存。

启动、关闭与恢复

关闭时刷新磁盘或写入日志文件，开启时进行恢复操作等。

MyISAM 和 InnoDB 比较

• 是否支持行级锁 : MyISAM 只有表级锁，而 InnoDB 支持行级锁和表级锁，默认为行级锁。
• 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性，其执行速度比 InnoDB 类型更快，但是不提供事务支持。 InnoDB 提供事务支持事务，外键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力的事务安全型表。 MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
• 是否支持外键： MyISAM 不支持，而 InnoDB 支持。
• 是否支持MVCC ：仅 InnoDB 支持。应对高并发事务， MVCC（多版本并发控制，见四）比单纯的加锁更高效；MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作；MVCC可以使用乐观锁和悲观锁来实现；各数据库中MVCC实现并不统一。
• 其它特性：MyISAM 支持压缩表和空间数据索引。

InnoDB逻辑存储结构

从 InnoDB 存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称为表空间（ tablespace ）。表空间又由段（Segment）、区（extent）、页（Page）组成。

• 表空间

在默认情况下， InnoDB 有一个共享表空间 ibdata1 ，即所有数据都存放在这个表空间内。如果启动参数 innodb_file_per_table ，则每张表的数据可以单独放到一个表空间内。但每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页，其他类的数据，如回滚（undo）信息，插入缓冲索引页、系统事务信息、二次写缓冲等还是存放在原来的共享表空间内。

• 段

表空间由各个段组成，常见的段有数据段、索引段、回滚段等。

• 区

由连续页组成的空间，大小1 MB。默认情况下，页大小为16 KB，一个区中共有64个连续的页。

• 页

磁盘管理的最小单位。

File Header：记录页的头信息。38字节。表空间中页的偏移值，当前页的上下页、页的类型等。

Page Header：记录页的状态信息。56字节。该页中记录的数量，当前页在索引中的位置，属于哪个索引等。

Infimum 和 Supremum Record ：限定记录的边界。

User Record和Free Space：实际存储行记录的内容和空闲链表。

Page Directory：记录的相对位置。

File Trailer：检测页是否已经完整地写入磁盘。

各个数据页可以组成一个双向链表。每个数据页中的记录又可以组成一个单向链表。

• 每个数据页都会为存储在它里边的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录；
• 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以说，如果我们写 select * from user where indexname = ‘xxx’ 这样没有进行任何优化的语句，默认会这样做：

• 定位到记录所在的页：需要遍历双向链表，找到所在的页
• 从所在的页内中查找相应的记录：由于不是根据主键查询，只能遍历所在页的单链表了

很明显，在数据量很大的情况下这样查找会很慢！时间复杂度为O（n）。

MySQL、SQL server之间的区别

• 本质区别是它们所遵循的基本原则：SQL server是狭隘的、保守的存储引擎；MySQL是开放的、可扩展的存储引擎。MySQL可以提供更多选择，如Heap，MyISAM，InnoDB等。MySQL并不完全支持陌生的关键词，所以它就比SQL server服务器少一些相关的数据库。MySQL也缺乏某些存储程序的功能。
• 发行费用上：MySQL不全是免费，但很便宜；SQL server相对较贵（尤其是用于商业产品开发）。
• 性能方面：MySQL性能更好。MySQL可以运行于Windows系统而不会发生冲突，在UNIX或类似UNIX系统上运行则更好。SQL server的稳定性要比它的竞争对手强很多，但是增加了额外复杂操作、磁盘存储、内存损耗等。
• 安全功能：MySQL更安全。MySQL有一个用于改变数据的二进制日志。因为它是二进制，这一日志能够快速地从主机上复制数据到客户机上。即使服务器崩溃，这一二进制日志也会保持完整，而且复制的部分也不会受到损坏。
• 恢复性：先进的SQL server服务器。SQL server服务器能够时刻监测数据交换点并能够把数据库损坏的过程保存下来。