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 ON >( [ASC|DESC] [,...n])
INCLUDE ( [, ...n])
[WITH
[PAD_INDEX = { ON | OFF }]
[[,] 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 =
]
[ON { | | DEFAULT }]
3.2 修改
ALTER INDEX { | ALL }
ON >
{ REBUILD
[ [ WITH (
[ PAD_INDEX = { ON | OFF } ]
| [[,] 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 =
) ]
| [ PARTITION =
[ WITH (
[ ,...n ] ) ] ] ]
| DISABLE
| REORGANIZE
[ PARTITION = ]
[ 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 >.
4. 使用索引应注意十么
1)聚集索引通常速度优于非聚集索引
2) 建索引时应考虑是否有足够的空间。索引占据空间,平均约1.2倍数据库本身大小。
3) 在经常用于查询或聚合条件的字段上建立聚集索引。这类查询条件包括 between, >, <,group by, max,min, count等。
4) 不要在经常作为插入,且插入字段无序的列上建立聚集索引。 插入数据行会涉及分页,rebuild索引会消耗大量时间。参考文末"一个不恰当使用聚集索引的例子"。
5) 在值高度的唯一性字段上建立索引。不能在诸如性别的字段上建立索引。
6) 只有作为索引的第一个列包含在查询条件中,该索引才的作用。
打个比方,我们用偏旁+部首来查汉字,那么偏旁首先必须包括在查询条件中,只有先定位偏旁,再结合部首,才能发挥偏旁+部首来检索的快速功效。
7) 删除一直不用的索引。特别是对于删除和修改比较频繁的数据表,必须考虑如何精华索引。
相关推荐
MySQL 数据库索引详解 MySQL 数据库索引是一种特殊的数据库结构,可以快速查询数据库表中特定记录。索引是提高数据库性能的重要方式。 索引的概念 ---------------- 索引是表中数据的目录。如果使用索引,则扫描...
Oracle数据库--索引讲义.pptx
mysql-数据库-索引详解.doc
数据库-实验4-数据库操作的实现算法-B树-索引查找.pdf
- 查询优化:通过优化查询语句、合理设计数据库结构和使用索引来减少查询时间。 - 分区和分片:大型数据库中,通过将数据分散到多个物理存储设备来提升查询效率。 8. 数据库集群与分布式数据库: - 数据库集群:...
这是我们老师自己总结的一些东西了希望对大家有用了,嘻嘻
"数据库基础-索引" 数据库基础中索引的概念是非常重要的,它可以提高数据库的查询速度和效率。在本文中,我们将详细介绍数据库基础中的索引,包括索引的概念、分类、特点、优点、应用场景等。 索引的概念 --------...
- 索引管理:包括创建、删除、重命名和重建索引,以及主键和唯一索引的处理。 - 分区管理:测试添加和删除分区的操作。 - 字段注释:验证在线修改字段注释的兼容性。 - 清除数据:如TRUNCATE TABLE和DROP语句的执行...
美国《医学索引》、Medline数据库-(2).pdf
【免费获取】MySQL数据库索引---总结的【文档+PPT】(可用于每日一讲)。
根据给定文件的信息,我们可以提炼出与“自我管理数据库-自动的SQL调优”相关的知识点。但是,需要注意的是,提供的内容主要围绕七个可能对Linux系统造成严重损害的命令,而不是直接涉及自我管理数据库或自动SQL调优...
### 数据库-零件交易中心管理系统知识点解析 #### 一、项目背景及目标 - **项目名称**:“零件交易中心管理系统”。 - **目标**:本项目旨在通过一个完整的开发过程,包括需求分析、数据库设计、编程与调试等阶段,...
(5)针对某种DBMS,设计数据库物理模型,包括表空间、表和索引等于物理存储有关的设计。 (6)优化物理模型 (7)生成某种DBMS的SQL语句,创建数据库及其表。 2.4装载数据 (8)收集真实数据或者生成模拟数据。 ...
数据库索引是数据库性能优化的重要手段之一。创建索引可以提高查询速度,降低数据库的负载,提高数据的安全性。本文将详细介绍数据库创建索引的原则、分类、创建方法、管理和优化等方面的知识点。 索引的概念和优点...
用户中心数据库设计,符合企业级数据库开发规范,数据库表考虑多种情况,包括自增,索引等等
- 定期优化:定期进行数据库性能调优,包括索引优化、查询优化、存储过程优化等。 7. 数据库设计与模式 - 正确设计关系模型:遵循第一范式(1NF)、第二范式(2NF)、第三范式(3NF)和BCNF,避免数据冗余和更新...
MySQL是一种广泛使用的开源关系型数据库管理系统(RDBMS),它基于结构化查询语言(SQL),在互联网应用中占据着重要地位。本资料包"数据库-MySQL(一).zip"可能包含了关于MySQL的基础知识,包括安装、配置、数据...
【数据库索引技术详解】 数据库索引是一种为了加速数据检索而创建的数据结构,它使得数据库管理系统(DBMS)能够在大量数据中快速找到所需记录。索引由一系列索引项组成,每个索引项包含索引字段(通常是表中一列或...