达梦数据库执行计划深度实验从数据生成到索引优化全流程实验准备与环境搭建达梦数据库作为国产数据库的代表其执行计划机制与Oracle高度相似但又有自己的特色。要真正理解执行计划的奥秘最好的方式就是动手实验。我们先从零开始搭建实验环境安装达梦数据库管理工具推荐使用DM管理工具这是达梦官方提供的图形化管理界面功能全面且稳定准备SQLark工具这个轻量级工具可以快速生成测试数据支持达梦、Oracle等多种数据库创建测试数据库建议专门新建一个测试库避免影响生产环境-- 创建测试用户 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万条测试数据连接到达梦数据库选择数据生成功能设置生成规则USER_ID自增整数范围1-20000USER_NAME随机中文姓名USER_AGE18-60随机整数CITY从预设城市列表中随机选择执行数据生成约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. 查看执行计划的三种方式达梦数据库提供了多种查看执行计划的方法方法命令/操作适用场景特点图形化DM管理工具中按F9交互分析直观可视化EXPLAINEXPLAIN SELECT...脚本分析可保存结果自动跟踪SET AUTOTRACE ON实时监控同时显示实际执行情况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]性能对比分组类型操作符预估时间(ms)适合场景哈希分组HAGR15无索引字段流分组SAGR5有索引字段执行计划优化实战技巧1. 索引选择策略通过实验我们总结出索引选择的最佳实践高频查询字段优先建索引区分度高的字段更适合建索引避免过多索引影响写入性能组合索引要考虑字段顺序-- 好的组合索引示例 CREATE INDEX IDX_USER_COMP ON T_USER(CITY, USER_AGE);2. 执行计划解读要点分析执行计划时需要关注操作符类型识别全表扫描(CSCN)等性能瓶颈预估行数对比实际返回行数判断统计信息准确性执行顺序从最内层开始阅读理解执行流程代价估算重点关注时间预估识别性能热点3. 常见性能问题解决方案问题现象可能原因解决方案全表扫描缺少合适索引添加针对性索引回表开销大索引覆盖不足使用覆盖索引分组效率低使用HAGR为分组字段建索引统计信息不准数据变化未更新重新收集统计信息达梦特有优化技巧除了通用优化方法达梦还有一些特有的优化手段HINT使用通过注释指导优化器SELECT /* INDEX(T_USER IDX_USER_CITY) */ * FROM T_USER WHERE CITY 北京;并行查询利用多核CPU加速ALTER SESSION ENABLE PARALLEL DML; SELECT * FROM T_USER WHERE USER_AGE 30;内存调整优化排序和哈希操作-- 增大排序内存 ALTER SYSTEM SET SORT_MEM_SIZE 256M;分区表策略对大表使用分区提高查询效率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) );在实际项目中我们通常会结合多种优化手段。例如一个千万级用户表的查询优化通过添加合适的组合索引、更新统计信息、使用分区技术可以将查询时间从秒级降到毫秒级。