关系型数据库面试题


1. 存储引擎

1.1. mysql 有哪些存储引擎?有什么区别?

2. 索引

2.1. 数据库索引有哪些数据结构?

2.1.1. B-Tree

一棵 M 阶的 B-Tree 满足以下条件:

对于任意结点,其内部的关键字 Key 是升序排列的。每个节点中都包含了 data。

img

对于每个结点,主要包含一个关键字数组 Key[],一个指针数组(指向儿子)Son[]。

在 B-Tree 内,查找的流程是:

  1. 使用顺序查找(数组长度较短时)或折半查找方法查找 Key[]数组,若找到关键字 K,则返回该结点的地址及 K 在 Key[]中的位置;
  2. 否则,可确定 K 在某个 Key[i]和 Key[i+1]之间,则从 Son[i]所指的子结点继续查找,直到在某结点中查找成功;
  3. 或直至找到叶结点且叶结点中的查找仍不成功时,查找过程失败。

2.1.2. B+Tree

B+Tree 是 B-Tree 的变种:

img

由于并不是所有节点都具有相同的域,因此 B+Tree 中叶节点和内节点一般大小不同。这点与 B-Tree 不同,虽然 B-Tree 中不同节点存放的 key 和指针可能数量不一致,但是每个节点的域和上限是一致的,所以在实现中 B-Tree 往往对每个节点申请同等大小的空间。

带有顺序访问指针的 B+Tree

一般在数据库系统或文件系统中使用的 B+Tree 结构都在经典 B+Tree 的基础上进行了优化,增加了顺序访问指针。

img

在 B+Tree 的每个叶子节点增加一个指向相邻叶子节点的指针,就形成了带有顺序访问指针的 B+Tree。

这个优化的目的是为了提高区间访问的性能,例如上图中如果要查询 key 为从 18 到 49 的所有数据记录,当找到 18 后,只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点,极大提到了区间查询效率。

2.1.3. Hash

Hash 索引只有精确匹配索引所有列的查询才有效。

对于每一行数据,对所有的索引列计算一个 hashcode。哈希索引将所有的 hashcode 存储在索引中,同时在 Hash 表中保存指向每个数据行的指针。

哈希索引的优点:

哈希索引的缺点:

2.2. B-Tree 和 B+Tree 有什么区别?

2.3. 索引原则有哪些?

2.3.1. 独立的列

如果查询中的列不是独立的列,则数据库不会使用索引。

“独立的列” 是指索引列不能是表达式的一部分,也不能是函数的参数。

❌ 错误示例:

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
SELECT ... WHERE TO_DAYS(CURRENT_DAT) - TO_DAYS(date_col) <= 10;

2.3.2. 前缀索引和索引选择性

有时候需要索引很长的字符列,这会让索引变得大且慢。

解决方法是:可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。但这样也会降低索引的选择性。

索引的选择性是指:不重复的索引值和数据表记录总数的比值。最大值为 1,此时每个记录都有唯一的索引与其对应。选择性越高,查询效率也越高。

对于 BLOB/TEXT/VARCHAR 这种文本类型的列,必须使用前缀索引,因为数据库往往不允许索引这些列的完整长度。

要选择足够长的前缀以保证较高的选择性,同时又不能太长(节约空间)。

2.3.3. 多列索引

不要为每个列创建独立的索引。

2.3.4. 选择合适的索引列顺序

经验法则:将选择性高的列或基数大的列优先排在多列索引最前列。

但有时,也需要考虑 WHERE 子句中的排序、分组和范围条件等因素,这些因素也会对查询性能造成较大影响。

2.3.5. 聚簇索引

聚簇索引不是一种单独的索引类型,而是一种数据存储方式。

聚簇表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引。

2.3.6. 覆盖索引

索引包含所有需要查询的字段的值。

具有以下优点:

2.3.7. 使用索引扫描来做排序

索引最好既满足排序,又用于查找行。这样,就可以使用索引来对结果排序。

2.3.8. = 和 in 可以乱序

比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql 的查询优化器会帮你优化成索引可以识别的形式。

2.3.9. 尽量的扩展索引,不要新建索引

比如表中已经有 a 的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

2.3.10.如何维护索引

注意事项

3. 事务

3.1. 数据库事务隔离级别?事务隔离级别分别解决什么问题?

隔离级别脏读不可重复读幻影读
未提交读YESYESYES
提交读NOYESYES
可重复读NONOYES
可串行化NONONO

3.2. 如何解决分布式事务?若出现网络问题或宕机问题,如何解决?

4. 锁

4.1. 数据库锁有哪些类型?如何实现?

4.1.1. 锁粒度

InnoDB 行锁是通过给索引上的索引项加锁来实现的。只有通过索引条件检索数据,InnoDB 才使用行级锁;否则,InnoDB 将使用表锁!

索引分为主键索引和非主键索引两种,如果一条 sql 语句操作了主键索引,MySQL 就会锁定这条主键索引;如果一条语句操作了非主键索引,MySQL 会先锁定该非主键索引,再锁定相关的主键索引。在 UPDATE、DELETE 操作时,MySQL 不仅锁定 WHERE 条件扫描过的所有索引记录,而且会锁定相邻的键值,即所谓的 next-key locking。

当两个事务同时执行,一个锁住了主键索引,在等待其他相关索引。另一个锁定了非主键索引,在等待主键索引。这样就会发生死锁。发生死锁后,InnoDB 一般都可以检测到,并使一个事务释放锁回退,另一个获取锁完成事务。

