sql性能优化精要分析

小灰灰 2022-04-14 03:40 235阅读 0赞

sql优化应该是任何一个开发，运维人员耳熟能详的问题，为什么有的人开发的系统测试时各种没问题，一到生产实际使用，数据量一大各种跑不动，崩盘挂死，很多时候和我们不合理的数据库设计以及sql有关，这里我将我这两年开发过程中总结的基于oracle相关sql调优经验总结一下，希望对大家有所启发。

1，对于数据量少的表，如果经常全表扫描，可以对全表缓存

alter table t1 cache;
    	alter table t1 buffer_pool keep;--放在keep池中。

2，当有大量相同语句是只是参数不同–建议使用动态变量

SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
    SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
    SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
    SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
    SET AUTOTRACE TRACEONLY EXPLAIN------ 同set autotrace on，但是不显示查询输出

3.关联语句中表的顺序（有兴趣的可以百度下oracle执行计划过程中表之间的关联关系，百度上一坨说明，写的比我好）  
在RBO优化器模式下，表应按结果记录数从大到小的顺序从左到右来排列，因为表间连接时，最右边的表会被放到嵌套循环的最外层。最外层的循环次数越少，效率越高。

3，执行计划

a）统计信息中逻辑读取平均每行小于5 时一般是不需要优化的也就是consistent gets/查询总行数<5
    b）执行计划中预测行 rows 和统计信息的 rows precessed 或者查询结果行数不一样的时候说明可以优化。
    c）predicate information 中 如果出现类型转换 to_number 等肯定需要优化 acess 表示使用索引

1）查看执行计划  
a）执行计划是从缩进最多的开始的，如果同时又多个缩进一样，按照从上往下进行（缩进一样是有可能并行处理）  
b）对于语句硬解析后，执行计划也会保存在缓存中。  
Statistics  
\---------------------------------------------------------------------------------------------------------------------------------------  
　　　　　0　recursive calls（归调用次数）  
　　　　　8　db block gets(从磁盘上读取的块数,即通过update/delete/select for update读的次数)  
　　　　　6　consistent gets(从内存里读取的块数,即通过不带for update的select 读的次数)  
　　　　　0　physical reads(物理读—从磁盘读到数据块数量,一般来说是’consistent gets’ + ‘db block gets’)  
　　　　　0　redo size (重做数——执行SQL的过程中，产生的重做日志的大小)  
　　　　551　bytes sent via SQL*Net to client  
　　　　430　bytes received via SQL*Net from client  
　　　　　2　SQL\*Net roundtrips to/from client  
　　　　　2　sorts (memory) (在内存中发生的排序)  
　　　　　0　sorts (disk) (在硬盘中发生的排序)  
　　　　　6　rows processed  
　　　　　  
1）Sort unique(sort的目的是取出所有的唯一值)（在distinct操作或者某个下一步需要unique值的操作）  
2）Sort aggregate(sort的目的是为了聚合)//聚合函数如count（）、sum（）等就是聚合操作(只是在计算整个表，所有行的总计时使用 不分组)  
select sum(a.p\_state\_id) from t\_policy\_state a ?  
3）Sort group by(sort的目的是为了分组)  
select a.state,sum(a.p\_state\_id) from t\_policy\_state a group by a.state;–这种合计使用的是group by 不是Sort aggregate  
4）Sort join(sort的目的是为了merge)  
5）Sort order by(sort的目的是为了有序输出)  
全表扫描需要扫描高水位线以下的所有块。  
2）表连连接方式  
a）sort --排序，很消耗资源（除非有特殊要求，尽量减少排序操作，可以先查出少量结果集后做排序）

order by clauses
    group by
    sort merge join

–-这三个会产生排序运算  
b）filter --过滤，如not in、min函数等容易产生  
c）索引  
索引范围扫描：like ‘%abc’ 不会走索引  
索引跳跃扫描：索引跳跃式扫描只有前导列 a 是低基列时才会引发跳跃式索引扫描，也就是第一列索引的不通知DISTINCT少的时候才会跳跃扫描（测试的数据是有37个不同值的时候就不会跳跃，低于37的才会跳跃）  
索引快速全扫描：比较依赖非空约束

