数据库索引机制

mysql效率提升 http://qgjie456.blog.163.com/blog/static/3545136720121145450840/

数据库索引 http://www.cnblogs.com/king1302217/archive/2010/05/11/1732250.html

建立索引提高数据库查询的原理 http://blog.chinaunix.net/uid-10673338-id-2936909.html

Primary key、unique、index之间的关系 http://www.cnblogs.com/xwdreamer/archive/2012/07/19/2598755.html

建立索引提高数据库查询速度的原理 2007-11-19 09:25:42

分类： 数据库开发技术

索引是提高数据查询最有效的方法，也是最难全面掌握的技术，因为正确的索引可能使效率提高10000倍，而无效的索引可能是浪费了数据库空间，甚至大大降低查询性能。

 

索引的管理成本

1、  存储索引的磁盘空间

2、  执行数据修改操作（INSERT、UPDATE、DELETE）产生的索引维护

3、  在数据处理时回需额外的回退空间。

 

实际数据修改测试：

一个表有字段A、B、C，同时进行插入10000行记录测试

在没有建索引时平均完成时间是2.9秒

在对A字段建索引后平均完成时间是6.7秒

在对A字段和B字段建索引后平均完成时间是10.3秒

在对A字段、B字段和C字段都建索引后平均完成时间是11.7秒

从以上测试结果可以明显看出索引对数据修改产生的影响

 

索引按存储方法分类

B*树索引

B*树索引是最常用的索引，其存储结构类似书的索引结构，有分支和叶两种类型的存储数据块，分支块相当于书的大目录，叶块相当于索引到的具体的书页。一般索引及唯一约束索引都使用B*树索引。

位图索引

位图索引储存主要用来节省空间，减少DB2对数据块的访问，它采用位图偏移方式来与表的行ID号对应，采用位图索引一般是重复值太多的表字段。位图索引在实际密集型OLTP（数据事务处理）中用得比较少，因为OLTP会对表进行大量的删除、修改、新建操作，ORACLE每次进行操作都会对要操作的数据块加锁，所以多人操作很容易产生数据块锁等待甚至死锁现象。在OLAP（数据分析处理）中应用位图有优势，因为OLAP中大部分是对数据库的查询操作，而且一般采用数据仓库技术，所以大量数据采用位图索引节省空间比较明显。

 

索引按功能分类

唯一索引

唯一索引有两个作用，一个是数据约束，一个是数据索引，其中数据约束主要用来保证数据的完整性，唯一索引产生的索引记录中每一条记录都对应一个唯一的ROWID。

 

主关键字索引

主关键字索引产生的索引同唯一索引，只不过它是在数据库建立主关键字时系统自动建立的。

一般索引

一般索引不产生数据约束作用，其功能主要是对字段建立索引表，以提高数据查询速度。

 

 

索引按索引对象分类

 

单列索引（表单个字段的索引）

多列索引（表多个字段的索引）

函数索引（对字段进行函数运算的索引）

建立函数索引的方法：

create index 收费日期索引 on GC_DFSS(trunc(sk_rq))

create index 完全客户编号索引 on yhzl(qc_bh||kh_bh)

在对函数进行了索引后，如果当前会话要引用应设置当前会话的query_rewrite_enabled为TRUE。

alter session set query_rewrite_enabled=true

注：如果对用户函数进行索引的话，那用户函数应加上 deterministic参数，意思是函数在输入值固定的情况下返回值也固定。例：

create or replace function trunc_add（input_date date）return date deterministic

as

begin

    return trunc(input_date+1);

end trunc_add;

 

应用索引的扫描分类

INDEX UNIQUE SCAN（按索引唯一值扫描）

select * from zl_yhjbqk where hbs_bh='5420016000'

INDEX RANGE SCAN（按索引值范围扫描）

select * from zl_yhjbqk where hbs_bh>'5420016000'

