oracle数据库之索引index

oracle数据库之索引index

1. 什么是索引
   在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
   索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。

2. 索引类型有哪些
   根据数据库的功能，可以在数据库设计器中创建4三种索引：
   主键索引、唯一索引、普通索引、全文索引、空间索引。
   主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
   唯一索引unique：加速查询 + 列值唯一（可以有null）
   普通索引normal：仅加速查询
   全文索引fulltext：对文本的内容进行分词，进行搜索
   空间索引spatial：空间索引。
   【单列索引：单个列上面建的索引
   联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于多个单列索引合并】

3. oracle索引方法有哪些
   oracle目前主要有下边几种索引方法：
   B-TREE：B-树索引，就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引方法。
   HASH：由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索 引，但效率不高。
   位图索引(bitmap index)：位图索引特定于该列只有几个枚举值的情况，比如性别字段，标示字段比如只有0和1的情况。
   分区索引和全局索引：这2个是用于分区表的时候。前者是分区内索引，后者是全表索引
   基于函数的索引：比如经常对某个字段做查询的时候是带函数操作的，那么此时建一个函数索引就有价值了。
   反向索引（REVERSE）：这个索引不常见，但是特定情况特别有效，比如一个varchar(5)位字段(员工编号)含值

4. 什么情况需要建立索引（索引是建立在数据库表中的某些列的上面的）
   在主键、唯一键、外键的列上；
   在经常需要搜索的列上，也就是where条件后的；
   在经常需要排序的列上，也就是order by后的；
   在经常需要分组的列上，也就是group by后的；
   在经常需要根据范围进行搜索的列上，因为索引已经排序，其指定的范围是连续的；

5. 什么情况不需要建立索引
   在查询中很少使用的列；
   在查询中参考的列不应该创建索引,(比如name名字)；
   对于那些只有很少数据值的列也不应该增加索引,(比如age年龄、sex性别)；
   当修改操作远远多于检索操作时，这个列不应该创建索引；
   对于那些定义为text, image和bit数据类型的列不应该增加索引；

6. 索引的优点缺点分别是
   优点：
   1.大大加快数据的检索速度;
   2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
   3.加速表和表之间的连接;
   4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
   缺点：
   1.索引需要占物理空间。
   2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

7. 什么情况索引会失效
   1.如果条件中有or，即使其中有条件带索引，只要有其他条件不是索引的会失效(这也是为什么尽量少用or的原因，解决办法是把or的所有条件都建一个索引)
   比如：select * from Student where id=‘XXX’ or name=’;其中id是索引，name不是索引，会失效。
   2.对于联合索引，不是使用的第一部分，索引会失效（少查不会失效，跳空查询会失效）
   3.like查询是以%开头的，会失效
   比如：select * from Student where id like ‘%XXX’ ;其中id是索引，会失效。
   4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引。
   比如：select * from Student where id =1008; 其中id是索引，会失效。
   比如：select * from Student where id =‘1008’ ;其中id是索引，不会失效。

8. 创建索引语句
   单独创建索引：
   例如create UNIQUE INDEX 索引的名字 ON 表名(表的某一列列名)；
   例如create INDEX 索引的名字 ON 表名(表的某一列列名)；
   例如create index 索引的名字 on 表名(列名1,列名2);
   创建表的时候指定索引：
   例如CREATE TABLE tablename ( […], UNIQUE 索引的名字 (表的某一列列名) )；
   修改表：
   例如ALTER TABLE 表名 ADD UNIQUE 索引的名字 (表的某一列列名)；

9. 修改索引语句
   alter index 旧索引名字 rename to 新索引名

10. 删除索引语句
    drop index 索引名

11. 查询索引的语句
    select * from user_indexs；(当前用户下所有索引)
    select * from user_indexs where table_name=‘XXX’; (查询某个表的所有索引，表名要大写)
    select * from user_indexs where index_name=‘XXX’;(查询具体的索引，索引名要大写)

12. 注意事项
    考虑已在表上创建的索引数量。最好避免在单个表上有很多索引。
    建议单张表，包含唯一索引在内最好不要超过3个索引。