剑指Java面试笔记 -- 数据库索引

仅作为面试知识点记录，具体课程请在慕课网搜索剑指Java面试-Offer直通车

索引

为什么要使用索引

• 避免全表扫描，快速查询数据

什么样的信息能成为索引

• 主键、唯一键以及普通键等

索引的数据结构

• 生成索引，建立二叉查找树进行二分查找
• 生成索引，建立B树结构进行查找
• 生成索引，建立B+树结构进行查找
• 生成索引，建立Hash结构进行查找

二叉查找树

如上图所示，二叉查找树左子树节点值小于父节点，右子树节点值大于父节点。

若要找6，因为大于根节点5，所以在右子树中寻找，由于小于7，则在7的左子树找到6。

若二叉查找树是平衡二叉树（如上图），则是二分查找，所以时间复杂度为O(logn)

若二叉查找树是线性二叉树，则时间复杂度为O(n)

B树

定义

• 根节点至少包括两个孩子
• 树中每个节点最多含有m个孩子(m>=2)
• 除根节点和叶节点外，其他每个节点至少有ceil(m-2)个孩子
• 所有叶子节点都位于同一层
• 假设每个非终端节点中包含有n个关键字信息，其中
  a) Ki(i = 1…n)为关键字，且关键字按顺序升序排序 K(i-1) < Ki （如上图蓝色框内数值均为从左到右升序排列）
  b) 关键字的个数n必须满足：[ceil(m/2)-1] <= n <= m - 1 （任意节点关键字个数比指向的孩子数上限少1，非叶子节点关键字个数比指向孩子的个数少一个，如上图第二行蓝色比黄色少一个）
  c) 非叶子节点的指针：P[1]…p[M];其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的字数，其他P[i]指向关键字属于(K[i-1], K[i])的子树 （如上图P1的子树都小于8，P2的子树介于8和12之间，P3的子树都大于12）

B+树

B+树是B树的变体，其定义基本与B树相同，除了：

• 非叶子节点的子树指针与关键字个数相同
• 非叶子节点的子树指针P[i]，指向关键字值[K[i], K[i + 1])的子树
• 非叶子节点仅用来索引，数据都保存在叶子节点中
• 所有叶子节点均有一个链指针指向下一个叶子节点 （紫色块之间有链指针连接，从左到右升序排列）

结论

B+树更适合用来做存储索引

• B+树的磁盘读写代价更低 （B+树内部结构没有指向关键字信息的指针，不存放数据，只存放索引信息）
• B+树的查询效率更加稳定 （任何关键字的查找都要从根节点走到叶子节点，所有关键字的查询效率相同）
• B+树更有利于数据库的扫描 （只需要遍历叶子节点，并且范围查询也有优化）

Hash索引

缺点

• 仅仅能满足“=”，“IN”，不能使用范围查询
• 无法被用来避免数据的排序操作
• 不能利用部分索引键查询
• 不能避免表扫描
• 遇到大量Hash值相等的情况后性能并不一定就会比B树索引高

BitMap索引

密集索引和稀疏索引的区别

• 密集索引文件中的每个搜索码值都对应一个索引值
• 稀疏索引文件只为索引码的某些值建立索引项

密集索引为每一个索引键值建立索引，确定索引就确定数据位置
稀疏索引会将数据分组并为最小的索引键值建立索引，通过偏移量来确定数据位置

InnoDB

• 若一个主键被定义，则该主键作为密集索引
• 若没有主键被定义，该表的第一个唯一非空索引则作为密集索引
• 若不满足以上条件，innoDB内部会生成一个隐藏主键（密集索引）
• 非主键索引存储相关键位和其对应的主键值，包含两次查找

衍生问题

• 如何定位并优化慢查询SQL
• 联合索引的最左匹配原则的成因
• 索引是建立越多越好吗

如何定位并优化慢查询SQL

• 根据慢日志定位慢查询SQL
• 使用explain等工具分析SQL
• 修改SQL或者尽量让SQL走索引

explain关键字段

• type：
  

• extra
  

若出现上述关键字则需要进行调优

联合索引的最左匹配原则的成因

MySQL创建复合索引的规则是会对复合索引最左边的索引字段进行排序，在第一个索引字段排序的基础上再为第二个索引字段排序，而第一个字段绝对有序，第二个字段无序，所以通常情况下直接第二个字段条件判断是用不到索引的。

1. 最左前缀匹配原则：MySQL会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。例如a = 3 and b = 4 and c > 5 and d = 6 如果建立(a, b, c, d)顺序的索引，d是用不到索引的，而建立(a, b, d, c)则都可以用到且a, b, d的顺序可以任意调整
2. =和in可以乱序，MySQL的查询优化器会优化成索引可以识别的形式

索引是建立越多越好吗

• 数据量小的表不需要建立索引，建立会增加额外的索引开销
• 数据变更需要维护索引，因此更多的索引意味着更多的维护成本
• 更多的索引意味着也需要更多的空间