数据库索引

          index/indexes是索引的意思不是约束！  

         就像根据书的目录去快速定位到想看的章节一样，这种方式的查找在数据库中成为索引扫描，而一页一页的去查找书中某内容则成为全表扫描。除非查找的数据占数据库很大比例，否则不要使用全表扫描。虽然索引项和列的值是一一对应的，但也不是一次就能查出来的，它是类似二分查找法的原理，只是通过快速排除查找范围而减少了查找的次数。数据库的索引多用B-树和B+树来实现，B-树是在二叉排序树的基础上，把每一个节点的分支从两个分支扩展成了多个分支，每个节点上的关键字的数量是子树的数量减一。比如一个根节点有三个子树，根节点的关键字个数是2，查找的时候，先根据节点上关键字的大小来确定在哪颗子树上找，比如1、3、5、7、9五个数分别在三颗子树上，由于B-树是有序树，数值从小到大排列，关键字是4和8，你自然知道怎么查找了。B+树和B-树很类似，唯一的不同是两点：B+树的节点上关键字的个数和子树的数量相等，第二点是所有叶子节点从小到大用指针相连，也就是说B+树可以有两种查找方式。

           索引和主键外键的关系：

           数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为表的主键。在另一张表中跟主键相同的属性便是外键了。

          主外键共同来表达表与表之间的关系，而索引是用来加速查询的，前者表明表之间的关系，后者加速查询，根本不是一个概念。

          

           网上有资料说主键不应该有实际意义，没有实际意义还怎么建立外键？？

           索引的三种分类：普通索引，唯一索引(还有个主键索引它是特殊的唯一索引)，聚集索引(在Oracle中没有聚集索引有个和聚集索引类似的IOT索引)。

            主键索引：将表中某列设置为主键后，通常数据库系统会自动为主键创建主键索引，主键索引就是唯一索引，只是列是特殊的列——主键而已。

            唯一索引：唯一索引表明每个索引只对应唯一的索引值。单列唯一索引时该列不含重复值，多列唯一索引时，多列的组合不含重复值。

            聚集索引确定表中行的物理顺序，行的顺序与逻辑索引顺序相同(跟查出的某列的记录的值的大小无关，对应于B+树上索引顺序查出的值可以相同，也可以先小后大或先大后小)。因此一个表中只能有一个聚集索引，但一个聚集索引可以包含多个列(组合索引)。

           聚集索引和非聚集索引的本质区别就是表记录的物理位置的排列顺序和索引排列顺序是否一致。

      

           使用索引的注意事项：

             主键(会自动加)

           经常用于查询的列：外键和出现在where子句中用来查询的列

            关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。     对于经常存取的列避免建立索引 

建立会怎么样？
 

           建立索引的语句：如果where子句有两个字段怎么建索引？

          

　1.41 创建索引的语法：

CREATE [UNIQUE][CLUSTERED | NONCLUSTERED]  INDEX  index_name  

