数据库-索引

一、索引基础

1. 本质：数据库检索的常用手段，是一种建立检索关键字与存储地址对应关系的数据结构。

2. 核心作用：大幅加快查询检索速度。

3. 两种数据搜索方式对比

方式 原理 效率特点

顺序读取/全表搜索 逐行读取记录并与查询条件一一对比 数据量越大，磁盘访问量越高，效率越低

索引搜索/选择性读取 先查索引定位符合条件的存储地址，再直接读取对应记录 数据量越大，优势越明显，磁盘访问量小、效率高

二、索引分类（两大核心视角）

视角1：记录存储顺序与关键词的关联关系

1. 聚簇索引（一级索引/主索引/聚集索引）

- 核心特点：关键列排序与磁盘地址直接关联，无需额外磁盘存储空间；索引项顺序与记录物理存放顺序完全一致。

- 硬性规则：一个基本表最多只能建立1个聚簇索引；支持多列组合索引。

- 列选择原则：

- 关键列越少越好，且列值非重复度越高越好

- 绝对不适合频繁增删改的列（每次修改可能触发整张表存储空间重排，维护成本极高）

- 适用场景：很少对基表进行增删操作、很少修改变长列的表。

2. 非聚簇索引（二级索引/辅助索引/文件索引）

- 核心特点：独立于数据表，建立"关键列值+数据存储位置"的索引目录，需要额外磁盘空间存储。

- 硬性规则：一个表可以建立多个非聚簇索引。

- 优势：关键列值的增删改仅影响索引文件，不会改动数据表本身，对系统性能影响小。

- 列选择原则：不同索引结构对列值重复度要求不同（哈希索引要求高，位图索引要求低）。

视角2：索引的数据结构类型

1. 哈希（Hash）索引

- 实现原理：基于哈希表，对索引列计算哈希码，存储"哈希码+指向数据行的指针"；哈希冲突采用链表解决。

- 查询特性：仅支持精确匹配索引所有列的查询；结构紧凑，无冲突时查询速度极快。

- 局限性：哈希冲突多会显著降低查询速度，且索引维护代价大幅升高。

2. B树家族索引（核心为B-树）

- 基础特性：平衡多叉树，节点为 [key, data] 二元组；所有节点的key有序，左子树key < 当前节点key < 右子树key。

- 动态平衡规则：m阶B-树的非根节点关键字数n满足：\boldsymbol{\frac{m}{2}-1 \leq n \leq m-1}。

- 核心操作：插入、查询、删除均基于二分查找，自动维持树的平衡。

- 极致优势：树高与key数量呈对数关系，检索复杂度为\boldsymbol{O(log_dN)}（d为树的度）。

- 直观示例：度为1001、高度为3（含根节点）的B-树，可存储超过10亿个key，仅需3次磁盘查找即可定位记录。

3. 其他索引类型

- 位图（BitMap）索引：PPT仅提及分类，无详细内容。

- B树家族还包含B树、B+树：PPT未展开讲解。

三、索引列选择黄金原则

优先在以下列上建立索引：

1. 主键列

2. UNIQUE唯一约束列

3. 频繁用于查询条件的列

4. 唯一值数/总行数比值尽可能高的列（列值重复度越低，索引定位精度越高）

数据索引通俗案例讲解
一、索引基础：为什么要有索引？

核心类比：图书馆找书

- 全表搜索（顺序读取）：你要找《空间数据库原理》，不看任何目录，从图书馆1楼第1个书架第1本书开始，一本一本翻，直到找到为止。
- 对应PPT：逐行对比查询条件，数据量越大越慢。如果图书馆有100万本书，你可能要找好几天。
- 索引搜索（选择性读取）：你先查门口的总目录，找到“计算机类→P208号书架→第3层”，直接走过去拿书。
- 对应PPT：先查索引定位存储地址，再直接读数据。100万本书，你可能1分钟就找到了。

一句话总结：索引就是数据库的“目录”，用少量空间换海量时间。



二、索引分类1：聚簇索引 vs 非聚簇索引

核心类比：图书馆的“书本身” vs “门口的目录册”

1. 聚簇索引（一级/主索引）

对应场景：图书馆里书的实际摆放顺序

- 图书馆规定：所有书必须按“ISBN号”从小到大摆放在书架上。
- 你查ISBN号目录，找到《空间数据库》的ISBN是9787030456789，直接去对应书架位置拿书——书的物理位置和索引顺序完全一致。

对应PPT所有规则：

