数据库常见面试题（一）——数据库架构和索引模块

- 日理万妓 2023-01-19 09:39 62阅读 0赞

### 数据库常见面试题（一）——数据库架构和索引模块 ###

*  1. 数据库架构
 *   *  问题1.1 如何设计一个关系型数据库？
 *  2. 索引模块
 *   *  问题2.1 为什么要使用索引？
     *  问题2.2 什么样的信息能成为索引？
     *  问题2.1 索引的数据结构？
     *   *  扩展一：可以通过减少io执行次数，优化索引的查询效率。
     *  问题2.1 密集索引和稀疏索引的区别？
     *   *  扩展二：mysql中的InnoDB和MyISAM存储引擎

# 1. 数据库架构 #

## 问题1.1 如何设计一个关系型数据库？ ##

该问题即回答设计一个关系型数据库需要包含哪些模块。

答：

*  一个关系型数据库应该具有将数据持久化到存储设备的**存储模块**；
 *  一个**操作数据库文件系统**的**程序实例**，程序实例包括逻辑数据和物理存储映射的**存储管理**模块；优化执行效率的**缓存模块**；对sql语句解析的**SQL解析**模块；记录操作的**日志管理**模块；进行多用户管理的**权限划分**模块；灾难恢复的**容灾模块**；优化数据查询效率的**索引模块**；使得数据库支持并发操作的**锁模块**；  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70]

# 2. 索引模块 #

## 问题2.1 为什么要使用索引？ ##

答： 在搜索数据量较大的表，避免全表扫描，减少IO访问量。

## 问题2.2 什么样的信息能成为索引？ ##

答：主键、唯一键等能唯一标识的键

## 问题2.1 索引的数据结构？ ##

答：主流B+树、还有hash结构、BitMap，但Mysql不支持BitMap索引，基于InnoDB和MyISAM引擎的Mysql不支持哈希。

### 扩展一：可以通过减少io执行次数，优化索引的查询效率。 ###

1、二叉查找树  
缺点是：删除或新增之后，树容易退化，树的深度会很大，io次数无法得到优化  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 1]  
2、B树

> B树的定义：  
> 1.根节点至少包括两个孩子。  
> 2.每个结点最多有m个孩子（m >= 2)。(如图m=3）  
> 3.除根节点和叶结点外，每个结点至少有 ceil （m / 2）个孩子。  
> 4.所有叶子结点都位于同一层  
> 5.非叶子结点的指针：p\[1\]、p\[2\]…p\[M\],满足p\[1\]指向的数据均小于k\[1\],p\[M\]指向的数据均大于K\[M-1\],其他p\[i\]指向的关键字属于(k\[i-1\],k\[i\])

缺点是：每个结点必须存储真实数据行，B树的每个节点的元素可以视为一次I/O读取，树的高度表示最多的I/O次数，在相同数量的总元素个数下，每个节点的元素个数越多，高度越低，查询所需的I/O次数越少；  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 2]  
3、B+树

> B+树定义：B+树是B树的变体，定义基本相同，除了  
> 1.非叶子结点的子树指针与关键字个数相同。  
> 2.非叶子结点的子树指针p\[i\]，指向关键字值\[k\[i\],k\[i+1\]）的子树  
> 3.非叶子结点仅用来索引，数据都存在叶子节点中。  
> 4.所有叶子结点均有一个链指针指向下一个叶子节点。

非叶子结点只存储索引，不存储数据，只有叶子结点存储数据。那样每个非叶子结点的页存储的索引数会多很多，这样树的高度会较矮，磁盘读写代价更低。所有叶子结点均有一个指针指向下一个叶子结点，可以横向跨子树统计，在范围查询更有优势。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 3]

4、hash索引（mysql不显式支持）  
hash索引不适合范围查找，仅满足“=”，“in”。  
hash索引不适合排序  
hash索引当Hash值相等数据较多会退化  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 4]  
5、位图索引（mysql不支持）  
当数据类型固定时，适合用位图索引  
缺点：锁的范围较大，不适合并发高的场景  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 5]

## 问题2.1 密集索引和稀疏索引的区别？ ##

*  密集索引文件的每个搜索码值（下图中的数据记录）都对应一个索引值（索引项）
 *  稀疏索引文件只为索引码（下图中的数据记录）的某些值建立索引项（索引项）  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 6]

### 扩展二：mysql中的InnoDB和MyISAM存储引擎 ###

1.  讨论MyISAM和InnoDB两个存储引擎的索引实现方式

**MyISAM索引实现：**

1）主键索引：

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM主键索引的原理图：

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5omLDbbN-1620550053183)(E:\\新桌面\\电子书\\1343757655\_1008.png)\]][img-5omLDbbN-1620550053183_E_1343757655_1008.png]

这里设表一共有三列，假设我们以Col1为主键，图myisam1是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。

2）辅助索引（Secondary key）

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 7]

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

**InnoDB索引实现**

然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同.

1）主键索引：

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 8]  
(图inndb主键索引）

(图inndb主键索引）是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做**聚集索引**。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

2）InnoDB的辅助索引

InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 9]

InnoDB 表是基于聚簇索引建立的。因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引（Secondary Index， 也就是非主键索引）也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义 、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。

文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

**InnoDB索引**和**MyISAM索引**的区别：

一是主索引的区别，InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。

二是辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

1.  InnoDB和MyISAM的简介：

> InnoDB存储引擎：InnoDB是一款支持事务，行级锁，外键，非锁定读等特性的存储引擎，其设计目标主要面向在线事务处理（OLTP online transaction processing）的应用。InnoDB的索引和数据是存放在一起的，格式为 \*.ibd。InnoDB通过使用多版本并发控制（MVCC）来实现高并发性，实现SQL标准的四种隔离级别：RU、RC、RR、SERIALIZABLE，默认为RR。同时使用一种被称为next-key locking的策略来避免幻读(phantom)现象的产生（关于幻读后面会讲）。为了满足一些高性能的需求，innodb实现了插入缓冲、二次读、自适应哈希、预读等特性。

> MyISAM存储引擎：MyISAM存储引擎不支持事务、表锁设计，支持全文索引，主要面向一些（OLAP On-Line Analytical Processing联机分析处理）数据库应用。MyISAM存储引擎另一个与众不同的地方是它的缓冲池只缓存索引文件，而不是缓冲数据文件（数据文件的缓存交由操作系统本身来完成）。MyISAM存储引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI则用来存放索引文件

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70]: /images/20221021/60e1041d888a4036b16b135444536319.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 1]: /images/20221021/1481c4a0dd7f4ca28c68c474c2c2c922.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 2]: /images/20221021/169c9737d48144568b6f93f5e96f64d8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 3]: /images/20221021/7d05d8cd6fa44f30841329e0a736e18f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 4]: /images/20221021/6ebbd4aaef614d23bdda41f110ed3d22.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 5]: /images/20221021/614188f4ff6c4ac78eabab5d0a59c4d0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 6]: /images/20221021/4bc800ae924648f593b2988982b4811f.png
[img-5omLDbbN-1620550053183_E_1343757655_1008.png]: /images/20221021/4a8d7144aac74dcdabf65b2c1e405113.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 7]: /images/20221021/687ac727de2c4f1d939644d2b1eebfda.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 8]: /images/20221021/7068bb3b0f154c64adf1ee99984dbd94.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk1MjczNg_size_16_color_FFFFFF_t_70 9]: /images/20221021/c9904e4cda294021b2cdf953c28a1739.png