文章介绍了如何定位和优化慢SQL，强调了高频但执行慢的SQL是优化重点。通过开启跟踪日志记录，配置sqllog.ini文件来分析SQL执行情况。同时，文章提供了通过系统视图监控SQL，以及通过执行计划理解SQL执行过程的方法，包括常见操作符的解释。此外，还讨论了SQL语句的优化策略，如GROUPBY优化、UNION替换成UNIONALL以及EXISTS替换DISTINCT。
1、定位慢SQL
2、SQL分析方法
3、SQL语句优化
SQL优化
定位慢SQL
定位执行效率低的 SQL 语句是 SQL 优化的第一步。待优化的 SQL 可大致分为两类：
- SQL 执行时间在十几秒到数十秒之间，但执行频率不高，此类 SQL 对数据库整体性能影响并不大，可以放到最后进行优化。
- SQL 单独执行时间可能很快，在几百毫秒到几秒之间，但执行频率非常高，甚至达到每秒上百次，高并发下执行效率降低，很可能导致系统瘫痪，此类 SQL 是优化的首要对象
- 开启跟踪日志记录
跟踪日志文件是一个纯文本文件，以”dmsql_实例名_日期_时间命名.log”，默认生成在 DM 安装目录的 log 子目录下。跟踪日志内容包含系统各会话执行的 SQL 语句、参数信息、错误信息、执行时间等。跟踪日志主要用于分析错误和分析性能问题，基于跟踪日志可以对系统运行状态进行分析
跟踪日志记录配置
配置dm.ini文件，设置SVR_LOG=1以启用sqllog.ini配置，改参数为动态参数，可通过调用函数修改
SP_SET_PARA_VALUE(1,’SVR_LOG’,1);
配置数据文件目录下sqllog.ini文件
[SLOG_ALL]
FILE_PATH = ../log
PART_STOR = 0
SWITCH_MODE = 1
SWITCH_LIMIT = 100000
ASYNC_FLUSH = 0
FILE_NUM = 200
ITEMS = 0
SQL_TRACE_MASK = 2:3:23:24:25
MIN_EXEC_TIME = 0
USER_MODE = 0
USERS =
##注意：为避免记录SQL log对服务器产生较大影响，可以配置异步日志刷新（参数ASYNC_FLUSH设置为1）
配置完sqllog.ini后，可通过调用一下函数即时生效，无需重启数据库
SP_REFRESH_SVR_LOG_CONFIG();
Sqllog.ini配置参数说明：
| 参数名 | 缺省值 | 说明 |
| SQL_TRACE_MASK | 1 | LOG 记录的语句类型掩码，是一个格式化的字符串， |
| FILE_NUM | 0 | 总共记录多少个日志文件，当日志文件达到这个设定值以后， |
| SWITCH_MODE | 0 | 表示 SQL 日志文件切换的模式： |
| SWITCH_LIMIT | 100000 | 不同切换模式 SWITCH_MODE 下，意义不同： |
| ASYNC_FLUSH | 0 | 是否打开异步 SQL 日志功能。 |
| MIN_EXEC_TIME | 0 | 详细模式下，记录的最小语句执行时间，单位为毫秒。 |
| FILE_PATH | ../log | 日志文件所在的文件夹路径 |
| BUF_TOTAL_SIZE | 10240 | SQL 日志 BUFFER 占用空间的上限，单位为 KB，取值范围（1024-1024000） |
| BUF_SIZE | 1024 | 一块 SQL 日志 BUFFER 的空间大小，单位为 KB，取值范围（50-09600） |
| BUF_KEEP_CNT | 6 | 系统保留的 SQL 日志缓存的个数， 有效值范围（1-100） |
| PART_STOR | 0 | SQL 日志分区存储，表示 SQL 日志进行分区存储的划分条件。 |
| ITEMS | 0 | 配置 SQL 日志记录中的那些列要被记录。 |
| USER_MODE | 0 | SQL 日志按用户过滤时的过滤模式，取值 |
| USERS | 空串 | 打开 USER_MODE 时指定的用户列表。 |
查询方法
Sqllog.ini文件配置成功后可在dmsql指定目录下看到dmsql开头的log日志文件
通过系统视图查看
DM数据库提供系统动态视图，可自动记录执行时间超过设定阈值的SQL语句
SQL记录配置
当 INI 参数 ENABLE_MONITOR=1、MONITOR_TIME=1 打开时，显示系统最近 1000 条执行时间超过预定值的 SQL 语句，默认预定值为 1000 毫秒
##修改参数值
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(1,'MONITOR_TIME',1);
注意：这两个参数均为动态参数，可直接用系统函数修改，无需重启数据库
查询方式
查询当前正在执行的会话信息
SELECT * FROM (
SELECT 'SP_CLOSE_SESSION('||SESS_ID||');' AS CLOSE_SESSION,
DATEDIFF(SS,LAST_SEND_TIME,SYSDATE) sql_exectime,
TRX_ID,
CLNT_IP,
B.IO_WAIT_TIME AS IO_WAIT_TIME,
SF_GET_SESSION_SQL(SESS_ID) FULLSQL,
A.SQL_TEXT
FROM V$SESSIONS a,V$SQL_STAT B WHERE STATE IN ('ACTIVE','WAIT')
AND A.SESS_ID = B.SESSID
)
注意：SQL_TEXT列记录的是部分SQL语句；FULLSQL列存储了完整的执行SQL语句
查询超过执行时间阈值的SQL语句
Select * from v$LONG_EXEC_SQLS;
v$LONG_EXEC_SQLS视图字段详细信息介绍
| 列名 | 说明 |
| SESS_ID | 会话 ID，会话唯一标识 |
| SQL_ID | 语句 ID，语句唯一标识 |
| SQL_TEXT | SQL 文本 |
| EXEC_TIME | 执行时间（毫秒） |
| FINISH_TIME | 执行结束时间 |
| N_RUNS | 执行次数 |
| SEQNO | 编号 |
| TRX_ID | 事务号 |
SQL分析方法
执行计划
执行计划的每行即为一个计划节点，主要包含三部分信息
- 第一部分 NEST2、PRJT2、CSCN2 为操作符及数据库具体执行了什么操作。
- 第二部分的三元组为该计划节点的执行代价，具体含义为[代价，记录行数，字节数]。
- 第三部分为操作符的补充信息
例如：第三个计划节点表示操作符是CSCN2（全表扫描），代价估算是0ms，扫描的记录行数是1121行，输出字节数是397个。
各计划节点的执行顺序为：缩进越多的越先执行，同样缩进的上面的先执行，下面的后执行，上下的优先级高于内外。缩进最深的，最先执行；缩进深度相同的，先上后下。口诀：最右最上先执行
#CSCN2: [1, 2, 12]; INDEX33555496(TEST)
操作符，[代价，行数，字节数] 描述
查看执行计划
达梦数据库可通过两种方式查看执行计划
方式一：通过 DM 数据库配套管理工具查看。
方式二：使用 explain 命令查看
常见操作符解读
CSCN ： 基础全表扫描(a)，从头到尾，全部扫描
SSCN ： 二级索引扫描(b), 从头到尾，全部扫描
SSEK ： 二级索引范围扫描(b) ，通过键值精准定位到范围或者单值
CSEK ： 聚簇索引范围扫描(c) ， 通过键值精准定位到范围或者单值
BLKUP ： 根据二级索引的ROWID 回原表中取出全部数据(b + a)
SQL> explain select * from wzp a,wzp1 b where a.id=b.c1 and gz=232;
NEST：结果集收集
NEST是用于结果集收集的操作符，一般是查询计划的顶层节点，优化中无需过多关注
PRJT：投影
PRJT是关系【投影】（project）运算，用于表达式项的计算，广泛用于查询，排序，函数索引创建等，优化中无需过多关注，一般没有优化空间。
SLCT：选择
SLCT是关系的【选择】运算，用于查询条件的过滤。可比较返回结果集与代价估算是否接近，如相差较大可考虑收集统计信息。若该过滤条件较好，可考虑在条件列增加索引。
AAGR：简单聚集
AAGR 用于没有 GROUP BY 的 COUNT、SUM、AGE、MAX、MIN 等聚集函数的计算
SAGR：快速聚集
SAGR 用于没有过滤条件时，从表或索引快速获取 MAX、MIN、COUNT 值
HAGR：HASH分组聚集
HAGR 用于分组列没有索引只能走全表扫描的分组聚集，该示例中 gz 列没有创建索引
表wzp的gz列创建索引后的执行计划是这样的
如上图SAGR：流分组聚集，SAGR用于分组列是有序的情况下，gz列已创建索引，SAGR2性能优于HAGR2。
BLKUP：二次扫描（回表）
BLKUP 先使用二级索引索引定位 rowid，再根据表的主键、聚集索引、rowid 等信息获取数据行中其它列
CSCN：全表扫描
CSCN2 是 CLUSTER INDEX SCAN 的缩写即通过聚集索引扫描全表，全表扫描是最简单的查询，如果没有选择谓词，或者没有索引可以利用，则系统一般只能做全表扫描。全表扫描 I/O 开销较大，在一个高并发的系统中应尽量避免全表扫描
SSEK、CSEK、SSCN：索引扫描
SSEK是二级索引扫描即先扫描索引，再通过主键、聚集索引、rowid等信息去扫描
CSEK
Create index inx_wzp_gz_jj on wzp(gz,jj);
CSEK2是聚集索引扫描只需要扫描索引，不需要扫描表，即无需BLKUP操作，如果BLKUP开销较大时，可以考虑创建聚集索引。
SSCN
SSCN是索引全扫描，不需要扫描表
NEST LOOP：嵌套循环连接
嵌套循环连接是最基础的连接方式，将一张表（驱动表）的每一个值与另一张表（被驱动表）的所有值拼接，形成一个大结果集，再从大结果集中过滤出满足条件的行。驱动表的行数就是循环的次数，将在很大程度上影响执行效率
连接列是否有索引，都可以走 NEST LOOP，但没有索引，执行效率会很差，语句如下所示
下面针对wzp和wzp1的连接列创建索引，并收集统计信息
Create index inx_wzp_gz on wzp(gz);
Create index inx_wzp1_xh on wzp1(xh);
DBMS_STATS.GATHER_INDEX_STATS(USER,’INX_WZP_GZ’);
DBMS_STATS.GATHER_INDEX_STATS(USER,’INX_WZP1_XH’);
使用场景：
驱动表有很好的过滤条件
表连接条件能使用索引
结果集比较小
HASH JOIN：哈希连接
哈希连接是在没有索引或索引无法使用情况下大多数连接的处理方式。哈希连接使用关联列去重后结果集较小的表做成 HASH 表，另一张表的连接列在 HASH 后向 HASH 表进行匹配，这种情况下匹配速度极快，主要开销在于对连接表的全表扫描以及 HASH 运算
哈希连接比较消耗内存，如果系统由很多这种链接时，需调整以下3个参数
| 参数名 | 说明 |
| HJ_BUF_GLOBAL_SIZE | HASH 连接操作符的数据总缓存大小 ()>=HJ_BUF_SIZE)，系统级参数，以兆为单位。有效值范围（10~500000） |
| HJ_BUF_SIZE | 单个哈希连接操作符的数据总缓存大小，以兆为单位。有效值范围（2~100000） |
| HJ_BLK_SIZE | 哈希连接操作符每次分配缓存( BLK )大小，以兆为单位，必须小于 HJ_BUF_SIZE。有效值范围（1~50） |
MERGE JOIN：归并排序连接
归并排序连接需要两张表的连接列都有索引，对两张表扫描索引后按照索引顺序进行归并
create index inx_wzp_gz_jj on wzp(gz,jj);
SQL语句优化
1、优化group by
提高 GROUP BY 语句的效率，可以在 GROUP BY 之前过滤掉不需要的内容
--优化前
SELECT JOB,AVG(AGE) FROM TEMP
GROUP BY JOB HAVING JOB = 'STUDENT' OR JOB = 'MANAGER';
--优化后
SELECT JOB,AVG(AGE) FROM EMP
WHERE JOB = 'STUDENT' OR JOB = 'MANAGER' GROUP BY JOB;
- 用union all替换union
当 SQL 语句需要 UNION 两个查询结果集合时，这两个结果集合会以 UNION ALL 的方式被合并，在输出最终结果前进行排序。用 UNION ALL 替代 UNION, 这样排序就不是必要了，效率就会因此得到提高。
--优化前
SELECT USER_ID,BILL_ID FROM USER_TAB1 WHERE AGE = '20'
UNION
SELECT USER_ID,BILL_ID FROM USER_TAB2 WHERE AGE = '20';
--优化后
SELECT USER_ID,BILL_ID FROM USER_TAB1 WHERE AGE = '20'
UNION ALL
SELECT USER_ID,BILL_ID FROM USER_TAB2 WHERE AGE = '20';
- 用EXISTS替换DISTINCT
当 SQL 包含一对多表查询时，避免在 SELECT 子句中使用 DISTINCT，一般用 EXIST 替换 EXISTS 查询更为迅速
--优化前
SELECT DISTINCT USER_ID,BILL_ID FROM USER_TAB1 D,USER_TAB2 E
WHERE D.USER_ID= E.USER_ID;
--优化后
SELECT USER_ID,BILL_ID FROM USER_TAB1 D WHERE EXISTS(SELECT 1 FROM USER_TAB2 E WHERE E.USER_ID= D.USER_ID);
4、多使用commit
扫一扫
791
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
