索引面试题复习

一、什么是索引？
一种用于提升查询效率的数据库对象；
通过快速定位数据的方法，减少磁盘I/O操作；
索引信息与表独立存放；
ORACLE数据库自动使用和维护索引；
二、索引的分类
唯一性索引；
非唯一性索引；
三、索引的创建
自动创建：在定义主键或唯一键约束时系统会自动在相应的字段创建唯一性索引；
手动创建：用户可以在其他列上创建非唯一性索引；
四、索引优缺点
优点：
1.大大加快数据的检索速度;
2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
3.加速表和表之间的连接;
4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
缺点：
1.索引需要占物理空间。
2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度；

五、创建索引的原则
创建索引：创建索引一般有以下两个目的：维护被索引列的唯一性和提供快速访问表中数据的策略。
1.在select操作占大部分的表上创建索引；
2.在where子句中出现最频繁的列上创建索引；
3.在选择性高的列上创建索引（补充索引选择性，最高是1，eg：primary key）
4.复合索引的主列应该是最有选择性的和where限定条件最常用的列，并以此类推第二列……。
5.小于5M的表，最好不要使用索引来查询，表越小，越适合用全表扫描。

六、使用索引的原则
1.查询结果是所有数据行的5%以下时，使用index查询效果最好；
2. where条件中经常用到表的多列时，使用复合索引效果会好于几个单列索引。因为当sql 语句所查询的列，全部都出现在复合索引中时，此时由于 Oracle 只需要查询索引块即可获得所有数据，当然比使用多个单列索引要快得多；
3.索引利于select，但对经常insert，delte尤其update的表，会降低效率。
例如：试比较下面两条SQL语句(emp 表的deptno列上建有ununique index)：
语句A：SELECT dname, deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp);
语句B：SELECT dname, deptno FROM dept WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);
这两条查询语句实现的结果是相同的，但是执行语句A的时候，ORACLE会对整个emp表进行扫描，没有使用建立在emp表上的deptno索引，执行语句B的时候，由于在子查询中使用了联合查询，ORACLE只是对emp表进行的部分数据扫描，并利用了deptno列的索引，所以语句B的效率要比语句A的效率高。
3. where 子句中的这个字段，必须是复合索引的第一个字段；
例如：一个索引是按f1, f2, f3的次序建立的，若where 子句是f2 = : var2, 则因为f2 不是索引的第1个字段，无法使用该索引。
4. where 子句中的这个字段，不应该参与任何形式的计算：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
5.应尽量熟悉各种操作符对 Oracle 是否使用索引的影响：以下这些操作会显式（explicitly）地阻止 Oracle 使用索引：
is null ; is not null ; not in; !=; like ; numeric_col+0;date_col+0; char_col||‘ ’;
to_char; to_number，to_date 等。
例如： Select jobid from mytabs where isReq=‘0’ and to_date (updatedate) >= to_Date ( ‘2001-7-18’, ‘YYYY-MM-DD’)；–updatedate列的索引也不会生效。

七、创建索引
create index abc on student(sid,sname);
create index abc1 on student(sname,sid);
这两种索引方式是不一样的
索引abc对Select * from student where sid=1; 这样的查询语句更有效
索引abc1对Select * from student where sname=‟louis‟; 这样的查询语句更有效
因此建立索引的时候，字段的组合顺序是非常重要的。一般情况下，需要经常访问的字段放在组合字段的前面

八、索引的存储
索引和表都是独立存在的。在为索引指定表空间的时候，不要将被索引的表和索引指向同一个表空间，这样可以避免产生IO冲突。使Oracle能够并行访问存放在不同硬盘中的索引数据和表数据，更好的提高查询速度。
八、删除索引
drop index PK_DEPT1;
九、索引类型
B树索引（B-Tree Index）
创建索引的默认类型，结构是一颗树，采用的是平衡B树算法：
右子树节点的键值大于等于父节点的键值
左子树节点的键值小于等于父节点的键值
比如有数据:100,101,102,103,104,105,106
位图索引(BitMap Index)
如果表中的某些字段取值范围比较小，比如职员性别、分数列ABC级等。只有两个值。这样的字段如果建B树索引没有意义，不能提高检索速度。这时我们推荐用位图索引
Create BitMap Index student on(sex);

一、管理索引
1）先插入数据后创建索引
向表中插入大量数据之前最好不要先创建索引，因为如果先建立索引。那么在插入每行数据的时候都要更改索引。这样会大大降低插入数据的速度；
2）设置合理的索引列顺序 ；
3）限制每个表索引的数量 ；
4）删除不必要的索引；
5）为每个索引指定表空间；
6）经常做insert，delete尤其是update的表最好定期exp/imp表数据，整理数据，降低碎片；有索引的最好定期rebuild索引（rebuild期间只允许表的select操作，可在数据库较空闲时间提交），以降低索引碎片，提高效率 。