比如： select deptno from t3 ；–deptno上有索引如果不是not null 约束，不走索引，因为怕有空数据，有 not null约束就会走索引  
所以一般索引建立原则是尽量建在有非空约束的字段上

4，数据库硬件解析  
1）共享池是缓存数据地方，SQL PLSQL 都会缓存在这里，即时其他用户执行相同的语句，也会从共享池中查看缓存，可以共享给所有用户。系统参数也在共享池中，共享池使用最近最少使用方法，只保留最频繁使用以及最近使用的。  
2）查看高速缓存里面的sql语句

select * from emp;
    	select * from EMP;
    	select sql_text from v$sql where upper(sql_text) like'%EMP%';    --会缓存两句，认为是不一样de
    	select * from emp where deptno=:no;      --使用动态变量会只解析一次（常用于存储过程中）

3）查询转换  
SELECT \* FROM EMP,(SELECT DEPTNO FROM DEPT) D WHERE EMP.DEPTNO=D.DEPTNO;  
会变查询转换 select \* from emp ,(select deptno from dept) d where e.deptno=d.deptno;  
强制不进行转换select \* from emp where deptno in (select/*+NO\_UNNEST*/ deptno from dept)  
–执行计划采取filter 不会使用nested loop 速度慢

是否使用视图合并
    SELECT * FROM EMP,(SELECT DEPTNO FROM DEPT) D WHERE EMP.DEPTNO=D.DEPTNO;
    --系统默认是走视图合并的 nested loops就是视图合并
    SELECT * FROM EMP,(SELECT /*+NO_MERGE*/DEPTNO FROM DEPT) D WHERE EMP.DEPTNO=D.DEPTNO;
    --强制不走视图合并 会慢很多 view 就是没有走视图合并

5，DML大数据时一些处理措施  
1）DML操作都是可以使用错误日志。  
a）执行创建错误表 `execute dbms_errlog.create_error_log('t1','t1_error');`  
b）插入式带上错误提示

insert into t1 values('a','b','c') log errors into t1_error reject limit unlimited;有错误不回滚，继续插入，
         insert into t12 select * from dba_objects log errors into t12_error reject limit unlimited;--即使有很多错误也会继续插入，错误保存在错误表中。
        rollback；--如果这时手动回滚，只会回滚基表数据，并不影响错误表，已经插入错误表的数据仍然存在，是自治事务。
        insert into t1 values('a','b','c') log errors into t1_error reject limit 2;两个错误就回滚，回滚本条数据；

2）update 大量数据时，不如重新创建新表（最重要的缺点是虽然速度上来了，但是必须让原表保持离线或者不被修改，要不然新创建的表是被修改后的表）。

SQL> update t2 set a=a+1;
     2200006 rows updated
     Executed in 449.455 seconds
     
     --重建速度会提高，但是必须保证对象重建期间不被修改
    
    
      SQL> create table t2_temp as select a+1 as a,b,c from t1 where 1=1;
         Table created
         Executed in 151.461 seconds--速度快很多。
         SQL> alter table t2 rename to t2_old;
        SQL> alter table t2_temp rename to t2;

或者使用直接路径插入  
create table t2\_temp  
insert /*+append*/ into t2\_temp select \* from emp;

3）delete 大量数据时和update一样  
a）创建一张临时表  
b）将不需要的记录放到临时表中  
c）重建相关对象（索引，约束，授权以及触发器）  
d）表重命名  
e）当然如果你足够自信，直接truncate或者drop重建表也是可以的

以上就是oracle数据优化一些经验，不过具体的DML/DQL操作速度快慢还取决于磁盘IO瓶颈，表结构设计是否合理，关联关系是否合理，针对于大批量数据业务表是否做了分区管理，以及db中是否有大量触发器，job等增加db负担，所以上述只是sql调优的相关指导，具体的实际效果还是需要具体情况具体分析的。

好了关于sql调优相关内容就介绍到这里了，大家有什么问题或者疑惑也欢迎给我留言！

写的不合理的地方望大家指正，谢谢