漫谈索引

[1].漫谈数据库索引

 

一、索引简介
1、索引相当于目录
2、索引是通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率。
3、索引的创建要适度，多了会影响增删改的效率，少了会影响查询的效率，索引最好创建在取值分散的列上，避免对同一张表创建过多的索引
4、索引的使用对用户来说是透明的，由系统来决定什么时候使用索引。
5、Oracle支持多种类型的索引，可以按列的多少、索引值是否唯一和索引数据的组织形式对索引进行分类，以满足各种表和查询条件的要求。（请见附件）
a. 单列索引和复合索引
    b．B树索引（create index时默认的类型）
 B树索引中所有叶子节点都具有相同的深度，所以不管查询条件如何，查询速度基本相同。另B树索引能够适应各种查询条件，包括精确查询、模糊查询和比较查询
      --Unique 唯一索引 值唯一，但允许存在null，主键默认存在唯一索引，但列不能为null
  --Non-Unique：非唯一索引，其索引值可以重复，允许为NULL。默认情况下，Oracle创建的索引是非唯一索引
          --Reverse Key：反向关键字索引。通过在创建索引时指定“REVERSE”关键字，可以创建反向关键字索引，被索引的每个数据列中的数据都是反向存储
                         的，但仍然保持原来数据列的次序
c．位图索引（对列值范围少，如性别 政治面貌适用，而不是默认的B树索引）
    c．函数索引
 当需要经常访问一些函数或表达式时，可以将其存储在索引中，当下次访问时，由于该值已经计算出来了，因此，可以大大提高那些在WHERE子句中包含该函数或
   表达式的查询操作的速度；
   函数索引既可以使用B树索引，也可以使用位图索引。
二、管理索引的原则
使用索引应该遵循以下一些基本的原则。
1．小表不需要建立索引。
2．对于大表而言，如果经常查询的记录数目少于表中总记录数目的15%时，可以创建索引。这个比例并不绝对，它与全表扫描速度成反比。
3．对于大部分列值不重复的列可建立索引。
4．对于基数大的列，适合建立B树索引，而对于基数小的列适合建立位图索引。
5．对于列中有许多空值，但经常查询所有的非空值记录的列，应该建立索引。
6．LONG和LONG RAW列不能创建索引。
7．经常进行连接查询的列上应该创建索引。
8．在使用CREATE INDEX语句创建查询时，将最常查询的列放在其他列前面。
9．维护索引需要开销，特别时对表进行插入和删除操作时，因此要限制表中索引的数量。对于主要用于读的表，则索引多就有好处，但是，一个表如果经常被更改，则索引应少点。
10．在表中插入数据后创建索引。如果在装载数据之前创建了索引，那么当插入每行时，Oracle都必须更改每个索引。
三、LONG类型（可存储2G）主要用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型，存储这么长的请使用 pstat1.setCharacterStream()方法，资料见附件P26 
四、创建索引语法如下
CREATE INDEX语句的语法如下：
CREATE [UNIQUE] | [BITMAP] INDEX index_name
ON table_name([column1 [ASC|DESC],column2
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]
[STORAGE (INITIAL n2)]
[NOLOGGING]
[NOLINE]
[NOSORT];

五、
1、查看索引信息可以在 all_indexs 表中
2、查看索引信息及引用的列 all_ind_columns
3、查看函数索引信息  all_ind_expressions

4、oracle比较智能，有时候即使创建了索引也不会使用，比如说在数据量比较少的情况下，可能就不会用索引
5、当进行全表扫描的时候，不用索引效率会更好
6、查询可能会使用缓存，所以说如果发现执行速度变快了，不一定说明你的sql更优了，有可能是使用到了缓存而已
7、使用plsql中的“解释计划”功能可以比较执行计划的消耗，进而写出更优的sql

 

注意  如果某列有多行包含 NULL 值，则不能在该列上创建唯一索引。同样，如果列的组合中有多行包含 NULL 值，则不能在多个列上创建唯一索引。在创建索引时，这些被视为重复的值。 

 

 

1.创建表并插入数据

在Sql Server2008中创建测试数据库Test，接着创建数据库表并插入数据，sql代码如下：

USE Test
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_NAME = 'emp_pay')
   DROP TABLE emp_pay
