聚集索引
聚集索引是一种特殊索引,使数据按照索引的排序顺序存放表中。实际上重组了表中的标准。当数据按值的范围查询时,聚集索引就显得特别有用。当大量数据修改的时候,不再适合使用聚集索引。
建立聚集索引的思想
1、大多数表都应该有聚集索引或使用分区来降低对表尾页的竞争,在一个高事务的环境中,对最后一页的封锁严重影响系统的吞吐量。
2、在聚集索引下,数据在物理上按顺序排在数据页上,重复值也排在一起,因而在那些包含范围检查(between、<、<=、>、>=)或使用group by或order by的查询时,一旦找到具有范围中第一个键值的行,具有后续索引值的行保证物理上连在一起而不必进一步搜索,避免了大范围扫描,可以大大提高查询速度。
3、在一个频繁发生插入操作的表上建立聚集索引时,不要建在具有单调上升值的列(如IDENTITY)上,否则会经常引起封锁冲突。
4、在聚集索引中不要包含经常修改的列,因为码值修改后,数据行必须移动到新的位置。
5、选择聚集索引应基于where子句和连接操作的类型。
聚集索引的侯选列
1、主键列,该列在where子句中使用并且插入是随机的。
2、按范围存取的列,如pri_order>100 and pri_order < 200。
3、在group by或order by中使用的列。
4、不经常修改的列。
5、在连接操作中使用的列。
非聚集索引
非聚集索引,叶级页指向表中的记录,记录的物理顺序与逻辑顺序没有必然的联系。非聚集索引则更像书的标准索引表,索引表中的顺序通常与实际的页码顺序是不一致的。每个表只能有一个聚集索引,因为一个表中的记录只能以一种物理顺序存放。但是,一个表可以有不止一个非聚集索引。实际上,对每个表你最多可以建立249个非聚集索引。非聚集索引需要大量的硬盘空间和内存。另外,虽然非聚集索引可以提高从表中取数据的速度,它也会降低向表中插入和更新数据的速度。每当你改变了一个建立了非聚集索引的表中的数据时,必须同时更新索引。因此你对一个表建立非聚集索引时要慎重考虑。如果你预计一个表需要频繁地更新数据,那么不要对它建立太多非聚集索引。另外,如果硬盘和内存空间有限,也应该限制使用非聚集索引的数量。
SQLServer缺省情况下建立的索引是非聚集索引,由于非聚集索引不重新组织表中的数据,而是对每一行存储索引列值并用一个指针指向数据所在的页面。换句话说非聚集索引具有在索引结构和数据本身之间的一个额外级。一个表如果没有聚集索引时,可有250个非聚集索引。每个非聚集索引提供访问数据的不同排序顺序。
在建立非聚集索引时,要权衡索引对查询速度的加快与降低修改速度之间的利弊。另外,还要考虑这些问题:
1、索引需要使用多少空间。
2、合适的列是否稳定。
3、索引键是如何选择的,扫描效果是否更佳。
4、是否有许多重复值。
对更新频繁的表来说,表上的非聚集索引比聚集索引和根本没有索引需要更多的额外开销。对移到新页的每一行而言,指向该数据的每个非聚集索引的页级行也必须更新,有时可能还需要索引页的分理。从一个页面删除数据的进程也会有类似的开销,另外,删除进程还必须把数据移到页面上部,以保证数据的连续性。所以,建立非聚集索引要非常慎重。
非聚集索引常被用在以下情况:
1、某列常用于集合函数(如Sum,....)。
2、某列常用于join,orderby,groupby。
3、查寻出的数据不超过表中数据量的20%。
总结
索引在数据库中的应用是非常广泛的,掌握数据库的索引规范在提高数据性能上发挥的作用是不可比拟的。本文还是比较偏理论性,后续更新小编会从两者的根本出发,并展示两种索引的使用方式。
实践请移步【数据库SQL】——聚集索引和非聚集索引根本区别以及使用方式