保姆级教程：用SQLark给达梦测试表造2万条数据，并实战分析不同索引下的执行计划变化

达梦数据库执行计划深度实验：从数据生成到索引优化全流程

实验准备与环境搭建

达梦数据库作为国产数据库的代表，其执行计划机制与Oracle高度相似，但又有自己的特色。要真正理解执行计划的奥秘，最好的方式就是动手实验。我们先从零开始搭建实验环境：

1. 安装达梦数据库管理工具：推荐使用DM管理工具，这是达梦官方提供的图形化管理界面，功能全面且稳定
2. 准备SQLark工具：这个轻量级工具可以快速生成测试数据，支持达梦、Oracle等多种数据库
3. 创建测试数据库：建议专门新建一个测试库，避免影响生产环境

-- 创建测试用户 CREATE USER TESTER IDENTIFIED BY "Dm123456"; GRANT DBA TO TESTER;

提示：实验环境最好与生产环境隔离，避免误操作影响重要数据。测试用户权限设置为DBA可以简化实验过程。

测试表设计与数据生成

1. 创建基础测试表

我们设计一个典型的用户信息表，包含ID、姓名、年龄和城市字段：

-- 删除已存在的表（如果存在） DROP TABLE IF EXISTS T_USER; -- 创建用户表 CREATE TABLE T_USER ( USER_ID INT, USER_NAME VARCHAR(50), USER_AGE INT, CITY VARCHAR(50) );

2. 使用SQLark生成测试数据

SQLark的数据生成功能非常强大，我们可以用它快速创建2万条测试数据：

1. 连接到达梦数据库
2. 选择"数据生成"功能
3. 设置生成规则：
   • USER_ID：自增整数，范围1-20000
   • USER_NAME：随机中文姓名
   • USER_AGE：18-60随机整数
   • CITY：从预设城市列表中随机选择
4. 执行数据生成，约10秒即可完成

-- 验证数据量 SELECT COUNT(*) FROM T_USER;

3. 收集统计信息

生成数据后，必须收集统计信息，否则执行计划可能不准确：

-- 收集表统计信息 SP_TAB_STAT_INIT('TESTER','T_USER'); -- 验证统计信息 SELECT TABLE_NAME, NUM_ROWS FROM ALL_TABLES WHERE OWNER = 'TESTER' AND TABLE_NAME = 'T_USER';

执行计划基础分析

1. 查看执行计划的三种方式

达梦数据库提供了多种查看执行计划的方法：

2. 基础操作符解析

我们先看一个最简单的全表查询的执行计划：

EXPLAIN SELECT * FROM T_USER;

典型输出会包含以下操作符：

1. #NSET2: [0, 20000, 156] 2. #PRJT2: [0, 20000, 156] 3. #CSCN2: [0, 20000, 156]

关键操作符解析：

• CSCN：聚集索引全表扫描，性能最低的操作
• PRJT：投影操作，对应SELECT子句中的列选择
• NSET：结果集收集，通常是执行计划的根节点

索引实验与执行计划变化

1. 无索引查询分析

先观察没有索引时的查询情况：

-- 按城市查询 EXPLAIN SELECT * FROM T_USER WHERE CITY = '北京';

执行计划显示：

1. #NSET2: [5, 400, 156] 2. #PRJT2: [5, 400, 156] 3. #SLCT2: [5, 400, 156] 4. #CSCN2: [5, 20000, 156]

新增了SLCT操作符，表示过滤条件。由于没有索引，仍然需要全表扫描。

2. 添加二级索引后的变化

现在为CITY字段创建索引：

CREATE INDEX IDX_USER_CITY ON T_USER(CITY);

再次执行相同查询，执行计划变为：

1. #NSET2: [1, 400, 156] 2. #PRJT2: [1, 400, 156] 3. #BLKUP2: [1, 400, 156] 4. #SSEK2: [1, 400, 156]

关键变化：

• SSEK：二级索引查找，替代了全表扫描
• BLKUP：回表操作，通过索引找到记录位置后获取完整数据

