优化_sql_视图_索引_plsql总结

一、sql优化1、SELECT子句中避免使用*，尽量应该根据业务需求按字段进行查询2、尽量多使用COMMIT如对大数据量的分段批量提交释放了资源，减轻了服务器压力3、在写sql语句的话，尽量保持每次查询的sql语句字段用大写，因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行4、用UNION-ALL替换UNION，因为UNION-ALL不会过滤重复数据，所执行效率要快于UNION,并且UNION可以自动排序，而UNION-ALL不会5、避免在索引列上使用计算和函数,这样索引就不能使用Sql优化精简版：1.(重点)(必须说)SELECT语句中避免使用*，尽量应该根据业务需求按字段进行查询举例：如果表中有个字段用的是clob或者是blob这种大数据字段的话，他们的查询应该根据业务需要来进行指定字段的查询，切记勿直接用*2.(重点)删除重复记录(oracle)：最高效的删除重复记录方法(因为使用了ROWID)例子：DELETEFROMEMPEWHEREE.ROWID(SELECTMIN(X.ROWID)FROMEMPXWHEREX.EMP_NO=E.EMP_NO);3.用=替换如一个表有100万记录，一个数值型字段A，A=0时，有30万条；A=1时，有30万条；A=2时，有39万条；A=3时，有1万记录。那么执行A2与A=3的效果就有很大的区别了，因为A2时，ORACLE会先找出为2的记录索引再进行比较，而A=3时ORACLE则直接找到=3的记录索引。4.(重点)尽量多使用COMMIT如对大数据量的分段批量提交5.(重点)用NOTEXISTS或（外连接+判断为空）方案替换NOTIN操作符此操作是强列推荐不使用的，因为它不能应用表的索引。推荐方案：用NOTEXISTS或（外连接+判断为空）方案代替6.(重点必须说)LIKE操作符(大数据的全文检索使用luncene)(solr)因为使用like不当，会导致性能问题，原因是like在左右两边都有%的时候，不会使用索引。如LIKE'%5400%'这种查询不会引用索引，而LIKE'X5400%'则会引用范围索引。一个实际例子：查询营业编号YY_BHLIKE'%5400%'这个条件会产生全表扫描，如果改成YY_BHLIKE'X5400%'ORYY_BHLIKE'B5400%'则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。7.(重点,必须说)避免在索引列上使用计算和函数,这样索引就不能使用举例:低效：SELECT…FROMDEPTWHERESAL*1225000;高效:SELECT…FROMDEPTWHERESAL25000/12;8.(重点必须说)用UNION-ALL替换UNION，因为UNION-ALL不会过滤重复数据而且不会自动排序，所执行效率要快于UNION。9.（优化,重点,3个方面a.缓存b.分段批量c.存储过程）减少访问数据库的次数举例:如果批量删除多条数据，可以用deletefromtableNamewhereidin(1,2,3)而不要用多条delete语句进行删除10.（重点必须说）用TRUNCATE替代DELETETRUNCATE不记录日志，DELETE记录日志，所以TRUNCATE要快于DELETE但是一旦用TRUNCATE进行删除就不能进行恢复,TRUNCATE是删除整张表的数据不能加where条件。=========================================mysql,sqlserver中如果id为自增类型，那么如果用TRUNCATE删除，则id字段再插入数据时从1开始，如果delete删除的话，则从删除之前的id的值继续增长。二、视图优化1.为视图建立索引:视图太复杂建不起索引2.重写视图优化逻辑:出错的风险太大,现在的逻辑是经过复杂验证,保证数据是正常的,如果重写逻辑出错的风险太大3.把视图数据写入表(以表替换视图),对表查询:对表查询性能会明显提高,但会带来数据不同步问题,数据的实时性要求很高,为了保证表与视图同步,太频繁同步操作性能反倒慢了,同步一次很费时间三、Plsql优化1.去掉不必要的大型表的全表扫描2.缓存小型表的全表扫描3.检验优化索引的使用4.检验优化的连接技术5.尽可能减少执行计划的Cost四、索引优化（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)：ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表drivingtable)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表.（2）WHERE子句中的连接顺序．：ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.（3）SELECT子句中避免使用‘*‘：ORACLE在解析的过程中,会将'*'依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间（10）尽量多使用COMMIT：只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:COMMIT所释放的资源:a.回滚段上用于恢复数据的信息.b.被程序语句获得的锁c.redologbuffer中的空间d.ORACLE为管理上述3种资源中的内部花费（11）用Where子句替换HAVING子句：避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.（19）sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行（21）避免在索引列上使用NOT通常，我们要避免在索引列上使用NOT,NOT会产生在和在索引列上使用函数相同的影响.当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.（22）避免在索引列上使用计算．WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．举例:低效：SELECT…FROMDEPTWHERESAL*1225000;高效:SELECT…FROMDEPTWHERESAL25000/12;（23）用=替代高效:SELECT*FROMEMPWHEREDEPTNO=4低效:SELECT*FROMEMPWHEREDEPTNO3两者的区别在于,前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.（25）用IN来替换OR这是一条简单易记的规则，但是实际的执行效果还须检验，在ORACLE8i下，两者的执行路径似乎是相同的．低效:SELECT….FROMLOCATIONWHERELOC_ID=10ORLOC_ID=20ORLOC_ID=30高效SELECT…FROMLOCATIONWHERELOC_ININ(10,20,30);（26）避免在索引列上使用ISNULL和ISNOTNULL避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录.对于复合索引，如果每个列都为空，索引中同样不存在此记录.如果至少有一个列不为空，则记录存在于索引中．-------举例:如果唯一性索引建立在表的A列和B列上,并且表中存在一条记录的A,B值为(123,null),ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入).然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空.因此你可以插入1000条具有相同键值的记录,当然它们都是空!因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.（27）总是使用索引的第一个列：如果索引是建立在多个列上,只有在它的第一个列(leadingcolumn)被where子句引用时,优化器才会选择使用该索引.这也是一条简单而重要的规则，当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引（30）避免改变索引列的类型.:当比较不同数据类型的数据时,ORACLE自动对列进行简单的类型转换.为了避免ORACLE对你的SQL进行隐式的类型转换,最好把类型转换用显式表现出来.注意当字符和数值比较时,ORACLE会优先转换数值类型到字符类型四、不要使用SELECT*1减少内存耗费和网络的带宽2你可以得到更安全的设计3给查询优化器机会从索引读取所有需要的列十三、不要使用INSERT导入大批的数据请不要这样做，除非那是必须的。使用UTS或者BCP，这样你可以一举而兼得灵活性和速度。十八、尽量不要使用TEXT数据类型除非你使用TEXT处理一个很大的数据，否则不要使用它。因为它不易于查询，速度慢，用的不好还会浪费大量的空间。一般的，VARCHAR可以更好的处理你的数据。视图视图是把现在有数据组合成新的形式展示出来，相当于一张虚拟的表，运行时用来呈现数据。视图和存储过程的区别只能查。。。增删改是不行的好处是不用存储在数据库里。。1，为最终用户减少数据库呈现的复杂性。客户端只要对视图写简单的代码，就能返回我所需要的数据，一些复杂的逻辑操作，放在了视图中来完成；2，防止敏感的列被选中，同时仍然提供对其他重要数据的访问；3，对视图添加一些额外的索引，来提高查询的效率；视图其实没有改变任何事情，只是对访问的数据进行了某种形式的筛选。考虑一下视图的作用，你应该能看到视图的概念如何为缺乏经验的用户简化数据(只显示他们关心的数据)，或者不给予用户访问基础表的权利，但授予他们访问不包含敏感数据视图的权力，从而提前隐藏敏感数据。要知道，在默认的情况下，视图没有做什么特殊的事情。视图就好象一个查询那样从命令行运行(这里不存在任何形式的预先优化),这意味着在数据请求和将被交付的数据之间多加了一层开销。这表明视图绝不可能像只是直接运行底层SELECT语句那样快。不过，视图存在有一个原因--这就是它的安全性或为用户所做的简化，在你的需要和开销之间权衡，找到最适合特定情况的解决方案