SQL的主键和外键的作用 索引

SQL Server中索引使用及维护
2008-11-25 11:14 [小 大] 来源: 站长论坛 评论: 我来评论 分享至:
百度权重查询 词库网 网站监控 服务器监控 SEO监控 手机游戏 iPhone游戏

在应用系统中,尤其在联机事务处理系统中,对数据查询及处理速度已成为衡量应用系统成败的标准。而采用索引来加快数据处理速度也成为广大数据库用户所接受的优化方法。
在良好的数据库设计基础上，能有效地使用索引是SQL Server取得高性能的基础，SQL Server采用基于代价的优化模型，他对每一个提交的有关表的查询，决定是否使用索引或用哪一个索引。因为查询执行的大部分开销是磁盘I/O，使用索引 提高性能的一个主要目标是避免全表扫描，因为全表扫描需要从磁盘上读表的每一个数据页，如果有索引指向数据值，则查询只需读几次磁盘就能了。所以如果建 立了合理的索引，优化器就能利用索引加速数据的查询过程。不过，索引并不总是提高系统的性能，在增、删、改操作中索引的存在会增加一定的工作量，因此，在 适当的地方增加适当的索引并从不合理的地方删除次优的索引，将有助于优化那些性能较差的SQL Server应用。实践表明，合理的索引设计是建立在对各种查询的分析和预测上的，只有正确地使索引和程式结合起来,才能产生最佳的优化方案。本文就 SQL Server索引的性能问题进行了一些分析和实践。

一、聚簇索引(clustered indexes)的使用


聚簇索引是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇 索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表 120%的附加空间，以存放该表的副本和索引中间页。建立聚簇索引的思想是：

1、大多数表都应该有聚簇索引或使用分区来降低对表尾页的竞争，在一个高事务的环境中，对最后一页的封锁严重影响系统的吞吐量。

2、在聚簇索引下，数据在物理上按顺序排在数据页上，重复值也排在一起，因而在那些 包含范围检查(between、<、<=、>、>=)或使用group by或order by的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行确保物理上毗连在一起而不必进一步搜索，避免了大范围扫描，能大大提高查询速度。

3、在一个频繁发生插入操作的表上建立聚簇索引时，不要建在具有单调上升值的列(如IDENTITY)上，否则会经常引起封锁冲突。

4、在聚簇索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置

。

5、选择聚簇索引应基于where子句和连接操作
的类型。

聚簇索引的侯选列是：

1、主键列,该列在where子句中使用并且插入是随机的。

2、按范围存取的列，如pri_order > 100 and pri_order < 200。

3、在group by或order by中使用的列。

4、不经常修改的列。

5、在连接操作中使用的列。

二、非聚簇索引(nonclustered indexes)的使用

SQL Server缺省情况下建立的索引是非聚簇索引，由于非聚簇索引不重新组织表中的数据，而是对每一行存储索引列值并用一个指针指向数据所在的页面。换句话 说非聚簇索引具有在索引结构和数据本身之间的一个额外级。一个表如果没有聚簇索引时,可有250个非聚簇索引。每个非聚簇索引提供访问数据的不同排序顺 序。在建立非聚簇索引时，要权衡索引对查询速度的加快和降低修改速度之间的利弊。另外，还要考虑这些问题：

1、索引需要使用多少空间。

2、合适的列是否稳定。

3、索引键是怎么选择的，扫描效果是否更佳。

4、是否有许多重复值。

对更新频繁的表来说，表上的非聚簇索引比聚簇索引和根本没有索引需要更多的额外开 销。对移到新页的每一行而言，指向该数据的每个非聚簇索引的页级行也必须更新，有时可能还需要索引页的分理。从一个页面删除数据的进程也会有类似的开销， 另外，删除进程还必须把数据移到页面上部，以确保数据的连续性。所以，建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情况:

1、某列常用于集合函数(如Sum,....)。

2、某列常用于join,order by,group by。

3、查寻出的数据不超过表中数据量的20%。

三、覆盖索引(covering indexes)的使用

覆盖索引是指那些索引项中包含查寻所需要的全部信息的非聚簇索引，这种索引之所以比较快也正是因为索引页中包含了查寻所必须的数据,不需去访问数据页。如果非聚簇索引中包含结果数据,那么他的查询速度将快于聚簇索引。

不过由于覆盖索引的索引项比较多,要占用比较大的空间。而且update操作会引起索引值改动。所以如果潜在的覆盖查询并不常用或不太关键，则覆盖索引的增加反而会降低性能。

四、索引的选择技术

