索引优化笔记

索引的三大特点：1、索引高度较低(几百亿条。高度不过6层)；2、由索引列存储的值及rowid组成；3、索引本身有序；
select t.INDEX_NAME,t.blevel（索引的层级）,t.LEAF_BLOCKS,t.NUM_ROWS,t.DISTINCT_KEYS,t.CLUSTERING_FACTOR（聚合因子，这个值比较低的话有利于提高查询速度） from 
user_ind_statistics t where t.TABLE_NAME like 'T%'

count(*)优化
即使有索引，select count(*) from t 不一定会用到索引，加上非空约束或者主键，会走快速索引扫描，sum(col),avg(col)同理
min(col)优化，只要该列上有索引，即使没有非空约束，会走快速索引扫描，但是select max(col),min(col) from t就变成全表扫描;针对这种同时要取最大最小值的查询sql语句应该这么写

select max,min from (select max(t.object_id) max from t) a,(select max(t.object_id) min from t) b

索引回表读
（1）减少索引回表读，能够有效减少逻辑读；
（2）索引不含查询列时候可以考虑组合索引；
如果联合索引的索引列太多，必然导致索引很大，虽然减少了回表动作，但是索引块变多，在索引中查询就要遍历更多的block了；
（3）聚合因子决定了回表查询的速度；
在索引回表读不可避免的情况下，必须执行回表这个动作，聚合因子会影响查询的速度；



番外
statistics_level 参数是oracle9.2开始引入的一个控制系统统计参数收集的一个开关.一共有三个值:basic,typical,all；如果要用statspack或者AWR收集系统性能统计数据.那么这个参数

的值必须为typical或all.通常all是一个全面收集,包括 OS以及sql执行路径方面的一些统计信息；
statistics_level=basic的情况下,oracle关闭了所有性能数据的收集,也就是如果要关闭AWR或statspack收集
statistics_level=typical的时 候,除了plan_executetion_statistics和OS Statistics不能收集外,其他的都可以收集
statistics_level=ALL,系统收集所有的统计信息

sqlplus默认的统计信息级别是TYPICAL，可以通过show parameter statistics_le查看；

有关排序的优化
order by
当order by语句中，可疑考虑在该列建索引，尤其是系统面临严重的瓶颈时；

distince（涉及到排序）
只有排序后，才能更容易去重；
0 sorts（memory）以及sorts（disk）可疑看出没有排序；
tempSpc可以看出排序所使用的空间；
distinct采用的是HASH UNIQUE算法，如果语句中有等值查询而非范围查询时，将产生SORT UNIQUE NOSORT的算法，会消除排序；

索引快速扫描与索引全扫描
两者的相同点是都是针对真个的索引的全扫描，从头到尾遍历索引，而非局部的INDEX FULL SCAN MIN(MAX)或者INDEX RANGE SCAN
不同点是快速扫描一次读取多个索引块，而索引全扫描一次读取一个索引块；一次读取一个索引块，不容易保证有序，一次读取一个索引块保证有序，因此有序的场合，全扫描的顺序读可以


让排序消除，快速扫描虽然减少了逻辑读，但是无法消除排序；所以count(*)以及一些sum之类统计运算无需使用排序，快速扫描即可；而涉及到排序时候，就需要权衡，有时候是快速扫描快


，有时候是全扫描快；


union all 合并优化
union all 肯定不需要排序，因为就是简单的合并，但是union需要排序，因为涉及到会去除重复的记录；所以有时候union all的优化就是把union 改成union all

涉及到主外键的索引的优化
外键上建索引，不只是性能上的提升，还能有效避免锁的竞争
在索引列上建主键，不必删除该索引，直接建主键即可；

联合索引
使用联合索引能够有效避免回表；
如条件a返回数据较多，条件b返回数据较多，但是a and b 返回数据较少，适合使用联合索引；
在等值查询的情况下，组合索引无论那一列在前在后，性能都差异不够明显；
组合索引两列，当一列是等值查询，而另外一列是范围查询时，等值查询在前，范围查询在后这样的索引比较高效；
where id in (20,21) 与where id >=20 and id<=21 其结果集是一样的，但是in代表的是两个点，而id >=20 and id<=21代表的是一条线；所以in的效率会高一些；
如果单列的查询列和联合索引的前置列一样，那单列可以不用建索引，而直接使用联合索引来进行检索数据；

索引的危害
索引越多，插入速度越慢；
无序的插入，速度更慢；（针对这种情况的优化，可以先把索引删掉，然后插入数据，在建索引）
索引过多对insert的影响最大，delete次之，对update的影响最小；
建索引会产生锁；

监控索引的使用情况
alter index 索引名 monitoring usage;
select * from v$object_usage;
该方法的不足是不能监控该索引被使用了多少次；


位图索引
count(*)的性能，在非空列有btree索引的情况下，一般使用该索引性能高于全表扫描，性能最高的还是列上有位图索引，而且位图索引不用考虑列的空值问题；
位图索引对高并发的支持不好，会造成锁等问题；比如在列有（m,g）两个值，如果一个会话对m进行更新，其他所以对m值的DML操作都会被阻塞；
所以位图索引适用的两个条件是：列值大量重复；该表极少被更新；
btree索引储存的是列值，而位图索引存储的是比特位值（0，1，null），所以count(*)时候，该列重复度高，会很快；

函数索引
避免列运算

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27057145/viewspace-1985954/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27057145/viewspace-1985954/