一、DM8 索引管理概述
1.1 索引的基本概念
索引是数据库中用于加速数据检索的数据结构，它如同书籍的目录，能够帮助数据库引擎快速定位到目标数据行，从而避免全表扫描带来的性能损耗。在 DM8 数据库中，索引是以 B+ 树为主要存储结构的辅助对象，合理使用索引是数据库性能优化的关键手段。
1.2 索引的分类
DM8 支持多种类型的索引，以适应不同的业务场景：
- 普通索引：最常见的索引类型，用于加速等值查询和范围查询。
- 唯一索引：保证索引列的取值唯一，常用于主键之外的唯一约束。
- 聚集索引：数据行按索引键的顺序物理存储，每张表只能有一个聚集索引。
- 位图索引：适用于取值种类较少的列，如性别、状态等。
- 函数索引：基于列的表达式或函数创建，适用于查询条件包含函数的场景。
- 全文索引：用于文本数据的全文检索。
1.3 索引管理的整体流程
索引管理是一个贯穿数据库生命周期的持续过程，包括创建、维护、监控和删除等环节：
#publish-mermaid-1786118591521-0{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1786118591521-0 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1786118591521-0 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1786118591521-0 .error-icon{fill:#552222;}#publish-mermaid-1786118591521-0 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1786118591521-0 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1786118591521-0 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1786118591521-0 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1786118591521-0 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1786118591521-0 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1786118591521-0 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1786118591521-0 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1786118591521-0 .marker.cross{stroke:#333333;}#publish-mermaid-1786118591521-0 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1786118591521-0 p{margin:0;}#publish-mermaid-1786118591521-0 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1786118591521-0 .cluster-label text{fill:#333;}#publish-mermaid-1786118591521-0 .cluster-label span{color:#333;}#publish-mermaid-1786118591521-0 .cluster-label span p{background-color:transparent;}#publish-mermaid-1786118591521-0 .label text,#publish-mermaid-1786118591521-0 span{fill:#333;color:#333;}#publish-mermaid-1786118591521-0 .node rect,#publish-mermaid-1786118591521-0 .node circle,#publish-mermaid-1786118591521-0 .node ellipse,#publish-mermaid-1786118591521-0 .node polygon,#publish-mermaid-1786118591521-0 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786118591521-0 .rough-node .label text,#publish-mermaid-1786118591521-0 .node .label text,#publish-mermaid-1786118591521-0 .image-shape .label,#publish-mermaid-1786118591521-0 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1786118591521-0 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1786118591521-0 .rough-node .label,#publish-mermaid-1786118591521-0 .node .label,#publish-mermaid-1786118591521-0 .image-shape .label,#publish-mermaid-1786118591521-0 .icon-shape .label{text-align:center;}#publish-mermaid-1786118591521-0 .node.clickable{cursor:pointer;}#publish-mermaid-1786118591521-0 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1786118591521-0 .arrowheadPath{fill:#333333;}#publish-mermaid-1786118591521-0 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1786118591521-0 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1786118591521-0 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786118591521-0 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1786118591521-0 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786118591521-0 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1786118591521-0 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1786118591521-0 .cluster text{fill:#333;}#publish-mermaid-1786118591521-0 .cluster span{color:#333;}#publish-mermaid-1786118591521-0 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1786118591521-0 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1786118591521-0 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1786118591521-0 .icon-shape,#publish-mermaid-1786118591521-0 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786118591521-0 .icon-shape p,#publish-mermaid-1786118591521-0 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1786118591521-0 .icon-shape .label rect,#publish-mermaid-1786118591521-0 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786118591521-0 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1786118591521-0 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1786118591521-0 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1786118591521-0 [data-look=\”neo\”].node rect,#publish-mermaid-1786118591521-0 [data-look=\”neo\”].cluster rect,#publish-mermaid-1786118591521-0 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786118591521-0 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1786118591521-0 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786118591521-0 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786118591521-0 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1786118591521-0 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786118591521-0 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1786118591521-0 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786118591521-0 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786118591521-0 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}有效无效
需求分析
选择索引类型
创建索引
监控使用情况
是否有效
定期维护
重建或删除
二、创建索引
2.1 创建普通索引
普通索引是最基础的索引形式，使用 CREATE INDEX 语句创建。建议在 WHERE 条件频繁出现的列上创建。
— 在 employees 表的 last_name 列上创建普通索引
CREATE INDEX idx_emp_last_name ON employees(last_name);
— 在多列上创建复合索引
CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary);
2.2 创建唯一索引
唯一索引在加速查询的同时，强制保证索引列的取值唯一性，可以作为业务唯一约束的兜底。
— 创建唯一索引
CREATE UNIQUE INDEX uk_emp_email ON employees(email);
— 使用表约束方式创建
ALTER TABLE employees ADD CONSTRAINT uk_emp_phone UNIQUE(phone);
2.3 创建位图索引
位图索引适用于低基数列，例如性别、状态等取值种类有限的场景。在 DM8 中，位图索引的创建语法如下：
— 在订单状态列上创建位图索引
CREATE BITMAP INDEX idx_order_status ON orders(status);
2.4 创建函数索引
当查询条件包含函数或表达式时，普通索引无法命中，需要创建函数索引来提升性能。
— 在姓名的大写形式上创建函数索引
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));
— 在计算列上创建索引
CREATE INDEX idx_emp_sal_yearly ON employees(salary * 12);
三、修改与维护索引
3.1 重命名索引
当业务需要调整索引命名规范时，可以使用 ALTER INDEX 语句重命名索引：
— 重命名索引
ALTER INDEX idx_emp_last_name RENAME TO idx_emp_lname;
3.2 重建索引
索引在长期使用过程中可能产生碎片，导致查询性能下降。重建索引可以消除碎片，回收存储空间：
— 重建索引
ALTER INDEX idx_emp_lname REBUILD;
— 在线重建索引，避免阻塞业务
ALTER INDEX idx_emp_lname REBUILD ONLINE;
3.3 监控索引使用情况
DM8 提供了系统视图，用于监控索引的使用情况，帮助识别低效或未使用的索引：
— 查看索引基本信息
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'EMPLOYEES';
— 查看索引的列信息
SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME = 'EMPLOYEES';
— 查看索引大小
SELECT NAME, USED_SPACE FROM V$OBJECT_USAGE WHERE OBJECT_TYPE = 'INDEX';
3.4 索引维护流程
索引维护的完整流程如下：
#publish-mermaid-1786118591570-1{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1786118591570-1 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1786118591570-1 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1786118591570-1 .error-icon{fill:#552222;}#publish-mermaid-1786118591570-1 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1786118591570-1 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1786118591570-1 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1786118591570-1 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1786118591570-1 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1786118591570-1 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1786118591570-1 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1786118591570-1 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1786118591570-1 .marker.cross{stroke:#333333;}#publish-mermaid-1786118591570-1 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1786118591570-1 p{margin:0;}#publish-mermaid-1786118591570-1 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1786118591570-1 .cluster-label text{fill:#333;}#publish-mermaid-1786118591570-1 .cluster-label span{color:#333;}#publish-mermaid-1786118591570-1 .cluster-label span p{background-color:transparent;}#publish-mermaid-1786118591570-1 .label text,#publish-mermaid-1786118591570-1 span{fill:#333;color:#333;}#publish-mermaid-1786118591570-1 .node rect,#publish-mermaid-1786118591570-1 .node circle,#publish-mermaid-1786118591570-1 .node ellipse,#publish-mermaid-1786118591570-1 .node polygon,#publish-mermaid-1786118591570-1 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786118591570-1 .rough-node .label text,#publish-mermaid-1786118591570-1 .node .label text,#publish-mermaid-1786118591570-1 .image-shape .label,#publish-mermaid-1786118591570-1 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1786118591570-1 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1786118591570-1 .rough-node .label,#publish-mermaid-1786118591570-1 .node .label,#publish-mermaid-1786118591570-1 .image-shape .label,#publish-mermaid-1786118591570-1 .icon-shape .label{text-align:center;}#publish-mermaid-1786118591570-1 .node.clickable{cursor:pointer;}#publish-mermaid-1786118591570-1 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1786118591570-1 .arrowheadPath{fill:#333333;}#publish-mermaid-1786118591570-1 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1786118591570-1 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1786118591570-1 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786118591570-1 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1786118591570-1 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786118591570-1 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1786118591570-1 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1786118591570-1 .cluster text{fill:#333;}#publish-mermaid-1786118591570-1 .cluster span{color:#333;}#publish-mermaid-1786118591570-1 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1786118591570-1 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1786118591570-1 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1786118591570-1 .icon-shape,#publish-mermaid-1786118591570-1 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786118591570-1 .icon-shape p,#publish-mermaid-1786118591570-1 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1786118591570-1 .icon-shape .label rect,#publish-mermaid-1786118591570-1 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786118591570-1 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1786118591570-1 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1786118591570-1 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1786118591570-1 [data-look=\”neo\”].node rect,#publish-mermaid-1786118591570-1 [data-look=\”neo\”].cluster rect,#publish-mermaid-1786118591570-1 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786118591570-1 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1786118591570-1 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786118591570-1 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786118591570-1 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1786118591570-1 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786118591570-1 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1786118591570-1 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786118591570-1 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786118591570-1 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}达标过低是否
启动索引监控
收集使用统计
使用率是否达标
检查碎片率
评估是否删除
碎片是否严重
执行重建
保持现状
更新统计信息
四、删除与禁用索引
4.1 禁用索引
在某些批量导入或临时性运维场景中，可以临时禁用索引以提升写入性能：
— 禁用索引
ALTER INDEX idx_emp_lname DISABLE;
— 启用索引
ALTER INDEX idx_emp_lname ENABLE;
4.2 删除索引
当确认索引不再需要或影响业务性能时，应及时删除以释放存储空间：
— 删除普通索引
DROP INDEX idx_emp_lname;
— 删除约束自动产生的索引需通过删除约束实现
ALTER TABLE employees DROP CONSTRAINT uk_emp_email;
4.3 处理无效索引
索引可能因硬件故障或异常停机变为无效状态，需要及时识别并修复：
— 查询无效索引
SELECT OWNER, INDEX_NAME, STATUS FROM ALL_INDEXES WHERE STATUS != 'VALID';
— 修复无效索引
ALTER INDEX idx_emp_lname REBUILD;
五、索引管理最佳实践
5.1 索引设计原则
索引设计需要综合考虑查询性能与写入开销，遵循以下原则：
5.2 索引性能优化
索引性能优化是一个持续迭代的过程，常用手段包括：
#publish-mermaid-1786118591616-2{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1786118591616-2 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1786118591616-2 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1786118591616-2 .error-icon{fill:#552222;}#publish-mermaid-1786118591616-2 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1786118591616-2 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1786118591616-2 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1786118591616-2 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1786118591616-2 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1786118591616-2 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1786118591616-2 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1786118591616-2 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1786118591616-2 .marker.cross{stroke:#333333;}#publish-mermaid-1786118591616-2 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1786118591616-2 p{margin:0;}#publish-mermaid-1786118591616-2 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1786118591616-2 .cluster-label text{fill:#333;}#publish-mermaid-1786118591616-2 .cluster-label span{color:#333;}#publish-mermaid-1786118591616-2 .cluster-label span p{background-color:transparent;}#publish-mermaid-1786118591616-2 .label text,#publish-mermaid-1786118591616-2 span{fill:#333;color:#333;}#publish-mermaid-1786118591616-2 .node rect,#publish-mermaid-1786118591616-2 .node circle,#publish-mermaid-1786118591616-2 .node ellipse,#publish-mermaid-1786118591616-2 .node polygon,#publish-mermaid-1786118591616-2 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786118591616-2 .rough-node .label text,#publish-mermaid-1786118591616-2 .node .label text,#publish-mermaid-1786118591616-2 .image-shape .label,#publish-mermaid-1786118591616-2 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1786118591616-2 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1786118591616-2 .rough-node .label,#publish-mermaid-1786118591616-2 .node .label,#publish-mermaid-1786118591616-2 .image-shape .label,#publish-mermaid-1786118591616-2 .icon-shape .label{text-align:center;}#publish-mermaid-1786118591616-2 .node.clickable{cursor:pointer;}#publish-mermaid-1786118591616-2 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1786118591616-2 .arrowheadPath{fill:#333333;}#publish-mermaid-1786118591616-2 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1786118591616-2 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1786118591616-2 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786118591616-2 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1786118591616-2 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786118591616-2 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1786118591616-2 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1786118591616-2 .cluster text{fill:#333;}#publish-mermaid-1786118591616-2 .cluster span{color:#333;}#publish-mermaid-1786118591616-2 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1786118591616-2 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1786118591616-2 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1786118591616-2 .icon-shape,#publish-mermaid-1786118591616-2 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1786118591616-2 .icon-shape p,#publish-mermaid-1786118591616-2 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1786118591616-2 .icon-shape .label rect,#publish-mermaid-1786118591616-2 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1786118591616-2 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1786118591616-2 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1786118591616-2 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1786118591616-2 [data-look=\”neo\”].node rect,#publish-mermaid-1786118591616-2 [data-look=\”neo\”].cluster rect,#publish-mermaid-1786118591616-2 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786118591616-2 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1786118591616-2 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1786118591616-2 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786118591616-2 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1786118591616-2 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786118591616-2 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1786118591616-2 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786118591616-2 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1786118591616-2 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}未走索引走索引是否
查询慢
查看执行计划
是否走索引
分析原因
索引不存在
函数包裹列
类型隐式转换
创建合适索引
改写查询或建函数索引
规范数据类型
评估索引效率
是否回表过多
考虑覆盖索引
优化完成
5.3 常见问题排查
管理索引过程中常见问题及处理思路：
— 查看执行计划
EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';
— 更新表统计信息
DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
六、总结
6.1 管理索引的核心要点
DM8 索引管理是一个系统化的工程，涉及创建、维护、监控、删除等多个环节。合理的索引设计能够显著提升查询性能，但过度或不当的索引也会带来写入性能下降和存储浪费。
6.2 持续优化的方向
索引管理并非一次性的工作，需要结合业务变化持续优化：
