索引 - 常规索引设计指南
2013-02-19 14:04 BIWORK 阅读(343) 评论(0) 编辑 收藏 举报数据库注意事项
设计索引时, 应考虑以下数据库准则:
- 一个表如果建有大量索引会影响 INSERT, UPDATE, DELETE 和 MERGE 语句的性能. 因为当表中的数据更改时, 所有索引都须进行适当的调整.
-
避免对经常更新的表进行过多的索引, 并且索引应保持较窄. 就是说,列要尽可能少.
-
使用多个索引可以提高更新少而数据量大的查询的性能. 大量索引可以提高不修改数据的查询（例如 SELECT 语句）的性能, 因为查询优化器有更多的索引可供选择, 从而可以确定最快的访问方法.
-
-
对小表进行索引可能不会产生优化效果, 因为查询优化器在遍历用于搜索数据的索引时, 花费的时间可能比执行简单的表扫描还长. 因此, 小表的索引可能从来不用, 但仍必须在表中的数据更改时进行维护.
-
视图包含聚合, 表联接或聚合和联接的组合时, 视图的索引可以显著地提升性能. 若要使查询优化器使用视图, 并不一定非要在查询中显式引用该视图. 有关详细信息,请参阅 设计索引视图.
- 使用数据库引擎优化顾问来分析数据库并生成索引建议. 有关详细信息, 请参阅 了解数据库引擎优化顾问.
查询注意事项
设计索引时, 应考虑以下查询准则:
- 为经常用于查询中的谓词和联接条件的所有列创建非聚集索引. 避免添加不必要的列, 添加太多索引列可能对磁盘空间和索引维护性能产生负面影响.
-
涵盖索引可以提高查询性能, 因为符合查询要求的全部数据都存在于索引本身中. 也就是说, 只需要索引页, 而不需要表的数据页或聚集索引来检索所需数据, 因此,减少了总体磁盘 I/O. 例如, 对某一表（其中对列 a, 列 b 和列 c 创建了组合索引）的列 a 和列 b 的查询,仅仅从该索引本身就可以检索指定数据.
-
将插入或修改尽可能多的行的查询写入单个语句内, 而不要使用多个查询更新相同的行. 仅使用一个语句, 就可以利用优化的索引维护.
- 评估查询类型以及如何在查询中使用列. 例如, 在完全匹配查询类型中使用的列就适合用于非聚集索引或聚集索引. 有关详细信息, 请参阅 查询类型和索引.
列注意事项
设计索引时, 应考虑以下列准则:
-
对于聚集索引, 请保持较短的索引键长度. 另外, 对唯一列或非空列创建聚集索引可以使聚集索引获益. 有关详细信息, 请参阅 聚集索引设计指南.
-
不能将 ntext、text、image、varchar(max)、nvarchar(max) 和 varbinary(max) 数据类型的列指定为索引键列.
不过，varchar(max)、nvarchar(max)、varbinary(max) 和 xml 数据类型的列可以作为非键索引列参与非聚集索引. 有关详细信息, 请参阅 具有包含列的索引.
-
xml 数据类型的列只能在 XML 索引中用作键列. 有关详细信息, 请参阅 XML 数据类型列的索引.
-
检查列的唯一性. 在同一个列组合的唯一索引而不是非唯一索引提供了有关使索引更有用的查询优化器的附加信息. 有关详细信息, 请参阅 唯一索引设计指南.
-
在列中检查数据分布. 通常情况下, 为包含很少唯一值的列创建索引或在这样的列上执行联接将导致长时间运行的查询. 这是数据和查询的基本问题, 通常不识别这种情况就无法解决这类问题. 例如, 如果物理电话簿按姓的字母顺序排序, 而城市里所有人的姓都是 Smith 或 Jones, 则无法快速找到某个人. 有关数据分布的详细信息, 请参阅 使用统计信息提高查询性能.
-
考虑对具有定义完善的子集的列（例如, 稀疏列, 大部分值为 NULL 的列, 含各类值的列以及含不同范围的值的列）使用筛选索引. 设计良好的筛选索引可以提高查询性能, 降低索引维护成本和存储成本.有关详细信息, 请参阅 筛选索引设计准则.
-
如果索引包含多个列, 则应考虑列的顺序. 用于等于 (=), 大于 (>), 小于 (<) 或 BETWEEN 搜索条件的 WHERE 子句或者参与联接的列应该放在最前面. 其他列应该基于其非重复级别进行排序, 就是说, 从最不重复的列到最重复的列.
例如, 如果将索引定义为 LastName, FirstName, 则该索引在搜索条件为 WHERE LastName = 'Smith' 或 WHERE LastName = Smith AND FirstName LIKE 'J%' 时将很有用. 不过, 查询优化器不会将此索引用于基于 FirstName (WHERE FirstName = 'Jane') 而搜索的查询.
- 考虑对计算列进行索引. 有关详细信息, 请参阅 为计算列创建索引.
索引的特征
