- Heap就是数据页之间没有顺序以及数据页中数据行之间没有顺序的表，是最简单的表结构，数据行被加到数据页第一个可用位置，如果当前页没有足够空间的话，就创建额外的数据页；SQL Server在称为Index Allocation Map的内部结构中使用一个条目跟踪可用的数据页，这个条目的index_id=0
- 在堆上执行的操作：
- INSERT：每个新行都放在第一个可用页上
- UPDATE：数据行即可以仍然在同一页上（如果大小合适的话），或者被从当前页删除，并放到第一个可用页上
- DELETE：释放当前页的空间，数据没有被覆盖，空间仅仅是被标记为可以被重用（即数据不会立即被删除，只有在需要在这块被删除的空间上分配新数据时，才会擦除被删除的数据）
- SELECT：如果没有索引的话，需要读取整张表
- forwarding pointers就是在数据行被移动时，在原来的位置留下来一个引用，这个引用指示数据行被移动到哪个位置，这样就不需要更新其他指向这行的索引（这些索引还是指向这个位置，而这个位置告诉这些索引数据行的位置），当数据行再次移动的时候，更新这个引用指向数据行新的位置（而不是在新的位置在留下一个引用，指向数据行这次被移到了什么位置，这样查询性能不会引用跟随forwarding pointers链而下降），forwarding pointers随着时间的推移会带来性能问题
- 可以通过移除forwarding pointers并更新其他索引来提高性能，在SQL Server2008之前没有非常简单的方式，因为之前只能重建索引，不能重建表，SQL Server2008引入了重建表能力（ALTER TABLE WITH REBUILD）
- 表建立聚集索引后，表中数据页、数据页上的行都有确定的顺序，每个表只能建一个聚集索引（这个索引可以一个或者多个列上创建），在Index Allocation Map中创建一个条目指向聚集索引，这个条目的index_id=1；通常都误认为使用聚集索引的数据页物理上按顺序存储的，实际上很少是这样，数据页之间也是双向链接的
- 聚集索引上的操作：
- INSERT：每个新行都放在正确的逻辑位置，可能会涉及到拆分数据页
- UPDATE：如果数据合适并且聚集键没有被修改的话，数据行还保留在原来的位置；如果数据行不在合适的话，就拆分数据页；如果聚集键修改的话，移除数据行，并放在正确的逻辑位置上
- DELETE：释放空间，并将数据标记未使用
- SELECT：和聚集键相关的查询可以直接找到或者扫描，避免排序
- 如果聚集索引没有指定为unique，SQL Server将向聚集键添加值保证每行是唯一的，该值称为uniqueifier；如果索引是唯一的话，应该总是将它们指定为unique的
- 可以通过CREATE CLUSTERED INDEX或者指定PRIMARY KEY的方式创建聚集索引；注意PRIMARY KEY和聚集索引的区别：PRIMARY KEY是约束，是索引支持的逻辑概念，而索引可以是聚集索引也可以是非聚集索引，也就是说创建主键，一定创建了索引，但可能是非聚集索引，不是必须是聚集索引，默认创建主键的时候，如果表没有聚集索引的话，就在主键列上建立聚集索引，可以通过NONCLUSTERED关键修改默认行为，使其创建非聚集索引
- 通过DROP INDEX语句删除聚集索引；对于内部支持约束的索引，需要通过删除约束来删除索引，比如使用主键作为索引的需要使用ALTER TABLE语句将主键删除来删除聚集索引；删除聚集索引后，数据不会丢失，表被在堆中重新组织
- ALTER INDEX只可以执行次要的操作，比如rebuilt或者reorganized（只是重新组织索引叶子节点的level），不允许restructuring索引，即不允许添加或者移除组成索引键的数据列，并且不能将索引移到另外一个FileGroup中；可以通过CREATE INDEX...WITH DROP EXISTING改变索引的结构，这个命令是替换原有的索引，这个命名不能将聚集索引修改为非聚集索引，反之亦然；可以通过在ALTER INDEX语句中包含DISABLE选项禁用索引，一旦禁用索引不允许访问表中的数据，直到rebuilt
- 索引页中的Free Space可以显著提高索引更新性能，因为如果插入索引时没有足够的空间时，需要新索引页，需要拆分原有索引页，但是Free Space损害了查询的性能，FILLFACTOR指定页面填充率（默认是0，表示100%占满，如果指定70的话，表示填充70%，剩余30%），FILLFACTOR仅仅应用于叶子节点，如果启用了PAD_INDEX的话，则也应用于非叶子节点
- 好的聚集索引键的特征：
- 短，索引键必须限制在16列，900字节，好的索引键要比这短的多
- 静态，索引键基于的数据不改变，这就是主键经常作为聚集索引键的原因之一，因为对索引键的修改需要移动数据行
- Increasing，键值不断增加，帮助插入，这样新插入的数据都放在表尾部，最小化索引碎片、数据页拆分、减少页缓存需要的内存空间
- 唯一，这样不需要添加uniqueifier，这也说明将值是唯一的列声明为unique的重要性
- int 和bitint是最好的聚集键，尤其是和IDENTITY约束一起使用的时候；uniqueidentifier(即GUID)作为聚集键的问题是他们比int类型size要大，更主要的是他们的值是随机的不连续，会造成许多索引碎片；字符类型作为聚集键的问题是他们排序性能不佳，而且在应用程序中他们的值经常改变；日期类型大小、排序性能都非常好，问题是不唯一，经常用来进行日期范围查询
- 可以在视图上创建聚集索引，称为indexed view，其他数据库称为materialized view，这种类型的视图已经被物化，即不再是存储一个SELECT语句声明，而是存储实际的数据；对于维护提前计算好的聚合值和join来说，indexed view非常有用；indexed view就是一种建立了聚集索引的特殊类型的表，该表不是通过通常的方式建立的，而是通过SELECT语句建立的，当底层表数据修改时，indexed view中的数据也会被修改；可以显著提高查询性能，但是会降低增删改的性能，对于经常查询很少修改的数据非常有用
- 为了在查询中使用indexed view，需要在创建视图和使用视图时，设置一系列的SET选项，尤其是CONCAT_NULL_YIELDS_NULL和QUOTED_IDENTIFIER设置；为了视图上建立索引，视图必须是确定的，即除非底层表进行修改，否则视图必须总是返回相同的数据，比如不能包含使用SYSDATETIME函数的列；创建视图时，必须指定SCHEMABINDING选项，该选项阻止底层表在视图还存在的情况下修改自己架构；Enterprise和Data Center版本自动利用indexed views特性，而其他版本则需要NOEXPAND查询提示
发表于
2012-12-22 23:20
马啸西风
阅读(463)
评论()
收藏
举报
