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

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.数据库都有哪些锁？

一、按锁的区间分别

• 间隙锁（gap locks） ：
  
  
  
  • 是开区间的，是一个在索引记录之间的间隙上的锁。
    
  • 作用是保证某个间隙内的数据在锁定情况下不会发生任何变化。例如在默认隔离级别（可重复读）下，当使用非唯一索引搜索或没有索引时等情况会产生间隙锁。
    
  
  
• 临键锁（next - key locks）
  
  
  
  • 是行锁 + 间隙锁，即临键锁是是一个左开右闭的区间。
    
  • InnoDB 的默认事件隔离级别是可重复读，在这种级别下，如果使用特定语句（如select... for update 等）会触发临键锁，可以防止幻读。
    
  
  

二、按锁的粒度分别

• 表级锁（table - level lock）
  
  
  
  • 直接给整个表添加锁。如 select * from student where name = 'tom' for update（InnoDB 在不通过索引检索数据时也是表锁 ）。
    
  • 开销小，加锁快；不会出现死锁；但锁定粒度大，发生锁冲突的概率最高，并发度最低。
    
  • MyISAM在实行查询语句（select）前，会自动给涉及的全部表加读锁，在实行更新操作（update、delete 、insert 等）前，会自动给涉及的表加写锁。
    
  
  
• 行级锁（record locks）
  
  
  
  • InnoDB中给指定的行添加锁：如 select * from student where id > 10 for update 。
    
  • 是通过给索引上的索引项加锁来实现的，如果没有索引则会类似表锁（比如通过隐藏的聚簇索引） 。
    
  • 行锁的劣势是开销大、加锁慢、会出现死锁；优势是锁的粒度小，发生锁冲突的概率低；处理并发的本领强。
    
  
  
• 页级锁
  
  
  
  • 页级锁的颗粒度介于行级锁与表级锁之间。
    
  • 主要应用于BDB存储引擎（现在使用相对较少）。
    
  
  

三、按锁级别分别

• 共享锁（share lock，即 S 锁）
  
  
  
  • 又称读锁，允许一个事件去读取一行，阻止其他事件获得相同数据集的排它锁。若事件 t 对数据对象 a 加上 S 锁，则事件 t 可以读 a，但不能修改 a，其他事件只能对再对 a 加 S 锁，而不能加 X 锁 ，直到 t 释放 a 上的锁。这保证了其他事件可以读 a，但在释放 a 上的 S 锁之前不能对 a 做任何修改。
    
  
  
• 排它锁 / 独占锁（exclusive lock，即 X 锁）
  
  
  
  • 又称写锁，允许获取排它锁的事物更新数据，阻止其他事件取得相同的数据集共享读锁和排它写锁。若事件 t 对数据对象 a 加上 X 锁，事物 t 可以读 a 也可以修改 a，其他事件不能再对 a 加任何锁，直到 t 释放 a 上的锁。
    
  
  
• 意向锁
  
  
  
  • 意向共享锁（IS）：表示事件准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的 IS 锁；
    
  • 意向排他锁（IX）：类似上面，表示事件准备给数据行加入排他锁，说明事件在一个数据行加排他锁前必须先取得该表的 IX 锁。意向锁是 InnoDB 自动加的，不必要用户干预。
    
  
  

四、按加锁方式分类

• 自动锁（automatic locks） ：数据库自动根据操作和场景加的锁。
  
• 显示锁（lock tables） ：通过特定的下令（如lock tables... ）手动显示加的锁。
  

五、按锁的使用方式分类

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

六、其他特殊锁

• 死锁：两个或多个事件相互等待对方持有的资源，从而导致都无法继续实行的情况。
  
• 全局锁：
  
  
  
  • 对整个数据库实例加锁，让整个数据库处于只读状态。如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存储引擎层面）
  在事件实行过程中：
  
  
  
  • 事件中的操作会先在内存中的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写入时机
  
  
  
  • 在事件实行期间：
    
    
    
    • 先把日志写到binlog cache（每个线程都有本身的binlog cache，binlog cache大小由参数binlog_cache_size控制，如果凌驾了这个大小就要暂存磁盘）。
      
    
    
  • 在事件提交时：
    
    
    
    • 实行器把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，备注：博客园口试群。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。