2022年达梦数据库的性能优化 .pdf

《2022年达梦数据库的性能优化 .pdf》由会员分享，可在线阅读，更多相关《2022年达梦数据库的性能优化 .pdf（11页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、达梦数据库的性能优化“棱镜门”、“微软 XP系统停摆”的接踵而至给我国信息安全敲响了警钟，也加速了国内“去 IOE”运动的进程。达梦数据库作为连续5 年国产数据库市场占有率第一的高性能、高可靠性、高安全性、高兼容性大型关系型数据库管理系统，已成功替代了Oracle，在电力、金融、电子政务、教育等行业领域得到了广泛的应用，逐渐成为国家信息化建设的重要基础平台。为了更好地支撑业务应用，有效管理和利用信息时代不断产生并急剧膨胀的数据，对达梦数据库的优化显得尤为重要。一、数据库参数优化1.优化内存公共内存池公共内存池提供了一组内存申请/释放接口，为系统中需要动态分配内存的模块提供服务。SQLselec

2、t*from v$dm_ini where para_name like%MEM%POOL%;行号 PARA_NAME PARA_VALUE-1 MEMORY_POOL 40 2 MEMORY_BAK_POOL 4 3 N_MEM_POOLS 4 MEMORY_POOL决定了以M 为单位的公共内存池的大小，上例中40M；N_MEM_POOLS决定把内存池划分为几个独立的单元，以减少并发访问的冲突，提升并发效率；MEMORY_BAK_POOL表示系统保留的备用内存量，当常规的内存申请都失败时，从这个备用内存里分配，然后在上层模块中进行必要的容错处理。可以在 v$sysstat中查看当前公用内存池

3、的使用情况：SQLselect*from v$sysstat where name like%MEM%;行号 ID CLASSID NAME STAT_VAL-1 26 3 MEMORY USED BYTES 25831296 名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 11 页 -这里的 STAT_VAL 给出的是已经使用的字节数。正常情况下，应该小于配置的池大小，否则系统不得不从池外向操作系统申请/释放内存，造成效率低下，并可能把操作系统的内存搞得很零碎。系统缓冲区 BUFFER 为了加速数据访问，系统开辟了一个缓冲区，使用 LRU算法存放经常访问的数据页，逐步淘汰不用的

4、数据页。使用下列参数，可配置基本的系统缓冲区的大小：SQLselect*from v$dm_ini where para_name like%BUFFER%;行号 PARA_NAME PARA_VALUE-1 HUGE_BUFFER 8 2 BUFFER 4000 3 MAX_BUFFER 8000 4 BUFFER_POOLS 1 其中 HUGE_BUFFER 是专门用于列存表的缓存区，BUFFER 是用户行存表的系统缓冲区。BUFFER 表示初始的系统缓冲区大小，单位为M。通常情况下，如果物理数据量大于物理内存，则应该把BUFFER 调到物理内存的三分之二比较合适。当 BUFFER_POO

5、LS=1时，系统支持缓冲区的自动扩展。MAX_BUFFER表示最多能扩到多大。在自动扩展后，如果系统的压力在一段时间内比较低，系统又会自动收缩缓冲区。系统缓冲区是一个共享资源，受一个 mutex 保护，在一个时间点，只允许一个线程可以持有这个资源。在高并发情况下，这个限制将极大降低并发效率，因此，可以配置 BUFFER_POOLS把一个大的系统缓冲区分割为多个小的部分，每一个小的部分作为临界资源，这样只要所访问的数据页不在同一个子池里，就不会发生冲突，从而提升并发性能。注意，如果配置了BUFFER_POOLS 1,则MAX_BUFFER参数就失效了，最大可用的缓冲区由BUFFER 参数决定。系

6、统缓冲区 RECYCLE 这是 DM 新引入的缓冲区，专门用于缓冲临时表空间。RECYCLE 的淘汰算法与BUFFER 完全一样，但是它有独立的HASH 表，LRU和更新链。引入 RECYCLE 的目的是防止某些复杂查询的中间结果挤占大量的BUFFER 空间,降低 BUFFER 的命中率，从而增加额外的 IO 操作。名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 11 页 -使用 RECYCLE 的场景主要有：大表的散列连接，在内存达到HJ_BUF_SIZE 时使用排序，大数据量的排序操作，蓄水池操作符，如:NTTS,SPL,HTAB等，这些操作符需要把数据收集在一起临时表数据M

