SQL Server 使用索引来对数据访问进行优化

数据库 SQL Server
如果你是SQL Server 使用索引来实现数据访问优化方面的新手,对其的相关实际应用不是很了解的话,就可以点击以下的文章进行观看了。

以下的文章主要描述的是SQL Server 使用索引来实现数据访问优化的实际操作步骤,我前两天在相关网站看见SQL Server 使用索引来实现数据访问优化的实际操作步骤的资料,觉得挺好,就拿出来供大家分享。

第一步:在列上采用正确的索引

 

有些人可能争论实施正确的索引是否是数据库优化过程的第一步。但是我认为在数据库应用正确的索引是第一位的。原因有两点:

 

1.在一个产品系统里,它将使你在很快的时间内提高尽可能大的性能。

2.创建数据库索引不需要你做任何的系统修改,因此不需要任何重新编译和部署

假如你发现有当前的数据库没有很好的处理索引,你建了索引,结果就是性能的快速提升。

(1)首先,创建了聚焦索引(即主键),接着数据页在物理文件里按照主键的值被排序。

(2)其次,在非主键列上创建一个非聚焦索引,将该列和主键绑定在一起,但是要按照该列进行排序。

策略:

(1)确保数据库的每个表有一个主健,这么做会确保每个表有一个聚焦索引,通过主健的值,表的数据页通按物理顺序排列在磁盘上。所以任何使用主健的数据检索操作,任何在主健字段的排序操作都能非常迅速的检索数据。

(2)在这些列上创建非聚焦索引:A: 经常被作为搜索凭证的列; B:用来联合其它表的列; C:用来作为外健的列;D:用来排序的列;E:高选择性列; F:Xml类型

下面是一个创建索引的命令的例子:

 

  1. CREATE INDEX NCLIX_OrderDetails_ProductID ON dbo.OrderDetails(ProductID) 

你也可以使用SQL Server控制台在需要的列上创建索引。

第二步:创建正确的复合索引

下面是创建复合索引的例子:

 

  1. CREATE INDEX NCLIX_Sales_ProductID ON dbo.Sales(ProductID) INCLUDE(SalesDate,SalesPersonID) 

请注重,创建复合索引应当包含少数几个列,并且这些列经常在select查询里使用。在复合索引里包含太多的列不仅不会给你带来太多好处。而且由于使用相当多的内存来存储复合索引的列的值,其后果是内存溢出和性能降低。

第三步:假如有碎片发生,重新整理它

1、什么是索引碎片

索引碎片是这样一种情形:由于在表里大量的插入、修改、删除操作而使索引页分裂。假如索引有了高的碎片,有两种情况,一种情况是扫描索引需要花费很多的时间,另一种情况是在查询的时候索引根本不SQL Server 使用索引,都会导致性能降低。

2、有两种类型的碎片:

内部破碎:由于索引页里的数据插入或修改操作而发生,以数据作为稀疏矩阵的形式的分布而结束,这将导致数据页的增加,从而增加查询时间。

外部破碎:由于索引/数据页的数据插入或修改而发生,以页码分离和在文件系统里不连贯的新的索引页的分配而结束,数据库服务器不能利用预读操作的优点,因为:下一个相关联的数据页不临近,而且这些相关连的下面的页码可能在数据文件的任何地方。

3、如何知道索引破碎是否已经发生?

在数据库执行下面的SQL语句(下面的语句在SQLserver2005及以后的版本运行正常,以你的目标数据库的名字取代AdventureWorks)

  1. SELECT object_name(dt.object_id) Tablename,si.name IndexName,dt.avg_fragmentation_in_percent AS ExternalFragmentation,  
  2. dt.avg_page_space_used_in_percent AS InternalFragmentation  
  3. FROM  
  4. (  
  5. SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent  
  6. FROM sys.dm_db_index_physical_stats(db_id('AdventureWorks'),null,null,null,'DETAILED')  
  7. WHERE index_id<>0  
  8. )   
  9. AS dt INNER JOIN sys.indexes as si ON si.object_id=dt.object_id  
  10. AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10  
  11. AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC 

分析查询结果,你就能发现在哪里出现了索引碎片,应用下面的规则:A :ExternalFragmentation的值>10,预示对应的索引出现外部碎片。B : InternalFragmentation的值<75,预示对应的索引出现内部碎片。

4、什么时候重组和重建索引?

当外部碎片的值在10-15,内部碎片的值在60-75,对于这样的索引,你应该重组索引。否则,你应该重建索引。

关于索引重建的一个重要的事情是:一旦在一个特定的表上重建索引,表就会被锁定(重组的时候不会发生)。所以,对于一个产品数据库的一个大的表,因为在一个大表上的索引重建往往需要花费数个小时,我们不希望这种锁定。幸运的是,在SQL2005有一个解决方法,你可以在重建一个表的索引的时候,把ONLINE选项的值设为ON,这样会使重建索引和表上的数据事务同样进行。 

5、怎样重新整理索引碎片。有两种方式:

索引重组:执行下面的命令:

 

  1. ALTER INDEX ALL ON TableName RECOGNIZE 

索引重建:

 

  1. ALTER INDEX ALL ON TableName REBUILD WITH(FILLFACTOR=90,ONONLINE=ON) 

通过使用具体索引的名字代替ALL,你能重组或重建单个的索引。你也可以使用数据库控制台来重建/重组索引。以上的相关内容就是对SQL Server 使用索引实现数据访问优化的介绍,望你能有所收获。

【编辑推荐】

  1. SQL Server数据库和Oracle数据同步方案
  2. SQL Server ASP代码与Access ASP代码的比较
  3. SQL Server 数据表信息的详细描述
  4. SQL Server置疑的出现的原因,现象与破解
  5. 创建SQL Server全文检索的2方案
责任编辑:佚名 来源: 51CTO
相关推荐

2009-06-08 09:22:07

数据访问优化SQL Server

2021-08-09 15:00:36

SQL数据库

2009-04-16 17:44:46

性能优化扩展高性能

2009-09-28 09:47:55

Hibernate数据

2010-07-01 14:18:09

SQL Server数

2010-11-12 14:16:21

SQL游标

2022-08-02 09:32:47

pandas移动计算

2023-10-10 09:13:15

Python数据的操作转换

2009-07-20 16:40:55

JDBC访问SQL S

2011-03-18 14:54:52

SQL Server索引结构

2021-05-09 22:48:40

SQL数据库变量

2011-05-20 10:52:50

SQL Server 索引

2009-04-16 17:24:54

性能优化SQL Server 数据收集

2023-07-27 08:16:51

数据访问层项目

2010-07-23 15:26:29

SQL Server

2011-04-02 14:24:25

SQL Server数网络链接

2010-07-08 16:52:31

SQL Server索

2010-07-14 09:17:17

SQL Server数

2023-07-13 11:24:14

SQL优化赋值

2011-06-14 10:43:44

索引
点赞
收藏

51CTO技术栈公众号