仓酷云

标题: SQL Server查询优化器剖析详解(二) [打印本页]

作者: 简单生活 时间: 2015-1-16 14:07
标题: SQL Server查询优化器剖析详解(二)
也就是说在php本地调用pdoprepare中的mysql_real_escape_string来操作query，使用的是本地单字节字符集，即编码为xbfx5cx27，并带入到mysql中查询，由于使用setnames设置了连接字符集.媒介
为了充实切磋查询优化器利用划定规矩机关实行企图的体例，我们必要一个来断定用于优化特定查询的那些划定规矩。在SQLServer2005今后，包括了一个未公然的DMV（DynamicManagementView），该DMV会显现优化器所利用的划定规矩。在运转一个测试的查询之前，检察下该DMV所显现信息的快照，并与运转测试查询后的DMV数据举行对照，我们能够推导出优化器为该查询所挪用的划定规矩。

在我们入手下手懂得该DMV之前，我们必要断定几个关于SQLServer查询优化器的内情。接上去的内容都是基于在文章1里展现的’trees和rules’的信息。

优化的历程
查询优化是一个迭代的历程，起首从逻辑操纵符树的根部入手下手，并以一个符合的物理操纵停止。各类大概的实行企图是经由过程使用各类划定规矩来天生的，这些划定规矩会招致以后实行企图的某个部分举行一个逻辑转换大概转化为一个物理操纵。
优化器其实不会实验往婚配每条查询里每部分一切可用的划定规矩。只管周全婚配能够包管找到最好的实行企图，但其编译工夫和内存利用倒是没法承受的。

为了疾速的找到一个好的实行企图，优化器会利用一系列的技能。在后续的文章中，我们会先容更多这方面的细节，但个中有两个技能是间接与该DMV相干的：

每个在逻辑树中的操纵符包括代码来形貌一切与它相婚配的划定规矩。这避免优化器往实验一些划定规矩招致最初没无机会发生一个更低本钱的实行企图。
每一个划定规矩里都包括了代码来盘算出一个值，从而暗示在以后高低文中划定规矩是不是是更优的。假如一个划定规矩可以削减实行企图的整体本钱（cost），那末它就更高的希冀值。一样平常来讲,经常使用的优化(好比改善谓词)就更具高希冀值的。而更专业的划定规矩,就像那些婚配索引视图的希冀值就不会那末高了。
劈面对几个大概的划定规矩,优化器利用希冀值作为部分裁剪（prune)战略。这有助于削减编译工夫,但同时仍旧在寻觅最优的转换。

sys.dm_exec_query_transformation_stats
该DMV里每条划定规矩对应一行，有以下几个列：

name这列暗示该划定规矩的外部称号，比方JNtoSM暗示将一个逻辑的innerjoin转换为物理的sort-mergejoin操纵符。
promised列暗示该划定规矩已被哀求为优化器供应希冀值。
promise_total列是一切希冀值的一个复杂乞降。
promise_avg列恰好是promise_total除以promised
built_substitute列跟踪了该划定规矩几次发生一个备用的完成。

succeeded列跟踪了几次该划定规矩天生一个转换且被乐成增加选为无效的备用战略。并非一切的发生一个备用完成的转换城市婚配以后查询的实行企图的特别请求。（比方，备用完成大概不会保留一个哀求的分列按次大概其他属性)。

利用该DMV
以下剧本在SQLServerx86DeveloperEdition,versions10.0.2775(2008SP1CU8)和9.0.4294(2005SP3CU9)版本上已测试经由过程，但也大概在其他版本上不成用。

既然该DMV包括了全部server局限的优化器信息，为了猎取准确了局，你必要包管没有其他并行的优化举动正在测试的服务器上运转。最好是在一个测试的SQLServer服务器上并包管以后只要一个毗连的情况下去举行测试。

起首，我们必要临盆一个一时表来保留在运转测试查询前该DMV里的值的快照：
SELECTTOP(0)
name,
promise_total,
promised,
built_substitute,
succeeded
INTO#Snapshot
FROMsys.dm_exec_query_transformation_stats;

