SQL优化二(SQL性能调优)

本文涉及的产品
全局流量管理 GTM,标准版 1个月
公共DNS(含HTTPDNS解析),每月1000万次HTTP解析
云解析 DNS,旗舰版 1个月
简介: 一·、前言:这篇博文内容非原创,是我们公司的架构师给我们做技术培训的时候讲的内容,我稍微整理了下,借花献佛。这篇博文只是做一个大概的科普介绍,毕竟SQL优化的知识太大了,几乎可以用一本书来介绍。另外,博主对SQL优化也是刚刚接触,也有很多不了解的地方,说的不对的地方,还请大家指正,共勉!   二、oracle服务器,所谓oracle服务器指的是一个数据库管理系统,它包括一个oracle实例(动态)和一个oracle数据库(静态)。
一·、前言:这篇博文内容非原创,是我们公司的架构师给我们做技术培训的时候讲的内容,我稍微整理了下,借花献佛。这篇博文只是做一个大概的科普介绍,毕竟SQL优化的知识太大了,几乎可以用一本书来介绍。另外,博主对SQL优化也是刚刚接触,也有很多不了解的地方,说的不对的地方,还请大家指正,共勉!
 
二、 oracle服务器,所谓oracle服务器指的是一个数据库管理系统,它包括一个oracle实例(动态)和一个oracle数据库(静态)。
oracle实例是一个运行的概念,提供了一种访问数据库的方式,由SGA和一些后台服务进程组成,DBWn PMON CKPT LGWR SMON是必备的后台进程,而ad queue,rac,shared server,ad replication则是可选的。连接到oracle实例有三种途径:
 1、如果用户登陆到运行oracle实例的操作系统上,则通过进程间通信进行访问
 2、C/S结构访问
 3、三层结构
oracle数据库是一个被统一处理的数据的集合,从物理角度来说包括三类文件,数据文件、控制文件、重做日志文件。
PMON监控其他后台进程,并且在服务器进程或者转发器进程异常终止之后执行恢复。pmon负责清理数据库的buffer cache,并且释放客户端进程使用的资源。比如说pmon重置当前活动的事务表,释放不需要的locks,清理进程id(隐式回滚)
 
SMON负责系统级别的清理工作
1.执行实例恢复。
2.恢复异常的transaction(实例恢复期间 file or tablespace被置为offline状态),smon会在他们置为online的时候执行恢复。
3.清理不使用的临时segments。比如当创建index的时候需要分配临时extent,如果操作失败,smon负责清理这些临时空间。
4.在使用字典管理表空间的时候合并连续的空闲extent。smon为定期监控。其他进程如果需要的话也会通知smon。
 
Database Writer Process (DBWn)负责将更改的buffer 从db buffer cache中写到datafile中去,通过一个dbwn进程(dbw0)就足够了,但是也可以配置更多额外的dbwr进程,它可以提升频繁更改的数据库系统的性能。当然额外的dbw进程对于单处理器系统是没有任何用处的。
 
Log Writer Process (LGWR)管理这redo log buffer。lgwr写buffer中连续的部分到online redo log 中。因为分离了更改数据库buffer的任务:dbwn散列写buffer到disk中,执行快速的顺序写到redo,所以数据库提升了性能。
1.用户提交了一个事务。
2. redo log switch 发生
3. 从上一次lgwr写操作开始已经过去了3秒
4. redo log buffer 三分之一满或者已经存储了1mb的数据量
5. dbwn必须写更改的数据到磁盘上面。
 
CKPT更新控制文件以及数据文件头部的检查点信息,并且给dbwn信号去写数据块到磁盘上面。检查点信息包括:检查点位置,scn,恢复时开始的redo log 位置,类似这样的信息。
 
Recoverer Process (RECO)在分布式数据库中,reco进程自动的解决分布式事务发生错误的情况。
 
三、分析语句阶段优化
 
