SQL server Bulk Delete 优化速度 sql server数据库性能优化

不过好像只将了八点

进过数月你和你的团队设计开发了个成功的互联网应用。你有很好的客户端界面，因此在一个短的时间段内，能吸引数千个用户注册和使用你的站点。你的客户，管理层，团队和你每天都很开心。

生活不是玫瑰花园。当用户数量在你的站点每天迅速发展壮大，问题开始出现。客户开始通过电子邮件抱怨站点访问非常缓慢（和一些生气的邮件），客户要求改进，同时开始损失你的用户。

你开始分析这个应用。不久你发现了问题，当应用尝试存储和更新数据时数据库执行非常缓慢。数据库中的数据表已经变得很大，包含几十万行数据。测试团队在产品站点执行一个测试，排序提交过程取得数据需要5分钟才能成功，然而用户只要2/3秒成功测试站点。

这是一个老故事，全世界有数千成熟的应用项目。几乎每个开发者包括我花费部分时间看着他成长。所以，我知道

Step1: Apply proper indexing in the table columns in the database

第一步，在表的列上创建适当的索引

那么，创建适当的索引在数据库优化中应该是第一步考虑的。但是我觉得合理的索引首先考虑，是因为以下两点：

1、这将是最短时间内最有效的可能改善应用性能的措施；

2、数据库中提交创建索引将不需要你做对应用的修改，因此不需要重新生成，部署；

当然，如果你能找到它这是快速性能改进，并且索引在当前数据库中没有适当地完成。然而，如果索引都已经完成了，我将不建议你走这一步。

什么是索引？

我认为，你知道什么是索引。但是，我看到一些人存在混淆这个。所以，让我们再尝试一次索引，让我们读个小故事。

在一个古老城市有一个大的图书馆。他们有数千图书，但是图书在书架上没有任何排序，因此每次一个人询问在图书管询问一本书时，图书馆必须查看每本书直到找到这人想要的书。在图书馆找到想要的书要花费数小时，以及客人等待很长时间。

[表没有主键，所以需要在所有数据中寻找，数据库引擎扫描全表找到符合的行，哪些执行非常慢]

当图书馆中书一天天增加以及来借书的人也越来越多，图书馆管理员就非常悲惨。然后某天，一个明智的人来到图书馆，看到图书馆的情形，他劝告他按照编号每本书和根据他们的数字安排这些在书架。“我将得到哪些好处”图书管理员问，明智的人回答说“那么，现在，如果某人给您一个帐簿编号并且请求那本书，包含book’s数字的您能迅速发现架子，并且，在那之内搁置，您能发现书作为这些根据他们的数字非常迅速被安排”。

[图书编号听起来像在数据表中创建主键，当你创建一个主键在数据表中一个聚集索引树创建以及所有数据页包含表中所有行在文件系统中按照他们主键的值进行物理排序，每一个数据页包含行的主键进行排序。所以，在表中查询一些行时，数据库服务器先找到符合的数据页首先使用聚集索引树，然后寻找数据页中包含的关键字的数据行]

“这就是我需要的！”兴奋的图书管理员立即开始编号图书和摆放那些不同的书架。他花费一整天做这个安排，但是到最会，他测试寻找一本书效果很好，图书管理员非常愉快。

[当你创建表主键时。在内部，在数据文件按照主键值进行，聚集索引树是创建和数据页是物理排序。同样你容易理解，一个表只能创建唯一一个聚集索引，也只能使用一个列的值做主键。就好像书只能使用一个标准编号]

等等！有一个问题没能解决。在第二天，一个人询问一本书只知道书名（不知道书的编号），可怜的图书管理员，没有办法，从1开始查找，最后发现在67号架子上。这花费了管理员20分钟来查找书。最早，还未编排的时候他使用2、3个小时查找书，因此还需要改善。但是，比较搜索时间使用30秒，这20分钟似乎是非常高的时间对于管理员说。所以他问聪明的人应该如何改善。

[当你有一个产品表时主键时产品ID，但是你没有其他索引在这张表里。因此，当根据产品名称查询产品时，数据库引擎没有方式，会扫描所有物理排序的数据页，直到找到需要的书。]

