总结了一下关于索引的基础问题，希望可以给想要了解索引相关知识的朋友带来点帮助
1.先说说索引的分类吧：
（1）B-Tree索引 (查询比较时，比较的是键值，适用于OLTP)
a. 单键索引
create index xx on t1(x);
b. 复合键索引
create index xx on t1(x,y,z);
c. 唯一索引（单列or复合列）
create unique index xx on t1(x);
d. 函数索引 (应用范围窄 只能为指定的一个函数服务)
create index xx on t1(upper(x));
e. 反键索引
（2）BITMAP索引
查询比较时，比较的是0和1这样的位图，并发（DML）性差，适用OLAP
这里有一道在笔试中经常遇到的面试题：
题目：使用索引查询一定能提高查询的性能吗？为什么？
答：不一定
1、 对于大表而言，如果要查询的行数过多（比如30%以上），走全表扫描可能性能会更好一些，成本会更低。
2、 对于小表而言，也许就用了一个8k的block，加载一次就可以了，此时再走索引，在查询索引的过程中还要加载索引块，反而没有全表扫描快。
*索引是oracle自动维护的
当DML发生时，我们改的是表，oracle却在后台维护索引的组织结构，所以索引会增加DML的负担。
2. 何时建索引？
索引多不一定就性能好，索引对查询有利，对DML是个负担。
（1） 一个字段包含的值比较多，且重复率比较的小，即：count（distinct col）~ count（*）
所谓的高基数列 ，如：员工编号，入职日期等。
在一个大的范围内找一个小的范围或单个的值，索引很效率。
（2） 一个字段中包含大量的NULL值
因为索引中是不存NULL值得，这就意味着，这个索引很小，查起来很快
因为你写where a=1 走索引就相当于写的是 where a is not null and a=1；
但是如果你查null值，效率会很低。
（3） 频繁出现在where 子句中的字段，是否需要建立索引。
查看SP 、AWR报告
看TOP SQL （CPU、IO、SORT）对应的sql语句。
（4） 大表的外键列，需要建立索引
1、因为主键DML操作会间接的去外键列查找是否在外键列引用，若外键列没有索引，就会走全表扫描，所以一个主键只有几条记录，外键无索引，一个DML可能会几个小时.....
2、主表和从表做关联查询的时候，如果从表外键没有索引，关联时会对从表全表扫描，效率低下
3.索引的创建和应用
关于索引的一些常用字典：
user_indexes、user_ind_columns、index_stats
普通索引的创建
自动创建
建立主键和唯一约束时，自动创建唯一索引
手动创建
Create (unique) index index_name on table(COL1,COL2....);
创建的条件：
对表有访问权限、create index权限
建立一个普通索引：
SQL> create table t1 as select * from emp;
Table created.
SQL> create index t1_empno_idx on t1(empno);
Index created.
SQL> select TABLE_NAME,INDEX_NAME,INDEX_TYPE,STATUS from user_indexes where table_name='T1';
TABLE_NAME INDEX_NAME INDEX_TYPE STATUS
----------- --------------------- ------------- ----------------
T1 T1_EMPNO_IDX NORMAL VALID
测试你的语句是否用到了索引：
SQL> select * from v$object_usage where table_name='T1';
no rows selected
SQL> alter index t1_empno_idx monitoring usage；
SQL> select * from v$object_usage where table_name='T1';
INDEX_NAME TABLE_NAME MONITO USED START_MONITORING END_MONITORING
--------- ----------- ---- ---- ---------------------- ------------------
T1_EMPNO_IDX T1 YES NO 04/21/2013 18:43:52
SQL> select count(*) from t1;
COUNT(*)
----------
14
SQL> select * from v$object_usage where table_name='T1';
INDEX_NAME TABLE_NAME MONITO USED START_MONITORING END_MONITORING
--------- ----------- ---- ----- ---------------- ------------------
T1_EMPNO_IDX T1 YES NO 04/21/2013 18:43:52
SQL> select count(*) from t1 where deptno=10;
COUNT(*)
----------
3
SQL> select * from v$object_usage where table_name='T1';
INDEX_NAME TABLE_NAME MONITO USED START_MONITORING END_MONITORING
------------------ ----------- ----------- -------- ---------------------- ------------------
T1_EMPNO_IDX T1 YES NO 04/21/2013 18:43:52
SQL> select count(*) from t1 where empno=7788;
COUNT(*)
----------
1
SQL> select * from v$object_usage where table_name='T1';
INDEX_NAME TABLE_NAME MONITO USED START_MONITORING END_MONITORING
------------------ ----------- ----------- -------- ---------------------- ------------------
T1_EMPNO_IDX T1 YES YES 04/21/2013 18:43:52
再次测试时，将监控停止重新开
SQL> alter index t1_empno_idx monitoring usage;
Index altered
SQL> select * from v$object_usage where table_name='T1';
INDEX_NAME TABLE_NAME MONITO USED START_MONITORING END_MONITORING
------------------ ----------- ----------- -------- ---------------------- ------------------
T1_EMPNO_IDX T1 YES NO 04/21/2013 19:50:45
查看索引的内部结构，查看之前要先分析
SQL> analyze index t1_empno_idx validate structure;
Index analyzed.
SQL> select HEIGHT,BLOCKS,BR_BLKS,LF_BLKS,LF_ROWS,DEL_LF_ROWS from index_stats;
HEIGHT BLOCKS BR_BLKS LF_BLKS LF_ROWS DEL_LF_ROWS
------ ------ ------- ------ ----- -------
1 8 0 1 14 0
当前的表很小，索引只需要一个叶子就能容下，高度只有1
SQL> update t1 set empno=7777 where empno=7839;
1 row updated.
SQL> commit;
Commit complete.
SQL> analyze index t1_empno_idx validate structure;
Index analyzed.
SQL> select HEIGHT,BLOCKS,BR_BLKS,LF_BLKS,LF_ROWS,DEL_LF_ROWS from index_stats;
HEIGHT BLOCKS BR_BLKS LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
1 8 0 1 15 1
7839这个empno没了，索引中被置空，但位置还是保留的，新值会记录在新位置。
置空的位置可以被重用。
SQL> insert into t1 select * from t1;
14 rows created.
SQL> insert into t1 select * from t1;
28 rows created.
SQL> /
56 rows created.
SQL> /
112 rows created.
SQL> /
224 rows created.
SQL> /
448 rows created.
SQL> analyze index t1_empno_idx validate structure;
Index analyzed.
SQL> select HEIGHT,BLOCKS,BR_BLKS,LF_BLKS,LF_ROWS,DEL_LF_ROWS from index_stats;
HEIGHT BLOCKS BR_BLKS LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
2 8 1 2 896 0
合并索引 去除置空条目
SQL> delete t1 where rownum<=600;
600 rows deleted.
SQL> analyze index t1_empno_idx validate structure;
Index analyzed.
SQL> select HEIGHT,BLOCKS,BR_BLKS,LF_BLKS,LF_ROWS,DEL_LF_ROWS from index_stats;
HEIGHT BLOCKS BR_BLKS LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
2 8 1 2 896 600
SQL> alter index t1_empno_idx coalesce; 合并索引
Index altered.
SQL> analyze index t1_empno_idx validate structure;
Index analyzed.
SQL> select HEIGHT,BLOCKS,BR_BLKS,LF_BLKS,LF_ROWS,DEL_LF_ROWS from index_stats;
HEIGHT BLOCKS BR_BLKS LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
2 8 1 1 296 0
合并索引只去除了置空条目，没有降低高度。
重构索引（rebuild），删除旧的，建新的。
SQL> alter index t1_empno_idx rebuild;
Index altered.
SQL> analyze index t1_empno_idx validate structure;
Index analyzed.
SQL> select HEIGHT,BLOCKS,BR_BLKS,LF_BLKS,LF_ROWS,DEL_LF_ROWS from index_stats;
HEIGHT BLOCKS BR_BLKS LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
1 8 0 1 296 0
Coalesce合并：
不释放索引结构占的空间
即使有事物也可以合并
只合并枝干内的叶子，不同枝干内的叶子独立合并，一个枝干内的叶子都被清空，回收这个枝干
合并不改变索引结构，高度不变
Rebuild 重构：
删旧建新。
只能在没有事物的时候重构
表很大时，重构的时间会很长
当业务高峰时，使用coalesce合并，但不能解决索引高度问题
等业务不繁忙的时候rebuild。
*重构或者建立索引前，加大排序区的大小。
删除索引
Drop index index_name;
删除表会级联删除表上的索引。
未完待续......
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