p_detail是住房公积金管理系统中记录个人明细的表，有890000行，观察 在不同索引下的查询运行效果，测试在C/S环境下进行，客户机是IBM PII350(内存64M),服务器是DEC Alpha1000A(内存128M),数据库为SYBASE11.0.3。

1、 select count(*) from p_detail where op_date>’19990101’ and op_date<’ 19991231’ and pri_surplus1>300 2、 select count(*),sum(pri_surplus1) from p_detail where op_d

ate>’19990101’ and pay_month between‘199908’ and’199912’ 不建所有索引查询1 1分15秒 查询2 1分7
秒 在op_date上建非聚簇索引查询1 57秒 查询2 57秒 在op_date上建聚簇索引查询1 <1秒 查询2 52秒 在pay_month、op_date、pri_surplus1上建索引查询1 34秒 查询2 <1秒 在op_date、pay_month、pri_surplus1上建索引查询1 <1秒 查询2 <1秒

从以上查询效果分析，索引的有无，建立方式的不同将会导致不同的查询效果，选择什么样的索引基于用户对数据的查询条件,这些条件体现于where从句和join表达式中。一般来说建立索引的思路是：

(1)、主键时常作为where子句的条件，应在表的主键列上建立聚簇索引，尤其当经常用他作为连接的时候。

(2)、有大量重复值且经常有范围查询和排序、分组发生的列，或非常频繁地被访问的列，可考虑建立聚簇索引。

(3)、经常同时存取多列，且每列都含有重复值可考虑建立复合索引来覆盖一个或一组查询，并把查询引用最频繁的列作为前导列，如果可能尽量使关键查询形成覆盖查询。

(4)、如果知道索引键的所有值都是唯一的，那么确保把索引定义成唯一索引。

(5)、在一个经常做插入操作的表上建索引时，使用fillfactor(填充因子)来减少页分裂，同时提高并发度降低死锁的发生。如果在只读表上建索引，则能把fillfactor置为100。

(6)、在选择索引键时，设法选择那些采用小数据类型的列作为键以使每个索引页能够容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必须遍历的索引页面降到最小。此外，尽可能地使用整数为键值，因为他能够提供比所有数据类型都快的访问速度。

五、索引的维护

上面讲到,某些不合适的索引影响到SQL Server的性能,随着应用系统的运行,数据不断地发生变化,当数据变化达到某一个程度时将会影响到索引的使用。这时需要用户自己来维护索引。索引的维护包括：

1、重建索引

随着数据行的插入、删除和数据页的分裂，有些索引页可能只包含几页数据，另外应用在执行大块I/O的时候，重建非聚簇索引能降低分片，维护大块I/O的效率。重建索引实际上是重新组织B-树空间。在下面情况下需要重建索引：

(1)、数据和使用模式大幅度变化。

(2)、排序的顺序发生改动。

(3)、要进行大量插入操作或已完成。

(4)、使用大块I/O的查询的磁盘读次数比预料的要多。

(5)、由于大量数据修改，使得数据页和索引页没有充分使用而导致空间的使用超出估算。

(6)、dbcc检查出索引有问题。

当重建聚簇索引时,这张

表的所有非聚簇索引将被重建。

2、索引统计信息的更新

当在一个包含数据的表上创建索引的时候，SQL Server会创建分布数据页来存放有关索引的两种
统计信息：分布表和密度表。优化器利用这个页来判断该索引对某个特定查询是否有用。但这个统计信息并不 动态地重新计算。这意味着，当表的数据改动之后，统计信息有可能是过时的，从而影响优化器追求最有工作的目标。因此，在下面情况下应该运行update statistics命令：

(1)、数据行的插入和删除修改了数据的分布。

(2)、对用truncate table删除数据的表上增加数据行。

(3)、修改索引列的值。

六、结束语

实践表明，不恰当的索引不仅于事无补，反而会降低系统的执行性能。因为大量的索引在插入、修改和删除操作时比没有索引花费更多的系统时间。例如下面情况下建立的索引是不恰当的：

1、在查询中非常少或从不引用的列不会受益于索引，因为索引非常少或从来不必搜索基于这些列的行。

2、只有两个或三个值的列，如男性和女性(是或否)，从不会从索引中得到好处。

另外，鉴于索引加快了查询速度，但减慢了数据更新速度的特点。可通过在一个段上建表，而在另一个段上建其非聚簇索引，而这两段分别在独立的物理设备上来改善操作性能。

索引：

进入查询窗口后，输入下面的语句：

CREATE INDEX mycolumn_index ON mytable (myclumn)

这个语句建立了一个名为mycolumn_index的索引。你可以给一个索引起任何名字，但你应该在索引名中包含所索引的字段名，这对你将来弄清楚建立该索引的意图是有帮助的。