这个聪明的人告诉图书馆管理员“好吧，因为你已经按照书的编号排序了所有的书，你不可能重新整理这些。所以，更好为所有书名创建一个目录或索引和它对应号码的地方。但是，在这目录里，整理书名的字母顺序排列和分组书名，如果有一些想找的书名“Database Management System”，你可以通过以下步骤找到书籍”

1、跳过进入“D”部分的书名，并且找到书名放在哪里

2、找到书的编号

“你是个天才”图书管理员大声说。经过数小时他创建书名目录，使用书名测试后现在只需要一分钟就能找到需要的书。

图书管理员认为，人们会请求书名等几个标准查找。所以，他创建其他相似的目录。并且发现共同的标准（书号，名字，作者名称），只需要1分钟就能查找。苦难的图书管理员很快缓解了人们的排队，因为能真正的快速。

图书管理员从此生活愉快。故事结束。

现在，我确定你现在理解了什么是索引，为什么他们重要以及内部是如何工作的。例如如果我们有一张产品表，创建一个聚集索引（创建主键列时自动创建），在产品名称列创建非聚集索引。如果我们做了这些，数据库引擎创建一个非聚集索引的索引树（像，图书名称那样排序）从产品名称排序在索引页上。每索引页与他们对应的主关键字一起将包含一些产品范围名称。所以，当一个产品查询时，这个产品的名称查询，数据库引擎将首先查询产品名称的非聚集索引树。一次查找，数据库引擎然后查找聚集索引树根据主键查找到实际的书所载的行。

索引树看起来就像：

图示：索引树结构

这叫做B+Tree（平衡的树）。当搜索树索引从根节点开始时，中间节点包含范围值并且只sql引擎到何处去，叶子节点是包含实际给定的值。如果这是一个聚集索引，叶子节点是物理数据页。如果是非聚集索引，叶子节点包含索引值，沿着索引值数据库引擎能找到聚集索引树上的值。

通常，在索引树种找到一个需要的值和跳过从那里的实际行花费的数据库引擎的时间极小。所以，索引时一般改善数据查询操作。

因此，在你的数据库中时刻应用索引最快的返回结果集。

根据这些步骤，保证适当的索引建立在你的数据库中。

在你的数据库中，每个表都建立主键。

这将保证每个表都有一个聚集索引的创建，并且，这些页在表中进行物理排序。所以一些从数据表中取数据操作使用主键或一些排序操作，在表上可以最快的排序。

下列条件适合创建非聚集索引：

1、经常使用的查询条件；

2、使用JOIN其他表；

3、使用外键；

4、高选择性（总行数在（0%-5%）之间的列）

5、使用排序的列；

6、对象是XML（主要和二次索引需要被创造。 更多在此在以后的文章上）

下面一个再表上创建索引的例子

CREATE INDEX

NCLIX_OrderDetails_ProductID ON

dbo.OrderDetails(ProductID) 

或者您使用sql管理器来创建

step2：创建适当的遮盖索引

因此，在你的数据库中你有创建所有适当的索引的，好？假如，你在销售表（SelesID,SalesDate,SalesPersonID,ProductID,Qty）创建一个外键列（productID）的过程中。现在，假设那，ProductID是一个高选择性列（选择未超过5%总数的行ProductID值，在查询中），一些查询语句从这张表快速查询，好？

是，比较与外键列上创建索引，一个全表扫描（扫描所有相关页在表里的需要数据）。但是，然而，改善查询的范围。

让我们假定那些，销售表包含10000行记录，用sql查询400行（4%记录行）。

语句：SELECT SalesDate, SalesPersonID FROM Sales WHERE ProductID = 112 

让我们尝试理解数据库引擎怎么被执行。

1、销售表先查找非聚集索引ProductID列，所以，它寻找非聚集索引树上查找ProductID=112；

2、在索引页包含ProductID=112也存在所有的聚集索引列（所有主键值，那是SalesIDs，那有ProductID=112假定主键列是已经创建在销售表中）；

3、在主键（400这里），在符合的数据页中，数据库引擎查询聚集索引列查找真实列位置；

4、查询主键，当找到，从符合的行中，数据库引擎查询SalesDate和SalesPersonID列值。

请记住上面的几步，查询ProductID=112的信息，数据库引擎查询聚集索引，取出附加的行。

