三、索引优化（5）索引设计指南

　　通过前面的几篇文章，我们初步了解了索引的一些特征，这将有助于设计出最佳的索引。

 

一、从数据库的角度进行设计

1. 索引的宽度

　　索引宽度，即索引的键占用了多少个字节。影响索引宽度有2个因素：一是引用的列，二是索的数据类型。原则上，索引应保持较窄，就是说，列要尽可能少，列的数据类型要尽可能精简。

　　不能将 ntext、text、image、varchar(max)、nvarchar(max) 和 varbinary(max) 数据类型的列指定为索引键列。不过，varchar(max)、nvarchar(max)、varbinary(max) 和 xml 数据类型的列可以作为非键索引列参与非聚集索引。

　　设计时注意以下几类数据类型：

（1）日期型 vs. 日期时间型

 　　旧版本的SQL Server只有日期时间型datetime（1753-01-01 00:00:00.000到 9999-12-31 23:59:59.999，8个字节）和smalldatetime（范围从1900-01-01 00:00到 2079-06-06 23:59，4个字节）。SQL Server 2008 新增了多种日期类型，其中包括date（范围从0001-01-01 到 9999-12-31，3个字节）和time。如果某个列经常只需要查询日期，建议将实际业务的datetime拆分为date和time类型的2个列，并在date类型的列上建立索引。

 

（2）整型 vs. 字符型

　　有些编号，例如客户ID，如果业务上没有特别要求，那么使用整型是最佳选择。因为，整型的范围从 -2,147,483,648 到 2,147,483,647 ，占4个字节，而同样范围的字符型却需要10个字节。此外，对于小范围的编号，smallint也是不错的选择，它的范围从 -32,768 到 32,767 ，只占2个字节。

 

（3）Uniqueidentifier列（GUID）vs. IDENTITY列

　　有些程序员希望每行有一个唯一标识，于是将GUID作为标识。如果在 SQL Server 的表定义中将列类型指定为 uniqueidentifier，则列的值就为 GUID 类型，占16个字节。

CREATE TABLE Table1( MyID uniqueidentifier, MyName varchar(10) ) insert into Table1 values (newid(),'noname') select * from Table1

　　从程序设计的角度来看，上述设计并无不妥。但如果在这个列上建立索引（尤其是聚集索引），对性能可能有很大的影响。建议改用IDENTITY列。

　　首先，GUID占用16个字节；而IDENTITY列为int类型，仅占用4个字节。对比之下，后者的索引宽度可以缩减12个字节。

　　其次，GUID是随机的，导致索引的分页现象非常严重；而IDENTITY列的值一般是连续增长，因此不会造成索引分页。

CREATE TABLE Table2 ( MyId int IDENTITY, MyName varchar(10) )  insert into Table2 (MyName) Values ('noname') select * from Table2  dbcc checkident('Table2', NORESEED)

 

　　或者，使用IDENTITY函数。

SELECT IDENTITY(int, 1,1) AS ID_Num INTO NewTable FROM OldTable

 

 

2. 索引维护的开销

（1）权衡读与写的数量

　　使用多个索引可以提高数据的查询（例如 SELECT 语句）的性能，因为查询优化器有更多的索引可供选择，从而可以确定最快的访问方法。

　　但是，一个表如果建有大量索引会影响 INSERT、UPDATE、DELETE 和 MERGE 语句的性能，因为当表中的数据更改时，所有索引都须进行适当的维护。

　　避免对经常更新的表进行过多的索引。

 

（2）索引尽可能窄

　　避免添加不必要的列。添加太多索引列可能对磁盘空间和索引维护性能产生负面影响。

3. 唯一索引

　　检查列的唯一性。在同一个列组合的唯一索引而不是非唯一索引提供了有关使索引更有用的查询优化器的附加信息。 

 

 

4. 聚集索引优化

　　请保持较短的索引键长度。另外，对唯一列或非空列创建聚集索引可以使聚集索引获益。

 

 

二、从查询的角度

　　设计索引时，应考虑以下查询准则：

（1）小表的索引

　　对小表进行索引可能不会产生优化效果，因为查询优化器在遍历用于搜索数据的索引时，花费的时间可能比执行简单的表扫描还长。因此，小表的索引可能从来不用，却仍必须在表中的数据更改时进行维护。

 

（2）索引覆盖

　　为经常用于查询中的谓词和联接条件的所有列创建非聚集索引。

　　涵盖索引可以提高查询性能，因为符合查询要求的全部数据都存在于索引本身中。也就是说，只需要索引页，而不需要表的数据页或聚集索引来检索所需数据，因此，减少了总体磁盘 I/O。例如，对某一表（其中对列 a、列 b 和列 c 创建了组合索引）的列 a 和列 b 的查询，仅仅从该索引本身就可以检索指定数据。

 

（3）降低索引维护的开销

　　将插入或修改尽可能多的行的查询写入单个语句内，而不要使用多个查询更新相同的行。仅使用一个语句，就可以利用优化的索引维护。

 

（4）索引的效率

　　评估查询类型以及如何在查询中使用列。例如，在完全匹配查询类型中使用的列就适合用于非聚集索引或聚集索引。

　　在列中检查数据分布。通常情况下，为包含很少唯一值的列创建索引或在这样的列上执行联接将导致长时间运行的查询。这是数据和查询的基本问题，通常不识别这种情况就无法解决这类问题。例如，如果物理电话簿按姓的字母顺序排序，而城市里所有人的姓都是 Smith 或 Jones，则无法快速找到某个人。

 

（5）索引中列的顺序

　　如果索引包含多个列，则应考虑列的顺序。用于等于 (=)、大于 (>)、小于 (<) 或 BETWEEN 搜索条件的 WHERE 子句或者参与联接的列应该放在最前面。其他列应该基于其非重复级别进行排序，就是说，从最不重复的列到最重复的列。

　　例如，如果将索引定义为 LastName、FirstName，则该索引在搜索条件为 WHERE LastName = 'Smith' 或 WHERE LastName = Smith AND FirstName LIKE 'J%' 时将很有用。不过，查询优化器不会将此索引用于基于 FirstName (WHERE FirstName = 'Jane') 而搜索的查询。