MySQL面试题

发表评论 / mysql / 作者：

InnoDB与MyISAM的区别

  • Innodb 支持事务,外键,行级锁,支持非锁定读，面向OLTP
  • MyISAM 不支持事务，支持全文索引，表锁设计，面向OLAP

InnoDB有哪些线程以及作用

  • Master Thread: 刷新脏页，合并插入缓冲，Undo页的回收等
  • Io Thread: 对磁盘进行读写操作的线程，Inndo存储引擎中使用了AIO来处理IO请求，这样可以极大提升数据库的性能。
  • Purge Thread: purge是清理的意思，这个后台线程的作用也是用来清理数据。在特定情况下，事务提交后的undo log不会在需要了,purge thread的作用就是回收undo page

Innodb缓冲池中有哪些数据页类型

索引页，数据页，undo页，插入缓冲，自适应哈希索引，锁信息，数据字典信息

Checkpoint

作用: 定期处理缓冲和redo log日志的一种机制，减少系统开销。
特点
  • 缩短数据库恢复的时间
  • 缓冲池不够用，将脏页刷新到磁盘
  • redo log不可用时，将脏页刷新到磁盘
LSN
LSN(Log Sequence Number)用来标记版本，8个字节的数字。重做日志每增加多少个字节，LSN就递增多少.
  • redo log：每次事务提交，先在log buffer生成LSN，然后刷新到磁盘的redo log，递增LSN；
  • 缓冲池中的页：保存两个LSN，第一次修改时LSN，最后一次修改时LSN；每次修改页，递增最后一次修改时LSN；
  • Checkpoint：每次将页刷新到磁盘，保存该页的第一次修改时LSN

什么是插入缓冲（insert buffer）

Insert Buffer就是用于解决非聚集索引离散性插入导致的下降问题，其数据结构类似于数据页的一个B+树，物理存储在共享表空间ibdata1中。MySQL5.5之前的版本中其实都叫做Insert Buffer，之后优化为 Change Buffer可以看做是 Insert Buffer 的升级版。插入缓冲（ Insert Buffer）这个其实只针对 INSERT 操作做了缓冲，而Change Buffer 对INSERT、DELETE、UPDATE都进行了缓冲，所以可以统称为写缓冲
条件
  • 索引是非聚集索引->因为(聚集索引本来就是顺序的也不需要你)
  • 索引不是唯一（unique）的->因为(在写入Insert Buffer时，数据库并不会去判断插入记录的唯一性。如果再去查找肯定又是离散读取的情况了，这样Insert Buffer就失去了意义)

什么是两次写（double write）

在数据库进行脏页刷新时，如果此时宕机，有可能会导致磁盘数据页损坏，丢失我们重要的数据。此时就算重做日志也是无法进行恢复的，因为重做日志记录的是对页的物理修改(redo log 不会记录完整的一页数据，因为这样日志太大，它只会记录哪次如何操作了哪页的哪行)。其实就是在重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是double write。

什么是自适应哈希

InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应（adaptive） 的。自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式 来为某些页建立哈希索引。
要求
  1. 对这个页的连续访问模式必须是一致的
  2. 页通过该模式访问了N次，其中N=页中记录 * 1/16

InnoDB有哪些索引

  • 数据结构角度：B+树，自适应哈希，全文索引

  • 物理存储角度：聚簇索引，非聚簇索引

  • 逻辑角度：主键索引，普通索引，单列索引，联合索引，唯一索引，多列索引

意向锁的作用

讲一下InnoDB锁的三种实现算法

  • Record Lock: 单个记录上的锁
  • Gap Lock: 间隙锁，锁定一个范围，但不包括记录本身
  • Next-Key Lock: Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

Next-Key Lock锁到底有什么用

默认隔离级别RR，InnoDB中行锁默认使用算法Next-Key Lock，只有当查询的索引是唯一索引或主键时，InnoDB会对Next-Key Lock进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是范围。当查询的索引为辅助索引时，InnoDB则会使用Next-Key Lock进行加锁。InnoDB对于辅助索引有特殊的处理，不仅会锁住辅助索引值所在的范围，还会将其下一键值加上Gap LOCK。==InnoDB引擎采用Next-Key Lock来解决幻读问题==。因为Next-Key Lock是锁住一个范围，所以就不会产生幻读问题。但是需要注意的是，InnoDB只在RR隔离级别下使用该机制。

四种隔离级别

  • 未提交读，事务所做的修改未提交，对其他事务可见，从而导致脏读的情况
  • 已提交读，解决了脏读的问题，但是出现了不可重复读的问题
  • 可重复读，解决了脏读和不可重复读的问题，但是带来了幻读的问题，而InnoDb引擎使用间隙锁解决了幻读，所以在InnoDB下不会有这些问题了
  • 可串行化，强制事务串行执行，并且在读取的每一行都加锁，会出现大量的超时和锁竞争的问题

脏读，不可重复读和幻读的区别

  • 脏读：一个事务A读取到另一个事务B未提交的修改，B回滚了，就造成了数据不一致
  • 不可重复读：一个事务A在事务执行过程中第一次读取的值和第二次读取的值不一致，这是由于事务B在A事务俩次读取之间修改了数据并提交了事务。
  • 幻读：RR下，一个事务A在事务执行过程中第一次读取的值和第二次读取的值一致（解决了不可重复读），但是其他事务B 的insert 或者 delete的操作，会影响到俩次查询的条数。

