面经精选:数据库高频面试十问

1.InnoDB和MyISAM存储引擎的区别?

MySQL 默认的存储引擎是 InnoDB,它采用 B+Tree 作为索引的数据结构。

在创建表时,InnoDB 存储引擎默认会创建一个主键索引,也就是聚簇索引,其它索引都属于二级索引。

MySQL 的 MyISAM 存储引擎支持多种索引数据结构,比如 B+ 树索引、R 树索引、Full-Text 索引。MyISAM 存储引擎在创建表时,创建的主键索引默认使用的是 B+ 树索引。

InnoDB存储引擎有2个文件:Frm文件和Ibd文件。Frm文件是表的定义文件,而Ibd文件是数据和索引存储文件(数据以主键进行聚集索引,把真正的数据保存在叶子节点中)。

MyISAM存储引擎有3个文件:Frm文件、MYD文件和MYI文件。Frm文件是表的定义文件,MYD文件是数据文件(所有的数据保存在这个文件中),MYI文件是索引文件。

综上所述,InnoDB 和 MyISAM 都支持 B+ 树索引,但是它们数据的存储结构实现方式不同。不同之处在于:

  • InnoDB 存储引擎:B+ 树索引的叶子节点保存数据本身,即数据和索引都放在一个文件中;
  • MyISAM 存储引擎:B+ 树索引的叶子节点保存数据的物理地址,即两个文件分开存储;

2.聚合索引和非聚合索引的区别,以及各自的优缺点?

InnoDB 存储引擎根据索引类型不同,分为聚簇索引(上图就是聚簇索引)和二级索引。它们区别在于,聚簇索引的叶子节点存放的是实际数据,所有完整的用户数据都存放在聚簇索引的叶子节点,而二级索引的叶子节点存放的是主键值,而不是实际数据

聚簇索引的优点:

  • 当你需要取出一定范围内的数据时,用聚簇索引比用非聚簇索引好
  • 数据访问更快,聚集索引将索引和数据保存在同一个B-Tree中,因此从聚集索引中获取数据通常比在非聚集索引中查找要快。
  • 当通过聚簇索引查找目标数据时理论上比非聚簇索引要快,因为非聚簇索引定位到对应主键时可能还要多一次回表操作
  • 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值

聚簇索引的缺点:

  • 插入速度严重依赖于插入顺序
  • 更新主键的代价很高,因为将会导致被更新的行移动

非聚簇索引的优点:

  • 插入和更新数据时不需要移动其他数据行,因此性能较好。
  • 非聚簇索引能够加速数据查询,提高查询速度。

非聚簇索引的缺点:

  • 查询非索引列的时候,需要进行二次查找,因此相对于聚簇索引,查询速度较慢。
  • 非聚簇索引的叶子节点不存储数据行,因此对于需要查询全部列的查询语句,需要进行额外的I/O操作,降低查询效率。

3.索引失效情景?

  • 当我们使用左或者左右模糊匹配的时候,也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。
  • 如果查询条件中对索引字段使用函数,就会导致索引失效。
  • 在查询条件中对索引进行表达式计算,也是无法走索引的。
  • 如果索引字段是字符串类型,但是在条件查询中,输入的参数是整型的话会发生隐式类型转换,此时也是没法走索引的。
  • 如果不符合最左匹配原则,也就无法匹配上联合索引,联合索引就会失效。
  • 在 WHERE 子句中,如果在 OR 前的条件列是索引列,而在 OR 后的条件列不是索引列,那么索引会失效。

4.说一说B+T是一个怎么样的数据结构?相对于平衡二叉树、红黑树、跳表,以及BT来说,为什么使用B+T?

  • B+T一个节点有多个叶子节点,并且非叶子节点只存储索引,叶子节点存放实际数据;叶子节点间用双向链表维护,实现了高效的范围查询。

为什么使用B+T

