MYSQL教程之sql server优化50法

平台即服务PaaS、基础设施即服务IaaS、软件即服务SaaS都是我们比较熟悉的MySQL学习教程，现在又出现了数据库即服务DBaaS，以基于云的方式存储结构化数据。查询速率慢的缘故原由良多，罕见以下几种：

1、没有索引大概没有效到索引(这是查询慢最多见的成绩，是程序计划的缺点)

2、I/O吞吐量小，构成了瓶颈效应。

3、没有创立盘算列招致查询不优化。

4、内存不敷

5、收集速率慢

6、查询出的数据量过年夜（能够接纳屡次查询，其他的办法下降数据量）

7、锁大概逝世锁(这也是查询慢最多见的成绩，是程序计划的缺点)

8、sp_lock,sp_who,举动的用户检察,缘故原由是读写合作资本。

9、前往了不用要的行和列

10、查询语句欠好，没有优化
能够经由过程以下办法来优化查询:

1、把数据、日记、索引放到分歧的I/O设备上，增添读取速率，之前能够将Tempdb应放在RAID0上，

SQL2000不在撑持。数据量（尺寸）越年夜，进步I/O越主要.

2、纵向、横向支解表，削减表的尺寸(sp_spaceuse)

3、晋级硬件

4、依据查询前提,创建索引,优化索引、优化会见体例，限定了局集的数据量。注重添补因子要得当（最

好是利用默许值0）。索引应当只管小，利用字节数小的列建索引好（参照索引的创立）,不要对无限的

几个值的字段建单一索引如性别字段

5、进步网速;

6、扩展服务器的内存,Windows2000和SQLserver2000能撑持4-8G的内存。设置假造内存：假造内存年夜

小应基于盘算机上并发运转的服务举行设置。运转MicrosoftSQLServer?2000时，可思索将假造内

存巨细设置为盘算机中安装的物理内存的1.5倍。假如别的安装了全文检索功效，并盘算运转

Microsoft搜刮服务以便实行全文索引和查询，可思索：将假造内存巨细设置为最少是盘算机中安装的

物理内存的3倍。将SQLServermaxservermemory服务器设置选项设置为物理内存的1.5倍（虚

拟内存巨细设置的一半）。

7、增添服务器CPU个数;可是必需分明并行处置串行处置更必要资本比方内存。利用并行仍是串路程是

MsSQL主动评价选择的。单个义务分化成多个义务，就能够在处置器上运转。比方延宕查询的排序、毗连

、扫描和GROUPBY字句同时实行，SQLSERVER依据体系的负载情形决意最优的并行品级，庞大的必要消

耗大批的CPU的查询最合适并行处置。可是更新操纵UPDATE,INSERT，DELETE还不克不及并行处置。

8、假如是利用like举行查询的话，复杂的利用index是不可的，可是全文索引，耗空间。likea%使

用索引like%a不利用索援用like%a%查询时，查询耗时和字段值总长度成反比,以是不克不及用

CHAR范例，而是VARCHAR。关于字段的值很长的建全文索引。

9、DBServer和applicationServer分别；OLTP和OLAP分别

10、散布式分区视图可用于完成数据库服务器团结体。团结体是一组分隔办理的服务器，但它们互相协

作分管体系的处置负荷。这类经由过程分区数据构成数据库服务器团结体的机制可以扩展一组服务器，以支

持年夜型的多层Web站点的处置必要。有关更多信息，拜见计划团结数据库服务器。（参照SQL匡助文件

分区视图）

a、在完成分区视图之前，必需先程度分区表

b、在创立成员表后，在每一个成员服务器上界说一个散布式分区视图，而且每一个视图具有不异的称号。这

样，援用散布式分区视图名的查询能够在任何一个成员服务器上运转。体系操纵好像每一个成员服务器上

都有一个原始表的复本一样，但实在每一个服务器上只要一个成员表和一个散布式分区视图。数据的地位

对使用程序是通明的。

11、重修索引DBCCREINDEX,DBCCINDEXDEFRAG,压缩数据和日记DBCCSHRINKDB,DBCCSHRINKFILE.