3. 聚集索引实验

删除原有索引，创建聚集索引：

DROP INDEX IDX_USER_CITY; CREATE CLUSTER INDEX IDX_CL_USER_ID ON T_USER(USER_ID);

执行USER_ID查询：

EXPLAIN SELECT * FROM T_USER WHERE USER_ID = 100;

执行计划：

1. #NSET2: [0, 1, 156] 2. #PRJT2: [0, 1, 156] 3. #CSEK2: [0, 1, 156]

CSEK操作符表示直接通过聚集索引获取数据，无需回表，效率最高。

高级查询场景分析

1. 聚合函数执行计划

观察COUNT聚合的执行计划：

EXPLAIN SELECT COUNT(*) FROM T_USER WHERE USER_ID > 1000;

输出：

1. #NSET2: [1, 1, 4] 2. #AAGR2: [1, 1, 4] 3. #PRJT2: [1, 19000, 4] 4. #CSEK2: [1, 19000, 4]

AAGR表示简单聚合操作，用于无GROUP BY的聚合查询。

2. 分组聚合对比

添加GROUP BY后的变化：

-- 无索引字段分组 EXPLAIN SELECT CITY, COUNT(*) FROM T_USER GROUP BY CITY;

执行计划出现HAGR（哈希分组）：

1. #NSET2: [15, 100, 60] 2. #HAGR2: [15, 100, 60] 3. #PRJT2: [15, 20000, 60] 4. #CSCN2: [15, 20000, 60]

为CITY创建索引后：

CREATE INDEX IDX_USER_CITY ON T_USER(CITY); EXPLAIN SELECT CITY, COUNT(*) FROM T_USER GROUP BY CITY;

执行计划变为SAGR（流分组）：

1. #NSET2: [5, 100, 60] 2. #SAGR2: [5, 100, 60] 3. #PRJT2: [5, 20000, 60] 4. #SSEK2: [5, 20000, 60]

性能对比：

执行计划优化实战技巧

1. 索引选择策略

通过实验我们总结出索引选择的最佳实践：

1. 高频查询字段优先建索引
2. 区分度高的字段更适合建索引
3. 避免过多索引，影响写入性能
4. 组合索引要考虑字段顺序

-- 好的组合索引示例 CREATE INDEX IDX_USER_COMP ON T_USER(CITY, USER_AGE);

2. 执行计划解读要点

分析执行计划时需要关注：

• 操作符类型：识别全表扫描(CSCN)等性能瓶颈
• 预估行数：对比实际返回行数，判断统计信息准确性
• 执行顺序：从最内层开始阅读，理解执行流程
• 代价估算：重点关注时间预估，识别性能热点

3. 常见性能问题解决方案

达梦特有优化技巧

除了通用优化方法，达梦还有一些特有的优化手段：

1. HINT使用：通过注释指导优化器

   SELECT /*+ INDEX(T_USER IDX_USER_CITY) */ * FROM T_USER WHERE CITY = '北京';

2. 并行查询：利用多核CPU加速

   ALTER SESSION ENABLE PARALLEL DML; SELECT * FROM T_USER WHERE USER_AGE > 30;

3. 内存调整：优化排序和哈希操作

   -- 增大排序内存 ALTER SYSTEM SET SORT_MEM_SIZE = '256M';

4. 分区表策略：对大表使用分区提高查询效率

   CREATE TABLE T_USER_PART ( USER_ID INT, USER_NAME VARCHAR(50), USER_AGE INT, CITY VARCHAR(50) ) PARTITION BY RANGE(USER_AGE) ( PARTITION P_YOUNG VALUES LESS THAN (30), PARTITION P_MIDDLE VALUES LESS THAN (50), PARTITION P_OLD VALUES LESS THAN (MAXVALUE) );

在实际项目中，我们通常会结合多种优化手段。例如一个千万级用户表的查询优化，通过添加合适的组合索引、更新统计信息、使用分区技术，可以将查询时间从秒级降到毫秒级。