7、AL 系统中，堆积的邮件并行查询中，堆积的消息大字段的临时数据RECYCLE 的配置可以从 V$DM_INI查到，单位 M SQLselect*from v$dm_ini where para_name=RECYCLE;行号 PARA_NAME PARA_VALUE-1 RECYCLE 64 2.利用缓存DM的缓存机制，可以避免系统重复的SQL解析工作，比如对于非常耗时的SQL语句解析，极大提升系统性能。SQL缓存池的大小用CACHE_POOL_SIZE来设置，缺省为 10M。如果应用程序对SQL语句都是先准备，再绑定参数，然后反复执行，那么就不需要计划缓存了。在这样理想的模式下，每一种 SQ

8、L语句都使用不同的语句句柄，并在应用程序启动之后不久就进行了准备，执行时使用相应的语句句柄，并给定不同的参数。但是这个理想模式要求有良好的应用设计，有限或很少的SQL语句形式，限制太多。因此DM数据库系统提供了计划缓存机制。计划缓存由 USE_PLN_POOL参数控制，当 USE_PLN_POOL=0，禁止计划缓存；当 USE_PLN_POOL=1,SQL语句需要完全匹配，才能使用计划。比如:Selct*from t1 where id=1;Select*from t1 where id=2;虽然这两个语句很相似，计划也基本上一样，但是因为常量不同，不能重用计划。因此使用精确匹配，会造成大量类

9、似重复的计划。精确匹配一般应该使用名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 11 页 -在语句非常复杂，查询很耗时的分析型场景。这类场景语句中，常量取值的不同对计划的影响很大。当 USE_PLN_POOL=2 时，使用模糊匹配模式。系统首先试图做精确匹配，如果没有找到合适的计划，则需要做语法分析，把常量提取出来，把语句转换为参数的形式，再从计划缓存中查找合适的计划。如果找到，则提取该计划运行，否则就需要做关系变换和代价分析，并把新生成的计划放入缓存中。模糊匹配适用于大部分OLTP 应用，但是系统还是需要做一遍语法分析,需要把常量分解出来,并重新把语法树反拼成一个字符串,这个

10、过程还把多余空格,注释去掉,因此,下列两个语句都匹配成同一个计划:Select*from t1 where id=1;Select/*this is a test*/*from t1 where id =2;二、SQL优化1.定位慢的 SQL 要分析性能瓶颈，首先得把执行的又慢、又多的 SQL语句找出来，DM提供了SQL日志的功能，可以将系统中运行的SQL语句、语句绑定的参数、SQL执行时间记录到 SQL日志文件中，并提供参数来进行过滤，比如只更新select语句、DELETE 语句、update 语句、报错的语句等等。1)修改 dm.ini文件SVR_LOG =1 SVR_LOG_FILE_

11、NUM=2 SQL_TRACE_MASK=1 SVR_LOG_SWITCH_COUNT=1000000#SVR_LOG：1 表示开启 SQL日志功能，0 表示关闭#SVR_LOG_FILE_NUM：1 表示不切换，2 表示记录两个日志文件，互相切换#SQL_LOG_MASK：要记录的语句类型掩码，1表示全部记录 4:7表示记录 update 和 select语句#SVR_LOG_SWITCH_COUN:每个 SQL日志文件中记录的消息条数#修改完成后需要重启数据库服务2)执行系统存储过程开启-开启 SQL日志，记录所有的SQL语句，两个文件互相切换-每个文件记录 100 万行名师资料总结-精品

12、资料欢迎下载-名师精心整理-第 4 页，共 11 页 -动态修改 INI 参数，无需重启服务call sp_set_para_value(1,SVR_LOG,1);call sp_set_para_value(1,SVR_LOG_FILE_NUM,2);call sp_set_para_value(1,SQL_LOG_MASK,1);call sp_set_para_value(1,SVR_LOG_SWITCH_COUNT,1000000);通过上面两种方法，我们成功开启了SQL日志功能，那么如何从这些日志文件中找到性能瓶颈呢？给大家提供一个SQL 日志分析小工具（http:/ 文件中2.优化

13、慢的 SQL 既然我们已经把执行的又慢又多的SQL语句找到了,就马上开始 SQL优化之旅吧。名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 11 页 -SQL 优化首先我们得会看SQL 的执行计划，DM 中查看 SQL 执行计划非常简单，在 SQL的前面加 EXPLAIN关键字即可，如下：explain select*from sysobjects where subtype$=UTAB;1#NSET2:8,1465,3405 2#PRJT2:8,1465,3405;exp_num(17),is_atom(FALSE)3#SLCT2:8,1465,3405;SYSOBJECTS.