硬解析:SQL语句从用户进程提交到oracle,经过分析装载到共享SQL区域(shared pool)。如果SQL语句不在shared pool,需要进行语句解析,即硬解析。
软解析:如果SQL语句在shared pool,就可以直接进入执行阶段。
优化技巧1:语法分析需要耗费很多资源,要尽量避免进行语法分析,即硬解析。
优化技巧2:即软解析时,当Shared pool没有空间时,oracle会根据LRU算法(最近最少使用页面置换算法)更新SQL区域,所以适当增加shared_pool,可以存放更多解析后的SQL来提高效率。
 
Oracle Optimizer(查询优化器):是Oracle在执行SQL之前分析语句的工具,Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行,主要有以下两种方式:
  • RBO(rule-base optimizer):优化器遵循Oracle内部预定的规则,句法驱动和数据字典驱动。
  • CBO(cost-based optimizer):依据语句执行的代价,主要指对CPU和内存的占用,优化器在判断是否使用CBO时,要参照表和索引的统计信息统计表驱动,统计信息要在对表做analyze后才会有。
优化技巧3:Oracle8及以后版本,推荐用CBO方式,Oracle10G此功能已经很强大。
 
Oracle优化器的优化模式主要有五种: 
  • Choose:默认模式。根据表或索引的统计信息,如果有统计信息,则使用CBO方式;如果没有统计信息,相应列有索引,则使用RBO方式。
  • Rule:基于规则优化,忽略任何统计信息
  • First rows:与Choose类似。不同的是如果表有统计信息,它将以最快的方式返回查询结果,以获得最佳响应时间。
  • First_rows_n:与Choose类似。不同的是如果表有统计信息,它将以最快的方式返回查询的前几行,以获得最佳响应时间。
  • All rows:完全基于CBO的模式。当一个表有统计信息时,以最快方式返回表所有行,以获得最大吞吐量。没有统计信息则使用RBO方式。
  Oracle Optimizer 运行级别怎么修改
  • Instance级:修改启动参数在init<SID>.ora文件中设定OPTIMIZER_MODE,需要数据库重启
  • Session级:(JDBC或者Hibernate或者一次连接),通过alter session set optimizer_mode = value修改,忽略instance级
  • Statement级:通过在SQL语句中加如Hint(隐语)实现,表明对语句块选择基于开销的优化方法,并获得最佳响应时间,忽略instance级和session级

               eg:SELECT /*+ALL_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';  ……来设定

DBMS_STATS包工具做CBO代价分析
DBMS_STATS:dbms_stats包下面一共有40多个存储过程. 对执行计划的生成非常重要。常见的有:
分析数据库(包括所有的用户对象和系统对象):gather_database_stats
分析用户所有的对象(包括表、索引、簇):gather_schema_stats
分析表:gather_table_stats
分析索引:gather_index_stats
删除数据库统计信息:delete_database_stats
删除用户方案统计信息:delete_schema_stats
删除表统计信息:delete_table_stats
删除索引统计信息:delete_index_stats
删除列统计信息:delete_column_stats
设置表统计信息:set_table_stats
设置索引统计信息:set_index_stats
设置列统计信息:set_column_stats
可以查看表 DBA_TABLES来查看表是否与被分析过,如:
SELECT TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES
通常使用的比较多的主要是DBMS_STATS.GATHER_TABLE_STATS和 DBMS_STATS.GATHER_INDEX_STATS。
使用步骤:
1、首先创建一个分析表,该表是用来保存之前的分析值。
SQL> begin
2 dbms_stats.create_stat_table(ownname => 'scott',stattab => 'STAT_TABLE');
3 end;
4 /
2、分析表信息。可以参考这篇 博客
exec dbms_stats.gather_schema_stats(
ownname          => 'SCOTT',
options          => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt       => 'for all columns size repeat',
degree           => 15
)
3、将执行计划导入到STAT_TABLE中
exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => 'stat_table') ; 
 
4、查看执行计划表
select * from  stat_table;
 
四、执行计划阶段优化
 
全表扫描(Full Table Scans)
Oracle读取表中所有的行,并检查每一行是否满足语句的WHERE限制条件,采用多块读的方式使一次I/O能读取多块数据块,而不是只读取一个数据块,这极大的减少了I/O总次数,提高了系统的吞吐量,所以利用多块读的方法可以十分高效地实现全表扫描,注意,只有全表扫描才能使用多块读的操作。
优化技巧4:通过设置db_block_multiblock_read_count和db_block_size来适当增加一次I/O可读的数据块。
优化技巧5:避免使用select * from 减少物理读,逻辑读(* 要走系统字典表,查看这张表有哪些字段),最好制定需要返回的字段。
优化技巧6:较小的表使用全表扫描,效率更高;较大的表应避免全表扫描,除非涉及全表记录10%以上的查询;避免给记录数少的表建立索引,避免索引开销。
优化技巧7:指定过滤谓词 where,尽可能缩小查询范围(能过滤掉大部分记录的字段应该放在右边,因为sql语句是从右至左执行的)。
 
通过ROWID的表存取(Table Access by ROWID)

ROWID记录了记录行所在的数据文件、数据块以及行在该块中的位置,所以通过ROWID来存取数据可以快速定位到目标数据上,可以说是整个数据库都在用的索引,是Oracle存取单行数据的最快方法。这种存取方法不会用到多块读操作,一次I/O只能读取一个数据块。

select empno from emp where rowid='AAAR3sAAEAAAACXAAA';

 

 
索引扫描(Index Scan)
通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值),然后根据rowid直接从表中得到具体的数据,这种查找方式称为索引扫描或索引查找(index lookup)。

索引唯一扫描(index unique scan)

通过唯一索引查找一个数值经常返回单个ROWID,如果存在UNIQUE 或PRIMARY KEY 约束(约束只有一行记录匹配),Oracle实现索引唯一性扫描。

select empno from emp where empno=7369;

 

索引范围扫描(index range scan)

使用一个索引存取多行数据,在唯一索引上使用索引范围扫描的典型情况下是在谓词(where限制条件)中使用了范围操作符,有以下三种情况会导致引起索引范围扫描:

  • 在唯一索引列上使用了range操作符(> < <> >= <= between)
  • 在组合索引上,只使用部分列进行查询,导致查询出多行
  • 对非唯一索引列上进行的任何查询。
select empno from emp where empno>7369;

 

索引全扫描(index full scan)

什么时候会引起索引全扫描呢?当不使用谓词逻辑where;所有查询结果数据都必须从索引中可以直接得到;需要排序操作,比如order by。

 select empno from emp order by empno;

index full scan使用单块读方式有序读取索引块,产生db file sequential reads事件,当采用该方式读取大量索引全扫描,效率低下

 

索引快速扫描(index fast full scan)

与索引全扫描很相似,只是不涉及排序动作。

select /*+ index_ffs(emp pk_emp) */empno from emp; //对指定的表执行快速全索引扫描,而不是全表扫描的办法.  