ON {table_name | view_name} [WITH [index_property [,....n]]

说明：

UNIQUE: 建立唯一索引。

CLUSTERED: 建立聚集索引。

NONCLUSTERED: 建立非聚集索引。

Index_property: 索引属性。

 UNIQUE索引既可以采用聚集索引结构，也可以采用非聚集索引的结构，如果不指明采用的索引结构，则SQL Server系统默认为采用非聚集索引结构。

1.42 删除索引语法：

DROP INDEX table_name.index_name[,table_name.index_name]

说明：table_name: 索引所在的表名称。

index_name : 要删除的索引名称。

1.43 显示索引信息：

使用系统存储过程：sp_helpindex 查看指定表的索引信息。

执行代码如下：

Exec sp_helpindex book1;

索引失效的情况及原因？

1.对索引使用函数    解决办法，建立索引时将索引建为函数索引

2.索引参与加减乘除等运算，将运算放在操作符(=,<,>)后面  

3.隐式转换

表的字段tu_mdn定义为varchar2(20), 
但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效. 
错误的例子：select * from test where tu_mdn=13333333333; 
正确的例子：select * from test where tu_mdn='13333333333'; 

4.like  '%...'百分号在前会失效，尖括号<>会失效,直接同=或！=与null比较会失效，可以用is null或is not null或者使用ISNULL函数(防止b-tree树实现的索引不支持is null)

 

 informix，oracle创建普通索引

create index indexTest on prpaccountinfo (inserttime)

 informix，oracle删除索引

drop  index  indexName

创建复合索引
create index in_test1 on test (a,b,c) 

 

 

本帖最后由 bfc99 于 2015-12-14 10:22 编辑

假设表A，有5个字段，其中有一个名为ID的字段为主键。日常业务中，经常需要根据ID字段的值，查找该表上其它字段（一个或多个字段，甚至全部字段）上的值。如果我们只在ID列上创建索引，当执行
select id,字段2，字段3，...
from 表A
where id=(>,<,>=,<=等逻辑操作）xxx;
时，如果结果集是少数记录，那么极可能是通过ID列上的索引获取ROWID，然后回表去读取相应ROWID的行记录，以获取其它字段上的信息。如果这种业务很频繁，为了这个回表的操作就可能带来不小的性能提升。所以，我们很自然的会想到用复合索引，把ID列以外的其它列都编进去，这样就不需要回表了，因为索引中这些列都已经有了。但这样一来，索引的大小几乎与表一样大了，相当于在数据库里同时拥有两张表A，只不过一个是根据ID排了序的，一个是未排序的。这时，如果我们用索引组织表，那么数据库就会以索引的方式来保存表A，数据库中只有一张表的空间使用，不仅节省了空间，而且查询的性能上与创建复合索引的方法有近似的提升。
但是，索引组织表也有弱点，由于这种表是以索引的方式来存储表中的数据，所以，索引叶子块中会保存相应行中所有字段的值。若这些字段的长度累计较大时，每个叶子块可以存储的行数就会很少，就会需要更多的叶子块来存储行记录，而叶子块越多，索引的深度（或层级）就越高，每次访问时需要访问的分支块就越多，即一次访问需要访问的总的数据块也就越多。当过多时，就会把消除回表操作所带来的好处抵消，基至有过之而无不及，性能反而会更差。同时，如果索引组织表的主键值经常更改，意味着相应的行就需要在不同的块之间进行移动。如果索引组织表频繁进行增删改，也会像索引那样，引起索引结构的维护工作（比如分支块的分裂，索引层级的增加，以及索引碎片的出现等）增加，也会引起性能的下降。

综上，一般情况下，只有在满足以下条件时，才应该考虑使用索引组织表：
1、表的宽度（即一行的数据长度）有限。
2、表的主键不会或极少更改。
3、表主要用于查询，DML操作较少。
4、大部分的业务需求是根据主键查询行中其它列上的信息。

        SQL SERVER的聚集索引可以完全想象成ORACLE的IOT表，它们的原理是一样的。它和IOT表的唯一不同是，IOT是根据主键来排序的，但聚集索引可以用任何一个字段来排序，这个字段可以有重复值。这是唯一的不同。
另外，聚集索引和普通索引的不同，我们用下面一个小例子来说明：

    一个入库单表，对日期字段建立聚集索引和普通索引，看它们的区别：
    假设索引有4层，我下SQL想查询2008.6.1-2008.6.15日的入库单，一共有10笔符合条件，
    对于普通索引，它会先根据索引找到2008.6.1的第1笔记录，此时I/O量是4个块，然后记下这10笔记录的指针，再去表中查找每条记录，每条记录的I/O量都是1个块，一共是14个块的I/O量。
    对于聚集索引，它会先根据索引找到2008.6.1的第1笔记录，因为叶子即是表，SQL就能获得其他字段了，然后顺序向下读，后面这10条符合条件的记录都在同一个块上，所以整个I/O量只有4个块。
    这就是聚集索引和普通索引的差别。

       

 

使用and或or时索引失效或效率？explain