一、索引基础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页的书”建了也是浪费空间。