设置主动压缩日记.关于年夜的数据库不要设置数据库主动增加，它会下降服务器的功能。在T-sql的写法

上有很年夜的考究，上面列出罕见的要点：起首，DBMS处置查询企图的历程是如许的：

1、查询语句的词法、语法反省

2、将语句提交给DBMS的查询优化器

3、优化器做代数优化和存取路径的优化

4、由预编译模块天生查询计划

5、然后在符合的工夫提交给体系处置实行

6、最初将实行了局前往给用户其次，看一下SQLSERVER的数据寄存的布局：一个页面的巨细为8K

(8060)字节，8个页面为一个盘区，依照B树寄存。

12、Commit和rollback的区分Rollback:回滚一切的事物。Commit:提交以后的事物.没有需要在静态

SQL里写事物，假如要写请写在表面如：begintranexec(@s)committrans大概将静态SQL写成函数

大概存储历程。

13、在查询Select语句顶用Where字句限定前往的行数,制止表扫描,假如前往不用要的数据，华侈了服务

器的I/O资本，减轻了收集的包袱下降功能。假如表很年夜，在表扫描的时代将表锁住，克制其他的连接访

问表,成果严峻。

14、SQL的正文声名对实行没有任何影响
15、尽量不利用光标，它占用大批的资本。假如必要row-by-row地实行，只管接纳非光标手艺,如：在

客户端轮回，用一时表，Table变量，用子查询，用Case语句等等。游标能够依照它所撑持的提取选项进

行分类：只进必需依照从第一行到最初一行的按次提取行。FETCHNEXT是独一同意的提取操纵,也是

默许体例。可转动性能够在游标中任何中央随机提取恣意行。游标的手艺在SQL2000下变得功效很壮大

，他的目标是撑持轮回。有四个并发选项READ_ONLY：不同意经由过程游标定位更新(Update)，且在构成结

果集的行中没有锁。OPTIMISTICWITHvalueS:悲观并发把持是事件把持实际的一个尺度部分。悲观并

发把持用于如许的情况，即在翻开游标及更新行的距离中，只要很小的时机让第二个用户更新某一行。

当某个游标以此选项翻开时，没有锁把持个中的行，这将有助于最年夜化其处置才能。假如用户试图修正

某一行，则此行确当前值会与最初一次提取此行时猎取的值举行对照。假如任何值产生改动，则服务器

就会晓得其别人已更新了此行，并会前往一个毛病。假如值是一样的，服务器就实行修正。选择这个并

发选项OPTIMISTICWITHROWVERSIONING:此悲观并发把持选项基于行版本把持。利用行版本把持，其

中的表必需具有某种版本标识符，服务器可用它来断定该行在读进游标后是不是有所变动。在SQLServer

中，这本性能由timestamp数据范例供应，它是一个二进制数字，暗示数据库中变动的绝对按次。每一个

数据库都有一个全局以后工夫戳值：@@DBTS。每次以任何体例变动带有timestamp列的行时，SQL

Server先在工夫戳列中存储以后的@@DBTS值，然后增添@@DBTS的值。假如某个表具有timestamp

列，则工夫戳会被记到行级。服务器就能够对照某行确当前工夫戳值和前次提取时所存储的工夫戳值，

从而断定该行是不是已更新。服务器不用对照一切列的值，只需对照timestamp列便可。假如使用程序对

没有timestamp列的表请求基于行版本把持的悲观并发，则游标默许为基于数值的悲观并发把持。

SCROLLLOCKS这个选项完成失望并发把持。在失望并发把持中，在把数据库的行读进游标了局集时，应

用程序将试图锁定命据库行。在利用服务器游标时，将行读进游标时会在其上安排一个更新锁。假如在

事件内翻开游标，则该事件更新锁将一向坚持到事件被提交或回滚；当提取下一行时，将撤除游标锁。

假如在事件外翻开游标，则提取下一行时，锁就被抛弃。因而，每当用户必要完整的失望并发把持时，