那像是，沿着包含的聚集索引，如果非聚集索引页可能也包含在询问指定的其他两个列（SalesDate，SalesPersonID）。sql引擎在上述步骤必须执行第三步和第四步，因此，能由“seeking”快速的选择与其的结果，从索引页

代码：

CREATE INDEX NCLIX_Sales_ProductID--Index name
ON dbo.Sales(ProductID)--Column on which index is to be created
INCLUDE(SalesDate, SalesPersonID)--Additional column values to include

第三步：重组索引

好，在你的表中你创建了所有合适的索引。或，多半，索引已经被创建在数据库表中。但是，你或许没有根据您的期望获得比较好的效果。

有可能，索引发生了碎片。

什么是索引碎片？

索引碎片是在索引页上进行插入，更新，删除操作造成的索引页分裂。如果索引有很多的碎片，扫描和查询索引需要的时间将更多。所以取数据操作执行很慢。

两种类型的碎片会出现：

内部碎片：由于数据在删除或更新操作，索引或数据页会分散（造成许多空的行）。增加查询的时间。

外部碎片：在索引页中数据插入或更新操作在配置一个新的索引页在文件系统中，表现为减少where查询结果集。并且，数据库服务器不可能利用预读操作，下相关数据页没有被保证接触的，这些下页可能任何地方在数据文件。

怎么知道是否发生了索引碎片？

在你的数据库中执行下列sql。（sql2005或较早的数据库，替换数据库名“AdventureWorks）

SELECT object_name(dt.object_id) Tablename,si.name
IndexName,dt.avg_fragmentation_in_percent AS
ExternalFragmentation,dt.avg_page_space_used_in_percent AS
InternalFragmentation
FROM
(
    SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
    FROM sys.dm_db_index_physical_stats (db_id('AdventureWorks'),null,null,null,'DETAILED'
)
WHERE index_id <> 0) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_id
AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC

根据结果，你会发现索引碎片发生在哪里，使用以下标准。

1、ExternalFragmentation 值 > 10 表明对应的内部碎片发生了，

2、InternalFragmentation 值 < 75 表明出现了外部碎片。

怎么管理这些碎片？

你有两条路：

1、重新整理索引，执行以下语句：

ALTER INDEX ALL ON TableName REORGANIZE

2、重新创建索引，执行以下语句：

ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=90,ONLINE=ON) 

在表里你也能重新创建或整理个别索引，使用索引名称代替“ALL”关键字。或者，使用sql管理器重建索引。

什么时候使用整理索引，什么时候使用重新创建索引？

外部碎片值在10-15以及内部碎片在60-75之间可以选择整理，其他情况选择重建索引。

重建索引时，有个重要的事情，当在一个特别的表上重建索引时，整个表将锁住，因此在一个大的产品数据库，锁住是不被允许的，因为，重建索引需要数小时。幸运的是，在SQL Server 2005中有一个办法。你能使用ONLINE操作在重建索引时，可以重建索引，且不锁表。

第二部分：：：：：

第四步：移动sql语句从应用程序中到数据库服务器

我知道你可以不喜欢这个建议。你获取已经使用ORM生成访问sql。或者，你或你的团队可能有个原则，保持sql在你的应用里。但是，然而，如果你需要优化数据库访问性能，或如果你需要在你的应用里寻找故障，我将建议你移动你的sql语句写在数据库中。为什么？好，我有以下理由：

1、从应用把sql移出来，你可以使用存储过程/视图/函数/触发器 去除一些重复的SQL。这将确保你的sql代码的可用性。

2、实现所有的SQL代码使用数据库对象，将可能找到其中无效率的SQL，那是性能慢的责任。并且，这将你处理SQL代码。

3、你的SQL可以使用先进的索引集数（去看看系列的最新部分）。并且，这将帮助您消灭您已经写了的所有程序SQL，

尽管实际上索引将让你在你的应用中迅速找到故障点的性能问题，也许这四步步能立刻给您一个真正的表现助力。但是，这主要将使您执行其他随后优化步和容易地申请不同进一步优选您的数据存取惯例的其他技术。

如果您使用ORM实施在您的应用的数据存取数据，你可能发现你的应用，能很好的执行在你的开发测试环境里。但是生产环境就不一定了。选择ORM时，您可以随时转换您的数据库类型。

第五步：识别无效率的SQL,分析使用最优的方法

无论多好的索引在你的数据库中，如果你使用简单的条件访问数据库，你一定得到缓慢的表现。

