（3.3）常用知识-索引使用、维护与填充因子
关键词：索引碎片，外部碎片，内部碎片，重建索引
1.索引与排序的关系
经过多番尝试，我发现，直接select * from table 默认是会按聚集索引来排序的。
那如果order by column ，column中有非聚集索引，排序用使用该索引吗？答案是不会，只有是聚集索引的时候才会根据其索引来排序。
2.索引的创建于维护
（1）创建聚集索引的选择（一般为主键自增ID）
- 数据变化的顺序应该尽可能考虑和聚集索引一致，如果不一致，则每次在数据变化时，都需要调整表中的记录存储顺序，这会大大降低存储效率。
- 列宽短：由于非聚集索引都会指向聚集索引，列宽越长则非聚集索引需要的空间越大，导致占页越多，引起更多页扫描。
- 尽量选唯一和列值不可为空的列。有益于精准定位
【1】多考虑列的顺序：如果有索引包含多个列的话，用于匹配和参与连接的列应该放在最前面，其他的列应该基于其非重复级别进行排序，也就是说，从最不重复的列到最重复的列：
【2】考虑列的宽度：对于复合索引，应该考虑把宽度小的列放在索引的前面，这样有利于数据检索
【3】考虑在计算列上建立索引（表达式/函数索引）：当确定需要计算或者转换时，可以在索引上就构建出 来，而不是在检索数据的时候做这件事
（2）统计信息
概念：统计是指相关列中值的分部情况的统计信息，是sql server能够正确评估索引的有效性，并在查询计划中选择正确索引的关键。查询优化器使用这些统计信息并通过估计使用索引评估查询的开销来确定最佳查询计划。数据引擎对列值（根据这些值创建统计信息）进行排序，并且根据这些值（在sql server2005最多只有200个间隔，以间隔分隔开）创建一个“直方图”。直方图指定有多少行精确匹配每个间隔值，有多少行在间隔范围内，以及间隔中值的密度大小或重复值的发生率。
当创建索引时，sql server自动为索引中的第1个列创建统计信息，当查询优化器在评估查询计划时，如果发现缺少相关的统计信息，也会自动创建（前提是将数据库的aoto create statistics选项开启（默认开启））。如果aoto create statistics选项关闭（创建时还是会创建统计信息，但是对于增删改则不会），则sql server会采用错误的执行计划，从而导致性能降低。
（3）索引维护
其主要考虑的问题是索引碎片（内部碎片和外部碎片）（不知道索引随便是什么的可以参考：http://www.cnblogs.com/CareySon/archive/2012/01/06/2313897.html）
索引碎片解决办法：
sys.dm_db_index_physical_stats的定义：
sys.dm_db_index_physical_stats ( {database_id | NULL } , { object_id | NULL } , { index_id | NULL | 0 } , { partition_number | NULL } , { mode | NULL | DEFAULT } )
--查看索引碎片
select
db_name(database_id) as '数据库名',
object_name(t.object_id) as '表名',
t.index_id as '索引id',
t1.index_name as '索引名称',
t1.type_desc as '索引类型',
t1.column_name as '索引列名',
t.partition_number as '当前索引所在分区',
t.page_count as '页统计',
t.avg_page_space_used_in_percent as '页使用率' ,
t.record_count as '页行记录数',
t.avg_record_size_in_bytes as '平均每条记录大小(B)',
t.avg_fragmentation_in_percent as '索引碎片比率',
t.fragment_count as '索引中的碎片数量',
t.avg_fragment_size_in_pages as '一个索引中碎片的平均页数'
from sys.dm_db_index_physical_stats(db_id('db_tank'),NULL,NULL,NULL,NULL) t
join (select distinct t3.object_id,t3.index_id,object_name(t3.object_id) as 'table_name',t3.name as 'index_name',t3.type_desc, t1.name as 'column_name'
from sys.columns t1 join sys.index_columns t2 on t1.column_id = t2.column_id AND t1.object_id = t2.object_id
join sys.indexes t3 on t2.index_id = t3.index_id AND t2.object_id = t3.object_id) t1
on t1.object_id = t.object_id AND t1.index_id = t.index_id
where object_name(t.object_id) = 'sys_users_goods'
---------------查看所有表中对应的索引名与索引列
select t3.object_id,object_name(t3.object_id) as 'table_name',t3.name as 'index_name',t3.type_desc, t1.name as 'column_name'
from sys.columns t1 join sys.index_columns t2 on t1.column_id = t2.column_id AND t1.object_id = t2.object_id
join sys.indexes t3 on t2.index_id = t3.index_id AND t2.object_id = t3.object_id
where object_name(t3.object_id) = 'sys_users_goods'
解释一下：
database_id--要查看索引所在数据库，当前数据库ID我们可以用db_id()函数来取到
object_id--要查索引所在表的id,比如我们要查看表T1，可以用object_id('T1')来取到该表的ID
index_id--要查看索引的索引号,该索引也同样可以用object_id('索引名')来获取
partition_number--对象中的分区号。partition_number为int类型。有效的输入包括索引或堆 的 partion_number 或 NULL
mode--在msdn中的解释是这样的：
函数的执行模式将确定为了获取此函数所使用的统计信息数据而执行的扫描级别。mode 被指定为 LIMITED、SAMPLED 或 DETAILED。此函数将遍历构成表或索引的指定分区的分配单元页链。
LIMITED：模式运行最快，扫描的页数最少。对于堆，它将扫描所有页，但对于索引，则只扫描叶级上面的父级别页。
SAMPLED：模式将返回基于索引或堆中所有页的 1％ 样本的统计信息。如果索引或堆少于 10,000 页，则使用DETAILED模式代替 SAMPLED。
DETAILED：模式将扫描所有页并返回所有统计信息。
从 LIMITED 到 DETAILED 模式，速度将越来越慢，因为在每个模式中执行的任务越来越多。若要快速测量表或索引的大小或碎片级别，请使用 LIMITED 模式。它的速度最快，并且对于索引的IN_ROW_DATA 分配单元中的每个非叶级别，不返回与其对应的一行。
对于碎片的解决办法
基本办法：
【1】当碎片>5% and <30%时： 使用 使用ALTER INDEX REORGANIZE整理碎片。
【2】当碎片>=30%时： ALTER INDEX REBUILD WITH(ONLINE) 来解决索引碎片。
基本上所有解决办法都是基于对索引的重建和整理，只是方式不同
1.删除索引并重建
这种方式并不好.在删除索引期间，索引不可用.会导致阻塞发生。而对于删除聚集索引，则会导致对应的非聚集索引重建两次(删除时重建，建立时再重建).虽然这种方法并不好，但是对于索引的整理最为有效
2.使用DROP_EXISTING语句重建索引
为了避免重建两次索引，使用DROP_EXISTING语句重建索引，因为这个语句是原子性的，不会导致非聚集索引重建两次，但同样的，这种方式也会造成阻塞
3.如前面文章所示，使用ALTER INDEX REBUILD语句重建索引
使用这个语句同样也是重建索引，但是通过动态重建索引而不需要卸载并重建索引.是优于前两种方法的，但依旧会造成阻塞。可以通过ONLINE关键字减少锁，但会造成重建时间加长.
4.使用ALTER INDEX REORGANIZE
这种方式不会重建索引，也不会生成新的页，仅仅是整理，当遇到加锁的页时跳过，所以不会造成阻塞。但同时，整理效果会差于前三种.
理解填充因子
重建索引固然可以解决碎片的问题.但是重建索引的代价不仅仅是麻烦，还会造成阻塞。影响使用.而对于数据比较少的情况下，重建索引代价并不大。而当索引本身超过百兆的时候。重建索引的时间将会很让人蛋疼.
填充因子的作用正是如此。对于默认值来说，填充因子为0（0和100表示的是一个概念）,则表示页面可以100%使用。所以会遇到前面update或insert时，空间不足导致分页.通过设置填充因子，可以设置页面的使用程度:
下面来看一个例子:
还是上面那个表.我插入31条数据，则占4页:
通过设置填充因子，页被设置到了5页上:
这时我再插入一页，不会造成分页:
上面的概念可以如下图来解释:
可以看出，使用填充因子会减少更新或者插入时的分页次数，但由于需要更多的页，则会对应的损失查找性能.
如何设置填充因子的值
如何设置填充因子的值并没有一个公式或者理念可以准确的设置。使用填充因子虽然可以减少更新或者插入时的分页，但同时因为需要更多的页，所以降低了查询的性能和占用更多的磁盘空间.如何设置这个值进行trade-off需要根据具体的情况来看.
具体情况要根据对于表的读写比例来看,我这里给出我认为比较合适的值:
1.当读写比例大于100：1时，不要设置填充因子，100%填充
2.当写的次数大于读的次数时，设置50%-70%填充
3.当读写比例位于两者之间时80%-90%填充
上面的数据仅仅是我的看法，具体设置的数据还要根据具体情况进行测试才能找到最优.
本文引用参考自：http://www.cnblogs.com/CareySon/archive/2012/01/06/2313897.html