MySQL是基于磁盘的数据库,磁盘的性能瓶颈在于磁盘IO,我们知道磁盘是按照页进行存取的,每一页是固定大小,比如16KB,对于平衡二叉树和红黑树等,当数据量大的时候,它们的树通常是很高的,每次查询都只能取一个节点放入内存中查找,这样就会增加IO次数,查询效率低下。因此B树一族的优化思路就是不再限制一个节点就只能有 2 个子节点,而是允许 M 个子节点 (M>2),从而降低树的高度。

B 树的每一个节点最多可以包括 M 个子节点,M 称为 B 树的阶,所以 B 树就是一个多叉树。每一次都取一批节点放入内存中查找,极大降低了磁盘IO。

相对于B树,B+树做了两方面优化,一方面是非叶子节点只存放索引,另一方面是叶子节点间使用双向链表维护。对于非叶子节点的优化,其实还是针对减少磁盘IO的进一步优化。磁盘IO每一页的大小是固定的,我们希望一页存取的元素个数越多,那么每个元素的大小就得越小,因此B+树的优化就是非叶子节点不再存放完整记录,这样节点的大小就极大减小了,磁盘IO次数也就减少了,进一步提升了查询效率。针对于叶子节点用双向链表维护,这种设计对范围查找非常有帮助。而 B 树没有将所有叶子节点用链表串联起来的结构,因此只能通过树的遍历来完成范围查询,这会涉及多个节点的磁盘 I/O 操作,范围查询效率不如 B+ 树。

5.事务的隔离级别分别怎么实现的?

  • 对于「读未提交」隔离级别的事务来说,因为可以读到未提交事务修改的数据,所以直接读取最新的数据就好了;

  • 对于「串行化」隔离级别的事务来说,通过加读写锁的方式来避免并行访问;

  • 对于「读提交」和「可重复读」隔离级别的事务来说,它们是通过 *Read View 来实现的,它们的区别在于创建 Read View 的时机不同,大家可以把 Read View 理解成一个数据快照,就像相机拍照那样,定格某一时刻的风景。 *** 「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View,而「可重复读」隔离级别是「启动事务时」生成一个 Read View,然后整个事务期间都在用这个 Read View

    注意,执行「开始事务」命令,并不意味着启动了事务。在 MySQL 有两种开启事务的命令,分别是:

    这两种开启事务的命令,事务的启动时机是不同的:

    • 执行了 begin/start transaction 命令后,并不代表事务启动了。只有在执行这个命令后,执行了第一条 select 语句,才是事务真正启动的时机;
    • 执行了 start transaction with consistent snapshot 命令,就会马上启动事务。
    • 第一种:begin/start transaction 命令;
    • 第二种:start transaction with consistent snapshot 命令;

6.可重复读解决幻读问题了吗?

首先说一下什么是幻读。当同一个查询在不同的时间产生不同的结果集时,事务中就会出现所谓的幻象问题。例如,如果 SELECT 执行了两次,但第二次返回了第一次没有返回的行,则该行是“幻像”行。

可重复读隔离级是由 MVCC(多版本并发控制)实现的,实现的方式是开始事务后(执行 begin 语句后),在执行第一个查询语句后,会创建一个 Read View,后续的查询语句利用这个 Read View,通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据,所以事务过程中每次查询的数据都是一样的,即使中途有其他事务插入了新纪录,是查询不出来这条数据的,所以就很好了避免幻读问题。

MySQL 里除了普通查询是快照读,其他都是当前读,比如 update、insert、delete,这些语句执行前都会查询最新版本的数据,然后再做进一步的操作。

针对当前读是仍存在幻读问题的,因为行锁并不影响其他事务的插入操作。因此Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题,就引出了间隙锁,通过 记录锁+间隙锁形成next-key lock(临键锁)的方式解决了幻读。

但是可重复读隔离级别下仍然没彻底解决幻读问题,举两个例子:

  • 对于快照读, MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录,那么事务 A 前后两次查询的记录条目就不一样了,所以就发生幻读。
  • 对于当前读,如果事务开启后,并没有执行当前读,而是先快照读,然后这期间如果其他事务插入了一条记录,那么事务后续使用当前读进行查询的时候,就会发现两次查询的记录条目就不一样了,所以就发生幻读。

