面试中有哪些经典的数据库问题？.docxVIP

面试中有哪些经典的数据库问题？ 2021-05-05 一、为什么用自增列作为主键 1、假如我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引、假如没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的独一索引作为主键索引、假如也没有这样的独一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。 2、数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键挨次存放，因而每当有一条新的记录插入时，MySQL会依据其主键将其插入适当的节点和位置，假如页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点） 3、假如表使用自增主键，那么每次插入新的记录，记录就会挨次添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页 4、假如使用非自增主键（假如身份证号或学号等），由于每次插入主键的值近似于随机，因而每次新纪录都要被插到现有索引页得两头某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这添加了很多开销，同时频繁的移动、分页操作形成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。 二、为什么使用数据索引能提高效率 1、数据索引的存储是有序的 2、在有序的情况下，通过索引查询一个数据是无需遍历索引记录的 3、极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N) 三、B+树索引和哈希索引的区分 B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接，是有序的 哈希索引就是接受肯定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可,是无序的 四、哈希索引的优势： 1、等值查询。哈希索引具有确定优势（前提是：没有大量反复键值，假如大量反复键值时，哈希索引的效率很低，由于存在所谓的哈希碰撞问题。） 五、哈希索引不适用的场景： 1、不支持范围查询 2、不支持索引完成排序 3、不支持联合索引的最左前缀婚配规章 通常，B+树索引结构适用于绝大多数场景，像下面这种场景用哈希索引才更有优势： 在HEAP表中，假如存储的数据反复度很低（也就是说基数很大），对该列数据以等值查询为主，没有范围查询、没有排序的时候，特殊适合接受哈希索引，例如这种SQL： 而常用的InnoDB引擎中默认使用的是B+树索引，它会实时监控表上索引的使用情况，假如认为建立哈希索引可以提高查询效率，则自动在内存中的“自顺应哈希索引缓冲区”建立哈希索引（在InnoDB中默认开启自顺应哈希索引），通过观看搜索模式，MySQL会利用index key的前缀建立哈希索引，假如一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。 留意：在某些工作负载下，通过哈希索引查找带来的功能提升远大于额外的监控索引搜索情况和保持这个哈希表结构所带来的开销。但某些时候，在负载高的情况下，自顺应哈希索引中添加的read/write锁也会带来竞争，比如高并发的join操作。like操作和%的通配符操作也不适用于自顺应哈希索引，可能要关闭自顺应哈希索引。 六、B树和B+树的区分 1、B树，每个节点都存储key和data，全部节点组成这棵树，并且叶子节点指针为nul，叶子结点不包含任何关键字信息。 2、B+树，全部的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的挨次链接，全部的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。(而B 树的非终节点也包含需要查找的有效信息) 七、为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引？ 1、B+的磁盘读写代价更低B+的内部结点并没有指向关键字具体信息的指针。因而其内部结点相对B树更小。假如把全部同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。 2、B+-tree的查询效率愈加稳定由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必需走一条从根结点到叶子结点的路。全部关键字查询的路径长度相同，导致每一个数据的查询效率相当。 八、MySQL联合索引 1、联合索引是两个或更多个列上的索引