select * from zl_yhjbqk where qc_bh>'7001'

INDEX FAST FULL SCAN（按索引值快速全部扫描）

select hbs_bh from zl_yhjbqk order by hbs_bh

select count(*) from zl_yhjbqk

select qc_bh from zl_yhjbqk group by qc_bh

 

什么情况下应该建立索引

表的主关键字

自动建立唯一索引

如zl_yhjbqk（用户基本情况）中的hbs_bh（户标识编号）

表的字段唯一约束

ORACLE利用索引来保证数据的完整性

如lc_hj（流程环节）中的lc_bh+hj_sx（流程编号+环节顺序）

直接条件查询的字段

在SQL中用于条件约束的字段

如zl_yhjbqk（用户基本情况）中的qc_bh（区册编号）

select * from zl_yhjbqk where qc_bh=’7001’

查询中与其它表关联的字段

字段常常建立了外键关系

如zl_ydcf（用电成份）中的jldb_bh（计量点表编号）

select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’

 

查询中排序的字段

排序的字段如果通过索引去访问那将大大提高排序速度

select * from zl_yhjbqk order by qc_bh（建立qc_bh索引）

select * from zl_yhjbqk where qc_bh='7001' order by cb_sx（建立qc_bh+cb_sx索引，注：只是一个索引，其中包括qc_bh和cb_sx字段）

查询中统计或分组统计的字段

select max(hbs_bh) from zl_yhjbqk

select qc_bh,count(*) from zl_yhjbqk group by qc_bh

 

什么情况下应不建或少建索引

表记录太少

如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块，这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出，处理速度显然会比用索引快。

如表zl_sybm（使用部门）一般只有几条记录，除了主关键字外对任何一个字段建索引都不会产生性能优化，实际上如果对这个表进行了统计分析后ORACLE也不会用你建的索引，而是自动执行全表访问。如：

select * from zl_sybm where sydw_bh='5401'（对sydw_bh建立索引不会产生性能优化）

 

经常插入、删除、修改的表

对一些经常处理的业务表应在查询允许的情况下尽量减少索引，如zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等业务表。

 

数据重复且分布平均的表字段

假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。

 

经常和主字段一块查询但主字段索引值比较多的表字段

如gc_dfss（电费实收）表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作标志来具体查询某一笔收款的情况，如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间，从实际上分析一笔收款如果按收费序号索引就已经将记录减少到只有几条，如果再按后面的几个字段索引查询将对性能不产生太大的影响。

 

如何只通过索引返回结果

一个索引一般包括单个或多个字段，如果能不访问表直接应用索引就返回结果那将大大提高数据库查询的性能。对比以下三个SQL，其中对表zl_yhjbqk的hbs_bh和qc_bh字段建立了索引：

1 select hbs_bh,qc_bh,xh_bz from zl_yhjbqk where qc_bh=’7001’

 

 

执行路径:

SELECT STATEMENT, GOAL = CHOOSE         11  265 5565

 TABLE ACCESS BY INDEX ROWID    DLYX    ZL_YHJBQK   11  265 5565

  INDEX RANGE SCAN  DLYX    区册索引    1   265

平均执行时间（0.078秒）

2 select hbs_bh,qc_bh from zl_yhjbqk where qc_bh=’7001’

执行路径:

SELECT STATEMENT, GOAL = CHOOSE         11  265 3710

 TABLE ACCESS BY INDEX ROWID    DLYX    ZL_YHJBQK   11  265 3710

  INDEX RANGE SCAN  DLYX    区册索引    1   265

平均执行时间（0.078秒）

3 select qc_bh from zl_yhjbqk where qc_bh=’7001’

执行路径:

SELECT STATEMENT, GOAL = CHOOSE         1   265 1060

 INDEX RANGE SCAN   DLYX    区册索引    1   265 1060

平均执行时间（0.062秒）

 

