SQLSERVER数据库优化：聚集与非聚集索引深度解析

需积分: 12 132 浏览量更新于2024-07-25 收藏 725KB PDF 举报

"这篇SQLSERVER完全优化教程着重讨论了如何优化SQL Server数据库，特别是关于索引的使用和优化。文章指出没有索引或未有效利用索引是导致查询效率低下的主要原因，并详细解释了聚集索引和非聚集索引的概念与应用场景。聚集索引是一种特殊类型的索引，它的叶子节点直接包含数据行，也就是说，数据行的物理顺序与索引顺序相同。当对列进行分组、排序或查询特定范围的数据时，聚集索引能显著提高性能，因为它只需要找到数据范围的起点和终点。然而，对于那些值变化较少或频繁更新的列，以及大范围不同值的列，使用聚集索引可能不是最佳选择，因为每次更新索引都需要重新排列整个索引。非聚集索引则独立于数据行的物理顺序，它包含指向数据行的指针。这种索引适用于大量不同值的列，尤其是在需要快速查找特定值时。但是，对于范围查询，非聚集索引的效率较低，因为需要通过索引找到对应的页码，再访问数据行。在实践中，一些常见的索引使用误区包括将主键视为聚集索引。虽然SQL Server默认在主键上创建聚集索引，但这并不总是最佳策略。例如，如果主键是自增ID，其顺序可能对查询优化帮助不大，反而浪费了聚集索引的宝贵资源。每个表只能有一个聚集索引，因此选择哪个列作为聚集索引需要根据数据的访问模式和业务需求谨慎决定。在建立索引时，应考虑以下几点： 1. 对于频繁搜索的列，尤其是用于WHERE子句、JOIN条件和ORDER BY子句的列，应建立索引。 2. 避免在经常更新的列上创建索引，因为这会导致写操作性能下降。 3. 主键列通常应有聚集索引，但如果数据分布均匀且更新频繁，非聚集索引可能更合适。 4. 对于外键，创建非聚集索引可以提高关联查询的速度。 5. 考虑使用覆盖索引，即包含查询所需所有信息的索引，可以减少回表操作，提高查询效率。总结来说，理解并正确使用聚集索引和非聚集索引是优化SQL Server性能的关键。根据数据的特性和查询模式来明智地创建和管理索引，可以帮助减少全表扫描，提高查询速度，从而提升整个数据库系统的性能。"

列中的值。当你添加或更新表中的数据行时，差值将自动插入至计算列中。

你可以很容易地在两个或更多的包含字符串或数字类型值的列的基础上创建计算列。(更多关于

如何创建此类型的计算列的详细信息，请参考Microsoft SQL Server Books Online)。然而，如果

你想要基于指定的XML列中元素值创建一个计算列，该过程相对更加复杂一些。因为你必须使用

Xquery表达式来从XML列中获取指定元素数据,且SQL Server不支持在计算列的定义中使用Xquery表

达式。

要解决此问题，可以创建一个函数来接收你想包含在计算列中的XML数据，并在计算列定义中调

用此函数。更好的示范这是如何工作的，我们在这给出一个例子。我在SQL Server 2005的示例数据

库AdventureWorks中创建以下的架构和表:

USE AdventureWorks;

CREATE SCHEMA hr

SELECT TOP 10 JobCandidateID AS CandidateID,

[Resume] AS JobResume

INTO hr.CandidateNames

FROM HumanResources.JobCandidate

正如名称所示，HumanResources.JobCandidate表中的Resume列是一个XML列，它包含侯选人的

履历信息。我从这张表中提取数据来创建hr架构中的CandidateNames表。(我创建了一个单独的表，

因为我希望可以修改表定义，从而可以增加计算列) 在建立好测试环境后，你可以创建函数。函数

应该包括在从指定的XML列中获取数据时所需的XQuery表达式。例如，以下函数接收工作候选人的姓

名，并保存在JobResume列中：

CREATE FUNCTION hr.FullName (@name XML)

RETURNS NVARCHAR(60) AS

BEGIN

