索引设计的建议：
一.检查where子句和连接条件列
当一个查询提交到sql server时，查询优化器尝试为查询中引用的所有表查找最佳的数据访问机制，
一下是它所进行的方式。
1.优化器识别Where子句和连接条件中包含的列、
2.接着优化器检查这些列的索引.
3.优化器通过从索引上维护的统计确定子句的选择性(也就是返回多少行),评估每个索引的有效性
4.最终,优化器根据前面几个步骤中收集的信息,评估读取所限定的行开销最低的方法.
下面看个例子: Table_1表内 又 30000条数据
执行查询:
查看结果:
逻辑读取的次数为 95次.
下面加上where 条件句, where 的列 是 含有聚集索引的.
结果:
很明显, 逻辑读 次数减少了两次.
下面看 where 条件句,where 的列 不含有索引的:
查询的结果:
总结:
where 子句列 帮助 优化器选择 一个对 查询最优的索引操作. 这也使用于 两个表之间的连接条件中使用的列.
优化器 查找在 where子句 或连接条件列上的索引,如果可用,考虑使用该索引 来从表中检索行,查询优化器在执行一个查询时,
考虑where子句或连接条件列上的索引. 因此,在where子句或连接条件中 频繁使用的列上有 索引将 帮助查询优化器避免基本表的扫描.
注意: 当一个表中的数据总量非常小以至于可以 放入一个单独的页面(8kb)时,表扫描可能比索引查找工作 得更好,
二: 使用 窄索引
应该避免在索引中使用 宽数据类型 的列. 比如 字符串类型(char,varchar,nchar ,nvarchar)的列有时候可能和二进制类型一样大.
窄索引可以再8kb的索引页面中 容纳比 宽索引 更多的行,这将有一下效果:
1.减少 i/o数量 (读取更少的8kb页面)
2.使数据库缓存更有效,因为 sql server 可以缓存更少的索引页面,从而减少内存中的索引页面所需的逻辑读操作.
3.减少数据库的存储空间.
下面看个例子:
表a 
说明所有的 索引 总大小为 4*27 byte, 一个索引页面(8kb)完全可以容得下.
sys.indexes系统表 在每个数据库中保存,包含了数据库中所有索引的基本信息.
DMF sys.dm_db_index_physical_stats 包含了关于索引上统计的更详细信息.
下面看sql语句:
select i.name,i.type_desc,s.page_count,s.record_count,s.index_level from sys.indexes i join sys.dm_db_index_physical_stats(DB_ID(N'test'),OBJECT_ID(N'dbo.a'),null,null,'DETAILED') as s on i.index_id=s.index_id where i.OBJECT_ID=OBJECT_ID(N'dbo.a')
获取如下 结果:
说明 索引页数 是1页, index_leval =0说明是 聚集索引.
下面使用宽索引 的一个例子:
b表结果如下: name列有一个 非聚集索引,索引名字为: IX_b 由于name 的数据类型为 char(500),b内数据有23行, 说明非聚集索引占用的大小事 23*500byte,那么超出了一个页面的大小,
按照预测,name的索引将存在两个索引页面, 下面用sql语句来证实这个结论.
sql语句:
select i.name,i.type_desc,s.page_count,s.record_count,s.index_level from sys.indexes i join sys.dm_db_index_physical_stats(DB_ID(N'test'),OBJECT_ID(N'dbo.b'),null,null,'DETAILED') as s on i.index_id=s.index_id where i.OBJECT_ID=OBJECT_ID(N'dbo.b')
查询结果如下:
果然,看到 IX_b record_count =23的 行, 它的 page_count是2 ,说明我们之前的猜想是正确的.
这就是宽索引的劣势.
三:检查列的唯一性.
在一个具有很小范围的可能值的列(如性别,只有,f和m)上创建索引,对性能是没有很大帮助的.
因为查询优化器不能使用索引有效的减少返回的行.
考虑只有两个唯一值 的 性别列(F和M).当执行一个 具有使用 性别列的where子句查询时,最终从表中得打很大数量的行,导致开销很大的表,或聚集索引扫描.
结论:
所以 使用where子句中的列 具有 大量的唯一行,一限制访问的行数,是个首选的方案. 应该在 这些列上 创建索引,来帮助 查询优化器
访问小的结果集.
而且,在创建多个列上的索引时(符合索引),列的顺序是有关系的.在某些情况下,先使用最有选择性的列将使索引行的列更有效率.
可以使用下面的语句 来判断 列的选择性.
test1 自己随意定义的表 select count(distinct EventClass) as 不同的值, count(EventClass) as 多少行, cast(count(distinct EventClass) as decimal) as 选择性 from test1
结果如下:
具有最高的唯一值数量(或选择性)的列 是 where子句 或连接条件 中引用的索引的 最佳候选.
四:考虑索引类型
`sql server主要有两种索引，聚集索引和非聚集索引，这两种类型的索引 都为 B-树 结构。两者之间的主要区别 是
聚集索引的叶子页面 是表的数据页面，因此数据和其指针的顺序相同，这意味着 聚集索引就是该表。
下一篇 ，主要讲述这两种索引。