2. 首页 > 国产数据库教程 > 达梦DM教程 > 正文

DM教程FG019-达梦数据库索引优化实战

教程整理：风哥教程  |  更新时间：2026-02-01  |  教程分类：达梦DM教程  |  文档学习：0

内容简介：本文档风哥主要介绍DM达梦数据库的索引优化，涵盖索引的概念、类型、结构、作用，以及索引的创建、维护、监控和优化等内容，风哥教程参考DM官方文档《DM8性能优化手册》、《DM8SQL参考手册》等官方资料。本文档为DM数据库学习系列的第十九篇，重点介绍索引优化的详细步骤和实战操作。

Part01-基础概念与理论知识

1.1 索引概述

索引是数据库中用于提高查询性能的数据结构，通过创建索引可以加快数据的查找速度。

1.1.1 索引的定义

索引是一种数据结构，用于快速定位表中的数据，提高查询效率。

1.1.2 索引的特点

  • 提高查询速度：通过索引可以快速定位数据，减少数据扫描
  • 增加写操作开销：索引需要维护，会增加插入、更新、删除操作的开销
  • 占用存储空间：索引需要额外的存储空间
  • 优化排序操作：通过索引可以避免排序操作

1.2 索引类型

DM数据库支持多种类型的索引，不同类型的索引适用于不同的场景。

1.2.1 普通索引

  • 定义：最基本的索引类型，没有唯一性限制
  • 适用场景：一般查询条件
  • 创建语法：CREATE INDEX index_name ON table_name(column_name);

1.2.2 唯一索引

  • 定义：确保索引列的值唯一
  • 适用场景：需要确保列值唯一的场景
  • 创建语法：CREATE UNIQUE INDEX index_name ON table_name(column_name);

1.2.3 主键索引

  • 定义：主键列上的索引，自动创建
  • 适用场景：主键查询
  • 创建语法：在创建表时指定主键

1.2.4 复合索引

  • 定义：基于多个列创建的索引
  • 适用场景：多列查询条件
  • 创建语法：CREATE INDEX index_name ON table_name(column1, column2, …);

1.2.5 函数索引

  • 定义：基于函数表达式创建的索引
  • 适用场景：函数表达式查询
  • 创建语法：CREATE INDEX index_name ON table_name(function(column_name));

1.3 索引结构

风哥提示：

DM数据库的索引采用B+树结构，这是一种平衡树结构，适合范围查询和排序操作。

1.3.1 B+树结构

  • 根节点：索引的顶层节点
  • 中间节点：索引的中间层节点
  • 叶子节点：索引的底层节点，存储实际数据的指针
  • 有序性：索引按照键值有序排列

1.3.2 索引查找过程

  • 从根节点开始查找
  • 根据键值大小选择子节点
  • 重复上述过程，直到找到叶子节点
  • 从叶子节点获取实际数据的指针
  • 根据指针获取实际数据

1.4 索引的作用

索引在数据库中发挥着重要的作用，主要体现在以下几个方面。

1.4.1 提高查询速度

  • 减少数据扫描的范围
  • 快速定位数据
  • 提高查询响应时间

1.4.2 优化排序操作

  • 通过索引避免排序
  • 提高排序操作的效率
  • 减少排序的资源消耗

学习交流加群风哥微信: itpux-com

1.4.3 优化连接操作

  • 提高连接操作的效率
  • 减少连接操作的资源消耗
  • 加快连接查询的速度

Part02-生产环境规划与建议

2.1 索引规划

在生产环境中，合理的索引规划可以提高数据库的性能和稳定性。

2.1.1 索引规划目标

  • 提高查询性能
  • 减少资源消耗
  • 优化存储空间
  • 确保索引的有效性

2.1.2 索引规划步骤

# 索引规划步骤
1. 分析业务需求
2. 分析SQL语句
3. 识别查询模式
4. 选择索引类型
5. 设计索引结构
6. 验证索引效果
7. 调整索引策略

2.2 索引设计原则

合理的索引设计是提高数据库性能的关键。

2.2.1 索引设计原则

  • 选择性原则：选择选择性高的列创建索引
  • 最左前缀原则：复合索引的查询条件要使用最左前缀
  • 覆盖索引原则：使用覆盖索引减少回表操作
  • 避免过多索引：避免创建过多索引，影响写操作性能
  • 定期维护原则：定期维护索引，确保索引的有效性

2.2.2 适合创建索引的场景

  • 经常用于WHERE子句的列

    • 学习交流加群风哥QQ113257174

  • 经常用于JOIN条件的列
  • 经常用于ORDER BY和GROUP BY的列
  • 选择性高的列

2.2.3 不适合创建索引的场景

  • 选择性低的列（如性别、状态等）
  • 经常更新的列
  • 数据量小的表
  • TEXT、BLOB等大字段

