6. 设计、创建和维护索引[w1]
|
本章主题 l 索引的基本概念 l 索引的设计准则 l 索引的类型 l 聚集索引VS非聚集索引 l 唯一索引VS非唯一索引 l 单列索引VS多列索引 l 填充因子 l 创建索引 l 删除索引 l 索引的评估和分析 l 索引的维护操作 l 为计算字段创建索引
速度是查询的首要考虑因素！！
|
索引最重要的功能：
l 确保数据的唯一性
l 提高数据的访问速度
准备工作：在示例代码ch7目录中，数据库(IndexTuneDemo)
文件名：IndexTuneDemo_Data.mdf、IndexTuneDemo_Log.ldf
6.1. 索引的基本概念
提高数据的访问速度
只要为适当的字段创建索引，将能大幅度提高下列操作的速度：
Ø 查询操作中的WHERE语句的数据提取
n 示例1：SELECT * FROM employee WHERE EmpID=’ATE3902F’，如果事先为EmpID字段创建索引，将提高查询的速度。
n 示例2：SELECT * FROM employee WHERE Salary BETWEEN 60000 AND 70000 (或SELECT * FROM employee WHERE Salary >=60000 AND Salary<=70000)，如果事先为Salary字段创建索引，将提高查询的速度。
Ø 查询操作中ORDER BY语句的数据排序
n 示例1：SELECT name FROM employee ORDER BY name，如果事先为字段name创建索引，将能提高查询的速度
Ø GROUP BY 语句的数据分组
n 示例1：SELECT department,avg(salary) FROM employee GROUP BY department，如果事先字段department创建索引，将能提高查询的速度
Ø 连接表
n 示例1：SELECT * FROM customers INNER JOIN orders ON customers.customerID=orders.customerID,如果分别为表customer和orders的customerID字段建立索引，将能提高使表的连接速度大幅度提高。
n 注：SQL Server会自动为PRIMARY KEY约束和UNIQUE约束的字段创建唯一索引，但不会为FOREIGN KEY约束的字段创建索引！
确保数据的唯一性
Ø SQL Server会自动为PRIMARY KEY约束和UNIQUE约束的字段创建唯一索引。
Ø 可以自行创建一个与约束无关的唯一索引
Ø 只有唯一索引（Unique Index）才能确保键列的内容绝对不会重复。
6.2. 索引的设计准则
Ø 经常被用来搜索数据记录的字段
n 提醒：用来搜索数据记录的字段最好是识别度较高的字段（也就是数据内容重复性低的字段），如：按工资来搜索用得就不多。
n 推荐：“覆盖查询”所提高的执行速度最显著。如：给字段A、B、C的组合创建一个索引，而某个查询只提取字段B、C的内容，则该查询是一个“覆盖查询”。
Ø PRIMARY KEY约束所定义的作为主键的字段（该索引自动由SQL Server创建）
Ø 应用UNIQUE约束的字段（该索引自动由SQL Server创建）
Ø FOREIGN KEY约束所定义的作为外键的字段。
Ø 在查询中用来连接表的字段在讲到连接表操作的SQL语句时会详讲
Ø 经常用来作为排序基准的字段
为什么随意创建索引不好？原因如下：
Ø 索引显然会占用磁盘空间，创建不必要的索引只会形成浪费。
n 建议：最好不要为数据内容超过20个字节的字段创建索引。
Ø 如果索引过多，将因为必须更新相关的索引而使得添加、修改和删除数据记录的速度减慢
n 建议：如果一个表内容很少变动，但经常被查询，则为它多建几个索引可以提高速度。
Ø 尽量不要为数据内容重复率很高的字段创建索引，毕竟它所带来的效益很少。
Ø 数据记录越多，索引提高数据访问效率的幅度就越明显。
6.3. 索引的类型
Ø 以存储结构区分
n 聚集索引（Clustered Index）
n 非聚集索引（NonClustered Index）
Ø 以数据的唯一性区分
n 唯一索引（Unique Index）
n 非唯一索引（NonUnique Index）
Ø 以键列的个数区分
n 单列索引
n 多列索引
小词汇：
创建索引的字段称为“键列”
键列字段在索引中的数据称这“键值”
6.4. 聚集索引VS非聚集索引
聚集索引
表中的数据记录实际存放的次序将会与聚集索引中相对应的键值的实际存放次序完全相同。--也就是会改变数据记录的实际存放次序！想想看它的利弊？
下列数据访问操作非常适合使用聚集索引：
Ø 如果某字段所包含的有差别的数据较少。如：血型、部门、州名称
Ø 使用BETWEEN、>、>=、<、<=等运算符返回介于特定范围的数据记录的查询
Ø 会按特定次序访问字段
Ø 会返回大结果集的查询
Ø 经常使用在查询的连接条件或GROUP BY语句中的字段。
Ø 使用聚焦索引搜索唯一键列的速度也非常快。
注意事项：
Ø 在每一个表最多只能拥有一个聚集索引。为什么？能否说出理由
Ø 聚集索引可以是唯一索引或非唯一索引。
Ø 聚集索引的键列的数目越少越好。
Ø 如果一个字段的内容经常变动，则非常不适合给这个字段创建聚集索引。因为键值一变动，会导致整个表的所有数据记录必须重排一次。
非聚集索引
非聚集索引不影响表中数据记录实际排列的顺序。
一个表可以拥有多个非聚集索引。（很显然）
下列的数据访问操作非常适合使用非聚集索引：
Ø 如果某字段所包含的有差别数据非常多，则非常适合为该字段创建非聚集索引。
Ø 某字段经常使用于查询的搜索条件。
Ø 覆盖查询
注意事项：
Ø 每一个表最多能够拥有249个非聚集索引
Ø 非聚集索引可以是唯一索引或非唯一索引。
6.5. 唯一索引VS非唯一索引
建立唯一索引后，当添加或修改数据时，如果发生唯一索引的源字段添加或修改后的值与其他现存的数据发生重复的情形，SQL Server后通知违反唯一性错误，并取消这项添加或修改操作。
如果某字段出现两个以上的NULL值，则认为是重复的
SQL Server会自动为PRIMARY KEY约束和UNIQUE约束的字段创建唯一索引（这个我们以前讲过）
是否忽略重复的键值
Ø 如果“要”忽略，则当一次性添加多条记录时，如果出现有重复键值的记录，则忽略这些具有重复键值的记录，其它记录照常添加。
Ø 反之，所有记录都不被添加。
6.6. 单列索引VS多列索引
Ø 单列索引：为某单一字段添加索引
Ø 多列索引：为多个字段的组合创建索引
注意事项：
Ø 最多可以为16个字段的组合创建一个多列索引，而且这些字段的总长度不能超过900个字节。
Ø 多列索引的各个字段必须来自同一个表。
Ø 查询的WHERE语句务必引用多列索引的第一个字段，如此才能让查询优化器使用该多列索引。
Ø 既能提高查询速度又能减少表索引的数目，是使用多列索引的最高境界。
6.7. 创建索引
填充因子：就是填满索引页的比率，设置值越高，可用空间越少，设置值越低可用空间越多。涉及到“页拆分”技术，大家了解概念。
使用企业管理器
使用创建索引向导
使用CREATE INDEX命令
/* 档案名称:Demo71.sql */
USE Northwind
CREATE UNIQUE NONCLUSTERED
INDEX IX_Phone ON Customers (Phone)
WITH FILLFACTOR = 50
CREATE UNIQUE NONCLUSTERED
INDEX IX_LFHH
ON Employees (LastName,FirstName,HireDate,HomePhone)
WITH FILLFACTOR = 50,IGNORE_DUP_KEY
6.8. 删除索引
/* 档案名称:Demo72.sql */
USE Northwind
DROP INDEX Customers.IX_Phone, Employees.IX_LFHH
|
第6章 结束 |
浙公网安备 33010602011771号