14、SUBTYPE$=UTAB 4#CSCN2:8,7328,3405;SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS)-从计划可以看出，这是一个对SYSOBJECTS 表的全表扫描，过滤条件是SYSOBJECTS.SUBTYPE$=UTAB-DM查 询 计 划 中 各 操 作 符 详 细 的 说 明 请 参 考 下 面 这 个 帖 子：http:/ DM 中如何找到 SQL 执行计划中各操作符的实际执行时间，如果知道了计划中各操作符的实际执行时间，那么我们就可以对慢的操作符进行针对性的优化。首先我们需要修改dm.ini，修改一个参数，记录SQL执行时各操作

15、符的实际执行时间，修改完成后需要重启数据库服务才能生效。ENABLE_MONITOR=3 然后我们创建一个存储过程，查询一些动态性能视图，获取我们需要的信息：createorreplaceproceduresql_et(eid int)isbeginselect name as OP,time_used/1000|msas TIME,cast(time_used*100.0/sum(time_used)over()as dec(10,2)|%as PERCENT,rank()over(orderbytime_useddesc)as RANK ,seq_noas SEQ fromv$sql_no

16、de_history a,v$sql_node_name b wherea.type$=b.type$and exec_id=eid orderbytime_useddesc;名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 11 页 -End;准备工作做完了，下面我们就构造一个简单的例子来实际操作一下。-创建表Createtabletx(id int,name varchar(100);Createtable ty(id int,name varchar(100);-Createindex txl01 on tx(id);-Createindex tyl01 on ty(id);

17、-创建存储过程Createorreplaceprocedureins_p(cnt int)asDeclareX int;BeginFor xin t loopInsertintotx values(x,hello);Insertinto ty values(x,world);Endloop;Commit;End;-每个表插入 100 万数据callins_p(1000000);-分析一下下面这个相关子查询select*from tx a whereexists(select*from tx b where a.id=b.id and a.name=b.name andb.ID=10)1.执行一

18、下这个SQL语句，记录下他的执行号3304：名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 11 页 -2.查看其执行计划：3.使用 SQL_ET存储过程查看该SQL各操作符的执行时间：名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 11 页 -从操作符的执行时间可以看出：时间主要消耗在对TX 表和 TY 表的全表扫描上了，我们有针对性的创建两个索引，将全表扫描转化为索引扫描即可。Createindex txl01 on tx(id);Createindex tyl01 on ty(id);-创建索引后，我们再来看看执行计划：-再来看这个 SQL各操作符的执行时间名

19、师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 11 页 -三、应用优化1.会话管理在 SQL SERVER 数据库中鼓励开发人员每执行一个SQL就创建一个会话，这样能够提高性能，但在DM 和 ORACLE 中这种应用的会话管理在性能上就是一个灾难，DM中一个会话足够能干，不需要为每一个SQL创建一个会话来执行，移植类似的 SQL SERVER 的应用需要注意。2.索引优化索引对数据库 I/O 的影响十分巨大。如果通过索引来访问数据库，可以大大减少对大型表的全表扫描，从而减少I/O 的开销。索引的使用通常能够提高SELECT，UPDATE 以及 DELETE 语句的性能，但会降低I

20、NSERT语句的性能，因此索引并非是越多越好，使用索引应该遵循以下原则:仅当要通过索引访问表中很少的一部分行（1%20%）如果要处理表中的多行，而且可以使用索引而不用表名师资料总结-精品资料欢迎下载-名师精心整理-第 10 页，共 11 页 -不能利用索引的场合：索引列上有函数（确定性的函数可以创建函数索引）索引列存在隐式类型转换索引列选择率差，不如全表扫描3.仅查询需要的列很多没有经验的程序员为了贪图一时的方便喜欢使用SELECT*来查询，这样实际上会带来很多额外的网络、IO 开销，严重的时候会引发灾难。给大家分享一个案例:某应用有条 SQL计划非常好，索引都利用上了，但是运行非常慢：SELECT*FROM MANALOG_DATA_SCADA WHERE,原因是：该表有 1441 列，这个业务只需要其中的3 个列，却写了 SELECT *带来不必要的网络和IO 的开销，导致性能低下。优化方法：修改 SQL语句，只查询需要的列：Select c1,c2,c3 from MANALOG_DATA_SCADA where,名师资料总结-精品资料欢迎下载-名师精心整理-第 11 页，共 11 页 -