如今，我们能够写一个批处置来捕捉一个该DMV的快照，然后运转我们的测试查询，接着show出运转后与运转前该DMV里值的差别：
--Clearthesnapshot
TRUNCATETABLE#Snapshot;

--SaveasnapshotoftheDMV
INSERT#Snapshot
(
name,
promise_total,
promised,
built_substitute,
succeeded
)
SELECTname,
promise_total,
promised,
built_substitute,
succeeded
FROMsys.dm_exec_query_transformation_stats
OPTION(KEEPFIXEDPLAN);

--Queryundertest
--MustuseOPTION(RECOMPILE)
SELECTP.ProductNumber,
P.ProductID,
total_qty=SUM(I.Quantity)
FROMProduction.ProductP
JOINProduction.ProductInventoryI
ONI.ProductID=P.ProductID
WHEREP.ProductNumberLIKENT%
GROUPBY
P.ProductID,
P.ProductNumber
OPTION(RECOMPILE);

--Results
SELECTQTS.name,
promise=QTS.promised-S.promised,
promise_value_avg=
CASE
WHENQTS.promised=S.promised
THEN0
ELSE
(QTS.promise_total-S.promise_total)/
(QTS.promised-S.promised)
END,
built=QTS.built_substitute-S.built_substitute,
success=QTS.succeeded-S.succeeded
FROM#SnapshotS
JOINsys.dm_exec_query_transformation_statsQTS
ONQTS.name=S.name
WHEREQTS.succeeded!=S.succeeded
ORDERBY
promise_value_avgDESC
OPTION(KEEPFIXEDPLAN);

该测试查询必需有OPTION(RECOMPILE)查询hint，确保会有一个编译产生。该批处置中的其他语句有OPTION(KEEPFIXEDPLAN)来制止编译招致的了局产生偏向。

下面的例子中，利用的是我们在AdventureWorks一向运转的查询语句。它最初天生了我们后面已看到过的，充实优化的实行企图：

上面从一个典范的运转中的(部分)了局:

输入显现了划定规矩名字，希冀值被盘算的次数，所发生的均匀的希冀值，转换布局被创立的次数，和该布局被乐成到场到优化器的可选列表的次数。

必要注重到的是，划定规矩大概被挪用屡次，由于它们大概在查询语句里的多个中央被婚配，同时编译历程是一个递回历程。同时，我们也注重到有些划定规矩的希冀值为0，这意味着盘算希冀值的代码没有充足的信息来天生值。MySQL这个名字是怎么来的已经不清楚了。基本指南和大量的库和工具带有前缀“my”已经有10年以上，而且不管怎样，MySQLAB创始人之一的的女儿也叫My。

作者: 小妖女 时间: 2015-1-18 12:00
两个月啃那本sqlserver2005技术内部-存储引擎，花了几个月啃四本书

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

作者: 老尸 时间: 2015-2-2 22:05
但换公司用MSSQL2K感觉自己好像根本就不了解MSSQL。什么DTS触发器以前根本没用过。

作者: 海妖 时间: 2015-2-8 12:26
一个是把SQL语句写到客户端,可以使用DataSet进行加工;

作者: 第二个灵魂 时间: 2015-2-25 14:44
多走走一此相关论坛，多看一些实例开发，多交流0经验，没什么的，我也是刚学没多久!加油

作者: 分手快乐 时间: 2015-3-7 22:38
学习SQL语言的话如果要学会去做网站就不是很难!但是要做数据库管理的话就有难度了!

作者: 小魔女 时间: 2015-3-15 16:18
光写几个SQL实在叫无知。

作者: 飘灵儿 时间: 2015-3-22 03:07
而SQLServer如果能像Oracle一样可以为登陆分配如：5%的cpu，10%的内存。就可以解决这个漏洞。

欢迎光临仓酷云 (http://ckuyun.com/)