一、SQL优化的重要性
作为一名DBA,SQL优化是我们工作中必不可少的技能,在投产的系统中,存在大量的SQL语句需要我们分析和快速做出处理。很是考验我们的经验。这些慢SQL的原因有很多,有SQL编码不规范,设计有缺陷,SQL场景未考虑全面,数据量未评估等,预先需要建立索引的表而未设计建立,需分表分区的未进行拆解,也有环境因素造成的缺陷。也有出厂测试之前没有问题,但实际部署到客户现场时候却出了问题。这些异常语句,如果不及时处理,会造成对数据库的一个性能瓶颈,直接影响着目标用户的使用和体验。所以数据库SQL的性能优化,快速处理显得尤为重要。
文章目标,希望读者掌握达梦SQL优化的基础,SQL性能分析思路,参数配置标准工艺化理念,性能辅助工具熟练应用。文章最后7,8章也整合了第三方的常见分析工具,辅助达梦数据库性能压测时候的一种分析手段。文章中没有过多对单个SQL性能优化深入讲解,主要原因还是部分SQL涉及公司代码不便发布,其次也是个人能力有限,也是在不断学习中。
- 预估执行计划生成及基础说明
我们以达梦8数据库为例,拿到一条SQL的时候,首先要下达梦手册中提出的有效SQL规范,及是否命中了特殊OR子句的不规范,是否用了复杂的正则表达式,避免重复很高的索引,UINON ALL 是否可以替换UNION操作等,某些场景INSTR函数导致的NEST LOOP效率等,再就是看下它的预估执行计划。预估执行计划,我们可以采用达梦客户端点击如下图解小按钮生成,也可以按快捷键F9生成,也可以在SQL前加EXPLAIN生成预估计划;
2.1、生成预估执行计划
达梦客户端直接点击菜单栏按钮或按F9生成:
执行EXPLAIN SELECT * FROM TEST_TAB1 WHERE DXID=11487150 生成:
2.2、操作符讲解
执行计划操作符,大家起初可能并不熟悉,这个可以参考达梦官方手册:DM8 DBA.pdf ,在附录3有执行计划操作符说明。常见操作符,需要掌握,比如:聚集索引扫描(CSCN2),全表扫描(CSCN),索引扫描(SSEK),二次回表(BLKUP),除重(DISTINCT) ,二级索引定位(SSEK2)等等;多表关联,我们通常会遇到:(HASH链接)HASH JOIN,嵌套循环连接(NEST LOOP JOIN),
还有MERGE 链接(MERGE JOIN );
大表和大表关联主要HASH JOIN更优,而如果小表和大表关联,通常是NEST LOOP JOIN会更优,实际情况都是和数据量有关,优化器大部分能选择到合适的操作符,少数情况需要人为干涉。
2.4、执行计划顺序讲解
执行计划我们可以理解是一个树型状,看懂执行计划顺序,会让我们更好的分析SQL性能问题,通常执行计划查看,大家可以在达梦客户端选择表格展示或文本方式,看执行顺序表格会更直观,分析执行计划,文本更适合,但是SQL的执行顺序,并不是简单的从下而上,她的解析是要遵守规律,具体大家可以这样理解:
- 缩进越深的越先执行;
- 同样缩进的上面的先执行,下面的后执行;
3、上下的优先级高于内外;
如上说明还是不够直观,我再图解一个样例让大家理解:
说明:越缩进越先执行,即最终缩进分叉了3,4,同样的缩进,从上由下优先级更高,所以这里的3先执行,3下面又有越深的越先执执行,又有1,2 ,所以整个排序执行顺序就出来了,开始看不习惯,大家多看几次就习惯了。
三、达梦性能分析思路
达梦数据库的优化,我平常使用的最多的还是,达梦数据库优化“三把斧”,在实践中发现可以排除93%(百分比数值只是个人以往解决性能问题的估算百分比)以上的性能问题,我说的三把斧主要三个方面(统计信息收集,合理的索引建立,DM.ini参数的优化),在使用三把斧过程中,我们首先要预检查下SQL,以及SQL编写的初级错误问题,例如:OR语句,困难正则表达式,未加过滤条件,笛卡尔,隐式转换,简单先过一遍。
在实际过程中,大部分SQL优化可以通过收集统计信息,索引新增,索引调整,达梦DM.INI参数调整等得到优化处理,而进阶一些的比较难分析的,就涉及到了增加HINT,SQL改写,在处理过程中,都需要根据具体SQL来判断,常见的有关联临时表查询,层次查询等容易需要Hint辅助,文章中给出浅显的例子来说明,实际优化过程中,还是需要不断的去实践,根据不同的SQL,分析执行计划,这样才能积累更加丰富的经验。
3.1、统计信息收集基础讲解
达梦数据库是基于代价的优化器,达梦数据库统计信息不准,会影响到执行计划的估算,导致SQL解析到错误的执行计划,如何判断统计信息有没有收集呢?
我们看如下SQL,主要看NUM_ROWS字段,如果没有收集通常是空,第二个LAST_ANALYZED 代表收集的时间,如果没有收集这个字段也是空,第三个TEMPORARY字段附带说明,N表示不是临时表,Y表示是临时表,通常如果TEMPORARY=Y 是不用收集的,
这个大家查询的时候注意下,临时表无需收集统计信息;
用例讲解:
SELECT NUM_ROWS,LAST_ANALYZED,TEMPORARY
FROM USER_TABLES WHERE TABLE_NAME='TEST_TAB1';
我们需要对表进行查询,查询语句如下:
当一个表缺失了统计信息我通常用如下语句修复:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST_TAB1',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
END;
DBMS_STATS.GATHER_TABLE_STATS参数说明:
以上参数注释:
第一个参数:USER是所属用户,如果你要指定其它用户,单独填写,默认USER是当前用户下的表;
第二个参数:就是你所要收集的表;
第三个参数:一般不是分区表名填写NULL即可,默认也是 NULL,区分大小写;
第四个参数:采样百分比,收集的百分比,范围为 0.000001~100,默认系统自定;第五个参数:TRUE,保留参数,是否使用随机块代替随机行,默认我们填写 TRUE即可,第六个参数:控制列的统计信息集合和直方图的创建的格式默认我们填写FOR ALL COLUMNS SIZE AUTO即可,表示所有列收集,需要深入理解的可以参考达梦官方手册:DM8 - System Packages.pdf,平常我比较常用的还是DBMS_STATS.GATHER_TABLE_STATS;其次官方也提供了一些函数;
3.2、统计信息收集简单案例
什么时候需要收集统计信息?
通常执行计划估算数据量不准的时候就需要收集了,相差不大一般没有影响,如果比较大就要收集了,否则会影响性能;这里用以下例子说明,只是对表未收集统计信息导致执行计划估算有差异的案例:
--步骤1:直接创建2个表,一般新创建的表统计信息是不会自动收集的(否则开启AUTO_STAT_OBJ全表监控的自动收集),我们查一下它的执行计划,这个时候结果集应该是估算有差异的;
CREATE TABLE TEST1208_1 AS
select * from dba_TAB_COLUMNS WHERE TABLE_NAME='ALL_ALL_TABLES';
CREATE TABLE TEST1208_2 AS
select * from dba_TAB_COLUMNS WHERE TABLE_NAME='ALL_ALL_TABLES';
--客户端按F9,查看预估执行计划:
步骤2:获取真实执行计划:
DISQL登录当前用户
E:\dmdbms\bin>DISQL SYSDBA/SYSDBA_PWD@127.0.0.1
然后执行如下命令:
ALTER SESSION SET 'MONITOR_SQL_EXEC'=1;
set autotrace trace;
SELECT A.COLUMN_NAME FROM TEST1208_1 A ,TEST1208_2 B WHERE A.COLUMN_NAME=B.COLUMN_NAME;
最后展示服务端执行计划:
从服务器上的执行计划这里可以看出,实际56,估算174;
步骤3:达梦客户端,我们对这连个表统计信息进行收集;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST1208_1',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST1208_2',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
END;
/
步骤4:客户端F9,我们再来看预估执行计划:
收集统计信息后,预估执行计划是已经正确了;
步骤5:我们重复步骤2看真实SQL计划,那继续再看收集统计信息后执行计划情况:
发现并没有变化,这是为什么呢?
我们思考一下就会想到,因为之前在未收集统计信息的时候,我们执行了一遍这个SQL,所以SQL缓存了计划,那遇到这样的情况,我们应该对这个SQL的缓存计划进行清理;
步骤6,清理方法如下:文章来源:https://www.toymoban.com/news/detail-460783.html
数值1034139736是通过SELECT * FROM v$cachepln where SQLSTR LIKE 文章来源地址https://www.toymoban.com/news/detail-460783.html
到了这里,关于达梦8数据库性能优化思路浅谈的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!