多动手多测试（四）通过执行计划优化sql与sql编写建议

奶糖非糖

已于 2022-03-18 14:32:49 修改

阅读量637

点赞数 3

分类专栏：数据库学习文章标签： sql 数据库 database sql优化

于 2022-03-11 09:04:02 首次发布

本文链接：https://blog.csdn.net/bojianhao123/article/details/123416153

版权

数据库学习专栏收录该内容

8 篇文章 0 订阅

订阅专栏

1.前言

生产环境的一次真实事件，应用使用druid连接数据库，其中有一个交易，是使用定时任务处理某张临时表中的待处理数据，处理完后从表中删除。
刚开始处理速度很慢，因为生产者的生产速度远大于消费者的消费速度，这就导致待处理数据越积越多，早上9点入表数据，到晚上10点才处理完。为了优化这种情况，增加了定时任务的线程数量，刚开始由3线程并发增加到20线程并发，效果有所好转，但是生产速度依然大于消费速度。
于是又由20线程并发增加到50线程并发。但是这次效果甚微，并且数据库服务器cpu报警，长时间处于高负荷状态，durid连接池与数据库的active连接数也逐渐达到最大值，从而后面的业务再也无法获取到连接而报错。（数据库连接参数调优问题在上一篇）
无奈只好将并发数调小，但是处理慢又成了大问题。后面分析了详细日志发现其中有几个sql执行都超过了15秒，这才导致了业务执行慢，从而线程无法释放数据库连接，使连接数达到最大值而报错。优化sql后，问题解决

2.log4j放开日志级别

在这里插入图片描述

3.查看业务日志

在这里插入图片描述

很明显看出一条sql执行了15秒之久，而且这样sql执行不止一次，这就是导致session长时间active无法释放的根本原因

4.拿出sql语句查看执行计划

toad可视化工具里是Ctrl+E查看执行计划
在这里插入图片描述

可以看到2 - filter(“PKG_NO”=:1 AND “REF_NO”<>“PKG_NO” AND “REF_NO”<>:1)是全表扫，
以前的时候这种情况不明显，随着表里数据越来越多，现在已经达到2000W+条数据，执行效率低的问题才暴露出来。
这里面用到了两个字段，pkg_no和ref_no，查看表结构发现ref_no上有索引而pkg_no上没有，所以问题就出现在pkg_no字段上
在这里插入图片描述

5.查看字段是否适合建立索引

计算count(distinct column)/count(1)，越接近1越好，一般来说超过33%就算是高效索引
在这里插入图片描述

6.建立索引

在这里插入图片描述

7.再次查看执行计划

在这里插入图片描述

可以看到走了刚建的TRAN_TABLE_IDX7索引，并且Cost值也大大降低，执行时间在1秒以内，连接不释放问题也得以解决。

附言一：编写sql建议

一、效率类

1.连接查询表顺序

多表连接查询时将表的顺序按照数据从多到少顺序排列，数据最少的表作为基础表（驱动表）放在最右边
例：dept为数据最少的表
SELECT a.empno, a.ename, c.deptno, c.dname, a.log_action
FROM emp_log a, emp b, dept c

2. 指定where条件顺序

表连接子句放在最前，能过滤掉更多数据的条件放在最后。
默认情况下，oracle采用自下而上的顺序解析where子句，因此在处理多表查询时，表之间的连接必须写在其他的where条件之前，但是过滤数据记录的条件则必须写在where子句的尾部，以便在过滤了数据之后再进行连接处理，这样可以提升sql语句的性能。

3、避免使用 * 符号

Oracle在遇到符号时，会去查询数据字典表中获取所有的列信息，然后依次转换成所有的列名，这将耗费较长的执行时间，因此尽量避免使用符号获取所有的列信息

4. 使用decode函数

通过灵活的运用decode函数，可以得到很多意想不到的结果，比如在group by 或order by子句中使用decode函数，或者在decode块中嵌套另一个decode块

5. 使用where而非having

WHERE语句是在GROUP BY语句之前筛选出记录，而HAVING是在各种记录都筛选之后再进行过滤。也就是说HAVING子句是在从数据库中提取数据之后进行筛选的。因此在编写SQL语句时，尽量在筛选之前将数据使用WHERE子句进行过滤

6. 使用UNION而非OR

如果要进行OR运算的两个列都是索引列，可以考虑使用union来提升性能
如果坚持使用OR语句，①.需要记住尽量将返回记录最少的索引列写在最前面
②.对单个字段值进行OR计算的时候使用IN来代替

7. 使用exists而非 in

①对于内表和外表同样数量级来说exists和in的效率差别不是很大。但对于内表特别大的sql，我们用in的效率就很底下，替换成exists可以获取更好的查询性能
②同样的替换页发生在not in 和not exists之间，not in 子句将执行一个内部的排序和合并，实际上它对子查询中的表执行了一次全表扫描，因此效率低，在需要使用NOT IN的场合，因而总是考虑把它更改成外连接或NOT EXISTS。

8. 避免低效的PL/SQL流程控制语句

①应该总是将开销较低的判断语句放在前面。
②IF条件中应该将最常走的分支放在最前面

9. 避免隐式类型转换

当比较不同数据类型的数据时， ORACLE自动对列进行简单的类型转换。

二、书写规范类

SQL语句中出现的所有表名、表别名、字段名、序列等数据库对象都应小写。

SQL 语句中出现的系统保留字、内置函数名、SQL保留字、绑定变量等都应大写。

SQL语句中出现的变量参数应遵循各语言编码规范的要求。

SQL语句中的表别名应简短明了，宜反映表名的实际意义。

应遵循各语言的编码规范的要求。

单引号应与所属的 SQL子句位于同一行。

SQL语句内的算术运算符、逻辑运算符（AND、OR、NOT）、比较运算符（=、<=、>=、>、<、<>、BETWEEN AND）、IN、LIKE等运算符前后都应加一空格。

SQL语句中逗号后应加一空格。

不等于应统一使用符号“<>”。

拼装SQL语句时宜使用StringBulider，不宜使用String + String的方式。

应遵循各语言编码规范的代码注释要求。

对较为复杂的 SQL语句应注释，并说明算法和功能。

对重要的计算应说明其功能。

对常量及变量注释时，应注释被保存值的含义，宜包括合法取值的范围

附言二：执行sql

-- 查看表哪些字段上有索引

SELECT a.uniqueness, b.index_name, b.column_name
  FROM DBA_INDEXES a, DBA_IND_COLUMNS b
 WHERE     a.table_owner = b.table_owner
       AND a.INDEX_NAME = b.INDEX_NAME
       AND a.table_owner = 'TEST'
       AND a.table_name = 'TRAN_TABLE';

-- 计算命中率，判断是否为低效索引

SELECT COUNT (DISTINCT pkg_no) / COUNT (1) FROM PT_BEPS_TRAN_MSG;

-- 创建索引

CREATE INDEX TEST.TRAN_TABLE_IDX7
   ON TEST.TRAN_TABLE (PKG_NO)
   LOGGING
   TABLESPACE TEST_INDX
   PCTFREE 15
   INITRANS 20
   MAXTRANS 255
   STORAGE (INITIAL 64 K
            NEXT 1 M
            MAXSIZE UNLIMITED
            MINEXTENTS 1
            MAXEXTENTS UNLIMITED
            PCTINCREASE 0
            BUFFER_POOL DEFAULT
            FLASH_CACHE DEFAULT
            CELL_FLASH_CACHE DEFAULT)
   NOPARALLEL;