sqlsever 关于索引

索引：

在sqlserver中，存储的单位最小是页，页是不可再分的
B树：初衷是减少对磁盘的扫描次数，如果一个表或者索引没有使用B树（对于没有聚集索引的表是使用 Heap 堆进行存储的），那么查找一个数据，需要在整个表包含的数据库页中进行全盘扫描，这大大增加了IO负担
打包sqlserver使用B树进行存储，只仅仅需要将B树根节点存入内存，经过几次查找后找到存放所需的数据在子页包含的节点的页，这样避免了进行全盘扫描，从而提高了性能

如果表中没有任何索引，则以堆进行存放，

可以通过在其上加上 聚集索引(以B树存放)来展现对IO的减少

 例如：

--开启IO数量
SET STATISTICS IO ON
SELECT * FROM student

--给表建立聚集索引
--
CREATE INDEX test_index ON student(id)
SET STATISTICS IO ON
SELECT * FROM student WHERE id = 5

 

--开启Io
SET STATISTICS IO ON 

--开启cpu时间
SET STATISTICS TIME ON

 

聚集：在sqlserver中：聚集的作用是将一列或是多列的物理顺序改变为和逻辑顺序相一致

聚集索引改变的是其所在表的物理存储顺序，所以每个表只能有一个聚集索引

在sqlserver中:聚集索引的存储是以B树存储，B树的叶子直接存储聚集索引的数据
非聚集索引：并不改变其所在表的物理结构，而是额外生成一个聚集索引的B树结构，但叶子节点是对于其所在表的引用，
这个引用分为两种：如果其所在表没有聚集索引，则引用行号，如果其所在表已经有了聚集索引，则引用聚集索引的页

非聚集索引需要额额外的空间进行存储，按照被索引列进行聚集索引，并在B树的叶子节点包含指向非聚集索引所在表的指针

与聚集索引不同的是：B树的叶子节点存的是指向堆或聚集索引的指针

非聚集索引仅仅包含原表中非聚集索引的列和指向实际物理表的指针。

如果表的物理结构发生改变，比如加上或者删除聚集索引，则所有非聚集索引都需要重建，这个对于性能的损耗是相当大的，所以最好先建立聚集索引，再建立对应的非聚集索引

大多数情况下：聚集索引的速度比非聚集索引都稍微快点，因为聚集索引的B树叶子节点直接存储数据，而非聚集索引还需要额外通过叶子节点的指针找到数据

还有：对于大量连续数据查找，非聚集索引性能十分不好，因为非聚集索引需要在非聚集索引的B树中找到每一行的指针，再去其所在表找数据，性能大打折扣，还不如不加非聚集索引

因此大多数情况下，聚集索引的速度都快于非聚集索引，但聚集索引只有一个，则一定要选择好使用哪个或者哪些列作为聚集索引

 

索引的使用：
索引的使用不需要显式使用，建立索引后查询分析器会自动找出最短路径使用索引

但是随着数据量的增长，产生了索引碎片，很多存储的数据进行了不适当的夸页，会造成碎片(跨页，碎片，填充因子) 这时候需要重新建立索引以加快性能

可以通过DMV语句查询其索引情况

SELECT index_type_desc,alloc_unit_type_desc,avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count,record_count,avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('Yip_20160322'),OBJECT_ID('student'),NULL,NULL,'Sampled')

当碎片量超过40%的时候（查看该列：avg_fragmentation_in_percent）往往需要重建索引，这样可以减少IO

可以这样重建索引：
ALTER INDEX idx_student_Id ON Student REBUILD

和更新表的统计信息

UPDATE STATISTICS Student

使用索引后的代价：

1 当表建立索引后，就以B树来存储数据，所以当对其进行更新插入删除时，就需要页在物理上的移动以调整B树，因此，会带来性能下降，
2 对于非聚集索引，当更新表后，非聚集索引也需要更新，相当于多更新了N(N=非聚集索引数量)个表，因此也下降了性能

3 通常可以将非聚集索引全部放在另外一个独立硬盘上，这样可以分散IO，这样可以使查询并行