MYSQL 必考面试题10道（答案解释）

1

MySQL偶尔会出现OOM（内存溢出）现象,导致MySQl服务重启，以下哪种方式能有效缓解OOM的情况发生（）

A.适当调低innodb_buffer_pool_size大小，5.7之前需要重启MySQL服务。 B.降低并发，避免查询分组排序和更多table cache。 C.开启swap。 D.将一个实例下库表拆成多个库表（分库分表）。

答案：ABC 解释：D.分库分表会open更多的表，占用更多系统内存。可以分库分表但不要放在一个主机和实例下。

2

INSERT BUFFER即合并插入缓存，从innodb 1.0.x版本开始引入CHANGE BUFFER，下面关于insert buffer说法正确的是（）

A.合并插入只适用非唯一的辅助索引。 B.合并插入的目的是使随机io变顺序io，提升性能。 C.合并插入的频率为每1s和10s都会进行。 D.合并插入由master thread控制。

答案：ABD 解释：C.合并插入并不是每秒都会发生，需要判断前1秒的IO次数是否小于5，如果小于5则认为当前压力较小，可以执行合并插入操作。每10秒，总是合并最多5个插入缓冲。

3

在MySQL的众多存储引擎中，只有InnoDB支持事务，关于事物隔离级别，以下说法错误的是（）

A.Read uncommitted、Read committed 、Repeatable read、Serializable四种隔离级别并行性能依次降低，安全性依次提高。 B.脏读是某一事务读取了另外一个事务未提交的数据，不可重复读是读取了其他事务提交的数据，脏读和不可重复读都可以通过事物隔离级别控制。 C.RR隔离级别，只能返回比当前事务早的提交插入、更新、删除值。 D.RR和RC隔离级别都存在幻读，无法避免。

答案：CD 解释：C.RR隔离级别，只能返回比当前事务早的提交插入、更新值和比当前事务晚的删除值。

D.RR和RC隔离级别都存在幻读，RR隔离级别幻读可以通过next-key lock避免。

4

Innodb为实现MVCC所使用的内部快照采用read view方式，关于read view 说法正确的是（）

A.RR是每个事务重建读视图，整个事务存在期间都用这个视图。

B.RC是每条SQL创建读视图，在每个SQL语句开始执行的时候创建的，隔离作用域仅限该条SQL语句。 C.Read uncommitted是不创建读视图的，直接返回记录上的最新值。 D.read view 存在物理结构，跟普通视图一样，记录当时事物执行时的数据信息。

答案：ABC 解释：D.read view为实现MVCC所使用的内部快照，没有物理结构，即在某一时刻给事物系统做快照，记录当时事物系统的状态，然后所有的读操作根据其事务ID与快照中事物系统的状态作比较，以此判断read view对于事务的可见性。

5

Undo log回滚日志，提供回滚操作，是为了满足事物的原子性，关于undo说法正确的是（）

A.MySQL5.6版本以后支持独立的undo表空间，可设置多个表空间。8.0之前默认为128个回滚段，启动后不能动态修改。5.7以后可以清理undo log。

B.undo log是为了满足事务的原子性，也可以用来辅助完成事务的持久化。 C.undo由purge cleaner thread控制，负责回收已经使用并分配的undo页，事物提交时，会立即删除undo log。 D.insert事物提交时，undo log直接删除，不进行purge操作；update和delete操作会放入undo log链表，等待purge线程删除。 答案：ABD 解释：C.undo由purge thread控制，purge cleaner thread线程是负责刷脏页的。事物提交时，不会立即删除undo log，RR隔离级别下，事物读取的都是开启事物时最新提交的行版本，只要该事物不结束，该版本就不能删除。而是将undo log放到删除列表，再通过purge thread删除。

6

关于MySQL Innodb 行记录隐藏字段的说法正确的是（）

A.每行记录都有三个隐藏字段：事务ID（DB_TRX_ID）、回滚指针（DB_ROLL_PTR）、隐藏的ID（DB_ROW_ID）。

B.DB_TRX_ID记录的是行变化发生时的版本号，而不是事件实际发生的时间。 C.DB_ROLL_PTR为指向写到rollback segment（回滚段）的一条undo log记录。 D.DB_ROW_ID用于索引中，主键和唯一索引就是利用这个值实现。

答案：ABC 解释：D.如果表中没有主键或合适的唯一索引,也就是无法生成聚簇索引的时候,InnoDB会帮我们自动生成聚集索引,聚簇索引会使用DB_ROW_ID的值来作为主键;如果表中有主键或者合适的唯一索引,那么聚簇索引中也就不会包含 DB_ROW_ID了。

7

MySQL默认页的大小为16K，操作系统为4K，在意外掉电时很容易引起部分写失败问题，所以引入doablewrite来避免此问题，innodb_doublewrite参数可以控制是否开启。以下关于double write的说法，正确的是（）

