第章_索引及其应用.ppt

建立索引的思路 定义有主键的列可以建立索引 定义有外键的列可以创建索引 在经常查询的列创建索引 不常查询，或重复值多的列不要建查询 TEXT，NTEXT，IMAGE和BIT不建索引。 填充因子 在创建聚集索引时，表中的数据按照索引列中的值的顺序存储在数据库的数据页中。 在表中插入新的数据行或更改索引列中的值时，Microsoft SQL Server 可能必须重新组织表中的数据存储，以便为新行腾出空间，保持数据的有序存储。这同样适用于非聚集索引。 添加或更改数据时，SQL Server 可能不得不重新组织非聚集索引页中的数据存储。 向一个已满的索引页添加某个新行时，SQL Server 把大约一半的行移到新页中以便为新行腾出空间。这种重组称为页拆分。 页拆分会降低性能并使表中的数据存储产生碎片。 创建索引时，可以指定一个填充因子，以便在索引的每个叶级页上留出额外的间隙和保留一定百分比的空间，供将来表的数据存储容量进行扩充和减少页拆分的可能性。 填充因子的值是从 0 到 100 的百分比数值，指定在创建索引后对数据页的填充比例。 值为 100 时表示页将填满，所留出的存储空间量最小。只有当不会对数据进行更改时（例如，在只读表中）才会使用此设置。 值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中对数据页进行拆分的需要，但需要更多的存储空间。当表中数据会发生更改时，这种设置更为适当。 说明??即使对于一个面向许多插入和更新操作的应用程序来说，数据库读取次数一般也超过数据库写入次数的 5 到 10 倍。 因此，指定一个不同于默认设置的填充因子会降低数据库的读取性能，而降低量与填充因子设置值成反比。 例如，当填充因子的值为 50% 时，数据库的读取性能会降低两倍。 填充因子只在创建索引时执行；索引创建后，当表中进行数据的添加、删除或更新时，不会保持填充因子。 如果试图在数据页上保持额外的空间，则将有悖于使用填充因子的本意，因为随着数据的输入，SQL Server 必须在每个页上进行页拆分，以保持填充因子指定的空闲空间百分比。 因此，如果表中的数据进行了较大的变动，添加了新数据，可以填充数据页的空闲空间。在这种情况下，可以重新创建索引，重新指定填充因子，以重新分布数据。 /* 在数据库student中的s表中sno列上创建名为 stud_id_index的聚集索引。*/ USE student GO CREATE CLUSTERED INDEX stud_id_index ON s(sno) GO 例 使用SQL语句，对表C创建列CNAME惟一性非聚集索引。 CREATE UNIQUE INDEX ix_c ON c(cname) 先创建聚集索引，再创建非聚集索引 以下示例使用 DROP_EXISTING 选项在s表的 sname 列上删除并重新创建现有索引。还设置了 FILLFACTOR 和 PAD_INDEX 选项。 USE student; GO CREATE NONCLUSTERED INDEX IX_1 ON s(sno) WITH ( FILLFACTOR = 80, PAD_INDEX = ON, DROP_EXISTING = ON); GO 创建聚集索引要求数据库中的可用空间大约为数据大小的1.2倍。该空间不包括现有表占用的空间。将对数据进行复制以创建聚集索引，旧的无索引数据将在索引创建完成后删除。 使用DROP_EXISTING子句时，聚集索引所需的空间数量与现有索引的空间要求相同。所需的额外空间可能还受指定的FILLFACTOR的影响。 在SQL Server 2005中创建索引时，可以使用SORT_IN_TEMPDB选项指示数据库引擎在tempdb中存储中间索引排序结果。如果tempdb在不同于用户数据库所在的磁盘集上，则此选项可能减少创建索引所需的时间，但会增加用于创建索引的磁盘空间。除了在用户数据库中创建索引所需的空间外，tempdb还必须有大约相同的额外空间来存储中间排序结果。 在SQL Server 2005中，还可以在计算列和视图上创建索引。 在视图上创建唯一聚集索引可以提高查询性能，因为视图存储在数据库中的方式与具有聚集索引的表的存储方式相同。 UNIQUE或PRIMARY KEY只要满足所有索引条件，就可以包含计算列。具体说来就是，计算列必须具有确定性，必须精确，且不能包含text、ntext或image列。 例如，下面的SQL语句是在数据库中创建一个表t1，然后在此表的计算列c上创建一个 索引： USE student CREATE TABLE t1 (a int, b int, c AS a*b) GO CREATE INDEX