4.1.2. 读写锁

有以下两个规定:

锁的兼容关系如下:

-XS
XNONO
SNOYES

使用:

innodb 下的记录锁(也叫行锁),间隙锁,next-key 锁统统属于排他锁。

在 InnoDB 中,行锁是通过给索引上的索引项加锁来实现的。如果没有索引,InnoDB 将会通过隐藏的聚簇索引来对记录加锁。另外,根据针对 sql 语句检索条件的不同,加锁又有以下三种情形需要我们掌握。

  1. Record lock:对索引项加锁。若没有索引项则使用表锁。
  2. Gap lock:对索引项之间的间隙加锁。
  3. Next-key lock:1+2,锁定一个范围,并且锁定记录本身。对于行的查询,都是采用该方法,主要目的是解决幻读的问题。当利用范围条件而不是相等条件获取排他锁时,innoDB 会给符合条件的所有数据加锁。对于在条件范围内但是不存在的记录,叫做间隙。innoDB 也会对这个间隙进行加锁。另外,使用相等的检索条件时,若指定了本身不存在的记录作为检索条件的值的话,则此值对应的索引项也会加锁。

4.1.3. 意向锁

使用意向锁(Intention Locks)可以更容易地支持多粒度封锁。

在存在行级锁和表级锁的情况下,事务 T 想要对表 A 加 X 锁,就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁,那么就需要对表 A 的每一行都检测一次,这是非常耗时的。

意向锁在原来的 X/S 锁之上引入了 IX/IS,IX/IS 都是表锁,用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定:

通过引入意向锁,事务 T 想要对表 A 加 X 锁,只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁,如果加了就表示有其它事务正在使用这个表或者表中某一行的锁,因此事务 T 加 X 锁失败。

各种锁的兼容关系如下:

-XIXSIS
XNONONONO
IXNOYESNOYES
SNONOYESYES
ISNOYESYESYES

解释如下:

意向锁是 InnoDB 自动加的,不需要用户干预。

5. 分库分表

5.1. 为什么要分库分表?

分库分表的基本思想就要把一个数据库切分成多个部分放到不同的数据库(server)上,从而缓解单一数据库的性能问题。

5.2. 分库分表的常见问题以及解决方案?

5.2.1. 事务问题

方案一:使用分布式事务

方案二:由应用程序和数据库共同控制

5.2.2. 跨节点 Join 的问题

只要是进行切分,跨节点 Join 的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的 id,根据这些 id 发起第二次请求得到关联数据。

5.2.3. 跨节点的 count,order by,group by 以及聚合函数问题

这些是一类问题,因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。

解决方案:与解决跨节点 join 问题的类似,分别在各个节点上得到结果后在应用程序端进行合并。和 join 不同的是每个节点的查询可以并行执行,因此很多时候它的速度要比单一大表快很多。但如果结果集很大,对应用程序内存的消耗是一个问题。

5.2.4. ID 唯一性

一旦数据库被切分到多个物理节点上,我们将不能再依赖数据库自身的主键生成机制。一方面,某个分区数据库自生成的 ID 无法保证在全局上是唯一的;另一方面,应用程序在插入数据之前需要先获得 ID,以便进行 SQL 路由。

一些常见的主键生成策略:

5.2.5. 数据迁移,容量规划,扩容等问题

来自淘宝综合业务平台团队,它利用对 2 的倍数取余具有向前兼容的特性(如对 4 取余得 1 的数对 2 取余也是 1)来分配数据,避免了行级别的数据迁移,但是依然需要进行表级别的迁移,同时对扩容规模和分表数量都有限制。总得来说,这些方案都不是十分的理想,多多少少都存在一些缺点,这也从一个侧面反映出了 Sharding 扩容的难度。

5.2.6. 分库数量

分库数量首先和单库能处理的记录数有关,一般来说,Mysql 单库超过 5000 万条记录,Oracle 单库超过 1 亿条记录,DB 压力就很大(当然处理能力和字段数量/访问模式/记录长度有进一步关系)。

5.2.7. 跨分片的排序分页

5.3. 如何设计可以动态扩容缩容的分库分表方案?

5.4. 有哪些分库分表中间件?各自有什么优缺点?底层实现原理?

简单易用的组件:

强悍重量级的中间件:

6. 数据库优化

6.1. 什么是执行计划?

7. 数据库架构设计

7.1. 高并发系统数据层面如何设计?

读写分离的原理

主服务器用来处理写操作以及实时性要求比较高的读操作,而从服务器用来处理读操作。

读写分离常用代理方式来实现,代理服务器接收应用层传来的读写请求,然后决定转发到哪个服务器。

MySQL 读写分离能提高性能的原因在于:

img

Mysql 的复制原理

Mysql 支持两种复制:基于行的复制和基于语句的复制。

这两种方式都是在主库上记录二进制日志,然后在从库重放日志的方式来实现异步的数据复制。这意味着:复制过程存在时延,这段时间内,主从数据可能不一致。

主要涉及三个线程:binlog 线程、I/O 线程和 SQL 线程。

img

垂直切分

按照业务线或功能模块拆分为不同数据库。

更进一步是服务化改造,将强耦合的系统拆分为多个服务。

水平切分

http://www.netingcn.com/mac-os-plist.html)

  #OS 

« OSX系统添加定时任务 nohup和&后台运行,进程查看及终止 »