SQL 优化_如果有id主键(数字,自增1模式),删除表的重复记录

1、删除一张表的重复记录（ID是自增唯一主键，重复记录：其他字段都是一样）
( 数据量很大,性能要求很高)
表名：T
Id name age
1 louis 20
2 louis 20
3 jimmy 30
4 louis 20
做法一：
Delete from t where id not in (Select min(id) from t Group by name,age);
做法二：
delete from t where id in(Select distinct a2.id from t a1,t a2 where
a1.id>a2.id and a1.name=a2.name and a1.age=a2.age);
做法三：
delete from t a1 where not exists(select *
from t a2
where a1.id>a2.id and a1.name=a2.name and a1.age=a2.age
);
前提数据量>100，0000
以上三种做法，均可。但是第三种做法的性能最佳。第一种用not in没办法用到索引.第三
种方式也不会用到索引
数据量 1000 100000 100,0000
方法一 0.047 3.77 72
方法二 0.286 5.77 65
第二种方式快于第一种方式。
SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，执
行过程中访问尽量少的数据块，减少表扫描的I/O次数，尽量避免全表扫描和其他额外开
销。
oracle数据库常用的两种优化器：RBO（rule-based-optimizer）和CBO(cost-based-optimizer)。
目前更多地采用CBO(cost-based-optimizer)基于开销的优化器。在CBO方式下，Oracle会根据表及索
引的状态信息来选择计划；在RBO方式下，Oracle会根据自己内部设置的一些规则来决定选择计划，
例如oracle会根据以下优先级来选择执行计划（越靠前，rank越低，
越快）：
2、尽量少用 IN 操作符
基本上所有的IN操作符都可以用EXISTS代替，在选择IN或EXIST操作时，要根据主子
表数据量大小来具体考虑
3、尽量不用“<>”或者“!=”操作符
不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。比如：a<>0
改为 a>0 or a<0
4、在设计表时，把索引列设置为 NOT NULL
判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的
5、尽量不用通配符“%”或者“”作为查询字符串的第一个字符
当通配符“%”或者“”作为查询字符串的第一个字符时，索引不会被使用。比如用T表中
Column1 LIKE ‘%5400%’ 这个条件会产生全表扫描，如果改成Column1 ‘X5400%’ OR
Column1 LIKE ‘B5400%’ 则会利用Column1的索引进行两个范围的查询，性能肯定大大提
高。
6、Where 子句中避免在索引列上使用计算
如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。
因此Where子句中避免在索引列上使用计算。
比如：
substr(no,1,4)=‘5400’，优化处理：no like ‘5400%’
trunc(hiredate)=trunc(sysdate)，优化处理：hiredate >=trunc(sysdate) and hiredate
<trunc(sysdate+1)
7、用“>=”替代“>”
大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有
的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A， 30万记
EEEEEEE. A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与
A>=3的效果就有很大的区别了，因为 A>2时ORACLE会先找出为2的记录索引再
进行比较，而A>=3时ORACLE则直接找到=3的记录索引
8、利用 SGA 共享池，避开 parse 阶段
1.共享池是oracle缓冲程序数据的地方，执行过的每一条sql语句在共享池中都存有解析后的内容，共享池中存储这些语句的地方称为高速缓冲（library cache），oracle在每次运行时都时先去高速缓冲中找，而不是再解析一遍
2.因为内存区域是有限的，所有不可以将解析后的语句一直放在内存中，oracle使用叫做最近最少使用算法（least Recently used ，LRU）来管理共享池中的对象，类似于先进先出系统，其基本思想是保留那些使用最频繁的以及最近使用的语句
不同区域出现的相同的Sql语句要保证查询字符完全相同，建议经常使用变量来代替常量，
以尽量使用重复sql代码，以利用SGA共享池，避开parse阶段，防止相同的Sql语句被
多次分析，提高执行速度。
因此使用存储过程，是一种很有效的提高share pool共享率，跳过parse阶段，提高效率的
办法。
9、WHERE 后面的条件顺序要求
WHERE后面的条件，表连接语句写在最前，可以过滤掉最大数量记录的条件居后。
比如：
Select * from zl_yhjbqk where dy_dj = ‘1KV以下’ and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = ‘1KV以下’
以上两个 SQL 中 dy_dj（电压等级）及 xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条 SQL 的 dy_dj = '1KV 以下’条件在记录集内比率为 99%，xh_bz=1 的比率只为 0.5%，在进行第一条 SQL 的时候 99%条记录都进行 dy_dj 及 xh_bz的比较，而在进行第二条 SQL 的时候 0.5%条记录都进行 dy_dj 及 xh_bz 的比较，以此可以得出第二条 SQL 的 CPU 占用率明显比第一条低。
10、使用表的别名，并将之作为每列的前缀
YX.Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解
析时间
11、进行了显式或隐式的运算的字段不能进行索引
比如：
ss_df+20>50，优化处理：ss_df>30
‘X’||hbs_bh>‘X5400021452’，优化处理：hbs_bh>‘5400021542’
sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5
hbs_bh=5401002554，优化处理：hbs_bh=‘ 5401002554’，注：此条件对hbs_bh 进行隐
式的to_number转换，因为hbs_bh字段是字符型。
12、用 UNION ALL 代替 UNION
UNION是最常用的集操作，使多个记录集联结成为单个集，对返回的数据行有唯一性要求，
所以oracle就需要进行SORT UNIQUE操作（与使用distinct时操作类似），如果结果集又
比较大，则操作会比较慢；
UNION ALL操作不排除重复记录行，所以会快很多，如果数据本身重复行存在可能性较
小时，用union all会比用union效率高很多！
13、其他操作
尽量使用packages： Packages在第一次调用时能将整个包load进内存，对提高性能有帮
助。
尽量使用cached sequences 来生成primary key ：提高主键生成速度和使用性能。
很好地利用空间：如用VARCHAR2 数据类型代替CHAR等
使用Sql优化工具：sqlexpert；toad；explain-table；PL/SQL；OEM
14、通过改变 oracle 的 SGA 的大小
SGA：数据库的系统全局区。
SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区
1 、 共享池又由两部分构成：共享SQL区和数据字典缓冲区。共享SQL区专门存放用户SQL命令，oracle使用最近最少使用等优先级算法来更新覆盖；数据字
典缓冲区（library cache）存放数据库运行的动态信息。数据库运行一段时间后，
DBA需要查看这些内存区域的命中率以从数据库角度对数据库性能调优。通过执
行下述语句查看：
select (sum(pins - reloads)) / sum(pins) “Lib Cache” from v$librarycache;--查看共享SQL区的重用率，最好在90％以上，否则需要增加共享池的大小。select(sum(gets-getmisses-usage-fixED))/sum(gets)"RowCache"fromv$rowcache;
–查看数据字典缓冲区的命中率，最好在90％以上，否则需要增加共享池的大小。
2 、 数据缓冲区：存放sql运行结果抓取到的data block；
SELECT name, value FROM v$sysstatWHEREnameIN{(}^{\prime }dbblockget{s}^{\prime }{,}^{\prime }consistentget{s}^{\prime }{,}^{\prime }physicalread{s}^{\prime });--查看数据库数据缓冲区的使用情况。查询出来的结果可以计算出来数据缓冲区的使用命中率＝1-(physicalreads/(dbblockgets+consistentgets))。命中率应该在90％以上，否则需要增加数据缓冲区的大小。3、日志缓冲区：存放数据库运行生成的日志。selectname,valuefromv$sysstat where name in (‘redo entries’,‘redo log space
requests’);
–查看日志缓冲区的使用情况。查询出的结果可以计算出日志缓冲区的申请失败
率：申请失败率＝requests/entries，申请失败率应该接近于0，否则说明日志缓冲
区开设太小，需要增加ORACLE数据库的日志缓冲区。