GO
USE Test
IF EXISTS (SELECT name FROM sys.indexes 
      WHERE name = 'employeeID_ind')
   DROP INDEX emp_pay.employeeID_ind
GO
USE Test
GO
CREATE TABLE emp_pay
(
 employeeID int NOT NULL,
 base_pay money NOT NULL,
 commission decimal(2, 2) NOT NULL
)
INSERT emp_pay
   VALUES (1, 500, .10)
INSERT emp_pay 
   VALUES (2, 1000, .05)
INSERT emp_pay 
   VALUES (6, 800, .07)
INSERT emp_pay
   VALUES (5, 1500, .03)
INSERT emp_pay
   VALUES (9, 750, .06)

执行完上述sql代码以后我们会发现在Test数据库中多出了一张emp_pay表，数据库表的内容如下图所示：

2.无索引查找

从上图我们可以看出数据库中存储的数据排列顺序与我们插入的先后顺序一致。接下来我们查询employeeID=5的字段，执行如下sql代码：

USE Test
SELECT * FROM emp_pay where employeeID=5

在SQL SERVER MANAGEMENT STUDIO中我们点击“显示估计的查询计划”，会出现如下图所示的查询计划图：

其中表扫描的内容为：

 

1.6  限制索引 
限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL 中有很多陷阱会使一些索引无法使用。下面讨论一些常见的问题： 
     1.6.1   使用不等于操作符（<> 、 != ）        
     下面的查询即使在cust_rating 列有一个索引，查询语句仍然执行一次全表扫描。       
   select cust_Id,cust_name from customers where  cust_rating <> 'aa';         
把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。         
  select cust_Id,cust_name from customers where cust_rating < 'aa' or cust_rating > 'aa'; 
  特别注意：通过把不等于操作符改成 OR 条件，就可以使用索引，以避免全表扫描。 
     1.6. 2 使用 IS NULL  或 IS NOT NULL 
   使用 IS NULL  或 IS NOT NULL 同样会限制索引的使用 。因为 NULL 值并没有被定义。在 SQL 语句中使用NULL 会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成  NOT NULL 。如果被索引的列在某些行中存在 NULL 值，就不会使用这个索引（除非索引是一个位图索引，关于位图索引在稍后在详细讨论）。 
    1.6 .3 使用函数 
   如果不使用基于函数的索引，那么在SQL 语句的 WHERE 子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。 

  下面的查询不会使用索引（只要它不是基于函数的索引） 
 select empno,ename,deptno from emp  where  trunc(hiredate)='01-MAY-81'; 
   把上面的语句改成下面的语句，这样就可以通过索引进行查找。 
select empno,ename,deptno from emp where  hiredate<(to_date('01-MAY-81')+0.9999);

 

3.创建索引

接下来我们为上述表添加聚集唯一索引，代码如下：

SET NOCOUNT OFF
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
   ON emp_pay (employeeID)
GO

在执行完上述创建索引的代码以后，我们再次查询emp_pay的数据内容，如下图所示：

从上图我们可以发现数据内容已经按照employeeID进行了排序。

我们继续执行前面关于employeeID=5的查询，点击“显示估计的执行计划”，出现如下图所示内容：

聚集索引查找的内容为：

总结：

当我们为数据库表中的某一个字段创建索引，并且在查询语句中where子句中用到这样一个字段，那么查询效率会有所提高，我们上述实验因为数据量的关系查询效率提高不明显。

补充

我们上面添加的索引是唯一聚集索引，因此当插入的数据在employeeID字段出现重复时会报错。假如我们在创建索引之前数据字段出现重复，那么就不能创建唯一索引。

创建索引以后的排序（PS:2012-5-28）

执行如下sql语句

update emp_pay set employeeID=7 where employeeID=1;

然后再次执行全表查询，我们发现查询结果如下所示：

只要我们更新了employeeID，那么最后的更新结果都会按照employeeID的值进行升序排序。这是因为我们在employeeID上创建了索引的缘故。

删除索引（PS:2012-6-4）

我们可以通过sql server management studio这个工具删除索引，也可以通过sql语句进行索引的删除，假设我们要求删除在前面创建的索引employeeID_ind，那么sql语句如下代码所示：

DROP INDEX employeeID_ind ON emp_pay;