所以,MySQL 可重复读隔离级别并没有彻底解决幻读,只是很大程度上避免了幻读现象的发生。

要避免这类特殊场景下发生幻读的现象的话,就是尽量在开启事务之后,马上执行 select ... for update 这类当前读的语句,因为它会对记录加 next-key lock,从而避免其他事务插入一条新记录。

7.数据库都有哪些锁?

一、按锁的区间划分

  1. 间隙锁(gap locks)

    • 是开区间的,是一个在索引记录之间的间隙上的锁。
    • 作用是保证某个间隙内的数据在锁定情况下不会发生任何变化。例如在默认隔离级别(可重复读)下,当使用非唯一索引搜索或没有索引时等情况会产生间隙锁。
  2. 临键锁(next - key locks)

    • 是行锁 + 间隙锁,即临键锁是是一个左开右闭的区间。
    • InnoDB 的默认事务隔离级别是可重复读,在这种级别下,如果使用特定语句(如select... for update 等)会触发临键锁,可以防止幻读。

二、按锁的粒度划分

  1. 表级锁(table - level lock)

    • 直接给整个表添加锁。如 select * from student where name = 'tom' for update(InnoDB 在不通过索引检索数据时也是表锁 )。
    • 开销小,加锁快;不会出现死锁;但锁定粒度大,发生锁冲突的概率最高,并发度最低。
    • MyISAM在执行查询语句(select)前,会自动给涉及的所有表加读锁,在执行更新操作(update、delete 、insert 等)前,会自动给涉及的表加写锁。
  2. 行级锁(record locks)

    • InnoDB中给指定的行添加锁:如 select * from student where id > 10 for update
    • 是通过给索引上的索引项加锁来实现的,如果没有索引则会类似表锁(比如通过隐藏的聚簇索引) 。
    • 行锁的劣势是开销大、加锁慢、会出现死锁;优势是锁的粒度小,发生锁冲突的概率低;处理并发的能力强。
  3. 页级锁

    • 页级锁的颗粒度介于行级锁与表级锁之间。
    • 主要应用于BDB存储引擎(现在使用相对较少)。

三、按锁级别划分

  1. 共享锁(share lock,即 S 锁)

    • 又称读锁,允许一个事务去读取一行,阻止其他事务获得相同数据集的排它锁。若事务 t 对数据对象 a 加上 S 锁,则事务 t 可以读 a,但不能修改 a,其他事务只能对再对 a 加 S 锁,而不能加 X 锁 ,直到 t 释放 a 上的锁。这保证了其他事务可以读 a,但在释放 a 上的 S 锁之前不能对 a 做任何修改。
  2. 排它锁 / 独占锁(exclusive lock,即 X 锁)

    • 又称写锁,允许获取排它锁的事物更新数据,阻止其他事务取得相同的数据集共享读锁和排它写锁。若事务 t 对数据对象 a 加上 X 锁,事物 t 可以读 a 也可以修改 a,其他事务不能再对 a 加任何锁,直到 t 释放 a 上的锁。
  3. 意向锁

    • 意向共享锁(IS):表示事务准备给数据行加入共享锁,也就是说一个数据行加共享锁前必须先取得该表的 IS 锁;
    • 意向排他锁(IX):类似上面,表示事务准备给数据行加入排他锁,说明事务在一个数据行加排他锁前必须先取得该表的 IX 锁。意向锁是 InnoDB 自动加的,不需要用户干预。

四、按加锁方式分类

  1. 自动锁(automatic locks) :数据库自动根据操作和场景加的锁。
  2. 显示锁(lock tables) :通过特定的命令(如lock tables... )手动显示加的锁。

