SQL 数据表修改，索引小结

照汗青 2010-11-26

展开全文

一、索引块与数据块的区别

大家都知道，索引可以提高检索效率，因为它的二叉树结构以及占用空间小，所以访问速度块。让我们来算一道数学题：如果表中的一条记录在磁盘上占用1000字节的话，我们对其中10字节的一个字段建立索引，那么该记录对应的索引块的大小只有10字节。我们知道，SQL Server的最小空间分配单元是“页（Page）”，一个页在磁盘上占用8K空间，那么这一个页可以存储上述记录8条，但可以存储索引800条。现在我们要从一个有8000条记录的表中检索符合某个条件的记录，如果没有索引的话，我们可能需要遍历8000条×1000字节/8K字节=1000个页面才能够找到结果。如果在检索字段上有上述索引的话，那么我们可以在8000条×10字节/8K字节=10个页面中就检索到满足条件的索引块，然后根据索引块上的指针逐一找到结果数据块，这样IO访问量要少的多。

二、索引优化技术

是不是有索引就一定检索的快呢？答案是否。有些时候用索引还不如不用索引快。比如说我们要检索上述表中的所有记录，如果不用索引，需要访问8000条×1000字节/8K字节=1000个页面，如果使用索引的话，首先检索索引，访问8000条×10字节/8K字节=10个页面得到索引检索结果，再根据索引检索结果去对应数据页面，由于是检索所有数据，所以需要再访问8000条×1000字节/8K字节=1000个页面将全部数据读取出来，一共访问了1010个页面，这显然不如不用索引快。

三、聚簇索引与非聚簇索引的本质区别

现在可以讨论聚簇索引与非聚簇索引的本质区别了。正如本文最前面的两个图所示，聚簇索引的叶节点就是数据节点，而非聚簇索引的页节点仍然是索引检点，并保留一个链接指向对应数据块。

还是通过一道数学题来看看它们的区别吧：假设有一8000条记录的表，表中每条记录在磁盘上占用1000字节，如果在一个10字节长的字段上建立非聚簇索引主键，需要二叉树节点16000个（这16000个节点中有8000个叶节点，每个页节点都指向一个数据记录），这样数据将占用8000条×1000字节/8K字节=1000个页面；索引将占用16000个节点×10字节/8K字节=20个页面，共计1020个页面。

同样一张表，如果我们在对应字段上建立聚簇索引主键，由于聚簇索引的页节点就是数据节点，所以索引节点仅有8000个，占用10个页面，数据仍然占有1000个页面。

下面我们看看在执行插入操作时，非聚簇索引的主键为什么比聚簇索引主键要快。主键约束要求主键不能出现重复，那么SQL Server是怎么知道不出现重复的呢？唯一的方法就是检索。对于非聚簇索引，只需要检索20个页面中的16000个节点就知道是否有重复，因为所有主键键值在这16000个索引节点中都包含了。但对于聚簇索引，索引节点仅仅包含了8000个中间节点，至于会不会出现重复必须检索另外1000个页数据节点才知道，那么相当于检索10+1000=1010个页面才知道是否有重复。所以聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。

让我们再来看看数据检索的效率，如果对上述两表进行检索，在使用索引的情况下（有些时候SQL Server执行计划会选择不使用索引，不过我们这里姑且假设一定使用索引），对于聚簇索引检索，我们可能会访问10个索引页面外加1000个数据页面得到结果（实际情况要比这个好），而对于非聚簇索引，系统会从20个页面中找到符合条件的节点，再映射到1000个数据页面上（这也是最糟糕的情况），比较一下，一个访问了1010个页面而另一个访问了1020个页面，可见检索效率差异并不是很大。所以不管非聚簇索引也好还是聚簇索引也好，都适合排序，聚簇索引仅仅比非聚簇索引快一点。

1.建立数据表：

create table choice
(
   s_no char(6),
   course_no char(5),
   score numeric(6,1),
   constraint cho_pri primary key(s_no,course_no)
)

2.添加新的字段

alter table choice

add choice_socre numeric(6,1)

--注意，这个字段会自动填充NULL值，所以，一定不能在新建的这个字段中使用not null 约束。因此，也不能添加primary key约束。当然，可以添加unique约束（unique允许有null值）。

alter table choice
add palgerrard char(6) check(palgerrard<>'pal') --字段和约束同时添加

alter table choice
add shuang char(5) unique --字段和约束同时添加

3.添加约束条件
alter table choice
add constraint sco_che check(score>=0 and score<=100)

alter table choice
add constraint pal_uni unique (score)

--注意：这里的unique约束，要用（）将约束的字段括起来， check 约束就不需要了。而且，如果table choice中已有数据内容。那么score中若有相同的值，这个约束就会添加失败。若score中没有相同的内容，这个约束可以成功添加。同理，check约束也会根据已有的数据项，判断是否可以添加。

4.字段的删除，约束的删除

要删除一个字段，如果该字段有约束。要先删除约束，再删除字段。

alter table choice
drop column course_no --字段的删除要有column关键字,否则course_no被认为是约束

alter table choice

drop constraint sco_che 或者 drop sco_che

5.关键字约束

关键字约束可以被删除，若删除了一个表中的关键字，该表变为“只读”表。关键字被删除了，不能通过add添加新字段并关键字约束。例如add pal char(6) primary key ，这句错了，add pal char（6）会为pal字段自动设置null约束。注意：通过add添加新字段自动填充NULL值，关键字只可以添加在not null 约束的字段上！

create table gerrard
(
   s_no char(6) not null,
   course_no char(5),   //系统默认null
   score numeric(6,1),
)
alter table gerrard
add constraint cc_pri primary key (s_no)

我们可以删除表的关键字，add一个新字段（null），修改为not null 约束，然后添加pimary key 约束

create table shuang
(
   s_no char(6) ,
   course_no char(5),
   score numeric(6,1),
)
alter table shuang
add pri char(4)
alter table shuang
alter column
pri varchar(3) not null      --这里修改了not null 约束，关键！
alter table shuang
add constraint ss_pri primary key (pri)

6.alter的限制

* 不能改变列名

* 不能将含有空值的列得定义修改为not null

* 若列中已有数据，则不能减少该列的宽度，也不能改变其数据类型

* 只能修改null/not 约束，其它类型的约束在修改之前必须先将约束删除，然后再重新添加修改过的约束定义。

7.聚集索引，关键字

关键字会自动建立聚集的唯一索引，unique约束会自动建立非聚集的唯一索引

下面的例子，综合运用了上面的很多知识

drop table number
create table number
(
    turn int ,
    next int ,
    sign int

) --在查询分析器中，此表只“可读”

create unique index pri_unique on number(turn asc) --建立了唯一索引，表可写了，不过这里的asc升序排列对表中数据无影响啊？？？

/*alter table number
alter column turn int not null*/ /* 前面有了索引，不能修改列turn */