印刻数据库

外观

DM 索引原理 ​

索引的作用 ​

  • 提高查询效率：通过索引可以快速定位到需要查询的数据，减少扫描的数据量
  • 加速排序和分组操作：索引通常是有序的，可以直接用于排序和分组操作
  • 保证数据唯一性：唯一索引可以保证列值的唯一性
  • 加速表连接操作：在表连接操作中，索引可以提高连接效率

索引的代价 ​

  • 占用存储空间：索引需要占用额外的存储空间
  • 降低写操作性能：插入、更新和删除操作需要维护索引，会降低写操作性能
  • 增加维护成本：需要定期维护和优化索引

索引的基本原理 ​

B树索引原理 ​

B树（Balanced Tree）是一种平衡的多路搜索树，是达梦数据库中最常用的索引结构。

B树的结构 ​

  • 根节点：B树的根节点，包含指向子节点的指针
  • 内部节点：根节点和叶子节点之间的节点，只包含索引键和指向子节点的指针
  • 叶子节点：B树的最底层节点，包含索引键和指向数据行的指针
  • 阶数：B树的阶数指的是每个节点最多可以包含的子节点数量

B树的特点 ​

  • 平衡性：B树的所有叶子节点都在同一层，保证了查询的最坏时间复杂度为O(log n)
  • 多路搜索：每个节点可以包含多个索引键和子节点指针，减少了树的高度
  • 有序性：B树中的索引键是有序排列的，便于范围查询
  • 高效的插入和删除：B树的插入和删除操作会自动调整树的结构，保持平衡性

B+树索引 ​

达梦数据库实际上使用的是B+树索引，B+树是B树的一种变体，具有以下特点：

  • 内部节点只包含索引键，不包含数据指针
  • 所有数据指针都存储在叶子节点中
  • 叶子节点之间通过链表连接，便于范围查询
  • 叶子节点包含了所有索引键，便于全索引扫描

哈希索引原理 ​

哈希索引是基于哈希表实现的索引，适用于等值查询。

哈希索引的结构 ​

  • 哈希表：用于存储索引键和数据指针的映射关系
  • 哈希函数：将索引键转换为哈希值
  • 桶：哈希表中的存储单元，每个桶可以存储多个键值对

哈希索引的特点 ​

  • 等值查询效率高：可以在O(1)时间内定位到数据
  • 不支持范围查询：哈希值是无序的，无法进行范围查询
  • 不支持排序：哈希值是无序的，无法直接用于排序
  • 适合高基数列：适合列值分布均匀的高基数列

位图索引原理 ​

位图索引是一种基于位图的索引，适用于低基数列。

位图索引的结构 ​

  • 位图：对于每个不同的列值，创建一个位图，位图中的每一位表示对应行是否包含该值
  • 位运算：通过位运算（与、或、非）来处理查询条件

位图索引的特点 ​

  • 适合低基数列：适合列值数量较少的列，如性别、状态等
  • 高效的多列查询：可以通过位运算高效处理多列查询条件
  • 不适合高并发写操作：插入、更新和删除操作会导致位图的大量修改
  • 存储空间占用小：对于低基数列，位图索引占用的存储空间非常小

索引类型 ​

达梦数据库支持多种类型的索引，每种索引都有其特定的适用场景。

普通索引 ​

  • 定义：最基本的索引类型，基于B+树实现
  • 适用场景：适用于大多数查询场景，包括等值查询、范围查询、排序等
  • 创建语法：
    sql
    CREATE INDEX idx_name ON table_name(column1, column2);

唯一索引 ​

  • 定义：保证索引列的值唯一，基于B+树实现
  • 适用场景：用于需要保证列值唯一性的场景，如主键、唯一约束等
  • 创建语法：
    sql
    CREATE UNIQUE INDEX idx_name ON table_name(column1, column2);