五、按锁的使用方式分类

  1. 乐观锁(optimistic lock) : 并不是真正的锁机制,通常是通过在表中增加版本号等字段来实现,在更新时检查版本等标识是否符合预期来判断是否发生并发冲突等。
  2. 悲观锁(pessimistic lock) :通过实实在在的锁来控制并发访问,如前面提到的共享锁、排它锁等都属于悲观锁策略。

六、其他特殊锁

  1. 死锁:两个或多个事务相互等待对方持有的资源,从而导致都无法继续执行的情况。

  2. 全局锁:

    • 对整个数据库实例加锁,让整个数据库处于只读状态。如MySQL提供了flush tables with read lock(ftwrl) 命令加全局读锁,加锁之后整个数据库实例处于只读状态,相关数据操作命令都会被阻塞。一般仅用于全库备份等特殊场景(且在InnoDB等支持一致性读的引擎中全库备份不一定需要 )。

8.说一说意向锁?

首先为什么需要引入意向锁呢?

我们在引入意向锁前有这样一个场景:其他事务对当前数据表进行了锁行操作(独占锁),而我们当前事务需要对该表加表级锁(独占表锁)。此时我们能加表锁的前提是当前表不存在独占锁,就需要遍历表里所有记录,查看是否有记录存在独占锁,这样效率会很慢。那么有了「意向锁」,由于在对记录加独占锁前,先会加上表级别的意向独占锁,那么在加「独占表锁」时,直接查该表是否有意向独占锁,如果有就意味着表里已经有记录被加了独占锁,这样就不用去遍历表里的记录。

那么什么是意向锁呢

  • 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前,需要先在表级别加上一个「意向共享锁」;
  • 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前,需要先在表级别加上一个「意向独占锁」;

也就是,当执行插入、更新、删除操作,需要先对表加上「意向独占锁」,然后对该记录加独占锁。

而普通的 select 是不会加行级锁的,普通的 select 语句是利用 MVCC 实现一致性读,是无锁的。

意向锁带来的影响有哪些呢?(优点)

意向共享锁和意向独占锁是表级锁,不会和行级的共享锁和独占锁发生冲突,而且意向锁之间也不会发生冲突,只会和共享表锁(lock tables ... read)和独占表锁(lock tables ... write)发生冲突。因此引入了意向锁不仅解决了上述问题,而且并发性能也是很高的。

9.Redo Log 和 Binlog 的区别?