从执行结果可以看出第三条SQL的效率最高。执行路径可以看出第1、2条SQL都多执行了TABLE ACCESS BY INDEX ROWID（通过ROWID访问表） 这个步骤，因为返回的结果列中包括当前使用索引(qc_bh)中未索引的列（hbs_bh,xh_bz），而第3条SQL直接通过QC_BH返回了结果，这就是通过索引直接返回结果的方法。

 

如何重建索引

alter index 表电量结果表主键 rebuild

 

如何快速新建大数据量表的索引

如果一个表的记录达到100万以上的话，要对其中一个字段建索引可能要花很长的时间，甚至导致服务器数据库死机，因为在建索引的时候ORACLE要将索引字段所有的内容取出并进行全面排序，数据量大的话可能导致服务器排序内存不足而引用磁盘交换空间进行，这将严重影响服务器数据库的工作。解决方法是增大数据库启动初始化中的排序内存参数，如果要进行大量的索引修改可以设置10M以上的排序内存（ORACLE缺省大小为64K），在索引建立完成后应将参数修改回来，因为在实际OLTP数据库应用中一般不会用到这么大的排序内存。

Primary key、unique、index之间的关系

参考文献

Difference between an Index and a Primary Key

本质区别

首先primary key跟unique都是Constraints，属于logical object，而index则是physical object，会占用index page并被保存在磁盘上。

Primary key Constraints和unique Constraints

Primary key Constraints和unique Constraints都需要保证列是唯一的，不能有重复值，但是一张表只能有一个Primary key Constraints，但是可以有多个unique Constraints。还有就是Primary key Constraints是非空的，但是unique Constraints允许为空。不过需要注意的是，因为在SQL Server中可以NULL==NULL，所以unique列只允许只有一个NULL值。具体参考UNIQUE Constraints。实例验证如下

use TESTDB3
go
--1.创建表
create table t1
(
    stuid int unique,
    stuname nvarchar(10)
);
go

--2.插入两条记录，有一列是null
insert t1 values(1,'xuwei');
insert t1 values(null,'xuwei');
select * from t1;

--3.在unique约束的列中再次插入null值
insert t1 values(null,'xuwei');

Primary key 和index

When you specify a PRIMARY KEY constraint for a table, the Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. Therefore, the primary keys that are chosen must follow the rules for creating unique indexes. --------PRIMARY KEY Constraints

创建primary key的时候肯定会创建一个unique index。创建unique index 是为了提高保证primary key唯一性的效率 。试想不创建unique index，那么就是堆结构。而主键是要保证唯一的，那么每次插入一条数据都要进行一次table scan，遍历整张表。假如我们有了索引，每次只需要一次index seek就好了。效率大大提高。

unique index的主要实现方式

1. 在创建 PRIMARY KEY 约束时，如果不存在该表的聚集索引且未指定唯一非聚集索引，则将自动对一列或多列创建唯一聚集索引。主键列不允许空值。
2. 在创建 UNIQUE 约束时，默认情况下将创建唯一非聚集索引，以便强制 UNIQUE 约束。如果不存在该表的聚集索引，则可以指定唯一聚集索引。

索引的有序结构

所有索引都是有序的。正是因为有序，查询才能够更快。

非聚集索引的叶子节点保存的是非聚集索引键和bookmark，其中非聚集索引键是有序的。如下图所示：

聚集索引的叶子节点就是数据本身，数据本身按照聚集索引键有序。如下图所示

mysql 效率提升  

在数据库表中，对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表：

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL

);

我们随机向里面插入了10000条记录，其中有一条：5555, admin。

在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时，如果在username上已经建立了索引，MySQL无须任何扫描，即准确可找到该记录。相反，MySQL会扫描所有记录，即要查询10000条记录。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

索引的类型包括：

（1）普通索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：

● 

      创建索引

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

●        修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))

●        创建表的时候直接指定

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

INDEX [indexName] (username(length))

);

