数据库索引,你该理解的几件事[MSSQL防范]

1．数据库的数据存储

1.1文件:

我们一旦成立一个数据库,城市生成两个文件:

DataBaseName.mdf: 主文件,这是数据库中的数据终究存放的地方.

DataBaseName.ldf:日记文件,由数据操作产生的一系列日记记录.

1.2分区:

在一个给定的文件中,为表和索引分配空间的基本存储单位. 1个区占64KB,由8个持续的页构成. 假如一个分区已满,但需存一条新的记录,那么该记录将占用整个新分区的空间.

1.3 页:

分区中的一个分配单位.这是实际数据行终究存放的地方. 页用于存储数据行.

Sql Server有多种范例的页:

Data, Index,BLOB,GAM(Global Allocation Map),SGAM,PFS(Page Free Space),IAM(Index Allocation Map),BCM(Bulk Changed Map)等.

51CTO数据库频道向您举荐:MySQL索引:数据库性能的双刃剑

2． 索引

2.1.1索引

索引是与表或视图关联的磁盘上构造,可以加快从表或视图中检索行的速度.索引包含由表或视图中的一列或多列生成的键.这些键存储在一个构造（B 树）中,使 SQL Server 可以快速有效地查找与键值关联的行.

通俗点说,索引与表或视图相关,旨在加快检索速度.索引本身占据存储空间,通过索引,数据便会以B树情势存储.因此也加快了查询速度.

2.1.2堆积索引

堆积索引按照数据行的键值在表或视图中排序和存储这些数据行.索引定义中包含堆积索引列.每个表只能有一个堆积索引,因为数据行本身只能按一个次序排序.只有当表包含堆积索引时,表中的数据行才按排序次序存储.假如表具有堆积索引,则该表称为堆积表.假如表没有堆积索引,则其数据行存储在一个称为堆的无序构造中.

通俗点说,堆积索引的页存储的是实际数据.每个表只能成立唯一的堆积索引,但也可以没有.

假如成立堆积索引,那么表中数据以B树情势存储数据.

关于堆积索引的理解,打个比方,即英文字典的单词编排. 英文字典单词以A,B,C,D….X,Y,Z的情势次序编排,假如我们查找 Good 单词,我们首先定位到G,然后定位o – o-d. 终究查找到Good,就是good实际存在的地方.

建堆积索引需求至少相当该表120%的附加空间,以存放该表的副本和索引中间页.

2.1.3非堆积索引

非堆积索引具有独立于数据行的构造.非堆积索引包含非堆积索引键值,并且每个键值项都有指向包含该键值的数据行的指针.

从非堆积索引中的索引行指向数据行的指针称为行定位器.行定位器的构造取决于数据页是存储在堆中还是堆积表中.关于堆,行定位器是指向行的指针.关于堆积表,行定位器是堆积索引键.

通俗点说,非堆积索引的页存储的是不是实际数据,而是实际数据的地址.一个表可以存在多个非堆积索引.在Sql Server2005中,每个表最多可以成立249个,而在Sql server2008中,则最多可以成立999个非堆积索引.

关于非堆积索引的理解,即新华字典的“偏旁部首”查字法.碰到您不熟习的字,不知道它的发音,这时刻,您就不能按照方才的办法找到您要查的字,而需求去按照“偏旁部首”查到您要找的字,然后按照这个字后的页码直接翻到某页来找到您要找的字.但您结合“部首目录”和“检字表”而查到的字的排序并非真正的正文的排序办法,比方您查“张”字,我们可以看到在查部首之后的检字表中“张”的页码是672页,检字表中“张”的上面是“驰”字,但页码倒是63页,“张”的下面是“弩”字,页面是390页.很明显,这些字并非真正的辨别位于“张”字的上下方,目前您看到的持续的“驰、张、弩”三字实际上就是他们在非堆积索引中的排序,是字典正文中的字在非堆积索引中的映射.我们可以通过这种方法来找到您所需求的字,但它需求两个历程,先找到目录中的后果,然后再翻到您所需求的页码.我们把这种目录纯粹是目录,正文纯粹是正文的排序方法称为“非堆积索引”.

2.1.4 覆盖索引:

覆盖索引是指那些索引项中包含查寻所需求的全部信息的非堆积索引,这种索引之所以对比快也恰是因为索引页中包含了查寻所必须的数据,不需去拜候数据页. 假如非聚簇索引中包含后果数据,那么它的查询速度将快于堆积索引.

但是由于覆盖索引的索引项对比多,要占用对比大的空间.并且update 操作会惹起索引值改变.所以假如潜在的覆盖查询并不常用或不太关键,则覆盖索引的增添反而会降低性能.

2.1.5 主键和索引

主键:表普通具有包含唯一标识表中每一行的值的一列或一组列.这样的一列或多列称为表的主键 (PK),用于强迫表的实体完好性.在成立或改正表时,您可以通过定义 PRIMARY KEY 约束来成立主键. 它是一种唯一索引.

下面是一个简单的对比表

主键

堆积索引

用处

强迫表的实体完好性

对数据行的排序,便利查询用

一个表多少个

一个表最多一个主键

一个表最多一个堆积索引

能否允很多个字段来定义

一个主键可以多个字段来定义

一个索引可以多个字段来定义

能否答应 null 数据行呈现

假如要成立的数据列中数据存在null,无法成立主键.
成立表时指定的 PRIMARY KEY 约束列隐式转换为 NOT NULL.

没有限制成立堆积索引的列一定必须 not null .
也就是可以列的数据是 null
参看最后一项对比

能否要求数据必须唯一

要求数据必须唯一