游标都应在事件内翻开。更新锁将制止任何别的义务猎取更新锁或排它锁，从而制止别的义务更新该行

。但是，更新锁其实不制止共享锁，以是它不会制止别的义务读取行，除非第二个义务也在请求带更新锁

的读取。转动锁依据在游标界说的SELECT语句中指定的锁提醒，这些游标并发选项能够天生转动锁。

转动锁在提取时在每行上猎取，并坚持到下次提取大概游标封闭，以先产生者为准。下次提取时，服务

器为新提取中的行猎取转动锁，并开释前次提取中行的转动锁。转动锁自力于事件锁，并能够坚持到一

个提交或回滚操纵以后。假如提交时封闭游标的选项为关，则COMMIT语句其实不封闭任何翻开的游标，

并且转动锁被保存到提交以后，以保护对所提取数据的断绝。所猎取转动锁的范例取决于游标并发选项

和游标SELECT语句中的锁提醒。锁提醒只读悲观数值悲观行版本把持锁定无提醒未锁定未锁定

未锁定更新NOLOCK未锁定未锁定未锁定未锁定HOLDLOCK共享共享共享更新UPDLOCK毛病更

新更新更新TABLOCKX毛病未锁定未锁定更新别的未锁定未锁定未锁定更新*指定NOLOCK提

示将使指定了该提醒的表在游标内是只读的。

16、用PRofiler来跟踪查询，失掉查询所需的工夫，找出SQL的成绩地点;用索引优化器优化索引

17、注重UNion和UNionall的区分。UNIONall好

18、注重利用DISTINCT，在没有需要时不要用，它同UNION一样会使查询变慢。反复的纪录在查询里是没

有成绩的

19、查询时不要前往不必要的行、列

20、用sp_configurequerygovernorcostlimit大概SETQUERY_GOVERNOR_COST_LIMIT来限定查询消

耗的资本。当评价查询损耗的资本超越限定时，服务器主动作废查询,在查询之前就抹杀失落。SET

LOCKTIME设置锁的工夫

21、用selecttop100/10Percent来限定用户前往的行数大概SETROWCOUNT来限定操纵的行

22、在SQL2000之前，一样平常不要用以下的字句:"ISNULL","","!=","!>","!<","NOT","NOT

EXISTS","NOTIN","NOTLIKE",and"LIKE%500"，由于他们不走索引满是表扫描。也不要在WHere

字句中的列名加函数，如Convert，substring等,假如必需用函数的时分，创立盘算列再创立索引来替换

.还能够变通写法：WHERESUBSTRING(firstname,1,1)=m改成WHEREfirstnamelikem%（索引扫

描），必定要将函数和列名分隔。而且索引不克不及建得太多和太年夜。NOTIN会屡次扫描表，利用EXISTS、

NOTEXISTS，IN,LEFTOUTERJOIN来替换，出格是左毗连,而Exists比IN更快，最慢的是NOT操纵.如

果列的值含有空，之前它的索引不起感化，如今2000的优化器可以处置了。不异的是ISNULL，"NOT",

"NOTEXISTS","NOTIN"能优化她，而""等仍是不克不及优化，用不到索引。

23、利用QueryAnalyzer，检察SQL语句的查询企图和评价剖析是不是是优化的SQL。一样平常的20%的代码占有

了80%的资本，我们优化的重点是这些慢的中央。

24、假如利用了IN大概OR等时发明查询没有走索引，利用显现声名指定索引：SELECT*FROM

PersonMember(INDEX=IX_Title)WHEREprocessidIN(男，女)

25、将必要查询的了局事后盘算好放在表中，查询的时分再SELECT。这在SQL7.0之前是最主要的手腕。

比方病院的住院费盘算。

26、MIN()和MAX()能利用到符合的索引。

27、数据库有一个准绳是代码离数据越近越好，以是优先选择Default,顺次为Rules,Triggers,

Constraint（束缚如外健主健CheckUNIQUE……,数据范例的最年夜长度等等都是束缚）,Procedure.如许不

