`
jiagou
  • 浏览: 2517121 次
文章分类
社区版块
存档分类
最新评论

SQL Server总结之——索引

 
阅读更多

    概念:

    索引是一种特殊的数据库对象。它使数据库程序无须对整个表进行扫描,就可以在其中找到所需数据。可以简单的理解为:目录!

    优缺点:

    优点:

    在数据库中,索引使数据库程序无须对整个表进行扫描,就可以在其中找到所需数据。

    利用索引可以大大提高系统的性能

    表现在:

  1. 通过创建惟一索引,可以保证数据记录的惟一性。
  1. 大大加快数据检索速度
  1. 加强表与表之间的连接

缺点:

  1. 需要占用数据表以外的物理储存空间。
  1. 创建和维护索引需要花费一定的时间。
  1. 因为对表进行更新操作时,索引需要被重建,降低了数据的维护速度。

原则:

建立索引原则:

  1. 主键列上一定要建立索引.(系统默认为聚集索引)
  1. 外键列可以建立索引。
  1. 经常查询的字段上最好建立索引
  1. 查询中很少涉及的列和重复值比较多的列不要建立索引
  1. 对于定义为textimage、和bit数据类型的列不要定义索引。

使用索引原则:

  1. 一个表中如果建有大量索引会影响insertupdate、和delete语句的性能,因为在表中的数据更改时,所有的索引都需要进行适当的调整。
  1. 避免对更新过多的表进行过多的索引,而且应尽量保持索引较窄,也就是说,列要尽可能的少。
  1. 使用多个索引可以提高更新少而数据量大的查询的性能,因为查询优化器有更多的索引可供选择。
  1. 对数据量小的表索引可能不会产生优化作用,因为对有索引的表,查询优化器会先遍历数据索引页,可能会比直接查询花费更多的时间。

分类:

根据储存结构的不同分为

一:聚集索引.

指物理存储顺序与索引顺序完全相同(按索引列进行排序),它由上下两层组成,上层为索引页,下层为数据页,只有一种排序方式,因此每个表中只能创建一个聚集索引。

计算机生成了可选文字: -一‘-『‘、、;「bennet口口Ch日ndUI}edw己rdsP日ge1133gre日ne9reengFeene'.Datapagc&Lcaf、色日.1IldcxpagcP日ge1001bennet1007k日Fsen1009smith1062卜卜J一卜J厂P日gebennetgre日nehUnter1007113211331127P日ge1127l·―。-l·l州·!}!.仁!.1,·…丫·…,…洲---·-r:匕_l_{一…一。一{一。一…一…一一{一红一厂一犷一「一犷一「p己ge1009k日FsenhUnterjenldns、{-.,甲.......~~尸~

·优点:查找数据很快以

·缺点:完全重排数据,要相当于数据所占用空间的120%!

注意:

  1. 由于一个表只能有一种实际的存储顺序,此,一个表只能有一个聚集索引
  2. 创建非聚集索引之前要先创建聚集索引
  1. 关键值的惟一性使用UNIQUE或标识符明确维护
  1. 保证有足够的临时空间来创建聚集索引

二:非聚集索引(默认的)

非聚集索引具有完全独立于数据行的结构。使用非聚集索引不用将物理数据页中的数据按列排序。通俗地说,不会影响数据表中记录的实际存储顺序。

因此,可以在一个表中创建多个非聚集索引。非聚集索引需要更多的空间,检索效率也较低。一个表中除建立一个聚集索引外,还可以建立249个非聚集索引。

无论是聚集索引还是非聚集索引:

根据索引键值是否重复,可以判定为是否为唯一索引

若希望在表中创建唯一索引,则该字段或字段组合的值在表中必须具有唯一性

注意:

  1. 建立唯一索引的字段最后也设置为notnu11,因为两个nun值将被认为是重复的字段值。
  1. 添加数据时,如果该列创建了唯一索引,那么添加的数据就不能重复,否则就会提示错误。

根据索引字段的组成情况,可以判定是否为复合索引。

概念:若基于多个字段的组合创建索引,则称该索引为复合索引。

注意:符合索引既可以是唯一索引,也可以不是唯一索引:即使是唯一索引,这个字段的组合的取值不能重复,但是单独的字段值依然可以重复。

操作:

一:创建索引。

计算机生成了可选文字: 利用T一SQL语句管理索引。其语法形式如下:.CREATE[UNIQUE][cLus仆REn}NONCLUSTERED].INDEXindexn溯eONtab已e·(colllo.n「,…n])·[with[PADINDEX][[,]FILLFACTOR二fillfactor][[,].IGNOREDUPKEY]NG]ISTICSNORECOMPUTE]]·[ONf11egrotlp]

例如:为“学生”表创建一个基于“系部代码”、“专业代码”的唯一、聚集、复合索引。

use student

   go

  create UNIQUE CLUSTERED INDEX xbzy_index

  on 学生(系部代码,专业代码)

  go

二查询

[EXEC] sp_helpindex [@objname=] name

其中 [@objname=] name是当前数据库中表或视图的名称。

例如:查看Student数据库中“class_info”表的索引信息。

Use student

Go

Exec sp_helpindex class_info

Go


三:删除

DROP INDEX表名.索引名[,n……]

例如:删除Student数据库中Class_InfoClassno_index的索引。

Use student

Go

DROP Index class_info.classno_index

Go

需要注意的是:

  1. DROP INDEX不能指定系统表中的索引。
  1. 删除聚集索引时,表中索引非聚集索引都会被重建。
  2. 删除表时,表中的索引都会被删除。
  3. 除去为实现PRIMARY KRYUNIQUE约束而创建的索引,必须先除去约束。

分析与维护

一:分析。

1.SHOWPLAN语句

该语句用来显示查询语句的执行信息,包括查询过程所选择的哪个索引。

语法格式:SETSHOWPLAN_ALL{ON|OFF}SETSHOWPLAN_TEXT{ON|OFF}

其中:on为显示执行信息,off为不显示(系统默认)

例如:在Student数据库中的“student_info”表上查询所有男同学的学号和班级,并显示查询处理过程。

Use student

Go

Set showplan_all on

Go

Select student_id,class_no from student_info where性别="男"

Go


2.STATISTICSIO语句

该语句用来显示执行数据检索语句所花费的磁盘活动量信息,从而确定是否重新设计索引

语法:STATISTICSIO{on|off}

用法与SHOWPLAN相同

二:维护。

1.DBCC SHOWCONTIG语句(查看锁片信息)

扫描密度为100%时,表示不存在碎片。

语法:DBCCSHOWCONTIG[{table_name|tabel_id|view_name|view,index_name|index_id}]]

例如:

Use student

Go

DBCC SHOWCONTIG

Go

2.DBCC INDEXDEFFRAG语句(整理碎片)

对索引的叶级进行碎片整理,使页的物理顺序与叶结点逻辑顺序匹配,从而提高扫描性能。

压缩索引页,并将压缩后产生的空白页删除。

语法:

DBCC INDEXDEFRAG

           ({database_name|database_di|0}

             ,tabel_name|table_id|'view_name'|view_id}

              ,{index_name|index_id})

             [WITH NO_INFOMSGS]

说明:{database_name|database_di|0}进行碎片整理的数据库,如果是0,则使用当前的数据库。

[WITH NO_INFOMSGS]进行显示所有信息性的消息(0~10的严重级别)。

例如:

Use student

Go

DBCC INDEXDEFRAG(student,class_info,class_no_index)

Go


最后:索引是与表或视图关联的磁盘上结构,可以加快从表或视图中检索行的速度。了解索引的分类、作用和优缺点可以让我们合理的利用索引。同样索引也存在的“增删改查”都是基本的操作,唯一不同的是因为数据库经常的变更,我们需要对索引进行分析和维护。

分享到:
评论

相关推荐

    SQL Server 2008数据库设计与实现

    《SQL Server 2008数据库设计与实现》深入浅出地介绍了目前世界上最受欢迎的数据库管理系统之一——SQL Server。全书共分三个部分:第一部分阐释了数据库的基本概念,讲解了数据库建模语言;第二部分展示了从概念...

    数据库技术及应用——SQL Server课件 7索引.ppt

    数据库技术及应用——SQL Server课件

    Microsoft SQL Server 2005技术内幕: T-SQ程序设计.pdf

     是Inside Microsoft SQL Server 2005系列书中的第一本,SQL Server类的顶尖之作  全球公认SQL Server 2005经典著作,囊括大量鲜为人知的技术内幕,大师智慧、专家经验尽览无余。   本系列图书中文版得到了微软...

    数据库程序设计—— SQL Server 2000 数据库程序设计(DOC+PPT)

    数据库程序设计—— SQL Server 2000 数据库程序设计,有完整的文档教程及幻灯,共22章: 第1章 SQL Server 概述 第2章 创建和管理数据库 第3章 创建数据类型和表 第4章 实现数据完整性 第5章 ...

    深入解析SQL Server 2008

    本书全面探讨了sql server 2008的内部工作原理。全书共分为11章,首先在第1章中详细介绍了sql server 2008... 运行dbcc时,sqlserver在内部检查什么  处理多个并发用户时,如何在5个隔离级别和2个并发模型中做出选择

    SQL-Server2008实验报告

    实验1:数据库的定义实验——使用SQL Server企业管理器创建数据库、索引和修改表结构,并学会使用查询分析器,接收T—SQL语句和进行结果分析。 实验2:数据库的查询实验——掌握SQL Server查询分析器的使用方法,...

    SQL+Server+2008数据库设计与实现

    《SQL Server 2008数据库设计与实现》深入浅出地介绍了目前世界上最受欢迎的数据库管理系统之一——SQL Server。全书共分三个部分:第一部分阐释了数据库的基本概念,讲解了数据库建模语言;第二部分展示了从概念...

    SQLServer2008查询性能优化 2/2

    书名: SQLServer2008查询性能优化 作者: 弗里奇(Grant Fritchey) 出版社: 人民邮电出版社 出版日期: 2010年8月1日 ISBN: 9787115230294 编辑推荐 《SQL Server 2008查询性能优化》为你提供了处理查询性能所...

    SQLServer2008查询性能优化 1/2

    书名: SQLServer2008查询性能优化 作者: 弗里奇(Grant Fritchey) 出版社: 人民邮电出版社 出版日期: 2010年8月1日 ISBN: 9787115230294 编辑推荐 《SQL Server 2008查询性能优化》为你提供了处理查询性能所...

    SQL Server 2008编程入门经典(第3版)

    第9章 SQLServer存储和索引结构 9.1 SQLServer存储机制 9.1.1 数据库 9.1.2 区段 9.1.3 页 9.1.4 行 9.1.5 稀疏列 9.2 理解索引 9.2.1 平衡树(B.树) 9.2.2 SQLServer中访问数据的方式 9.3 创建、修改和删除索引 ...

    SQL server 语句大全

    目录 SQL语法 2 数据库 5 创建数据文件 5 修改数据库 6 删除 7 数据表 8 判断数据库中的某表是否存在,存在删除该表 8 修改表语法 8 ... 使用触发器方法其一 —— 判断 24 数据库备份和恢复 25

    SQL.Server.2008编程入门经典(第3版).part2.rar

    第9章 SQLServer存储和索引结构 9.1 SQLServer存储机制 9.1.1 数据库 9.1.2 区段 9.1.3 页 9.1.4 行 9.1.5 稀疏列 9.2 理解索引 9.2.1 平衡树(B.树) 9.2.2 SQLServer中访问数据的方式 9.3 创建、修改和删除索引 ...

    Microsoft_SQL_Server_2005技术内幕:T-SQL查询.pdf

    本书及其续篇——《Microsoft SQL Server 2005技术内幕:T-SQL程序设计》介绍了SQL Server 2005中高级T-SQL查询、查询优化及编程相关的知识。这两本书侧重于解决实践中的常见问题,并讨论了解决这些问题的方法。它们...

    SQL.Server.2008编程入门经典(第3版).part1.rar

    第9章 SQLServer存储和索引结构 9.1 SQLServer存储机制 9.1.1 数据库 9.1.2 区段 9.1.3 页 9.1.4 行 9.1.5 稀疏列 9.2 理解索引 9.2.1 平衡树(B.树) 9.2.2 SQLServer中访问数据的方式 9.3 创建、修改和删除索引 ...

    一个完整的SQL SERVER数据库全文索引的示例介绍

    (sp_fulltext_column) 5) 为表创建全文索引 (sp_fulltext_table) 6) 填充全文目录 (sp_fulltext_catalog) ———********示例********————- 以对pubs数据库的title和notes列建立全文索引,之后使用索

    SQL Server统计信息??在过滤索引上的统计信息

     从2008开始,引入了一个增强非聚集索引的新功能——过滤索引(filter index),可以使用带有where条件的语句来创建非聚集索引,过滤掉不需要的数据,降低索引的维护开销和存储空间,提高查询性能。  准备工作...

    SQLSERVER聚集索引和主键(Primary Key)的误区认识

    例如下面: 代码如下: USE [pratice] GO CREATE TABLE #tempPKCL ( ID INT PRIMARY KEY CLUSTERED –聚集索引 ) ——————————— USE [pratice] GO CREATE TABLE #tempPKNCL ( ID INT PR

Global site tag (gtag.js) - Google Analytics