删除索引的语法：

DROP INDEX [indexName] ON mytable;

（2）唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

●        创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

●        修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

●        创建表的时候直接指定

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

UNIQUE [indexName] (username(length))

);

（3）主键索引
它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

PRIMARY KEY(ID)

);

当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

（4）组合索引
为了形象地对比单列索引和组合索引，为表添加多个字段：

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

city VARCHAR(50) NOT NULL,

age INT NOT NULL

);

为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

usernname,city,age

usernname,city

usernname

为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：

SELECT * FROM mytable WHREE username="admin" AND city="郑州"

SELECT * FROM mytable WHREE username="admin"

而下面几个则不会用到：

SELECT * FROM mytable WHREE age=20 AND city="郑州"

SELECT * FROM mytable WHREE city="郑州"

（5）建立索引的时机
到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢？一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为 MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：

SELECT t.Name

FROM mytable t LEFT JOIN mytable m

ON t.Name=m.username WHERE m.age=20 AND m.city='郑州'

此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：

SELECT * FROM mytable WHERE username like'admin%'

而下句就不会使用：

SELECT * FROM mytable WHEREt Name like'%admin'

因此，在使用LIKE时应注意以上的区别。

（6）索引的不足之处
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

●        虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

●        建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

（7）使用索引的注意事项
使用索引时，有以下一些技巧和注意事项：

●        索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

●        使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

●        索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

●        like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

●        不要在列上进行运算

select * from users where YEAR(adddate)<2007;将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成select * from users where adddate<‘2007-01-01’;

●        不使用NOT IN和<>操作

NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以使用NOT EXISTS代替，id<>3则可以使用id>3 or id<3来代替。

数据库索引

一、索引的概念
        索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

 

二、索引的特点
    1.索引可以加快数据库的检索速度
    2.索引降低了数据库插入、修改、删除等维护任务的速度
    3.索引创建在表上，不能创建在视图上
    4.索引既可以直接创建，也可以间接创建
    5.可以在优化隐藏中，使用索引
    6.使用查询处理器执行SQL语句，在一个表上，一次只能使用一个索引
    7.其他

 

三、索引的优点
    1.创建唯一性索引，保证数据库表中每一行数据的唯一性
    2.大大加快数据的检索速度，这也是创建索引的最主要的原因
    3.加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
    4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
    5.通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。

 

四、索引的缺点
    1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
    2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
    3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

 

五、索引分类
    1.直接创建索引和间接创建索引
    直接创建索引： CREATE INDEX mycolumn_index ON mytable (myclumn)
    间接创建索引：定义主键约束或者唯一性键约束，可以间接创建索引
    2.普通索引和唯一性索引
    普通索引：CREATE INDEX mycolumn_index ON mytable (myclumn)
    唯一性索引：保证在索引列中的全部数据是唯一的，对聚簇索引和非聚簇索引都可以使用
    CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)
    3.单个索引和复合索引
    单个索引：即非复合索引
    复合索引：又叫组合索引，在索引建立语句中同时包含多个字段名，最多16个字段
    CREATE INDEX name_index ON username(firstname,lastname)
    4.聚簇索引和非聚簇索引(聚集索引，群集索引)
   聚簇索引：物理索引，与基表的物理顺序相同，数据值的顺序总是按照顺序排列
    CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH
    ALLOW_DUP_ROW(允许有重复记录的聚簇索引)
   非聚簇索引：CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)

 

/

 

2.索引的类型
有两种基本的索引结构，也就是索引文件的保存方式，一个是顺序索引，就是根据值的顺序排序的（这个文件里面的值，也就是为其建索引的字段值，是顺序的放在索引文件里面），另外一个是散列索引，就是将值平均分配到若干散列桶中，通过散列函数定位的。

 

2.1.顺序索引