我们总是想写好的代码，不是吗？当我们为一个特殊要求写一个数据操作。。。但是，许多选择，我们有不同的才干，经历和观点的人必须在一个团队里一起工作。应此我们的团队以不同的方式写代码，并且丢失了最优的方法。当写代码时，我们首先想到的是让他工作。但是我们的代码在生产环境跑，就会有问题。

现在时间验证代码，时间最好的验证你的代码。

我有一些Sql调优方法你能做。但是，我肯定你已经知道了大多数。问题是，在实际中，你没有在你的代码中很好的实现（当然，你总是有一些好的理由不这么做）。但是会发生什么，你的代码跑得缓慢，并且你的客户变得不开心。

所以，知道最优的方法是不够的。更多重要的部分是，你写更好的SQL，这是更重要的事。

一些sql最优的方法

不要写“SELECT *”的Sql语句

1、不必要的列会增加所消耗的时间；

2、数据库引擎不能利用“覆盖索引（Covered Index）”（之前文章有讨论），执行会缓慢。

避免不必要的列和表的join条件

1、查询不必要的列会增加查询开销，特别是增加的列时大的类型时；

2、包含不必要的表的join查询，会强制数据库引擎去取不必要的数据和增加执行事件。

不要使用含有COUNT()的子查询

例如：SELECT column_list FROM table WHERE 0 < (SELECT count(*) FROM table2 WHERE ..) 

可以使用：SELECT column_list FROM table WHERE EXISTS (SELECT * FROM table2 WHERE ...)  

1、当你使用COUNT()时，sql server不知道你要做的是一个存在的验证。他计数所有匹配的值，通过表扫描或扫描最小的非聚集索引；

2、当你使用EXISTS时，sql server知道你是做存在的验证。他会找第一个匹配的值，它返回true就停止查看。同样适用使用COUNT()代替IN或ANY。

尝试避免连接两种类型的列

1、当join两列不同类型时，一列必须转换成另一列类型。谁的类型低转换谁。

2、如果你join表属于不相容的类型，其中一个能使用索引，则查询优化不能使用这个索引。例如：

SELECT column_list FROM small_table, large_table WHERE

smalltable.float_column = large_table.int_column 

这种情况下int列会转换成float列。因为int低于float。它不能使用larget表的int列的索引，虽然能使用small表float列上的索引。

尝试避免锁住

1、总是访问表在同样顺序存储过程和触发器。

2、保持你的事务尽可能短。尽量少的数据交换。

3、在一个事物中不要等待用户的中间输入。

写SQL使用宁可使用“组合途径（set based approach）”不要使用“程序途径（Procedural approach）”

1、数据库引擎基于集合sql被设置最佳。因此，应该避免使用程序方法（使用游标处理集合行）操作大的结果集（超过1000行）；

2、怎样清除“程序sql”？看这些例子：

--使用内联子查询替换用户定义函数。

--使用有关系的自查询替换游标代码；

--如果程序编码真的是必须的。至少，使用一个表变量替代游标操作结果。

不要使用COUNT(*)获得表的记录数

避免使用动态SQL

避免使用临时表

替换LIKE查询，使用全字符查询文本数据

尝试使用UNION替换OR操作

大对象使用延迟加载策略

使用VARCHAR(MAX), VARBINARY(MAX) 和 NVARCHAR(MAX) 

1、在sqlserver2000一行不能存储超过8000bytes的值。SQLserver内部页有8kb的限制。所以存储更多数据在单列中，你需要使用TEXT,NTEXT或IMAGE等数据类型。

在用户自定义函数中实施跟随好练习

在存储过程中实施跟随好练习

在触发器中实施跟随好练习

在视图中实施跟随好练习

在事务中实施跟随好练习

第三部分；；；

第六步：应用一些高级的索引技巧

在xml列上创建索引

主要的xml索引
CREATE PRIMARY XML INDEX
index_name
ON <object> ( xml_column )  

次要的索引
CREATE XML INDEX
index_name
ON <object> ( xml_column )
USING XML INDEX primary_xml_index_name
FOR { VALUE | PATH | PROPERTY }

第七步：使用非正常，历史表和预先列；

第四部分；；；；；

第八步：诊断性能问题，有效的使用sql profiler和Performance Monitoring tool