没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
50
50
50
50 种方法优化 SQL
SQL
SQL
SQL Server
Server
Server
Server 数据库查询
查询速度慢的原因很多,常见如下几种:
1 、没有索引或者没有用到索引 ( 这是查询慢最常见的问题,是程序设计的缺陷 )
2 、 I/O 吞吐量小,形成了瓶颈效应。
3 、没有创建计算列导致查询不优化。
4 、内存不足
5 、网络速度慢
6 、查询出的数据量过大(可以采用多次查询,其他的方法降低数据量)
7 、锁或者死锁 ( 这也是查询慢最常见的问题,是程序设计的缺陷 )
8 、 sp_lock,sp_who, 活动的用户查看 , 原因是读写竞争资源。
9 、返回了不必要的行和列
10 、查询语句不好,没有优化
可以通过如下方法来优化查询 :
1 、把数据、日志、索引放到不同的 I/O 设备上,增加读取速度,以前可以将 Tempd b
应放在 RAID0 上, SQL2000 不支持。数据量(尺寸)越大,提高 I/O 越重要 .
2 、纵向、横向分割表,减少表的尺寸 (sp_spaceuse)
3 、升级硬件
4 、 根据查询条件 , 建立索引 , 优化索引 、 优化访问方式 , 限制结果集的数据量 。 注意填充
因子要适当 ( 最好是使用默认值 0 ) 。 索引应该尽量小 , 使用字节数小的列建索引好 ( 参照索
引的创建) , 不要对有限的几个值的字段建单一索引如性别字段
5 、提高网速 ;
6 、扩大服务器的内存 ,Windows 2000 和 SQL server 2000 能支持 4-8G 的内存。配置虚拟
内存 : 虚拟内存大小应基于计算机上并发运行的服务进行配置 。 运行 Microsoft SQL Server?
2000 时 , 可考虑将虚拟内存大小设置为计算机中安装的物理内存的 1.5 倍 。 如果另外安装
了全文检索功能 , 并打算运行 Microsoft 搜索服务以便执行全文索引和查询 , 可考虑 : 将虚
拟内存大小配置为至少是计算机中安装的物理内存的 3 倍。将 SQL Server max server
memory 服务器配置选项配置为物理内存的 1.5 倍(虚拟内存大小设置的一半 ) 。
7 、增加服务器 CPU 个数 ; 但是必须明白并行处理串行处理更需要资源例如内存。使用
并行还是串行程是 MsSQL 自动评估选择的 。 单个任务分解成多个任务 , 就可以在处理器上
运行 。 例如耽搁查询的排序 、 连接 、 扫描和 GROUP BY 字句同时执行 , SQL SERVER 根据
系统的负载情况决定最优的并行等级 , 复杂的需要消耗大量的 CPU 的查询最适合并行处理
。
但是更新操作 Update,Insert , Delete 还不能并行处理。
8 、如果是使用 like 进行查询的话,简单的使用 index 是不行的,但是全文索引,耗空
间 。 like 'a%' 使用索引 like '%a' 不使用索引用 like '%a%' 查询时 , 查询耗时和字段值总长
度成正比 , 所以不能用 CHAR 类型,而是 VARCHAR 。对于字段的值很长的建全文索引。
9 、 DB Server 和 APPLication Server 分离; OLTP 和 OLAP 分离
10 、 分布式分区视图可用于实现数据库服务器联合体 。 联合体是一组分开管理的服务器
,
但它们相互协作分担系统的处理负荷 。 这种通过分区数据形成数据库服务器联合体的机制能
够扩大一组服务器 , 以支持大型的多层 Web 站点的处理需要 。 有关更多信息 , 参见设计联
合数据库服务器 。 (参照 SQL 帮助文件 ' 分区视图 ' )
a 、在实现分区视图之前,必须先水平分区表
b 、在创建成员表后,在每个成员服务器上定义一个分布式分区视图,并且每个视图具
有相同的名称。这样,引用分布式分区视图名的查询可以在任何一个成员服务器上运行 。 系
统操作如同每个成员服务器上都有一个原始表的复本一样 , 但其实每个服务器上只有一个成
员表和一个分布式分区视图。数据的位置对应用程序是透明的。
11 、重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG, 收缩数据和日志 DBCC
SHRINKDB,DBCC SHRINKFILE. 设置自动收缩日志 . 对于大的数据库不要设置数据库自动
增长,它会降低服务器的性能。在 T-sql 的写法上有很大的讲究,下面列出常见的要点:首
先, DBMS 处理查询计划的过程是这样的:
1 、 查询语句的词法、语法检查
2 、 将语句提交给 DBMS 的查询优化器
3 、 优化器做代数优化和存取路径的优化
4 、 由预编译模块生成查询规划
5 、 然后在合适的时间提交给系统处理执行
6 、 最后将执行结果返回给用户其次,看一下 SQL SERVER 的数据存放的结构:一个
页面的大小为 8K(8060) 字节, 8 个页面为一个盘区,按照 B 树存放。
12 、 Commit 和 rollback 的区别 Rollback: 回滚所有的事物 。 Commit: 提交当前的事物 . 没
有必要在动态 SQL 里写事物 , 如果要写请写在外面如 : begin tran exec(@s) commit trans 或
者将动态 SQL 写成函数或者存储过程。
13 、 在查询 Select 语句中用 Where 字句限制返回的行数 , 避免表扫描 , 如果返回不必要的
数据 , 浪费了服务器的 I/O 资源 , 加重了网络的负担降低性能 。 如果表很大 , 在表扫描的期
间将表锁住,禁止其他的联接访问表 , 后果严重。
14 、 SQL 的注释申明对执行没有任何影响
15 、尽可能不使用光标,它占用大量的资源。如果需要 row-by-row 地执行,尽量采用
非光标技术 , 如:在客户端循环,用临时表, Table 变量,用子查询,用 Case 语句等等。游
标可以按照它所支持的提取选项进行分类 : 只进 必须按照从第一行到最后一行的顺序提取
行 。 FETCH NEXT 是唯一允许的提取操作 , 也是默认方式 。 可滚动性可以在游标中任何地方
随机提取任意行。游标的技术在 SQL2000 下变得功能很强大,他的目的是支持循环。有四
个并发选项 READ_ONLY : 不允许通过游标定位更新 (Update) , 且在组成结果集的行中没有
锁。 OPTIMISTIC WITH valueS: 乐观并发控制是事务控制理论的一个标准部分。乐观并发
控制用于这样的情形 , 即在打开游标及更新行的间隔中 , 只有很小的机会让第二个用户更新
某一行 。 当某个游标以此选项打开时 , 没有锁控制其中的行 , 这将有助于最大化其处理能力
。
如果用户试图修改某一行,则此行的当前值会与最后一次提取此行时获取的值进行比较 。 如
果任何值发生改变 , 则服务器就会知道其他人已更新了此行 , 并会返回一个错误 。 如果值是
一样的 , 服务器就执行修改 。 选择这个并发选项 OPTIMISTIC WITH ROW VERSIONING:
此乐观并发控制选项基于行版本控制 。 使用行版本控制 , 其中的表必须具有某种版本标识符
,
服务器可用它来确定该行在读入游标后是否有所更改。在 SQL Server 中,这个性能由
timestamp 数据类型提供 , 它是一个二进制数字 , 表示数据库中更改的相对顺序 。 每个数据
库都有一个全局当前时间戳值 : @@DBTS 。 每次以任何方式更改带有 timestamp 列的行时
,
SQL Server 先在时间戳列中存储当前的 @@DBTS 值 , 然后增加 @@DBTS 的值 。 如果某
个表具有 timestamp 列,则时间戳会被记到行级。服务器就可以比较某行的当前时间戳值
和上次提取时所存储的时间戳值,从而确定该行是否已更新。服务器不必比较所有列的值
,
只需比较 timestamp 列即可。如果应用程序对没有 timestamp 列的表要求基于行版本控制
的乐观并发,则游标默认为基于数值的乐观并发控制。 SCROLL LOCKS 这个选项实现悲
观并发控制 。 在悲观并发控制中 , 在把数据库的行读入游标结果集时 , 应用程序将试图锁定
数据库行 。 在使用服务器游标时 , 将行读入游标时会在其上放置一个更新锁 。 如果在事务内
打开游标 , 则该事务更新锁将一直保持到事务被提交或回滚 ; 当提取下一行时 , 将除去游标
锁 。 如果在事务外打开游标 , 则提取下一行时 , 锁就被丢弃 。 因此 , 每当用户需要完全的悲
观并发控制时,游标都应在事务内打开。更新锁将阻止任何其它任务获取更新锁或排它锁
,
从而阻止其它任务更新该行 。 然而 , 更新锁并不阻止共享锁 , 所以它不会阻止其它任务读取
行,除非第二个任务也在要求带更新锁的读取。滚动锁根据在游标定义的 Select 语句中指
定的锁提示 , 这些游标并发选项可以生成滚动锁 。 滚动锁在提取时在每行上获取 , 并保持到
下次提取或者游标关闭 , 以先发生者为准 。 下次提取时 , 服务器为新提取中的行获取滚动锁
,
并释放上次提取中行的滚动锁 。 滚动锁独立于事务锁 , 并可以保持到一个提交或回滚操作之
后。如果提交时关闭游标的选项为关,则 COMMIT 语句并不关闭任何打开的游标,而且
滚动锁被保留到提交之后 , 以维护对所提取数据的隔离 。 所获取滚动锁的类型取决于游标并
发选项和游标 Select 语句中的锁提示。锁提示 只读 乐观数值 乐观行版本控制 锁定无提
示 未锁定 未锁定 未锁定 更新 NOLOCK 未锁定未锁定未锁定 未锁定 HOLDLOCK 共
享 共享 共享 更新 UPDLOCK 错误 更新 更新 更新 TABLOCKX 错误 未锁定未锁定
更新其它 未锁定 未锁定 未锁定 更新 * 指定 NOLOCK 提示将使指定了该提示的表在游
标内是只读的。
16 、用 Profiler 来跟踪查询,得到查询所需的时间,找出 SQL 的问题所在 ; 用索引优化
器优化索引
17 、注意 UNion 和 UNion all 的区别。 UNION all 好
18 、注意使用 DISTINCT ,在没有必要时不要用,它同 UNION 一样会使查询变慢。重
复的记录在查询里是没有问题的
19 、查询时不要返回不需要的行、列
20 、用 sp_configure 'query governor cost limit' 或者 SET
QUERY_GOVERNOR_COST_LIMIT 来限制查询消耗的资源 。 当评估查询消耗的资源超出限
制时,服务器自动取消查询 , 在查询之前就扼杀掉。 SET LOCKTIME 设置锁的时间
21 、 用 select top 100 / 10 Percent 来限制用户返回的行数或者 SET ROWCOUNT 来限制
操作的行
22 、 在 SQL2000 以前 , 一般不要用如下的字句 : "IS NULL", "<>", "!=", "!>", "!<", "NOT",
"NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'" , 因为他们不走索引全是表扫描
。
也不要在 Where 字句中的列名加函数,如 Convert , substring 等 , 如果必须用函数的时候 , 创
建计算列再创建索引来替代 . 还可以变通写法: Where SUBSTRING(firstname,1,1) = 'm' 改 为
Where firstname like 'm%' (索引扫描 ) ,一定要将函数和列名分开。并且索引不能建得太多
和太大 。 NOT IN 会多次扫描表 , 使用 EXISTS 、 NOT EXISTS , IN , LEFT OUTER JOIN 来
替代 , 特别是左连接 , 而 Exists 比 IN 更快 , 最慢的是 NOT 操作 . 如果列的值含有空 , 以前它
的索引不起作用 , 现在 2000 的优化器能够处理了 。 相同的 是 IS NULL , "NOT", "NOT EXISTS",
"NOT IN" 能优化她,而 "<>" 等还是不能优化,用不到索引。
23 、使用 Query Analyzer ,查看 SQL 语句的查询计划和评估分析是否是优化的 SQL 。
一般的 20% 的代码占据了 80% 的资源,我们优化的重点是这些慢的地方。
24 、 如果使用了 IN 或者 OR 等时发现查询没有走索引 , 使用显示申明指定索引 : Select
* FROM PersonMember (INDEX = IX_Title) Where processid IN (' 男 ' , ' 女 ')
25 、将需要查询的结果预先计算好放在表中,查询的时候再 Select 。这在 SQL7.0 以前
是最重要的手段。例如医院的住院费计算。
26 、 MIN() 和 MAX() 能使用到合适的索引。
27 、数据库有一个原则是代码离数据越近越好,所以优先选择 Default, 依次 为
Rules,Triggers, Constraint (约束如外健主健 CheckUNIQUE …… , 数据类型的最大长度等等都
是约束) ,Procedure. 这样不仅维护工作小,编写程序质量高,并且执行的速度快。
28 、 如果要插入大的二进制值到 Image 列 , 使用存储过程 , 千万不要用内嵌 Insert 来插
入 ( 不知 JAV A 是否 ) 。因为这样应用程序首先将二进制值转换成字符串(尺寸是它的两倍 )
,
服务器受到字符后又将他转换成二进制值 . 存储过程就没有这些动作 : 方法 : Create procedure
p_insert as insert into table(Fimage) values (@image), 在前台调用这个存储过程传入二进制参
数,这样处理速度明显改善。
29 、 Between 在某些时候比 IN 速度更快 ,Between 能够更快地根据索引找到范围。用查
询优化器可见到差别。 select * from chineseresume where title in (' 男 ',' 女 ') Select * from
chineseresume where between ' 男 ' and ' 女 ' 是一样的 。 由于 in 会在比较多次 , 所以有时会慢些
。
30 、 在必要是对全局或者局部临时表创建索引 , 有时能够提高速度 , 但不是一定会这样
,
因为索引也耗费大量的资源。他的创建同是实际表一样。
31 、 不要建没有作用的事物例如产生报表时 , 浪费资源 。 只有在必要使用事物时使用它
。
32 、 用 OR 的字句可以分解成多个查询 , 并且通过 UNION 连接多个查询 。 他们的速度
只同是否使用索引有关 , 如果查询需要用到联合索引 , 用 UNION al l 执行的效率更高 . 多 个 O R
的字句没有用到索引 , 改写成 UNION 的形式再试图与索引匹配 。 一个关键的问题是否用到
索引。
33 、 尽量少用视图 , 它的效率低 。 对视图操作比直接对表操作慢 , 可以用 stored procedur e
来代替她。特别的是不要用视图嵌套 , 嵌套视图增加了寻找原始资料的难度。我们看视图的
本质:它是存放在服务器上的被优化好了的已经产生了查询规划的 SQL 。对单个表检索数
据时 , 不要使用指向多个表的视图 , 直接从表检索或者仅仅包含这个表的视图上读 , 否则增
加了不必要的开销 , 查询受到干扰 . 为了加快视图的查询, MsSQL 增加了视图索引的功能。
34 、没有必要时不要用 DISTINCT 和 ORDER BY ,这些动作可以改在客户端执行。它
们增加了额外的开销。这同 UNION 和 UNION ALL 一样的道理。
select top 20
20
20
20 ad.companyname,comid,position,ad.referenceid,worklocation, convert ( varchar
varchar
varchar
varchar ( 10
10
10
10 ),a
d.postDate, 120
120
120
120 ) as postDate1,workyear,degreedescription FROM jobcn_query.dbo.COMPANYAD_query
ad where referenceID in ( 'JCNAD00329667' , 'JCNAD132168' , 'JCNAD00337748' , 'JCNAD00338345' ,
'JCNAD00333138' , 'JCNAD00303570' , 'JCNAD00303569' ,
'JCNAD00303568' , 'JCNAD00306698' , 'JCNAD00231935' , 'JCNAD00231933' ,
'JCNAD00254567' , 'JCNAD00254585' , 'JCNAD00254608' ,
'JCNAD00254607' , 'JCNAD00258524' , 'JCNAD00332133' , 'JCNAD00268618' ,
'JCNAD00279196' , 'JCNAD00268613' ) order by postdate desc
剩余20页未读,继续阅读
资源评论
a992650689
- 粉丝: 19
- 资源: 13
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功