- ✅ 一个表只能有1个聚簇索引：图书馆的书只能按一种主要顺序摆放（要么按ISBN，要么按分类号，不能同时按两种）
- ✅ 无需额外存储空间：索引就是书本身的顺序，不用单独印目录
- ✅ 绝对不能频繁改：如果突然要求把所有书按“书名拼音”重新摆一遍，整个图书馆要瘫痪好几天——对应聚簇索引列增删改会触发整张表数据重排，维护成本极高
- ✅ 适用场景：很少改动的静态表（比如历史数据、字典表）

2. 非聚簇索引（二级/辅助索引）

对应场景：图书馆门口的各种独立目录册

- 除了主摆放顺序（ISBN），图书馆还会印：
- 《书名目录册》：按书名拼音排序，标注对应ISBN号
- 《作者目录册》：按作者姓氏排序，标注对应ISBN号
- 《出版社目录册》：按出版社名称排序，标注对应ISBN号
- 你查《作者目录册》找到“程昌秀→ISBN 9787030456789”，再去书架拿书——目录是独立的，不影响书的实际摆放。

对应PPT所有规则：

- ✅ 一个表可以有多个非聚簇索引：图书馆可以印N种不同的目录册
- ✅ 需要额外磁盘空间：每本目录册都要单独用纸印刷
- ✅ 增删改影响小：新书上架，只要在所有目录册里加一行就行，不用挪动书架上的书
- ✅ 列重复度要求不同：
- 作者目录（哈希索引）：适合作者名字重复少的情况，如果有1000个“张三”，这个目录就不好用
- 出版社目录（位图索引）：适合出版社数量少的情况（全国只有几百家出版社），用位图标记非常高效



三、索引分类2：哈希索引 vs B-树索引

1. 哈希索引

对应场景：快递驿站取件

- 快递员给每个快递生成一个唯一取件码（比如7-2-3456），把快递放到对应编号的格子里。
- 你报取件码“7-2-3456”，快递员直接走到7号柜第2层第3456号格子，1秒钟拿出你的快递——哈希函数把“手机号/快递单号”映射成“格子编号”，直接定位。

对应PPT所有规则：

- ✅ 仅支持精确匹配：你不能说“给我拿个3456左右的快递”，必须报完整的取件码——对应哈希索引只能精确匹配索引所有列，不支持范围查询（>、<、between）
- ✅ 无冲突时查询极快：正常情况下，取件比翻目录快得多
- ✅ 哈希冲突的问题：如果两个快递生成了同一个取件码（概率极低），快递员就要在那个格子里一个个翻快递单核对——对应冲突时需要遍历链表，查询和维护成本骤升

2. B-树索引（数据库最常用）

对应场景：图书馆的多层分级目录

- 总目录（根节点）：1楼→2楼→3楼→4楼
- 3楼目录（中间节点）：A区（计算机）→B区（地理）→C区（数学）
- A区目录（中间节点）：第1书架→第2书架→…→第20书架
- 第5书架目录（叶子节点）：第1层→第2层→第3层→第4层
- 你找《空间数据库》：总目录→3楼→A区→第5书架→第3层，4步找到。

对应PPT所有规则：

- ✅ 平衡多叉树：每一层的目录页都尽量填满，不会出现某一层特别长的情况
- ✅ 动态平衡规则：m阶B-树非根节点关键字数 \frac{m}{2}-1 \leq n \leq m-1——就像每个书架最多放100本书，最少放50本，空太多就合并，满了就拆分
- ✅ 检索复杂度 O(log_dN)：树高和数据量呈对数关系
- 对应PPT直观例子：度为1001、高度为3的B-树，可存10亿个key，仅需3次查找——就像从全国图书馆总目录→省图书馆→市图书馆→书架，3步找到任何一本书
- ✅ 支持范围查询和排序：你可以查“所有ISBN号在9787030000000到9787030999999之间的书”，B-树可以直接遍历叶子节点，哈希索引做不到



四、索引列选择黄金原则（图书馆版）

优先在这些“列”上建目录：

1. 主键列：ISBN号——每个书唯一，绝对不会重复，是最好的聚簇索引列
2. UNIQUE唯一约束列：图书条码号——和ISBN一样唯一
3. 频繁查询列：书名、作者名——大家最常按这两个找书
4. 唯一值占比高的列：作者名（重复少）> 出版社名（重复较多）> 图书开本（几乎全是16开，建索引完全没用）

反例：不要在“图书页数”上建索引——几乎没人会说“给我找所有234页的书”，建了也是浪费空间。