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

1、SQL Server中索引使用及维护 2008-11-25 11:14 小 大 来源: 站长论坛 评论: 我来评论 分享至: 百度权重查询 词库网 网站监控 服务器监控 SEO监控 手机游戏 iPhone游戏 在应用系统中,尤其在联机事务处理系统中,对数据查询及处理速度已成为衡量应用系统成败的标准。而采用索引来加快数据处理速度也成为广大数据库用户所接受的优化方法。 在良好的数据库设计基础上，能有效地使用索引是SQL Server取得高性能的基础，SQL Server采用基于代价的优化模型，他对每一个提交的有关表的查询，决定是否使用索引或用哪一个索引。因为查询执行的大部分开销是磁盘I/O，使用索

2、引 提高性能的一个主要目标是避免全表扫描，因为全表扫描需要从磁盘上读表的每一个数据页，如果有索引指向数据值，则查询只需读几次磁盘就能了。所以如果建 立了合理的索引，优化器就能利用索引加速数据的查询过程。不过，索引并不总是提高系统的性能，在增、删、改操作中索引的存在会增加一定的工作量，因此，在 适当的地方增加适当的索引并从不合理的地方删除次优的索引，将有助于优化那些性能较差的SQL Server应用。实践表明，合理的索引设计是建立在对各种查询的分析和预测上的，只有正确地使索引和程式结合起来,才能产生最佳的优化方案。本文就 SQL Server索引的性能问题进行了一些分析和实践。 一、聚簇索引(c

3、lustered indexes的使用 聚簇索引是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇 索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表 120%的附加空间，以存放该表的副本和索引中间页。建立聚簇索引的思想是： 1、大多数表都应该有聚簇索引或使用分区来降低对表尾页的竞争，在一个高事务的环境中，对最后一页的封锁严重影响系统的吞吐量。 2、在聚簇索引下，数据在物理上按顺序排在数据页上，重复值也排在一起，因而在那些 包含范围检查(between、=或使用group by或ord

4、er by的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行确保物理上毗连在一起而不必进一步搜索，避免了大范围扫描，能大大提高查询速度。 3、在一个频繁发生插入操作的表上建立聚簇索引时，不要建在具有单调上升值的列(如IDENTITY上，否则会经常引起封锁冲突。 4、在聚簇索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置。 5、选择聚簇索引应基于where子句和连接操作 秒在op_date上建非聚簇索引查询1 57秒查询2 57秒在op_date上建聚簇索引查询1 1秒查询2 52秒在pay_month、op_date、pri_surplus1上建索引查询1 34

5、秒查询2 1秒在op_date、pay_month、pri_surplus1上建索引查询1 1秒查询2 1秒 从以上查询效果分析，索引的有无，建立方式的不同将会导致不同的查询效果，选择什么样的索引基于用户对数据的查询条件,这些条件体现于where从句和join表达式中。一般来说建立索引的思路是： (1、主键时常作为where子句的条件，应在表的主键列上建立聚簇索引，尤其当经常用他作为连接的时候。 (2、有大量重复值且经常有范围查询和排序、分组发生的列，或非常频繁地被访问的列，可考虑建立聚簇索引。 (3、经常同时存取多列，且每列都含有重复值可考虑建立复合索引来覆盖一个或一组查询，并把查询引用最频

6、繁的列作为前导列，如果可能尽量使关键查询形成覆盖查询。 (4、如果知道索引键的所有值都是唯一的，那么确保把索引定义成唯一索引。 (5、在一个经常做插入操作的表上建索引时，使用fillfactor(填充因子来减少页分裂，同时提高并发度降低死锁的发生。如果在只读表上建索引，则能把fillfactor置为100。 (6、在选择索引键时，设法选择那些采用小数据类型的列作为键以使每个索引页能够容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必须遍历的索引页面降到最小。此外，尽可能地使用整数为键值，因为他能够提供比所有数据类型都快的访问速度。 五、索引的维护 上面讲到,某些不合适的索引影响到SQL

7、Server的性能,随着应用系统的运行,数据不断地发生变化,当数据变化达到某一个程度时将会影响到索引的使用。这时需要用户自己来维护索引。索引的维护包括： 1、重建索引 随着数据行的插入、删除和数据页的分裂，有些索引页可能只包含几页数据，另外应用在执行大块I/O的时候，重建非聚簇索引能降低分片，维护大块I/O的效率。重建索引实际上是重新组织B-树空间。在下面情况下需要重建索引： (1、数据和使用模式大幅度变化。 (2、排序的顺序发生改动。 (3、要进行大量插入操作或已完成。 (4、使用大块I/O的查询的磁盘读次数比预料的要多。 (5、由于大量数据修改，使得数据页和索引页没有充分使用而导致空间的使

8、用超出估算。 (6、dbcc检查出索引有问题。 当重建聚簇索引时,这张表的所有非聚簇索引将被重建。 2、索引统计信息的更新 当在一个包含数据的表上创建索引的时候，SQL Server会创建分布数据页来存放有关索引的两种 统计信息：分布表和密度表。优化器利用这个页来判断该索引对某个特定查询是否有用。但这个统计信息并不 动态地重新计算。这意味着，当表的数据改动之后，统计信息有可能是过时的，从而影响优化器追求最有工作的目标。因此，在下面情况下应该运行update statistics命令： (1、数据行的插入和删除修改了数据的分布。 (2、对用truncate table删除数据的表上增加数据行。

9、(3、修改索引列的值。 六、结束语 实践表明，不恰当的索引不仅于事无补，反而会降低系统的执行性能。因为大量的索引在插入、修改和删除操作时比没有索引花费更多的系统时间。例如下面情况下建立的索引是不恰当的： 1、在查询中非常少或从不引用的列不会受益于索引，因为索引非常少或从来不必搜索基于这些列的行。 2、只有两个或三个值的列，如男性和女性(是或否，从不会从索引中得到好处。 另外，鉴于索引加快了查询速度，但减慢了数据更新速度的特点。可通过在一个段上建表，而在另一个段上建其非聚簇索引，而这两段分别在独立的物理设备上来改善操作性能。 索引： 进入查询窗口后，输入下面的语句： CREATE INDEX m

10、ycolumn_index ON mytable (myclumn 这个语句建立了一个名为mycolumn_index的索引。你可以给一个索引起任何名字，但你应该在索引名中包含所索引的字段名，这对你将来弄清楚建立该索引的意图是有帮助的。 注意： 在本书中你执行任何SQL语句，都会收到如下的信息： This command did not return data,and it did not return any rows 这说明该语句执行成功了。 索引mycolumn_index对表mytable的mycolumn字段进行。这是个非聚簇索引，也是个非唯一索引。（这是一个索引的缺省属性） 如果你

11、需要改变一个索引的类型，你必须删除原来的索引并重建 一个。建立了一个索引后，你可以用下面的SQL语句删除它： DROP INDEX mytable.mycolumn_index 注意在DROP INDEX 语句中你要包含表的名字。在这个例子中，你删除的索引是mycolumn_index，它是表mytable的索引。 要建立一个聚簇索引，可以使用关键字CLUSTERED。）记住一个表只能有一个聚簇索引。（这里有一个如何对一个表建立聚簇索引的例子： CREATE CLUSTERED INDEX mycolumn_clust_index ON mytable(mycolumn 如果表中有重复的记录，

12、当你试图用这个语句建立索引时，会出现错误。但是有重复记录的表也可以建立索引；你只要使用关键字ALLOW_DUP_ROW把这一点告诉SQL Sever即可： CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn WITH ALLOW_DUP_ROW 这个语句建立了一个允许重复记录的聚簇索引。你应该尽量避免 在一个表中出现重复记录，但是，如果已经出现了，你可以使用这种方法。 要对一个表建立唯一索引，可以使用关键字UNIQUE。对聚簇索引和非聚簇索引都可以使用这个关键字。这里有一个例子： CREATE UNIQUE COUSTERED

13、INDEX myclumn_cindex ON mytable(mycolumn 这是你将经常使用的索引建立语句。无论何时，只要可以，你应该尽量对一个对一个表建立唯一聚簇索引来增强查询操作。 最后，要建立一个对多个字段的索引复合索引在索引建立语句中同时包含多个字段名。下面的例子对firstname和lastname两个字段建立索引： CREATE INDEX name_index ON username(firstname,lastname 这个例子对两个字段建立了单个索引。在一个复合索引中，你最多可以对16个字段进行索引。 用事务管理器建立索引 用事务管理器建立索引比用SQL语句容易的多。使

14、用事务管理器，你可以看到已经建立的索引的列表，并可以通过图形界面选择索引选项。 使用事务管理器你可以用两种方式建立索引：使用Manage Tables窗口或使用Manage Indexes窗口。 要用Manage Tables 窗口建立一个新索引，单击按钮Advanced Options(它看起来象一个前面有一加号的表。这样就打开了Advanced Options对话框。这个对话框有一部分标名为Primary Key（见图11.1）。 图11。1 要建立一个新索引，从下拉列表中选择你想对之建立索引的字段名。如果你想建立一个对多字段的索引，你可以选择多个字段名。你还可以选择索引是聚簇的还是非聚簇

15、的。在保存表信息后，索引会自动被建立。在Manage Tables窗口中的字段名旁边，会出现一把钥匙。 你已经为你的表建立了“主索引”。主索引必须对不包含空值的字段建立。另外，主索引强制一个字段成为唯一值字段。 要建立没有这些限制的索引，你需要使用Manage Indexes窗口。从菜单中选择Manage|Indexes，打开Manage Indexes 窗口。在Manage Indexes 窗口中，你可以通过下拉框选择表和特定的索引。（见图11.2）。要建立一个新索引，从Index下拉框中选择New Index.，然后就可以选择要对之建立索引的字段。单击按钮Add，把字段加人到索引中。 图1

16、1。2 你可以为你的索引选择许多不同的选项。例如，你可以选择该索引是聚簇的还是非聚簇的。你还可以指定该索引为唯一索引。设计好索引后，单击按钮Build，建立该索引。 注意： 唯一索引是指该字段不能有重复的值，而不是只能建立这一个索引。 SQL核心语句 在第十章，你学会了如何用SQL SELECT 语句从一个表中取数据。但是，到现在为止，还没有讨论如何添加，修改或删除表中的数据。在这一节中，你将学习这些 内容。 插入数据 向表中添加一个新记录，你要使用SQL INSERT 语句。这里有一个如何使用这种语句的例子： INSERT mytable (mycolumn VALUES (some dat

17、a 这个语句把字符串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

18、语句添加了一条三个字段都有值的完整记录： INSERT mytable (first_column,second_column,third_column VALUES (some data,some more data,yet more data 注意： 你可以使用INSERT语句向文本型字段中插入数据。但是，如果你需要输入很长的字符串，你应该使用WRITETEXT语句。这部分内容对本书来说太高级了，因此不加讨论。要了解更多的信息，请参考Microsoft SQL Sever 的文档。 如果你在INSERT 语句中只指定两个字段和数据会怎么样呢？换句话说，你向一个表中插入一条新记录，但有一个字

19、段没有提供数据。在这种情况下，有下面的四种可能： 如果该字段有一个缺省值，该值会被使用。例如，假设你插入新记录时没有给字段third_column提供数据，而这个字段有一个缺省值some value。在这种情况下，当新记录建立时会插入值some value。 如果该字段可以接受空值，而且没有缺省值，则会被插入空值。 如果该字段不能接受空值，而且没有缺省值，就会出现错误。你会收到错误信息： The column in table mytable may not be null. 最后，如果该字段是一个标识字段，那么它会自动产生一个新值。当你向一个有标识字段的表中插入新记录时，只要忽略该字段，标识

20、字段会给自己赋一个新值。 注意： 向一个有标识字段的表中插入新记录后，你可以用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

21、_first应该与字段first_column有相同的数据类型。但是，字段another_first不能是应该标识字段。Another_first字段用来保存字段first_column的值。 删除记录 要从表中删除一个或多个记录，需要使用SQL DELETE语句。你 。是能确定另一张表记录的字段，用于保持数据的一致性。比如，A表中的一个字段，是B表的主键，那他就可以是A表的外键。 二、主键、外键和索引的区别 定义： 主键-唯一标识一条记录，不能有重复的，不允许为空 外键-表的外键是另一表的主键, 外键可以有重复的, 可以是空值 索引-该字段没有重复值，但可以有一个空值 作用： 主键-用来保证

22、数据完整性 外键-用来和其他表建立联系用的 索引-是提高查询排序的速度 个数： 主键-主键只能有一个 外键-一个表可以有多个外键 索引-一个表可以有多个唯一索引 创建SQL的主键和外键约束的方法： create table Student -建表格式:create table 自定义的表名 ( -字段名一般为有一定意义的英文 StudentName nvarchar(15, - 格式：字段名类型(括号里面的是允许输入的长度 StudentAge int, -int型的后面不需要接长度 StudentSex nvarchar(2 -最后一个字段后面不要逗号 -在创建表时就可以对字段加上约束: c

23、reate 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, -加默认值约束 Stud

24、entSex 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

25、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 (Studen

26、tNo 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