Redo Log 和 BinLog 是 MySQL 中两种重要的日志,它们的区别如下:

  • 功能

    • Redo Log 主要用于实现事务的持久性,确保在数据库发生故障(如停电、系统崩溃等)时,能够恢复未完成事务对数据的修改,从而保证数据不会丢失。
    • BinLog 则用于记录数据库的变更操作,包括数据的插入、更新和删除等,主要用于数据备份、主从复制和数据恢复等场景。
  • 写入方式

    • Redo Log 是循环写入的,空间固定大小,写满时会覆盖旧的日志。
    • BinLog 可以通过配置来控制文件大小,当达到一定大小或其他条件时,会生成新的文件进行续写。
  • 存储位置

    • Redo Log 是存储在 InnoDB 存储引擎特有的文件中。
    • BinLog 是存储在服务器级别的日志文件中。
  • 写入时机

    redo log写入时机(InnoDB存储引擎层面)

    在事务执行过程中:

    1. 事务中的操作会先在内存中的redo log buffer(重做日志缓冲)中记录相应的redo log 条目。 以下是后续几种触发刷到磁盘(redo log文件)的情况:
    • 由参数innodb_flush_log_at_trx_commit控制:

      • 当设置为 1(默认):每次事务提交时都将redo log buffer中的redo log强制持久化到磁盘。
      • 当设置为 0:每次事务提交的时候都只是把redo log留在redo log buffer中;后台线程每隔1s进行一次刷盘操作,但如果MySQL进程崩溃可能丢失1s内的事务日志 。
      • 当设置为 2 :每次事务提交时都只是把redo log写到page cache(文件系统缓存),由操作系统决定何时刷到磁盘;如果MySQL进程崩溃,操作系统不崩溃则数据不会丢失,但如果系统崩溃则可能丢失1s内的数据(因为后台线程1s刷新一次)。
    • 当redo log buffer占用的空间即将达到innodb_log_buffer_size一半的时候,后台线程会主动写盘(write,没有fsync)。

    • 并行的事务提交的时候,顺带将这个事务的redo log buffer持久化到磁盘(事务a执行一半,部分redo log到buffer中;事务b提交,且innodb_flush_log_at_trx_commit设置为1或2,会把redo log buffer里的log全部持久化到磁盘中)。

    binlog写入时机

    1. 在事务执行期间:

      • 先把日志写到binlog cache(每个线程都有自己的binlog cache,binlog cache大小由参数binlog_cache_size控制,如果超过了这个大小就要暂存磁盘)。
    2. 在事务提交时:

      • 执行器把binlog cache里完整的事务写入binlog文件中,并清空binlog cache。
    • 由参数sync_binlog控制真正刷盘(write后进行fsync)的时机:

      • 当sync_binlog = 0 时,每次提交事务都只write,不fsync;如果操作系统崩溃,可能丢失部分事务的binlog 。
      • 当sync_binlog = 1 时,每次提交事务都会执行fsync,保证binlog完整持久化;这是最安全但性能相对低的方式。
      • 当sync_binlog = n(n > 1)时,表示每次提交事务都write,但累积n个事务后才fsync;这种方式可以减少磁盘IO操作次数提升性能,但如果主机发生异常重启,会丢失最近n个事务的binlog日志。

10.Binlog 有哪几种日志格式?

1. statement(基于语句的复制,SBR )

优点

  • 每一条会修改数据的 SQL 语句都会被记录在 binlog 中,日志量相对较小(如果不是批量、整表等操作情况下),相比于 row 格式在很多常规单一语句执行时节省空间和 I/O 。
  • 直观显示执行的语句,方便理解。

缺点

  • 一些语句在主从环境下可能出现不一致结果,例如使用了不确定函数(如UUID() 每次生成不同值 )等情况。
  • 特定存储过程、函数、触发器调用和触发在从库可能无法正确复制。
  • 对于有依赖上下文的语句(如依赖于当前数据分布等)可能导致主从结果不同。

2. row(基于行的复制,RBR)

优点

  • 不记录 SQL 语句上下文信息,只记录数据行的变化情况(哪条记录被修改以及被修改成什么样等)。
  • 能准确地进行数据复制,不会出现由于语句逻辑在主从不同环境下的不一致性问题。

缺点

  • 产生的日志内容会非常多,特别是在执行批量更新、整表删除、alter 表等操作时,日志量极大,会造成大量的磁盘 I/O 开销。
  • 查看日志相对不够直观,不能直接看出执行的语句逻辑。

3. mixed(混合模式,MBR)

从 MySQL 5.1.8 版本开始推出。

特点和运行机制

  • 是 statement 和 row 的混合体。
  • 系统自动判断语句该用 statement 还是 row 格式来记录日志。一般情况下,常规的语句修改使用 statement 格式保存 binlog ;对于一些 statement 无法准确完成主从复制的复杂操作(如函数使用等导致潜在不一致的语句 )则采用 row 格式保存 binlog 。
  • 可以一定程度上兼顾日志量和数据复制的准确性。但有时在切换格式时可能会存在一些边界情况需要关注和处理(比如切换瞬间数据的一致性保障等 )。

欢迎关注 ❤

我们搞了一个免费的面试真题共享群,互通有无,一起刷题进步。

没准能让你能刷到自己意向公司的最新面试题呢。

感兴趣的朋友们可以加我微信:wangzhongyang2024,备注:博客园面试群。

作者:王中阳Go1原文地址:https://www.cnblogs.com/wangzhongyang/p/18354457

%s 个评论

要回复文章请先登录注册