索引在数据库中是很重要的.没有索引的数据库是不可想象的,我们普通的表是无序的,也叫做堆表(heap table),一句话概括索引,索引是有序的结构,通过索引可以快速定位我们要找的行,避免全表扫描.索引的访问模式有五种.
1、INDEX UNIQUE SCAN 效率最高，主键或唯一索引
2、INDEX FAST FULL SCAN 读的最块，可以并行访问索引，但输出不按顺序
3、INDEX FULL SCAN 有顺序的输出，不能并行读索引
4、INDEX RANGE SCAN 给定的区间查询
5、INDEX SKIP SCAN 联合索引，不同值越少的列，越要放在前面
analyze table t1 compute statistics;
收集表t1的统计信息
create unique index i2t1 on t1(object_id);
set autot traceonly explain
1、INDEX UNIQUE SCAN 效率最高，主键或唯一索引
select * from t1 where object_id=9999;
2、INDEX FAST FULL SCAN 读的最块，可以并行访问索引，但输出不按顺序
select object_id from t1 ;
为什么没有使用索引,而进行了全表扫描.因为object_id可能有null值.因为null不入普通索引.我们进行全索引的扫描就会得到错误的结果.这是全表扫描是正确的.虽然我们的的查询仅包含了索引中的值.我们如果有非空约束就会极大的提高性能.
delete t1 where object_id is null;
alter table t1 modify (OBJECT_ID not null);
select object_id from t1 ;
计划仅扫描了索引,代价为51.因为所有的行都在索引中了,使用索引不会造成错误的结果.因为我们的输出没有要求有序,所以数据库将高水位下所有的索引块都读一遍就可以了,这就叫索引的快速全扫描.
3、INDEX FULL SCAN 有顺序的输出，不能并行读索引
select object_id from t1 order by object_id ;
执行计划为全扫描索引,含义是按叶子的大小顺序来读索引,因为我们要求输出是有序的.代价为106,高于快速全扫描,因为我们不是将高水位的块连续读,而是按照叶子的顺序读.正因为是按照叶子的顺序读,所以不能并行操作.
4、INDEX RANGE SCAN 给定的区间查询
select * from t1 where object_id between 300 and 400;
当我们的索引为非唯一,或者我们的索引唯一但查询的条件为一个范围的时候数据库会选择范围定位.代价的大小取决于你所查询行的多少.
5、INDEX SKIP SCAN 联合索引，不同值越少的列，越要放在前面
索引使用总论:
能用唯一索引，一定用唯一索引
能加非空，就加非空约束
一定要统计表的信息，索引的信息，柱状图的信息。
联合索引的顺序不同，影响索引的选择，尽量将不同值少的列放在前面
只有做到以上四点，数据库才会正确的选择执行计划。
索引是在不修改代码的情况下提高性能的重要手段.索引也是约束的维护纽带.在外键上最好建立索引.
参数optimizer_index_cost_adj定义了索引的权重,该值越大,数据库认为使用索引的成本越高,默
认值为100,如果设置为50,那么数据库认为使用索引的代价比它计算出来的少一半,如果你设置为
1000,那么认为你使用索引的成本为计算出来的10倍,该值最大为10000,最小为1.
转载于:https://blog.51cto.com/liujia/259257
