MSSQL Server查询优化方法 |
|
查问速度慢的缘由众多，常见如下几种： 1、没有索引或者没有用到索引(这是 查问慢最常见的问题，是程序设计的缺点) 2、I/O吞吐量小， 构成了瓶颈效应 。 3、没有 缔造计算列招致 查问不优化 。 4、内存缺乏 5、网络速度慢 6、 查问出的数据量过大( 可以采纳 频繁 查问， 其余的 步骤减低数据量) 7、锁或者死锁(这也是 查问慢最常见的问题，是程序设计的缺点)sp_lock,sp_who, 运动的消费者查看,缘由是读写竞争资源 。 9、返回了 毋庸要的行和列 10、 查问语句不好，没有优化 可以通过如下 步骤来优化 查问 : 1、把数据、日志、索引放到不同的I/O 设施上，添加读取速度，以往 可以将Tempdb应放在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树 存放 。 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:乐观并发控制是事务控制 实际的一个 标准 部分 。乐观并发控制用于这样的 情景，即在 打开游标及更新行的 间隔中，惟独很小的机会让第二个消费者更新某一行 。当某个游标以此选项 打开时，没有锁控制其中的行，这将有助于最大化其 解决 威力 。假如消费者试图 批改某一行，则此行的目前值会与最终一次提取此行时猎取的值进行 比较 。假如任何值 产生转变，则服务器就会晓得 其余人已更新了此行，并会返回一个 舛误 。假如值是一样的，服务器就执行 批改 。 取舍这个并发选项仁褂没Щ虺绦蛟背械Ｔ鹑危砟切┍硎酒渌没б丫云浣辛诵薷牡拇砦蟆Ｓτ贸绦蚴盏秸庵执砦笫辈扇〉牡湫痛胧┚褪撬⑿掠伪辏竦闷湫轮担缓笕糜没Ь龆ㄊ欠穸孕轮到行薷摹?BR>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来插入(不知JAVA是不是) 。由于这样 利用程序首先将二进制值转换成字符串(尺寸是它的两倍)，服务器受到字符后又将他转换成二进制值.存储过程就没有这些动作: 步骤：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 all执行的效率更高.多个OR的字句没有用到索引，改写成UNION的 模式再试图与索引匹配 。一个 要害的问题是不是用到索引 。 33、尽量少用视图，它的效率低 。对视图操作比直接对表操作慢, 可以用stored procedure来 接替她 。特殊的是不要用视图嵌套,嵌套视图添加了寻觅原始 材料的难度 。我们看视图的 性质：它是 存放在服务器上的被优化好了的已经产生了 查问规划的SQL 。对单个表检索数据时，不要 使用指向多个表的视图，直接从表检索或者仅仅包括这个表的视图上读，不然添加了 毋庸要的开销, 查问受到 烦扰.为了加速视图的 查问，MsSQL添加了视图索引的 性能 。 34、没有必要时不要用DISTINCT和ORDER BY，这些动作 可以改在客户端执行 。它们添加了额外的开销 。这同UNION 和UNION ALL一样的 情理 。 SELECT top 20 ad.companyname,comid,position,ad.referenceid,worklocation, convert(varchar(10),ad.postDate,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 35、在IN后面值的列表中，将浮现最频繁的值放在最前面，浮现得 起码的放在最终面，削减推断的次数 。 36、当用SELECT INTO时，它会锁住系统表(sysobjects，sysindexes等等)，堵塞 其余的衔接的存取 。 缔造暂时表时用显示声明语句，而不是select INTO. drop table t_lxh begin tran select * into t_lxh from chineseresume where name = 'XYZ' --commit 在另一个衔接中SELECT * from sysobjects 可以看到 SELECT INTO 会锁住系统表，Create table 也会锁系统表( 无论是暂时表还是系统表) 。所以千万不要在事物内 使用它！！！这样的话假如是 时常要用的暂时表请 使用实表，或者暂时表变量 。 37、普通在GROUP BY 个HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作 。他们的执行顺序应该如下最优：select 的Where字句 取舍全部 合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组 。这样Group By 个Having的开销小， 查问快.关于大的数据 前进行分组和Having非常 消费资源 。假如Group BY的 目标不包括计算，只不过分组，那么用Distinct更快 41、一次更新多条记录比分 频繁更新每次一条快,便是说批 解决好 42、少用暂时表，尽量用 后果集和Table类性的变量来 接替它,Table 类型的变量比暂时表好 43、在SQL2000下，计算字段是 可以索引的，需求满足的条件如下： a、计算字段的 抒发是确定的 b、不能用在TEXT,Ntext，Image数据类型 c、必须配制如下选项 ANSI_NULLS = ON, ANSI_PADDINGS = ON, ……. 44、尽量将数据的 解决工作放在服务器上，削减网络的开销，如 使用存储过程 。存储过程是编译好、优化过、而且被组织到一个执行规划里、且存储在数据库中的SQL 语句，是控制流语言的 集中，速度固然快 。 反复执行的动态SQL, 可以 使用暂时存储过程，该过程(暂时表)被放在Tempdb中 。 以往由于SQL SERVER对复杂的数学计算不 支撑，所以只能将这个工作放在 其余的层上而添加网络的开销 。SQL2000 支撑UDFs,现在 支撑复杂的数学计算，函数的返回值不要太大，这样的开销很大 。消费者自定义函数象光标一样执行的 消费大量的资源，假如返回大的 后果采纳存储过程 45、不要在一句话里再三的 使用 雷同的函数， 浪费资源,将 后果放在变量里再调用更快 46、SELECT COUNT(*)的效率教低，尽量变通他的写法，而EXISTS快.同时请 留神区别： select count(Field of null) from Table 和 select count(Field of NOT null) from Table 的返回值是不同的！！！ 47、当服务器的内存够多时，配制线程数量 = 最大衔接数+5，这样能 施展最大的效率； 不然 使用 配制线程数量<最大衔接数启用SQL SERVER的线程池来解决,假如还是数量 = 最大衔接数+5，严峻的伤害服务器的性能 。 48、依照 定然的 秩序来 拜访你的表 。假如你先锁住表A，再锁住表B，那么在全部的存储过程中都要依照这个顺序来锁定它们 。假如你(不经意的)某个存储过程中先锁定表B，再锁定表A，这可能就 会招致一个死锁 。假如锁定顺序没有被预先 详尽的设计好，死锁很难被发现 49、通过SQL Server Performance Monitor 监督相应硬件的负载 Memory: Page Faults / sec计数器 假如该值间或走高，表明当时有线程竞争内存 。假如 连续很高，则内存可能是瓶颈 。 Process: 1、 % DPC Time 指在范例 间隔期间 解决器用在缓延程序调用(DPC) 接纳和提供服务的百分比 。(DPC 正在运行的为比 标准 间隔优先权低的 间隔) 。 由于 DPC 是以特权模式执行的，DPC 工夫的百分比为特权 工夫百分比的一 部分 。这些 工夫 径自计算而且不属于 间隔计算总数的一部 分 。这个总数显示了作为实例 工夫百分比的 均匀忙时 。 2、%Processor Time计数器 假如该参数值 连续超过95%，表明瓶颈是CPU 。 可以考量添加一个 解决器或换一个更快的 解决器 。 3、% Privileged Time 指非闲置 解决器 工夫用于特权模式的百分比 。(特权模式是为操作系统组件和控制硬件驱动程序而设计的一种 解决模式 。它同意直接 拜访硬件和全部内存 。另一种模式为消费者模式，它是一种为 利用程序、环境分系统和整数分系统设计的一种有限 解决模式 。操作系统将 利用程序线程转换成特权模式以 拜访操作系统服务) 。 特权 工夫的 % 包括为间断和 DPC 提供服务的 工夫 。特权 工夫比率高可能是由于失败 设施产生的大数量的 间隔而引起的 。这个计数器将 均匀忙时作为样本 工夫的一 部分显示 。 4、% User Time 示意 消费CPU的数据库操作，如排序，执行aggregate functions等 。假如该值很高，可考量增 加索引，尽量 使用 方便的表联接，水平分割大表格等 步骤来减低该值 。 Physical Disk: Curretn Disk Queue Length计数器 该值应不超过磁盘数的1.5'2倍 。要 普及性能，可添加磁盘 。 SQLServer:Cache Hit Ratio计数器 该值越高越好 。假如 连续低于80%， 应试量添加内存 。 留神该参数值是从SQL Server启动后，就向来累加记数，所以运行 通过一段 工夫后，该值将不能反映系统目前值 。 40、 综合select emp_name form employee where salary > 3000 在此语句中若salary是Float类型的，则优化器对其进行优化为Convert(float,3000)，由于3000是个整数，我们应在编程时 使用3000.0而不要等运行时让DBMS进行转化 。同样字符和整型数据的转换 。 41、 查问的关联同写的顺序 select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' (A = B ,B = ‘号码’) select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' (A = B ,B = ‘号码’， A = ‘号码’) select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' (B = ‘号码’， A = ‘号码’) 42、(1) IF 没有输入负责人代码 THEN code1=0 code2=9999 ELSE code1=code2=负责人代码 END IF 执行SQL语句为: SELECT 负责人名 FROM P2000 WHERE 负责人代码>=:code1 AND负责人代码 <=:code2 (2) IF 没有输入负责人代码 THEN SELECT 负责人名 FROM P2000 ELSE code= 负责人代码 SELECT 负责人代码 FROM P2000 WHERE 负责人代码=:code END IF 第一种 步骤只用了一条SQL语句,第二种 步骤用了两条SQL语句 。在没有输入负责人代码时,第二种 步骤显然比第一种 步骤执行效率高,由于它没有 制约条件;在输入了负责人代码时,第二种 步骤 依旧比第一种 步骤效率高,不只不过少了一个 制约条件,还因相等运算是最快的 查问运算 。我们写程序不要怕麻烦 43、关于JOBCN现在 查问分页的新 步骤(如下)，用性能优化器 综合性能的瓶颈，假如在I/O或者网 络的速度上，如下的 步骤优化切实有效，假如在CPU或者内存上，用现在的 步骤更好 。请 划分如下的 步骤， 注明索引越小越好 。 begin DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20)) insert into @local_variable (ReferenceID) select top 100000 ReferenceID from chineseresume order by ReferenceID select * from @local_variable where Fid > 40 and fid <= 60 end 和 begin DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20)) insert into @local_variable (ReferenceID) select top 100000 ReferenceID from chineseresume order by updatedate select * from @local_variable where Fid > 40 and fid <= 60 end 的不同 begin create table #temp (FID int identity(1,1),ReferenceID varchar(20)) insert into #temp (ReferenceID) select top 100000 ReferenceID from chineseresume order by updatedate select * from #temp where Fid > 40 and fid <= 60 drop table #temp end |