注意：

在本书中你执行任何SQL语句，都会收到如下的信息：

This command did not return data,and it did not return any rows

这说明该语句执行成功了。

索引mycolumn_index对表mytable的mycolumn字段进行。这是个非聚簇索引，也是个非唯一索引。（这是一个索引的缺省属性）

如果你需要改变一个索引的类型，你必须删除原来的索引并重建 一个。建立了一个索引后，你可以用下面的SQL语句删除它：

DROP INDEX mytable.mycolumn_index

注意在DROP INDEX 语句中你要包含表的名字。在这个例子中，你删除的索引是mycolumn_index，它是表mytable的索引。

要建立一个聚簇索引，可以使用关键字CLUSTERED。）记住一个表只能有一个聚簇索引。（这里有一个如何对一个表建立聚簇索引的例子：

CREATE CLUSTERED INDEX mycolumn_clust_index ON mytable(mycolumn)

如果表中有重复的记录，当你试图用这个语句建立索引时，会出现错误。但是有重复记录的表也可以建立索

引；你只要使用关键字ALLOW_DUP_ROW把这一点告诉SQL Sever即可：

CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)

WITH ALLOW_DUP_ROW

这个语句建立了一个允许重复记录的聚簇索引。你应该尽量避免
在一个表中出现重复记录，但是，如果已经出现了，你可以使用这种方法。

要对一个表建立唯一索引，可以使用关键字UNIQUE。对聚簇索引和非聚簇索引都可以使用这个关键字。这里有一个例子：

CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

这是你将经常使用的索引建立语句。无论何时，只要可以，你应该尽量对一个对一个表建立唯一聚簇索引来增强查询操作。

最后，要建立一个对多个字段的索引──复合索引──在索引建立语句中同时包含多个字段名。下面的例子对firstname和lastname两个字段建立索引：

CREATE INDEX name_index ON username(firstname,lastname)

这个例子对两个字段建立了单个索引。在一个复合索引中，你最多可以对16个字段进行索引。

用事务管理器建立索引

用事务管理器建立索引比用SQL语句容易的多。使用事务管理器，你可以看到已经建立的索引的列表，并可以通过图形界面选择索引选项。

使用事务管理器你可以用两种方式建立索引：使用Manage Tables窗口或使用Manage Indexes窗口。

要用Manage Tables 窗口建立一个新索引，单击按钮Advanced Options(它看起来象一个前面有一加号的表)。这样就打开了Advanced Options对话框。这个对话框有一部分标名为Primary Key（见图11.1）。

图11。1

要建立一个新索引，从下拉列表中选择你想对之建立索引的字段名。如果你想建立一个对多字段的索引，你可以选择多个字段名。你还可以选择索引是聚簇的还是非聚簇的。在保存表信息后，索引会自动被建立。在Manage Tables窗口中的字段名旁边，会出现一把钥匙。

你已经为你的表建立了“主索引”。主索引必须对不包含空值的字段建立。另外，主索引强制一个字段成为唯一值字段。

要建立没有这些限制的索引，你需要使用Manage Indexes窗口。从菜单中选择Manage|Indexes，打开Manage Indexes 窗口。在Manage Indexes 窗口中，你可以通过下拉框选择表和特定的索引。（见图11.2）。要建立一个新索引，从Index下拉框中选择New Index.，然后就可以选择要对之建立索引的字段。单击按钮Add，把字段加人到索引中。

图11。2

你可以为你的索引选择许多不同的选项。例如，你可以选择该索引是聚簇的还是非聚簇的。你还可以指定该索引为唯一索引。设计好索引后，单击按钮Build，建立该索引。

注意：

唯一索引

是指该字段不能有重复的值，而不是只能建立这一个索引。

SQL核心语句

在第十章，你学会了如何用SQL SELECT 语句从一个表中取数据。但是，到现在为止，还没有讨论如何添加，修改或删除表中的数据。在这一节中，你将学习这些
内容。

插入数据

向表中添加一个新记录，你要使用SQL INSERT 语句。这里有一个如何使用这种语句的例子：