数据便可以唯一,也可以不惟一.看你定义这个索引的 UNIQUE 设置.
（这一点需求看背面的一个对比,固然你的数据列大概不惟一,但是系统会替你产生一个你看不到的唯一列）

成立的逻辑

数据库在成立主键同时,会自动成立一个唯一索引.
假如这个表之前没有堆积索引,同时成立主键时刻没有强迫指定利用非堆积索引,则成立主键时刻,同时成立一个唯一的堆积索引

假如未利用 UNIQUE 属性成立堆积索引,数据库引擎 将向表自动增添一个四字节 uniqueifier 列.
必要时,数据库引擎 将向行自动增添一个 uniqueifier 值,使每个键唯一.此列和列值供内部利用,用户不能查看或拜候.

2.2 索引的存储构造

2.1.1 整表扫描和索引扫描

整表扫描和索引扫描是Sql Server数据库检索到数据的唯一的两种方法.除此之外,没有第三种方法供Sql Server检索到数据.

整表扫描

最直接的检索方法, Sql Server举行表扫描时,会从表头开始扫描,直到整个表完毕. 当找到符合条件的记录,便把该记录存在后果集合.关于小数据量的表,这是一种很快速的方法.假如没有为表成立索引,那么Sql server便按这种方法检索数据.

索引扫描

假如为表成立了索引,在举行检索前,Sql Server优化器会按照查询条件,从可用的索引中挑选最优化的索引.检索时,便会遍历B树,当找到符合条件的记录,便把该记录存在后果集合.因此,检索大数据量的表,利用索引相关于整表扫描会明显地提高性能.

2.1.2 B-Tree

2.2.3 堆积索引

叶子节点存放的是实际的数据.索引的进口点存放在master->sys.indexes中.

2.2.4 非堆积索引

2．4．1 堆上的非堆积索引（Non-clustered index on heap）

与堆积索引很近似.

差别处在:

叶子节点存放的不是实际数据,而是指向实际数据的指针.检索速度非常接近于堆积索引,比起堆积索引,实际上只是多一步由按照指针检索到实际数据的历程.

2．4．2 堆积表上的非堆积索引

3． 管理索引

3．1 成立

CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]  
 
INDEX <index name> ON <table or view name>(<column name> [ASC|DESC] [,...n])  
INCLUDE (<column name> [, ...n])  
[WITH 
[PAD_INDEX = { ON | OFF }]  
[[,] FILLFACTOR = <fillfactor>]  
[[,] IGNORE_DUP_KEY = { ON | OFF }]  
[[,] DROP_EXISTING = { ON | OFF }]  
[[,] STATISTICS_NORECOMPUTE = { ON | OFF }]  
[[,] SORT_IN_TEMPDB = { ON | OFF }]  
[[,] ONLINE = { ON | OFF }  
[[,] ALLOW_ROW_LOCKS = { ON | OFF }  
[[,] ALLOW_PAGE_LOCKS = { ON | OFF }  
[[,] MAXDOP = <maximum degree of parallelism>  
]  
[ON {<filegroup> | <partition scheme name> | DEFAULT }] 

3．2 改正

ALTER INDEX { <name of index> | ALL }  
ON <table or view name>  
{ REBUILD  
[ [ WITH (  
[ PAD_INDEX = { ON | OFF } ]  
| [[,] FILLFACTOR = <fillfactor>  
| [[,] SORT_IN_TEMPDB = { ON | OFF } ]  
| [[,] IGNORE_DUP_KEY = { ON | OFF } ]  
| [[,] STATISTICS_NORECOMPUTE = { ON | OFF } ]  
| [[,] ONLINE = { ON | OFF } ]  
| [[,] ALLOW_ROW_LOCKS = { ON | OFF } ]  
| [[,] ALLOW_PAGE_LOCKS = { ON | OFF } ]  
| [[,] MAXDOP = <max degree of parallelism>  
) ]  
| [ PARTITION = <partition number>  
[ WITH ( <partition rebuild index option>  
[ ,...n ] ) ] ] ]  
| DISABLE  
| REORGANIZE  
[ PARTITION = <partition number> ]  
[ WITH ( LOB_COMPACTION = { ON | OFF } ) ]  
| SET ([ ALLOW_ROW_LOCKS= { ON | OFF } ]  
| [[,] ALLOW_PAGE_LOCKS = { ON | OFF } ]  
| [[,] IGNORE_DUP_KEY = { ON | OFF } ]  
| [[,] STATISTICS_NORECOMPUTE = { ON | OFF } ]  
)  
} [ ; ] 

3．3 删除

DROP INDEX <table name>.<index name> 

4． 利用索引应注意十么

1）堆积索引普通速度优于非堆积索引

2) 建索引时应考虑能否有充足的空间.索引占据空间,平均约1.2倍数据库本身大小.

3) 在常常用于查询或聚合条件的字段上成立堆积索引.这类查询条件包含 between, >, <,group by, max,min, count等.

4) 不要在常常作为插入,且插入字段无序的列上成立堆积索引. 插入数据行会触及分页,rebuild索引会损耗大量时间.参考文末"一个不得当利用堆积索引的例子".   

5) 在值高度的唯一性字段上成立索引.不能在诸如性别的字段上成立索引.

6) 只有作为索引的第一个列包含在查询条件中,该索引才的作用.

打个比方,我们用偏旁+部首来查汉字,那么偏旁首先必须包含在查询条件中,只有先定位偏旁,再结合部首,才能施展偏旁+部首来检索的快速功效.

7) 删除一向不用的索引.分外是关于删除和改正对比频繁的数据表,必须考虑若何精华索引.

原文标题:闲谈数据库之索引

链接:http://www.cnblogs.com/Ring1981/archive/2010/09/15/1826997.html