index fast full scan使用多块读的方式读取索引块,产生db file scattered reads 事件,读取时高效,但为无序读取

 

 

优化技巧7:对于只从表中查询出总行数的2%到4%行的表时,可以考虑创建索引。
优化技巧8:不要将那些频繁修改的列作为索引列,频繁修改会导致不必要的索引开销。
优化技巧9:不要使用包含函数或操作符放入WHERE从句中的关键字作为索引,会导致索引失效,可以考虑使用函数索引。
优化技巧10:在组合索引中,没有按照建立时的索引关键字顺序描述,比如xyz变成了yxz,也会导致索引失效。
优化技巧11:如果在表中要建立索引的一列或多列上使用了函数或表达式,则创建的是基于函数的索引。基于函数的索引预先计算函数或表达式的值,并将结果存储在索引中。B树索引和bitmap索引也是函数索引
优化技巧12:排序动作能不做就不做,增加系统开销的同时还会使快速索引失效。

 备注:

函数索引
CREATE INDEX idx ON table_1 (a + b * (c - 1), a, b);
则可以在查询语句中使用函数索引:
SELECT a
FROM table_1
WHERE a + b * (c - 1) < 100;

 
五、多表关联查询操作
 
任何N(N大于2)张表之间的操作都将转化为两张表之间的关联操作,查询中,由上一操作返回的符合条件的行的集合,即可以是表的全部行数据的集合,也可以是表的部分行数据的集合,或者说集合筛选后的集合都成为row source。无论连接操作符如何,典型的连接类型共有3种:

排序合并连接(Sort Merge Join (SMJ))

select aa.CREATEPERSONNAME, bb.CREATEPERSON
from tbl_comm_commonticket aa, tbl_ybgz_ticket bb
where aa.CREATEPERSONNAME = bb.CREATEPERSON
order by aa.CREATEPERSONNAME, bb.CREATEPERSON

排序属于代价很高的操作,特别对于大表。因此经常避免使用排序合并连接方法,但是如果2个row source都已经预先排序(比如primary Key索引),则这种连接方法可以选用。

 

嵌套循环(Nested Loops (NL))

分为驱动表(OUTER TABLE)和内层表(INNER TABLE)。因为嵌套循环,所以外层循环的次数越少越好,因此一般将数据量较小表或满足条件的row source较小的表作为驱动表(用于外层循环)的理论依据。

select /*+USE_NL(emp,dept)*/ * from emp,dept where emp.deptno=dept.deptno;

嵌套循环返回已经连接的行,而不必等待所有的连接操作处理完才返回数据 ,所以提高了响应速度。如果OUTER TABLE比较小,并且在INNER TABLE上有唯一索引,或有高选择性非唯一索引时,使用这种方法可以得到较好的效率。另外,这种连接方式,是在RBO优化器中。


哈希连接(Hash Join)

散列基本原理是:使用一个下标范围比较大的数组来存储元素。可以设计一个函数(哈希函数,也叫做散列函数),使得每个元素的关键字都与一个函数值(即数组下标,hash值)相对应,于是用这个数组单元来存储这个元素;但是,不能够保证每个元素的关键字与函数值是一一对应的,因此极有可能出现对于不同的元素,却计算出了相同的函数值,这样就产生了“冲突”,换句话说,就是把不同的元素分在了相同的“类”之中。 总的来说,“直接定址”与“解决冲突”是哈希表的两大特点。

散列连接是CBO 做大数据集连接时的常用方式,优化器使用两个表中较小的表(或数据源)利用连接键在内存中建立散列表,然后扫描较大的表并探测散列表,找出与散列表匹配的行。

hash join只有在CBO方式下可以使用;Oracle初始化参数HASH_JOIN_ENABLED决定是否启用hash join;pga_aggregate_target指定散列连接可用的内存大小;尽量使内层表生成的散列表最小,最好能够全部载入内存;主要用于等值连接。

select /*+USE_HASH(emp,dept)*/ * from emp,dept where emp.deptno=dept.deptno;

 

 
六、其他

优化技巧13:避免使用不确定操作符<>,!= 或者 where 子句中使用 or 来连接条件,因为会引起全表扫描; or 可以用union或者(union all) 代替。

select * from emp where empno>7369 union select * from emp where empno<7369;

优化技巧14:Where子句中出现IS NULL或者IS NOT NULL时,Oracle会停止使用索引而执行全表扫描。可以在设计表时,对索引列设置为NOT NULL。这样就可以用其他操作来取代判断NULL的操作。
优化技巧15:当通配符“%”或者“_”作为查询字符串的第一个字符时,索引不会被使用 。
优化技巧16:对数据类型不同的列进行比较时,会使索引失效。
优化技巧17:UNION操作符会对结果进行筛选,消除重复,数据量大的情况下可能会引起磁盘排序。如果不需要删除重复记录,应该使用UNION ALL。

SQL> select deptno from emp union select deptno from dept;
SQL> select deptno from emp union all select deptno from dept;

优化技巧18:Order By语句中的非索引列会降低性能,可以通过添加索引的方式处理。严格控制在Order By语句中使用表达式。
优化技巧19:相同的Sql语句,要保证查询字符完全相同,大小写,空格位置,利用shared_pool,防止相同的Sql语句被多次分析,使用变量绑定。
优化技巧20:调整SQL语句的目的是为了在执行中使资源的使用减少到最小。除了选择使用不同的SQL语法来优化执行代价,还可以通过调整执行顺序优化SQL。
优化技巧21:Oracle在执行IN子查询时,首先执行子查询,将查询结果放入临时表再执行主查询。而EXIST则是首先检查主查询,然后运行子查询直到找到第一个匹配项。因此NOT EXISTS比NOT IN效率稍高,相应更快。但是(NOT) EXISTS 不等于(NOT) IN。