顺序索引下面又有很多概念。
如果被索引的字段本身按照一定的顺序排序，那么这种索引叫做聚集索引。否则叫做非聚集索引。如果被索引的字段的每个值都有一个索引与其对应，那么这种索引叫做稠密索引，否则叫做稀疏索引。
      顺序索引分为两类，单级索引（不怎么用）和多级索引（通常是B+树，大量使用）。
      单级索引就是把所有的索引字段以及对应的文件位置按顺序一个个的排列出来，这种索引查找起来比较慢，因为是顺序存储的，可以使用二分查找法，但是总体来说效率不高，这种索引是最基础的索引，一般不用，ORACLE里面好像不支持这种索引。
     多级索引实际上就是在单级索引之上再加索引（稀疏索引），也就是指向索引的索引，二级索引上面还可以再加三级索引，可以不停的加，加到最后最上层只剩下一个节点（根节点），就成了一个树状结构了。
     我们经常听到B+树就是这个概念，用这个树的目的和红黑树差不多，也是为了尽量保持树的平衡，当然红黑树是二叉树，但B+树就不是二叉树了，节点下面可以有多个子节点，数据库开发商会设置子节点数的一个最大值，这个值不会太小，所以B+树一般来说比较矮胖，而红黑树就比较瘦高了。
关于B+树的插入，删除，会涉及到一些算法以保持树的平衡，这里就不详述了。ORACLE的默认索引就是这种结构的。
      如果经常需要同时对两个字段进行AND查询,那么使用两个单独索引不如建立一个复合索引，因为两个单独索引通常数据库只能使用其中一个，而使用复合索引因为索引本身就对应到两个字段上的，效率会有很大提高。

 2.2 散列索引
第二种索引叫做散列索引，就是通过散列函数来定位的一种索引，不过很少有单独使用散列索引的，反而是散列文件组织用的比较多。
散列文件组织就是根据一个键通过散列计算把对应的记录都放到同一个槽中，这样的话相同的键值对应的记录就一定是放在同一个文件里了，也就减少了文件读取的次数，提高了效率。
散列索引呢就是根据对应键的散列码来找到最终的索引项的技术，其实和B树就差不多了，也就是一种索引之上的二级辅助索引，我理解散列索引都是二级或更高级的稀疏索引，否则桶就太多了，效率也不会很高。

 

 2.3 位图索引
位图索引是一种针对多个字段的简单查询设计一种特殊的索引，适用范围比较小，只适用于字段值固定并且值的种类很少的情况，比如性别，只能有男和女，或者级别，状态等等，并且只有在同时对多个这样的字段查询时才能体现出位图的优势。
位图的基本思想就是对每一个条件都用0或者1来表示，如有5条记录，性别分别是男，女，男，男，女，那么如果使用位图索引就会建立两个位图，对应男的10110和对应女的01001,这样做有什么好处呢，就是如果同时对多个这种类型的字段进行and或or查询时，可以使用按位与和按位或来直接得到结果了。

总结：
B+树最常用，性能也不差，用于范围查询和单值查询都可以。特别是范围查询，非得用B+树这种顺序的才可以了。
HASH的如果只是对单值查询的话速度会比B+树快一点，但是ORACLE好像不支持HASH索引，只支持HASH表空间。
位图的使用情况很局限，只有很少的情况才能用，一定要确定真正适合使用这种索引才用（值的类型很少并且需要复合查询），否则建立一大堆位图就一点意义都没有了。

 

///

聚集索引和非聚集索引

 其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an

”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”

开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张

”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。

我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

 

如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方

法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首

目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页

码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分

别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引

中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。

我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

 

通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。

进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。

（二）何时使用聚集索引或非聚集索引

下面的表总结了何时使用聚集索引或非聚集索引（很重要）。

动作描述
使用聚集索引
使用非聚集索引

列经常被分组排序
应
应

返回某范围内的数据
应
不应

一个或极少不同值
不应
不应

小数目的不同值
应
不应

