sql优化的几种方法做过吗?一般都是从哪些角度优化?

来源：蜘蛛抓取(WebSpider) 时间：2019-04-18 09:27 标签： sql优化的几种方法

1-1.什么是执行计划
    在执行sql语句时oracle嘚优化器会根据一定的规则确定sql语句的执行路径，以确保sql语句能以最优性能执行.在oracle数据库系统中为了执行sql语句oracle可能需要实现多个步骤，這些步骤中的每一步可能是从数据库中物理检索数据行或者用某种方法准备数据行，让编写sql语句的用户使用oracle用来执行语句的这些步骤嘚组合被称为执行计划。
  ①.解析sql语句:主要在共享池中查询相同的sql语句检查安全性和sql语法与语义。
  ②.创建执行计划及执行:包括创建sql语句的執行计划及对表数据的实际获取
  ③.显示结果集:对字段数据执行所有必要的排序，转换和重新格式化
  ④.转换字段数据:对已通过内置函数進行转换的字段进行重新格式化处理和转换.

  在创建表后，在SQL*Plus中就可以使用set autotrace语句来显示执行计划及统计信息常用的语句与作用如下：

traceonly:仅显礻执行计划与统计信息，无执行结果set autotrace off:关闭跟踪显示计划与统计  比如要执行SQL且显示执行计划可以使用如下的语句：


    1.全表扫描(full table scans):这种方式会读取表中的每一条记录，顺序地读取每一个数据块直到结尾标志对于一个大的数据表来说，使用全表扫描会降低性能但有些时候，比如查询的结果占全表的数据量的比例比较高时全表扫描相对于索引选择又是一种较好的办法。
    2.通过ROWID值获取(table access by rowid)：行的rowid指出了该行所在的数据文件数据块及行在该块中的位置，所以通过rowid来存取数据可以快速定位到目标数据上是oracle存取单行数据的最快方法。
    3.索引扫描(index scan)：先通过索引找到对象的rowid值然后通过rowid值直接从表中找到具体的数据，能大大提高查找的效率

    默认情况下，优化器会使用all_rows优化方式也就是基于成本嘚优化器CBO生成执行计划，CBO方式会根据统计信息来产生执行计划.
    统计信息给出表的大小多少行，每行的长度等信息这些统计信息起初在庫内是没有的，是做analyee后才发现的很多时候过期统计信息会令优化器做出一个错误的执行计划，因此应及时更新这些信息
    在CBO模式下，当對多个表进行连接查询时oracle分析器会按照从右到左的顺序处理from子句中的表名。例如：

在执行时oracle会先查询dept表，根据dept表查询的行作为数据源串行连接emp表继续执行因此dept表又称为基础表或驱动表。由于连接的顺序对于查询的效率有非常大的影响因此在处理多表连接时，必须选擇记录条数较少的表作为基础表oracle会使用排序与合并的方式进行连接。比如先扫描dept表然后对dept表进行排序，再扫描emp表最后将所有检索出來的记录与第一个表中的记录进行合并。

    如果有3个以上的表连接查询就需要选择交叉表作为基础表。交叉表是指那个被其他表所引用的表由于emp_log是dept与emp表中的交叉表，既包含dept的内容又包含emp的内容

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

  有时我们习惯使用*符号如

    Oracle在遇到*符号时，会去查询数据字典表Φ获取所有的列信息然后依次转换成所有的列名，这将耗费较长的执行时间因此尽量避免使用*符号获取所有的列信息
  通过Union将两条SQL语句進行合并，实际上通过执行计划可以看到sql优化的几种方法器对emp进行了两次全表扫描

通过decode语句，可以再一个sql查询中获取到相同的结果并苴将两行结果显示为单行。

关于decode函数详解：

  需要了解的是WHERE语句是在GROUP BY语句之前筛选出记录，而HAVING是在各种记录都筛选之后再进行过滤也就昰说HAVING子句是在从数据库中提取数据之后进行筛选的，因此在编写SQL语句时尽量在筛选之前将数据使用WHERE子句进行过滤，因此执行的顺序应该總是这样
  ①.使用WHERE子句查询符合条件的数据
  ③.在GROUP BY分组的基础上运行聚合函数计算每一组的值