RETURN @name.value('declare namespace ns=

"http://schemas.microsoft.com/sqlserver/2004/07/adventure-

works/Resume";

concat((/ns:Resume/ns:Name/ns:Name.First)[1], " ",

(/ns:Resume/ns:Name/ns:Name.Last)[1])','nvarchar(60)')

END

正如你所看到的，函数FullName带一个输入参数，该参数被定义成XML类型。这个做法是当调用

此函数时，可以把包含所需提取的数据的XML列名称作为输入值来使用。

Value()方法带两个参数。第一个参数定义了目标XML列使用的名称空间，第二个参数包含接收

实际数据的Xquery表达式。在这个例子中，表达式使用concat()方法来连接姓与名，就像它们在XML

文件中。要想了解更多的关于如何使用value()方法，以及如何创建Xquery表达式，请查看我的文章

《Retrieve XML data values with XQuery》。一旦创建了函数，你可以通过从hr.CandidateNames

表的JobResume列中接收数据测试:

SELECT CandidateID, hr.FullName(JobResume) AS FullName FROM hr.CandidateNames

正如你所看到的，我已经传入了XML列名称，将其做为函数FullName的一个参数。SELECT语句

应该返回以下结果：

CandidateID FullName

1 Shai Bassli

2 Max Benson

3 Krishna Sunkammurali

4 Stephen Jiang

5 Thierry D'Hers

6 Christian Kleinerman

注意，以上结果包含姓与名，正如它们在XML列中显示的一样。如果回到函数定义，可发现在

value()方法中使用的Xquery表达式指定了此表达式返回值为NVARCHAR(60)类型，以适应Unicode字

符，如查询结果集的最后三行中的那些字符。

一旦函数经过测试，你就可以开始创建计算列：以下ALTER TABLE语句添加了FullName列到

CandidateNames表中来:

ALTER TABLE hr.CandidateNames

ADD FullName AS hr.FullName(JobResume)

我已经在计算列表达式中使用FullName函数，并将列JobResume作为参数传入函数。在运行ALTER

TABLE语句后，你可以用以下SELECT语句测试数据是否已经被插入到计算列中：

SELECT CandidateID, FullName FROM hr.CandidateNames

运行以上语句后，应该返回与上文中相同的结果集。

这就是在SQL Server中基于XML data数据创建的一个计算列。关键是创建一个函数来运行Xquery

表达式，且稍后在计算列中使用此函数定义。要了解更多关于计算列、XML列、Xquery表达式的详细

信息，请查看Microsoft SQL Server在线书籍。

本文来自CSDN博客，转载请标明出处：

http://blog.csdn.net/mazhaoyi/archive/2010/01/08/5158278.aspx

在CREATE TABLE或ALTER TABLE语句中定义的列可以源于独立的或者基于列的计算。如果需要在相关

查询中对相同的数据重复计算，则计算列就变得很有用。计算列以创建表或修改表的时候定义的表

达式为基础，除非使用了PERSISTED关键字，否则计算列不会物理保存在表中。

在这个技巧中，我会给出创建计算列的演示，并且介绍使用SQL Server 2005的PERSISTED选项的方

法。通过CREATE或ALTER TABLE增加一个计算列的语法如下：

column_name AS computed_column_expression [ PERSISTED ] column_name是新列的名字。

computed_column_expression是你为了得到列的值而执行的计算。增加PERSISTED关键字能让计算的

结果被物理保存。

在这个示例中，我们向既有表中加了一个新的计算列：

ALTER TABLE Production.TransactionHistory ADD CostPerUnit AS (ActualCost/Quantity) 前

面的示例创建了一个名为CostPerUnit的计算列。下一个查询利用它返回数量超过10的最高的

CostPerUnit：

SELECT TOP 1 CostPerUnit, Quantity, ActualCost FROM Production.TransactionHistory WHERE

Quantity > 10 ORDER BY ActualCost DESC 这个查询返回：

CostPerUnit Quantity ActualCost 132.0408 13 1716.5304 下一个示例

创建一个PERSISTED计算列，这意味着计算后的数据实际上会物理地保存在数据库中（但仍然是由SQL

Server自动计算）：

CREATE TABLE HumanResources.CompanyStatistic (CompanyID int NOT NULL, StockTicker

char(4) NOT NULL, SharesOutstanding int NOT NULL, Shareholders int NOT NULL,

AvgSharesPerShareholder AS (SharesOutStanding/Shareholders) PERSISTED) 解析

第一个示例向Production.TransactionHistory表添加了一个名为CostPerUnit的非持久化列，使它

在SELECT查询中能像普通表列那样被引用：

ADD CostPerUnit AS (ActualCost/Quantity) 计算列不能用DEFAULT或者FOREIGN KEY约束。计算列

不能被显式更新或插入（因为它的值都是计算出的）。

计算列能用在索引中，但是一定要符合一些条件。比如是确定的（对于一组给定的输入总是返回相

同的结果）和精确的（不包含浮点值）。

第二个示例演示了在CREATE TABLE命令中使用计算列：

AvgSharesPerShareholder AS (SharesOutStanding/Shareholders) PERSISTED 和第一个示例不同，

增加了PERSISTED意味着数据被物理地保存在数据库中。对计算中使用的列进行的任何修改都会引起

存储值的再一次更新。但是保存的数据仍然不可以直接修改--数据仍然要经过计算。然而和非持久

化的方式不同，保存数据意味着列能用于表分区（请参阅4.7.1节的"实现表分区"技巧）或者非精确

（基于浮点）值的索引。

4、内存不足

5、网络速度慢

6、查询出的数据量过大（可以采用多次查询，其他的方

法降低数据量）

7、锁或者死锁(这也是查询慢最常见的问题，是程序设计

的缺陷)

【IT专家网独家】死锁是指在某组资源中，两个或两个以上的线程在执行过程中，在争夺某一

资源时而造成互相等待的现象，若无外力的作用下,它们都将无法推进下去，死时就可能会产生死锁,

这些永远在互相等待的进程称为死锁线程。简单的说，进程A等待进程B释放他的资源，B又等待A释

放他的资源，这样互相等待就形成死锁。

如在数据库中，如果需要对一条数据进行修改，首先数据库管理系统会在上面加锁，以保证在

同一时间只有一个事务能进行修改操作。如事务1的线程 T1具有表A上的排它锁，事务2的线程T2 具

有表B上的排它锁，并且之后需要表A上的锁。事务2无法获得这一锁，因为事务1已拥有它。事务2

被阻塞，等待事务1。然后，事务1需要表B的锁，但无法获得锁，因为事务2将它锁定了。事务在提

交或回滚之前不能释放持有的锁。因为事务需要对方控制的锁才能继续操作，所以它们不能提交或

回滚，这样数据库就会发生死锁了。

如在编写存储过程的时候，由于有些存储过程事务性的操作比较频繁，如果先锁住表A，再锁住

表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。如果无意中某个存储过程中先锁定表

B，再锁定表A，这可能就会导致一个死锁。而且死锁一般是不太容易被发现的。

如果服务器上经常出现这种死锁情况，就会降低服务器的性能，所以应用程序在使用的时候，

我们就需要对其进行跟踪，使用sp_who和sp_who2来确定可能是哪些用户阻塞了其他用户，我们还可

以用下面的存储过程来跟踪具体的死锁执行的影响：

create procedure sp_who_lock

begin

declare @spid int,@bl int,

剩余76页未读，继续阅读

chaofansky

粉丝: 4
资源: 35

SQLSERVER数据库优化：聚集与非聚集索引深度解析

SQLSERVER完全优化教程

SQL优化教程

SQLSERVER教程

SQLSERVER完全优化教程.pdf

sqlserver完全教程

Java(Andriod平台)连接SQLserver完全教程

SQL SERVER 2000 实用教程

SQL Server 2008基础教程

SQLServer授课 数据库教程

SQL SERVER2000使用教程

最新资源

SQLServer授课数据库教程