本文目录
- SQL语句的优化
- 记一次Sql执行从17分钟到3秒的优化
- 通过分析SQL语句的执行计划优化SQL
- 【DB2】SQL优化
- 数据库系统优化的LECCO SQL Expert自动优化实例
- ORACLE优化SQL语句，提高效率(2)
SQL语句的优化
倒数第二个可以这样select po.must_pay_cashfrom erp_product_outcoming poLEFT JOIN (select * from erp_sale_collent_detail where flag=1) scd ON scd.shop_id = po.shop_id 先做个子查询消掉一些项再做连接，最后一个类似。
记一次Sql执行从17分钟到3秒的优化
同事小A拿来了一段sql语句问我说为什么执行特别慢，跑一次要十多分钟。我试了一下，好家伙，最慢17分钟。语句如下： 其中TABLE1是一个数据记录表，VEMPLOYEE是一个员工表的视图，我看了一下视图定义，彻底被震惊了 小A解释说，客户要求有好多地方页面展示的时候要屏蔽一些员工，所以就直接搞了个员工视图来做统一的过滤处理。 在sql中使用 IN 或者 NOT IN 的性能是非常差的，至于具体原因，好多大佬解释的很清楚了，我就不再赘述。那么第一步，就是使用LEFT JOIN替换掉语句里边的NOT IN 首先创建一个表 IGNORE_EMP_ID 存储需要忽略的员工ID，只有一个ID列，修改视图创建语句如下： 展示一下 LEFT JOIN 替换 NOT IN 的执行过程，假设EMPLOYEE表有ID为1、2、3这三个员工，需要忽略的ID有1、3这两个时得到的数据为：修改的方法告诉了小A，过了几分钟，我就问他改的咋样，他说正在往新建的 IGNORE_EMP_ID 表插数据。那好吧，我来帮忙插数据好了。 前面介绍过原来的sql里边都是一行一个数字排列的，我们把 NOT IN 里边的所有ID复制出来到txt文件然后回车拉至最后一行，复制出B列所有sql执行即可。
通过分析SQL语句的执行计划优化SQL
如何干预执行计划--使用hints提示基于代价的优化器是很聪明的，在绝大多数情况下它会选择正确的优化器，减轻了DBA的负担。但有时它也聪明反被聪明误，选择了很差的执行计划，使某个语句的执行变得奇慢无比。此时就需要DBA进行人为的干预，告诉优化器使用我们指定的存取路径或连接类型生成执行计划，从而使语句高效的运行。例如，如果我们认为对于一个特定的语句，执行全表扫描要比执行索引扫描更有效，则我们就可以指示优化器使用全表扫描。在Oracle中，是通过为语句添加hints(提示)来实现干预优化器优化的目的。hints是oracle提供的一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用hints来实现：1.使用的优化器的类型2.基于代价的优化器的优化目标，是all_rows还是first_rows。3.表的访问路径，是全表扫描，还是索引扫描，还是直接利用rowid。4.表之间的连接类型5.表之间的连接顺序6.语句的并行程度除了”RULE”提示外，一旦使用的别的提示，语句就会自动的改为使用CBO优化器，此时如果你的数据字典中没有统计数据，就会使用缺省的统计数据。所以建议大家如果使用CBO或HINTS提示，则最好对表和索引进行定期的分析。如何使用hints:Hints只应用在它们所在sql语句块(statementblock，由select、update、delete关键字标识)上，对其它SQL语句或语句的其它部分没有影响。如：对于使用union操作的2个sql语句，如果只在一个sql语句上有hints，则该hints不会影响另一个sql语句。我们可以使用注释(comment)来为一个语句添加hints，一个语句块只能有一个注释，而且注释只能放在SELECT,UPDATE,orDELETE关键字的后面使用hints的语法：{DELETE|INSERT|SELECT|UPDATE}/*+hint...*/or{DELETE|INSERT|SELECT|UPDATE}--+hint...注解：1.DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字，包含提示的注释只能出现在这些关键字的后面，否则提示无效。2.“+”号表示该注释是一个hints，该加号必须立即跟在”/*”的后面，中间不能有空格。3.hint是下面介绍的具体提示之一，如果包含多个提示，则每个提示之间需要用一个或多个空格隔开。4.text是其它说明hint的注释性文本如果你没有正确的指定hints，Oracle将忽略该hints，并且不会给出任何错误。
【DB2】SQL优化
于我来说，我喜欢技术，不偏执于某一类开发语言，愿意花时间精力去解决问题。
1.去除在谓词列上编写的任何标量函数
优化前：(耗时3.1s)
优化后：(耗时0.922s)
总结：
DB2可以选择使用START_DATE上的列索引，但是在列上使用了函数后，DB2就无法使用列索引了，从而导致查询效率变低。
2.去除在谓词列上编写的任何数学运算
优化前：(耗时10.265)
优化后：(耗时3.39s)
总结：
DB2查询时候，会优先选择列CONTRACT_AMT上的索引，如果直接对列CONTRACT_AMT应用数学运算，DB2就无法使用索引了。一定要做到：列本身(不加数学运算)放在操作符的一边，而所有的计算都放在另外一边。
3.SQL语句中指定查询列
优化前：(耗时13.15s)
优化后：(耗时2.922s)
总结：
如果Select包含不需要的列，优化工具会选择Indexonly=’N’,这会强制DB2必须进入数据页来得到所请求的特定列，这就要求更多的I/O操作，梁歪，这些多余的列可能是某些排序的部分，这样一来就需要和传递一个更大的排序文件，相应的会使排序成本更高。
4.尽可能不使用distinct
优化前：(耗时0.687s)
优化后：(耗时0.437s)
总结：
在测试distinct与group by性能的过程中，在列CST_ID上添加索引后，发现group by 确实比distinct快一些，但是在数据分布比较离散的情况下使用group by ，比较集中的情况下使用distinct.表数据量较少的情况随便使用哪个都一样， 不管选择谁，都要建立索引
5.Exists、in、not in 、not exists的使用场景选择
5.1 in跟exists的区别：
例如：表A(小表)，表B(大表)
优化前：(耗时1.93s)
优化后：(耗时1.125s)
相反的，
优化前：(耗时1.9s)
优化后：(耗时1.0s)
总结：
in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询，一直以来认为exists比in效率高的说法是不准确的。 如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in；
简称：子大Exists,子小in
5.2 not in 与 not exists区别：
如果查询语句使用了not in，那么对内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in 要快。
6.尽可能使用union all来代替union
优化前：(耗时15.344s)
优化后：(耗时2.719s)
总结：
在union中，DB2最后会自动执行一个排序来消除重复值，这样是很耗费资源的，所以在不需要去重复的情况下，尽可能使用UNION ALL 代替union
N.模板
优化前：(耗时3.1s)
优化后：(耗时0.922s)
总结：
数据库系统优化的LECCO SQL Expert自动优化实例
假设我们从源代码中抽取出这条SQL语句(也可以通过内带的扫描器或监视器获得SQL语句)：SELECT COUNT(*)FROM EMPLOYEEswheresEXISTS (SELECT ’X’FROM DEPARTMENTswheresEMP_DEPT=DPT_IDAND DPT_NAME LIKE ’AC%’)AND EMP_ID IN (SELECT SAL_EMP_IDFROM EMP_SAL_HIST BswheresSAL_SALARY 》 70000)按下“优化”按钮后，经过10几秒，SQL Expert就完成了优化的过程，并在这10几秒的时间里重写产生了2267 条等价的SQL语句，其中136条SQL语句有不同的执行计划。接下来，我们可以对自动重写产生的136条SQL语句进行批运行测试，以选出性能最佳的等效SQL语句。按下“批运行” 按钮，在“终止条件” 页选择“最佳运行时间SQL语句”，按“确定”。经过几分钟的测试运行后，我们可以发现SQL124的运行时间和反应时间最短。运行速度约有22.75倍的提升(源SQL语句运行时间为2.73秒，SQL124运行时间为0.12秒)。现在我们就可以把SQL124放入源代码中，结束一条SQL语句的优化工作了。
ORACLE优化SQL语句，提高效率(2)
索引是表的一个概念部分 用来提高检索数据的效率 Oracle使用了一个复杂的自平衡B tree结构 通常 通过索引查询数据比全表扫描要快 当 Oracle找出执行查询和Update语句的最好路径时 Oracle优化器将使用索引 同样在联结多个表时使用索引也能够提高效率 另一个使用索引的好处是 他提供了主键(primary key)的唯一性验证 那些LONG或LONG RAW数据类型 您能够索引几乎任何的列 通常 在大型表中使用索引特别有效 当然 您也会发现 在扫描小表时 使用索引同样能提高效率 虽然使用索引能得到查询效率的提高 但是我们也必须注意到他的代价 索引需要空间来存储 也需要定期维护 每当有记录在表中增减或索引列被修改时 索引本身也会被修改 这意味着每条记录的INSERT DELETE UPDATE将为此多付出 次的磁盘I/O 因为索引需要额外的存储空间和处理 那些不必要的索引反而会使查询反应时间变慢 定期的重构索引是有必要的
ALTER INDEX 《INDEXNAME》 REBUILD 《TABLESPACENAME》
（ ）用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时 避免在SELECT子句中使用DISTINCT 一般能够考虑用EXIST替换 EXISTS 使查询更为迅速 因为RDBMS核心模块将在子查询的条件一旦满足后 立即返回结果 例子
(低效): SELECT DISTINCT DEPT_NO DEPT_NAME FROM DEPT D EMP E WHERE D DEPT_NO = E DEPT_NO (高效): SELECT DEPT_NO DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT X FROM EMP E WHERE E DEPT_NO = D DEPT_NO);
（ ）SQL语句用大写的 因为Oracle总是先解析SQL语句 把小写的字母转换成大写的再执行
（ ）在Java代码中尽量少用连接符 ＋ 连接字符串
（ ）避免在索引列上使用NOT通常 我们要避免在索引列上使用NOT NOT会产生在和在索引列上使用函数相同的影响 当Oracle 碰到 NOT 他就会停止使用索引转而执行全表扫描
（ ）避免在索引列上使用计算 WHERE子句中 假如索引列是函数的一部分 优化器将不使用索引而使用全表扫描
举例:
低效 SELECT … FROM DEPT WHERE SAL * 》 ; 高效: SELECT … FROM DEPT WHERE SAL 》 / ;
（ ）用》=替代》
高效 SELECT * FROM EMP WHERE DEPTNO 》= 低效: SELECT * FROM EMP WHERE DEPTNO 》
两者的区别在于 前者DBMS将直接跳到第一个DEPT等于 的记录而后者将首先定位到DEPTNO= 的记录并且向前扫描到第一个DEPT大于 的记录
（ ）用UNION替换OR (适用于索引列)
通常情况下 用UNION替换WHERE子句中的OR将会起到较好的效果 对索引列使用OR将造成全表扫描 注意 以上规则只针对多个索引列有效 假如有column没有被索引 查询效率可能会因为您没有选择OR而降低 在下面的例子中 LOC_ID 和REGION上都建有索引
高效 SELECT LOC_ID LOC_DESC REGION FROM LOCATION WHERE LOC_ID = UNION SELECT LOC_ID LOC_DESC REGION FROM LOCATION WHERE REGION = MELBOURNE
低效: SELECT LOC_ID LOC_DESC REGION FROM LOCATION WHERE LOC_ID = OR REGION = MELBOURNE
（ ）用IN来替换OR
这是一条简单易记的规则 但是实际的执行效果还须检验 在Oracle i下 两者的执行路径似乎是相同的:
低效:
SELECT… FROM LOCATION WHERE LOC_ID = OR LOC_ID = OR LOC_ID =
高效
SELECT… FROM LOCATION WHERE LOC_IN IN ( );
（ ）避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何能够为空的列 Oracle将无法使用该索引 对于单列索引 假如列包含空值 索引中将不存在此记录 对于复合索引 假如每个列都为空 索引中同样不存在此记录 假如至少有一个列不为空 则记录存在于索引中 举例 假如唯一性索引建立在表的A列和B列上 并且表中存在一条记录的 A B值为( null) Oracle将不接受下一条具备相同A B值（ null）的记录(插入) 然而假如任何的索引列都为空 Oracle将认为整个键值为空而空不等于空 因此您能够插入 条具备相同键值的记录 当然他们都是空! 因为空值不存在于索引列中 所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引
低效: (索引失效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
高效 (索引有效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE 》= ;
（ ）总是使用索引的第一个列
假如索引是建立在多个列上 只有在他的第一个列(leading column)被where子句引用时 优化器才会选择使用该索引 这也是一条简单而重要的规则 当仅引用索引的第二个列时 优化器使用了全表扫描而忽略了索引
（ ）用UNION ALL 替换UNION ( 假如有可能的话)
当SQL语句需要UNION两个查询结果集合时 这两个结果集合会以UNION ALL的方式被合并 然后在输出最终结果前进行排序 假如用 UNION ALL替代UNION 这样排序就不是必要了 效率就会因此得到提高 需要注意的是 UNION ALL 将重复输出两个结果集合中相同记录 因此各位还是要从业务需求分析使用UNION ALL的可行性 UNION 将对结果集合排序 这个操作会使用到SORT_AREA_SIZE这块内存 对于这块内存的优化也是相当重要的 下面的SQL能够用来查询排序的消耗量
lishixinzhi/Article/program/Oracle/201311/16789