仅保护事情小，编写程序质量高，而且实行的速率快。

28、假如要拔出年夜的二进制值到Image列，利用存储历程，万万不要用内嵌INsert来拔出(不知java是不是)

。由于如许使用程序起首将二进制值转换成字符串（尺寸是它的两倍），服务器遭到字符后又将他转换

成二进制值.存储历程就没有这些举措:办法：Createprocedurep_insertasinsertintotable

(Fimage)values(@image),在前台挪用这个存储历程传进二进制参数，如许处置速率分明改良。

29、Between在某些时分比IN速率更快,Between可以更快地依据索引找到局限。用查询优化器可见赴任别

。select*fromchineseresumewheretitlein(男,女)Select*fromchineseresumewhere

between男and女是一样的。因为in会在对照屡次，以是偶然会慢些。

30、在需要是对全局大概部分一时表创立索引，偶然可以进步速率，但不是必定会如许，由于索引也耗

费大批的资本。他的创立同是实践表一样。

31、不要建没有感化的事物比方发生报表时，华侈资本。只要在需要利用事物时利用它。

32、用OR的字句能够分化成多个查询，而且经由过程UNION毗连多个查询。他们的速率只同是不是利用索引有

关,假如查询必要用到团结索引，用UNIONall实行的效力更高.多个OR的字句没有效到索引，改写成

UNION的情势再试图与索引婚配。一个关头的成绩是不是用到索引。

33、只管罕用视图，它的效力低。对视图操纵比间接对表操纵慢,能够用storedprocedure来取代她。特

其余是不要用视图嵌套,嵌套视图增添了寻觅原始材料的难度。我们看视图的实质：它是寄存在服务器上

的被优化好了的已发生了查询计划的SQL。对单个表检索数据时，不要利用指向多个表的视图，间接从

表检索大概仅仅包括这个表的视图上读，不然增添了不用要的开支,查询遭到搅扰.为了加速视图的查询

，MsSQL增添了视图索引的功效。

34、没有需要时不要用DISTINCT和ORDERBY，这些举措能够改在客户端实行。它们增添了分外的开支。

这同UNION和UNIONALL一样的事理。

selecttop20ad.companyname,comid,position,ad.referenceid,worklocation,convert(varchar

(10),ad.postDate,120)aspostDate1,workyear,degreedescriptionFROM

jobcn_query.dbo.COMPANYAD_queryadwherereferenceIDin

(JCNAD00329667,JCNAD132168,JCNAD00337748,JCNAD00338345,
JCNAD00333138,JCNAD00303570,JCNAD00303569,
JCNAD00303568,JCNAD00306698,JCNAD00231935,JCNAD00231933,
JCNAD00254567,JCNAD00254585,JCNAD00254608,
JCNAD00254607,JCNAD00258524,JCNAD00332133,JCNAD00268618,
JCNAD00279196,JCNAD00268613)orderbypostdatedesc

35、在IN前面值的列表中，将呈现最频仍的值放在最后面，呈现得起码的放在最初面，削减判别的次数

。

36、当用SELECTINTO时，它会锁住体系表(sysobjects，sysindexes等等)，堵塞其他的毗连的存取。创

建一时表时用显现声名语句，而不是selectINTO.droptablet_lxhbegintranselect*into

t_lxhfromchineseresumewherename=XYZ--commit在另外一个毗连中SELECT*fromsysobjects

能够看到SELECTINTO会锁住体系表，Createtable也会锁体系表(不论是一时表仍是体系表)。以是

万万不要在事物内利用它！！！如许的话假如是常常要用的一时表请利用实表，大概一时表变量。

37、一样平常在GROUPBY个HAVING字句之前就可以剔除过剩的行，以是只管不要用它们来做剔除行的事情。他

们的实行按次应当以下最优：select的Where字句选择一切符合的行，GroupBy用来分组个统计行，

Having字句用来剔除过剩的分组。如许GroupBy个Having的开支小，查询快.关于年夜的数据行举行分组

和Having非常损耗资本。假如GroupBY的目标不包含盘算，只是分组，那末用Distinct更快