主键索引 ​

  • 定义：特殊的唯一索引，用于标识表中的主键
  • 适用场景：用于表的主键约束
  • 创建语法：
    sql
    ALTER TABLE table_name ADD PRIMARY KEY(column1, column2);

函数索引 ​

  • 定义：基于函数或表达式的索引
  • 适用场景：用于频繁使用函数或表达式的查询
  • 创建语法：
    sql
    CREATE INDEX idx_name ON table_name(UPPER(column1));

位图索引 ​

  • 定义：基于位图的索引，适用于低基数列
  • 适用场景：用于低基数列的查询，如性别、状态等
  • 创建语法：
    sql
    CREATE BITMAP INDEX idx_name ON table_name(column1);

全文索引 ​

  • 定义：用于全文检索的索引
  • 适用场景：用于文本内容的全文检索
  • 创建语法：
    sql
    CREATE FULLTEXT INDEX idx_name ON table_name(column1);

空间索引 ​

  • 定义：用于空间数据的索引
  • 适用场景：用于地理信息系统（GIS）中的空间数据查询
  • 创建语法：
    sql
    CREATE SPATIAL INDEX idx_name ON table_name(geometry_column);

索引设计原则 ​

选择合适的列创建索引 ​

  • 高基数列：选择基数高的列创建索引，如身份证号、手机号等
  • 频繁查询的列：选择频繁出现在WHERE子句中的列创建索引
  • 连接列：选择用于表连接的列创建索引
  • 排序和分组列：选择用于排序和分组的列创建索引

避免不合适的索引 ​

  • 低基数列：避免为低基数列创建普通索引（可以考虑位图索引）
  • 频繁更新的列：避免为频繁更新的列创建索引，因为更新操作需要维护索引
  • 小表：对于小表，全表扫描可能比使用索引更高效
  • 重复索引：避免创建重复的索引，如为同一列创建多个索引

复合索引设计 ​

  • 列的顺序：将选择性高的列放在复合索引的前面
  • 最左前缀原则：复合索引遵循最左前缀原则，只有使用了最左前缀的查询才能使用索引
  • 避免过多列：复合索引的列数不宜过多，一般不超过4-5列
  • 考虑查询模式：根据实际的查询模式设计复合索引

索引维护 ​

  • 定期重建索引：当索引碎片较多时，需要重建索引
  • 更新统计信息：定期更新表和索引的统计信息，确保优化器生成准确的执行计划
  • 监控索引使用情况：定期监控索引的使用情况，删除未使用的索引
  • 避免过度索引：过多的索引会降低写操作性能，需要平衡查询性能和写操作性能

索引的使用 ​

索引的选择 ​

优化器会根据查询条件和统计信息自动选择合适的索引，影响索引选择的因素包括：

  • 索引的选择性：索引的选择性越高，优化器越倾向于使用该索引
  • 查询的数据量：如果查询的数据量超过表数据量的一定比例，优化器可能会选择全表扫描
  • 索引的大小：索引越大，扫描索引的成本越高
  • 统计信息的准确性：准确的统计信息有助于优化器选择合适的索引

强制使用索引 ​

在某些情况下，可以使用HINT强制优化器使用特定的索引：

sql
SELECT /*+ INDEX(table_name idx_name) */ * FROM table_name WHERE column1 = 'value';

索引的限制 ​

  • 索引列的数据类型限制：某些数据类型不适合创建索引，如大对象类型
  • 索引长度限制：索引列的总长度有一定限制
  • 索引数量限制：每个表可以创建的索引数量有限制
  • 查询条件限制：某些查询条件无法使用索引，如使用函数或表达式的查询

索引的实现机制 ​

索引的存储结构 ​

  • 索引段：索引数据存储在索引段中
  • B+树结构：大多数索引基于B+树结构实现
  • 叶子节点：存储索引键和数据指针
  • 非叶子节点：存储索引键和指向子节点的指针

