当前位置：首页 > news >正文

深入理解索引（一）

news 文章来源：https://blog.csdn.net/qq_45732829/article/details/143992499 2025/5/9 6:38:05

1.引言

在数据库和数据结构中，索引（Index）是一种用于提高数据检索速度的重要机制。本文将详细深入介绍索引。

2. 索引的分类

2.1 B - 树索引（B - Tree Index）

2.1.1 结构细节

树状结构：B - 树索引是一种平衡的多叉树结构。它由根节点、分支节点和叶子节点组成。根节点位于树的顶部，包含指向子节点的指针和索引键值范围。分支节点用于引导搜索路径，也包含指向子节点的指针和索引键值范围。叶子节点存储实际的索引键值和对应的行标识符（ROWID），ROWID 用于定位表中的数据行。
有序存储：索引键值在树的节点中是按照一定顺序（通常是升序）排列的。这种有序排列使得范围查询和排序查询更加高效。例如，在一个存储员工工资信息的表中，如果对工资列建立了 B - 树索引，工资数据会按照从小到大的顺序存储在索引的叶子节点中。

2.1.2 查询场景优势

等值查询高效：当进行等值查询（如查询工资等于 5000 元的员工）时，数据库可以从根节点开始，沿着索引树的分支节点快速定位到存储该工资值的叶子节点，然后通过 ROWID 找到对应的员工记录。这个过程避免了全表扫描，大大提高了查询速度。
范围查询支持良好：对于范围查询（如查询工资在 4000 - 6000 元之间的员工），由于索引键值的有序性，数据库可以顺序读取叶子节点中的数据，找到符合范围的索引键值及其对应的 ROWID，从而获取相应的员工记录。这种顺序读取减少了磁盘 I/O 的随机访问，提高了查询效率。

2.1.3 更新操作影响

插入和删除影响：当插入新数据时，B - 树索引可能需要调整树的结构来保持平衡。例如，如果插入一个新的工资值，可能会导致索引节点的分裂或合并操作。删除数据时也可能导致节点的调整。这些操作会消耗一定的系统资源，但 Oracle 数据库有相应的机制来尽量减少这种影响。
更新索引列影响：如果更新的是索引列的值，那么索引也需要相应地更新。如果更新后的索引列值仍然在原索引键值的范围内，可能只需要在叶子节点内进行调整；如果超出了原范围，可能会导致节点的重新排列。

2.2 位图索引（Bitmap Index）

2.2.1 结构细节

位图表示：位图索引针对表中的每一个可能的索引值都有一个对应的位图。位图是由一系列的位（0 或 1）组成，位图中的每一位代表表中的一行。如果位的值为 1，表示该行包含对应的索引值；如果为 0，则表示该行不包含。例如，在一个有性别（男 / 女）列的客户表中，对于 “男” 这个索引值，位图中对应男性客户行的位为 1，女性客户行的位为 0。
存储空间节省：对于具有低基数（即不同值的数量相对较少）的列，位图索引可以有效地节省存储空间。因为它不需要像 B - 树索引那样存储每个索引键值和 ROWID，而是通过位图来表示数据分布。

2.2.2 查询场景优势

低基数列查询高效：在查询低基数列时，位图索引表现出色。例如，在查询所有男性客户的信息时，数据库只需对 “男” 对应的位图进行扫描，找到位为 1 的行，就可以快速定位到男性客户的记录。对于多条件查询（如查询男性且年龄大于 30 岁的客户），位图索引可以通过位运算（如 AND、OR 操作）来快速合并查询条件，提高查询效率。

2.2.3 更新操作影响

更新复杂性：位图索引在更新操作时比较复杂。当插入或删除数据时，需要更新多个位图。例如，在客户表中插入一个新的男性客户，需要更新性别列的位图，将新客户对应的位置为 1。而且，由于位运算的特性，在高并发环境下，位图索引的更新可能会导致锁竞争等问题，影响系统性能。

2.3 函数索引（Function - Based Index）

2.3.1 结构细节

基于函数结果存储：函数索引不是直接对列的值进行索引，而是对列经过特定函数或表达式计算后的结果进行索引。例如，在一个存储产品销售日期的表中，对日期列建立一个提取年份的函数索引，索引中存储的是经过提取年份函数计算后的结果（如 2024）和对应的 ROWID。