InnoDB如何保证ACID的

  • redo log保证事务的原子性和持久性
  • undo log保证事务的一致性
  • 锁保证事务的隔离性

redo log如何保证事务的持久性？

InnoDB是事务的存储引擎，其通过Force Log at Commit 机制实现事务的持久性，即当事务提交时，先将 redo log buffer 写入到 redo log file 进行持久化，待事务的commit操作完成时才算完成。这种做法也被称为 Write-Ahead Log(预先日志持久化)，在持久化一个数据页之前，先将内存中相应的日志页持久化。
Write-Ahead Log
在前面阐述中就提到了Write-Ahead Log(预先写日志)。在持久化一个数据页之前，必须先将内存中相应的日志页持久化。每个页都有一个LSN(log sequence number)，代表日志序列号，（LSN占用8字节，单调递增), 当一个数据页需要写入到持久化设备之前，要求内存中小于该页LSN的日志先写入持久化设备
为什么必须要先写日志呢？而不是直接将数据写入磁盘
数据修改会产生随机IO，但日志是顺序IO，append方式顺序写，是一种串行的方式，这样才能充分利用磁盘的性能。
Force-log-at-commit
Write-Ahead Log 可以保证单个数据页的一致性，但是无法保证事务的持久性，Force-log-at-commit 要求当一个事务提交时，其产生所有的mini-transaction 日志必须刷新到磁盘中，若日志刷新完成后，在缓冲池中的页刷新到持久化存储设备前数据库发生了宕机，那么数据库重启时，可以通过日志来保证数据的完整性。

undo log的作用

  1. 用于事务的回滚
  2. MVCC

undo log的类型和区别

  • insert undo log是指在insert 操作中产生的undo log，因为insert操作的记录，只对事务本身可见，对其他事务不可见。故该undo log可以在事务提交后直接删除，不需要进行purge操作。

  • update undo log记录的是对delete 和update操作产生的undo log，该undo log可能需要提供MVCC机制，因此不能再事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。

undo log 是否是redo log的逆过程吗？

不是。undo log是逻辑日志，对事务回滚时，只是将数据库逻辑地恢复到原来的样子，而redo log是物理日志，记录的是数据页的物理变化。

MVCC用来解决什么问题？

解决不可重复读和幻读问题，取代行锁，降低系统开销。

InnoDB 的 MVCC 是如何实现的？

undo log + read view

InnoDB的二级索引叶子节点为什么存储的是主键值，而不是行指针

因为出现行移动或者数据页分裂时不需要维护二级索引

一致性非锁定读和一致性锁定读

一致性非锁定读

  • 一致性非锁定读是指InnoDB存储引擎通过多版本控制（multi versionning）的方式来读取当前执行时间数据库中的行数据。如果读取的行正在执行update或delete操作，这时读取操作不会因此等待行上的锁释放。相反的，InnonDB会去读取行上的一个快照数据。

  • 快照数据是指该行之前版本的数据，该实现是通过undo page来完成。而undo用来事务中的回滚数据，因此快照数据没有额外的开销。而读取快照是不需要加锁的，没有事务会操作历史数据。

  • 快照其实是当前行数据之前的历史版本，每行记录可能有多个版本的快照数据，一般称这种技术叫多版本并发控制（MVCC，Multi Version Concurrency Control）

  • 非锁定读提高了数据库的并发性，在InnonDB存储引擎下，这是默认的读写方式，即读不会占用和等待表上的锁。

一致性锁定读
有些情况下为了保证数据的一致性，需要对select的操作加锁。InnonDB存储引擎对于select语句支持两种一致性的锁定读。
  • select …… for update
  • select …… lock in share mode
  • select …… for update是对读取的记录加一个X锁，select …… lock in share mode是对读取的记录加一个S锁。
即使被读取的行被加了一致性锁定读，如果有另一个一致性非锁定读的操作来读取该行数据是不会阻塞的，读取的是改行的快照版本。
SELECT …… FOR UPDATE和SELECT …… LOCK IN SHARE MODE必须在一个事务中，当一个事务提交了，锁就释放了。因此在使用上述两个SELECT锁定语句时，必须开启事务。

InnoDB是如何处理死锁的

两种死锁处理方式：

  • 等待，直到超时（innodb_lock_wait_timeout=50s）。

  • 发起死锁检测，主动回滚一条事务，让其他事务继续执行（innodb_deadlock_detect=on）。

    由于性能原因，一般都是使用死锁检测来进行处理死锁。
死锁检测
死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。回滚
检测到死锁之后，选择插入更新或者删除的行数最少的事务回滚，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段来判断。

减少死锁：

  • 使用事务，不使用 lock tables 。
  • 保证没有长事务。
  • 操作完之后立即提交事务，特别是在交互式命令行中。
  • 如果在用 (SELECT … FOR UPDATE or SELECT … LOCK IN SHARE MODE)，尝试降低隔离级别。
  • 修改多个表或者多个行的时候，将修改的顺序保持一致。
  • 创建索引，可以使创建的锁更少。
  • 最好不要用 (SELECT … FOR UPDATE or SELECT … LOCK IN SHARE MODE)。
    -如果上述都无法解决问题，那么尝试使用 lock tables t1, t2, t3 锁多张表

发表评论