大数目的不同值
不应
应

频繁更新的列
不应
应

外键列
应
应

主键列
应
应

频繁修改索引列
不应
应

事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，

恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正

文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项

数据对应的页码，然后再根据页码查到具体内容。

（三）结合实际，谈索引使用的误区

理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引，但在实践中以上规则却很容易被忽视或不能根据实际情况进行综

合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区，以便于大家掌握索引建立的方法。

1、主键就是聚集索引

这种想法笔者认为是极端错误的，是对聚集索引的一种浪费。虽然SQL SERVER默认是在主键上建立聚集索引的。

通常，我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自动增大的，步长一般为1。我们的这个办公自动化的实例中的列

Gid就是如此。此时，如果我们将这个列设为主键，SQL SERVER会将此列默认为聚集索引。这样做有好处，就是可以让您的数据在数据库中按照

ID进行物理排序，但笔者认为这样做意义不大。

显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。

从我们前面谈到的聚集索引的定义我们可以看出，使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。在实

际应用中，因为ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚

集索引成为一种资源浪费。其次，让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则；当然，

这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。

在办公自动化系统中，无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是

“日期”还有用户本身的“用户名”。

通常，办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的where语句可以仅仅限制当前用户尚未签收的情况，但如果您的系

统已建立了很长时间，并且数据量很大，那么，每次每个用户打开首页的时候都进行一次全表扫描，这样做意义是不大的，绝大多数的用户1个

月前的文件都已经浏览过了，这样做只能徒增数据库的开销而已。事实上，我们完全可以让用户打开系统首页时，数据库仅仅查询这个用户近3

个月来未阅览的文件，通过“日期”这个字段来限制表扫描，提高查询速度。如果您的办公自动化系统已经建立的2年，那么您的首页显示速度

理论上将是原来速度8倍，甚至更快。

在这里之所以提到“理论上”三字，是因为如果您的聚集索引还是盲目地建在ID这个主键上时，您的查询速度是没有这么高的，即使您在“日

期”这个字段上建立的索引（非聚合索引）。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现（3个月内的数据为25万条）

：
（1）仅在主键上建立聚集索引，并且不划分时间段：

Select gid,fariqi,neibuyonghu,title from tgongwen

用时：128470毫秒（即：128秒）

（2）在主键上建立聚集索引，在fariq上建立非聚集索引：

select gid,fariqi,neibuyonghu,title from Tgongwen

where fariqi> dateadd(day,-90,getdate())

用时：53763毫秒（54秒）

（3）将聚合索引建立在日期列（fariqi）上：

select gid,fariqi,neibuyonghu,title from Tgongwen

where fariqi> dateadd(day,-90,getdate())

用时：2423毫秒（2秒）

虽然每条语句提取出来的都是25万条数据，各种情况的差异却是巨大的，特别是将聚集索引建立在日期列时的差异。事实上，如果您的数据库

真的有1000万容量的话，把主键建立在ID列上，就像以上的第1、2种情况，在网页上的表现就是超时，根本就无法显示。这也是我摒弃ID列作

为聚集索引的一个最重要的因素。

得出以上速度的方法是：在各个select语句前加：declare @d datetime

set @d=getdate()

并在select语句后加：

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

2、只要建立索引就能显著提高查询速度

事实上，我们可以发现上面的例子中，第2、3条语句完全相同，且建立索引的字段也相同；不同的仅是前者在fariqi字段上建立的是非聚合索

引，后者在此字段上建立的是聚合索引，但查询速度却有着天壤之别。所以，并非是在任何字段上简单地建立索引就能提高查询速度。

从建表的语句中，我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。在现实

中，我们每天都会发几个文件，这几个文件的发文日期就相同，这完全符合建立聚集索引要求的：“既不能绝大多数都相同，又不能只有极少

数相同”的规则。由此看来，我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。

3、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度