2.3 索引维护计划

定期的索引维护可以确保索引的有效性和性能。

2.3.1 索引维护内容

  • 重建索引
  • 收集统计信息
  • 监控索引使用情况
  • 删除 unused 索引

2.3.2 索引维护频率

  • 重建索引：根据索引碎片情况，定期进行
  • 收集统计信息：每周或每月进行一次
  • 监控索引使用情况：定期进行，如每周
  • 删除 unused 索引：根据监控结果，定期进行

2.4 索引性能目标

明确的索引性能目标可以指导索引优化工作。

2.4.1 查询性能目标

  • 简单查询：响应时间小于0.1秒
  • 复杂查询：响应时间小于1秒
  • 报表查询：响应时间小于30秒

2.4.2 索引使用率目标

  • 索引使用率：大于80%
  • 索引碎片率：小于10%
  • 索引大小：不超过表大小的50%

    • 更多视频教程www.fgedu.net.cn

Part03-生产环境项目实施方案

3.1 索引创建

详细介绍DM数据库的索引创建方法。

3.1.1 创建普通索引

# 创建普通索引
SQL> create index idx_fgedu_test_id on fgedu_test(id);
# 输出信息
操作已执行

3.1.2 创建唯一索引

# 创建唯一索引
SQL> create unique index idx_fgedu_test_name on fgedu_test(name);
# 输出信息
操作已执行

3.1.3 创建复合索引

# 创建复合索引
SQL> create index idx_fgedu_test_id_name on fgedu_test(id, name);
# 输出信息
操作已执行

3.1.4 创建函数索引

# 创建函数索引
SQL> create index idx_fgedu_test_upper_name on fgedu_test(upper(name));
# 输出信息
操作已执行

3.2 索引维护

详细介绍DM数据库的索引维护方法。

3.2.1 重建索引

# 重建索引
SQL> alter index idx_fgedu_test_id rebuild;
# 输出信息
操作已执行 更多学习教程公众号风哥教程itpux_com
# 重建所有索引
SQL> alter index all on fgedu_test rebuild;
# 输出信息
操作已执行

3.2.2 收集统计信息

# 收集统计信息
# 收集表统计信息
SQL> analyze table fgedu_test compute statistics;
# 输出信息
操作已执行
# 收集索引统计信息
SQL> analyze index idx_fgedu_test_id compute statistics;
# 输出信息
操作已执行

3.2.3 删除索引

# 删除索引
SQL> drop index idx_fgedu_test_id;
# 输出信息
操作已执行

3.3 索引监控

详细介绍DM数据库的索引监控方法。

3.3.1 查看索引信息

# 查看索引信息
# 查看表的索引 from DB视频:www.itpux.com
SQL> select index_name, index_type, status from dba_indexes where table_name = ‘FGEDU_TEST’;
INDEX_NAME INDEX_TYPE STATUS
——————– ——————– ——–
IDX_FGEDU_TEST_ID NORMAL VALID
IDX_FGEDU_TEST_NAME UNIQUE VALID
# 查看索引列
SQL> select index_name, column_name, column_position from dba_ind_columns where table_name = ‘FGEDU_TEST’;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
——————– ——————– —————
IDX_FGEDU_TEST_ID ID 1
IDX_FGEDU_TEST_NAME NAME 1

3.3.2 监控索引使用情况

# 监控索引使用情况
# 查看索引使用统计信息
SQL> select * from v$index_usage where index_name like ‘IDX_FGEDU%’;
INDEX_NAME TABLE_NAME USED_COUNT
——————– ——————– ———-
IDX_FGEDU_TEST_ID FGEDU_TEST 100
IDX_FGEDU_TEST_NAME FGEDU_TEST 50
# 查看索引扫描情况
SQL> select * from v$sysstat where name like ‘%index%’;
NAME VALUE
————————————— ———–
Index scans 150
Index range scans 100
Index unique scans 50

3.4 索引优化

详细介绍DM数据库的索引优化方法。

3.4.1 优化索引结构

# 优化索引结构
# 分析索引碎片
SQL> select index_name, blevel, leaf_blocks, distinct_keys from dba_indexes where table_name = ‘FGEDU_TEST’;
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS
——————– ———- ———– ————-
IDX_FGEDU_TEST_ID 1 10 1000
IDX_FGEDU_TEST_NAME 1 20 500
# 重建碎片化的索引
SQL> alter index idx_fgedu_test_name rebuild;
# 输出信息
操作已执行

3.4.2 优化索引使用