A.脏页和redo log都需要写doublewrite，都是为了解决部分写失败问题。 B.doublewrite由两部分组成，一部分为内存中的doublewrite buffer，另一部分为磁盘上共享表空间中，大小都为2M。 C.脏页刷新过程为先写入doublewrite buffer中，再分两次，每次1M写入共享表空间中，然后再将doublewrite buffe脏页离散写入各个表空间文件中。 D.如果在写入共享表空间时失败，在恢复时会通过重做日志重新写入到doublewrite buffer；如果共享表空间写入成功，但在写入磁盘时失败，在恢复时会用doublewrite空间中的数据来恢复异常页面的数据。

答案：BCD

解释：A.文件系统是以4k为单位写入，磁盘是以扇区（512字节）为单位写入，redolog写入的单位就是512字节，也就是磁盘IO的最小单位。redo log是物理逻辑日志，并不是纯粹物理日志，所以不存在数据页损坏问题。

8

MySQL的二进制日志binlog可以说是MySQL最重要的日志，关于binlog说法正确的是（）

A.当指定了binlog大小，如果有大事物，一个binlog放不下，会放到下一个binlog中，而不会导致binlog超过指定大小。

B.如果主库binlog格式为statement，主库执行分页删除时，可能造成从库数据不一致。 C.主从复制时，binlog dump线程将binlog cache中event推送给从库，而不是从binlog file中even推送给从库。 D.从库存在两个用户为system user的线程，分别为复制的io thread和sql thread。当从库执行stop slave时，不会主动通知主库的binlog dump线程。

答案：BD

解释：A.大事物如果一个binlog文件放不下也不会放到新的binlog文件，而是会超过指定大小继续增大。

B.binlog为statement格式时，limit为随机取值，主从取到的结果可能不一样。如果加上order by 或者改成row模式，可以避免此问题。

C.slave读的是文件系统的page cache，而不是binlog cache，即commit后才会write到page cache，flush操作由sync_binlog参数控制。所以即使sync_binlog !=1时也能看到binlog file中有这条记录，实际读就是文件系统的page cache。

D.从库执行stop slave，只是将io线程结束掉，并不会通知主库的dump线程，主库dump线程在给从库发送binlog event或者心跳包时，由于从库io线程已经结束，网络包无响应，主库等待net.ipv4.tcp_fin_timeout（默认60秒）后，报异常，退出Binlog_sender内部的while循环，调用unregister_slave函数注销从库的注册，此时再次在主库执行show slave hosts，就不会再看到从库的信息了。

9

MySQL Innodb数据是先写到内存的，再将内存数据写到磁盘，当脏页刷盘的时候会产生检查点，以下哪些情况会产生checkpoint（）

A.flush lru list，如果free buffer少于1024个页，则会触发刷脏页到磁盘产生checkpoint。 B.master thread，每1s、10s会触发将脏页刷到磁盘产生checkpoint。 C.dirty page too much，默认当buffer pool中max_dirty_page_pct达75%时，会将脏页刷到磁盘产生checkpoint。 D.redo log full switch，重做日志写满，切换日志时会产生checkpoint。

答案：ABCD 解释：checkpoint主要用于数据快速恢复。四种情况会产生checkpoint都属于fuzzy checkpoint，只在数据库运行期间产生。另外数据库正常关闭时，会产生sharp checkpoint，将所有的脏页都写入到磁盘上。

10

不考虑组提交方式，当sync_binlog=1、innodb_flush_log_at_trx_commit=1时，一个事物的流程：加载数据页到内存-->修改数据undo-->更新数据到内存-->写入redolog（状态为prepare）-->写binlog-->redolog flush-->binlog flush-->提交事务（redolog状态修改为commit）。MySQL为了保证redo log 和binlog一致性，内部事物提交采用xa两阶段方式，以下关于这个事物的说法，正确的是（）

A.如果redo log file中未提交，binlog file中存在，会将redo log中的这个未提交事物提交。

B.如果redo log file中未提交，binlog file中不存在，会将redo log中的这个未提交事物回滚。

C.如果redo log file中已提交，binlog file中存在，正常完成的事务，不需要恢复。

D.如果redo log file中已提交，binlog file中不存在，会将redo log中的这个提交事物回滚。

答案：ABC

解释：D.根据2PC，双1模式下，不会存在此种情况。redo log prepare阶段的redo log落盘的时间节点是在binlog落盘前，也就是binlog完全落盘则表明redo log prepare阶段的redo log已经落盘完成，如果binlog未落盘，则redo log不会落盘。

binlog为server层日志，redo log为引擎层日志，一个事务的提交必须写redo log和binlog，MySQL通过两阶段提交2PC(内部XA的两阶段提交)解决这一问题。参数innodb_support_xa默认为on，表示启用XA，虽然它会导致一次额外的磁盘flush(prepare阶段flush redo log)。但必须启用，而不能关闭它。因为关闭会导致binlog写入的顺序和实际的事务提交顺序不一致，会导致崩溃恢复和slave复制时发生数据错误。如果启用了log-bin参数，并且不止一个线程对数据库进行修改，那么就必须启用innodb_support_xa参数。

扩展分析：5.6以后的日志“组提交方式”及“2PC方式”