INSERT mytable (mycolumn) VALUES (‘some data')

这个语句把字符串'some data'插入表mytable的mycolumn字段中。将要被插入数据的字段的名字在第一个括号中指定，实际的数据在第二个括号中给出。

INSERT 语句的完整句法如下：

INSERT [INTO] {table_name|view_name} [(column_list)] {DEFAULT VALUES |

Values_list | select_statement}

如果一个表有多个字段，通过把字段名和字段值用逗号隔开，你可以向所有的字段中插入数据。假设表mytable有三个字段first_column,second_column,和third_column。下面的INSERT语句添加了一条三个字段都有值的完整记录：

INSERT mytable (first_column,second_column,third_column)

VALUES (‘some data','some more data','yet more data')

注意：

你可以使用INSERT语句向文本型字段中插入数据。但是，如果你需要输入很长的字符串，你应该使用WRITETEXT语句。这部分内容对本书来说太高级了，因此不加讨论。要了解更多的信息，请参考Microsoft SQL Sever 的文档。

如果你在INSERT 语句中只指定两个字段和数据会怎么样呢？换句话说，你向一个表中插入一条新记录，但有一个字段没有提供数据。在这种情况下，有下面的四种可能：

如果该字段有一个缺省值，该值会被使用。例如，假设你插入新记录时没有给字段third_column提供数据，而这个字段有一个缺省值'some value'。在这种情况下，当新记录建立时会插入值'some value'。
如果该字段可以接受空值，而且没有缺省值，则会被插入空值。
如果该字段不能接受空值，而且没有缺省值，就会出现错误。你会收到错误信息：
The column in table mytable may not be null.

最后，如果该字段是一个标识字段，那么它会自动产生一个新值。当你向一个有标识字段的表中插入新记录时，只要忽略该字段，标识字段会给自己赋一个新值。
注意：

向一个有标识字段的表中插入新记录后，你可以用SQL变量@@identity来访问新记录

的标识字段的值。考虑如下的SQL语句：

INSERT mytable (first_column) VALUES(‘some value')

INSERT anothertable(another_first,another_second)

VALUES(@@identity,'some value')

如果表mytable有一个标识字段，该字段的值

会被插入表anothertable的another_first字段。这是因为变量@@identity总是保存最后一次插入标识字段的值。

字段another_first应该与字段first_column有相同的数据类型。但是，字段another_first不能是应该标识字段。Another_first字段用来保存字段first_column的值。

删除记录

要从表中删除一个或多个记录，需要使用SQL DELETE语句。你
可以给DELETE 语句提供WHERE 子句。WHERE子句用来选择要删除的记录。例如，下面的这个DELETE语句只删除字段first_column的值等于'Delete Me'的记录：

DELETE mytable WHERE first_column='Deltet Me'

DELETE 语句的完整句法如下：

DELETE [FROM] {table_name|view_name} [WHERE clause]

在SQL SELECT 语句中可以使用的任何条件都可以在DELECT 语句的WHERE子句中使用。例如，下面的这个DELETE语句只删除那些first_column字段的值为'goodbye'或second_column字段的值为 'so long'的记录：

DELETE mytable WHERE first_column='goodby' OR second_column='so long'

如果你不给DELETE 语句提供WHERE 子句，表中的所有记录都将被删除。你不应该有这种想法。如果你想删除应该表中的所有记录，应使用第十章所讲的TRUNCATE TABLE语句。

注意：

为什么要用TRUNCATE TABLE 语句代替DELETE语句？当你使用TRUNCATE TABLE语句时，记录的删除是不作记录的。也就是说，这意味着TRUNCATE TABLE 要比DELETE快得多

详细出处参考：https://www.360docs.net/doc/5e17911559.html,/article/15574.htm

SQL的主键和外键的作用
SQL的主键和外键约束


SQL的主键和外键的作用：

外键取值规则：空值或参照的主键值。

(1)插入非空值时，如果主键表中没有这个值，则不能插入。

(2)更新时，不能改为主键表中没有的值。

(3)删除主键表记录时，你可以在建外键时选定外键记录一起级联删除还是拒绝删除。

(4)更新主键记录时，同样有级联更新和拒绝执行的选择。

简而言之，SQL的主键和外键就是起约束作用。





关系型数据库中的一条记录中有若干个属性，若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键。

比如：

学生表(学号，姓名，性别，班级)

其中每个学生的学号是唯一的，学号就是一个主键；

课程表(课程编号,课程名,学分)

其中课程编号是唯一的,课程编号就是一个主键；

成绩表(学号,课程号,成绩)

成绩表中单一一个属性无法唯一标识一条记录，学号和课程号的组合才可以唯一标识一条记录，所以，学号和课程号的属性组是一个主键。



成绩表中的学号不是成绩表的主键，但它和学生表中的学号相对应，并且学生表

中的学号是学生表的主键，则称成绩表中的学号是学生表的外键；同理，成绩表中的课程号是课程表的外键。



定义主键和外键主要是为了维护关系数据库的完整性，总结一下：

一、主键是能确定一条记录的唯一标识，比如，一条记录包括身份正号，姓名，年龄。身份证号是唯一能确定你这个人的，其他都可能有重复，所以，身份证号是主键。

外键用于与另一张表的关联
。是能确定另一张表记录的字段，用于保持数据的一致性。比如，A表中的一个字段，是B表的主键，那他就可以是A表的外键。



二、主键、外键和索引的区别



定义：

主键--唯一标识一条记录，不能有重复的，不允许为空

外键--表的外键是另一表的主键, 外键可以有重复的, 可以是空值

索引--该字段没有重复值，但可以有一个空值



作用：

主键--用来保证数据完整性

外键--用来和其他表建立联系用的

索引--是提高查询排序的速度



个数：

主键--主键只能有一个

外键--一个表可以有多个外键

索引--一个表可以有多个唯一索引









创建SQL的主键和外键约束的方法：



create table Student --建表格式:create table 自定义的表名

( --字段名一般为有一定意义的英文

StudentName nvarchar(15), -- 格式：字段名类型()括号里面的是允许输入的长度

StudentAge int, --int型的后面不需要接长度

StudentSex nvarchar(2) --最后一个字段后面不要逗号

)



--在创建表时就可以对字段加上约束:

create table Student

(

StudentNo int PRIMARY KEY IDENTITY(1,1), --加主键约束,还有标识列属性（两者构成实体完整性）

StudentName nvarchar(15) not null, --加非空约束,不加"not null" 默认为：可以为空

StudentSchool text(20) FOREIGN KEY REFERENCES SchoolTable(SchoolName), --加外键约束,格式:FOREIGN KEY REFERENCES 关联的表名(字段名)

StudentAge int DEFAULT ((0)), --加默认值约束

StudentSex nvarchar(2) CHECK(StudentSex=N'男' or StudentSex=N'女') --加检查约束,格式:check (条件表达式)

)



--如果在表创建好了以后再加约束，则格式分别为：



-- 主键:

alter table 表名

add constraint PK_字段名--"PK"为主键的缩写，字段名为要在其上创建主键的字段名,'PK_字段名'就为约束名

primary key (字段名) --字段名同上



--唯一约束:

alter table 表名

add constraint UQ_字段名

unique (字段名)



--外键约束：

alter table 表名

add constraint FK_字段名--"FK"为外键的缩写

foreign key (字段名) references 关联的表名(关联的字段名) --注意'关联的表名'

和'关联的字段名'



alter table 表A add constraint FK_B foreign key (ticket_no) references 表B(ticket_no)

alter table 表A add constraint FK_C foreign key (person_no) references 表C(person_no)



alter table 成绩表 add constraint FK_StudentNo foreign key (StudentNo) references Student (StudentNo)

ON UPDATE CASCADE ON DELETE CASCADE

级联更新，级联删除，这样在删除主表Student时，成绩表中该学生的所有成绩都会删除。





--检查约束:

alter table 表名

add constraint CK_字段名

check (条件表达式) --条
件表达式中的条件用关系运算符连接



--默认值约束:

alter table 表名

add constraint DF_字段名

default '默认值' for 字段名--其中的'默认值'为你想要默认的值，注意'for'



--删除创建的约束:

alter table 表名

drop constraint 约束名--约束名为你前面创建的如：PK_字段这样的约束名

--注意：如果约束是在创建表的时候创建的，则不能用命令删除

--只能在'企业管理器'里面删除











-- 获取SqlServer中表结构

SELECT https://www.360docs.net/doc/5e17911559.html,,https://www.360docs.net/doc/5e17911559.html,,syscolumns.isnullable,

syscolumns.length

FROM syscolumns,systypes

WHERE syscolumns.xusertype = systypes.xusertype

AND syscolumns.id = OBJECT_ID('Student')



-- 单独查询表递增字段

SELECT [name] FROM syscolumns WHERE

id = OBJECT_ID(N'Student') AND COLUMNPROPERTY(id,name,'IsIdentity')=1



-- 获取表主外键约束

EXEC sp_helpconstraint 'StuResults'





-- 查询表主键外键信息

SELECT sysobjects.id objectId,OBJECT_NAME(sysobjects.parent_obj) tableName,

https://www.360docs.net/doc/5e17911559.html, constraintName, sysobjects.xtype AS constraintType,

https://www.360docs.net/doc/5e17911559.html, AS columnName

FROM sysobjects INNER JOIN sysconstraints

ON sysobjects.xtype in('C', 'F', 'PK', 'UQ', 'D')

AND sysobjects.id = sysconstraints.constid

LEFT OUTER JOIN syscolumns ON sysconstraints.id = syscolumns.id

WHERE OBJECT_NAME(sysobjects.parent_obj)='StuResults'