2.3.2 查询场景优势

函数查询加速：当查询条件经常涉及对列的函数操作时，函数索引可以大大提高查询效率。比如，在上述产品销售日期表中，如果经常查询某一年的销售情况，通过提取年份的函数索引，数据库可以直接定位到该年份对应的销售记录，而不需要对每个销售日期进行函数计算后再查询。

2.3.3 更新操作影响

更新时重新计算：当更新索引列时，由于函数索引是基于函数结果的，需要重新计算函数值来更新索引。如果函数计算比较复杂，可能会增加更新操作的成本。而且，函数索引的创建和维护需要考虑函数的确定性（即相同的输入总是得到相同的输出），否则可能会导致索引不一致等问题。

2.4 全文索引（Full - Text Index）

2.4.1 结构细节

文本内容分析：全文索引用于对文本数据进行索引，它会对文本中的单词、词组等进行分析和存储。Oracle 会将文本内容分解为一个个的词汇单元（token），并记录这些词汇单元在文本中的位置等信息。例如，在一个包含文章内容的表中，全文索引会对文章中的每个单词进行索引，包括单词出现的频率、位置等。

2.4.2 查询场景优势

文本搜索高效：当进行文本搜索（如查询包含某个特定关键词的文章）时，全文索引可以快速定位到相关的文本内容。它支持多种文本搜索方式，如模糊搜索、词干搜索（如搜索 “run” 可以匹配 “running”）等，为文本相关的应用提供了强大的搜索功能。

2.4.3 更新操作影响

更新成本高：由于全文索引需要对文本内容进行复杂的分析和处理，在更新文本数据时，全文索引的更新成本相对较高。特别是对于大量文本数据的更新，可能会导致系统性能下降。

2.5 反向键索引（Reverse Key Index）

2.5.1 结构细节

键值反转存储：反向键索引是一种特殊的 B - 树索引，它将索引键值的字节顺序反转后存储。例如，对于索引键值为 1234 的列，在反向键索引中存储为 4321。这种反转存储主要是为了避免在插入数据时，由于索引键值的顺序性导致索引树的不平衡。

2.5.2 查询场景优势

插入热点问题缓解：在一些应用场景中，如使用序列生成的主键列，数据可能会按照顺序插入，导致索引树的右侧分支过度增长（插入热点问题）。反向键索引通过反转键值，使得插入的数据在索引树中的分布更加均匀，从而在一定程度上缓解了插入热点问题，提高了插入操作的性能。

2.5.3 查询性能权衡

反向键索引在查询性能上可能会有一定的损失。因为在查询时，需要先将查询条件中的键值反转，然后再在索引树中进行搜索。对于范围查询，反向键索引的性能通常不如普通 B - 树索引，因为反转后的键值顺序打乱了原有的范围顺序。

3. 索引的创建

3.1 B - 树索引创建

3.1.1 语法

基本的创建 B - 树索引的语法是:

CREATE INDEX index_name ON table_name (column_name [ASC|DESC],...);

其中，index_name是要创建的索引名称，table_name是索引所属的表名，column_name是要建立索引的列名。可以指定多个列来创建组合索引，列名之间用逗号分隔。ASC或DESC用于指定索引列的排序方式，默认为ASC（升序）。

3.1.2 示例

假设存在一个员工表employees，包含employee_id（员工编号）、employee_name（员工姓名）和department_id（部门编号）列。如果经常根据员工姓名进行查询，可以创建一个 B - 树索引：

CREATE INDEX idx_employee_name ON employees (employee_name);

3.1.3 考虑因素：

选择合适的列：应该选择那些经常在查询条件中出现的列建立索引。同时，要避免对数据变化频繁的列过度建立索引，因为这会增加数据更新的成本。例如，在一个日志记录表中，日志内容列通常不需要建立索引，因为很少会根据日志内容进行查询，而且日志内容可能会频繁变化。
组合索引的列顺序：当创建组合索引时，列的顺序很重要。应该将最常用于过滤数据的列放在前面。例如，在一个订单表中，如果经常根据客户编号和订单日期进行查询，且客户编号的选择性更高（不同客户编号的数量相对订单日期的组合更多），那么组合索引的顺序应该是(customer_id, order_date)。

3.2 位图索引创建

