第7章使用视图和索引优化查询技术方案.ppt

* * * * * * * * * * * * * * * * * * * * * * * * 一、索引的基本概念 3. 使用索引的准则 何时可不考虑建立索引？ 很少或从来不作为查询条件的列 在小表中的任何列 属于text、image、长度较大的char、varchar、binary等数据类型的列 当修改的性能需求远大于查询的性能需求时，不要创建索引 一、索引的基本概念 4. 索引的类型 按数据的物理存放顺序： 聚集索引:会改变表记录的物理存储顺序，使之与索引列的顺序完全相同（适用于范围搜索）。 非聚集索引:不改变表记录的存放顺序（适用于直接匹配单个条件的查询）。 按索引列取值的唯一性： 唯一索引:要求被索引列不能有重复值，也不能有两个NULL。 非唯一索引:不存在这一限制。 按索引列的个数：单索引、复合索引 二、创建索引 1. 创建索引的方法 系统自动建立索引 人工创建索引 T-SQL命令方式（掌握） 对象资源管理器方式 二、创建索引 2. 系统自动建立索引 添加主键约束时 系统会自动在表中生成一个聚集惟一索引。 在xsqk表的“学号”列上已创建了聚集惟一索引(名为PK_xsqk) 在xs_kc表的“学号和课程号”两列上已创建了聚集惟一索引 在kc表中的“课程号”列上已创建了聚集惟一索引 添加惟一性约束时 系统会自动在表中生成一个非聚集惟一索引。 二、创建索引 2. 系统自动建立索引 实例1：创建一个名为test的表，其结构定义如下。 use xscj create table test (课程号 smallint constraint pk_test primary key, 课程名 char(14) constraint uq_test unique, 授课教师 char(10) ) 生成惟一性聚集索引 生成惟一性非聚集索引 二、创建索引 3. 手工建立索引—(1)表索引 命令格式 create [ unique ] [ clustered | nonclustered ] index 索引名on { 表名 | 视图名 } ( 列名 [ asc | desc ] [ , ...n ] ) 说明： 一个表中只能创建1个聚集索引。 由于系统已自动在主键上创建了聚集索引，所以用户不能再创建，除非先删除已有的。 一个表中可以创建若干个非聚集索引。 默认值 二、创建索引 3. 手工建立索引—(1)表索引 实例2：在kc表的“课程名”列上创建一个非聚集索引，索引名为“ix_kcm”，并降序排列。 实例3：在kc表中，重新创建名为“ix_kcm”的索引，使其成为惟一性的非聚集索引。 create unique index ix_kcm on kc ( 课程名 desc) with drop_existing create index ix_kcm on kc ( 课程名 desc) 删除同名的原索引，重建新索引。 二、创建索引 3. 手工建立索引—(1)表索引 实例4：已知xs_kc表中的“学号”和“课程号”两列已设置为主键，主键约束名为“PK_xs_kc”，请重新创建这两列的组合索引，使学号降序排列，使索引页填满60%后就换新页进行填充。 create unique clustered index pk_xs_kc on xs_kc(学号 desc ,课程号) with pad_index, fillfactor=60, drop_existing 中间级索引页也按“填充因子”指定的填充度填充 叶级索引页按60%进行填充 二、创建索引 3. 手工建立索引—(1)表索引 课堂练习：已知xsqk表中的“学号”列已设置为主键，请在“姓名”和“所在系”两列上创建名为“ix_name_xi”的索引，按姓名降序排列，并使索引页填满60%后就换新页进行填充。 请考虑：是否要创建惟一性索引？能否创建聚集索引？ create index ix_name_xi on xsqk ( 姓名 desc,所在系 ) with pad_index, fillfactor=60 三、操作索引 1. 查看索引 命令格式： [exec] sp_helpindex {表名 | 视图名 } 用对象资源管理器方式 三、操作索引 2. 删除索引 命令格式： drop index {表名 . | 视图名 . } 索