索引的创建过程 ​

  1. 扫描表数据，提取索引列的值
  2. 对索引列的值进行排序
  3. 创建B+树结构，将索引键和数据指针存储到树中
  4. 更新数据字典，记录索引信息

索引的维护过程 ​

  • 插入操作：向索引中插入新的索引键和数据指针
  • 更新操作：如果更新的列包含在索引中，需要更新索引
  • 删除操作：从索引中删除对应的索引键和数据指针
  • 分裂和合并：当索引节点满时，会进行分裂操作；当索引节点稀疏时，会进行合并操作

版本差异 ​

DM7 索引实现 ​

  • 支持基本的索引类型，包括普通索引、唯一索引、主键索引等
  • 基于B+树实现索引
  • 提供了基本的索引管理功能

DM8 索引实现 ​

  • 增强了索引功能，支持更多的索引类型，如函数索引、位图索引等
  • 优化了索引的存储结构，提高了索引的访问效率
  • 改进了索引的维护算法，提高了写操作性能
  • 增加了索引压缩功能，减少了索引的存储空间占用
  • 提供了更多的索引监控视图，便于监控索引的使用情况

最佳实践 ​

索引设计建议 ​

  • 根据查询模式设计索引：分析实际的查询模式，设计合适的索引
  • 优先考虑复合索引：对于多列查询，优先考虑使用复合索引
  • 遵循最左前缀原则：设计复合索引时，遵循最左前缀原则
  • 定期分析索引使用情况：使用DM提供的工具分析索引的使用情况，优化索引设计
  • 平衡查询性能和写操作性能：避免创建过多的索引，影响写操作性能

索引性能优化 ​

  • 选择合适的索引类型：根据列的特点和查询需求选择合适的索引类型
  • 优化索引列的顺序：将选择性高的列放在复合索引的前面
  • 定期重建索引：当索引碎片较多时，重建索引可以提高性能
  • 更新统计信息：定期更新表和索引的统计信息，确保优化器生成准确的执行计划
  • 使用索引组织表：对于频繁根据主键查询的表，可以考虑使用索引组织表

索引监控建议 ​

  • 监控索引使用情况：使用V$INDEX_USAGE视图监控索引的使用情况
  • 监控索引碎片：使用V$INDEX_FRAGMENTATION视图监控索引碎片
  • 监控索引统计信息：使用V$INDEX_STATISTICS视图监控索引的统计信息
  • 定期审计索引：定期审计索引的使用情况，删除未使用的索引

常见问题（FAQ） ​

Q1: 如何查看表的索引信息？ ​

A1: 可以使用以下语句查看表的索引信息：

sql
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'table_name';
SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME = 'table_name';

Q2: 如何分析索引的使用情况？ ​

A2: 可以使用以下语句分析索引的使用情况：

sql
SELECT * FROM V$INDEX_USAGE WHERE TABLE_NAME = 'table_name';

Q3: 什么是索引的选择性？ ​

A3: 索引的选择性是指索引列中不同值的数量与表行数的比值，选择性越高，索引的效率越高。计算公式为：

选择性 = 不同值的数量 / 表行数

Q4: 如何重建索引？ ​

A4: 可以使用以下语句重建索引：

sql
ALTER INDEX idx_name REBUILD;

Q5: 为什么我的查询没有使用索引？ ​

A5: 查询没有使用索引的原因可能包括：

  • 查询条件没有使用索引的最左前缀
  • 查询的数据量超过表数据量的一定比例，优化器选择了全表扫描
  • 统计信息不准确，导致优化器做出了错误的选择
  • 查询条件使用了函数或表达式，无法使用索引
  • 索引的选择性太低，优化器认为使用索引不划算

Q6: 如何优化复合索引的设计？ ​

A6: 优化复合索引设计的方法包括：

  • 将选择性高的列放在前面
  • 遵循最左前缀原则
  • 考虑实际的查询模式
  • 避免过多的列
  • 定期分析索引的使用情况，调整索引设计