3.2.1 语法

创建位图索引的语法为:

CREATE BITMAP INDEX bitmap_index_name ON table_name (column_name);

其中，bitmap_index_name是位图索引的名称，table_name是所属表名，column_name是要建立位图索引的列名。

3.2.2 示例

对于一个包含产品类别列product_category的产品表products，如果产品类别列的取值较少（低基数），可以创建位图索引：

CREATE BITMAP INDEX bitmap_product_category ON products (product_category);

3.2.3 考虑因素：

适用场景：主要适用于低基数列，即列的取值范围较小且重复值较多的情况。如性别、状态等列。对于高基数列，使用位图索引可能会导致存储空间过大和性能下降。
更新操作影响：要考虑到位图索引在更新操作时比较复杂。当插入或删除数据时，需要更新多个位图，在高并发环境下可能会导致锁竞争等问题，影响系统性能。

3.3 函数索引创建

3.3.1 语法

创建函数索引的语法是:

CREATE INDEX function_index_name ON table_name (function(column_name));

其中，function_index_name是函数索引的名称，table_name是所属表名，function(column_name)是基于列column_name的函数表达式。

3.3.2 示例

在一个销售记录表sales中，包含销售日期列sale_date，如果经常需要查询某一月份的销售记录，可以创建一个提取月份的函数索引：

CREATE INDEX idx_sale_month ON sales (EXTRACT(MONTH FROM sale_date));

3.3.3 考虑因素：

函数确定性：函数索引的创建和维护需要考虑函数的确定性，即相同的输入总是得到相同的输出。否则可能会导致索引不一致等问题。
更新成本：当更新索引列时，由于函数索引是基于函数结果的，需要重新计算函数值来更新索引。如果函数计算比较复杂，可能会增加更新操作的成本。

3.4 全文索引创建

3.4.1 语法（以 Oracle Text为例）

首先需要安装和配置 Oracle Text 组件。创建全文索引的基本语法如下：

CREATE INDEX fulltext_index_name ON table_name (column_name) INDEXTYPE IS CTXSYS.CONTEXT;

3.4.2 示例

在一个文档内容表documents中，包含content（文档内容）列，可以创建全文索引：

CREATE INDEX idx_document_content ON documents (content) INDEXTYPE IS CTXSYS.CONTEXT;

3.4.3 考虑因素：

文本分析要求：在创建全文索引时，需要考虑对文本内容的分析要求，如是否需要进行词干提取、停用词过滤等操作。这些操作可以通过 Oracle Text 的参数进行配置。
更新成本：由于全文索引需要对文本内容进行复杂的分析和处理，在更新文本数据时，全文索引的更新成本相对较高。特别是对于大量文本数据的更新，可能会导致系统性能下降。

4. 管理索引

4.1 查看索引信息

数据字典视图：可以使用数据字典视图来查看索引的相关信息。USER_INDEXES视图显示当前用户拥有的索引信息，包括索引名称、所属表、索引类型等。ALL_INDEXES视图可以查看当前用户有权访问的所有索引信息，DBA_INDEXES视图（需要管理员权限）则可以查看数据库中的所有索引信息。
示例：通过以下查询可以查看用户自己创建的索引：

SELECT index_name, table_name, index_type FROM USER_INDEXES;

4.2 重建和维护索引

索引碎片问题：随着数据的插入、更新和删除操作，索引可能会变得碎片化，影响其性能。例如，在频繁更新数据的表中，B - 树索引的节点可能会频繁分裂和合并，导致索引结构不紧凑，降低查询效率。
重建索引方法：可以通过重建索引来优化其性能。对于 B - 树索引，使用ALTER INDEX index_name REBUILD;语句进行重建。重建索引可以重新组织索引结构，减少碎片，提高索引的效率。

4.3 删除索引

语法：当索引不再需要时，可以使用DROP INDEX index_name;语句删除索引。
考虑因素：在删除索引之前，需要谨慎考虑。应该评估该索引是否真的不再使用，因为删除索引后可能会导致相关查询性能下降。如果是为了测试或者临时调整，可以先备份索引定义，以便在需要时重新创建。

未完待续
码字不易，宝贵经验分享不易，请各位支持原创，转载注明出处，多多关注作者，后续不定期分享DB基本知识和排障案例及经验、性能调优等。