38、一次更新多笔记录比分屡次更新每次一条快,就是说批处置好

39、罕用一时表，只管用了局集和Table类性的变量来取代它,Table范例的变量比一时表好

40、在SQL2000下，盘算字段是能够索引的，必要满意的前提以下：

a、盘算字段的表达是断定的

b、不克不及用在TEXT,Ntext，Image数据范例

c、必需配制以下选项ANSI_NULLS=ON,ANSI_PADDINGS=ON,…….

41、只管将数据的处置事情放在服务器上，削减收集的开支，如利用存储历程。存储历程是编译好、优

化过、而且被构造到一个实行计划里、且存储在数据库中的SQL语句，是把持流言语的汇合，速率固然快

。重复实行的静态SQL,可使用一时存储历程，该历程（一时表）被放在Tempdb中。之前因为SQL

SERVER对庞大的数学盘算不撑持，以是不能不将这个事情放在其他的层上而增添收集的开支。SQL2000支

持UDFs,如今撑持庞大的数学盘算，函数的前往值不要太年夜，如许的开支很年夜。用户自界说函数象光标一

样实行的损耗大批的资本，假如前往年夜的了局接纳存储历程

42、不要在一句话里再三的利用不异的函数，华侈资本,将了局放在变量里再挪用更快

43、SELECTCOUNT(*)的效力教低，只管变通他的写法，而EXISTS快.同时请注重区分：selectcount

(Fieldofnull)fromTable和selectcount(FieldofNOTnull)fromTable的前往值是分歧的！

！！

44、当服务器的内存够多时，配制线程数目=最年夜毗连数+5，如许能发扬最年夜的效力；不然利用配制

线程数目<最年夜毗连数启用SQLSERVER的线程池来办理,假如仍是数目=最年夜毗连数+5，严峻的伤害服务

器的功能。

45、依照必定的序次来会见你的表。假如你先锁住表A，再锁住表B，那末在一切的存储过程当中都要依照

这个按次来锁定它们。假如你（不经意的）某个存储过程当中先锁定表B，再锁定表A，这大概就会招致一

个逝世锁。假如锁定按次没有被事后具体的计划好，逝世锁很难被发明

46、经由过程SQLServerPerformanceMonitor监督响应硬件的负载Memory:PageFaults/sec计数器如

果该值偶然走高，标明事先有线程合作内存。假如延续很高，则内存多是瓶颈。
Process:

1、%DPCTime指在典范距离时代处置器用在缓延程序挪用(DPC)吸收和供应服务的百分比。(DPC正在

运转的为比尺度距离优先权低的距离)。因为DPC是以特权形式实行的，DPC工夫的百分比为特权工夫

百分比的一部分。这些工夫独自盘算而且不属于距离盘算总数的一部分。这个总数显现了作为实例工夫

百分比的均匀忙时。

2、%ProcessorTime计数器　假如该参数值延续凌驾95%，标明瓶颈是CPU。能够思索增添一个处置器或

换一个更快的处置器。

3、%PrivilegedTime指非闲置处置器工夫用于特权形式的百分比。(特权形式是为操纵体系组件和操

纵硬件驱动程序而计划的一种处置形式。它同意间接会见硬件和一切内存。另外一种形式为用户形式，它

是一种为使用程序、情况分体系和整数分体系计划的一种无限处置形式。操纵体系将使用程序线程转换

成特权形式以会见操纵体系服务)。特权工夫的%包含为中断和DPC供应服务的工夫。特权工夫比率

高多是因为失利设备发生的年夜数目的距离而引发的。这个计数器将均匀忙时作为样本工夫的一部分显

示。

4、%UserTime暗示泯灭CPU的数据库操纵，如排序，实行aggregatefunctions等。假如该值很高，可

思索增添索引，只管利用复杂的表连接，程度支解年夜表格等办法来下降该值。PhysicalDisk:Curretn

DiskQueueLength计数器该值应不凌驾磁盘数的1.5~2倍。要进步功能，可增添磁盘。