SQL>select * from emp where deptno in (select deptno from dept);
SQL>select * from emp where exists (select deptno from dept where emp.deptno=dept.deptno);

优化技巧22:可以多使用视图进行软解析,视图只是把你要用的sql进行保存而已,你需要担心的是视图中的sql会不会效率太低,而不用担心视图的耗时。
优化技巧23:适当的时候强制使用rule会获得更高效率;调试SQL时关注执行计划和执行代价。
优化技巧24:避免视图嵌套使用,尤其是针对视图排序,筛选等操作。
优化技巧25:不同版本数据库的执行计划差别可能很大。
优化技巧26:不是只有select..是查询,所有的DML操作都含有查询过程。

  

七、SQL分析工具
  EXPLAIN PLAN
使用步骤:
1、SQL> explain plan for select * from emp,dept where emp.deptno=dept.deptno;
2、select * from table(dbms_xplan.display);
3、

 

  AUTOTRACE
使用步骤:
1、set autotrace on (可能会报Cannot SET AUTOTRACE的错误,参考 这篇博客解决)
2、select * from emp,dept where emp.deptno=dept.deptno;
3、

 

 
 
目录
相关文章
|
11天前
|
SQL 缓存 监控
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
本文详细解析了数据库、缓存、异步处理和Web性能优化四大策略,系统性能优化必知必备,大厂面试高频。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
|
19天前
|
SQL 存储 缓存
如何优化SQL查询性能?
【10月更文挑战第28天】如何优化SQL查询性能?
71 10
|
18天前
|
SQL 存储 缓存
SQL Server 数据太多如何优化
11种优化方案供你参考,优化 SQL Server 数据库性能得从多个方面着手,包括硬件配置、数据库结构、查询优化、索引管理、分区分表、并行处理等。通过合理的索引、查询优化、数据分区等技术,可以在数据量增大时保持较好的性能。同时,定期进行数据库维护和清理,保证数据库高效运行。
|
20天前
|
SQL 关系型数据库 MySQL
惊呆:where 1=1 可能严重影响性能,差了10多倍,快去排查你的 sql
老架构师尼恩在读者交流群中分享了关于MySQL中“where 1=1”条件的性能影响及其解决方案。该条件在动态SQL中常用,但可能在无真实条件时导致全表扫描,严重影响性能。尼恩建议通过其他条件或SQL子句命中索引,或使用MyBatis的`&lt;where&gt;`标签来避免性能问题。他还提供了详细的执行计划分析和优化建议,帮助大家在面试中展示深厚的技术功底,赢得面试官的青睐。更多内容可参考《尼恩Java面试宝典PDF》。
|
1月前
|
SQL 资源调度 分布式计算
如何让SQL跑快一点?(优化指南)
这篇文章主要探讨了如何在阿里云MaxCompute(原ODPS)平台上对SQL任务进行优化,特别是针对大数据处理和分析场景下的性能优化。
|
1月前
|
SQL 监控 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生显著的不利影响
|
1月前
|
SQL 关系型数据库 PostgreSQL
遇到SQL 子查询性能很差?其实可以这样优化
遇到SQL 子查询性能很差?其实可以这样优化
88 2
|
30天前
|
SQL 数据库 开发者
8种SQL编写陷阱:性能杀手还是团队乐趣?
【10月更文挑战第17天】记住,一个好的开发者不仅要知道如何编写代码,还要知道如何编写高效的代码。
35 0
|
1月前
|
SQL 存储 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生显著的不利影响
|
1月前
|
SQL 数据处理 数据库
警惕!这八个 SQL 习惯正在拖垮数据库性能
【10月更文挑战第3天】在日常的数据库开发与维护工作中,编写高效、清晰的SQL语句是每位数据工程师的必修课。然而,不当的SQL编写习惯不仅能降低查询效率,还可能给同事的工作带来不必要的困扰。今天,我们就来揭秘八种常见的“专坑同事”SQL写法,助你避免成为那个无意间拖慢整个团队步伐的人。
35 0