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 锁多张表

发表评论