# 优化索引使用
# 查看执行计划
SQL> explain select * from fgedu_test where id = 1;
PLAN
——————————
1 #NSET2: [0, 1, 132]
2 #PRJT2: [0, 1, 132]; exp_num(4), is_atom(FALSE)
3 #SLCT2: [0, 1, 132]; (FGEDU_TEST.ID = 1)
4 #SSEK2: [0, 1, 132]; scan_type(EQ), index33555445, table:FGEDU_TEST
# 优化SQL语句，使用索引
SQL> select id, name from fgedu_test where id = 1; — 使用覆盖索引

Part04-生产案例与实战讲解

4.1 索引创建实战演示

通过实际操作演示DM数据库的索引创建过程。

4.1.1 创建测试表和数据

# 创建测试表和数据
[dmdba@fgedu ~]$ disql SYSDBA/SYSDBA
SQL> create table fgedu_test (id int, name varchar(100), age int, address varchar(200));
# 插入测试数据
SQL> insert into fgedu_test values (1, ‘fgedu1’, 20, ‘address1’);
SQL> insert into fgedu_test values (2, ‘fgedu2’, 21, ‘address2’);
SQL> insert into fgedu_test values (3, ‘test3’, 22, ‘address3’);
SQL> insert into fgedu_test values (4, ‘test4’, 23, ‘address4’);
SQL> insert into fgedu_test values (5, ‘test5’, 24, ‘address5’);
SQL> commit;

4.1.2 创建普通索引

# 创建普通索引
SQL> create index idx_fgedu_test_id on fgedu_test(id);
# 输出信息
操作已执行
# 查看索引信息
SQL> select index_name, index_type, status from dba_indexes where table_name = ‘FGEDU_TEST’;
INDEX_NAME INDEX_TYPE STATUS
——————– ——————– ——–
IDX_FGEDU_TEST_ID NORMAL VALID

4.1.3 创建复合索引

# 创建复合索引
SQL> create index idx_fgedu_test_id_name on fgedu_test(id, name);
# 输出信息
操作已执行
# 查看索引信息
SQL> select index_name, index_type, status from dba_indexes where table_name = ‘FGEDU_TEST’;
INDEX_NAME INDEX_TYPE STATUS
——————– ——————– ——–
IDX_FGEDU_TEST_ID NORMAL VALID
IDX_FGEDU_TEST_ID_NAME NORMAL VALID
# 查看索引列
SQL> select index_name, column_name, column_position from dba_ind_columns where table_name = ‘FGEDU_TEST’;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
——————– ——————– —————
IDX_FGEDU_TEST_ID ID 1
IDX_FGEDU_TEST_ID_NAME ID 1
IDX_FGEDU_TEST_ID_NAME NAME 2

4.2 索引维护实战演示

演示DM数据库的索引维护操作。

4.2.1 重建索引

# 重建索引
[dmdba@fgedu ~]$ disql SYSDBA/SYSDBA
# 重建单个索引
SQL> alter index idx_fgedu_test_id rebuild;
# 输出信息
操作已执行
# 重建所有索引
SQL> alter index all on fgedu_test rebuild;
# 输出信息
操作已执行

4.2.2 收集统计信息

# 收集统计信息
# 收集表统计信息
SQL> analyze table fgedu_test compute statistics;
# 输出信息
操作已执行
# 收集索引统计信息
SQL> analyze index idx_fgedu_test_id compute statistics;
# 输出信息
操作已执行
# 查看统计信息
SQL> select table_name, num_rows, blocks from dba_tables where table_name = ‘FGEDU_TEST’;
TABLE_NAME NUM_ROWS BLOCKS
——————– ———- ———-
FGEDU_TEST 5 1

4.2.3 删除 unused 索引

# 删除 unused 索引
# 查看索引使用情况
SQL> select index_name, used_count from v$index_usage where table_name = ‘FGEDU_TEST’;
INDEX_NAME USED_COUNT
——————– ———-
IDX_FGEDU_TEST_ID 10
IDX_FGEDU_TEST_ID_NAME 0
# 删除 unused 索引
SQL> drop index idx_fgedu_test_id_name;
# 输出信息
操作已执行

4.3 索引优化实战演示

演示DM数据库的索引优化操作。

4.3.1 分析索引使用情况

# 分析索引使用情况
[dmdba@fgedu ~]$ disql SYSDBA/SYSDBA
# 查看执行计划（优化前）
SQL> explain select * from fgedu_test where name = ‘test3’;
PLAN
——————————
1 #NSET2: [0, 1, 132]
2 #PRJT2: [0, 1, 132]; exp_num(4), is_atom(FALSE)
3 #SLCT2: [0, 1, 132]; (FGEDU_TEST.NAME = ‘test3’)
4 #CSCN2: [0, 5, 132]; index33555444, table:FGEDU_TEST
# 创建索引
SQL> create index idx_fgedu_test_name on fgedu_test(name);
# 查看执行计划（优化后）
SQL> explain select * from fgedu_test where name = ‘test3’;
PLAN
——————————
1 #NSET2: [0, 1, 132]
2 #PRJT2: [0, 1, 132]; exp_num(4), is_atom(FALSE)
3 #SLCT2: [0, 1, 132]; (FGEDU_TEST.NAME = ‘test3’)
4 #SSEK2: [0, 1, 132]; scan_type(EQ), index33555446, table:FGEDU_TEST

