1 概述
在实际的工作中,尤其是在生产环境里边,SQL语句的优化问题十分的重要,它对数据库的性能的提升也起着显著的作用.我们总是在抱怨机器的性能问题,总是在抱怨并发访问所带来的琐问题,但是如果我们对没一条SQL语句进行优化,尽管不能说可以解决全部问题,但是至少可以解决大部分问题。
2 SQL SERVER的索引
索引主要目的是提高了SQL Server系统的性能,加快数据的查询速度与减少系统的响应时间 ,索引是优化SQL脚本的第一手段起着非常重要作用,并且数据量较大的时候,优化效果较为明显。索引是存储引擎用于快速找到记录的一种数据结构。优化SQL语句的关键是尽可能减少语句的logical reads, 索引的最大好处是它可以极大减少SQL语句的logical reads数目,从而极大减少语句的执行时间,logical reads 越少,其需要的内存和CPU时间也就越少,语句执行速度就越快。
2.1 索引的分类
索引分为聚集索引和非聚集索引。聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续。聚集索引一个表只能有一个,检索效率比普通索引高 , 但对数据新增/修改/删除的影响比较大。而非聚集索引一个表可以存在多个,检索效率比聚集索引低 , 对数据新增/修改/删除的影响很小。
2.2 索引的设计
在设计阶段,可以根据功能和性能的需求进行初步的索引设计,这里需要根据预计的数据量和查询来设计索引,可能与将来实际使用的时候会有所区别。
关于索引的选择,应改主意:
A、根据数据量决定哪些表需要增加索引,数据量小的可以只有主键。
B、根据使用频率决定哪些字段需要建立索引,选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。
C、把经常一起出现的字段组合在一起,组成组合索引,组合索引的字段顺序与主键一样,也需要把最常用的字段放在前面,把重复率低的字段放在前面。
D、一个表不要加太多索引,因为索引影响插入和更新的速度。
2.3 索引的语法
Ø 创建索引CREATE [UNIQUE][CLUSTERED | NONCLUSTERED] INDEX index_name
ON table_name[字段,....n]]
说明:
UNIQUE: 建立唯一索引。
CLUSTERED: 建立聚集索引。
NONCLUSTERED: 建立非聚集索引。
UNIQUE索引既可以采用聚集索引结构,也可以采用非聚集索引的结构,如果不指明采用的索引结构,则SQL Server系统默认为采用非聚集索引结构。
Ø 删除索引语法
DROP INDEX table_name.index_name[,table_name.index_name]
说明:table_name: 索引所在的表名称。
index_name : 要删除的索引名称。
Ø 显示索引信息
使用系统存储过程:sp_helpindex 查看指定表的索引信息。
2.4 填充因子
使用 fill factor 选 项可以指定 Microsoft SQL Server 使用现有数据创建新索引时将每页填满到什么程度。由于在页填充时 SQL Server 必须花时间来拆分页,因此填充因子会影响性能。仅在创建或重新生成索引时使用填充因子。页面不会维护在任何特定的填充水平上。 fill factor 的默认值为 0,有效值介于 0 和 100 之间。FILLFACTOR 设置为 0 或 100 时,叶级别几乎完全填满,但至少会保留一个其他索引行的空间。这样设置后,叶级别空间会得到有效利用,而且仍有空间可以在必须拆分页之前进行有限扩展。很少需要更改 fill factor 的默认值,因为可以使用 CREATE INDEX 或 ALTER INDEX REBUILD 语句来覆盖其对于指定索引的值。
2.5 索引的维护
如果你的表已经创建好了索引,但性能却仍然不好,那很可能是产生了索引碎片,你需要进行索引碎片整理。
2.5.1 什么是索引碎片?
由于表上有过度地插入、修改和删除操作,索引页被分成多块就形成了索引碎片,如果索引碎片严重,那扫描索引的时间就会变长,甚至导致索引不可用,因此数据检索操作就慢下来了。
2.5.2 如何知道是否发生了索引碎片
关于索引碎片的查看,可以通过以下DMV语句进行
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME() AS DatbaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] INTO #TempFragmentation FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE 1=2 EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempFragmentation SELECT TOP 20 DB_NAME() AS DatbaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE s.database_id = DB_ID() AND i.name IS NOT NULL AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 ORDER BY [Fragmentation %] DESC' SELECT top 20 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC DROP TABLE #TempFragmentation
看到了,这部分索引的碎片到大了99%...这就需要我们重建进行维护了,否则将严重拖垮数据的性能。
2.在SQLServer数据库,通过DBCC ShowContig或DBCC ShowContig(表名)检查索引碎片情况,指导我们对其进行定时重建整理。通过对扫描密度(过低),扫描碎片(过高)的结果分析,判定是否需要索引重建,主要看如下两个:
Scan Density [Best Count:Actual Count]-扫描密度[最佳值:实际值]:DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100%。低了则说明有外部碎片。
Logical Scan Fragmentation-逻辑扫描碎片:无序页的百分比。该百分比应该在0%到10%之间,高了则说明有外部碎片。
2.5.3 索引维护
Ø 利用DBCC INDEXDEFRAG索引重组
如果逻辑扫描碎片在10%~30%,就建议用DBCC INDEXDEFRAG来进行索引重组,重组并不产生新页,但是会压缩页,如果页面已经为空,将会移除这个页,所以填充因子选项是不需要的。同时,因为它不会锁住对象,所以总是联机处理。
DBCC INDEXDEFRAG(‘数据库名’,’表名’,’索引名’)
Ø 利用DBCC DBREINDEX重建索引
如果逻辑扫描碎片超过30%,那么重建索引会比较好,在重建索引时,会锁住资源,直到进程完毕为止。重建大表索引会非常耗时,所以不要不耐烦并停止重建操作,这样会引起一些危险的后果,并可能使得数据库进入恢复模式。
DBCC DBREINDEX(‘表名’)
索引维护前
索引维护后
2.6 索引的建议
1、不要索引数据量不大的表,对于小表来讲,表扫描的成本并不高。
2、不要设置过多的索引,在没有聚集索引的表中,最大可以设置249个非聚集索引,过多的索引首先会带来更大的磁盘空间,而且在数据发生修改时,对索引的维护是特别消耗性能的。
3、合理应用复合索引,有某些情况下可以考虑创建包含所有输出列的覆盖索引,其前导列一定是使用最频繁的列
4、对经常使用范围查询的字段,可能考虑聚集索引。
5、避免对不常用的列,逻辑性列(例如性别),大字段列(例如text)创建索引。
3 SQL SERVER 事件探索器
3.1 事件探索器作用
通过事件探索器进行跟踪SQL执行语句,筛选执行比较慢的SQL语句,为进一步分析和优化作准备。
3.2 事件探索器方法
在运行项目或执行批量SQL语句的时侯,打开SQL SERVER 事件探索运行器,在跟踪属性的筛选列中选择CPU和Duration大于或等于选项(可以设置为1000毫秒),把执行慢的SQL语句进行筛选,定位出问题的语句之后就可以具体分析了。
4 SQL SERVER 显示估计的执行计划
4.1 显示估计的执行计划作用
执行计划就是用于描述SQL引擎在执行一个sql语句时的所有步骤,通过执行计划,我们可以知道哪个表是驱动表,如何访问一个表:是通过索引访问还是通过表扫描,如何进行连接:使用嵌套连接,合并连接还是哈希连接,连接的顺序等等;
查询优化器是一个基本成本分析的优化器,它会为每条语句生成若干个执行计划,然后尝试找到成本最低的执行计划。 执行计划选取影响因素有很多,常规的有统计值、索引,数据量等等。 同语句where条件不同会导致满足条件数据量变化,查询优化器就会选择不同的计划。 需要说明的是,成本只是“估算”成本,优化器最后选择执行计划未必是最优计划。这就引出了强制查询使用某索引,干预执行计划的选择。 如:SELECT ID FROM T WITH(INDEX(索引名)) WHERENUM=@NUM。
4.2 显示估计的执行计划操作步聚
在查询分析器中,选择数据库,输入SQL语句,点击显示估计的执行计划,查看执行计划。
4.3 显示估计的执行计划表扫描比较
Ø “Index Seek”在性能改善上比“Index Scan”和“Table Scan”要好, “Index Seek”不需要对表和索引页进行扫描;而“Table Scan”和“Index Scan”需要。“Index Seek”利用“WHERE”来过滤获取的数据,这样比用“Index Scan”和“Table Scan”快很多。
Ø 从改善性能角度考虑,“Clustered Index Seek”比“Clustered Index Scan”和“Index Seek”要好。“Clustered Index Seek”不需要扫描整个聚集索引页。和“Index Scan”相比,对于检索选择的字段包含那些没有索引的字段时,“Clustered Index Seek”不会有“Bookmark Lookup”方法出现。和“Index Scan”相比,对于检索选择的字段包含那些没有索引的字段时,“Clustered Index Seek”不会有“Bookmark Lookup”方法出现。
Ø 常见的有没有建索引或索引建立不合理,会出现table scan或index scan,凡是看到SCAN,就意味着会做全表或全索引扫描,这是带来的必然是读次数过多。我们期望看到的是seek或键查找。
5 SQL SERVER 的STATISTICS调优命令
5.1 SET SHOWPLAN_ALL ON
显示查询计划,以及运行该计划的估计开销,以及大量额外的统计信息,显示图形执行计划工具,将SHOWPLAN_ALL中的一些片段进行组合,并封装到一个单个的图形化格式中。
5.2 SET STATISTICS IO ON
显示执行查询所必需的实际工作的关键信息片刻,提供的信息包括:物理读取(从磁盘上读取的实际物理页面,绝不会大于逻辑读取数值的数值,经常变化第二次查询会比第一次查询少,对放入到缓存中将不再进行物理读取),逻辑读取(页面被请求发生的逻辑读取),预读读取(SQL SERVER读入到缓存中的页面数量,作为预读机制预期将需要的页面的结果而被读入),扫描计数(表被访问的次数)
在生产环境里边SET STATISTICS IO ON关注scan count(计数)------查询读取的表数量;logical read( 逻辑读)次数很重要。
5.3 SET STATISTICS TIME ON
显示执行查询所要求的实际CPU时间执行的扫描时间。
在生产环境里边SET STATISTICS TIME ON看cpu时间。
6 SQL SERVER表分区,索引分区
6.1 为什么要表分区?
当一个表的数据量太大的时候,我们最想做的一件事是什么?将这个表一分为二或者更多分,但是表还是这个表,只是将其内容存储分开,这样读取就快了N倍了
原理:表数据是无法放在文件中的,但是文件组可以放在文件中,表可以放在文件组中,这样就间接实现了表数据存放在不同的文件中。能分区存储的还有:表、索引和大型对象数据 。
SQL SERVER 2005中,引入了表分区的概念, 当表中的数据量不断增大,查询数据的速度就会变慢,应用程序的性能就会下降,这时就应该考虑对表进行分区,当一个表里的数据很多时,可以将其分拆到多个的表里,因为要扫描的数据变得更少 ,查询可以更快地运行,这样操作大大提高了性能,表进行分区后,逻辑上表仍然是一张完整的表,只是将表中的数据在物理上存放到多个表空间(物理文件上),这样查询数据时,不至于每次都扫描整张表。
6.2 什么时候使用分区表:
1、表的大小超过2GB。
2、表中包含历史数据,新的数据被增加到新的分区中。
6.3 表分区的优缺点
表分区有以下优点:
1、改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。
2、增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用;
3、维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可;
4、均衡I/O:可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能。
缺点:
分区表相关:已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能.
6.4 表分区的操作
6.4.1 创建文件组
建立分区表先要创建文件组,而创建多个文件组主要是为了获得好的 I/O 平衡。一般情况下,文件组数最好与分区数相同,并且这些文件组通常位于不同的磁盘上。每个文件组可以由一个或多个文件构成,而每个分区必须映射到一个文件组。
ALTER DATABASE [DeanDB] ADD FILEGROUP [FG1]
DeanDB为数据库名称,FG1文件组名。创建文件组后,再使用 ALTER DATABASE 将文件添加到该文件组中:
ALTER DATABASE [DeanDB] ADD FILE ( NAME = N'FG1', FILENAME = N'C:DeanDataFG1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG1]
类似的建立三个文件和文件组,并把每一个存储数据的文件放在不同的磁盘驱动器里。
6.4.2 创建分区函数
创建分区表必须先确定分区的功能机制,表进行分区的标准是通过分区函数来决定的。
CREATE PARTITION FUNCTION xx1(int)
AS RANGE LEFT FOR VALUES (10000, 20000);
注释:创建分区函数:myRangePF2,以INT类型分区,分三个区间,10000以内在A 区,1W-2W在B区,2W以上在C区.
6.4.3 创建分区方案
创建分区表必须先确定分区的功能机制,表进行分区的标准是通过分区函数来决定的。
CREATE PARTITION SCHEME myRangePS2
AS PARTITION xx1
TO (a, b, c);
注释:在分区函数XX1上创建分区架构:myRangePS2,分别为A,B,C三个区间
A,B,C分别为三个文件组的名称,而且必须三个NDF隶属于这三个组,文件所属文件组一旦创建就不能修改
6.4.4 对表进行分区
常用数据规范--数据空间类型修改为:分区方案,然后选择分区方案名称和分区列列表。
CREATE TABLE [dbo].[AvCache](
[AVNote] [varchar](300) NULL,
[bb] [int] IDENTITY(1,1)
) ON [myRangePS2](bb); --注意这里使用[myRangePS2]架构,根据bb分区
6.4.5 查询表分区
SELECT *, $PARTITION.[myRangePF2](bb) FROM dbo.AVCache
这样就可以清楚的看到表数据是如何分区的了
6.4.6 创建索引分区
7 SQL SERVER性能监控
7.1 SQL SERVER性能监控作用
监视关键的计数器,并进行趋势分析(可以利用Microsoft Excel为工具)。例如,利用收集的数据进行趋势分析,有助于预测SQL Server对硬件的需求,如是否需要更多的CPU,更快的I/O设备或更多内存,定位是否有硬件性能瓶径,下面是关键的性能计数器:
7.1.1 监视CPU的使用率
Ø Processor: % Privileged Time对应于处理器执行 Microsoft Windows 内核命令(例如处理 SQL Server I/O 请求)所用时间的百分比。
Ø Processor: %User Time应于处理器执行用户进程(例如 SQL Server)所用时间的百分比。
Ø System: Processor Queue Length对应于等待处理器时间的线程数。当一个进程的线程需要的处理器循环数超过可获得的循环数时,就产生了处理器瓶颈。
7.1.2 监视磁盘 I/O
Ø PhysicalDisk: % Disk Time 计数器监视磁盘忙于读/写活动所用时间的百分比。
Ø PhysicalDisk: Avg. Disk Queue Length 计数器了解等待进行磁盘访问的系统请求数量。
7.1.3 监视内存使用量
Ø Memory: Available Mbytes:提供系统上可用内存的数量。
Ø Memory: Pages/sec:显示有多少页被用于读或写入硬盘,这些基于硬页面错误。
Ø Paging File:%Usage:显示挂起的总数的百分比。
7.1.4 SQL Server 使用的内存量
Ø SQL Server: Buffer Manager: Buffer cache hit ratio:返回SQLServer从缓存但不是从硬盘返回的数据的百分比。
Ø SQL Server: Buffer Manager: Page life expectancy:显示数据驻留在内存的平均秒数
Ø SQL Server: Buffer Manager: Memory Grants Pending:等待内存工作区授予的进程数。
7.2 SQL SERVER性能监控步聚
打开可靠性和性能监视器,在【运行】中输入perfmon.exe,选择性能监视器,去除所有已存在的计数器,添加新计数器,选择所要监视的服务器,选择下面的计数器:
Processor: % Privileged Time
Processor: %User Time
System: Processor Queue Length
PhysicalDisk: % Disk Time
PhysicalDisk: Avg. Disk Queue Length
Memory: Available Mbytes
Memory: Pages/sec
Paging File:%Usage
SQL Server: Buffer Manager: Buffer cache hit
SQL Server: Buffer Manager: Page life expectancy
SQL Server: Memory Manager: Memory Grants Pending
点击确定。
7.3 SQL SERVER性能监控分析
Ø 一个确定 CPU 使用率的有效方法是使用系统监视器中的 Processor:% Processor Time 计数器。该计数器监视 CPU 执行非闲置线程所用的时间。持续 80% 到 90% 的状态可能表明需要升级 CPU 或需要增加更多的处理器。
Ø System: Processor Queue Length对应于等待处理器时间的线程数。当一个进程的线程需要的处理器循环数超过可获得的循环数时,就产生了处理器瓶颈。若使用率为 100% 左右(表示在处理大量的客户端请求),可能表示进程正在排队,等待处理器时间,并因而导致出现瓶颈。可以通过增加速度更快的处理器来解决这一问题。
Ø Current Disk Queue Length 和 % Disk Time 计数器的值检测磁盘子系统中的瓶颈。PhysicalDisk: % Disk Time 计数器的值较高(大于 90%),请检查 PhysicalDisk: Current Disk Queue Length 计数器了解等待进行磁盘访问的系统请求数量。如果 Current Disk Queue Length 和 % Disk Time 计数器的值一直很高请考虑使用速度更快的磁盘驱动器,将某些文件移至其他磁盘或服务器。如果正在使用一个 RAID 阵列,则在该阵列中添加磁盘。
Ø 检查Memory: Available Mbytes,这个值意味着系统的可用内存。如果发现这个值经常很低,可能表示服务器内存不足,在生产数据库中,这个值可以使用GB为单位。
Ø 检查Memory: Pages/sec ,以为这因为硬页面错误导致的从磁盘读或写页面。这个值如果长期高于20,意味着内存不足使得应用程序使用虚拟内存,从而导致挂起。
Ø 检查Paging File:%Usage去预估内存挂起。如果这个值经常超过20%,可能意味着内存不足。
Ø SQL Server: Buffer Manager: Buffer cache hit ratio:意味着数据从缓存中读取的次数,比较合理的值为大于90%。如果该值很低,可能内存不足或者需要检查索引和查询。如果你需要获得大量数据,这一步可能就会占用大量内存然后引起SQLServer从磁盘读数据而不是从内存。检查索引,确保在大表中能尽可能笔描扫描。并尽可能限制查询返回的结果行。
Ø 检查SQL Server: Buffer Manager: Page life expectancy,表示数据页驻留在内存的秒数。微软建议最少300秒。如果在一个实例中经常低于300秒,意味着数据保留的时间少于5分钟就被移出内存。
Ø 如果SQL Server: Memory Manager: Memory Grants Pending经常建议等待进程,你可能需要增加服务器的内存了。
8 SQL SERVER的优化建议
8.1 使用表的别名
当 SQL 语句中涉及到多个表时,使用表的别名,并将别前缀在每一个列上,这样可以减少简析时间,避免一些语法上的歧义造成的错误。
8.2 简化的手段
Ø 不要有超过5个以上的表连接(JOIN)
连接的表越多,其编译的时间和连接的开销也越大,性能越不好控制。最好是把连接拆开成较小的几个部分逐个顺序执行。优先执行那些能够大量减少结果的连接。拆分的好处不仅仅是减少SQL Server优化的时间,更使得SQL语句能够以你可以预测的方式和顺序执行。如果一定需要连接很多表才能得到数据,那么很可能意味着设计上的缺陷。
Ø 考虑使用临时表或表变量存放中间结果
使用临时表或表变量存放中间结果的目标也是简化SQL语句,使得其执行顺序和执行方式得到控制。这个技术在某些时候会产生戏剧化的效果。
Ø 少用子查询
Ø 视图嵌套不要过深
不要有过深的视图嵌套。我就曾经看到有个系统,建立了好多视图,视图里面是复杂的语句,然后在视图的上面再建立视图,系统的视图嵌套有四层之多。我并不是反对使用视图,只是视图越多,语句展开后就越复杂,优化起来就越难。一般视图嵌套不要超过2个为宜。
8.3 合理使用 in , exists 有助于提高效率
In 是把外表和内表做 hash 连接,而 exists 是对外表作 loop 循环,每次 loop 循环在对内表进行查询,对于认为 exists 始终比 in 效率高的说法是不准确的
如果内表和外表大小相当,那么使用 exists 和使用 in 的效率差别不大,如果两个表一个大一个小,则子查询大的表用exists ,子查询小的用 in 。
例如:表 A (大表)、表 B( 小表 )
Sql代码
select * from B where B.c1 in (select A.c1 from A) 效率低
Select * from B where exists(select A.c1 from A where A.c1=B.c1) 效率高
而 not in 和 not exists 相比 not in 对内外表都使用全表扫描,没有使用索引, not exists 仍能对内表使用索引。因此不管内外表谁大, not exists 都比 not in 快。
8.4 “非”操作符无法使用索引 ,如: NOT 、 != 、 <> 、 !< 、 !> 、 NOT IN 、 NOT LIKE
对于以下例子:
Select * from t_ employee where salary <>3000;
可以改写为:
Select * from t_employee where salary<3000 orsalary>3000;
两者的查询结果一样,但是第二种的查询方案会叫第一种更快,第二种查询允许对 salary 列使用索引,第一种不行
8.5 like 操作符
我们在进行模糊查询时,如果有可能尽量少使用 %X% 而是用 AX% 和 BX% ,因为前者会产生全表扫描,后者则是采用索引AX 、 BX 的索引,效率更高。
8.6 IS NULL 或 IS NOT NULL 操作 ( 判断字段是否为空 )
判断字段是否为空会导致系统无法使用索引,因为索引是不索引空值的。
推荐方案:用其它相同功能的操作运算代替,如: a is not null 改为 a>0 或 a>’’ 等。不允许字段为空,而用一个缺省值代替空值,如申请中状态字段不允许为空,缺省为申请。
8.7 字段提取尽量按照“用多少,取多少”的原则,避免使用“select*”
字段大小越大,数目越多, select 所耗费的资源越多,取一个 int 型的数据比取 char 型数据快很多,我们没少取一个字段,数据的提取速度就会有相应的提升。
8.8 不要再索引列上使用计算
在 where 语句中,如果索引列是函数或者是表达式的一部分,优化器将不使用索引,而使用全表扫描,降低 sql 效率。例如:
Sql代码
低效 : select * from T1 where T1.C1*12>35000;
高效 :Select * from T2 where T1.C1>35000/12;
8.9 优化 group by 语句
提高 group by 语句的效率,可以讲不需要的记录在执行 group by 之前过滤掉,如以下示例:
Sql代码
低效: select job,avg(salary) from employee
group by job having job=’teacher’ or job=’manager’
高效: select job ,avg(sal) from employee where job=’teacher’ or job=’manager’
group by job
如果Group BY的目的不包括计算,只是分组,那么用Distinct更快
8.10 避免不必要的类型转换
需要注意的是,尽最避免潜在的数据类型转换。如将字符型数据与数值型数据比较,会自动将字符进行转换,从而导致全表扫描。注意表之间连接的数据类型,避免不同类型数据之间的连接。
8.11 尽量避免反复访问同一张或几张表,尤其是数据量较大的表,可以考虑先根据条件提取数据到临时表中,然后再做连接。
8.12 尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该改写;如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作。
8.13 注意where字句写法,必须考虑语句顺序,应该根据索引顺序、范围大小来确定条件子句的前后顺序,尽可能的让字段顺序与索引顺序相一致,范围从大到小。
8.14 尽量使用exists代替select count(1)来判断是否存在记录,count函数只有在统计表中所有行数时使用,而且count(1)比count(*)更有效率。
8.15 用OR的字句可以分解成多个查询,并且通过UNION 连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引,用UNION ALL执行的效率更高。多个OR的字句没有用到索引,改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索引。
8.16 对于连续的数值,能用 between 就不要用 in。
8.17 尽量少用视图,它的效率低。对视图操作比直接对表操作慢,可以用stored procedure来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的本质:它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时,不要使用指向多个表的视图,直接从表检索或者仅仅包含这个表的视图上读,否则增加了不必要的开销,查询受到干扰。为了加快视图的查询,MsSQL增加了视图索引的功能。
8.18 在IN后面值的列表中,将出现最频繁的值放在最前面,出现得最少的放在最后面,减少判断的次数。对于连续的数值,能用 between 就不要用 in 。
8.19 少用临时表,尽量用结果集和Table类型的变量来代替它,Table 类型的变量比临时表好。
8.20 不要在一句话里再三的使用相同的函数,浪费资源,将结果放在变量里再调用更快。
8.21 如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:
select id from t where num=@num
可以改为强制查询使用索引
select id from t with(index(索引名)) where num=@num
8.22 应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。
8.23 尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
8.24 .在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
清除SQLServer2008缓存
假如某张表中的数据正在被前台读取,而我们在后台通过编辑的方式,直接将数据写入表中。此时,会发现无论我们怎么读取,取出来还是旧的数据,新的数据到哪儿去了呢?
别急,下面的方法尽管试...一般执行第一、第二条语句就可以了。
DBCC DROPCLEANBUFFERS
从缓冲池中删除所有清除缓冲区。
DBCC FREEPROCCACHE
从过程缓存中删除所有元素。
DBCC FREESYSTEMCACHE( 'ALL' )