SQLServer:CacheHitRatio计数器该值越高越好。假如延续低于80%，招考虑增添内存。注重该参数值

是从SQLServer启动后，就一向累加记数，以是运转经由一段工夫后，该值将不克不及反应体系以后值。

47、剖析selectemp_nameformemployeewheresalary>3000在此语句中若salary是Float范例的，

则优化器对其举行优化为Convert(float,3000)，由于3000是个整数，我们应在编程时利用3000.0而不要

等运转时让DBMS举行转化。一样字符和整型数据的转换。

48、查询的联系关系同写的按次

selecta.personMemberID,*fromchineseresumea,personmemberbwherepersonMemberID=

b.referenceidanda.personMemberID=JCNPRH39681（A=B,B=号码）

selecta.personMemberID,*fromchineseresumea,personmemberbwherea.personMemberID=

b.referenceidanda.personMemberID=JCNPRH39681andb.referenceid=JCNPRH39681（A=

B,B=号码，A=号码）

selecta.personMemberID,*fromchineseresumea,personmemberbwhereb.referenceid=

JCNPRH39681anda.personMemberID=JCNPRH39681（B=号码，A=号码）

49、

(1)IF没有输出卖力人代码THENcode1=0code2=9999ELSEcode1=code2=卖力人代码ENDIF实行SQL

语句为:SELECT卖力人名FROMP2000WHERE卖力人代码>=:code1AND卖力人代码<=:code2

(2)IF没有输出卖力人代码THEN　SELECT卖力人名FROMP2000ELSEcode=卖力人代码SELECT负

责人代码FROMP2000WHERE卖力人代码=:codeENDIF第一种办法只用了一条SQL语句,第二种办法用

了两条SQL语句。在没有输出卖力人代码时,第二种办法明显比第一种办法实行效力高,由于它没无限制条

件;在输出了卖力人代码时,第二种办法仍旧比第一种办法效力高,不但是少了一个限定前提,还因相称运

算是最快的查询运算。我们写程序不要怕贫苦

50、关于JOBCN如今查询分页的新办法（以下），用功能优化器剖析功能的瓶颈，假如在I/O大概收集的

速率上，以下的办法优化实在无效，假如在CPU大概内存上，用如今的办法更好。请辨别以下的办法，说

明索引越小越好。

begin

DECLARE@local_variabletable(FIDintidentity(1,1),ReferenceIDvarchar(20))

insertinto@local_variable(ReferenceID)

selecttop100000ReferenceIDfromchineseresumeorderbyReferenceID

select*from@local_variablewhereFid>40andfid<=60

end和

begin

DECLARE@local_variabletable(FIDintidentity(1,1),ReferenceIDvarchar(20))

insertinto@local_variable(ReferenceID)

selecttop100000ReferenceIDfromchineseresumeorderbyupdatedate

select*from@local_variablewhereFid>40andfid<=60

end的分歧

begin

createtable#temp(FIDintidentity(1,1),ReferenceIDvarchar(20))

insertinto#temp(ReferenceID)

selecttop100000ReferenceIDfromchineseresumeorderbyupdatedate

select*from#tempwhereFid>40andfid<=60droptable#temp

end



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/sarah0601/archive/2009/12/21/5049424.aspx列举选择MySQL的理由的最困难的地方在于，如何对这些理由进行排序。MySQL学习教程这就如同我们经常争论的故事：先有鸡还是先有蛋?

总感觉自己还是不会SQL

从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。

作了些试验，发现使用CLR的存储过程或函数在达到一定的阀值的时候，系统性能会呈指数级下滑！这是非常危险的！只使用几个可能没有问题，当一旦大规模使用会造成严重的系统性能问题！

仓酷云

一个百万级别的基本信息表A，一个百万级别的详细记录表B，A中有个身份证id，B中也有身份id；先要找出A中在B的详细记录。

你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。

对于微软系列的东西除了一遍遍尝试还真没有太好的办法

无法深入到数据库系统层面去了解和探究

也可谈一下你是怎么优化存储过程的?