五种提高SQL性能的方法

johnny papa 　　偶尔， 为了让运用步调运转得更快，所做的十足处事即是在这边或何处做少许很小安排。啊，但要害在乎决定怎样举行安排！早晚您会遇到这种情景：运用步调中的 sql 查问不许依照您想要的办法举行相应。它要么不归来数据，要么奢侈的功夫长得稀奇。即使它贬低了汇报或您的企业运用步调的速率，用户必需等候的功夫过长，她们就会很不合意。就像您的双亲不想听您证明干什么在深更深夜才回顾一律，用户也不会听你证明干什么查问奢侈这么长功夫。（“抱歉，妈妈，我运用了太多的 left join。”）用户蓄意运用步调相应赶快，她们的汇报不妨在刹时之内归来领会数据。就我本人而言，即使在 web 上越野时某个页面要奢侈十多秒本领加载（好吧，五秒更本质少许），我也会很不耐心。为领会决那些题目，要害的是找到题目的基础。那么，从何处发端呢？基础因为常常在乎数据库安排和考察它的查问。在本月的专栏中，我将报告四项本领，那些本领可用来普及鉴于 sql server? 的运用步调的本能或革新其可舒卷性。我将提防证明 left join、cross join 的运用以及 identity 值的检索。请记取，基础没有神秘的处置计划。安排您的数据库及其查问须要占用功夫、举行领会，还须要洪量的尝试。那些本领都已被表明卓有成效，但对您的运用步调而言，大概个中少许本领比另少许本领更实用。本页实质 从 insert 归来 identity 内嵌视图与偶尔表 制止 left join 和 null 精巧运用笛卡尔乘积 拣到补零 从 insert 归来 identity 我确定从遇到很多题目的实质动手：怎样在实行 sql insert 后检索 identity 值。常常，题目不在乎怎样编写检索值的查问，而在乎在何处以及何时举行检索。在 sql server 中，底下的语句可用来检索由最新在震动数据库贯穿上运转的 sql 语句所创造的 identity 值：select @@identity这个 sql 语句并不搀杂，但须要记取的一点是：即使这个最新的 sql 语句不是 insert，大概您对准非 insert sql 的其余贯穿运转了此 sql，则不会赢得憧憬的值。您必需运转下列代码本领检索跟不上在 insert sql 之后且坐落同一贯穿上的 identity，如次所示：insert into products (productname) values ('chalk')select @@identity在一个贯穿上对准 northwind 数据库运转那些查问将归来一个称呼为 chalk 的新产物的 identity 值。以是，在运用 ado 的 visual basic? 运用步调中，不妨运转以次语句：set ors = ocn.execute("set nocount on;insert into products _(productname) values ('chalk');select @@identity")lproductid = ors(0)此代码报告 sql server 不要归来查问的行计数，而后实行 insert 语句，并归来方才为这个新行创造的 identity 值。set nocount on 语句表白归来的记载集有一条龙和一列，个中包括了这个新的 identity 值。即使没有此语句，则会开始归来一个空的记载集（由于 insert 语句不归来任何数据），而后会归来第二个记载集，第二个记载会合包括 identity 值。这大概有些令人迷惑，更加是由于您历来就没有蓄意过 insert 会归来记载集。之以是会爆发此情景，是由于 sql server 看到了这个行计数（即一条龙遭到感化）并将其证明为表白一个记载集。所以，真实的数据被推回到了第二个记载集。固然您不妨运用 ado 中的 nextrecordset 本领获得此第二个记载集，但即使总不妨开始归来该记载集且只归来该记载集，则会更简单，也更有功效。此本领固然灵验，但须要在 sql 语句中特殊增添少许代码。赢得沟通截止的另一本领是在 insert 之前运用 set nocount on 语句，并将 select @@identity 语句放在表中的 for insert 触发器中，如底下的代码片断所示。如许，任何加入该表的 insert 语句都将机动归来 identity 值。create trigger trproducts_insert on products for insert as select @@identity go触发器只在 products 表上爆发 insert 时启用，以是它老是会在胜利 insert 之后归来一个 identity。运用此本领，您不妨一直以沟通的办法在运用步调中检索 identity 值。归来页首内嵌视图与偶尔表 某些功夫，查问须要将数据与其余少许大概只能经过实行 group by 而后实行规范查问本领搜集的数据举行联接。比方，即使要查问最新五个存单的相关消息，您开始须要领会是哪些存单。这不妨运用归来存单 id 的 sql 查问来检索。此数据就会保存在偶尔表（这是一个常用本领）中，而后与 products 表举行联接，以归来那些存单售出的产物数目：create table #temp1 (orderid int not null, _ orderdate datetime not null)insert into #temp1 (orderid, orderdate)select top 5 o.orderid, o.orderdatefrom orders o order by o.orderdate descselect p.productname, sum(od.quantity) as productquantityfrom #temp1 t inner join [order details] od on t.orderid = od.orderidinner join products p on od.productid = p.productid group by p.productnameorder by p.productnamedrop table #temp1那些 sql 语句会创造一个偶尔表，将数据插入该表中，将其余数据与该表举行联接，而后取消该偶尔表。这会引导此查问举行洪量 i/o 操纵，所以，不妨从新编写查问，运用内嵌视图代替偶尔表。内嵌视图不过一个不妨联接到 from 子句中的查问。以是，您不必在 tempdb 中的偶尔表上奢侈洪量 i/o 和磁盘考察，而不妨运用内嵌视图获得同样的截止：select p.productname, sum(od.quantity) as productquantityfrom (select top 5 o.orderid, o.orderdatefrom orders o order by o.orderdate desc) t inner join [order details] od on t.orderid = od.orderidinner join products p on od.productid = p.productid group byp.productnameorder byp.productname此查问不只比前方的查问功效更高，并且长度更短。偶尔表会耗费洪量资源。即使只须要将数据联接到其余查问，则不妨试试运用内嵌视图，以俭朴资源。归来页首制止 left join 和 null 固然，有很多功夫您须要实行 left join 和运用 null 值。然而，它们并不实用于一切情景。变换 sql 查问的建立办法大概会爆发将一个花几秒钟运转的汇报减少到只花几秒钟如许的云泥之别的功效。偶尔，必需在查问中安排数据的样式，使之符合运用步调所诉求的表露办法。固然 table 数据典型会缩小洪量占用资源的情景，但在查问中再有很多地区不妨举行优化。sql 的一个有价格的常用工能是 left join。它不妨用来检索第一个表中的一切行、第二个表中一切配合的行、以及第二个表中与第一个表不配合的一切行。比方，即使蓄意归来每个存户及其存单，运用 left join 则不妨表露有存单和没有存单的存户。此东西大概会被过渡运用。left join 耗费的资源特殊之多，由于它们包括与 null（不生存）数据配合的数据。在某些情景下，这是不行制止的，然而价格大概特殊高。left join 比 inner join 耗费资源更多，以是即使您不妨从新编写查问以使得该查问不运用任何 left join，则会获得特殊可观的汇报（请参见图 1 中的图）。图 1 查问 加速运用 left join 的查问速率的一项本领波及创造一个 table 数据典型，插入第一个表（left join 左侧的表）中的一切行，而后运用第二个表中的值革新 table 数据典型。此本领是一个两步的进程，但与规范的 left join 比拟，不妨俭朴洪量功夫。一个很好的准则是试验百般各别的本领并记载每种本领所需的功夫，直到赢得用来您的运用步调的实行本能最好的查问。尝试查问的速率时，有需要屡次运转此查问，而后取一个平衡值。由于查问（或保存进程）大概会保存在 sql server 外存中的进程缓存中，所以第一次试验奢侈的功夫犹如稍长少许，而一切后续试验奢侈的功夫都较短。其余，运转您的查问时，大概正在对准沟通的表运转其余查问。当其余查问锁定妥协锁那些表时，大概会引导您的查问要列队等候。比方，即使您举行查问时或人正在革新此表中的数据，则在革新提交时您的查问大概须要奢侈更长功夫来实行。制止运用 left join 时速率贬低的最简片面法是尽大概多地环绕它们安排数据库。比方，假如某一产物大概具备类型也大概没有类型。即使 products 表保存了其类型的 id，而没有效于某个一定产物的类型，则您不妨在字段中保存 null 值。而后您必需实行 left join 来获得一切产物及其类型。您不妨创造一个值为“no category”的类型，进而指定外键联系不承诺 null 值。经过实行上述操纵，此刻您就不妨运用 inner join 检索一切产物及其类型了。固然这看上去犹如是一个带有过剩数据的灵活本领，但大概是一个很有价格的本领，由于它不妨取消 sql 批处置语句中耗费资源较多的 left join。在数据库中十足运用此观念不妨为您俭朴洪量的处置功夫。请记取，对于您的用户而言，纵然几秒钟的功夫也特殊要害，由于当您有很多用户正在考察同一个联机数据库运用步调时，这几秒钟本质上的意旨会特殊宏大。 归来页首精巧运用笛卡尔乘积 对于此本领，我将举行特殊精细的引见，并倡导在某些情景下运用笛卡尔乘积。出于某些因为，笛卡尔乘积 (cross join) 受到了很多诽谤，开拓职员常常会被劝告基础就不要运用它们。在很多情景下，它们耗费的资源太多，进而没辙高效运用。然而像 sql 中的任何东西一律，即使精确运用，它们也会很有价格。比方，即使您想运转一个归来每月数据（纵然某一一定月份存户没有存单也要归来）的查问，您就不妨很简单地运用笛卡尔乘积。 图 2 中的 sql 就实行了上述操纵。固然这看上去犹如没什么神秘的，然而请商量一下，即使您从存户到存单（那些存单按月份举行分批并对出卖额举行小计）举行了规范的 inner join，则只会赢得存户有存单的月份。所以，对于存户未定购任何产物的月份，您不会赢得 0 值。即使您想为每个存户都绘制一个图，以表露每个月和该月出卖额，则大概蓄意此图囊括月出卖额为 0 的月份，再不直觉标识出那些月份。即使运用 图 2 中的 sql，数据则会跳过出卖额为 0 美元的月份，由于在存单表中对于零出卖额不会包括任何行（假如您只保存爆发的事变）。图 3 中的代码固然较长，然而不妨到达获得一切出卖数据（以至囊括没有出卖额的月份）的目的。开始，它会索取客岁一切月份的列表，而后将它们放入第一个 table 数据典型表 (@tblmonths) 中。下一步，此代码会获得在该功夫段内有出卖额的一切存户公司的称呼列表，而后将它们放入另一个 table 数据典型表 (@tblcus-tomers) 中。这两个表保存了创造截止集所必定的一切基础数据，但本质出卖数目之外。 第一个表中列出了一切月份（12 行），第二个表中列出了这个功夫段内有出卖额的一切存户（对于我是 81 个）。并非每个存户在往日 12 个月中的每个月都购置了产物，以是，实行 inner join 或 left join 不会归来每个月的每个存户。那些操纵只会归来购置产物的存户和月份。笛卡尔乘积则不妨归来一切月份的一切存户。笛卡尔乘积基础上是将第一个表与第二个表相加，天生一个行汇合，个中包括第一个表中的行数与第二个表中的行数相加的截止。所以，笛卡尔乘积会向表 @tblfinal 归来 972 行。结果的办法是运用此日子范畴内每个存户的月出卖额合计革新 @tblfinal 表，以及采用最后的行集。即使因为笛卡尔乘积占用的资源大概会很多，而不须要真实的笛卡尔乘积，则不妨精心地运用 cross join。比方，即使对产物和类型实行了 cross join，而后运用 where 子句、distinct 或 group by 来挑选出大普遍行，那么运用 inner join 会赢得同样的截止，并且功效高得多。即使须要为一切的大概性都归来数据（比方在您蓄意运用每月出卖日子弥补一个图表时），则笛卡尔乘积大概会特殊有扶助。然而，您不该当将它们用来其余用处，由于在大普遍计划中 inner join 的功效要高得多。归来页首拣到补零 这边引见其余少许可扶助普及 sql 查问功效的常用本领。假如您将按地区对一切出卖职员举行分批并将她们的出卖额举行小计，然而您只想要那些数据库中标志为居于震动状况的出卖职员。您不妨按地区对出卖职员分批，并运用 having 子句取消那些未居于震动状况的出卖职员，也不妨在 where 子句中实行此操纵。在 where 子句中实行此操纵会缩小须要分批的行数，以是比在 having 子句中实行此操纵功效更高。having 子句中鉴于行的前提的挑选会强迫查问对那些在 where 子句中会被去除的数据举行分批。另一个普及功效的本领是运用 distinct 要害字搜索数据行的独立报表，来包办运用 group by 子句。在这种情景下，运用 distinct 要害字的 sql 功效更高。请在须要计划会合因变量（sum、count、max 等）的情景下再运用 group by。其余，即使您的查问老是本人归来一个独一的行，则不要运用 distinct 要害字。在这种情景下，distinct 要害字只会减少体例开支。您仍旧看到了，有洪量本领都可用来优化查问和实行一定的交易准则，本领即是举行少许试验，而后比拟它们的本能。最要害的是要尝试、尝试、再尝试。在此专栏的未来各期实质中，我将连接深刻报告 sql server 观念，囊括数据库安排、好的索引试验以及 sql server 安定典型。如有向 johnny 提出的题目和倡导，请发送电子邮件到 mmdata@microsoft.com johnny papa 是北卡罗来纳州罗顺利的 mjm 接洽公司的消息本领副总裁，他著有professional ado 25 rds programming with asp(wrox, 2000)，并常常行家业聚会中做报告。要与他接洽，请发送电子邮件到 datapoints@lancelotweb.com