数据库索引:
索引有单列索引,复合索引之说。
如某表的某个字段有主键约束和唯一性约束,则oracle会自动在相应的约束列上建议问一索引。数据库索引主要进行提高访问速度。
(如楼层索引,进来先看索引能快速定位到要找的楼层房间)
建设原则:
1、索引应该常建在where子句经常用到的列上。如果某个大表经常使用某个字段进行查询,并且检索行数小于总表行数的5%,则应该考虑。
2、对于两表链接的字段,应该建立索引。如果经常在某表的一个字段进行order by 则也经过进行索引。
3、不应该在小表上建设索引。
优缺点:
1、索引主要进行提高数据的查询速度。当进行DML时,会更新索引。因此索引越多,则DML越慢,其需要维护索引。因此在创建索引及DML时需要权衡。
创建索引:
单一索引:create Index on (column_name)。
复合索引:create Index cbs_Index on thr_cbsglb(ssxm,ywzgbm)。
select * from thr_cbsglb where ssxm= 66 and ywzgbm= ywzgbm-->走索引
select * from thr_cbsglb where CBS = 66 or ywzgbm= 45 -->不走索引
select * from thr_cbsglb where ssxm= 66 -->走索引
select * from thr_cbsglb where ywzgbm= 45-->不走索引
如果where子句中有or操作符或单独引用ywzgbm列(索引列的后面列)则将不会走索引,将会进行全盘扫描。
Sql优化:
当oracle数据库拿到sql语句时,其会根据查询优化器分析改语句,并根据分析结果生成查询执行计划。也就是说数据库时执行的查询计划,而不时sql语句。
查询优化器有rule-based-optimizer(基于规则的查询优化器)和Cost-Based-optimizer(基于成本的查询优化器)。
其中基于规则的查询优化器在10g版本中消失。对于规则查询,其最后查询的是全表扫描。而CBO则会根据统计信息进行最后的选择。
1、先执行from -> where -> group by -> order by
2、执行form字句是从右往左执行。因此必须选择记录条数最少的表放在右边。
3、对于where子句其执行顺序是从后往前执行、因此可以过滤大数量记录的条件必须写在where子句的末尾。而对于多表之间的连接,则写在之前,因为这样连接时,
可以去掉大多不重复的项。
4、select子句中避免使用(*),oracle在解析的过程中,会将(*)依次转换成所有的列名,这个工作时通过数据字典完成的,这意味着将耗费更多的时间。
5、索引失效情况:
① not null/null 如果某列建立索引,当进行select * from thr_cbsglb where ssxm is not null/ is null 则会索引失效。
② 索引列上不要使用函数,select ssgs from thr_cbsglb where substr(cbsmc,1,3) = '12队' 或则 select ssgs from thr_cbsglb where cbsmc like '%12队%',前者失效后者会使用。
③ 索引上不能进行计算, select ssgs from thr_cbsglb where ssxm / 10 > 10 则会使索引失效,应改成select ssgs from thr_cbsglb where ssxm > 10 * 10。
④ 索引列上不要使用not(!=、<>)如:select ssgs from thr_cbsglb where ssxm != 10,应改成select ssgs from thr_cbsglb where ssxm > 10 or ssxm < 10。
6、用union替换or(适用于索引列)
union:是将使将两个查询的结果集并行到一起,它不会引起列的变化。由于是追加操作,需要两个结果集的列数应该是相关的,并且相应列的数据类型也因该相同的。union返回
两个结果集,同时将两个结果集的重复项进行取消,如果不取消则用union all。
通常情况下,用union替换where子句中的or将会起到较好的效果,队索引列使用or将造成全表扫描。注意:以上规则只针对多个索引列有效。
如果有column没有被索引,查询效率可能会因为没有选择or而降低。(thr_cbsglb_id、cbsmc都建有索引)
高效:
select thr_cbsglb_id,ssxm,cbsmc from thr_cbsglb where thr_cbsglb_id = 10
union
select thr_cbsglb_id,ssxm,cbsmc from thr_cbsglb where cbsmc = '12队'
低效:
select thr_cbsglb_id,ssxm,cbsmc from thr_cbsglb where thr_cbsglb_id = 10 or cbsmc = '12队'
如果坚持要用or,那就需要将返回记录最少的索引列写在最前面。
7、用exists代替 in、用not exists代替 not in
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表惊醒连接,在这种情况下使用exists或者not exists通常将提高查询的效率。
在子查询中,not in 子句将执行一个内部的排序和合并,无论在哪种情况下,not in都是最低效的(因为它对子查询中的表执行了一个全表遍历)。
为了避免使用not in,可以把它改写为外连接(outer joins)或not exists。
高效:
select * from THR_AQJCBZK where exists(select 'x' from thr_yjfl where thr_yjfl.yjfl_id = thr_aqjcbzk.yjfl_id and zjid like '12%')
低效:
select * from THR_AQJCBZK where yjfl_id in(select yjfl_id from thr_yjfl where zjid like '12%')