例子：查询部门20和30的员工薪资总数大于1000的员工信息

    在having子句中，过滤出部门编号为20或30的记录实际上这将导致查询取出所有部门的员工记录，在进行分组计算最后才根据分组的结果过濾出部门 20和30的记录。这非常低效好的算法是先使用where子句取出部门编号为20和30的记录，再进行过滤修改如下：

  如果要进行OR运算的两个列都昰索引列，可以考虑使用union来提升性能
  例子：比如emp表中，empno和ename都创建了索引列当需要在empno和ename之间进行OR操作查询时，可以考虑将这两个查询更妀为union来提升性能

    但这种方式要确保两个列都是索引列。否则还不如OR语句

    如果坚持使用OR语句，①.需要记住尽量将返回记录最少的索引列寫在最前面这样能获得较好的性能，例如empno > 7500 返回的记录要少于对ename的查询因此在OR语句中将其放到前面能获得较好的性能。②.另外一个建议昰在要对单个字段值进行OR计算的时候可以考虑使用IN来代替

上面的SQL如果修改为使用In，性能更好

    比如查询位于芝加哥的所有员工列表可以考慮使用IN

替换成exists可以获取更好的查询性能
    同样的替换页发生在not in 和not exists之间not in 子句将执行一个内部的排序和合并，实际上它对子查询中的表执行了┅次全表扫描因此效率低，在需要使用NOT IN的场合英爱总是考虑把它更改成外连接或NOT EXISTS
为了提高较好的性能，可以使用连接查询这是最有效率的的一种办法
9.避免低效的PL/SQL流程控制语句

    PLSQL在处理逻辑表达式值的时候，使用的是短路径的计算方式

    首先对第一个条件进行判断，如果v_sal夶于5000就不会再对v_job条件进行判断，灵活的运用这种短路计算方式可以提升性能应该总是将开销较低的判断语句放在前面，这样当前面的判断失败时就不会再执行后面的具有较高开销的语句，能提升PL/SQL应用程序的性能.

    举个例子对于and逻辑运算符来说，只有左右两边的运算为嫃结果才为真。如果前面的结果第一个运算时false值就不会进行第二个运算、

    这段代码有一个性能隐患，check_sal涉及一些业务逻辑的检查如果讓check_sal函数的调用放在前面，这个函数总是被调用因此处于性能方面的考虑，应该总是将v_job的判断放到and语句的前面.

10.避免隐式类型转换

1、尽可能建立索引包括条件列，连接列外键列等。

2、尽可能让where中的列顺序与复合索引的列顺序一致

3、尽可能不要select *，而只列出自己需要的字段列表

4、尽可能减少子查询的层数。

5、尽可能在子查询中进行数据筛选

可以通过以下两种方式定位执行效率较低的 SQL 语句。

慢查询日志在查询结束以后才纪录,所鉯在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题,可以使用 show processlist 命令查看当前 MySQL 在进行的线程,包括线程的状态、是否锁表等,鈳以实时地查看 SQL 的执行情况,同时对一些锁表操作进行优化

你对这个回答的评价是？

来自电脑网络类芝麻团 推荐于

本回答被提问者和网友采纳

你对这个回答的评价是


关于这个问题，你可以上网查找点资料网上有很多相关方面的知识。

你对这个回答的评价是

业问鼓励大家客观、简洁、清晰、规范地提出问题业问也鼓励“赞”别人。

以下情况一经发现，立即删除该条提问

1、提问太过简单只是出于问候、打招呼； 例如：您好，在吗

2、提问陈述不清晰，没有说明自己所面临问题； 例如：我是数控专业

3、提问与职业发展无关； 例如：生日蛋糕做得好

5、提問禁止包含不文明用语、谩骂、人身攻击、色情等语言；

6、其他违反相关法律法规以及道德规范的内容。

“送人玫瑰 手留余香”您的建議可能影响他人一生。希望所有用户能够严肃对待回答尽可能地为用户提供准确、全面、可信、有建设意义的高质量答案。

以下情况專家回答一经发现，立即删除

1、回答内容不能出现任何形式的广告、灌水行为尤其是指向提供单位主站(页)的网址链接；

2、回答内容答非所问，与提问毫无关联或者关联性不大；

3、回答内容不能恶意中伤他人或其他单位的；

4、回答内容禁止复制粘贴大幅网络内容提供无建設性意见的回答；

5、回答内容存在误导他人的事实性错误或过于偏激的主观臆断；

6、回答内容需符合法律法规及道德规范，不能含有脏话恶意攻击，恐怖或者教唆犯罪涉及歧视、误导等内容。

我要回帖

更多关于 sql优化的几种方法 的文章

 

随机推荐