上面已经谈到：在进行数据查询时都离不开字段的是“日期”还有用户本身的“用户名”。既然这两个字段都是如此的重要，我们可以把他们

合并起来，建立一个复合索引（compound index）。

很多人认为只要把任何字段加进聚集索引，就能提高查询速度，也有人感到迷惑：如果把复合的聚集索引字段分开查询，那么查询速度会减慢

吗？带着这个问题，我们来看一下以下的查询速度（结果集都是25万条数据）：（日期列fariqi首先排在复合聚集索引的起始列，用户名

neibuyonghu排在后列）

（1）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5'

查询速度：2513毫秒

（2）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5' and neibuyonghu='办公室'

查询速度：2516毫秒

（3）select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu='办公室'

查询速度：60280毫秒

从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用

上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起

任何作用的。当然，语句1、2的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会形成

“索引覆盖”，因而性能可以达到最优。同时，请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。

 

（四）其他书上没有的索引使用经验总结

1、用聚合索引比用不是聚合索引的主键速度快

下面是实例语句：（都是提取25万条数据）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

使用时间：3326毫秒

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000

使用时间：4470毫秒

这里，用聚合索引比用不是聚合索引的主键速度快了近1/4。

2、用聚合索引比用一般的主键作order by时速度快，特别是在小数据量情况下

select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi

用时：12936

select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid

用时：18843

这里，用聚合索引比用一般的主键作order by时，速度快了3/10。事实上，如果数据量很小的话，用聚集索引作为排序列要比使用非聚集索引

速度快得明显的多；而数据量如果很大的话，如10万以上，则二者的速度差别不明显。

3、使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1'

用时：6343毫秒（提取100万条）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-6-6'

用时：3170毫秒（提取50万条）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

用时：3326毫秒（和上句的结果一模一样。如果采集的数量一样，那么用大于号和等于号是一样的）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' and fariqi<'2004-6-6'

用时：3280毫秒

4 、日期列不会因为有分秒的输入而减慢查询速度

下面的例子中，共有100万条数据，2004年1月1日以后的数据有50万条，但只有两个不同的日期，日期精确到日；之前有数据50万条，有5000个

不同的日期，日期精确到秒。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' order by fariqi

用时：6390毫秒

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi<'2004-1-1' order by fariqi

用时：6453毫秒

 / 

Oracle索引介绍如下： 

一、 ROWID的概念

　　存储了row在数据文件中的具体位置：64位编码的数据，A-Z, a-z, 0-9, +, 和 /，

　　row在数据块中的存储方式

　　SELECT ROWID, last_name FROM hr.employees WHERE department_id = 20;

　　比如：OOOOOOFFFBBBBBBRRR

　　OOOOOO：data object number, 对应dba_objects.data_object_id

　　FFF：file#, 对应v$datafile.file#

　　BBBBBB：block#

　　RRR：row#

　　Dbms_rowid包

　　SELECT dbms_rowid.rowid_block_number('AAAGFqAABAAAIWEAAA') from dual;

　　具体到特定的物理文件

　　二、 索引的概念

　　1、 类似书的目录结构

　　2、 Oracle 的“索引”对象，与表关联的可选对象，提高SQL查询语句的速度

　　3、 索引直接指向包含所查询值的行的位置，减少磁盘I/O

　　4、 与所索引的表是相互独立的物理结构

　　5、 Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引

　　6、 语法：CREATE INDEX index ON table (column[, column]...);

　　7、 B-tree结构(非bitmap)：

　　[一]了解索引的工作原理：

　　表：emp

　　目标：查询Frank的工资salary

　　建立索引：create index emp_name_idx on emp(name);

 

 

 

　　[试验]测试索引的作用：

　　1. 运行/rdbms/admin/utlxplan 脚本

　　2. 建立测试表

　　create table t as select * from dba_objects;

　　insert into t select * from t;

　　create table indextable