4.3.2 优化复合索引

# 优化复合索引
# 创建复合索引
SQL> create index idx_fgedu_test_id_name on fgedu_test(id, name);
# 查看执行计划（使用复合索引的第一列）
SQL> explain select * from fgedu_test where id = 3;
PLAN
——————————
1 #NSET2: [0, 1, 132]
2 #PRJT2: [0, 1, 132]; exp_num(4), is_atom(FALSE)
3 #SLCT2: [0, 1, 132]; (FGEDU_TEST.ID = 3)
4 #SSEK2: [0, 1, 132]; scan_type(EQ), index33555445, table:FGEDU_TEST
# 查看执行计划（使用复合索引的两列）
SQL> explain select * from fgedu_test where id = 3 and name = ‘test3’;
PLAN
——————————
1 #NSET2: [0, 1, 132]
2 #PRJT2: [0, 1, 132]; exp_num(4), is_atom(FALSE)
3 #SLCT2: [0, 1, 132]; (FGEDU_TEST.ID = 3 AND FGEDU_TEST.NAME = ‘test3’)
4 #SSEK2: [0, 1, 132]; scan_type(EQ), index33555447, table:FGEDU_TEST

Part05-风哥经验总结与分享

5.1 索引优化技巧与注意事项

基于实际经验，分享一些索引优化的技巧和注意事项。

5.1.1 索引优化技巧

  • 为经常查询的列创建索引
  • 为WHERE子句中的列创建索引
  • 为JOIN条件中的列创建索引
  • 为排序和分组的列创建索引
  • 使用复合索引优化多列查询
  • 使用覆盖索引减少回表操作
  • 定期重建索引，减少碎片
  • 定期收集统计信息

5.1.2 索引优化注意事项

  • 避免创建过多索引
  • 避免在经常更新的列上创建索引
  • 避免在选择性低的列上创建索引
  • 注意复合索引的顺序
  • 注意索引的存储空间
  • 定期监控索引使用情况
  • 删除 unused 索引
  • 在测试环境中验证索引效果

5.2 常见索引问题

基于实际经验，分享一些常见的索引问题及解决方案。

5.2.1 索引失效

问题：索引存在但不被使用
解决方案：

  • 检查查询条件是否使用了索引列
  • 检查索引是否创建正确
  • 收集统计信息
  • 重建索引
  • 优化SQL语句

5.2.2 索引碎片

问题：索引存在碎片，影响性能
解决方案：

  • 重建索引
  • 定期维护索引
  • 优化索引结构

5.2.3 索引过多

问题：表上创建了过多索引，影响写操作性能
解决方案：

  • 删除 unused 索引
  • 合并重复索引
  • 优化索引结构
  • 根据查询模式调整索引

5.2.4 索引选择性低

问题：索引选择性低，查询性能差
解决方案：

  • 删除选择性低的索引
  • 使用复合索引提高选择性
  • 优化查询条件

5.3 最佳实践建议

基于实际经验，提供DM数据库索引优化的最佳实践。

5.3.1 索引设计最佳实践

风哥提示：在设计索引时，要根据查询模式选择合适的索引类型，遵循索引设计原则，避免创建过多索引，定期维护索引，确保索引的有效性和性能。

5.3.2 索引维护最佳实践

  • 定期重建索引：根据索引碎片情况，定期重建索引
  • 收集统计信息：定期收集表和索引的统计信息
  • 监控索引使用情况：定期监控索引的使用情况
  • 删除 unused 索引：定期删除 unused 索引
  • 记录索引维护：记录索引维护的详细信息

5.3.3 索引监控最佳实践

  • 监控索引的使用情况
  • 监控索引的碎片情况
  • 监控索引的大小
  • 监控索引的性能影响
  • 建立索引监控机制

5.3.4 索引优化最佳实践

  • 根据查询模式优化索引结构
  • 使用复合索引优化多列查询
  • 使用覆盖索引减少回表操作
  • 优化SQL语句，充分利用索引
  • 定期评估索引效果，调整索引策略

5.3.5 持续优化最佳实践

  • 建立索引优化机制
  • 定期进行索引优化
  • 学习索引优化技术
  • 分享索引优化经验
  • 持续改进索引性能

本文档风哥教程参考DM官方文档《DM8性能优化手册》、《DM8SQL参考手册》等资料编写，。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息