最详细关系数据库(Mysql)面试（原理+实例）

关系数据库主要考点

主要考察的是索引、锁部分。

如何设计一个关系型数据库（RDBMS）？

• 1.存储（文件系统），即将数据持久化到硬盘中。
• 2.程序实例模块，对存储进行逻辑上的管理，主要分为八个部分：
  索引管理——优化查询效率
  锁管理——支持并发
  存储管理 ——逻辑关系转换成物理存储关系
  缓存机制——优化执行效率
  SQL解析——解析SQL语句
  日志管理——记录操作
  权限划分——进行多用户管理
  容灾机制——灾难恢复

一、索引模块

1.1 为什么使用索引？

查询数据方式有两种：

• 1.全局（表）扫描，将数据分块或分批次加载到内存，逐块进行查询，效率慢适合小规模数据。
• 2.索引查找，能够避免全局扫描，将数据查找限定在一定的范围内，提升查找效率。

1.2 什么样的信息能成为索引？

主键、唯一键以及普通键等，能够使数据具备一定区分性的字段。

1.3 索引的数据结构

• 生成索引——二叉查找树进行二分查找，树的高度如果很高，在每个判断的树的结点都会进行一次I/O，效率较低。（因此可以在每个子结点存储更多的数据，即B-Tree）。

• 生成索引——建立B-tree结构进行查找，包含关键字、指向子结点的指针。结点最多能存储的数据取决于每个存储快的容量和数据库的相关配置。目的，尽可能的减少I/O次数。（m表示每个结点包含的孩子数）（前4条限定B-树的孩子树和深度，5条限定B-树的关键字数和大小）（查找效率O(logn)（插入或删除数据，分裂、合并、上升等操作保证下面5条规则）。

  1.根结点至少包括两个孩子。
  2.树中每个结点最多包含有m个孩子（m>=2）。
  3.根结点和叶结点除外，其他每个结点至少有ceil(m/2)个子结点。
  4.叶子结点位于同一层。
  5.
  

  

• 生成索引——建立B+Tree结构进行查找（主要使用，更适合做存储索引）。B±树是B-树的变体，定义基本与B树相同。除了：

  1.非叶子结点的子树指针与关键字个数相同。
  2.非叶子结点的子树指针P[i]，指向关键字值(K[i],K[i+1])的子树。
  3.非叶子结点只做索引，这样非叶子结点空间不存储数据，有更多的空间存储索引，数据只存储在叶子结点，搜索只在叶子结点终结。
  4.所有叶子结点均有一个链指针指向下一个叶子结点（有序表存储），查找不会返回到叶子结点，而是横向跨子树统计（范围统计）。
  

• 生成索引—建立Hash结构进行查找。只需一次hash查找就能找到数据所在的buckets，再将该bucket加载到内存，因为bucket里面的数据存储结构为链表，通过指针查找，查找相应数据。

• 另外，还有BitMap位图索引。

1.4 为何B+树适合做存储索引？

• 1.B+树的磁盘读写代价更低，B+树的内部结构没有指向关键字具体信息的指针（不存放数据），只存放索引信息，其内部结点相对B-树更小，块中可以存放的关键字信息更多，I/O次数更少。
• 2.B+树的查询效率更加稳定，任何查找路径都必须包含根结点-叶子结点，关键字查找长度相同，每个数据的查找效率几乎相同（O(logn)）。
• 3.B+树更有利于对数据库的扫描。

1.5 hash索引的缺点？

• 1.仅仅能满足‘=’，‘IN’之类的查询，不能使用范围查询。hash查找是比较hash运算后的hash值，只能用于‘=’值的过滤，不能用于基于范围的查询
• 2.无法进行数据的排序操作。hash索引中存放的是经过hash运算后的值，hash值大小关系并不一定和hash运算前的键值完全一样，所以数据库无法进行排序操作。
• 不能利用部分索引键进行查询。对于组合索引，hash索引在进行hash值的时候，将组合索引键合并之后，再进行hash值，而不是单独计算hash值。
• 不能避免表扫描。由于不同的主键可能存在相同的hash值，hash索引不能直接从一个bucket中得到相应的值，因此还是需要对bucket的值进行扫描比较。
• 遇到大量hash值相等的情况，性能并不一定就会比B-Tree索引高。

1.6 密集索引和稀疏索引

• 1.密集索引文件中的每个搜索码值都对应一个索引值。叶子结点中不仅包含了键值，还包含了位于同一行记录里的其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只能拥有一个物理排列顺序，所以一个表只能创建一个密集索引。
• 2.稀疏索引只为索引码的某些值建立索引项。叶子结点仅保存了键位信息和该行数据的地址，有的稀疏索引仅包含了键位信息和其主键。定位到叶子结点后，仍然需要通过地址和主键信息，进一步定位到数据。

mysql有两种数据的存储格式：InnoDB和MyISAM

1.7 InnoDB（必须要有一个密集索引）