　　as select rownum id,owner,object_name,subobject_name,

　　object_id,data_object_id,object_type,created

　　from t;

　　3. set autotrace trace explain

　　4. set timing on

　　5. 分析表，可以得到cost

　　6. 查询 object_name=’DBA_INDEXES’

　　7. 在object_name列上建立索引

　　8. 再查询

　　[思考]索引的代价：

　　插入，更新

　　三、 唯一索引

　　1、 何时创建：当某列任意两行的值都不相同

　　2、 当建立Primary Key(主键)或者Unique constraint(唯一约束)时，唯一索引将被自动建立

　　3、 语法：CREATE UNIQUE INDEX index ON table (column);

　　4、 演示

　　四、 组合索引

　　1、 何时创建：当两个或多个列经常一起出现在where条件中时，则在这些列上同时创建组合索引

　　2、 组合索引中列的顺序是任意的，也无需相邻。但是建议将最频繁访问的列放在列表的最前面

　　3、 演示(组合列，单独列)

　　五、 位图索引

　　1、 何时创建：

　　列中有非常多的重复的值时候。例如某列保存了 “性别”信息。

　　Where 条件中包含了很多OR操作符。

　　较少的update操作，因为要相应的跟新所有的bitmap

　　2、 结构：位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE(1)、FALSE(0)、或NULL值。

　　3、 优点：位图以一种压缩格式存放，因此占用的磁盘空间比标准索引要小得多

　　4、 语法：CREATE BITMAP INDEX index ON table (column[, column]...);

　　5、 掩饰：

　　create table bitmaptable as select * from indextable where owner in('SYS','PUBLIC');

　　分析，查找，建立索引，查找

　　六、 基于函数的索引

　　1、 何时创建：在WHERE条件语句中包含函数或者表达式时

　　2、 函数包括：算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数。

　　3、 语法：CREATE INDEX index ON table (FUNCTION(column));

　　4、 演示

　　必须要分析表，并且query_rewrite_enabled=TRUE

　　或者使用提示/*+ INDEX(ic_index)*/

　　七、 反向键索引

　　目的：比如索引值是一个自动增长的列：

 

　　多个用户对集中在少数块上的索引行进行修改，容易引起资源的争用，比如对数据块的等待。此时建立反向索引。

　　性能问题：

　　语法：

　　重建为标准索引：反之不行

　　八、 键压缩索引

　　比如表landscp的数据如下：

　　site feature job

　　Britten Park, Rose Bed 1, Prune

　　Britten Park, Rose Bed 1, Mulch

　　Britten Park, Rose Bed 1,Spray

　　Britten Park, Shrub Bed 1, Mulch 

　　Britten Park, Shrub Bed 1, Weed

　　Britten Park, Shrub Bed 1, Hoe

　　……

　　查询时，以上3列均在where条件中同时出现，所以建立基于以上3列的组合索引。但是发现重复值很多，所以考虑压缩特性。

　　Create index zip_idx

　　on landscp(site, feature, job)

　　compress 2;

　　将索引项分成前缀(prefix)和后缀(postfix)两部分。前两项被放置到前缀部分。

　　Prefix 0: Britten Park, Rose Bed 1

　　Prefix 1: Britten Park, Shrub Bed 1

　　实际所以的结构为：

　　0 Prune

　　0 Mulch

　　0 Spray

　　1 Mulch

　　1 Weed

　　1 Hoe

　　特点：组合索引的前缀部分具有非选择性时，考虑使用压缩。减少I/O,增加性能。

　　九、 索引组织表(IOT)

　　将表中的数据按照索引的结构存储在索引中，提高查询速度。

　　牺牲插入更新的性能，换取查询性能。通常用于数据仓库，提供大量的查询，极少的插入修改工作。

　　必须指定主键。插入数据时，会根据主键列进行B树索引排序，写入磁盘。

　　十、 分区索引

　　簇:

　　A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.